SupplyAgent/alembic/versions/20260731_11_daily_demand_package.py

"""add explainable evaluations and the unified daily demand package

Revision ID: 20260731_11
Revises: 20260731_10
Create Date: 2026-07-31
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "20260731_11"
down_revision: str | None = "20260731_10"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _pipeline_table_options() -> dict[str, str]:
    bind = op.get_bind()
    if bind.dialect.name != "mysql":
        return {}
    row = bind.execute(
        sa.text(
            "SELECT CHARACTER_SET_NAME, COLLATION_NAME "
            "FROM INFORMATION_SCHEMA.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = 'pipeline_run' "
            "AND COLUMN_NAME = 'run_id'"
        )
    ).one()
    return {
        "mysql_charset": str(row.CHARACTER_SET_NAME),
        "mysql_collate": str(row.COLLATION_NAME),
    }


def upgrade() -> None:
    table_options = _pipeline_table_options()
    op.create_table(
        "strategy_version",
        sa.Column("strategy_version_id", sa.String(36), primary_key=True),
        sa.Column("version_key", sa.String(64), nullable=False),
        sa.Column("status", sa.String(24), nullable=False),
        sa.Column("definition_json", sa.JSON(), nullable=False),
        sa.Column("change_reason", sa.Text(), nullable=False),
        sa.Column("created_by", sa.String(128), nullable=False),
        sa.Column("activated_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("version_key", name="uk_strategy_version_key"),
        **table_options,
    )
    op.create_index(
        "idx_strategy_version_status",
        "strategy_version",
        ["status", "activated_at"],
    )

    op.create_table(
        "demand_evaluation_snapshot",
        sa.Column("evaluation_id", sa.String(36), primary_key=True),
        sa.Column(
            "platform_demand_version_id",
            sa.String(36),
            sa.ForeignKey(
                "platform_demand_version.platform_demand_version_id",
                ondelete="RESTRICT",
            ),
            nullable=False,
        ),
        sa.Column(
            "run_id",
            sa.String(36),
            sa.ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "strategy_version_id",
            sa.String(36),
            sa.ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("biz_dt", sa.String(8), nullable=False),
        sa.Column("validity_score", sa.Numeric(6, 5), nullable=False),
        sa.Column("local_supply_priority", sa.Numeric(6, 5), nullable=False),
        sa.Column("data_confidence", sa.Numeric(6, 5), nullable=False),
        sa.Column("posterior_state", sa.String(24), nullable=False),
        sa.Column("action_tier", sa.String(32), nullable=False),
        sa.Column("metrics_json", sa.JSON(), nullable=False),
        sa.Column("missing_dimensions_json", sa.JSON(), nullable=False),
        sa.Column("decision_reason", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint(
            "platform_demand_version_id",
            name="uk_demand_evaluation_version",
        ),
        **table_options,
    )
    op.create_index(
        "idx_demand_evaluation_biz",
        "demand_evaluation_snapshot",
        ["biz_dt", "action_tier"],
    )

    op.create_table(
        "demand_score_contribution",
        sa.Column("contribution_id", sa.String(36), primary_key=True),
        sa.Column(
            "evaluation_id",
            sa.String(36),
            sa.ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("score_type", sa.String(24), nullable=False),
        sa.Column("dimension", sa.String(64), nullable=False),
        sa.Column("raw_value_json", sa.JSON(), nullable=False),
        sa.Column("normalized_value", sa.Numeric(8, 7), nullable=False),
        sa.Column("configured_weight", sa.Numeric(8, 7), nullable=False),
        sa.Column("effective_weight", sa.Numeric(8, 7), nullable=False),
        sa.Column("contribution", sa.Numeric(8, 7), nullable=False),
        sa.Column("data_status", sa.String(24), nullable=False),
        sa.Column("reason", sa.Text(), nullable=False),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint(
            "evaluation_id",
            "score_type",
            "dimension",
            name="uk_demand_score_contribution",
        ),
        **table_options,
    )
    op.create_index(
        "idx_demand_score_contribution_eval",
        "demand_score_contribution",
        ["evaluation_id"],
    )

    op.create_table(
        "daily_demand_package",
        sa.Column("demand_package_id", sa.String(36), primary_key=True),
        sa.Column(
            "run_id",
            sa.String(36),
            sa.ForeignKey("pipeline_run.run_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "evidence_package_id",
            sa.String(36),
            sa.ForeignKey("evidence_package.package_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "strategy_version_id",
            sa.String(36),
            sa.ForeignKey("strategy_version.strategy_version_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("biz_dt", sa.String(8), nullable=False),
        sa.Column("package_version", sa.Integer(), nullable=False),
        sa.Column("status", sa.String(24), nullable=False),
        sa.Column("content_hash", sa.String(64), nullable=False),
        sa.Column("item_count", sa.Integer(), nullable=False),
        sa.Column("published_at", sa.DateTime(), nullable=True),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint("run_id", name="uk_daily_demand_package_run"),
        **table_options,
    )
    op.create_index(
        "idx_daily_demand_package_biz",
        "daily_demand_package",
        ["biz_dt", "status"],
    )

    op.create_table(
        "daily_demand_task",
        sa.Column("task_id", sa.String(36), primary_key=True),
        sa.Column(
            "demand_package_id",
            sa.String(36),
            sa.ForeignKey("daily_demand_package.demand_package_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column(
            "platform_demand_version_id",
            sa.String(36),
            sa.ForeignKey(
                "platform_demand_version.platform_demand_version_id",
                ondelete="RESTRICT",
            ),
            nullable=False,
        ),
        sa.Column(
            "evaluation_id",
            sa.String(36),
            sa.ForeignKey("demand_evaluation_snapshot.evaluation_id", ondelete="RESTRICT"),
            nullable=False,
        ),
        sa.Column("action_tier", sa.String(32), nullable=False),
        sa.Column("search_terms_json", sa.JSON(), nullable=False),
        sa.Column("exclude_terms_json", sa.JSON(), nullable=False),
        sa.Column("hit_rules_json", sa.JSON(), nullable=False),
        sa.Column("hypotheses_json", sa.JSON(), nullable=False),
        sa.Column("evidence_gaps_json", sa.JSON(), nullable=False),
        sa.Column("existing_content_json", sa.JSON(), nullable=False),
        sa.Column("allocation_json", sa.JSON(), nullable=False),
        sa.Column("task_payload_json", sa.JSON(), nullable=False),
        sa.Column("created_at", sa.DateTime(), server_default=sa.func.now(), nullable=False),
        sa.UniqueConstraint(
            "demand_package_id",
            "platform_demand_version_id",
            name="uk_daily_demand_task_version",
        ),
        **table_options,
    )
    op.create_index(
        "idx_daily_demand_task_tier",
        "daily_demand_task",
        ["demand_package_id", "action_tier"],
    )


def downgrade() -> None:
    op.drop_index("idx_daily_demand_task_tier", table_name="daily_demand_task")
    op.drop_table("daily_demand_task")
    op.drop_index(
        "idx_daily_demand_package_biz",
        table_name="daily_demand_package",
    )
    op.drop_table("daily_demand_package")
    op.drop_index(
        "idx_demand_score_contribution_eval",
        table_name="demand_score_contribution",
    )
    op.drop_table("demand_score_contribution")
    op.drop_index(
        "idx_demand_evaluation_biz",
        table_name="demand_evaluation_snapshot",
    )
    op.drop_table("demand_evaluation_snapshot")
    op.drop_index("idx_strategy_version_status", table_name="strategy_version")
    op.drop_table("strategy_version")