-- Phase 4 capture entitlement, immutable image variants, occupant history, and replacement commit state.
-- Intended implementation: one MySQL transaction plus wedding-scoped Redis/distributed lock.

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)
);

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
);

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
);

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
);