SET NAMES utf8mb4;
START TRANSACTION;

CREATE TABLE event_day_packs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 unit_id BIGINT UNSIGNED NOT NULL, meeting_id BIGINT UNSIGNED NOT NULL, programme_id BIGINT UNSIGNED NOT NULL,
 seating_snapshot_id BIGINT UNSIGNED NOT NULL, generated_by BIGINT UNSIGNED NOT NULL,
 generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, expires_at DATETIME NOT NULL, purged_at DATETIME NULL,
 source_fingerprint CHAR(64) NOT NULL, snapshot_json LONGTEXT NULL, restricted_snapshot_cipher LONGTEXT NULL,
 KEY idx_event_pack_event(meeting_id,generated_at,id), KEY idx_event_pack_expiry(expires_at,purged_at),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(meeting_id) REFERENCES meetings(id),
 FOREIGN KEY(programme_id) REFERENCES event_programmes(id), FOREIGN KEY(seating_snapshot_id) REFERENCES programme_table_plan_snapshots(id),
 FOREIGN KEY(generated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE event_day_pack_people (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pack_id BIGINT UNSIGNED NOT NULL,
 person_type ENUM('booking','guest') NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 display_name VARCHAR(240) NOT NULL, booking_reference VARCHAR(50) NOT NULL,
 UNIQUE KEY uq_pack_person(pack_id,person_type,person_id), KEY idx_pack_people(pack_id,display_name,id),
 FOREIGN KEY(pack_id) REFERENCES event_day_packs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE offline_checkin_batches (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, pack_id BIGINT UNSIGNED NOT NULL,
 unit_id BIGINT UNSIGNED NOT NULL, meeting_id BIGINT UNSIGNED NOT NULL,
 status ENUM('review','approved','applied','cancelled') NOT NULL DEFAULT 'review',
 created_by BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_by BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, applied_by BIGINT UNSIGNED NULL, applied_at DATETIME NULL,
 KEY idx_offline_batch_event(meeting_id,status,created_at), FOREIGN KEY(pack_id) REFERENCES event_day_packs(id),
 FOREIGN KEY(unit_id) REFERENCES units(id), FOREIGN KEY(meeting_id) REFERENCES meetings(id),
 FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(reviewed_by) REFERENCES users(id), FOREIGN KEY(applied_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE offline_checkin_rows (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, batch_id BIGINT UNSIGNED NOT NULL,
 person_type ENUM('booking','guest') NOT NULL, person_id BIGINT UNSIGNED NOT NULL,
 marked_arrived_at DATETIME NULL,
 review_state ENUM('ready','duplicate','conflict') NOT NULL,
 review_decision ENUM('pending','apply','skip') NOT NULL DEFAULT 'pending',
 result_state ENUM('pending','applied','duplicate','conflict','skipped') NOT NULL DEFAULT 'pending',
 live_arrived_at_review DATETIME NULL, detail_code VARCHAR(60) NULL,
 UNIQUE KEY uq_offline_batch_person(batch_id,person_type,person_id),
 FOREIGN KEY(batch_id) REFERENCES offline_checkin_batches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO migration_history(version,name,checksum,execution_ms)
VALUES('202609280037','Short-lived event-day packs and offline check-in reconciliation',SHA2('202609280037_event_day_packs_v1',256),0);
COMMIT;