SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE dining_room_templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, unit_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(160) NOT NULL, room_width DECIMAL(8,2) NOT NULL DEFAULT 100, room_height DECIMAL(8,2) NOT NULL DEFAULT 70,
 active TINYINT(1) NOT NULL DEFAULT 1, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_room_template(unit_id,name), FOREIGN KEY(unit_id) REFERENCES units(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE dining_room_template_tables (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, template_id BIGINT UNSIGNED NOT NULL,
 stable_key CHAR(36) NOT NULL, table_number VARCHAR(30) NOT NULL, table_name VARCHAR(100) NULL,
 shape ENUM('round','rectangular') NOT NULL DEFAULT 'round', is_top_table TINYINT(1) NOT NULL DEFAULT 0,
 x_pos DECIMAL(8,2) NOT NULL, y_pos DECIMAL(8,2) NOT NULL, width DECIMAL(8,2) NOT NULL, height DECIMAL(8,2) NOT NULL,
 capacity SMALLINT UNSIGNED NOT NULL, display_order INT UNSIGNED NOT NULL DEFAULT 0,
 UNIQUE KEY uq_template_table_key(template_id,stable_key), UNIQUE KEY uq_template_table_number(template_id,table_number),
 FOREIGN KEY(template_id) REFERENCES dining_room_templates(id) ON DELETE CASCADE,
 CONSTRAINT chk_template_table_size CHECK(width>0 AND height>0 AND capacity>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_table_plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL,
 room_template_id BIGINT UNSIGNED NULL, title VARCHAR(180) NOT NULL, revision_no INT UNSIGNED NOT NULL DEFAULT 1,
 status ENUM('draft','review','published') NOT NULL DEFAULT 'draft', created_by BIGINT UNSIGNED NOT NULL,
 published_snapshot_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_programme_plan(programme_id), FOREIGN KEY(programme_id) REFERENCES event_programmes(id) ON DELETE CASCADE,
 FOREIGN KEY(room_template_id) REFERENCES dining_room_templates(id) ON DELETE SET NULL, FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_plan_tables (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plan_id BIGINT UNSIGNED NOT NULL,
 stable_key CHAR(36) NOT NULL, table_number VARCHAR(30) NOT NULL, table_name VARCHAR(100) NULL,
 shape ENUM('round','rectangular') NOT NULL DEFAULT 'round', is_top_table TINYINT(1) NOT NULL DEFAULT 0,
 x_pos DECIMAL(8,2) NOT NULL, y_pos DECIMAL(8,2) NOT NULL, width DECIMAL(8,2) NOT NULL, height DECIMAL(8,2) NOT NULL,
 capacity SMALLINT UNSIGNED NOT NULL, display_order INT UNSIGNED NOT NULL DEFAULT 0,
 UNIQUE KEY uq_plan_table_key(plan_id,stable_key), UNIQUE KEY uq_plan_table_number(plan_id,table_number),
 FOREIGN KEY(plan_id) REFERENCES programme_table_plans(id) ON DELETE CASCADE,
 CONSTRAINT chk_plan_table_size CHECK(width>0 AND height>0 AND capacity>0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_plan_seats (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, table_id BIGINT UNSIGNED NOT NULL,
 seat_number SMALLINT UNSIGNED NOT NULL, x_pos DECIMAL(8,2) NULL, y_pos DECIMAL(8,2) NULL, locked TINYINT(1) NOT NULL DEFAULT 0,
 UNIQUE KEY uq_plan_seat(table_id,seat_number), FOREIGN KEY(table_id) REFERENCES programme_plan_tables(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_seat_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plan_id BIGINT UNSIGNED NOT NULL,
 seat_id BIGINT UNSIGNED NOT NULL, diner_id BIGINT UNSIGNED NOT NULL, assigned_by BIGINT UNSIGNED NOT NULL,
 assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_plan_assigned_seat(seat_id), UNIQUE KEY uq_plan_assigned_diner(plan_id,diner_id),
 FOREIGN KEY(plan_id) REFERENCES programme_table_plans(id) ON DELETE CASCADE,
 FOREIGN KEY(seat_id) REFERENCES programme_plan_seats(id) ON DELETE CASCADE,
 FOREIGN KEY(diner_id) REFERENCES programme_diners(id) ON DELETE CASCADE, FOREIGN KEY(assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_seating_preferences (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, programme_id BIGINT UNSIGNED NOT NULL,
 diner_id BIGINT UNSIGNED NOT NULL, preference_type ENUM('sit_together','keep_apart','honoured_visitor','accessibility','host_pairing') NOT NULL,
 related_diner_id BIGINT UNSIGNED NULL, hard_constraint TINYINT(1) NOT NULL DEFAULT 0,
 public_label VARCHAR(160) NULL, private_reason_cipher TEXT NULL, active TINYINT(1) NOT NULL DEFAULT 1,
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY idx_seating_preference(programme_id,diner_id,active), FOREIGN KEY(programme_id) REFERENCES event_programmes(id) ON DELETE CASCADE,
 FOREIGN KEY(diner_id) REFERENCES programme_diners(id) ON DELETE CASCADE, FOREIGN KEY(related_diner_id) REFERENCES programme_diners(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_table_plan_revisions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plan_id BIGINT UNSIGNED NOT NULL,
 revision_no INT UNSIGNED NOT NULL, action VARCHAR(60) NOT NULL, change_json JSON NOT NULL,
 actor_user_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_plan_revision(plan_id,revision_no), FOREIGN KEY(plan_id) REFERENCES programme_table_plans(id) ON DELETE CASCADE,
 FOREIGN KEY(actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE programme_table_plan_snapshots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, plan_id BIGINT UNSIGNED NOT NULL,
 snapshot_no INT UNSIGNED NOT NULL, payload_json LONGTEXT NOT NULL, source_fingerprint CHAR(64) NOT NULL,
 published_by BIGINT UNSIGNED NOT NULL, published_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_plan_snapshot(plan_id,snapshot_no), FOREIGN KEY(plan_id) REFERENCES programme_table_plans(id),
 FOREIGN KEY(published_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE programme_table_plans ADD CONSTRAINT fk_plan_published_snapshot FOREIGN KEY(published_snapshot_id) REFERENCES programme_table_plan_snapshots(id) ON DELETE SET NULL;
CREATE TRIGGER plan_revision_no_update BEFORE UPDATE ON programme_table_plan_revisions FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Table plan history is append-only';
CREATE TRIGGER plan_revision_no_delete BEFORE DELETE ON programme_table_plan_revisions FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Table plan history is append-only';
CREATE TRIGGER plan_snapshot_no_update BEFORE UPDATE ON programme_table_plan_snapshots FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Published table plans are immutable';
CREATE TRIGGER plan_snapshot_no_delete BEFORE DELETE ON programme_table_plan_snapshots FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='Published table plans are immutable';

INSERT INTO migration_history(version,name,checksum,execution_ms) VALUES('202609280036','Shared-programme dining table plans',SHA2('202609280036_programme_table_plans_v1',256),0);
COMMIT;