"""Add exploit-intel columns (Exploit-DB / PoC-in-GitHub / Metasploit)

Revision ID: 025
Revises: 024
Create Date: 2026-05-27 14:00:00.000000

Plan M foundation: explicit per-source exploit-availability columns
so the list endpoint can SQL-sort + filter, and the priority breakdown
can weight a CVE that has a real working exploit (not just KEV's
"observed in the wild" pointer).

Three nullable columns + denormalised counts. Future-proof for
PoC-in-GitHub + Metasploit (filled by separate enrichers later).

Idempotent — ADD COLUMN IF NOT EXISTS.
"""
from alembic import op


revision = "025"
down_revision = "024"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        ALTER TABLE vulnerabilities
            ADD COLUMN IF NOT EXISTS exploit_db_ids          TEXT,
            ADD COLUMN IF NOT EXISTS exploit_db_count        INTEGER DEFAULT 0,
            ADD COLUMN IF NOT EXISTS pocs_github_urls        TEXT,
            ADD COLUMN IF NOT EXISTS pocs_github_count       INTEGER DEFAULT 0,
            ADD COLUMN IF NOT EXISTS metasploit_modules      TEXT,
            ADD COLUMN IF NOT EXISTS metasploit_module_count INTEGER DEFAULT 0,
            ADD COLUMN IF NOT EXISTS exploit_intel_updated_at TIMESTAMP;
    """)
    # Indexes for filter/sort on "show me CVEs with public exploits"
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_vulnerabilities_exploit_db_count
            ON vulnerabilities (exploit_db_count) WHERE exploit_db_count > 0;
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_vulnerabilities_metasploit_count
            ON vulnerabilities (metasploit_module_count) WHERE metasploit_module_count > 0;
    """)


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_vulnerabilities_exploit_db_count;")
    op.execute("DROP INDEX IF EXISTS ix_vulnerabilities_metasploit_count;")
    op.execute("""
        ALTER TABLE vulnerabilities
            DROP COLUMN IF EXISTS exploit_db_ids,
            DROP COLUMN IF EXISTS exploit_db_count,
            DROP COLUMN IF EXISTS pocs_github_urls,
            DROP COLUMN IF EXISTS pocs_github_count,
            DROP COLUMN IF EXISTS metasploit_modules,
            DROP COLUMN IF EXISTS metasploit_module_count,
            DROP COLUMN IF EXISTS exploit_intel_updated_at;
    """)
