from __future__ import annotations

"""Material analytics and unit price benchmarks."""


import logging
from typing import Any

logger = logging.getLogger(__name__)


class InvoiceAnalyticsCostMixin:
    """Mixin for material and cost analytics."""

    def get_material_and_cost_analytics(
        self,
        project_id: str | None = None,
        from_date: str | None = None,
        to_date: str | None = None,
    ) -> dict[str, Any]:
        """Phân tích phân rã chi phí vật tư chính, ca máy, thầu phụ và đơn giá bình quân gia quyền."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                where_clauses = ["i.direction = 'input'", "i.status = 'valid'"]
                params: list[Any] = []

                if project_id:
                    where_clauses.append("i.matched_project_id = %s")
                    params.append(project_id)
                if from_date:
                    where_clauses.append("i.issue_date >= %s")
                    params.append(from_date)
                if to_date:
                    where_clauses.append("i.issue_date <= %s")
                    params.append(to_date)

                where_sql = " AND ".join(where_clauses)

                # 1. Cost Breakdown by Category
                cur.execute(
                    f"""
                    SELECT
                        COALESCE(item.cost_category, 'other') AS cost_category,
                        COUNT(item.id) AS item_count,
                        COALESCE(SUM(item.amount_before_vat_vnd), 0) AS total_subtotal_vnd,
                        COALESCE(SUM(item.total_item_amount_vnd), 0) AS total_amount_vnd
                    FROM erp_invoice_items item
                    JOIN erp_invoices i ON item.invoice_id = i.id
                    WHERE {where_sql}
                    GROUP BY COALESCE(item.cost_category, 'other')
                    ORDER BY total_amount_vnd DESC;
                """,
                    params,
                )
                cat_rows = cur.fetchall()

                category_labels = {
                    "material_main": "Vật liệu chính (Thép, Xi măng, Bê tông, Cát, Đá)",
                    "machinery_labor": "Ca máy & Nhân công thi công",
                    "subcontractor": "Thầu phụ & Gói thầu chuyên biệt",
                    "general_expense": "Chi phí chung & Lán trại",
                    "other": "Chi phí khác",
                }

                categories_data = []
                total_all_spend = (
                    sum(float(r["total_amount_vnd"]) for r in cat_rows) or 1.0
                )

                for r in cat_rows:
                    cat_code = r["cost_category"]
                    tot_vnd = float(r["total_amount_vnd"])
                    sub_vnd = float(r["total_subtotal_vnd"])
                    pct = round((tot_vnd / total_all_spend) * 100.0, 2)
                    categories_data.append(
                        {
                            "category_code": cat_code,
                            "category_name": category_labels.get(cat_code, cat_code),
                            "item_count": int(r["item_count"]),
                            "subtotal_vnd": sub_vnd,
                            "total_amount_vnd": tot_vnd,
                            "percentage": pct,
                        }
                    )

                # 2. Material Pricing & Quantity Grouping (Thép, Bê tông, Xi măng...)
                cur.execute(
                    f"""
                    SELECT
                        item.item_name,
                        item.unit,
                        COALESCE(item.cost_category, 'other') AS cost_category,
                        SUM(item.quantity) AS total_qty,
                        SUM(item.amount_before_vat_vnd) AS total_subtotal,
                        SUM(item.total_item_amount_vnd) AS total_spent
                    FROM erp_invoice_items item
                    JOIN erp_invoices i ON item.invoice_id = i.id
                    WHERE {where_sql} AND item.quantity > 0
                    GROUP BY item.item_name, item.unit, COALESCE(item.cost_category, 'other')
                    ORDER BY total_spent DESC
                    LIMIT 20;
                """,
                    params,
                )
                top_items_rows = cur.fetchall()

                top_materials = []
                for r in top_items_rows:
                    qty = float(r["total_qty"])
                    sub = float(r["total_subtotal"])
                    avg_price = (sub / qty) if qty > 0 else 0.0
                    top_materials.append(
                        {
                            "item_name": r["item_name"],
                            "unit": r["unit"],
                            "cost_category": r["cost_category"],
                            "total_quantity": qty,
                            "total_subtotal_vnd": sub,
                            "total_spent_vnd": float(r["total_spent"]),
                            "weighted_avg_unit_price_vnd": round(avg_price, 2),
                        }
                    )

                return {
                    "project_id": project_id,
                    "from_date": from_date,
                    "to_date": to_date,
                    "total_spend_vnd": total_all_spend
                    if total_all_spend != 1.0
                    else 0.0,
                    "categories_breakdown": categories_data,
                    "top_materials": top_materials,
                }