-- Made in Heaven Wedding Reels - reference schema
-- MySQL 8.x. Adapt naming/types to the selected migration framework.

CREATE TABLE weddings (
  id BINARY(16) PRIMARY KEY,
  public_id VARCHAR(32) NOT NULL UNIQUE,
  join_slug VARCHAR(96) NOT NULL UNIQUE,
  title VARCHAR(160) NOT NULL,
  couple_name_1 VARCHAR(100) NOT NULL,
  couple_name_2 VARCHAR(100) NOT NULL,
  timezone VARCHAR(64) NOT NULL DEFAULT 'America/Los_Angeles',
  status ENUM('DRAFT','OPEN','REPLACEMENT_PENDING','COMMITTING_REPLACEMENT','ANNOUNCING','PAUSED_BY_ADMIN','CLOSED') NOT NULL DEFAULT 'DRAFT',
  state_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
  event_sequence BIGINT UNSIGNED NOT NULL DEFAULT 0,
  normal_win_rate DECIMAL(7,6) NOT NULL DEFAULT 0.050000,
  protected_win_rate DECIMAL(7,6) NOT NULL DEFAULT 0.005000,
  min_spin_interval_ms INT UNSIGNED NOT NULL DEFAULT 2500,
  entitlement_seconds INT UNSIGNED NOT NULL DEFAULT 120,
  announcement_seconds INT UNSIGNED NOT NULL DEFAULT 8,
  pause_policy ENUM('finish_in_progress','pause_on_commit','no_global_pause') NOT NULL DEFAULT 'finish_in_progress',
  moderation_mode ENUM('immediate','winner_preview','admin_approval') NOT NULL DEFAULT 'winner_preview',
  require_guest_name BOOLEAN NOT NULL DEFAULT FALSE,
  show_guest_names BOOLEAN NOT NULL DEFAULT TRUE,
  notify_removed_guest BOOLEAN NOT NULL DEFAULT TRUE,
  opens_at DATETIME(6) NULL,
  closes_at DATETIME(6) NULL,
  closed_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  CHECK (normal_win_rate BETWEEN 0.020000 AND 0.100000),
  CHECK (protected_win_rate BETWEEN 0.000000 AND 0.020000)
) ENGINE=InnoDB;

