from __future__ import annotations

import logging
from decimal import Decimal
from typing import Any

logger = logging.getLogger("dscons.autonomous.projects")


class HealProjectsMixin:
    """Self-healing routines for project EVM metrics and financial recalculations."""

    def heal_projects_evm_module(self, session_id: str) -> dict[str, Any]:
        """Tính toán EVM tự động, chuẩn hóa dữ liệu tiến độ (AC, PV, EV)."""
        logger.info(
            "[AUTONOMOUS] AI Quỳnh & Phúc bắt đầu quét phân hệ Dự Án & WBS/EVM..."
        )
        scanned = 0
        healed = 0
        findings: list[dict[str, Any]] = []

        try:
            with self._erp_client.get_connection() as conn:
                with conn.cursor() as cur:
                    cur.execute("""
                        SELECT id, project_code, project_name, contract_value, status, progress_percent
                        FROM projects
                        ORDER BY created_at DESC
                        LIMIT 50;
                    """)
                    projects = cur.fetchall()

            scanned = len(projects)
            for proj in projects:
                proj_id = str(proj["id"])

                # Quét snapshot EVM gần nhất
                with self._erp_client.get_connection() as conn:
                    with conn.cursor() as cur:
                        cur.execute(
                            """
                            SELECT id, planned_value_pv, earned_value_ev, actual_cost_ac, cpi, spi
                            FROM erp_project_evm_snapshots
                            WHERE project_id = %s
                            ORDER BY period_date DESC
                            LIMIT 1;
                        """,
                            (proj_id,),
                        )
                        evm = cur.fetchone()

                if evm:
                    pv = Decimal(str(evm.get("planned_value_pv") or 0))
                    ev = Decimal(str(evm.get("earned_value_ev") or 0))
                    ac = Decimal(str(evm.get("actual_cost_ac") or 0))

                    if pv > 0 and ac > 0:
                        calc_cpi = round(ev / ac, 4)
                        calc_spi = round(ev / pv, 4)

                        if calc_cpi < Decimal("0.9000"):
                            findings.append(
                                {
                                    "agent": "quynh",
                                    "severity": "warning",
                                    "table": "erp_project_evm_snapshots",
                                    "record_id": str(evm["id"]),
                                    "message": f"Dự án [{proj.get('project_code')}] vượt chi phí (CPI = {calc_cpi:.3f} < 0.90).",
                                }
                            )

                        if calc_spi < Decimal("0.8500"):
                            findings.append(
                                {
                                    "agent": "phuc",
                                    "severity": "warning",
                                    "table": "erp_project_evm_snapshots",
                                    "record_id": str(evm["id"]),
                                    "message": f"Dự án [{proj.get('project_code')}] chậm tiến độ nghiêm trọng (SPI = {calc_spi:.3f} < 0.85).",
                                }
                            )

        except Exception as exc:
            logger.error("[AUTONOMOUS] Lỗi heal_projects_evm_module: %s", exc)
            findings.append(
                {
                    "agent": "quynh",
                    "severity": "error",
                    "table": "projects",
                    "message": f"Lỗi quét EVM dự án: {exc}",
                }
            )

        return {
            "agent": "quynh_phuc",
            "module": "projects_evm",
            "scanned": scanned,
            "healed": healed,
            "findings": findings,
        }