from __future__ import annotations

"""Invoice listing and multi-criteria filtering query mixin."""


import logging
from typing import Any

from .query_builder import build_list_invoices_filters
from .query_mapper import map_invoice_row

logger = logging.getLogger(__name__)


class InvoiceQueryListMixin:
    """Mixin for list_invoices query execution."""

    def list_invoices(
        self,
        direction: str | None = None,
        status: str | None = None,
        search: str | None = None,
        from_date: str | None = None,
        to_date: str | None = None,
        reconciliation_status: str | None = None,
        project_id: str | None = None,
        vat_rate: str | None = None,
        min_amount: float | None = None,
        max_amount: float | None = None,
        signature_valid: bool | None = None,
        invoice_series: str | None = None,
        sort_by: str = "issue_date",
        sort_order: str = "DESC",
        invoice_type_filter: str | None = None,
        category: str | None = None,
        limit: int = 50,
        offset: int = 0,
    ) -> tuple[list[dict[str, Any]], int]:
        """Lấy danh sách hóa đơn theo bộ lọc nâng cao chuẩn meInvoice/Excel kèm sắp xếp và phân trang."""
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                where_sql, params = build_list_invoices_filters(
                    direction=direction,
                    status=status,
                    search=search,
                    from_date=from_date,
                    to_date=to_date,
                    reconciliation_status=reconciliation_status,
                    project_id=project_id,
                    vat_rate=vat_rate,
                    min_amount=min_amount,
                    max_amount=max_amount,
                    signature_valid=signature_valid,
                    invoice_series=invoice_series,
                    invoice_type_filter=invoice_type_filter,
                    category=category,
                )

                count_sql = f"""
                    SELECT COUNT(*) AS total_count
                    FROM erp_invoices i
                    LEFT JOIN projects p ON i.matched_project_id = p.id
                    WHERE {where_sql};
                """
                cur.execute(count_sql, params)
                total_count = cur.fetchone()["total_count"] if cur.rowcount > 0 else 0

                safe_sort_map = {
                    "issue_date": "i.issue_date",
                    "total_amount_vnd": "i.total_amount_vnd",
                    "subtotal_amount_vnd": "i.subtotal_amount_vnd",
                    "vat_amount_vnd": "i.vat_amount_vnd",
                    "invoice_number": "i.invoice_number",
                    "invoice_series": "i.invoice_series",
                    "seller_name": "i.seller_name",
                    "buyer_name": "i.buyer_name",
                    "direction": "i.direction",
                    "signature_valid": "i.signature_valid",
                    "reconciliation_status": "i.reconciliation_status",
                    "created_at": "i.created_at",
                }
                order_column = safe_sort_map.get(sort_by, "i.issue_date")
                order_direction = (
                    "ASC" if str(sort_order).strip().upper() == "ASC" else "DESC"
                )

                sql = f"""
                    SELECT
                        i.id,
                        i.direction,
                        i.invoice_type,
                        i.invoice_number,
                        i.invoice_series,
                        i.template_code,
                        i.issue_date,
                        i.seller_tax_code,
                        i.seller_name,
                        i.buyer_tax_code,
                        i.buyer_name,
                        i.subtotal_amount_vnd,
                        i.vat_rate_percent,
                        i.vat_amount_vnd,
                        i.total_amount_vnd,
                        i.amount_in_words,
                        i.status,
                        i.tax_authority_code,
                        i.signature_valid,
                        i.signed_by,
                        i.signed_at,
                        i.source_channel,
                        i.reconciliation_status,
                        i.matched_project_id,
                        p.project_name AS matched_project_name,
                        p.project_code AS matched_project_code,
                        i.payment_status,
                        i.payment_due_date,
                        i.remaining_amount_vnd,
                        i.created_at,
                        (SELECT COUNT(*) FROM erp_invoice_items WHERE invoice_id = i.id) AS item_count,
                        (SELECT string_agg(item_name, ' | ') FROM (SELECT item_name FROM erp_invoice_items WHERE invoice_id = i.id LIMIT 5) sub) AS item_names_summary
                    FROM erp_invoices i
                    LEFT JOIN projects p ON i.matched_project_id = p.id
                    WHERE {where_sql}
                    ORDER BY {order_column} {order_direction}, i.created_at DESC
                    LIMIT %s OFFSET %s;
                """
                query_params = list(params)
                query_params.extend([limit, offset])
                cur.execute(sql, query_params)
                rows = cur.fetchall()

                result = [map_invoice_row(self, r) for r in rows]
                return result, int(total_count)