SET NAMES utf8mb4;
START TRANSACTION;

ALTER TABLE event_programmes
 ADD COLUMN dining_mode ENUM('fixed','choice') NOT NULL DEFAULT 'fixed' AFTER capacity,
 ADD COLUMN menu_choice_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER dining_mode,
 ADD COLUMN menu_choice_cutoff DATETIME NULL AFTER menu_choice_enabled,
 ADD COLUMN published_menu_version_id BIGINT UNSIGNED NULL AFTER menu_choice_cutoff;

CREATE TABLE programme_menu_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL,
 version_no INT UNSIGNED NOT NULL, status ENUM('draft','published','retired') NOT NULL DEFAULT 'draft',
 title VARCHAR(180) NOT NULL DEFAULT 'Dining menu', change_note VARCHAR(1000) NULL,
 published_by BIGINT UNSIGNED NULL, published_at DATETIME NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_programme_menu_version(programme_id,version_no), KEY idx_programme_menu_status(programme_id,status),
 FOREIGN KEY(programme_id) REFERENCES event_programmes(id) ON DELETE CASCADE,
 FOREIGN KEY(published_by) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE event_programmes ADD CONSTRAINT fk_programme_published_menu FOREIGN KEY(published_menu_version_id) REFERENCES programme_menu_versions(id) ON DELETE SET NULL;

CREATE TABLE programme_menu_courses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_version_id BIGINT UNSIGNED NOT NULL,
 stable_key CHAR(36) NOT NULL, name VARCHAR(120) NOT NULL, description VARCHAR(1000) NULL,
 display_order INT UNSIGNED NOT NULL, selection_required TINYINT(1) NOT NULL DEFAULT 0,
 UNIQUE KEY uq_menu_course_key(menu_version_id,stable_key), KEY idx_menu_course_order(menu_version_id,display_order),
 FOREIGN KEY(menu_version_id) REFERENCES programme_menu_versions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_menu_dishes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, course_id BIGINT UNSIGNED NOT NULL,
 stable_key CHAR(36) NOT NULL, name VARCHAR(180) NOT NULL, description VARCHAR(1500) NULL,
 available TINYINT(1) NOT NULL DEFAULT 1, supplement DECIMAL(10,2) NOT NULL DEFAULT 0.00,
 allergen_summary VARCHAR(2000) NULL, allergen_verified_at DATETIME NULL, allergen_verified_by BIGINT UNSIGNED NULL,
 display_order INT UNSIGNED NOT NULL,
 UNIQUE KEY uq_menu_dish_key(course_id,stable_key), KEY idx_menu_dish_available(course_id,available,display_order),
 FOREIGN KEY(course_id) REFERENCES programme_menu_courses(id) ON DELETE CASCADE,
 FOREIGN KEY(allergen_verified_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_dish_supplement CHECK(supplement>=0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_diners (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL,
 person_key CHAR(64) NOT NULL, booking_id BIGINT UNSIGNED NOT NULL, guest_id BIGINT UNSIGNED NULL,
 diner_kind ENUM('main','guest') NOT NULL, display_name VARCHAR(240) NOT NULL,
 dietary_requirements TEXT NULL, allergy_details TEXT NULL, active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_programme_diner(programme_id,person_key), UNIQUE KEY uq_booking_guest_diner(booking_id,guest_id),
 KEY idx_programme_diner_booking(booking_id,active), FOREIGN KEY(programme_id) REFERENCES event_programmes(id) ON DELETE CASCADE,
 FOREIGN KEY(booking_id) REFERENCES bookings(id) ON DELETE CASCADE, FOREIGN KEY(guest_id) REFERENCES guests(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE diner_menu_orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, diner_id BIGINT UNSIGNED NOT NULL,
 menu_version_id BIGINT UNSIGNED NOT NULL, status ENUM('current','needs_reselection','cancelled','superseded') NOT NULL DEFAULT 'current',
 base_charge DECIMAL(10,2) NOT NULL DEFAULT 0.00, supplement_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,
 submitted_by_type ENUM('attendee','officer','system') NOT NULL, submitted_by_user_id BIGINT UNSIGNED NULL,
 late_reason VARCHAR(1000) NULL, submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_diner_menu_order(diner_id,menu_version_id), KEY idx_menu_order_status(menu_version_id,status),
 FOREIGN KEY(diner_id) REFERENCES programme_diners(id) ON DELETE CASCADE, FOREIGN KEY(menu_version_id) REFERENCES programme_menu_versions(id),
 FOREIGN KEY(submitted_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE diner_course_choices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL,
 course_id BIGINT UNSIGNED NOT NULL, dish_id BIGINT UNSIGNED NOT NULL, supplement DECIMAL(10,2) NOT NULL DEFAULT 0.00,
 UNIQUE KEY uq_diner_course_choice(order_id,course_id), KEY idx_dish_totals(dish_id,order_id),
 FOREIGN KEY(order_id) REFERENCES diner_menu_orders(id) ON DELETE CASCADE,
 FOREIGN KEY(course_id) REFERENCES programme_menu_courses(id), FOREIGN KEY(dish_id) REFERENCES programme_menu_dishes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE diner_order_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, diner_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NULL,
 action VARCHAR(60) NOT NULL, detail_json JSON NOT NULL, actor_type ENUM('attendee','officer','system') NOT NULL,
 actor_user_id BIGINT UNSIGNED NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_diner_history(diner_id,occurred_at,id), FOREIGN KEY(diner_id) REFERENCES programme_diners(id) ON DELETE CASCADE,
 FOREIGN KEY(order_id) REFERENCES diner_menu_orders(id) ON DELETE SET NULL, FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE catering_handoffs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL,
 handoff_no INT UNSIGNED NOT NULL, parent_handoff_id BIGINT UNSIGNED NULL, menu_version_id BIGINT UNSIGNED NOT NULL,
 snapshot_json LONGTEXT NOT NULL, comparison_json LONGTEXT NULL, approved_by BIGINT UNSIGNED NOT NULL,
 approved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_catering_handoff(programme_id,handoff_no), FOREIGN KEY(programme_id) REFERENCES event_programmes(id),
 FOREIGN KEY(parent_handoff_id) REFERENCES catering_handoffs(id), FOREIGN KEY(menu_version_id) REFERENCES programme_menu_versions(id),
 FOREIGN KEY(approved_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE catering_handoff_delivery (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, handoff_id BIGINT UNSIGNED NOT NULL,
 status ENUM('delivered','acknowledged') NOT NULL, method ENUM('manual','email','other') NOT NULL DEFAULT 'manual',
 acknowledgement_reference VARCHAR(190) NULL, recorded_by BIGINT UNSIGNED NOT NULL, recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_handoff_delivery(handoff_id,recorded_at,id), FOREIGN KEY(handoff_id) REFERENCES catering_handoffs(id) ON DELETE CASCADE,
 FOREIGN KEY(recorded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TRIGGER diner_order_history_no_update BEFORE UPDATE ON diner_order_history FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Diner order history is append-only';
CREATE TRIGGER diner_order_history_no_delete BEFORE DELETE ON diner_order_history FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Diner order history is append-only';
CREATE TRIGGER catering_handoff_no_update BEFORE UPDATE ON catering_handoffs FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Approved catering snapshots are immutable';
CREATE TRIGGER catering_handoff_no_delete BEFORE DELETE ON catering_handoffs FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Catering handoffs are append-only';

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280035','Per-diner menu choices and catering handoffs',SHA2('202609280035_diner_menu_choices_v1',256),0);
COMMIT;