-- Ceri.us identity integration -- additive schema change.
--
-- Run this ONCE against the existing airbridg_photo_slots database, after
-- server/sql/schema.sql. Does not touch `instances`, `realtime_events`, or
-- `app_state`; only adds two new tables and one new nullable column (plus
-- its indexes) on `instance_members`. See server/CERI_INTEGRATION_AUDIT.md
-- for the full design rationale and the approved decisions this implements.
--
-- Why real tables instead of the generic `app_state` JSON bucket schema.sql
-- uses for most non-relational state: `instance_members.airbridge_identity_id`
-- needs to be a genuine, database-enforced foreign key with a real
-- uniqueness constraint (one Ceri identity may hold at most one roster row
-- per wedding) -- that requires the identities table it references to
-- actually exist as a real table, the same reasoning schema.sql already
-- used for `instances`/`instance_members`/`realtime_events`.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- One row per distinct Ceri identity that has ever completed a launch
-- exchange with this app. `airbridge_subject` is the opaque, pairwise,
-- appId="weddings"-scoped subject Ceri returns from `/launch/verify` --
-- stable across every wedding a person touches, never Ceri's real numeric
-- user id (see CeriIdentityService.php / CERI_INTEGRATION_AUDIT.md section
-- 10.5 and Codex's confirmed "one stable subject per Ceri identity across
-- all Wedding instances" architecture decision).
--
-- `id` is a CHAR(36) UUID, generated in PHP, NOT an auto-increment integer
-- -- deliberately matching instance_members.member_id/realtime_events.event_id
-- elsewhere in this schema. Every table in this app is read/written through
-- Database::read()/write(), which round-trips the ENTIRE app state as one
-- in-memory PHP array per request (see MysqliDatabase.php) -- an
-- auto-increment PK's value isn't known until after the INSERT actually
-- runs, which doesn't fit that whole-array read-mutate-write cycle (there's
-- no "read back the id I just got" step in it). A PHP-generated UUID sidesteps
-- that entirely: CeriSessionService can mint the id up front, use it
-- immediately to link an instance_members row in the very same request, and
-- the eventual write() just upserts whatever id it was given.
CREATE TABLE IF NOT EXISTS weddings_airbridge_identities (
  id                 CHAR(36)      NOT NULL,
  airbridge_subject  VARCHAR(255)  NOT NULL,
  created_at         DATETIME      NOT NULL,
  last_login_at      DATETIME      NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_airbridge_subject (airbridge_subject)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Weddings' own first-ever server-side session (see CERI_INTEGRATION_AUDIT.md
-- section 1: this app previously had zero PHP native sessions/cookies
-- anywhere -- every request was authenticated per-call via a bearer token).
-- Only the SHA-256 hash of the selector/CSRF token is ever stored, mirroring
-- ceri_sessions' own approach -- the raw cookie value never touches the
-- database.
CREATE TABLE IF NOT EXISTS weddings_sessions (
  selector_hash          CHAR(64)      NOT NULL,
  airbridge_identity_id  CHAR(36)      NOT NULL,
  instance_ref           VARCHAR(120)  NULL,
  role                   VARCHAR(16)   NOT NULL DEFAULT 'guest',
  csrf_hash              CHAR(64)      NOT NULL,
  idle_expires_at        DATETIME      NOT NULL,
  absolute_expires_at    DATETIME      NOT NULL,
  created_at             DATETIME      NOT NULL,
  revoked_at             DATETIME      NULL,
  PRIMARY KEY (selector_hash),
  KEY idx_sessions_identity (airbridge_identity_id),
  CONSTRAINT fk_sessions_identity FOREIGN KEY (airbridge_identity_id)
    REFERENCES weddings_airbridge_identities (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Links an existing wedding roster row to a Ceri identity. Nullable: most
-- historical/future members (invited by email/phone, no Ceri login yet)
-- simply have NULL here and keep working exactly as before -- MySQL unique
-- indexes treat every NULL as distinct, so any number of members with no
-- linked identity coexist fine. Only actually-linked rows are constrained
-- to at most one per (public_id, airbridge_identity_id) pair, per the
-- approved decision "one Ceri identity may hold at most one roster row per
-- wedding."
ALTER TABLE instance_members
  ADD COLUMN airbridge_identity_id CHAR(36) NULL AFTER access_token,
  ADD KEY idx_members_airbridge_identity (airbridge_identity_id),
  ADD UNIQUE KEY uniq_public_airbridge_identity (public_id, airbridge_identity_id),
  ADD CONSTRAINT fk_members_airbridge_identity FOREIGN KEY (airbridge_identity_id)
    REFERENCES weddings_airbridge_identities (id) ON DELETE SET NULL;

SET FOREIGN_KEY_CHECKS = 1;