"""false positive reports + ingest blacklist

Revision ID: 0006
Revises: 0005
Create Date: 2026-04-17

When a user/admin flags a process as wrongly matched (e.g. the strict matcher
let a homónimo slip through), the row is moved to this table and deleted from
the processes table. dedup_hash is kept UNIQUE so the ingest path can skip re-inserting
the exact same (company,process,tribunal,date) combo on the next scrape.
"""
from alembic import op


revision = "0006"
down_revision = "0005"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE false_positive_reports (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          dedup_hash CHAR(64) NOT NULL UNIQUE,
          company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          company_legal_name TEXT NOT NULL,
          process_number TEXT NOT NULL,
          tribunal TEXT NOT NULL,
          source TEXT NOT NULL,
          parties_snapshot JSONB,
          raw_snapshot JSONB,
          flagged_by UUID REFERENCES users(id) ON DELETE SET NULL,
          flagged_by_email TEXT,
          flagged_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          reason TEXT
        )
    """)
    op.execute("CREATE INDEX ix_fpr_company ON false_positive_reports(company_id)")
    op.execute("CREATE INDEX ix_fpr_flagged_at ON false_positive_reports(flagged_at DESC)")


def downgrade() -> None:
    op.execute("DROP TABLE IF EXISTS false_positive_reports CASCADE")