from __future__ import annotations

import json
import logging
from typing import Any

logger = logging.getLogger(__name__)


class ErpAutonomousMixin:
    def create_autonomous_session(self, payload: dict[str, Any]) -> dict[str, Any]:
        """Tạo mới một phiên tự trị chuẩn hóa dữ liệu."""
        sql = """
            INSERT INTO erp_autonomous_agent_sessions (
                session_code, session_type, status, lead_agent_code,
                total_records_scanned, total_records_healed, data_quality_index,
                agent_actions_summary, findings, executive_summary
            ) VALUES (
                %(session_code)s, %(session_type)s, %(status)s, %(lead_agent_code)s,
                %(total_records_scanned)s, %(total_records_healed)s, %(data_quality_index)s,
                %(agent_actions_summary)s, %(findings)s, %(executive_summary)s
            ) RETURNING *;
        """
        params = {
            "session_code": payload["session_code"],
            "session_type": payload.get("session_type", "scheduled_sweep"),
            "status": payload.get("status", "running"),
            "lead_agent_code": payload.get("lead_agent_code", "minh"),
            "total_records_scanned": payload.get("total_records_scanned", 0),
            "total_records_healed": payload.get("total_records_healed", 0),
            "data_quality_index": payload.get("data_quality_index", 0.0),
            "agent_actions_summary": json.dumps(
                payload.get("agent_actions_summary", {})
            ),
            "findings": json.dumps(payload.get("findings", [])),
            "executive_summary": payload.get(
                "executive_summary", "Đang chạy phiên tự trị..."
            ),
        }
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            conn.commit()
            return row

    def update_autonomous_session(
        self, session_id: str, payload: dict[str, Any]
    ) -> dict[str, Any] | None:
        """Cập nhật kết quả phiên tự trị."""
        set_clauses: list[str] = []
        params: dict[str, Any] = {"id": session_id}

        for key, value in payload.items():
            if key in ["agent_actions_summary", "findings"] and isinstance(
                value, (dict, list)
            ):
                set_clauses.append(f"{key} = %({key})s::jsonb")
                params[key] = json.dumps(value)
            elif key in [
                "status",
                "completed_at",
                "total_records_scanned",
                "total_records_healed",
                "data_quality_index",
                "executive_summary",
            ]:
                set_clauses.append(f"{key} = %({key})s")
                params[key] = value

        if not set_clauses:
            return self.get_autonomous_session(session_id)

        sql = f"""
            UPDATE erp_autonomous_agent_sessions
            SET {", ".join(set_clauses)}
            WHERE id = %(id)s
            RETURNING *;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            conn.commit()
            return row

    def get_autonomous_session(self, session_id: str) -> dict[str, Any] | None:
        """Lấy thông tin chi tiết một phiên tự trị."""
        sql = "SELECT * FROM erp_autonomous_agent_sessions WHERE id = %s OR session_code = %s LIMIT 1;"
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (session_id, session_id))
            return cur.fetchone()

    def list_autonomous_sessions(self, limit: int = 30) -> list[dict[str, Any]]:
        """Lấy danh sách các phiên tự trị gần nhất."""
        sql = """
            SELECT * FROM erp_autonomous_agent_sessions
            ORDER BY started_at DESC
            LIMIT %s;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (limit,))
            return cur.fetchall()

    def create_autonomous_healing_log(self, payload: dict[str, Any]) -> dict[str, Any]:
        """Ghi nhận vết tự sửa đổi (Self-Healing Log) của một Agent."""
        sql = """
            INSERT INTO erp_autonomous_healing_logs (
                session_id, agent_code, table_name, record_id, action_type,
                snapshot_before, snapshot_after, reason, confidence_score
            ) VALUES (
                %(session_id)s, %(agent_code)s, %(table_name)s, %(record_id)s, %(action_type)s,
                %(snapshot_before)s, %(snapshot_after)s, %(reason)s, %(confidence_score)s
            ) RETURNING *;
        """
        params = {
            "session_id": payload["session_id"],
            "agent_code": payload["agent_code"],
            "table_name": payload["table_name"],
            "record_id": str(payload["record_id"]),
            "action_type": payload["action_type"],
            "snapshot_before": json.dumps(payload.get("snapshot_before", {})),
            "snapshot_after": json.dumps(payload.get("snapshot_after", {})),
            "reason": payload.get(
                "reason", "Tự chuẩn hóa dữ liệu theo quy tắc nghiệp vụ DSCons."
            ),
            "confidence_score": payload.get("confidence_score", 95),
        }
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, params)
            row = cur.fetchone()
            conn.commit()
            return row

    def list_autonomous_healing_logs(self, session_id: str) -> list[dict[str, Any]]:
        """Lấy danh sách các hành động tự chuẩn hóa trong một phiên."""
        sql = """
            SELECT * FROM erp_autonomous_healing_logs
            WHERE session_id = %s
            ORDER BY created_at ASC;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (session_id,))
            return cur.fetchall()

    def get_autonomous_overview(self) -> dict[str, Any]:
        """Thống kê tổng quan trạng thái vận hành tự trị."""
        sql = """
            SELECT 
                COUNT(*) as total_sessions,
                COALESCE(SUM(total_records_healed), 0) as total_healed_records,
                COALESCE(AVG(data_quality_index), 0.0) as avg_dqi,
                MAX(started_at) as last_sweep_at
            FROM erp_autonomous_agent_sessions;
        """
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(sql)
                row = cur.fetchone() or {}
                return {
                    "total_sessions": int(row.get("total_sessions", 0)),
                    "total_healed_records": int(row.get("total_healed_records", 0)),
                    "avg_dqi": round(float(row.get("avg_dqi", 0.0)), 2),
                    "last_sweep_at": row.get("last_sweep_at").isoformat()
                    if row.get("last_sweep_at")
                    else None,
                }