-- Phase 0 foundation migration.
-- Only the tables in scope for Phase 0 (per CLAUDE.md) are created here.
-- Subsequent phases add tables via additional numbered migration files.
-- Pragmas are applied by the runtime when the SQLite connection is opened
-- (see internal/db); they are NOT placed in migrations because they are
-- session-level settings, not schema.

CREATE TABLE IF NOT EXISTS secrets (
    key             TEXT PRIMARY KEY,
    encrypted_value BLOB NOT NULL,
    updated_at      INTEGER NOT NULL,
    rotation_due_at INTEGER
);

CREATE TABLE IF NOT EXISTS schema_migrations (
    version    INTEGER PRIMARY KEY,
    applied_at INTEGER NOT NULL,
    name       TEXT    NOT NULL
);

CREATE TABLE IF NOT EXISTS system_events (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp      INTEGER NOT NULL,
    level          TEXT    NOT NULL,
    component      TEXT    NOT NULL,
    message        TEXT    NOT NULL,
    context_json   TEXT,
    correlation_id TEXT
);
CREATE INDEX IF NOT EXISTS idx_system_events_time
    ON system_events(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_system_events_level_time
    ON system_events(level, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_system_events_correlation
    ON system_events(correlation_id) WHERE correlation_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS audit_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    timestamp   INTEGER NOT NULL,
    actor       TEXT    NOT NULL,
    action      TEXT    NOT NULL,
    target_type TEXT,
    target_id   TEXT,
    details_json TEXT,
    ip_address  TEXT,
    user_agent  TEXT
);
CREATE INDEX IF NOT EXISTS idx_audit_log_time ON audit_log(timestamp DESC);
