"""api 层：把存储过程封装成业务语义。

调用序列来自 QuickTeam 前端构建产物（chunk-0cadb1de / 039a8b29 / 9b10aab2），
每个方法对应的存储过程与参数见 docs/QuickTeam-API导入-调研与设计.md §2.4。

dry_run=True 时写操作只记录意图并返回桩值，不产生任何服务端副作用。
"""

from __future__ import annotations

import hashlib
from typing import Any, Callable

from .errors import Category, QtError
from .models import (
    Feature,
    Project,
    Team,
    TfsTeam,
    User,
    WorkSheet,
    WorkSheetVersion,
    ms_to_date,
    normalize_iteration,
)
from .transport import Transport

CONN_QUICKTEAM = "QuickTeam"
CONN_USERCENTER = "UserCenter"

SP_LOGIN = "dbo.usp_Authentication"
SP_TEAMS = "QuickTeam.usp_GetTeamsByUserId"
SP_PROJECTS = "QuickTeam.usp_GetProjectsByTeamId"
SP_WORKSHEET = "QuickTeam.usp_GetWorkSheetById"
SP_PROJECT_USERS = "QuickTeam.usp_GetProjectUsersByProjectId"
SP_INSERT_WORKSHEET = "QuickTeam.usp_InsertWorkSheet"
SP_UPDATE_WORKSHEET = "QuickTeam.usp_UpdateWorkSheetById"
SP_VERIFY_WORKSHEET = "QuickTeam.usp_VerifyWorkSheetById"
SP_FEATURES = "QuickTeam.usp_GetWorkFeaturesByWorkSheetIds"
SP_WORKSHEET_VERSIONS = "QuickTeam.usp_GetWorkSheetsById"
SP_IMPORT_EXCEL = "QuickTeam.usp_BatchImportFeatureFromExcel"
SP_PUSH_FEATURES = "[QuickTeam].[usp_BatchImportWorkFeaturesByFeatureIds]"
SP_DELETE_WORKSHEET = "QuickTeam.usp_DeleteWorkSheetAllById"


