"""intelligence layer: segmentation, parties, coverage

Revision ID: 0003
Revises: 0002
Create Date: 2026-04-16

"""
from alembic import op


revision = "0003"
down_revision = "0002"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # --- companies: segmentation + coverage ---
    op.execute("""
        ALTER TABLE companies
          ADD COLUMN monitoring_type TEXT NOT NULL DEFAULT 'internal'
            CHECK (monitoring_type IN ('internal','competitor','analysis')),
          ADD COLUMN data_coverage_start TIMESTAMPTZ,
          ADD COLUMN data_coverage_end TIMESTAMPTZ,
          ADD COLUMN risk_score INT
    """)
    op.execute(
        "CREATE INDEX ix_companies_monitoring_type ON companies(monitoring_type)"
    )

    # --- process_parties: extracted parties from each process, trigram-searchable ---
    op.execute("""
        CREATE TABLE process_parties (
          id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
          process_id UUID NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
          name TEXT NOT NULL,
          nif VARCHAR(9),
          role TEXT,
          created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        )
    """)
    op.execute("CREATE INDEX ix_process_parties_process ON process_parties(process_id)")
    op.execute(
        "CREATE INDEX ix_process_parties_name_trgm "
        "ON process_parties USING gin (name gin_trgm_ops)"
    )

    # Backfill process_parties from existing processes.raw.
    # Distribuição stores parties under 'parties', CIRE under '_parties'.
    op.execute("""
        INSERT INTO process_parties (process_id, name, nif, role)
        SELECT
          p.id,
          trim(party->>'name') AS name,
          NULLIF(NULLIF(party->>'nif', ''), '—') AS nif,
          NULLIF(party->>'role', '') AS role
        FROM processes p,
          jsonb_array_elements(
            COALESCE(p.raw->'parties', p.raw->'_parties', '[]'::jsonb)
          ) AS party
        WHERE COALESCE(trim(party->>'name'), '') <> ''
    """)

    # --- extra index mentioned in spec ---
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_processes_company_first_seen "
        "ON processes(company_id, first_seen_at DESC)"
    )

    # --- seed coverage for existing companies ---
    op.execute("""
        UPDATE companies c SET
          data_coverage_start = (
            SELECT MIN(first_seen_at) FROM processes WHERE company_id = c.id
          ),
          data_coverage_end = now()
    """)


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_processes_company_first_seen")
    op.execute("DROP TABLE IF EXISTS process_parties CASCADE")
    op.execute("""
        ALTER TABLE companies
          DROP COLUMN IF EXISTS risk_score,
          DROP COLUMN IF EXISTS data_coverage_end,
          DROP COLUMN IF EXISTS data_coverage_start,
          DROP COLUMN IF EXISTS monitoring_type
    """)
    op.execute("DROP INDEX IF EXISTS ix_companies_monitoring_type")