"""lightweight relations between companies based on shared parties/processes

Revision ID: 0012
Revises: 0011
Create Date: 2026-04-17

Intentionally NOT a person entity graph — we never materialise people. This
table is a denormalised cache of 'company A and company B share X', computed
nightly from process_parties + processes by the relations_service.
"""
from alembic import op


revision = "0012"
down_revision = "0011"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.execute("""
        CREATE TABLE entity_relations (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          source_company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          target_company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
          relation_type TEXT NOT NULL CHECK (relation_type IN ('shared_person','same_process')),
          shared_name TEXT,
          shared_nif TEXT,
          shared_processes TEXT[],
          process_count INT NOT NULL DEFAULT 1,
          confidence NUMERIC(3,2) NOT NULL,
          computed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
          CHECK (source_company_id < target_company_id)
        )
    """)
    op.execute("CREATE INDEX ix_rel_source ON entity_relations(source_company_id)")
    op.execute("CREATE INDEX ix_rel_target ON entity_relations(target_company_id)")
    op.execute("CREATE INDEX ix_rel_type ON entity_relations(relation_type, confidence DESC)")


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