class QuickTeamApi:
    def __init__(
        self,
        transport: Transport,
        *,
        dry_run: bool = False,
        log: Callable[[str], None] | None = None,
    ) -> None:
        self.t = transport
        self.dry_run = dry_run
        self.log = log or (lambda message: None)
        self._projects: dict[str, list[Project]] = {}

    # ------------------------------------------------------------------ 只读

    def login(self, username: str, password: str) -> User:
        digest = hashlib.md5(password.encode("utf-8")).hexdigest()
        rows = self.t.exec_table(
            CONN_USERCENTER,
            SP_LOGIN,
            [("UserName", username, "string"), ("Password", digest, "string")],
            op="login",
        )
        if not rows:
            raise QtError("登录失败：账号或密码不匹配", Category.AUTH)
        row = rows[0]
        return User(user_id=row["GuidId"], real_name=row.get("RealName") or username, user_name=row["UserName"])

    def teams(self, user_id: str) -> list[Team]:
        rows = self.t.exec_table(CONN_QUICKTEAM, SP_TEAMS, [("UserId", user_id, "string")], op="teams") or []
        return [Team(team_id=row["TeamId"], team_name=row.get("TeamName", "")) for row in rows]

    @staticmethod
    def resolve_team(teams: list[Team], name: str) -> Team:
        for team in teams:
            if team.team_name == name:
                return team
        available = "、".join(t.team_name for t in teams)
        raise QtError(f"未找到团队「{name}」，可选：{available}", Category.PARAM)

    def projects(self, team_id: str) -> list[Project]:
        if team_id not in self._projects:
            rows = (
                self.t.exec_table(
                    CONN_QUICKTEAM,
                    SP_PROJECTS,
                    [("TeamId", team_id, "string"), ("IsTemplate", "1", "string")],
                    op="projects",
                )
                or []
            )
            self._projects[team_id] = [
                Project(project_id=row["ProjectId"], project_name=row.get("ProjectName", "")) for row in rows
            ]
        return self._projects[team_id]

    def worksheet(self, worksheet_id: int) -> WorkSheet:
        rows = self.t.exec_table(
            CONN_QUICKTEAM, SP_WORKSHEET, [("WorkSheetId", worksheet_id, "int")], op="worksheet"
        )
        if not rows:
            raise QtError(f"工单 {worksheet_id} 不存在", Category.PARAM)
        return self._to_worksheet(rows[0])

    def project_users(self, project_id: str) -> list[dict]:
        return (
            self.t.exec_table(
                CONN_QUICKTEAM, SP_PROJECT_USERS, [("ProjectId", project_id, "string")], op="project_users"
            )
            or []
        )

    def work_sheets(self, project_id: str) -> list[WorkSheet]:
        """项目下全部工单（一次查全，含 SystemName / Status / 当前版本）。

        比「按窗口查 + 逐单读详情」少 N 次往返，也不受 `usp_GetWeeklyWorkSheetsByProjectId`
        按季度过滤的坑影响 —— plan 的幂等判定就靠它。
        """
        rows = (
            self.t.exec_table(
                CONN_QUICKTEAM,
                SP_WORKSHEET_VERSIONS,
                [("ProjectId", project_id, "string"), ("isReclaimed", 0, "int")],
                op="work_sheets",
            )
            or []
        )
        # 该 SP 用 union 拼了两部分：工单本体行（VersionId 为空）+ 每个版本一行。
        # 版本行的 WorkSheetCode 被覆盖成了 VersionCode，所以字段优先取本体行。
        base: dict[int, dict] = {}
        other: dict[int, dict] = {}
        for row in rows:
            try:
                worksheet_id = int(row.get("WorkSheetId"))
            except (TypeError, ValueError):
                continue
            is_base = row.get("VersionId") in (None, "", 0)
            (base if is_base else other).setdefault(worksheet_id, row)

        result: list[WorkSheet] = []
        for worksheet_id in sorted(set(base) | set(other)):
            row = base.get(worksheet_id) or other[worksheet_id]
            result.append(
                WorkSheet(
                    worksheet_id=worksheet_id,
                    code=row.get("WorkSheetCode", ""),
                    status=str(row.get("Status") or ""),
                    version_id=row.get("CurrentVersionId"),
                    version_code=str(row.get("VersionCode") or ""),
                    system_name=str(row.get("SystemName") or ""),
                    start_time=ms_to_date(row.get("StartTime")),
                    end_time=ms_to_date(row.get("EndTime")),
                )
            )
        return result

    def worksheet_versions(self, project_id: str, worksheet_id: int | None = None) -> list[WorkSheetVersion]:
        """项目下所有工单的版本行（列表页用的那个 SP，一行 = 一个版本）。"""
        rows = (
            self.t.exec_table(
                CONN_QUICKTEAM,
                SP_WORKSHEET_VERSIONS,
                [("ProjectId", project_id, "string"), ("isReclaimed", 0, "int")],
                op="worksheet_versions",
            )
            or []
        )
        result: list[WorkSheetVersion] = []
        seen: set[int] = set()
        for row in rows:
            raw_id = row.get("VersionId")
            if raw_id in (None, "", 0):
                continue  # 该 SP 用 union 拼了「无版本」的占位行，跳过
            try:
                version_id = int(raw_id)
                ws_id = int(row.get("WorkSheetId"))
            except (TypeError, ValueError):
                continue
            if version_id in seen:
                continue
            if worksheet_id is not None and ws_id != worksheet_id:
                continue
            seen.add(version_id)
            result.append(
                WorkSheetVersion(
                    version_id=version_id,
                    worksheet_id=ws_id,
                    version_code=str(row.get("VersionCode") or ""),
                    version_status=str(row.get("VersionStatus") or ""),
                    next_operator=str(row.get("NextOperator") or ""),
                )
            )
        result.sort(key=lambda item: item.version_id)
        return result

    def pending_version(self, project_id: str, worksheet_id: int) -> WorkSheetVersion | None:
        """取「待确认版本」。导入明细和确认工单都必须落到它上面。"""
        versions = self.worksheet_versions(project_id, worksheet_id)
        if not versions:
            return None
        pending = [item for item in versions if item.pending]
        if pending:
            return max(pending, key=lambda item: item.version_id)
        # 全部已确认：退回最后一个版本，交给上层按 version_code 判断流程是否走完
        return versions[-1]

    def features(self, worksheet_id: int) -> list[Feature]:
        rows = (
            self.t.exec_table(CONN_QUICKTEAM, SP_FEATURES, [("WorkSheetIds", str(worksheet_id), "string")], op="features")
            or []
        )
        return [
            Feature(feature_id=row["FeatureId"], code=row.get("FeatureCode", ""), name=row.get("FeatureName", ""))
            for row in rows
        ]

    # ------------------------------------------------------------------ 写入

    def create_worksheet(self) -> int:
        """新增空工单，返回 WorkSheetId。"""
        if self.dry_run:
            self.log("  [dry-run] InsertWorkSheet() → 桩 WorkSheetId=0")
            return 0
        rows = self.t.exec_table(CONN_QUICKTEAM, SP_INSERT_WORKSHEET, [], op="insert_worksheet", write=True)
        if not rows or not isinstance(rows, list) or "WorkSheetId" not in rows[0]:
            raise QtError(f"InsertWorkSheet 返回异常：{rows!r}", Category.PARAM)
        return int(rows[0]["WorkSheetId"])

    def fill_worksheet(
        self,
        worksheet_id: int,
        project: Project,
        *,
        system_name: str,
        start_time: str,
        end_time: str,
        evaluator: str,
        technical_leader: str,
        customer_leader: str,
        belong_team: str,
        content: str,
        operator: str,
        service_type: str = "DevelopService",
        status: str = "00",
        other_instructions: str = "",
    ) -> int:
        """填工单基本信息（Operation=new）。返回 Result（>0 视为成功）。"""
        params: list[tuple[str, Any, str]] = [
            ("WorkSheetId", worksheet_id, "string"),
            ("ProjectId", project.project_id, "string"),
            ("ProjectName", project.project_name, "string"),
            ("SystemName", system_name, "string"),
            ("Evaluator", evaluator, "string"),
            ("TechnicalLeader", technical_leader, "string"),
            ("CustomerLeader", customer_leader, "string"),
            ("StartTime", start_time, "string"),
            ("EndTime", end_time, "string"),
            ("Status", status, "string"),
            ("ServiceType", service_type, "string"),
            ("ImplementContent", content, "string"),
            ("OtherInstructions", other_instructions, "string"),
            ("Operator", operator, "string"),
            ("DeleteFlag", "0", "string"),
            ("Operation", "new", "string"),
            ("BelongTeam", belong_team, "string"),
        ]
        if self.dry_run:
            self.log(f"  [dry-run] UpdateWorkSheetById(ws={worksheet_id}, sprint={system_name}, "
                     f"{start_time}~{end_time}, 负责人={technical_leader}) → 桩 Result=1")
            return 1
        rows = self.t.exec_table(CONN_QUICKTEAM, SP_UPDATE_WORKSHEET, params, op="update_worksheet", write=True)
        result = rows[0].get("Result") if rows and isinstance(rows, list) else None

        # 实测结论：这个 SP 的 Result 恒为 0，但数据确实写入了（WorkSheetCode/ProjectId/
        # SystemName/StartTime/Status/BelongTeam 全部落库）。前端 `Result>0` 的提示判断
        # 也不可靠，所以这里以「重新读取工单」为准，而不是信返回值。
        if result is not None and int(result) > 0:
            return int(result)
        detail = self.worksheet(worksheet_id)
        if detail.system_name != system_name:
            raise QtError(
                f"保存后校验失败：SystemName={detail.system_name!r}，期望 {system_name!r}"
                f"（Result={result!r}）",
                Category.PARAM,
            )
        return int(result or 0)

    def upload_excel(self, local_path: str, folder: str = "WorkSheetImport") -> str:
        if self.dry_run:
            self.log(f"  [dry-run] FileUpload({local_path}) → 桩路径")
            return "<dry-run>"
        return self.t.upload(local_path, folder)

    def import_excel(self, worksheet_id: int, version_id: int, remote_path: str) -> None:
        """服务器端解析 Excel 写明细；Column1 == 0 为成功。"""
        params: list[tuple[str, Any, str]] = [
            ("WorkSheetId", worksheet_id, "int"),
            ("VersionId", version_id, "int"),
        ]
        if self.dry_run:
            self.log(f"  [dry-run] BatchImportFeature(ws={worksheet_id}, version={version_id}, {remote_path})")
            return
        result = self.t.batch_import(SP_IMPORT_EXCEL, params, remote_path)
        code = None
        if isinstance(result, list) and result and isinstance(result[0], dict):
            code = result[0].get("Column1")
        if code is None:
            raise QtError(f"Excel 解析未返回结果：{result!r}", Category.EXCEL)
        if int(code) != 0:
            raise QtError(f"Excel 解析失败（Column1={code}），模板或内容不符", Category.EXCEL)

    def delete_worksheet(self, worksheet_id: int) -> None:
        """删除整单（回滚半成品用）。"""
        if self.dry_run:
            self.log(f"  [dry-run] DeleteWorkSheetAllById(ws={worksheet_id})")
            return
        self.t.exec_table(
            CONN_QUICKTEAM, SP_DELETE_WORKSHEET, [("WorkSheetId", worksheet_id, "int")], op="delete_worksheet", write=True
        )

    def verify_worksheet(
        self,
        worksheet_id: int,
        version_id: int,
        *,
        next_operator: str,
        next_version_code: str,
        operator: str,
        percent: int,
    ) -> None:
        """确认工单（推进版本）。返回 0 为成功。"""
        params: list[tuple[str, Any, str]] = [
            ("WorkSheetId", worksheet_id, "int"),
            ("VersionId", version_id, "int"),
            ("NextOperator", next_operator, "string"),
            ("NextVersionCode", next_version_code, "string"),
            ("Percent", percent, "int"),
            ("Operator", operator, "string"),
        ]
        if self.dry_run:
            self.log(f"  [dry-run] VerifyWorkSheetById(ws={worksheet_id}, v={version_id}, "
                     f"next={next_operator}/{next_version_code}, percent={percent})")
            return
        result = self.t.exec_result(CONN_QUICKTEAM, SP_VERIFY_WORKSHEET, params, op="verify_worksheet")
        if isinstance(result, list) and result and isinstance(result[0], dict):
            result = result[0].get("Result", result[0].get("Column1"))
        if result is not None and str(result).strip() not in {"0", "0.0"}:
            raise QtError(f"确认工单失败，返回 {result!r}", Category.UNKNOWN)

    # ------------------------------------------------------------------ TFS

    def tfs_projects(self) -> list[dict]:
        return self.t.tfs("GetTeamProjects", op="TFS.GetTeamProjects") or []

    def tfs_teams(self, project_name: str, project_id: str) -> list[TfsTeam]:
        rows = (
            self.t.tfs(
                "GetTeamByProject",
                {"projectName": project_name, "projectId": project_id},
                op="TFS.GetTeamByProject",
            )
            or []
        )
        teams: list[TfsTeam] = []
        for row in rows:
            area = row.get("AreaPath") or []
            teams.append(
                TfsTeam(
                    team_name=row.get("TeamName", ""),
                    area_path=area[0] if area else "",
                    iterations=row.get("Iterations") or [],
                )
            )
        return teams

    @staticmethod
    def resolve_tfs_context(
        teams: list[TfsTeam], team_name: str, sprint: str
    ) -> tuple[str, str]:
        """按团队名与 Sprint 名解析 (areaPath, iterationPath)。匹配不到直接报错，不猜。"""
        team = next((t for t in teams if t.team_name == team_name), None)
        if team is None:
            available = "、".join(t.team_name for t in teams)
            raise QtError(f"TFS 团队「{team_name}」不存在，可选：{available}", Category.TFS)
        if not team.area_path:
            raise QtError(f"TFS 团队「{team_name}」没有 AreaPath", Category.TFS)

        target = normalize_iteration(sprint)
        hits = [item for item in team.iterations if normalize_iteration(str(item.get("Name", ""))) == target]
        if not hits:  # 退化：用完整路径的最后一段匹配
            hits = [
                item
                for item in team.iterations
                if normalize_iteration(str(item.get("Path", "")).split("\\")[-1]) == target
            ]
        if len(hits) != 1:
            sample = "、".join(str(i.get("Path")) for i in team.iterations[-3:]) or "（空）"
            raise QtError(
                f"TFS 迭代「{sprint}」在团队「{team_name}」下匹配到 {len(hits)} 条（最近：{sample}）",
                Category.TFS,
            )
        path = str(hits[0].get("Path", "")).replace("/", "\\")
        return team.area_path, path

    def push_tfs(self, feature_ids: list[int], project_name: str, area_path: str, iteration_path: str) -> Any:
        joined = ",".join(str(i) for i in feature_ids)
        if self.dry_run:
            self.log(
                f"  [dry-run] BatchImportWorkItemsToTfs(项目={project_name}, "
                f"area={area_path}, iter={iteration_path}, features={joined or '-'})"
            )
            return []
        if not feature_ids:
            raise QtError("没有可推送的功能模块", Category.TFS)
        inner = {
            "SPName": SP_PUSH_FEATURES,
            "SQLParams": [{"Name": "FeatureIds", "Value": joined, "DBType": "string"}],
        }
        import json
        import urllib.parse

        payload = {
            "connectionName": CONN_QUICKTEAM,
            "param": urllib.parse.quote(json.dumps(inner, separators=(",", ":"))),
            "projectName": project_name,
            "areaPath": area_path,
            "iterationPath": iteration_path,
        }
        result = self.t.tfs("BatchImportWorkItemsToTfs", payload, op="TFS.BatchImportWorkItemsToTfs", write=True)
        # 这个接口返回的是**状态码**不是数量：0 = 成功，-1 = 失败（实测传不存在的项目名
        # 返回 -1；正常返回 0）。别拿 >0 当成功，否则会把一批已推送的工单记成失败。
        if isinstance(result, int) and not isinstance(result, bool) and result != 0:
            raise QtError(f"TFS 推送失败（返回 {result}）", Category.TFS)
        return result

    # ------------------------------------------------------------------ 内部

    @staticmethod
    def _to_worksheet(row: dict) -> WorkSheet:
        return WorkSheet(
            worksheet_id=int(row.get("WorkSheetId", 0)),
            code=row.get("WorkSheetCode", ""),
            status=str(row.get("Status", "")),
            version_id=row.get("CurrentVersionId"),
            version_code=row.get("VersionCode", "") or "",
            system_name=row.get("SystemName", ""),
            start_time=ms_to_date(row.get("StartTime")),
            end_time=ms_to_date(row.get("EndTime")),
        )