-- =============================================================================
-- Made in Heaven Wedding Reels -- Consolidated MySQL schema (FOR REVIEW)
-- =============================================================================
--
-- WHAT THIS FILE IS
-- Codey (Codex) already generated proposed table-creation SQL for this app,
-- split across six phase-based migration files:
--   server/database/migrations/001_phase1_weddings_assets_reels.sql
--   server/database/migrations/002_phase2_spins.sql
--   server/database/migrations/003_phase3_realtime_events.sql
--   server/database/migrations/004_phase4_replacements.sql
--   server/database/migrations/005_phase5_history_admin_exports.sql
--   server/database/migrations/006_phase6_airbridge_join_resolution.sql
-- Those are plain `CREATE TABLE IF NOT EXISTS` statements -- no ORM, no Doctrine,
-- no Phinx-specific PHP wrapper -- so they already run fine through mysqli,
-- PDO, the `mysql` CLI, or a phpMyAdmin import. Per docs/RELEASE_READINESS.md
-- these were applied successfully to a clean MySQL 8 database in Docker.
--
-- This file is that same set of tables, concatenated in dependency order, with
-- one real inconsistency fixed and documented below. It changes nothing on
-- disk in server/database/migrations/ -- those originals are untouched. This
-- is meant to be your reviewable, "ready to actually run" version.
--
-- THE ONE FIX MADE HERE
-- Migrations 001/002/006 declare `wedding_id` (and `weddings.id` itself) as
-- BINARY(16) -- a packed 16-byte UUID. Migrations 003/004/005 declare
-- `wedding_id` as CHAR(36) -- a plain UUID string like
-- "3fa85f64-5717-4562-b3fc-2c963f66afa6". Those two representations of "the
-- same wedding" cannot be joined or foreign-keyed together as originally
-- written. Eleven of the eighteen tables already use CHAR(36), and plain
-- mysqli code is simpler to write against plain string UUIDs (no
-- bin2hex()/hex2bin() packing at every read/write), so this file standardizes
-- every id/wedding_id column on CHAR(36) and lets the app generate UUIDs in
-- PHP (see Support/Uuid.php, already in server/src/Support/).
--
-- WHAT THIS FILE DOES NOT DO
-- It does not wire the running PHP app to these tables. Right now
-- server/src/Support/FileDatabase.php reads/writes a flat JSON file
-- (server/storage/dev-database.json) instead of real MySQL -- that IS the
-- "simulated backend" you saw demoed. Building the real mysqli
-- service/repository layer that reads and writes these tables is the next
-- concrete backend task, not this file.
--
-- HOW TO APPLY
-- cPanel/shared hosting: phpMyAdmin -> your database -> Import -> choose this
-- file -> Go.
-- Local: mysql -u root -p wedding_reels < CONSOLIDATED_SCHEMA_FOR_REVIEW.sql
--
-- =============================================================================

SET NAMES utf8mb4;

