"""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 '',
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Version history of reaction instructions: the agent can edit its own
-- instructions (self-improvement), the history keeps that auditable.
CREATE TABLE IF NOT EXISTS synapse_instruction_versions (
    id          SERIAL PRIMARY KEY,
    user_id     TEXT NOT NULL REFERENCES navi_users(id) ON DELETE CASCADE,
    content     TEXT NOT NULL,
    edited_by   TEXT NOT NULL,                             -- 'user' | 'navi'
    reason      TEXT,
    created_at  TIMESTAMPTZ NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_synapse_instruction_versions_user ON synapse_instruction_versions (user_id, created_at);
"""


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