from __future__ import annotations

from collections.abc import Iterable

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

from supply_infra.db.models.demand_belong_category import DemandBelongCategory
from supply_infra.db.repositories.base import BaseRepository

_BATCH_SIZE = 1000


class DemandBelongCategoryRepository(BaseRepository[DemandBelongCategory]):
    """需求归属分类表 — 按 name 唯一键去重后批量写入。"""

    model = DemandBelongCategory

    def get_by_ids(self, ids: Iterable[int]) -> list[DemandBelongCategory]:
        """按 id 批量查询，跳过已软删除记录。"""
        id_list = [int(i) for i in ids]
        if not id_list:
            return []
        stmt = select(DemandBelongCategory).where(
            DemandBelongCategory.id.in_(id_list),
            DemandBelongCategory.is_delete == 0,
        )
        return list(self.session.scalars(stmt).all())

    def search_by_name_like(self, keyword: str) -> list[DemandBelongCategory]:
        """按名称模糊匹配（双向包含关系不在此处理，仅 LIKE %keyword%），跳过已软删除记录。"""
        keyword = (keyword or "").strip()
        if not keyword:
            return []
        stmt = select(DemandBelongCategory).where(
            DemandBelongCategory.name.like(f"%{keyword}%"),
            DemandBelongCategory.is_delete == 0,
        )
        return list(self.session.scalars(stmt).all())

    def get_existing_names(self, names: Iterable[str]) -> set[str]:
        """返回 names 中已存在于表内的名称（含软删除）。"""
        name_list = [n for n in names if n]
        if not name_list:
            return set()

        existing: set[str] = set()
        for i in range(0, len(name_list), _BATCH_SIZE):
            batch = name_list[i : i + _BATCH_SIZE]
            stmt = select(DemandBelongCategory.name).where(
                DemandBelongCategory.name.in_(batch)
            )
            existing.update(n for n in self.session.scalars(stmt).all() if n)
        return existing

    def list_active(self) -> list[DemandBelongCategory]:
        """返回所有未删除的需求归属记录，按 category_id、id 排序。"""
        stmt = (
            select(DemandBelongCategory)
            .where(DemandBelongCategory.is_delete == 0)
            .order_by(DemandBelongCategory.category_id, DemandBelongCategory.id)
        )
        return list(self.session.scalars(stmt).all())

    def list_by_category_ids(
        self, category_ids: list[int]
    ) -> list[DemandBelongCategory]:
        """返回挂载在指定类目上的未删除需求词。"""
        if not category_ids:
            return []
        stmt = (
            select(DemandBelongCategory)
            .where(
                DemandBelongCategory.is_delete == 0,
                DemandBelongCategory.category_id.in_(category_ids),
            )
            .order_by(DemandBelongCategory.category_id, DemandBelongCategory.id)
        )
        return list(self.session.scalars(stmt).all())

    def list_active_id_name(self) -> list[tuple[int, str]]:
        """返回所有未删除记录的 (id, name)，跳过空名称。"""
        stmt = (
            select(DemandBelongCategory.id, DemandBelongCategory.name)
            .where(
                DemandBelongCategory.is_delete == 0,
                DemandBelongCategory.name.is_not(None),
                DemandBelongCategory.name != "",
            )
            .order_by(DemandBelongCategory.id)
        )
        return [(int(row_id), name) for row_id, name in self.session.execute(stmt).all() if name]

    def get_by_names(self, names: Iterable[str]) -> dict[str, DemandBelongCategory]:
        """按 name 批量查询未删除记录，返回 name → row。"""
        name_list = [n for n in names if n]
        if not name_list:
            return {}

        result: dict[str, DemandBelongCategory] = {}
        for i in range(0, len(name_list), _BATCH_SIZE):
            batch = name_list[i : i + _BATCH_SIZE]
            stmt = select(DemandBelongCategory).where(
                DemandBelongCategory.is_delete == 0,
                DemandBelongCategory.name.in_(batch),
            )
            for row in self.session.scalars(stmt).all():
                if row.name:
                    result[row.name] = row
        return result

    def bulk_insert_ignore(self, rows: list[dict]) -> int:
        """批量插入，MySQL 按 name 唯一索引忽略已存在行。"""
        if not rows:
            return 0

        inserted = 0
        for i in range(0, len(rows), _BATCH_SIZE):
            batch = rows[i : i + _BATCH_SIZE]
            stmt = insert(DemandBelongCategory).values(batch).prefix_with("IGNORE")
            result = self.session.execute(stmt)
            inserted += result.rowcount
        return inserted

    def update_video_lists(self, updates: dict[int, str | None]) -> int:
        """按 id 批量更新 video_list。"""
        if not updates:
            return 0

        updated = 0
        for belong_id, video_list in updates.items():
            stmt = (
                update(DemandBelongCategory)
                .where(DemandBelongCategory.id == belong_id)
                .values(video_list=video_list)
            )
            result = self.session.execute(stmt)
            updated += result.rowcount or 0
        return updated
