-- Outbound modules (integrations): kanban events → external systems.
--
-- A module is a configured subscription. The dispatcher (modules.go)
-- subscribes to the EventHub and, for each event whose type matches the
-- module's event_filter, calls the kind-specific handler with the
-- decrypted config.
--
-- Tokens / secrets are encrypted with AES-GCM at rest. The key is derived
-- from the KANBAN_MODULE_KEY environment variable (sha256 of the value).

CREATE TABLE IF NOT EXISTS modules (
    id            TEXT PRIMARY KEY,
    name          TEXT NOT NULL,
    kind          TEXT NOT NULL,           -- 'jira' | 'webhook' | …
    enabled       INTEGER NOT NULL DEFAULT 1,
    event_filter  TEXT NOT NULL,           -- comma-separated event types
    config_cipher BLOB NOT NULL,           -- AES-GCM ciphertext of JSON
    config_nonce  BLOB NOT NULL,
    created_at    TEXT NOT NULL,
    updated_at    TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS module_logs (
    id          TEXT PRIMARY KEY,
    module_id   TEXT NOT NULL,
    event_type  TEXT NOT NULL,
    card_id     TEXT,
    status      INTEGER,                   -- HTTP status or 0 if pre-flight
    duration_ms INTEGER,
    error       TEXT,
    created_at  TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_module_logs_module_created
    ON module_logs(module_id, created_at DESC);

-- jira_key: 1:1 link between a kanban card and its Jira issue. Empty
-- string when the card has not yet been synced to Jira.
ALTER TABLE cards ADD COLUMN jira_key TEXT NOT NULL DEFAULT '';

-- is_admin: gates /api/modules access and the Modulos menu item.
-- Bootstrap: egutierrez (the initial admin) is marked admin so the
-- feature is reachable on first deploy. Other users start as non-admin.
ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0;
UPDATE users SET is_admin = 1 WHERE username = 'egutierrez';