"""publication_people — people extracted from MJ publication detail bodies

People (administradores, gerentes, sócios) referenced in MJ publications.
One row per (publication, person NIF). Used for the Pessoas card on the
company detail page and for cross-company lookup (is this person on the
board of any other monitored company?).

The NIF is the join key — it's unique enough in Portugal to identify a
natural or legal person. We keep the name too but don't trust it for
matching (accents / abbreviations vary).
"""
from alembic import op
import sqlalchemy as sa

revision = "0016"
down_revision = "0015"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute(
        """
        CREATE TABLE publication_people (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            publication_id UUID NOT NULL REFERENCES dre_publications(id) ON DELETE CASCADE,
            company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
            person_nif TEXT NOT NULL,
            person_name TEXT,
            cargo TEXT,
            event TEXT NOT NULL CHECK (event IN ('appointed','ceased','other')),
            act_date DATE,
            created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        );
        CREATE INDEX ix_pub_people_nif ON publication_people(person_nif);
        CREATE INDEX ix_pub_people_company ON publication_people(company_id, act_date DESC NULLS LAST);
        CREATE UNIQUE INDEX ux_pub_people_dedup ON publication_people(publication_id, person_nif);
        """
    )


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