"""Add last_modified_date to vulnerabilities (NVD lastModified)

Revision ID: 029
Revises: 028
Create Date: 2026-06-03 10:00:00.000000

Tester feedback: "Newly Published" widget sorted wrong even in VIEW ALL.
Root cause — published_date was NEVER populated by any ingest path
(Nessus/Wazuh import only set detected_at), so the column was all-NULL
and the nulls-last sort produced arbitrary order.

Fix is two-part:
  1) Backfill published_date from the NVD CVE API (enrichment_service).
  2) Also persist the CVE's lastModified date so the UI can show
     "published vs updated" — the tester explicitly wanted both
     ("Die Infos aus den CVEs published date und updated date").

This migration only adds the new column; published_date already exists.
Idempotent — ADD COLUMN IF NOT EXISTS.
"""
from alembic import op


revision = "029"
down_revision = "028"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        ALTER TABLE vulnerabilities
            ADD COLUMN IF NOT EXISTS last_modified_date TIMESTAMP;
    """)
    op.execute("""
        CREATE INDEX IF NOT EXISTS ix_vulnerabilities_published_date
            ON vulnerabilities (published_date);
    """)


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_vulnerabilities_published_date;")
    op.execute("""
        ALTER TABLE vulnerabilities
            DROP COLUMN IF EXISTS last_modified_date;
    """)
