from __future__ import annotations

"""Query filter and condition builders for invoice listing."""


from typing import Any


def build_list_invoices_filters(
    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,
    invoice_type_filter: str | None = None,
    category: str | None = None,
) -> tuple[str, list[Any]]:
    where_clauses = ["1=1"]
    params: list[Any] = []

    if direction:
        where_clauses.append("i.direction = %s")
        params.append(direction)
    if status:
        where_clauses.append("i.status = %s")
        params.append(status)
    if reconciliation_status:
        where_clauses.append("i.reconciliation_status = %s")
        params.append(reconciliation_status)
    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)
    if vat_rate:
        if str(vat_rate).lower() in ("kct", "none", "0-vat"):
            where_clauses.append(
                "(i.vat_amount_vnd = 0 AND (i.vat_rate_percent = 0 OR i.vat_rate_percent IS NULL))"
            )
        else:
            try:
                vnum = float(str(vat_rate).replace("%", "").strip())
                where_clauses.append("i.vat_rate_percent = %s")
                params.append(vnum)
            except Exception:
                pass
    if min_amount is not None and min_amount > 0:
        where_clauses.append("i.total_amount_vnd >= %s")
        params.append(min_amount)
    if max_amount is not None and max_amount > 0:
        where_clauses.append("i.total_amount_vnd <= %s")
        params.append(max_amount)
    if signature_valid is not None:
        where_clauses.append("i.signature_valid = %s")
        params.append(signature_valid)
    if invoice_series:
        where_clauses.append("i.invoice_series ILIKE %s")
        params.append(f"%{invoice_series.strip()}%")
    if invoice_type_filter:
        if invoice_type_filter in ("cash_register", "mtt"):
            where_clauses.append(
                "(i.invoice_series ILIKE 'K%%' OR i.invoice_series ILIKE '1K%%' OR i.invoice_series ILIKE '2K%%' OR i.invoice_series ILIKE 'M%%' OR i.invoice_series ILIKE '1M%%' OR i.invoice_series ILIKE '2M%%')"
            )
        elif invoice_type_filter in ("standard", "standard_vat"):
            where_clauses.append(
                "(i.invoice_series NOT ILIKE 'K%%' AND i.invoice_series NOT ILIKE '1K%%' AND i.invoice_series NOT ILIKE '2K%%' AND i.invoice_series NOT ILIKE 'M%%' AND i.invoice_series NOT ILIKE '1M%%' AND i.invoice_series NOT ILIKE '2M%%')"
            )

    if category and category != "all":
        cat_lower = category.strip().lower()
        if cat_lower in ("machinery", "equipment", "thiet_bi", "may_moc"):
            where_clauses.append("""(
                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%%' OR i.notes ILIKE '%%máy%%' OR
                    i.notes ILIKE '%%đào%%' OR i.notes ILIKE '%%xe tải%%' OR i.notes ILIKE '%%hàn%%' OR
                    i.notes ILIKE '%%khoan%%' OR i.notes ILIKE '%%cắt%%' OR i.notes ILIKE '%%đầm%%'
                )
            )""")
        elif cat_lower in ("materials", "vat_tu"):
            where_clauses.append("""(
                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%%'
                )
            )""")
        elif cat_lower in ("fuel", "xang_dau"):
            where_clauses.append("""(
                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%%'
                )
            )""")
        elif cat_lower in ("services", "dich_vu"):
            where_clauses.append("""(
                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Ụ%%'
                )
            )""")

    if search:
        search_like = f"%{search.strip()}%"
        where_clauses.append("""(
            i.invoice_number ILIKE %s OR
            i.invoice_series ILIKE %s OR
            i.seller_name ILIKE %s OR
            i.buyer_name ILIKE %s OR
            i.seller_tax_code ILIKE %s OR
            i.buyer_tax_code ILIKE %s OR
            p.project_name ILIKE %s OR
            p.project_code ILIKE %s
        )""")
        params.extend([search_like] * 8)

    return " AND ".join(where_clauses), params