"""public contracts per company (ptdata / BASE-IMPIC)

Revision ID: 0008
Revises: 0007
Create Date: 2026-04-17

Stores BASE/IMPIC procurement contracts fetched from ptdata.org. Each row is
anchored to a company in our DB; the same contract may appear against two
companies (awarding entity + supplier) — hence UNIQUE(company_id, external_id)
rather than external_id alone.
"""
from alembic import op


revision = "0008"
down_revision = "0007"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE public_contracts (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          external_id TEXT NOT NULL,
          role TEXT NOT NULL CHECK (role IN ('supplier','awarding')),
          title TEXT,
          awarding_entity_name TEXT,
          awarding_entity_nif TEXT,
          supplier_names TEXT[],
          supplier_nifs TEXT[],
          contract_price NUMERIC(14,2),
          base_price NUMERIC(14,2),
          actual_price NUMERIC(14,2),
          publication_date DATE,
          signing_date DATE,
          close_date DATE,
          execution_days INT,
          procedure_type TEXT,
          cpv_codes TEXT[],
          district_code TEXT,
          municipality_code TEXT,
          raw_json JSONB,
          source TEXT NOT NULL DEFAULT 'ptdata',
          created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          UNIQUE (company_id, external_id)
        )
    """)
    op.execute("CREATE INDEX ix_pc_company ON public_contracts(company_id)")
    op.execute("CREATE INDEX ix_pc_signing ON public_contracts(signing_date DESC NULLS LAST)")
    op.execute("CREATE INDEX ix_pc_cpv ON public_contracts USING gin(cpv_codes)")


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