-- Photo Slots core -- MySQL schema (hybrid design)
--
-- ONE database, ONE set of tables, shared by every Photo Slots instance
-- across every Events subcategory (Wedding, Birthday, Bar/Bat Mitzvah,
-- Funeral, Baby Reveal, Graduation, ...) and every PlatformHost who owns one.
-- Running this file is a ONE-TIME setup step for the whole database --
-- it is NOT re-run per wedding, per birthday, or per PlatformHost. Creating a
-- new instance (a new wedding, a new birthday party, etc.) is just a new
-- row in `instances`, written by the app itself the same way it already
-- creates a new wedding today (WeddingService::create()) -- never a
-- manual script. See server/BACKEND_MYSQL_MIGRATION.md and the
-- category/subcategory columns below for how many different subcategories
-- coexist in these same four tables.
--
-- This is a *hybrid* schema, not a fully-normalized one, by deliberate
-- choice:
--
--   - `instances`, `instance_members`, and `realtime_events` are real,
--     indexed, foreign-keyed relational tables. These are exactly the
--     three things that benefit most from a real database: the
--     Host/SuperHost/PlatformHost roster + roles + access tokens, and the
--     realtime event log, which only ever grows and previously lived as
--     one never-pruned JSON array per instance inside a single flat file.
--
--   - Everything else an instance needs (theme colors/images, the 18 reel
--     symbols, reel strips, settings, animation builder config, calendar
--     events, ticker broadcast, welcome video, etc.) stays as ONE JSON
--     document per instance, in the `instances.config` column. This is
--     not a shortcut -- every one of ~15 PHP services in
--     server/src/Services was already written against a single
--     in-memory nested PHP array (via FileDatabase::read()/write()), and
--     every one of those services already works correctly today.
--     Decomposing every nested field into its own table would mean
--     rewriting the internals of all ~15 services to do targeted SQL
--     instead of whole-array reads/writes -- thousands of lines that
--     can't be verified without a live PHP+MySQL server to run them
--     against. Keeping this part as JSON means ZERO changes to any
--     existing service or controller.
--
--   - `app_state` is a generic key/value bucket (one row per remaining
--     top-level key the old JSON file had: guestSessions, spins,
--     replacementEntitlements, joinTokens, auditLog, etc.) -- mostly
--     short-lived workflow/session bookkeeping tied tightly to specific
--     services' internal logic. Preserved byte-for-byte in shape so the
--     services that own it keep working unmodified.
--
-- On the PHP application side, server/src/Services/WeddingService.php
-- and the rest of the "wedding_reels" code are still wedding-specific in
-- their business logic and default copy today (e.g. default theme title
-- "Made in Heaven", the couple-names field, etc.) -- this schema makes
-- the DATABASE ready to hold every Events subcategory in one place with
-- zero extra script-running, but turning the application layer itself
-- into a true reusable "Photo Slots core + pluggable subcategory" engine
-- (so creating a Birthday or Bar/Bat Mitzvah instance picks different
-- default copy/theme than a Wedding does) is a separate, larger next
-- step -- ask when you're ready to tackle it.
--
-- Run this once against an empty database before running
-- server/scripts/migrate_json_to_mysql.php. Requires MySQL 5.7+ or
-- MariaDB 10.2+ (both support the JSON column type used below).

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS instances (
  public_id      VARCHAR(191)   NOT NULL PRIMARY KEY,
  instance_uuid  CHAR(36)       NOT NULL,
  join_slug      VARCHAR(191)   NOT NULL,
  -- Which core app this instance runs -- 'photo_slots' for everything
  -- built so far. Other future Airbridge core apps (unrelated to Photo
  -- Slots) can share this same instances/instance_members/
  -- realtime_events/app_state infrastructure by using their own
  -- core_app value and their own JSON shape inside `config` -- nothing
  -- about the roster/roles/realtime-event machinery is Photo-Slots
  -- specific.
  core_app       VARCHAR(64)    NOT NULL DEFAULT 'photo_slots',
  -- Top-level category (e.g. 'EVENT') and its subcategory (e.g.
  -- 'WEDDING', 'BIRTHDAY', 'BAR_BAT_MITZVAH', 'FUNERAL', 'BABY_REVEAL',
  -- 'GRADUATION', ...) -- matches the APP_TYPES map already defined
  -- client-side in local-universe.js. A Wedding, a Birthday, and a
  -- Funeral all using the Photo Slots core sit in this exact same table,
  -- distinguished only by these two columns plus whatever's inside
  -- `config` -- there is no per-subcategory table or per-instance
  -- database to create.
  category       VARCHAR(64)    NOT NULL DEFAULT 'EVENT',
  subcategory    VARCHAR(64)    NOT NULL DEFAULT 'WEDDING',
  status         VARCHAR(32)    NOT NULL DEFAULT 'DRAFT',
  state_version  INT UNSIGNED   NOT NULL DEFAULT 1,
  -- Everything else: theme, symbols, reelStrips, settings, animationConfig,
  -- animationObjects, calendarEvents, permissions, welcomeVideo,
  -- tickerBroadcast, coupleNames, eyebrowText, title, timezone, etc.
  -- (every field WeddingService::create() sets, minus id/publicId/
  -- joinSlug/status/stateVersion/createdAt/updatedAt/members, which all
  -- have their own columns/table).
  config         JSON           NOT NULL,
  created_at     DATETIME       NOT NULL,
  updated_at     DATETIME       NOT NULL,
  UNIQUE KEY uniq_join_slug (join_slug),
  KEY idx_category_subcategory (category, subcategory),
  KEY idx_core_app (core_app)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS instance_members (
  member_id             CHAR(36)      NOT NULL PRIMARY KEY,
  public_id             VARCHAR(191)  NOT NULL,
  name                  VARCHAR(191)  NOT NULL,
  email                 VARCHAR(191)  NULL,
  phone                 VARCHAR(64)   NULL,
  role                  ENUM('godhost','superhost','host','member','pending','guest') NOT NULL DEFAULT 'guest',
  is_owner              TINYINT(1)    NOT NULL DEFAULT 0,
  access_token          CHAR(36)      NOT NULL,
  status                VARCHAR(32)   NOT NULL DEFAULT 'active',
  invited_by_member_id  CHAR(36)      NULL,
  created_at            DATETIME      NOT NULL,
  updated_at            DATETIME      NOT NULL,
  UNIQUE KEY uniq_access_token (access_token),
  KEY idx_public_id (public_id),
  CONSTRAINT fk_members_instance FOREIGN KEY (public_id)
    REFERENCES instances (public_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS realtime_events (
  id                    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  public_id             VARCHAR(191)  NOT NULL,
  sequence_no           INT UNSIGNED  NOT NULL,
  event_id              CHAR(36)      NOT NULL,
  instance_uuid         CHAR(36)      NOT NULL,
  type                  VARCHAR(64)   NOT NULL,
  occurred_at           DATETIME      NOT NULL,
  payload               JSON          NOT NULL,
  state_version         INT UNSIGNED  NOT NULL,
  authoritative_source  VARCHAR(64)   NOT NULL DEFAULT 'php-mysql',
  UNIQUE KEY uniq_public_sequence (public_id, sequence_no),
  KEY idx_public_sequence (public_id, sequence_no),
  CONSTRAINT fk_events_instance FOREIGN KEY (public_id)
    REFERENCES instances (public_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Generic key/value bucket -- see the file-level comment above. One row
-- per remaining top-level key from the old JSON file (guestSessions,
-- sessionCapabilities, presence, lastSpinAt, spins, spinsById,
-- replacementEntitlements and its by-spin/by-token/by-idempotency-key
-- indexes, replacementLocks, replacementUploads, joinTokens, joinAudit,
-- galleryMedia, occupantHistory, submissionArchive, finalSnapshots,
-- realtimeHealth, realtimeOutbox, privateRealtimeOutbox, auditLog).
-- Rows are created lazily on first write -- nothing needs to be
-- pre-seeded here.
CREATE TABLE IF NOT EXISTS app_state (
  state_key   VARCHAR(64)  NOT NULL PRIMARY KEY,
  data        JSON         NOT NULL,
  updated_at  DATETIME     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;