CREATE TABLE wedding_assets (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  asset_role ENUM('launch_icon','background','splash','protected_image','default_symbol','music','spin_sound','win_sound','protected_sound','arrival_sound','departure_sound') NOT NULL,
  logical_key VARCHAR(64) NOT NULL,
  storage_key VARCHAR(512) NOT NULL,
  mime_type VARCHAR(100) NOT NULL,
  width INT UNSIGNED NULL,
  height INT UNSIGNED NULL,
  bytes BIGINT UNSIGNED NOT NULL,
  sha256 BINARY(32) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_wedding_asset_key (wedding_id, asset_role, logical_key),
  CONSTRAINT fk_asset_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;

CREATE TABLE guests (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  display_name VARCHAR(100) NULL,
  public_alias VARCHAR(100) NULL,
  status ENUM('ACTIVE','BANNED','DELETED') NOT NULL DEFAULT 'ACTIVE',
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  KEY ix_guest_wedding (wedding_id),
  CONSTRAINT fk_guest_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;

CREATE TABLE guest_sessions (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  guest_id BINARY(16) NOT NULL,
  session_hash BINARY(32) NOT NULL UNIQUE,
  platform ENUM('PWA_IOS','PWA_ANDROID','WEB_DESKTOP','CHROME_EXTENSION','OTHER') NOT NULL,
  notification_endpoint TEXT NULL,
  last_spin_at DATETIME(6) NULL,
  last_seen_at DATETIME(6) NOT NULL,
  revoked_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL,
  KEY ix_session_wedding_guest (wedding_id, guest_id),
  CONSTRAINT fk_session_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_session_guest FOREIGN KEY (guest_id) REFERENCES guests(id)
) ENGINE=InnoDB;

CREATE TABLE symbol_identities (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  symbol_number TINYINT UNSIGNED NOT NULL,
  match_class VARCHAR(64) NOT NULL,
  label VARCHAR(100) NOT NULL,
  protected BOOLEAN NOT NULL DEFAULT FALSE,
  default_asset_id BINARY(16) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_symbol_number (wedding_id, symbol_number),
  CONSTRAINT fk_symbol_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_symbol_default_asset FOREIGN KEY (default_asset_id) REFERENCES wedding_assets(id),
  CHECK (symbol_number BETWEEN 1 AND 20)
) ENGINE=InnoDB;

CREATE TABLE symbol_occupants (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  symbol_identity_id BINARY(16) NOT NULL,
  occupant_type ENUM('DEFAULT','GUEST','ADMIN_RESTORE') NOT NULL,
  guest_id BINARY(16) NULL,
  display_name_snapshot VARCHAR(100) NULL,
  occupant_version INT UNSIGNED NOT NULL,
  previous_occupant_id BINARY(16) NULL,
  started_at DATETIME(6) NOT NULL,
  ended_at DATETIME(6) NULL,
  end_reason ENUM('REPLACED','RESTORED','MODERATED','EVENT_CLOSED') NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_occupant_version (symbol_identity_id, occupant_version),
  KEY ix_current_occupant (symbol_identity_id, ended_at),
  CONSTRAINT fk_occupant_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_occupant_symbol FOREIGN KEY (symbol_identity_id) REFERENCES symbol_identities(id),
  CONSTRAINT fk_occupant_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
  CONSTRAINT fk_occupant_previous FOREIGN KEY (previous_occupant_id) REFERENCES symbol_occupants(id)
) ENGINE=InnoDB;

CREATE TABLE occupant_images (
  id BINARY(16) PRIMARY KEY,
  occupant_id BINARY(16) NOT NULL,
  ordinal TINYINT UNSIGNED NOT NULL,
  archive_asset_id BINARY(16) NOT NULL,
  reel_asset_id BINARY(16) NOT NULL,
  thumbnail_asset_id BINARY(16) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_occupant_image (occupant_id, ordinal),
  CONSTRAINT fk_occ_image_occupant FOREIGN KEY (occupant_id) REFERENCES symbol_occupants(id),
  CONSTRAINT fk_occ_image_archive FOREIGN KEY (archive_asset_id) REFERENCES wedding_assets(id),
  CONSTRAINT fk_occ_image_reel FOREIGN KEY (reel_asset_id) REFERENCES wedding_assets(id),
  CONSTRAINT fk_occ_image_thumb FOREIGN KEY (thumbnail_asset_id) REFERENCES wedding_assets(id),
  CHECK (ordinal BETWEEN 1 AND 3)
) ENGINE=InnoDB;

CREATE TABLE reel_strips (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  reel_number TINYINT UNSIGNED NOT NULL,
  strip_version INT UNSIGNED NOT NULL DEFAULT 1,
  stops_json JSON NOT NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_reel_version (wedding_id, reel_number, strip_version),
  CONSTRAINT fk_reel_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CHECK (reel_number BETWEEN 1 AND 3)
) ENGINE=InnoDB;

CREATE TABLE spins (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  guest_id BINARY(16) NOT NULL,
  session_id BINARY(16) NOT NULL,
  idempotency_key VARCHAR(96) NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  result_type ENUM('NO_WIN','NORMAL_WIN','MADE_IN_HEAVEN','CELEBRATION_ONLY','EVENT_PAUSED','EVENT_CLOSED') NOT NULL,
  target_symbol_identity_id BINARY(16) NULL,
  symbols_json JSON NOT NULL,
  target_stops_json JSON NOT NULL,
  claim_token_hash BINARY(32) NULL,
  commitment_hash BINARY(32) NOT NULL,
  authorized_at DATETIME(6) NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  completed_at DATETIME(6) NULL,
  UNIQUE KEY uq_spin_idempotency (session_id, idempotency_key),
  KEY ix_spin_wedding_time (wedding_id, authorized_at),
  CONSTRAINT fk_spin_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_spin_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
  CONSTRAINT fk_spin_session FOREIGN KEY (session_id) REFERENCES guest_sessions(id),
  CONSTRAINT fk_spin_target_symbol FOREIGN KEY (target_symbol_identity_id) REFERENCES symbol_identities(id)
) ENGINE=InnoDB;

CREATE TABLE capture_entitlements (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  spin_id BINARY(16) NOT NULL UNIQUE,
  guest_id BINARY(16) NOT NULL,
  target_symbol_identity_id BINARY(16) NOT NULL,
  expected_state_version BIGINT UNSIGNED NOT NULL,
  status ENUM('PENDING','UPLOADING','READY','COMMITTED','CANCELLED','EXPIRED','REJECTED') NOT NULL DEFAULT 'PENDING',
  expires_at DATETIME(6) NOT NULL,
  committed_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL,
  KEY ix_entitlement_wedding_status (wedding_id, status),
  CONSTRAINT fk_ent_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_ent_spin FOREIGN KEY (spin_id) REFERENCES spins(id),
  CONSTRAINT fk_ent_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
  CONSTRAINT fk_ent_symbol FOREIGN KEY (target_symbol_identity_id) REFERENCES symbol_identities(id)
) ENGINE=InnoDB;

CREATE TABLE replacement_events (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  entitlement_id BINARY(16) NULL,
  symbol_identity_id BINARY(16) NOT NULL,
  removed_occupant_id BINARY(16) NOT NULL,
  added_occupant_id BINARY(16) NOT NULL,
  event_sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  reason ENUM('GUEST_WIN','ADMIN_RESTORE','MODERATION') NOT NULL,
  occurred_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_replacement_sequence (wedding_id, event_sequence),
  CONSTRAINT fk_rep_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_rep_entitlement FOREIGN KEY (entitlement_id) REFERENCES capture_entitlements(id),
  CONSTRAINT fk_rep_symbol FOREIGN KEY (symbol_identity_id) REFERENCES symbol_identities(id),
  CONSTRAINT fk_rep_removed FOREIGN KEY (removed_occupant_id) REFERENCES symbol_occupants(id),
  CONSTRAINT fk_rep_added FOREIGN KEY (added_occupant_id) REFERENCES symbol_occupants(id)
) ENGINE=InnoDB;

CREATE TABLE event_log (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  event_sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  actor_type ENUM('GUEST','ADMIN','SYSTEM') NOT NULL,
  actor_id BINARY(16) NULL,
  payload_json JSON NOT NULL,
  occurred_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_event_sequence (wedding_id, event_sequence),
  KEY ix_event_type_time (wedding_id, event_type, occurred_at),
  CONSTRAINT fk_event_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;

CREATE TABLE final_snapshots (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  snapshot_json JSON NOT NULL,
  manifest_sha256 BINARY(32) NOT NULL,
  created_at DATETIME(6) NOT NULL,
  UNIQUE KEY uq_final_snapshot (wedding_id),
  CONSTRAINT fk_snapshot_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;