41 lines
2.2 KiB
SQL
41 lines
2.2 KiB
SQL
-- SUFW Terminbuchung: Bestätigungsfluss, Test-Mailausgang und iCalendar-Feeds
|
|
-- Diese Migration ergänzt das Anfangsschema und kann gefahrlos erneut laufen.
|
|
|
|
ALTER TABLE `sufw_booking_registrations`
|
|
ADD COLUMN IF NOT EXISTS `confirmation_token_hash` char(64) DEFAULT NULL AFTER `privacy_accepted_at`,
|
|
ADD COLUMN IF NOT EXISTS `confirmation_expires_at` datetime DEFAULT NULL AFTER `confirmation_token_hash`,
|
|
ADD COLUMN IF NOT EXISTS `confirmed_at` datetime DEFAULT NULL AFTER `confirmation_expires_at`,
|
|
ADD COLUMN IF NOT EXISTS `removed_at` datetime DEFAULT NULL AFTER `confirmed_at`;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sufw_booking_project_settings` (
|
|
`project` varchar(32) NOT NULL,
|
|
`feed_token` char(64) NOT NULL,
|
|
`updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
|
|
PRIMARY KEY (`project`),
|
|
UNIQUE KEY `sufw_booking_project_settings_feed_token` (`feed_token`),
|
|
CONSTRAINT `sufw_booking_project_settings_project_check` CHECK (`project` IN ('perspektivwerk', 'produktionsschule'))
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `sufw_booking_outbox` (
|
|
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
|
|
`registration_id` char(36) NOT NULL,
|
|
`kind` varchar(32) NOT NULL,
|
|
`recipient_email` varchar(254) NOT NULL,
|
|
`subject` varchar(255) NOT NULL,
|
|
`body` text NOT NULL,
|
|
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
|
|
`sent_at` datetime DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `sufw_booking_outbox_registration_kind` (`registration_id`, `kind`),
|
|
KEY `sufw_booking_outbox_pending` (`sent_at`, `created_at`),
|
|
CONSTRAINT `sufw_booking_outbox_kind_check` CHECK (`kind` IN ('confirmation_request', 'confirmation', 'reminder')),
|
|
CONSTRAINT `sufw_booking_outbox_registration_fk` FOREIGN KEY (`registration_id`) REFERENCES `sufw_booking_registrations` (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT IGNORE INTO `sufw_booking_project_settings` (`project`, `feed_token`)
|
|
VALUES
|
|
('perspektivwerk', SHA2(CONCAT(UUID(), RAND(), NOW(6)), 256)),
|
|
('produktionsschule', SHA2(CONCAT(UUID(), RAND(), NOW(6)), 256));
|
|
|
|
INSERT IGNORE INTO `sufw_booking_schema_migrations` (`version`) VALUES ('002_booking_workflow');
|