"""DDL for synapse_targets — per-user secrets of Synapse s2s delivery targets.

Each user registers their own Synapse target (create it in the Synapse admin
panel / MCP `target_create` with the given token_ref, pointing at this navi's
POST /webhooks/synapse) and pastes the secret here. The gateway collects the
secrets of all active targets to verify incoming deliveries.
"""

_DDL = """
CREATE TABLE IF NOT EXISTS synapse_targets (
    id          SERIAL PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES navi_users(id) ON DELETE CASCADE,
    token_ref   TEXT NOT NULL,
    secret_enc  TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    revoked_at  TIMESTAMPTZ
);

CREATE INDEX IF NOT EXISTS idx_synapse_targets_user_id ON synapse_targets (user_id);

CREATE TABLE IF NOT EXISTS synapse_settings (
    user_id             TEXT PRIMARY KEY REFERENCES navi_users(id) ON DELETE CASCADE,
    reactions_enabled   BOOLEAN NOT NULL DEFAULT FALSE,
    push_target         TEXT NOT NULL DEFAULT 'app',       -- app | app_synapse | synapse
    completion_notify   TEXT NOT NULL DEFAULT 'important', -- always | important | never
    instructions        TEXT NOT NULL DEFAULT '',
    -- Routing contract, separate from `instructions`: which events go to which
    -- profile, and what may be treated as the same conversation. Read by the
    -- dispatcher meta-pass only; never enters the reaction session.
    dispatcher_instructions TEXT NOT NULL DEFAULT '',
    -- Idle window for continuing a reaction thread. 0 disables continuation:
    -- every event starts a fresh session, as it did before this column existed.
    reaction_session_ttl_minutes INTEGER NOT NULL DEFAULT 1440,
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Version history of the instruction documents: the agent can edit them
-- (self-improvement), the history keeps that auditable. `doc` says which
-- document the row belongs to — the two are versioned independently.
CREATE TABLE IF NOT EXISTS synapse_instruction_versions (
    id          SERIAL PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES navi_users(id) ON DELETE CASCADE,
    doc         TEXT NOT NULL DEFAULT 'reaction',         -- 'reaction' | 'dispatcher'
    content     TEXT NOT NULL,
    edited_by   TEXT NOT NULL,                             -- 'user' | 'navi'
    reason      TEXT,
    created_at  TIMESTAMPTZ NOT NULL
);
"""

# Rows written before these columns existed keep their meaning: an absent `doc`
# is a reaction-instructions version, an absent settings value is "no routing
# document yet". The old index is dropped because its name is taken by an
# (user_id, created_at) shape that the new three-column index supersedes.
#
# The `doc` index is created here rather than in _DDL on purpose: on a database
# that already has the table, _DDL runs *before* this migration, and an index
# over a column that does not exist yet fails the whole batch.
_MIGRATE = """
ALTER TABLE synapse_settings ADD COLUMN IF NOT EXISTS dispatcher_instructions TEXT NOT NULL DEFAULT '';
ALTER TABLE synapse_settings ADD COLUMN IF NOT EXISTS reaction_session_ttl_minutes INTEGER NOT NULL DEFAULT 1440;
ALTER TABLE synapse_instruction_versions ADD COLUMN IF NOT EXISTS doc TEXT NOT NULL DEFAULT 'reaction';
DROP INDEX IF EXISTS idx_synapse_instruction_versions_user;
CREATE INDEX IF NOT EXISTS idx_synapse_instruction_versions_user_doc ON synapse_instruction_versions (user_id, doc, created_at);
"""


async def ensure_tables(pool) -> None:
    await pool.execute(_DDL)
    await pool.execute(_MIGRATE)