from __future__ import annotations

"""Invoice category summaries calculation."""


import logging
from typing import Any

logger = logging.getLogger(__name__)


class InvoiceAnalyticsCategoriesMixin:
    """Mixin for category breakdown."""

    def get_invoice_categories_summary(self) -> dict[str, Any]:
        """Thống kê tổng số lượng và giá trị hóa đơn theo từng phân nhóm nghiệp vụ."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT count(*) as total, sum(total_amount_vnd) as total_val FROM erp_invoices WHERE direction = 'input';"
                )
                all_r = cur.fetchone()

                cur.execute("""
                    SELECT count(DISTINCT i.id) as cnt, sum(i.total_amount_vnd) as total_val
                    FROM erp_invoices i
                    WHERE i.direction = 'input' AND (
                        EXISTS (
                            SELECT 1 FROM erp_invoice_items itm
                            WHERE itm.invoice_id = i.id AND (
                                itm.item_name ILIKE '%máy%' OR itm.item_name ILIKE '%đào%' OR itm.item_name ILIKE '%xúc%' OR
                                itm.item_name ILIKE '%tải%' OR itm.item_name ILIKE '%ben%' OR itm.item_name ILIKE '%hàn%' OR
                                itm.item_name ILIKE '%khoan%' OR itm.item_name ILIKE '%cắt%' OR itm.item_name ILIKE '%đầm%' OR
                                itm.item_name ILIKE '%nén khí%' OR itm.item_name ILIKE '%phát điện%' OR itm.item_name ILIKE '%uốn sắt%' OR
                                itm.item_name ILIKE '%volvo%' OR itm.item_name ILIKE '%komatsu%' OR itm.item_name ILIKE '%thaco%' OR
                                itm.item_name ILIKE '%howo%' OR itm.item_name ILIKE '%bosch%' OR itm.item_name ILIKE '%makita%' OR
                                itm.item_name ILIKE '%mikasa%' OR itm.item_name ILIKE '%jasic%' OR itm.item_name ILIKE '%riland%' OR
                                itm.item_name ILIKE '%conmec%' OR itm.item_name ILIKE '%denyo%' OR itm.item_name ILIKE '%ebara%'
                            )
                        ) OR
                        EXISTS (
                            SELECT 1 FROM erp_equipment eq
                            WHERE eq.purchase_invoice_id = i.id OR eq.source_invoice_id = i.id
                        ) OR
                        (
                            i.seller_name ILIKE '%MÁY CÔNG TRÌNH%' OR i.seller_name ILIKE '%THACO%' OR
                            i.seller_name ILIKE '%THIÊN KHANG%' OR i.seller_name ILIKE '%VELTECH%' OR
                            i.seller_name ILIKE '%EMC VIỆT NAM%' OR i.seller_name ILIKE '%ĐỨC ANH%' OR
                            i.seller_name ILIKE '%THIẾT BỊ ĐIỆN HẢI PHÒNG%'
                        )
                    );
                """)
                mach_r = cur.fetchone()

                cur.execute("""
                    SELECT count(DISTINCT i.id) as cnt, sum(i.total_amount_vnd) as total_val
                    FROM erp_invoices i
                    WHERE i.direction = 'input' AND (
                        EXISTS (
                            SELECT 1 FROM erp_invoice_items itm
                            WHERE itm.invoice_id = i.id AND (
                                itm.item_name ILIKE '%thép%' OR itm.item_name ILIKE '%sắt%' OR itm.item_name ILIKE '%xi măng%' OR
                                itm.item_name ILIKE '%cát%' OR itm.item_name ILIKE '%đá%' OR itm.item_name ILIKE '%bê tông%' OR
                                itm.item_name ILIKE '%gạch%' OR itm.item_name ILIKE '%ống%' OR itm.item_name ILIKE '%sơn%' OR
                                itm.item_name ILIKE '%inox%' OR itm.item_name ILIKE '%tôn%' OR itm.item_name ILIKE '%cọc%'
                            )
                        ) OR
                        (
                            i.seller_name ILIKE '%HÒA PHÁT%' OR i.seller_name ILIKE '%CHINFON%' OR
                            i.seller_name ILIKE '%BÍCH VÂN%' OR i.seller_name ILIKE '%LÂM CƯỜNG%' OR
                            i.seller_name ILIKE '%ĐẠI VŨ%' OR i.seller_name ILIKE '%THÉP%' OR
                            i.seller_name ILIKE '%BÊ TÔNG%' OR i.seller_name ILIKE '%GẠCH%'
                        )
                    );
                """)
                mat_r = cur.fetchone()

                cur.execute("""
                    SELECT count(DISTINCT i.id) as cnt, sum(i.total_amount_vnd) as total_val
                    FROM erp_invoices i
                    WHERE i.direction = 'input' AND (
                        EXISTS (
                            SELECT 1 FROM erp_invoice_items itm
                            WHERE itm.invoice_id = i.id AND (
                                itm.item_name ILIKE '%xăng%' OR itm.item_name ILIKE '%dầu%' OR itm.item_name ILIKE '%diesel%' OR
                                itm.item_name ILIKE '%do 0.05%' OR itm.item_name ILIKE '%ron 95%' OR itm.item_name ILIKE '%nhớt%'
                            )
                        ) OR
                        (
                            i.seller_name ILIKE '%PETROLIMEX%' OR i.seller_name ILIKE '%PVOIL%' OR
                            i.seller_name ILIKE '%XĂNG DẦU%' OR i.seller_name ILIKE '%HFC%' OR
                            i.seller_name ILIKE '%HOÀNG PHÚC%'
                        )
                    );
                """)
                fuel_r = cur.fetchone()

                cur.execute("""
                    SELECT count(DISTINCT i.id) as cnt, sum(i.total_amount_vnd) as total_val
                    FROM erp_invoices i
                    WHERE i.direction = 'input' AND (
                        EXISTS (
                            SELECT 1 FROM erp_invoice_items itm
                            WHERE itm.invoice_id = i.id AND (
                                itm.item_name ILIKE '%thi công%' OR itm.item_name ILIKE '%thuê ca%' OR itm.item_name ILIKE '%bốc xúc%' OR
                                itm.item_name ILIKE '%vận chuyển%' OR itm.item_name ILIKE '%tư vấn%' OR itm.item_name ILIKE '%thí nghiệm%' OR
                                itm.item_name ILIKE '%nhân công%'
                            )
                        ) OR
                        (
                            i.seller_name ILIKE '%VẬN TẢI%' OR i.seller_name ILIKE '%TƯ VẤN%' OR
                            i.seller_name ILIKE '%THÍ NGHIỆM%' OR i.seller_name ILIKE '%DỊCH VỤ%'
                        )
                    );
                """)
                serv_r = cur.fetchone()

                cur.execute(
                    "SELECT count(*) as cnt, sum(purchase_cost_vnd) as total_asset_val FROM erp_equipment;"
                )
                eq_stat = cur.fetchone()

                return {
                    "all": {
                        "count": int(all_r["total"] or 0),
                        "total_amount_vnd": float(all_r["total_val"] or 0),
                    },
                    "machinery": {
                        "count": int(mach_r["cnt"] or 0),
                        "total_amount_vnd": float(mach_r["total_val"] or 0),
                    },
                    "materials": {
                        "count": int(mat_r["cnt"] or 0),
                        "total_amount_vnd": float(mat_r["total_val"] or 0),
                    },
                    "fuel": {
                        "count": int(fuel_r["cnt"] or 0),
                        "total_amount_vnd": float(fuel_r["total_val"] or 0),
                    },
                    "services": {
                        "count": int(serv_r["cnt"] or 0),
                        "total_amount_vnd": float(serv_r["total_val"] or 0),
                    },
                    "equipment_assets": {
                        "total_count": int(eq_stat["cnt"] or 0),
                        "total_purchase_cost_vnd": float(
                            eq_stat["total_asset_val"] or 0
                        ),
                    },
                }