-- -----------------------------------------------------------------------------
-- Phase 1: wedding setup, assets, symbols, occupants, static reel strips
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS weddings (
  id CHAR(36) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wedding_assets (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) 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 CHAR(64) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS symbol_identities (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) 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 CHAR(36) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS symbol_occupants (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  symbol_identity_id CHAR(36) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS reel_strips (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Phase 2: server-authoritative spin authorizations
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS spins (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  guest_id CHAR(36) NULL,
  session_id CHAR(36) 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 CHAR(36) NULL,
  symbols_json JSON NOT NULL,
  target_stops_json JSON NOT NULL,
  claim_token_hash CHAR(64) NULL,
  commitment_hash CHAR(64) NOT NULL,
  authorized_at DATETIME(6) NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  completed_at DATETIME(6) NULL,
  UNIQUE KEY uq_phase2_spin_idempotency (wedding_id, session_id, idempotency_key),
  KEY ix_phase2_spin_wedding_time (wedding_id, authorized_at),
  CONSTRAINT fk_phase2_spin_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Phase 3: durable realtime event history and outbox
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS realtime_events (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(64) NOT NULL,
  payload JSON NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_realtime_events_wedding_sequence (wedding_id, sequence),
  CONSTRAINT fk_phase3_event_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS realtime_outbox (
  id CHAR(36) PRIMARY KEY,
  realtime_event_id CHAR(36) NOT NULL,
  wedding_id CHAR(36) NOT NULL,
  publish_status ENUM('PENDING','PUBLISHED','FAILED') NOT NULL DEFAULT 'PENDING',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  published_at TIMESTAMP NULL,
  CONSTRAINT fk_phase3_outbox_event FOREIGN KEY (realtime_event_id) REFERENCES realtime_events(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Phase 4: capture entitlement, immutable image variants, replacement commits
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS replacement_entitlements (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  spin_id CHAR(36) NOT NULL,
  session_id VARCHAR(128) NOT NULL,
  target_symbol_id INT NOT NULL,
  token_hash CHAR(64) NOT NULL,
  status ENUM('ACTIVE','USED','EXPIRED','CANCELLED') NOT NULL DEFAULT 'ACTIVE',
  expires_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  used_at TIMESTAMP NULL,
  UNIQUE KEY uq_one_active_entitlement (wedding_id, status),
  CONSTRAINT fk_phase4_entitlement_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_phase4_entitlement_spin FOREIGN KEY (spin_id) REFERENCES spins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS occupant_images (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  archive_url VARCHAR(1024) NOT NULL,
  reel_url VARCHAR(1024) NOT NULL,
  thumbnail_url VARCHAR(1024) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_phase4_image_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS occupant_history (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  symbol_id INT NOT NULL,
  event_id CHAR(36) NOT NULL,
  removed_occupant JSON NOT NULL,
  added_occupant JSON NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_phase4_history_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS replacement_commits (
  idempotency_key VARCHAR(191) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  entitlement_id CHAR(36) NOT NULL,
  upload_id CHAR(36) NOT NULL,
  canonical_result JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_phase4_commit_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id),
  CONSTRAINT fk_phase4_commit_entitlement FOREIGN KEY (entitlement_id) REFERENCES replacement_entitlements(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Phase 5: append-only history, moderation, restore, close, snapshot, export
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS history_events (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  sequence BIGINT UNSIGNED NOT NULL,
  state_version BIGINT UNSIGNED NOT NULL,
  type VARCHAR(80) NOT NULL,
  payload JSON NOT NULL,
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_history_sequence (wedding_id, sequence),
  CONSTRAINT fk_phase5_history_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS symbol_occupant_versions (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  symbol_id INT NOT NULL,
  occupant JSON NOT NULL,
  previous_occupant JSON NULL,
  next_occupant JSON NULL,
  source_event_id CHAR(36) NULL,
  replacement_reason VARCHAR(80) NOT NULL,
  started_at TIMESTAMP NOT NULL,
  ended_at TIMESTAMP NULL,
  CONSTRAINT fk_phase5_version_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS moderation_actions (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  administrator_id VARCHAR(191) NOT NULL,
  action VARCHAR(80) NOT NULL,
  target JSON NOT NULL,
  before_ref JSON NULL,
  after_ref JSON NULL,
  reason TEXT NOT NULL,
  request_id VARCHAR(191) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_phase5_moderation_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS final_snapshots (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  snapshot JSON NOT NULL,
  manifest_hash CHAR(64) NOT NULL,
  immutable TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_final_snapshot_wedding (wedding_id),
  CONSTRAINT fk_phase5_snapshot_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS export_packages (
  id CHAR(36) PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  manifest JSON NOT NULL,
  checksum_manifest JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_phase5_export_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- Phase 6: AirBridge (acoustic discovery) join tokens and resolution audit
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS join_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wedding_id CHAR(36) NOT NULL,
  public_wedding_id VARCHAR(96) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  source ENUM('qr','link','airbridge','extension') NOT NULL DEFAULT 'airbridge',
  environment VARCHAR(64) NOT NULL DEFAULT 'production',
  single_use TINYINT(1) NOT NULL DEFAULT 1,
  issued_at DATETIME(6) NOT NULL,
  expires_at DATETIME(6) NOT NULL,
  used_at DATETIME(6) NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  UNIQUE KEY uq_join_tokens_hash (token_hash),
  KEY idx_join_tokens_public_wedding (public_wedding_id),
  KEY idx_join_tokens_expiry (expires_at),
  CONSTRAINT fk_join_tokens_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS join_resolution_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  wedding_id CHAR(36) NULL,
  public_wedding_id VARCHAR(96) NOT NULL,
  source ENUM('qr','link','airbridge','extension') NOT NULL,
  outcome VARCHAR(64) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  remote_hash CHAR(64) NULL,
  occurred_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  KEY idx_join_audit_public_wedding_time (public_wedding_id, occurred_at),
  KEY idx_join_audit_token_hash (token_hash),
  CONSTRAINT fk_join_audit_wedding FOREIGN KEY (wedding_id) REFERENCES weddings(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;