"""archive ALL DRE serie II parte E publications regardless of company match

Revision ID: 0014
Revises: 0013
Create Date: 2026-04-20

Currently we only store DRE items that matched a monitored company at ingest
time. A company added later starts from zero — no historical context. This
table stores the full raw stream of DRE Série II Parte E (registo comercial:
atos societários, nomeações, alterações de capital, liquidações). When a new
company is created, schedule_backfill_for_company retroactively runs its
distintivo against this archive for the last 12 months.

Only Parte E is captured: that's where competitive-intel signals live.
Other parts (laws, contracts, municipal acts) add noise and disk without
useful data for security-sector monitoring.
"""
from alembic import op


revision = "0014"
down_revision = "0013"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # Defensive — enables trigram index for ILIKE search. May be no-op if
    # already installed by an earlier migration.
    op.execute("CREATE EXTENSION IF NOT EXISTS pg_trgm")
    op.execute("""
        CREATE TABLE dre_raw (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          series TEXT NOT NULL DEFAULT 'II',
          part TEXT,
          title TEXT NOT NULL,
          summary TEXT,
          date DATE,
          type TEXT,
          source_url TEXT,
          raw_json JSONB,
          dedup_hash CHAR(64) NOT NULL UNIQUE,
          relevance TEXT NOT NULL DEFAULT 'low'
            CHECK (relevance IN ('high','medium','low')),
          change_kind TEXT,
          captured_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_dre_raw_date ON dre_raw(date DESC NULLS LAST)")
    op.execute("CREATE INDEX ix_dre_raw_title_trgm ON dre_raw USING gin (title gin_trgm_ops)")
    op.execute("CREATE INDEX ix_dre_raw_summary_trgm ON dre_raw USING gin (summary gin_trgm_ops)")
    op.execute(
        "CREATE INDEX ix_dre_raw_relevance ON dre_raw(relevance, date DESC) "
        "WHERE relevance IN ('high','medium')"
    )


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