-- Sprout — Declarative Postgres schema (managed by pgschema)
--
-- This file represents the desired state of the database schema.
-- Use `pgschema apply --file schema/schema.sql` to bring the database up to date.
--
-- All "enum" columns use TEXT + CHECK constraints instead of CREATE TYPE so that
-- sqlx runtime queries can decode them as plain String without custom type registration.

-- ── Channels ──────────────────────────────────────────────────────────────────

CREATE TABLE channels (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(255) NOT NULL,
    channel_type    TEXT NOT NULL DEFAULT 'stream',
    visibility      TEXT NOT NULL DEFAULT 'open',
    description     TEXT,
    canvas          TEXT,
    created_by      BYTEA NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    archived_at     TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ,
    nip29_group_id  VARCHAR(255) UNIQUE,
    topic_required  BOOLEAN NOT NULL DEFAULT FALSE,
    max_members     INT,
    topic           TEXT,
    topic_set_by    BYTEA,
    topic_set_at    TIMESTAMPTZ,
    purpose         TEXT,
    purpose_set_by  BYTEA,
    purpose_set_at  TIMESTAMPTZ,
    participant_hash BYTEA,
    CONSTRAINT chk_channel_type CHECK (channel_type IN ('stream', 'forum', 'dm', 'workflow')),
    CONSTRAINT chk_channel_visibility CHECK (visibility IN ('open', 'private'))
);

CREATE INDEX idx_channels_type ON channels (channel_type);
CREATE INDEX idx_channels_visibility ON channels (visibility);
CREATE INDEX idx_channels_created_by ON channels (created_by);
CREATE UNIQUE INDEX idx_channels_dm_hash ON channels (participant_hash);

-- ── Channel members ───────────────────────────────────────────────────────────

CREATE TABLE channel_members (
    channel_id  UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
    pubkey      BYTEA NOT NULL,
    role        TEXT NOT NULL DEFAULT 'member',
    joined_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    invited_by  BYTEA,
    removed_at  TIMESTAMPTZ,
    removed_by  BYTEA,
    PRIMARY KEY (channel_id, pubkey),
    CONSTRAINT chk_member_role CHECK (role IN ('owner', 'admin', 'member', 'guest', 'bot'))
);

-- ── Users ─────────────────────────────────────────────────────────────────────

CREATE TABLE users (
    pubkey              BYTEA PRIMARY KEY,
    nip05_handle        VARCHAR(255) UNIQUE,
    display_name        VARCHAR(255),
    avatar_url          TEXT,
    about               TEXT,
    agent_type          VARCHAR(255),
    capabilities        JSONB,
    okta_user_id        VARCHAR(255) UNIQUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deactivated_at      TIMESTAMPTZ,
    metadata_event_id   BYTEA,
    agent_owner_pubkey  BYTEA REFERENCES users(pubkey) ON DELETE SET NULL,
    channel_add_policy  TEXT NOT NULL DEFAULT 'anyone',
    CONSTRAINT chk_users_pubkey_len CHECK (LENGTH(pubkey) = 32),
    CONSTRAINT chk_channel_add_policy CHECK (channel_add_policy IN ('anyone', 'owner_only', 'nobody'))
);

-- ── Events (partitioned by month on created_at) ──────────────────────────────

