from __future__ import annotations

import logging
from typing import Any

logger = logging.getLogger("dscons.invoice_truth.queries")


class TruthQueriesMixin:
    """Queries and analytics views for warehouse inventory and equipment truth."""

    def list_warehouse_inventory(
        self,
        material_group: str | None = None,
        status: str | None = None,
        search: str | None = None,
        limit: int = 50,
        offset: int = 0,
    ) -> Tuple[List[Dict[str, Any]], int]:
        """Fetch paginated warehouse inventory items with advanced filtering."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                where_clauses = ["1=1"]
                params: List[Any] = []

                if material_group:
                    where_clauses.append("w.material_group = %s")
                    params.append(material_group)

                if status:
                    where_clauses.append("w.status = %s")
                    params.append(status)

                if search:
                    s_like = f"%{search.strip()}%"
                    where_clauses.append(
                        "(w.item_name ILIKE %s OR w.item_code ILIKE %s OR w.supplier_name ILIKE %s OR w.invoice_series_number ILIKE %s)"
                    )
                    params.extend([s_like, s_like, s_like, s_like])

                where_sql = " AND ".join(where_clauses)

                # Total count
                cur.execute(
                    f"SELECT COUNT(*) as total FROM erp_warehouse_inventory w WHERE {where_sql};",
                    params,
                )
                total_cnt = cur.fetchone()["total"]

                # Fetch rows
                query = f"""
                    SELECT 
                        w.id, w.item_code, w.item_name, w.material_group,
                        w.unit, w.quantity_received, w.quantity_remaining,
                        w.unit_price_vnd, w.total_value_vnd,
                        w.supplier_name, w.supplier_tax_code,
                        w.source_invoice_id, w.invoice_series_number,
                        w.receipt_date, w.warehouse_location, w.status,
                        w.allocated_project_id, w.notes,
                        p.project_name as allocated_project_name,
                        p.project_code as allocated_project_code
                    FROM erp_warehouse_inventory w
                    LEFT JOIN projects p ON w.allocated_project_id = p.id
                    WHERE {where_sql}
                    ORDER BY w.receipt_date DESC, w.created_at DESC
                    LIMIT %s OFFSET %s;
                """
                params.extend([limit, offset])
                cur.execute(query, params)
                rows = cur.fetchall()

                return [dict(r) for r in rows], total_cnt

    def list_truth_equipment(self) -> List[Dict[str, Any]]:
        """Fetch all owned equipment extracted directly from real purchase invoices."""
        with self.get_connection() as conn, conn.cursor() as cur:
            cur.execute("""
                    SELECT 
                        e.id, e.equipment_code, e.equipment_name, e.equipment_type,
                        e.license_plate,
                        e.brand_model, e.ownership_type, e.hourly_rate_standard,
                        e.fuel_norm_per_hour, e.status, e.purchase_cost_vnd,
                        e.purchase_invoice_series_number, e.supplier_name,
                        e.source_invoice_id, e.notes, e.created_at,
                        p.project_name as current_project_name,
                        p.project_code as current_project_code
                    FROM erp_equipment e
                    LEFT JOIN projects p ON e.current_project_id = p.id
                    WHERE e.ownership_type = 'owned'
                    ORDER BY e.purchase_cost_vnd DESC, e.created_at DESC;
                """)
            rows = cur.fetchall()
            return [dict(r) for r in rows]

    def get_truth_summary(self) -> Dict[str, Any]:
        """Aggregate summary metrics of Truth equipment and warehouse materials."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                # Equipment stats
                cur.execute("""
                    SELECT 
                        COUNT(*) as total_owned_equipment,
                        COALESCE(SUM(purchase_cost_vnd), 0) as total_equipment_value_vnd
                    FROM erp_equipment
                    WHERE ownership_type = 'owned';
                """)
                eq_stat = cur.fetchone()

                # Warehouse inventory stats
                cur.execute("""
                    SELECT 
                        COUNT(*) as total_material_lots,
                        COALESCE(SUM(total_value_vnd), 0) as total_warehouse_value_vnd,
                        COUNT(*) FILTER (WHERE status = 'CHỜ_XUẤT_CÔNG_TRƯỜNG') as pending_dispatch_count
                    FROM erp_warehouse_inventory;
                """)
                wh_stat = cur.fetchone()

                # Group breakdown
                cur.execute("""
                    SELECT material_group, COUNT(*) as count, COALESCE(SUM(total_value_vnd), 0) as total_value
                    FROM erp_warehouse_inventory
                    GROUP BY material_group
                    ORDER BY total_value DESC;
                """)
                group_breakdown = cur.fetchall()

                return {
                    "total_owned_equipment": eq_stat["total_owned_equipment"],
                    "total_equipment_value_vnd": float(
                        eq_stat["total_equipment_value_vnd"]
                    ),
                    "total_material_lots": wh_stat["total_material_lots"],
                    "total_warehouse_value_vnd": float(
                        wh_stat["total_warehouse_value_vnd"]
                    ),
                    "pending_dispatch_count": wh_stat["pending_dispatch_count"],
                    "group_breakdown": [dict(g) for g in group_breakdown],
                }