-- chat_2026 schema draft — Option 3 chat-service persistence layer
-- Author: Z, 2026-06-02
-- Review gate: Phase 1 concurrence (E + B + S + Curator)
--
-- Design anchors:
--   - State vs event (Echo doctrine): messages = events (append-only history),
--     rooms + members + invitations = state (current truth)
--   - Lifecycle owners (Echo): every mutable state has named owner + tracked transitions
--   - Cross-DB FK soft-discipline (Echo): identity columns reference citadel users by
--     email at the app layer; no engine-level FK across DBs (MariaDB doesn't enforce
--     cross-DB FK anyway). Within chat_2026, FKs are explicit (room_id integrity).
--   - Multi-device/multi-session: server-side truth. Browser localStorage stops being
--     load-bearing for "who's in the room" / "what was said". Server is canonical.
--   - Room semantics (Ray's directive): main = at-will join, private = invite-required.
--     Schema enforces via room.type + invitation flow.

CREATE DATABASE IF NOT EXISTS chat_2026 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE chat_2026;

-- ============================================================================
-- chat_rooms — STATE: rooms (main + private + DM)
-- ============================================================================
-- Lifecycle owner: created_by (room creator). Archive is soft (archived_at);
-- never hard-delete a room because messages reference it.

CREATE TABLE chat_rooms (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug            VARCHAR(64)  NOT NULL,
    name            VARCHAR(128) NOT NULL,
    type            ENUM('main','private','dm') NOT NULL,
    description     TEXT         NULL,
    created_by      VARCHAR(255) NOT NULL,           -- soft-ref to citadel identity
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    archived_at     DATETIME     NULL,               -- lifecycle: soft-archive
    archived_by     VARCHAR(255) NULL,               -- lifecycle-owner for archive transition (per Bollux v0 review)
    PRIMARY KEY (id),
    UNIQUE KEY uk_slug (slug),
    KEY idx_type (type),
    KEY idx_archived (archived_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- App-layer enforcement (not schema): for type='dm' rows, chat_room_members
-- must have exactly 2 entries. DM-room creation flow (per Solace v0 review):
-- first sendDm() creates the room with slug='dm-<lo>-<hi>' (sorted identities)
-- + inserts both identities as members. chat-api.php enforces +blocks
-- third-party adds.

-- ============================================================================
-- chat_room_members — STATE: who is in what room, in what capacity
-- ============================================================================
-- Lifecycle owner: the member (identity) for their own join/leave; room owner
-- for ban + role changes. Transitions:
--   active  -> left      (user-initiated leave)
--   active  -> banned    (owner-initiated)
--   left    -> active    (rejoin a public room or accept invitation)
-- This table holds REALIZED membership only. Pending invitations live in
-- chat_invitations until accept (locked in v1 review by E + B + S — keeps each
-- table single-state-machine, prevents compound-status interpretation in
-- member-list queries). On accept: app inserts a row here with status=active.

CREATE TABLE chat_room_members (
    room_id         INT UNSIGNED NOT NULL,
    identity        VARCHAR(255) NOT NULL,           -- soft-ref to citadel
    role            ENUM('owner','admin','member','observer') NOT NULL DEFAULT 'member',
    status          ENUM('active','left','banned') NOT NULL DEFAULT 'active',
    joined_at       DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    left_at         DATETIME     NULL,
    last_read_at    DATETIME     NULL,               -- for unread-count rendering
    PRIMARY KEY (room_id, identity),
    KEY idx_identity (identity),
    KEY idx_status (status),
    CONSTRAINT fk_member_room FOREIGN KEY (room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- chat_messages — EVENTS: append-only message stream per room
-- ============================================================================
-- Lifecycle owner: from_identity (the author) for edit + soft-delete; admins
-- can soft-delete via app-layer authority. Never hard-delete; history is
-- canonical even if redacted (deleted_at + body kept for audit).
--
-- metadata JSON column carries flexible extensions: attachments, reactions,
-- reply-to thread parent, mention list, etc. Schema-rigid only on the core
-- (id, room_id, from, body, created_at).
--
-- Soft-delete contract (per Curator v1 handshake): body stays NOT NULL in
-- the schema; deletion is a marker (deleted_at), the body is preserved for
-- audit/un-delete. chat-api redacts in the GET response (returns body=null
-- + deleted_at set + UI renders "this message was deleted"). Keep-in-DB +
-- redact-on-read keeps canonical truth, makes recovery possible, and lets
-- future admin roles see history without schema rework.

CREATE TABLE chat_messages (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id         INT UNSIGNED NOT NULL,
    from_identity   VARCHAR(255) NOT NULL,           -- soft-ref to citadel
    body            TEXT         NOT NULL,
    metadata        JSON         NULL,
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_at       DATETIME     NULL,
    deleted_at      DATETIME     NULL,               -- soft-delete
    PRIMARY KEY (id),
    KEY idx_room_created (room_id, created_at),
    KEY idx_from (from_identity),
    FULLTEXT KEY ft_body (body),
    CONSTRAINT fk_message_room FOREIGN KEY (room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- chat_invitations — STATE: pending/resolved invites to private rooms
-- ============================================================================
-- Lifecycle owner: invitee for accept/decline; inviter for revoke; system
-- (cron or on-read) for expire. One pending invite per (room, invitee).
-- After accept: a row is added to chat_room_members with status=active.
-- After decline/expire/revoke: status set + responded_at set; the invitation
-- record stays for audit. New invitations while status=pending already exists
-- are blocked at the APP LAYER (chat-api enforces single-pending via
-- SELECT-then-INSERT; no engine-level UNIQUE per E v0 review concern A — the
-- UNIQUE would have blocked the legit re-invite-after-decline cycle).

CREATE TABLE chat_invitations (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    room_id         INT UNSIGNED NOT NULL,
    inviter         VARCHAR(255) NOT NULL,
    invitee         VARCHAR(255) NOT NULL,
    status          ENUM('pending','accepted','declined','revoked','expired') NOT NULL DEFAULT 'pending',
    message         TEXT         NULL,               -- optional invite note
    created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    responded_at    DATETIME     NULL,
    expires_at      DATETIME     NULL,
    PRIMARY KEY (id),
    -- NOTE: no engine-level UNIQUE on (room_id, invitee, status) — that blocks
    -- the legit re-invite-after-decline cycle (per Echo v0 review concern A).
    -- App-layer enforces single-pending via SELECT-then-INSERT in chat-api.
    -- Audit-trail rows accumulate freely (declined + revoked + expired all kept).
    KEY idx_invitee_status (invitee, status),
    KEY idx_room (room_id),
    -- (room_id, invitee, status) covers the dedup-pending hot path queried on
    -- every invite (per Echo v1.5 audit catch #3).
    KEY idx_room_invitee_status (room_id, invitee, status),
    CONSTRAINT fk_invitation_room FOREIGN KEY (room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- Seed: the "main" room (always-exists, at-will-join, default destination)
-- ============================================================================
INSERT INTO chat_rooms (slug, name, type, description, created_by)
VALUES ('main', 'Main', 'main', 'Default fleet chat room - at-will join, all crew + Ray + family.', 'system');

-- ============================================================================
-- Cross-DB FK soft-discipline note:
--
-- identity columns (created_by, from_identity, inviter, invitee, archived_by,
-- etc.) are VARCHAR(255) and reference citadel_2025.users.email at the
-- application layer. MariaDB does not enforce FK across databases on the
-- same server; the app (chat-api) validates identity existence on write.
--
-- Agent-vs-user discriminator (per Echo v0 review concern C, canonical):
--   contains '@' → citadel user (email), validated against citadel_2025.users
--   no '@'      → agent identity, validated against citadel_2025.agent_secrets
-- The app-layer validator applies this single rule to every identity column.
-- 'system' is reserved for schema-seeded rows (the main room).
--
-- Cleanup ownership (per Echo v0 review concern B):
--   v0 ships with NO cleanup cron. Archived rooms, expired invitations, and
--   banned members accumulate. Manual admin UI is the future path; explicit
--   non-feature for v0 so the absence isn't ambiguous.
-- ============================================================================
