"""Dynamic attribute and criterion value records.

Revision ID: 0004_dynamic_attributes
Revises: 0003_discovery_schema
Create Date: 2026-10-04
"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

revision: str = "0004_dynamic_attributes"
down_revision: str | None = "0003_discovery_schema"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column("criterion_definitions", sa.Column("category", sa.String(128)))
    op.add_column("source_categories", sa.Column("canonical_category", sa.String(128)))
    op.create_table(
        "project_criteria",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column("project_id", postgresql.UUID(as_uuid=True), nullable=False),
        sa.Column("version", sa.Integer(), nullable=False),
        sa.Column("item_id", sa.String(128), nullable=False),
        sa.Column("criterion_key", sa.String(128), sa.ForeignKey("criterion_definitions.key"), nullable=False),
        sa.Column("operator", sa.String(16), nullable=False),
        sa.Column("value", postgresql.JSONB),
        sa.Column("mandatory", sa.Boolean(), nullable=False, server_default=sa.true()),
        sa.ForeignKeyConstraint(
            ["project_id", "version"],
            ["buying_project_criteria.project_id", "buying_project_criteria.version"],
            name="fk_project_criteria_version",
        ),
        sa.UniqueConstraint("project_id", "version", "item_id", name="uq_project_criterion_item"),
    )
    op.create_table(
        "product_observation_attributes",
        sa.Column("id", postgresql.UUID(as_uuid=True), primary_key=True),
        sa.Column(
            "observation_id",
            postgresql.UUID(as_uuid=True),
            sa.ForeignKey("product_observations.id"),
            nullable=False,
        ),
        sa.Column("attribute_key", sa.String(128), nullable=False),
        sa.Column("raw_value", postgresql.JSONB),
        sa.Column("normalized_value", postgresql.JSONB),
        sa.Column("unit", sa.String(32)),
        sa.UniqueConstraint("observation_id", "attribute_key", name="uq_observation_attribute"),
    )
    _migrate_criteria_json()
    _migrate_observation_json()
    op.drop_column("buying_project_criteria", "definition")
    op.drop_column("product_observations", "raw_attributes")
    op.drop_column("product_observations", "normalized_attributes")


def _migrate_criteria_json() -> None:
    bind = op.get_bind()
    rows = bind.execute(sa.text("SELECT project_id, version, definition FROM buying_project_criteria")).mappings()
    for row in rows:
        items = (row["definition"] or {}).get("items") or []
        for raw in items:
            bind.execute(
                sa.text(
                    """
                    INSERT INTO project_criteria
                        (id, project_id, version, item_id, criterion_key, operator, value, mandatory)
                    VALUES
                        (gen_random_uuid(), :project_id, :version, :item_id, :criterion_key, :operator, :value, :mandatory)
                    """
                ),
                {
                    "project_id": row["project_id"],
                    "version": row["version"],
                    "item_id": str(raw.get("id") or raw.get("criterion")),
                    "criterion_key": str(raw.get("criterion")),
                    "operator": str(raw.get("operator")),
                    "value": raw.get("value"),
                    "mandatory": bool(raw.get("mandatory", True)),
                },
            )


def _migrate_observation_json() -> None:
    bind = op.get_bind()
    rows = bind.execute(
        sa.text("SELECT id, raw_attributes, normalized_attributes FROM product_observations")
    ).mappings()
    for row in rows:
        raw = row["raw_attributes"] or {}
        normalized = row["normalized_attributes"] or {}
        keys = set(raw) | set(normalized)
        for key in keys:
            bind.execute(
                sa.text(
                    """
                    INSERT INTO product_observation_attributes
                        (id, observation_id, attribute_key, raw_value, normalized_value, unit)
                    VALUES
                        (gen_random_uuid(), :observation_id, :attribute_key, :raw_value, :normalized_value, NULL)
                    """
                ),
                {
                    "observation_id": row["id"],
                    "attribute_key": key,
                    "raw_value": raw.get(key),
                    "normalized_value": normalized.get(key),
                },
            )


def downgrade() -> None:
    op.add_column("product_observations", sa.Column("raw_attributes", postgresql.JSONB, nullable=False, server_default="{}"))
    op.add_column(
        "product_observations",
        sa.Column("normalized_attributes", postgresql.JSONB, nullable=False, server_default="{}"),
    )
    op.add_column("buying_project_criteria", sa.Column("definition", postgresql.JSONB, nullable=False, server_default='{"items":[]}'))
    op.drop_table("product_observation_attributes")
    op.drop_table("project_criteria")
    op.drop_column("source_categories", "canonical_category")
    op.drop_column("criterion_definitions", "category")