-- Phase 6 migration: notification rules + delivery log.
-- The push_subscriptions table already exists (Phase 2). The VAPID keypair
-- lives in the age-encrypted secrets store, not in any database column.

CREATE TABLE IF NOT EXISTS notification_rules (
    id                   INTEGER PRIMARY KEY AUTOINCREMENT,
    name                 TEXT NOT NULL,
    enabled              INTEGER NOT NULL DEFAULT 1,
    trigger_type         TEXT NOT NULL,
    trigger_config_json  TEXT NOT NULL,
    channels_json        TEXT NOT NULL DEFAULT '["push"]',
    cooldown_seconds     INTEGER NOT NULL DEFAULT 3600,
    last_fired_at        INTEGER,
    source               TEXT NOT NULL DEFAULT 'yaml',
    created_at           INTEGER NOT NULL,
    updated_at           INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_notification_rules_trigger
    ON notification_rules(trigger_type, enabled);

CREATE TABLE IF NOT EXISTS notification_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    rule_id         INTEGER REFERENCES notification_rules(id),
    title           TEXT NOT NULL,
    body            TEXT,
    sent_at         INTEGER NOT NULL,
    delivered       INTEGER NOT NULL DEFAULT 0,
    channel         TEXT,
    subscription_id INTEGER REFERENCES push_subscriptions(id),
    error_message   TEXT
);
CREATE INDEX IF NOT EXISTS idx_notification_log_sent_at
    ON notification_log(sent_at DESC);
CREATE INDEX IF NOT EXISTS idx_notification_log_rule
    ON notification_log(rule_id, sent_at DESC);
