"""alvara_snapshots + alvara_companies — PSP security-company watchlist.

Each daily scrape of https://sigesponline.psp.pt creates a snapshot row
and upserts the per-NIPC state in alvara_companies. Transitions are
tracked via first_seen_at/last_seen_at/removed_at so the UI can surface
"new this week" and "removed" independently of any particular snapshot.
"""
from alembic import op

revision = "0018"
down_revision = "0017"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE alvara_snapshots (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            alvara_type TEXT NOT NULL CHECK (alvara_type IN ('A','B','C','D')),
            fetched_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            total_count INT NOT NULL,
            success BOOLEAN NOT NULL DEFAULT true,
            error_message TEXT,
            duration_ms INT
        );
        CREATE INDEX ix_alvara_snapshots_fetched ON alvara_snapshots(fetched_at DESC);

        CREATE TABLE alvara_companies (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            nipc TEXT NOT NULL,
            alvara_type TEXT NOT NULL CHECK (alvara_type IN ('A','B','C','D')),
            alvara_number TEXT,
            nome TEXT,
            morada TEXT,
            localidade TEXT,
            telefone TEXT,
            email TEXT,
            first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            removed_at TIMESTAMPTZ,
            UNIQUE (nipc, alvara_type)
        );
        CREATE INDEX ix_alvara_companies_first_seen
            ON alvara_companies(first_seen_at DESC);
        CREATE INDEX ix_alvara_companies_removed
            ON alvara_companies(removed_at DESC)
            WHERE removed_at IS NOT NULL;
        CREATE INDEX ix_alvara_companies_nipc ON alvara_companies(nipc);
        """
    )


def downgrade() -> None:
    op.execute(
        """
        DROP TABLE IF EXISTS alvara_companies;
        DROP TABLE IF EXISTS alvara_snapshots;
        """
    )