from __future__ import annotations

from sqlalchemy import delete, select
from sqlalchemy.dialects.mysql import insert

from supply_infra.db.models.demand_grade import DemandGrade
from supply_infra.db.models.demand_grade_category_rel import DemandGradeCategoryRel
from supply_infra.db.repositories.base import BaseRepository


class DemandGradeCategoryRelRepository(BaseRepository[DemandGradeCategoryRel]):
    """demand_grade ↔ global_tree_category 归属关系 — 支撑按分类高效查询已分级需求。"""

    model = DemandGradeCategoryRel

    def replace_for_demand_grade(self, demand_grade_id: int, category_ids: list[int]) -> None:
        """覆盖某个 demand_grade 的分类映射：先删除旧的，再写入新的（空列表则只删）。"""
        self.session.execute(
            delete(DemandGradeCategoryRel).where(
                DemandGradeCategoryRel.demand_grade_id == demand_grade_id
            )
        )
        unique_ids = sorted({int(c) for c in category_ids if c is not None})
        if not unique_ids:
            return
        rows = [
            {"demand_grade_id": demand_grade_id, "category_id": category_id}
            for category_id in unique_ids
        ]
        stmt = insert(DemandGradeCategoryRel).values(rows).prefix_with("IGNORE")
        self.session.execute(stmt)

    def list_items_with_category(self, biz_dt: str) -> list[dict]:
        """按 biz_dt 展开为一行一个 category_id，供前端按分类展示需求列表使用。"""
        stmt = (
            select(
                DemandGrade.id,
                DemandGrade.demand_name,
                DemandGradeCategoryRel.category_id,
                DemandGrade.grade,
                DemandGrade.score,
                DemandGrade.reason,
                DemandGrade.strategies,
                DemandGrade.biz_dt,
            )
            .join(DemandGradeCategoryRel, DemandGradeCategoryRel.demand_grade_id == DemandGrade.id)
            .where(DemandGrade.biz_dt == biz_dt)
            .order_by(DemandGrade.grade, DemandGrade.demand_name)
        )
        return [
            {
                "id": int(row.id),
                "demand_name": row.demand_name,
                "category_id": int(row.category_id),
                "grade": row.grade,
                "score": float(row.score) if row.score is not None else None,
                "reason": row.reason,
                "strategies": row.strategies,
                "biz_dt": row.biz_dt,
            }
            for row in self.session.execute(stmt).all()
        ]
