"""court_filings — arquivo nacional da Distribuição do CITIUS.

A varredura já descarregava o país inteiro e deitava fora o que não era nosso: o
`scrape_distribuicao_tribunal_sweep` faz a consulta **sem filtro de parte**,
pagina a lista completa de cada tribunal e guardava só as linhas que davam match
contra as empresas monitorizadas. O tráfego estava pago; o que faltava era onde
pôr o resto.

**A fonte é uma janela deslizante.** Sondado a 03/08/2026: 04/02/2026 devolve
1.256 processos, 13/01/2026 devolve zero. São ~180 dias, e o que se descarta hoje
não se recupera nunca — nem por nós nem por ninguém. Entre 16/10/2025, quando a
varredura começou, e Fevereiro de 2026 só existem as 552 linhas que deram match;
as outras ~90 mil desse período estão perdidas.

É a mesma arquitectura dos anúncios CIRE (migration 0020) e pela mesma razão: a
tabela nacional não tem dono, o cruzamento vive à parte e é **re-executável**.
Quando entra um cliente novo, ou quando a regra de correspondência melhora,
reaplica-se ao arquivo em vez de valer só daí para a frente. Hoje uma falha de
correspondência é silenciosa e definitiva.

A `processes` não muda: continua a ser a vista das monitorizadas, com
`company_id NOT NULL`, escrita pelo `upsert_process` — só que a partir daqui em
vez de directamente do scraper.

**As partes não são duplicadas no `raw`**, pela lição que o 0020 já tinha
aprendido com o `processes.raw`. Medido: a linha da distribuição pesa 692 bytes
com as partes lá dentro e são 6,0 partes por processo. A ~230 mil processos por
ano, guardá-las duas vezes custava ~80 MB/ano por nada.

Custo previsto do arquivo: 950 bytes por linha, 118 por parte, ~0,75 GB/ano.
"""
from alembic import op

revision = "0031"
down_revision = "0030"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE court_filings (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            process_number TEXT NOT NULL,
            tribunal TEXT NOT NULL,
            -- `unorganica` é o juízo tal como o CITIUS o escreve
            -- ("Juízo Local Cível de Matosinhos - Juiz 4"). Fica inteiro; o
            -- `juizo` normalizado é derivado dele quando fizer falta.
            unorganica TEXT,
            especie TEXT,
            valor_raw TEXT,
            valor NUMERIC(16, 2),
            data_distribuicao DATE,
            data_entrada DATE,
            observacoes TEXT,
            raw JSONB NOT NULL DEFAULT '{}'::jsonb,

            dedup_hash CHAR(64) NOT NULL UNIQUE,
            first_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
            last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
        CREATE INDEX ix_cfilings_data ON court_filings(data_distribuicao DESC NULLS LAST);
        CREATE INDEX ix_cfilings_tribunal ON court_filings(tribunal);
        CREATE INDEX ix_cfilings_process ON court_filings(process_number);
        -- O cruzamento por dia (backfill, rematch incremental) percorre a coluna
        -- da data; o `especie` serve os filtros da consulta.
        CREATE INDEX ix_cfilings_especie ON court_filings(especie);

        CREATE TABLE court_filing_parties (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            filing_id UUID NOT NULL REFERENCES court_filings(id) ON DELETE CASCADE,
            name TEXT NOT NULL,
            -- Normalizado à entrada (minúsculas, sem acentos) para casar com o
            -- `registry_entities.name_norm`, que é o que dá o NIF que o tribunal
            -- não publica.
            name_norm TEXT NOT NULL,
            role TEXT,
            nif CHAR(9),
            -- Como é que este NIF apareceu: 'grafo' (resolvido pelo nome contra
            -- o registo comercial) ou 'fonte' (se um dia o CITIUS o publicar).
            nif_source TEXT
        );
        CREATE INDEX ix_cfparties_filing ON court_filing_parties(filing_id);
        CREATE INDEX ix_cfparties_nif ON court_filing_parties(nif) WHERE nif IS NOT NULL;
        CREATE INDEX ix_cfparties_name_trgm
            ON court_filing_parties USING gin (name_norm gin_trgm_ops);

        CREATE TABLE court_filing_hits (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            filing_id UUID NOT NULL REFERENCES court_filings(id) ON DELETE CASCADE,
            -- Um acerto é sempre de uma parte concreta: é ela que diz se a
            -- empresa é autora ou ré, e sem isso o acerto não se explica.
            party_id UUID NOT NULL
                REFERENCES court_filing_parties(id) ON DELETE CASCADE,
            company_id UUID REFERENCES companies(id) ON DELETE CASCADE,
            watchlist_id UUID REFERENCES insolvency_watchlist(id) ON DELETE CASCADE,
            matched_nif CHAR(9),
            matched_name TEXT,
            matched_role TEXT,
            -- 'nif' quando a parte resolveu para uma entidade do registo e o NIF
            -- bateu certo; 'nome' quando foi só o distintivo. A distinção conta:
            -- um falso positivo por nome é muito mais provável, e é o que
            -- permite rever depois só o que é frágil.
            match_kind TEXT NOT NULL,
            score NUMERIC(4, 3),
            created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
        -- Únicos parciais, e não um UNIQUE simples: com colunas anuláveis o
        -- Postgres trata cada NULL como distinto e a chave não deduplicava nada.
        CREATE UNIQUE INDEX ux_cfhits_company ON court_filing_hits(filing_id, party_id, company_id)
            WHERE company_id IS NOT NULL;
        CREATE UNIQUE INDEX ux_cfhits_watchlist
            ON court_filing_hits(filing_id, party_id, watchlist_id)
            WHERE watchlist_id IS NOT NULL;
        CREATE INDEX ix_cfhits_company ON court_filing_hits(company_id)
            WHERE company_id IS NOT NULL;
        CREATE INDEX ix_cfhits_filing ON court_filing_hits(filing_id);

        -- O `registry_entities` só tinha índice trigram no `name_norm`, que serve
        -- pesquisa aproximada mas não a igualdade exacta que resolve o NIF de uma
        -- parte do tribunal. São 1,27 M de linhas: sem isto, cada lote do
        -- cruzamento varria a tabela toda.
        CREATE INDEX ix_rent_name_norm ON registry_entities(name_norm);
        """
    )


def downgrade() -> None:
    op.execute(
        """
        DROP INDEX IF EXISTS ix_rent_name_norm;
        DROP TABLE IF EXISTS court_filing_hits;
        DROP TABLE IF EXISTS court_filing_parties;
        DROP TABLE IF EXISTS court_filings;
        """
    )