from __future__ import annotations

"""Invoice KPI overview aggregations."""


import logging
from typing import Any

logger = logging.getLogger(__name__)


class InvoiceAnalyticsSummaryMixin:
    """Mixin for get_invoice_summary_kpis."""

    def get_invoice_summary_kpis(
        self,
        from_date: str | None = None,
        to_date: str | None = None,
        project_id: str | None = None,
    ) -> dict[str, Any]:
        """Lấy tổng hợp chỉ số tài chính và thuế VAT từ Sổ Hóa Đơn (hỗ trợ lọc theo thời gian và dự án)."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                where_clauses = ["status = 'valid'"]
                params: list[Any] = []
                if from_date:
                    where_clauses.append("issue_date >= %s")
                    params.append(from_date)
                if to_date:
                    where_clauses.append("issue_date <= %s")
                    params.append(to_date)
                if project_id:
                    where_clauses.append("matched_project_id = %s")
                    params.append(project_id)

                base_where = " AND ".join(where_clauses)

                cur.execute(
                    f"""
                    SELECT
                        COUNT(*) AS count_input,
                        COALESCE(SUM(subtotal_amount_vnd), 0) AS subtotal_input,
                        COALESCE(SUM(vat_amount_vnd), 0) AS vat_input,
                        COALESCE(SUM(total_amount_vnd), 0) AS total_input
                    FROM erp_invoices
                    WHERE direction = 'input' AND {base_where};
                """,
                    params,
                )
                inp = cur.fetchone() or {
                    "count_input": 0,
                    "subtotal_input": 0,
                    "vat_input": 0,
                    "total_input": 0,
                }

                cur.execute(
                    f"""
                    SELECT
                        COUNT(*) AS count_output,
                        COALESCE(SUM(subtotal_amount_vnd), 0) AS subtotal_output,
                        COALESCE(SUM(vat_amount_vnd), 0) AS vat_output,
                        COALESCE(SUM(total_amount_vnd), 0) AS total_output
                    FROM erp_invoices
                    WHERE direction = 'output' AND {base_where};
                """,
                    params,
                )
                outp = cur.fetchone() or {
                    "count_output": 0,
                    "subtotal_output": 0,
                    "vat_output": 0,
                    "total_output": 0,
                }

                cur.execute(
                    f"""
                    SELECT COUNT(*) AS unreconciled_count
                    FROM erp_invoices
                    WHERE reconciliation_status = 'unreconciled' AND {base_where};
                """,
                    params,
                )
                unrec = cur.fetchone()["unreconciled_count"] if cur.rowcount > 0 else 0

                vat_deductible = float(inp["vat_input"])
                vat_payable = float(outp["vat_output"])
                net_vat_balance = vat_deductible - vat_payable

                return {
                    "total_input_invoices": int(inp["count_input"]),
                    "total_input_subtotal_vnd": float(inp["subtotal_input"]),
                    "total_input_vat_vnd": vat_deductible,
                    "total_input_amount_vnd": float(inp["total_input"]),
                    "total_output_invoices": int(outp["count_output"]),
                    "total_output_subtotal_vnd": float(outp["subtotal_output"]),
                    "total_output_vat_vnd": vat_payable,
                    "total_output_amount_vnd": float(outp["total_output"]),
                    "net_vat_balance_vnd": net_vat_balance,
                    "vat_status_label": "Còn được khấu trừ chuyển kỳ sau"
                    if net_vat_balance >= 0
                    else "Số thuế GTGT phải nộp ngân sách",
                    "unreconciled_invoices_count": int(unrec),
                }