from __future__ import annotations

import json
import logging
import uuid
from datetime import date, timedelta
from typing import Any

logger = logging.getLogger(__name__)


class ErpTakeoffItemMixin:
    def create_drawing_takeoff_item(self, payload: dict[str, Any]) -> dict[str, Any]:
        defaults = {
            "id": payload.get("id") or str(uuid.uuid4()),
            "takeoff_id": payload.get("takeoff_id"),
            "item_order": payload.get("item_order", 1),
            "wbs_code": payload.get("wbs_code", "1.1.1"),
            "norm_code": payload.get("norm_code", "AB.11110"),
            "item_name": payload.get("item_name", "Công tác xây lắp"),
            "category": payload.get("category", "concrete"),
            "dimension_formula": payload.get("dimension_formula", ""),
            "unit": payload.get("unit", "m3"),
            "quantity": payload.get("quantity", 0.0),
            "unit_price_vnd": payload.get("unit_price_vnd", 0.0),
            "total_amount_vnd": payload.get("total_amount_vnd", 0.0),
            "price_source_url": payload.get("price_source_url", ""),
        }
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                sql = """
                    INSERT INTO erp_drawing_takeoff_items (
                        id, takeoff_id, item_order, wbs_code, norm_code, item_name,
                        category, dimension_formula, unit, quantity, unit_price_vnd,
                        total_amount_vnd, price_source_url
                    ) VALUES (
                        %(id)s, %(takeoff_id)s, %(item_order)s, %(wbs_code)s, %(norm_code)s, %(item_name)s,
                        %(category)s, %(dimension_formula)s, %(unit)s, %(quantity)s, %(unit_price_vnd)s,
                        %(total_amount_vnd)s, %(price_source_url)s
                    ) RETURNING *;
                """
                cur.execute(sql, defaults)
                conn.commit()
                return cur.fetchone()

    def create_drawing_takeoff_items_batch(self, items: list[dict[str, Any]]) -> int:
        """High-speed batch insert of takeoff items in a single SQL transaction."""
        if not items:
            return 0
        prepared = []
        for payload in items:
            prepared.append(
                {
                    "id": payload.get("id") or str(uuid.uuid4()),
                    "takeoff_id": payload.get("takeoff_id"),
                    "item_order": payload.get("item_order", 1),
                    "wbs_code": payload.get("wbs_code", "1.1.1"),
                    "norm_code": payload.get("norm_code", "AB.11110"),
                    "item_name": payload.get("item_name", "Công tác xây lắp"),
                    "category": payload.get("category", "concrete"),
                    "dimension_formula": payload.get("dimension_formula", ""),
                    "unit": payload.get("unit", "m3"),
                    "quantity": payload.get("quantity", 0.0),
                    "unit_price_vnd": payload.get("unit_price_vnd", 0.0),
                    "total_amount_vnd": payload.get("total_amount_vnd", 0.0),
                    "price_source_url": payload.get("price_source_url", ""),
                }
            )
        sql = """
            INSERT INTO erp_drawing_takeoff_items (
                id, takeoff_id, item_order, wbs_code, norm_code, item_name,
                category, dimension_formula, unit, quantity, unit_price_vnd,
                total_amount_vnd, price_source_url
            ) VALUES (
                %(id)s, %(takeoff_id)s, %(item_order)s, %(wbs_code)s, %(norm_code)s, %(item_name)s,
                %(category)s, %(dimension_formula)s, %(unit)s, %(quantity)s, %(unit_price_vnd)s,
                %(total_amount_vnd)s, %(price_source_url)s
            );
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.executemany(sql, prepared)
            conn.commit()
            return len(prepared)

    def list_drawing_takeoff_items(self, takeoff_id: str) -> list[dict[str, Any]]:
        """Lấy danh sách chi tiết các đầu mục công việc đã bóc tách của một bản vẽ."""
        sql = """
            SELECT *
            FROM erp_drawing_takeoff_items
            WHERE takeoff_id = %s
            ORDER BY item_order ASC, created_at ASC;
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (takeoff_id,))
            return cur.fetchall()

    def update_drawing_takeoff_item(
        self, item_id: str, payload: dict[str, Any]
    ) -> dict[str, Any] | None:
        """Cập nhật khối lượng, đơn giá hoặc công thức của một dòng bóc tách."""
        if not payload:
            return None
        set_clauses = []
        params = []
        for k, v in payload.items():
            set_clauses.append(f"{k} = %s")
            params.append(v)
        params.append(item_id)
        sql = f"UPDATE erp_drawing_takeoff_items SET {', '.join(set_clauses)} WHERE 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 delete_drawing_takeoff_items(self, takeoff_id: str) -> int:
        """Xóa toàn bộ các dòng chi tiết bóc tách của một bản vẽ để chuẩn bị bóc tách lại."""
        sql = (
            "DELETE FROM erp_drawing_takeoff_items WHERE takeoff_id = %s RETURNING id;"
        )
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute(sql, (takeoff_id,))
            rows = cur.fetchall()
            conn.commit()
            return len(rows)

    def sync_takeoff_to_project_wbs(
        self, takeoff_id: str, project_id: str
    ) -> dict[str, Any]:
        """1-Click đồng bộ các hạng mục bóc tách sang cây WBS của dự án."""
        items = self.list_drawing_takeoff_items(takeoff_id)
        if not items:
            return {
                "synced_count": 0,
                "message": "Không có hạng mục bóc tách nào để đồng bộ",
            }

        synced_count = 0
        base_date = date.today()

        with self.get_connection() as conn:
            with conn.cursor() as cur:
                for idx, itm in enumerate(items):
                    wbs_code = itm.get("wbs_code") or f"2.{idx + 1}"
                    task_name = itm.get("item_name")
                    unit = itm.get("unit") or "m3"
                    qty = float(itm.get("quantity") or 0)
                    price = float(itm.get("unit_price_vnd") or 0)
                    total_val = float(itm.get("total_amount_vnd") or (qty * price))

                    # Insert or update erp_project_wbs
                    wbs_sql = """
                        INSERT INTO erp_project_wbs (
                            project_id, wbs_code, task_name, task_type,
                            unit, quantity, unit_price_vnd, total_amount_vnd,
                            start_date, end_date, duration_days, progress_percent,
                            status, assigned_role, sort_order
                        )
                        VALUES (
                            %s, %s, %s, 'task',
                            %s, %s, %s, %s,
                            %s, %s, 15, 0,
                            'todo', 'Kỹ sư QS & Hiện trường', %s
                        )
                        RETURNING id;
                    """
                    cur.execute(
                        wbs_sql,
                        (
                            project_id,
                            wbs_code,
                            task_name,
                            unit,
                            qty,
                            price,
                            total_val,
                            base_date.isoformat(),
                            (base_date + timedelta(days=15)).isoformat(),
                            idx + 1,
                        ),
                    )
                    wbs_row = cur.fetchone()
                    wbs_id = wbs_row["id"] if wbs_row else None

                    # Mark item as synced
                    cur.execute(
                        """
                        UPDATE erp_drawing_takeoff_items
                        SET is_synced_to_wbs = TRUE, synced_wbs_id = %s
                        WHERE id = %s;
                    """,
                        (wbs_id, itm["id"]),
                    )
                    synced_count += 1

                # Update takeoff header status
                cur.execute(
                    """
                    UPDATE erp_drawing_takeoffs
                    SET takeoff_status = 'synced_to_wbs', project_id = %s, updated_at = NOW()
                    WHERE id = %s;
                """,
                    (project_id, takeoff_id),
                )
                conn.commit()

        return {
            "synced_count": synced_count,
            "project_id": project_id,
            "takeoff_id": takeoff_id,
            "status": "success",
            "message": f"Đã đồng bộ thành công {synced_count} đầu mục bóc tách sang cây WBS Dự án.",
        }

    def get_takeoff_rate_breakdown(self, takeoff_id: str) -> dict[str, Any]:
        """Lấy danh sách các hạng mục kèm phân rã đơn giá chi tiết (VL + NC + M) theo Định mức 2026."""
        from app.modules.takeoff.application.boq_rate_breakdown_service import (
            BoqRateBreakdownEngine,
        )

        items = self.list_drawing_takeoff_items(takeoff_id)
        if not items:
            return {
                "status": "success",
                "takeoff_id": takeoff_id,
                "items": [],
                "totals": {},
            }

        total_vl = 0.0
        total_nc = 0.0
        total_m = 0.0
        total_boq = 0.0

        enriched_items = []
        with self.get_connection() as conn, conn.cursor() as cur:
            for itm in items:
                c_itm = dict(itm)
                qty = float(c_itm.get("quantity") or 0.0)
                unit_price = float(c_itm.get("unit_price_vnd") or 0.0)

                vl = float(c_itm.get("material_cost_vnd") or 0.0)
                nc = float(c_itm.get("labor_cost_vnd") or 0.0)
                m = float(c_itm.get("equipment_cost_vnd") or 0.0)
                breakdown = c_itm.get("material_breakdown")

                if (vl == 0.0 and nc == 0.0 and m == 0.0) or not breakdown:
                    bd_res = BoqRateBreakdownEngine.breakdown_item_rate(
                        item_name=c_itm.get("item_name", ""),
                        category=c_itm.get("category", ""),
                        unit=c_itm.get("unit", "m3"),
                        total_unit_price=unit_price,
                        norm_code=c_itm.get("norm_code", "AF.11110"),
                    )
                    vl = bd_res["material_cost_vnd"]
                    nc = bd_res["labor_cost_vnd"]
                    m = bd_res["equipment_cost_vnd"]
                    breakdown = bd_res["material_breakdown"]

                    # Cache back to DB
                    cur.execute(
                        """
                            UPDATE erp_drawing_takeoff_items
                            SET material_cost_vnd = %s,
                                labor_cost_vnd = %s,
                                equipment_cost_vnd = %s,
                                material_breakdown = %s::jsonb
                            WHERE id = %s;
                        """,
                        (vl, nc, m, json.dumps(breakdown), c_itm["id"]),
                    )

                c_itm["material_cost_vnd"] = vl
                c_itm["labor_cost_vnd"] = nc
                c_itm["equipment_cost_vnd"] = m
                c_itm["material_breakdown"] = breakdown
                c_itm["total_material_vnd"] = round(vl * qty, 0)
                c_itm["total_labor_vnd"] = round(nc * qty, 0)
                c_itm["total_equipment_vnd"] = round(m * qty, 0)

                total_vl += c_itm["total_material_vnd"]
                total_nc += c_itm["total_labor_vnd"]
                total_m += c_itm["total_equipment_vnd"]
                total_boq += float(c_itm.get("total_amount_vnd") or (qty * unit_price))
                enriched_items.append(c_itm)

            conn.commit()

        return {
            "status": "success",
            "takeoff_id": takeoff_id,
            "items": enriched_items,
            "count": len(enriched_items),
            "totals": {
                "total_material_cost_vnd": round(total_vl, 0),
                "total_labor_cost_vnd": round(total_nc, 0),
                "total_equipment_cost_vnd": round(total_m, 0),
                "total_boq_amount_vnd": round(total_boq, 0),
                "material_percentage": round(
                    (total_vl / total_boq * 100.0) if total_boq > 0 else 0, 1
                ),
                "labor_percentage": round(
                    (total_nc / total_boq * 100.0) if total_boq > 0 else 0, 1
                ),
                "equipment_percentage": round(
                    (total_m / total_boq * 100.0) if total_boq > 0 else 0, 1
                ),
            },
        }

    def get_takeoff_triple_variance(self, takeoff_id: str) -> dict[str, Any]:
        """Lấy bảng so sánh 3 cột (Bản vẽ Thiết kế vs HSMT vs Thực tế Thi công) kèm cảnh báo phát sinh VO."""
        from app.modules.takeoff.application.boq_rate_breakdown_service import (
            BoqRateBreakdownEngine,
        )

        items = self.list_drawing_takeoff_items(takeoff_id)
        if not items:
            return {
                "status": "success",
                "takeoff_id": takeoff_id,
                "items": [],
                "summary": {},
            }

        variance_items = []
        vo_increase_count = 0
        vo_decrease_count = 0
        total_design_val = 0.0
        total_hsmt_val = 0.0
        total_actual_val = 0.0

        for itm in items:
            c_itm = dict(itm)
            design_qty = float(c_itm.get("quantity") or 0.0)
            hsmt_qty = (
                float(c_itm["hsmt_quantity"])
                if c_itm.get("hsmt_quantity") is not None
                else None
            )
            act_qty = (
                float(c_itm["actual_quantity"])
                if c_itm.get("actual_quantity") is not None
                else None
            )
            price = float(c_itm.get("unit_price_vnd") or 0.0)

            v_res = BoqRateBreakdownEngine.compute_triple_variance(
                design_quantity=design_qty,
                hsmt_quantity=hsmt_qty,
                actual_quantity=act_qty,
            )
            c_itm.update(v_res)
            c_itm["variance_amount_vnd"] = round(v_res["variance_quantity"] * price, 0)

            total_design_val += design_qty * price
            if hsmt_qty is not None:
                total_hsmt_val += hsmt_qty * price
            if act_qty is not None:
                total_actual_val += act_qty * price

            if v_res["variation_status"] == "vo_increase":
                vo_increase_count += 1
            elif v_res["variation_status"] == "vo_decrease":
                vo_decrease_count += 1

            variance_items.append(c_itm)

        net_variance_vnd = (
            round(total_design_val - total_hsmt_val, 0) if total_hsmt_val > 0 else 0.0
        )

        return {
            "status": "success",
            "takeoff_id": takeoff_id,
            "items": variance_items,
            "count": len(variance_items),
            "summary": {
                "total_design_amount_vnd": round(total_design_val, 0),
                "total_hsmt_amount_vnd": round(total_hsmt_val, 0),
                "total_actual_amount_vnd": round(total_actual_val, 0),
                "net_variance_amount_vnd": net_variance_vnd,
                "vo_increase_items_count": vo_increase_count,
                "vo_decrease_items_count": vo_decrease_count,
                "has_variation_orders": (
                    vo_increase_count > 0 or vo_decrease_count > 0
                ),
            },
        }

    def generate_takeoff_material_request(self, takeoff_id: str) -> dict[str, Any]:
        """1-Click tổng hợp nhu cầu vật tư công trường (MR Requisition Schedule)."""
        from app.modules.takeoff.application.boq_rate_breakdown_service import (
            BoqRateBreakdownEngine,
        )

        items = self.list_drawing_takeoff_items(takeoff_id)
        takeoff = (
            self.get_drawing_takeoff(takeoff_id)
            if hasattr(self, "get_drawing_takeoff")
            else None
        )
        title = (
            f"{takeoff.get('drawing_code', '')} - {takeoff.get('drawing_title', 'Dự án Định Sơn')}"
            if takeoff
            else "Công trình Định Sơn"
        )

        return BoqRateBreakdownEngine.aggregate_material_requisition(
            items=items, project_title=title
        )

    def update_takeoff_item_quantities(
        self,
        item_id: str,
        hsmt_quantity: float | None = None,
        actual_quantity: float | None = None,
    ) -> dict[str, Any] | None:
        """Cập nhật khối lượng HSMT / Thực tế của một dòng BoQ và tự động tính chênh lệch."""
        from app.modules.takeoff.application.boq_rate_breakdown_service import (
            BoqRateBreakdownEngine,
        )

        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT * FROM erp_drawing_takeoff_items WHERE id = %s;", (item_id,)
                )
                row = cur.fetchone()
                if not row:
                    return None

                design_qty = float(row.get("quantity") or 0.0)
                v_res = BoqRateBreakdownEngine.compute_triple_variance(
                    design_quantity=design_qty,
                    hsmt_quantity=hsmt_quantity,
                    actual_quantity=actual_quantity,
                )

                cur.execute(
                    """
                    UPDATE erp_drawing_takeoff_items
                    SET hsmt_quantity = %s,
                        actual_quantity = %s,
                        variance_quantity = %s,
                        variance_percentage = %s,
                        variation_status = %s
                    WHERE id = %s
                    RETURNING *;
                """,
                    (
                        hsmt_quantity,
                        actual_quantity,
                        v_res["variance_quantity"],
                        v_res["variance_percentage"],
                        v_res["variation_status"],
                        item_id,
                    ),
                )
                updated = cur.fetchone()
                conn.commit()
                return updated