CREATE SCHEMA IF NOT EXISTS athena;
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS athena.chat_rooms (
    id uuid PRIMARY KEY,
    organization_id text NOT NULL,
    kind text NOT NULL CHECK (kind IN ('dm', 'group', 'channel')),
    title text,
    created_by text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    archived_at timestamptz,
    last_message_id uuid,
    last_message_seq bigint NOT NULL DEFAULT 0,
    last_message_at timestamptz,
    version bigint NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_chat_rooms_org_recent_active
    ON athena.chat_rooms (organization_id, updated_at DESC)
    WHERE archived_at IS NULL;

CREATE TABLE IF NOT EXISTS athena.chat_room_members (
    room_id uuid NOT NULL REFERENCES athena.chat_rooms(id) ON DELETE CASCADE,
    user_id text NOT NULL,
    role text NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
    joined_at timestamptz NOT NULL DEFAULT now(),
    last_read_seq bigint NOT NULL DEFAULT 0,
    last_read_message_id uuid,
    muted boolean NOT NULL DEFAULT false,
    notification_mode text,
    hidden_at timestamptz,
    PRIMARY KEY (room_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_chat_room_members_user_room
    ON athena.chat_room_members (user_id, room_id);

CREATE TABLE IF NOT EXISTS athena.chat_messages (
    id uuid PRIMARY KEY,
    room_id uuid NOT NULL REFERENCES athena.chat_rooms(id) ON DELETE CASCADE,
    room_seq bigint NOT NULL,
    sender_id text NOT NULL,
    client_message_id text,
    body_text text,
    body_json jsonb,
    reply_to_message_id uuid,
    created_at timestamptz NOT NULL DEFAULT now(),
    edited_at timestamptz,
    deleted_at timestamptz,
    metadata_json jsonb,
    CONSTRAINT chat_messages_room_seq_unique UNIQUE (room_id, room_seq),
    CONSTRAINT chat_messages_idempotency_unique UNIQUE (room_id, sender_id, client_message_id)
);

CREATE INDEX IF NOT EXISTS idx_chat_messages_room_seq_desc
    ON athena.chat_messages (room_id, room_seq DESC);

CREATE INDEX IF NOT EXISTS idx_chat_messages_room_created_desc
    ON athena.chat_messages (room_id, created_at DESC);

CREATE TABLE IF NOT EXISTS athena.chat_message_attachments (
    message_id uuid NOT NULL REFERENCES athena.chat_messages(id) ON DELETE CASCADE,
    file_id text NOT NULL REFERENCES athena.files(id) ON DELETE RESTRICT,
    ordinal integer NOT NULL,
    PRIMARY KEY (message_id, ordinal)
);

CREATE INDEX IF NOT EXISTS idx_chat_message_attachments_file
    ON athena.chat_message_attachments (file_id);

CREATE TABLE IF NOT EXISTS athena.chat_message_reactions (
    message_id uuid NOT NULL REFERENCES athena.chat_messages(id) ON DELETE CASCADE,
    user_id text NOT NULL,
    emoji text NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (message_id, user_id, emoji)
);

CREATE INDEX IF NOT EXISTS idx_chat_message_reactions_lookup
    ON athena.chat_message_reactions (message_id, emoji);

CREATE TABLE IF NOT EXISTS athena.chat_outbox (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    aggregate_type text NOT NULL CHECK (aggregate_type IN ('chat_message', 'chat_room', 'read_cursor')),
    aggregate_id text NOT NULL,
    room_id uuid NOT NULL REFERENCES athena.chat_rooms(id) ON DELETE CASCADE,
    room_seq bigint,
    event_type text NOT NULL,
    payload_json jsonb NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    published_at timestamptz,
    publish_attempts integer NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_chat_outbox_unpublished
    ON athena.chat_outbox (published_at, created_at)
    WHERE published_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_chat_outbox_room_seq
    ON athena.chat_outbox (room_id, room_seq);
