-- Phase 5 migration: automation tools + filter performance.
-- Matches PROJECT.md Appendix A. Phase 6 adds notification_rules; do not
-- pre-create that here.

CREATE TABLE IF NOT EXISTS automation_tools (
    id                    TEXT PRIMARY KEY,
    name                  TEXT NOT NULL,
    type                  TEXT NOT NULL,      -- "autobrr" (Phase 5), "tqm"/"crossseed" later
    base_url              TEXT NOT NULL,
    config_json           TEXT NOT NULL,
    webhook_token         TEXT,               -- secrets-store key when set; FK to webhook_endpoints.id
    enabled               INTEGER NOT NULL DEFAULT 1,
    poll_interval_seconds INTEGER NOT NULL DEFAULT 60,
    created_at            INTEGER NOT NULL,
    updated_at            INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS filter_performance (
    id                    INTEGER PRIMARY KEY AUTOINCREMENT,
    automation_tool_id    TEXT NOT NULL REFERENCES automation_tools(id),
    filter_external_id    TEXT NOT NULL,
    filter_name           TEXT NOT NULL,
    variant_id            TEXT,               -- Phase 13 bandit variants; NULL in Phase 5
    info_hash             TEXT,               -- NULL until linked
    release_name          TEXT,
    grabbed_at            INTEGER NOT NULL,
    final_uploaded_bytes  INTEGER,            -- updated by the scorer
    final_ratio           REAL,
    last_measured_at      INTEGER
);
CREATE INDEX IF NOT EXISTS idx_filter_performance_filter
    ON filter_performance(automation_tool_id, filter_external_id);
CREATE INDEX IF NOT EXISTS idx_filter_performance_variant
    ON filter_performance(automation_tool_id, filter_external_id, variant_id);
CREATE INDEX IF NOT EXISTS idx_filter_performance_unlinked
    ON filter_performance(grabbed_at) WHERE info_hash IS NULL;
CREATE INDEX IF NOT EXISTS idx_filter_performance_hash
    ON filter_performance(info_hash) WHERE info_hash IS NOT NULL;
