-- Phase 1 migration: wedding setup, assets, symbols, occupants, and static reel strips.
-- This narrows the reference schema to the Phase 1 tables needed before gameplay.

CREATE TABLE IF NOT EXISTS 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','PAUSED_BY_ADMIN','CLOSED') NOT NULL DEFAULT 'DRAFT',
  state_version BIGINT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS wedding_assets (
  id BINARY(16) PRIMARY KEY,
  wedding_id BINARY(16) NOT NULL,
  asset_role ENUM('launch_icon','background','reel_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_phase1_asset_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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_phase1_symbol_number (wedding_id, symbol_number),
  CHECK (symbol_number BETWEEN 1 AND 20),
  CONSTRAINT fk_phase1_symbol_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_phase1_symbol_asset FOREIGN KEY (default_asset_id) REFERENCES wedding_assets(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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 DEFAULT 'DEFAULT',
  display_name_snapshot VARCHAR(100) NULL,
  occupant_version INT UNSIGNED NOT NULL DEFAULT 1,
  started_at DATETIME(6) NOT NULL,
  ended_at DATETIME(6) NULL,
  UNIQUE KEY uq_phase1_current_version (symbol_identity_id, occupant_version),
  CONSTRAINT fk_phase1_occupant_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_phase1_occupant_symbol FOREIGN KEY (symbol_identity_id) REFERENCES symbol_identities(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS 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_phase1_reel_version (wedding_id, reel_number, strip_version),
  CHECK (reel_number BETWEEN 1 AND 3),
  CONSTRAINT fk_phase1_reel_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB;