from __future__ import annotations

import json
import logging
from typing import Any

logger = logging.getLogger("dscons.postgres.takeoff.items")


class TakeoffItemsMixin:
    """Takeoff line items and WBS synchronization database operations."""

    def save_drawing_takeoff_items(
        self,
        takeoff_id: str,
        items: list[dict[str, Any]],
    ) -> list[dict[str, Any]]:
        """Bulk save extracted BoQ items for a drawing takeoff."""
        if not items:
            return []

        sql = """
            INSERT INTO erp_drawing_takeoff_items (
                takeoff_id, item_order, wbs_code, norm_code,
                item_name, category, dimension_formula,
                unit, quantity, unit_price_vnd, total_amount_vnd,
                confidence_score, bounding_box_json, notes
            )
            VALUES (
                %s, %s, %s, %s,
                %s, %s, %s,
                %s, %s, %s, %s,
                %s, %s, %s
            )
            RETURNING *;
        """
        saved_rows = []
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                for idx, itm in enumerate(items, start=1):
                    bbox_json = (
                        json.dumps(itm.get("bounding_box_json"))
                        if itm.get("bounding_box_json")
                        else None
                    )
                    params = (
                        takeoff_id,
                        itm.get("item_order", idx),
                        itm.get("wbs_code", f"1.{idx}"),
                        itm.get("norm_code"),
                        itm.get("item_name", "Hạng mục công tác"),
                        itm.get("category", "general"),
                        itm.get("dimension_formula"),
                        itm.get("unit", "m3"),
                        itm.get("quantity", 0.0),
                        itm.get("unit_price_vnd", 0.0),
                        itm.get("total_amount_vnd", 0.0),
                        itm.get("confidence_score", 90),
                        bbox_json,
                        itm.get("notes"),
                    )
                    cur.execute(sql, params)
                    row = cur.fetchone()
                    saved_rows.append(dict(row))
                conn.commit()

        return saved_rows

    def list_drawing_takeoff_items(self, takeoff_id: str) -> list[dict[str, Any]]:
        """List all BoQ line items extracted for a specific takeoff."""
        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,))
            rows = cur.fetchall()
            return [dict(r) for r in rows]

    def delete_drawing_takeoff_items(self, takeoff_id: str) -> int:
        """Delete all BoQ items for a drawing takeoff."""
        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 sync extracted BoQ items to project WBS schedule tree."""
        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
        from datetime import date

        base_date = date.today()

        with self.get_connection() as conn, 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))

                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
                        )
                        ON CONFLICT (project_id, wbs_code) DO UPDATE
                        SET task_name = EXCLUDED.task_name,
                            unit = EXCLUDED.unit,
                            quantity = EXCLUDED.quantity,
                            unit_price_vnd = EXCLUDED.unit_price_vnd,
                            total_amount_vnd = EXCLUDED.total_amount_vnd,
                            updated_at = NOW();
                    """
                cur.execute(
                    wbs_sql,
                    (
                        project_id,
                        wbs_code,
                        task_name,
                        unit,
                        qty,
                        price,
                        total_val,
                        base_date,
                        base_date,
                        idx + 1,
                    ),
                )
                synced_count += 1
            conn.commit()

        return {
            "synced_count": synced_count,
            "project_id": project_id,
            "message": f"Đã đồng bộ {synced_count} đầu việc vào WBS",
        }