CREATE TABLE events (
    id          BYTEA NOT NULL,
    pubkey      BYTEA NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    kind        INT NOT NULL,
    tags        JSONB NOT NULL,
    content     TEXT NOT NULL,
    sig         BYTEA NOT NULL,
    received_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    channel_id  UUID,
    deleted_at  TIMESTAMPTZ,
    PRIMARY KEY (created_at, id)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_p2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_p2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
CREATE TABLE events_p2026_03 PARTITION OF events
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE events_p2026_04 PARTITION OF events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE events_p2026_05 PARTITION OF events
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE events_p2026_06 PARTITION OF events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_p_future PARTITION OF events
    FOR VALUES FROM ('2026-07-01') TO (MAXVALUE);

CREATE INDEX idx_events_pubkey_kind_created ON events (pubkey, kind, created_at);
CREATE INDEX idx_events_channel_created ON events (channel_id, created_at);
CREATE INDEX idx_events_kind_created ON events (kind, created_at);
CREATE INDEX idx_events_id ON events (id);
CREATE INDEX idx_events_deleted ON events (deleted_at);
CREATE INDEX idx_events_addressable ON events (kind, pubkey, channel_id, deleted_at);

-- ── Event mentions ────────────────────────────────────────────────────────────

CREATE TABLE event_mentions (
    pubkey_hex          VARCHAR(64) NOT NULL,
    event_id            BYTEA NOT NULL,
    event_created_at    TIMESTAMPTZ NOT NULL,
    channel_id          UUID,
    event_kind          INT,
    PRIMARY KEY (pubkey_hex, event_id)
);

CREATE INDEX idx_event_mentions_pubkey_created ON event_mentions (pubkey_hex, event_created_at DESC);
CREATE INDEX idx_event_mentions_pubkey_kind_created ON event_mentions (pubkey_hex, event_kind, event_created_at DESC);

-- ── Subscriptions ─────────────────────────────────────────────────────────────

CREATE TABLE subscriptions (
    id                  VARCHAR(255) PRIMARY KEY,
    owner_pubkey        BYTEA NOT NULL REFERENCES users(pubkey),
    filter_kinds        JSONB,
    filter_authors      JSONB,
    filter_channel_ids  JSONB,
    filter_since        TIMESTAMPTZ,
    filter_until        TIMESTAMPTZ,
    delivery_method     TEXT NOT NULL DEFAULT 'webhook',
    delivery_url        TEXT,
    status              TEXT NOT NULL DEFAULT 'active',
    pause_reason        TEXT,
    delivered_count     BIGINT NOT NULL DEFAULT 0,
    error_count         BIGINT NOT NULL DEFAULT 0,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_delivery_method CHECK (delivery_method IN ('webhook', 'websocket')),
    CONSTRAINT chk_subscription_status CHECK (status IN ('active', 'paused', 'deleted')),
    CONSTRAINT chk_pause_reason CHECK (pause_reason IS NULL OR pause_reason IN ('user', 'system', 'rate_limit'))
);

-- ── Delivery log (partitioned by month on delivered_at) ──────────────────────

CREATE TABLE delivery_log (
    id              BIGINT GENERATED ALWAYS AS IDENTITY,
    subscription_id VARCHAR(255),
    event_id        BYTEA,
    method          TEXT,
    delivered_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    success         BOOLEAN,
    http_status     INT,
    error_message   TEXT,
    attempt_number  INT DEFAULT 1,
    PRIMARY KEY (delivered_at, id)
) PARTITION BY RANGE (delivered_at);

CREATE TABLE delivery_log_p2026_03 PARTITION OF delivery_log
    FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');
CREATE TABLE delivery_log_p2026_04 PARTITION OF delivery_log
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
CREATE TABLE delivery_log_p2026_05 PARTITION OF delivery_log
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE delivery_log_p2026_06 PARTITION OF delivery_log
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE delivery_log_p_future PARTITION OF delivery_log
    FOR VALUES FROM ('2026-07-01') TO (MAXVALUE);

-- ── Workflows ─────────────────────────────────────────────────────────────────

CREATE TABLE workflows (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(255) NOT NULL,
    owner_pubkey    BYTEA NOT NULL REFERENCES users(pubkey),
    channel_id      UUID REFERENCES channels(id),
    definition      JSONB NOT NULL,
    definition_hash BYTEA NOT NULL,
    status          TEXT NOT NULL DEFAULT 'active',
    enabled         BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_workflow_status CHECK (status IN ('active', 'disabled', 'archived'))
);

CREATE INDEX idx_workflows_channel_active ON workflows (channel_id, status, enabled);

-- ── Workflow runs ─────────────────────────────────────────────────────────────

CREATE TABLE workflow_runs (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    workflow_id         UUID NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    status              TEXT NOT NULL DEFAULT 'pending',
    trigger_event_id    BYTEA,
    current_step        INT NOT NULL DEFAULT 0,
    execution_trace     JSONB NOT NULL DEFAULT '[]',
    trigger_context     JSONB,
    started_at          TIMESTAMPTZ,
    completed_at        TIMESTAMPTZ,
    error_message       TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_run_status CHECK (status IN ('pending', 'running', 'waiting_approval', 'completed', 'failed', 'cancelled'))
);

CREATE INDEX idx_workflow_runs_workflow ON workflow_runs (workflow_id);
CREATE INDEX idx_workflow_runs_status ON workflow_runs (status);

-- ── Workflow approvals ────────────────────────────────────────────────────────

CREATE TABLE workflow_approvals (
    token           BYTEA PRIMARY KEY,
    workflow_id     UUID NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    run_id          UUID NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
    step_id         VARCHAR(64) NOT NULL,
    step_index      INT NOT NULL,
    approver_spec   TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'pending',
    approver_pubkey BYTEA,
    note            TEXT,
    granted_at      TIMESTAMPTZ,
    denied_at       TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_approval_status CHECK (status IN ('pending', 'granted', 'denied', 'expired'))
);

CREATE INDEX idx_workflow_approvals_workflow ON workflow_approvals (workflow_id);
CREATE INDEX idx_workflow_approvals_run ON workflow_approvals (run_id);
CREATE INDEX idx_workflow_approvals_status ON workflow_approvals (status);

-- ── API tokens ────────────────────────────────────────────────────────────────

CREATE TABLE api_tokens (
    id                  BYTEA PRIMARY KEY,
    token_hash          BYTEA NOT NULL UNIQUE,
    owner_pubkey        BYTEA NOT NULL REFERENCES users(pubkey),
    name                VARCHAR(255) NOT NULL,
    scopes              JSONB NOT NULL,
    channel_ids         JSONB,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at          TIMESTAMPTZ,
    last_used_at        TIMESTAMPTZ,
    revoked_at          TIMESTAMPTZ,
    revoked_by          BYTEA,
    created_by_self_mint BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT chk_api_tokens_hash_len CHECK (LENGTH(token_hash) = 32)
);

-- ── Rate limit violations ─────────────────────────────────────────────────────

CREATE TABLE rate_limit_violations (
    id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pubkey          BYTEA,
    violation_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    limit_type      VARCHAR(64),
    limit_value     INT,
    actual_value    INT,
    action_taken    VARCHAR(64)
);

-- ── Thread metadata ───────────────────────────────────────────────────────────

CREATE TABLE thread_metadata (
    event_created_at        TIMESTAMPTZ NOT NULL,
    event_id                BYTEA NOT NULL,
    channel_id              UUID NOT NULL REFERENCES channels(id),
    parent_event_id         BYTEA,
    parent_event_created_at TIMESTAMPTZ,
    root_event_id           BYTEA,
    root_event_created_at   TIMESTAMPTZ,
    depth                   INT NOT NULL DEFAULT 0,
    reply_count             INT NOT NULL DEFAULT 0,
    descendant_count        INT NOT NULL DEFAULT 0,
    last_reply_at           TIMESTAMPTZ,
    broadcast               BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (event_created_at, event_id)
);

CREATE INDEX idx_thread_metadata_parent ON thread_metadata (parent_event_id);
CREATE INDEX idx_thread_metadata_root ON thread_metadata (root_event_id);
CREATE INDEX idx_thread_metadata_channel_depth ON thread_metadata (channel_id, depth, event_created_at);
CREATE INDEX idx_thread_metadata_event_id ON thread_metadata (event_id);

-- ── Reactions ─────────────────────────────────────────────────────────────────

CREATE TABLE reactions (
    event_created_at    TIMESTAMPTZ NOT NULL,
    event_id            BYTEA NOT NULL,
    pubkey              BYTEA NOT NULL,
    emoji               VARCHAR(64) NOT NULL,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    removed_at          TIMESTAMPTZ,
    reaction_event_id   BYTEA,
    PRIMARY KEY (event_created_at, event_id, pubkey, emoji)
);

CREATE INDEX idx_reactions_event ON reactions (event_id, event_created_at);
CREATE INDEX idx_reactions_pubkey ON reactions (pubkey);
CREATE UNIQUE INDEX idx_reactions_source_event ON reactions (reaction_event_id);

-- ── Pubkey allowlist ──────────────────────────────────────────────────────────

CREATE TABLE pubkey_allowlist (
    pubkey      BYTEA PRIMARY KEY,
    added_by    BYTEA,
    added_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    note        TEXT
);