-- ============================================================
-- Patina · MySQL schema
-- Run order: top to bottom. All FKs are explicit. utf8mb4 throughout.
-- Designed for MySQL 8.0+. Should also work on MariaDB 10.5+.
--
-- Naming: "Versions" = Proofs (treatment_versions). "Workspace" = Library.
-- ============================================================

-- The express-mysql-session package creates its own `sessions` table at
-- startup. We do NOT define it here so the package can manage its own
-- schema. (See server.js — the session store is configured with
-- createDatabaseTable: true.)

-- ----- studios --------------------------------------------------
-- Production companies. Only verified studios can hold the 'studio' tier;
-- everyone else stays on 'pro'. Verification is manual (admin-only).
CREATE TABLE IF NOT EXISTS studios (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  slug         VARCHAR(120) NOT NULL,
  name         VARCHAR(200) NOT NULL,
  region       VARCHAR(8) DEFAULT NULL,         -- ISO-ish region code (e.g. 'ZA', 'NG')
  verified_at  DATETIME DEFAULT NULL,           -- NULL = pending verification
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_studios_slug (slug),
  KEY idx_studios_region (region),
  KEY idx_studios_verified_at (verified_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- users ----------------------------------------------------
-- bcrypt password_hash (60 chars). role_admin is the only server-checked
-- elevation flag (see /api/admin/* in server.js). tier is product-level.
CREATE TABLE IF NOT EXISTS users (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email                VARCHAR(254) NOT NULL,
  password_hash        VARCHAR(72)  NOT NULL,         -- bcrypt = 60 chars; allow headroom
  name                 VARCHAR(120) NOT NULL,
  role                 VARCHAR(40)  DEFAULT NULL,     -- 'Director' | 'Producer' | 'Executive Producer' | 'Studio' | 'Agency' | 'Other'
  studio_id            INT UNSIGNED DEFAULT NULL,     -- FK studios.id; nullable so users can register without a studio
  studio_text          VARCHAR(200) DEFAULT NULL,     -- free-text studio name pre-vetting
  region               VARCHAR(8)   DEFAULT NULL,     -- primary region (e.g. 'ZA')
  tier                 ENUM('pro','studio') NOT NULL DEFAULT 'pro',
  role_admin           TINYINT(1)   NOT NULL DEFAULT 0,
  email_verified_at    DATETIME DEFAULT NULL,
  last_login_at        DATETIME DEFAULT NULL,
  created_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_studio_id (studio_id),
  KEY idx_users_tier (tier),
  KEY idx_users_role_admin (role_admin),
  CONSTRAINT fk_users_studio FOREIGN KEY (studio_id) REFERENCES studios (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- treatments -----------------------------------------------
-- Owned by a user (and by extension a studio, via users.studio_id).
-- slides_json is the full Canvas state — kept opaque on the server.
CREATE TABLE IF NOT EXISTS treatments (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       INT UNSIGNED NOT NULL,
  slug          VARCHAR(160) NOT NULL,         -- url-safe per-user; unique within user
  title         VARCHAR(200) NOT NULL,
  slides_json   LONGTEXT NOT NULL,             -- JSON: { slides: [...], currentSlideIndex: N, document: {...} }
  is_archived   TINYINT(1) NOT NULL DEFAULT 0,
  share_token   CHAR(32) DEFAULT NULL,          -- nullable random token for #view/<token>
  password_hash VARCHAR(72) DEFAULT NULL,       -- optional viewer password (bcrypt)
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_treatments_user_slug (user_id, slug),
  UNIQUE KEY uq_treatments_share_token (share_token),
  KEY idx_treatments_user (user_id),
  KEY idx_treatments_updated (updated_at),
  CONSTRAINT fk_treatments_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- treatment_versions ("Proofs") ----------------------------
-- Named snapshots a director saves. label is the user-facing name (e.g. "v1 — pitch").
CREATE TABLE IF NOT EXISTS treatment_versions (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  treatment_id  INT UNSIGNED NOT NULL,
  version_num   INT UNSIGNED NOT NULL,         -- monotonic per treatment
  label         VARCHAR(120) DEFAULT NULL,
  slides_json   LONGTEXT NOT NULL,
  created_by    INT UNSIGNED DEFAULT NULL,     -- user_id; nullable so a deleted user doesn't nuke history
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_versions_treatment_num (treatment_id, version_num),
  KEY idx_versions_treatment (treatment_id),
  CONSTRAINT fk_versions_treatment FOREIGN KEY (treatment_id) REFERENCES treatments (id) ON DELETE CASCADE,
  CONSTRAINT fk_versions_user      FOREIGN KEY (created_by)   REFERENCES users (id)      ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- archive_clips --------------------------------------------
-- The 1,264+ reference clips. source + source_id is the natural key
-- (e.g. ('vimeo', '123456') or ('bunny', 'abc-def')).
CREATE TABLE IF NOT EXISTS archive_clips (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  source       ENUM('vimeo','bunny') NOT NULL,
  source_id    VARCHAR(120) NOT NULL,
  title        VARCHAR(300) NOT NULL,
  director     VARCHAR(200) DEFAULT NULL,
  studio_id    INT UNSIGNED DEFAULT NULL,
  studio_text  VARCHAR(200) DEFAULT NULL,     -- denormalised string if studio not in studios table
  year         SMALLINT UNSIGNED DEFAULT NULL,
  region       VARCHAR(8) DEFAULT NULL,
  genre        VARCHAR(40) DEFAULT NULL,
  duration_s   INT UNSIGNED DEFAULT NULL,
  thumb_url    VARCHAR(500) DEFAULT NULL,
  embed_url    VARCHAR(500) DEFAULT NULL,
  metadata     JSON DEFAULT NULL,              -- room for credits, brand, agency, notes
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_clips_source (source, source_id),
  KEY idx_clips_studio (studio_id),
  KEY idx_clips_region (region),
  KEY idx_clips_year (year),
  KEY idx_clips_genre (genre),
  CONSTRAINT fk_clips_studio FOREIGN KEY (studio_id) REFERENCES studios (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- archive_likes -------------------------------------------
-- Three near-identical join tables. Kept separate so the UI can ask "is
-- this clip in your saved list?" with a single primary-key lookup.
CREATE TABLE IF NOT EXISTS archive_likes (
  user_id    INT UNSIGNED NOT NULL,
  clip_id    INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, clip_id),
  KEY idx_likes_clip (clip_id),
  CONSTRAINT fk_likes_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_likes_clip FOREIGN KEY (clip_id) REFERENCES archive_clips (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS archive_saved (
  user_id    INT UNSIGNED NOT NULL,
  clip_id    INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, clip_id),
  KEY idx_saved_clip (clip_id),
  CONSTRAINT fk_saved_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_saved_clip FOREIGN KEY (clip_id) REFERENCES archive_clips (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS archive_watch_later (
  user_id    INT UNSIGNED NOT NULL,
  clip_id    INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, clip_id),
  KEY idx_watch_later_clip (clip_id),
  CONSTRAINT fk_watch_later_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT fk_watch_later_clip FOREIGN KEY (clip_id) REFERENCES archive_clips (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- password_resets ------------------------------------------
-- token_hash holds SHA-256 of the raw token; raw token only ever lives in
-- the reset email link. Single-use: used_at set on consumption.
CREATE TABLE IF NOT EXISTS password_resets (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  token_hash  CHAR(64) NOT NULL,         -- sha256 hex
  expires_at  DATETIME NOT NULL,
  used_at     DATETIME DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_pwreset_token (token_hash),
  KEY idx_pwreset_user (user_id),
  KEY idx_pwreset_expires (expires_at),
  CONSTRAINT fk_pwreset_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- email_verification_tokens --------------------------------
-- Issued on register and on email-change. Same shape as password_resets.
CREATE TABLE IF NOT EXISTS email_verification_tokens (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     INT UNSIGNED NOT NULL,
  email       VARCHAR(254) NOT NULL,        -- the email being verified (may differ from users.email until consumed)
  token_hash  CHAR(64) NOT NULL,
  expires_at  DATETIME NOT NULL,
  used_at     DATETIME DEFAULT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_emailverify_token (token_hash),
  KEY idx_emailverify_user (user_id),
  KEY idx_emailverify_expires (expires_at),
  CONSTRAINT fk_emailverify_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----- audit_log ------------------------------------------------
-- Append-only. Powers /api/admin/activity. Keep cheap to write.
CREATE TABLE IF NOT EXISTS audit_log (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id      INT UNSIGNED DEFAULT NULL,     -- nullable for system events
  action       VARCHAR(80) NOT NULL,           -- 'user.register', 'user.login', 'treatment.create', ...
  target_type  VARCHAR(40) DEFAULT NULL,       -- 'treatment', 'user', 'clip', ...
  target_id    VARCHAR(64) DEFAULT NULL,
  ip           VARCHAR(45) DEFAULT NULL,       -- IPv4 or IPv6
  user_agent   VARCHAR(300) DEFAULT NULL,
  metadata     JSON DEFAULT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_user (user_id),
  KEY idx_audit_action (action),
  KEY idx_audit_created (created_at),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- Optional: bootstrap an admin user. Replace the bcrypt hash with the
-- output of `node -e "console.log(require('bcryptjs').hashSync('YOUR_PW',12))"`.
-- Leave commented in production; uncomment + edit for first deploy.
-- ============================================================
-- INSERT INTO users (email, password_hash, name, role_admin, tier, email_verified_at)
-- VALUES ('admin@patina.local', '$2a$12$REPLACE_ME_WITH_BCRYPT_HASH', 'Patina Admin', 1, 'studio', NOW());