from __future__ import annotations

import logging
from typing import Any

from app.core.postgres.base import BasePostgresClient

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


class PartnerQueriesMixin(BasePostgresClient):
    """Partner lookup, detail retrieval, and manual profile editing."""

    def get_partners(
        self,
        search: str | None = None,
        role_filter: str = "all",  # 'all', 'customer', 'partner', 'vendor'
        category_filter: str = "all",  # 'all', 'organization', 'individual'
        has_history: bool | None = None,
        page: int = 1,
        limit: int = 50,
    ) -> dict[str, Any]:
        """
        Lấy danh sách đối tác/khách hàng với phân trang và bộ lọc linh hoạt.
        """
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                where_clauses = ["1=1"]
                params: list[Any] = []

                if search:
                    s_clean = f"%{search.strip()}%"
                    where_clauses.append(
                        "(tax_code ILIKE %s OR company_name ILIKE %s OR current_address ILIKE %s OR representative_name ILIKE %s)"
                    )
                    params.extend([s_clean, s_clean, s_clean, s_clean])

                if role_filter == "customer":
                    where_clauses.append("is_customer = TRUE")
                elif role_filter == "partner":
                    where_clauses.append("is_partner = TRUE")
                elif role_filter == "vendor":
                    where_clauses.append("is_vendor = TRUE")

                if category_filter in ("organization", "individual"):
                    where_clauses.append("partner_category = %s")
                    params.append(category_filter)

                if has_history is True:
                    where_clauses.append("history_change_count > 0")

                where_sql = " AND ".join(where_clauses)

                # Count query
                cur.execute(
                    f"SELECT COUNT(*) as total FROM erp_partners WHERE {where_sql};",
                    tuple(params),
                )
                total_count = cur.fetchone()["total"]

                # Data query
                offset = (page - 1) * limit
                cur.execute(
                    f"""
                    SELECT 
                        id, tax_code, company_name, short_name, partner_category, current_address,
                        phone, email, representative_name,
                        is_customer, is_partner, is_vendor,
                        total_output_revenue_vnd, total_input_expense_vnd, total_invoices_count,
                        first_transaction_date, latest_transaction_date, history_change_count, status, notes
                    FROM erp_partners
                    WHERE {where_sql}
                    ORDER BY 
                        (total_output_revenue_vnd + total_input_expense_vnd) DESC,
                        total_invoices_count DESC,
                        latest_transaction_date DESC NULLS LAST
                    LIMIT %s OFFSET %s;
                """,
                    tuple(params + [limit, offset]),
                )

                rows = [dict(r) for r in cur.fetchall()]

                # KPI Summary
                cur.execute("""
                    SELECT 
                        COUNT(*) as total_all,
                        COUNT(*) FILTER (WHERE is_customer = TRUE) as total_customers,
                        COUNT(*) FILTER (WHERE is_partner = TRUE) as total_partners,
                        COUNT(*) FILTER (WHERE is_vendor = TRUE) as total_vendors,
                        COUNT(*) FILTER (WHERE history_change_count > 0) as total_with_history,
                        COALESCE(SUM(total_output_revenue_vnd), 0) as grand_total_output,
                        COALESCE(SUM(total_input_expense_vnd), 0) as grand_total_input
                    FROM erp_partners;
                """)
                kpis = dict(cur.fetchone())

                return {
                    "total": total_count,
                    "page": page,
                    "limit": limit,
                    "total_pages": (total_count + limit - 1) // limit
                    if limit > 0
                    else 1,
                    "kpis": kpis,
                    "items": rows,
                }

    def get_partner_detail(self, partner_id: str) -> dict[str, Any] | None:
        """
        Lấy chi tiết đối tác, kèm toàn bộ lịch sử thay đổi tên/địa chỉ,
        danh sách hóa đơn liên quan và dự án liên kết.
        """
        with self.get_connection() as conn:
            with conn.cursor() as cur:
                # 1. Partner Profile
                cur.execute(
                    """
                    SELECT * FROM erp_partners WHERE id::text = %s OR tax_code = %s;
                """,
                    (partner_id, partner_id),
                )
                partner = cur.fetchone()
                if not partner:
                    return None

                p_dict = dict(partner)
                p_id = str(p_dict["id"])
                p_tax = p_dict["tax_code"]

                # 2. History Timeline
                cur.execute(
                    """
                    SELECT 
                        id, historical_name, historical_address,
                        source_invoice_id, source_invoice_number, source_invoice_series, recorded_date, created_at
                    FROM erp_partner_history
                    WHERE partner_id = %s
                    ORDER BY recorded_date ASC, source_invoice_number ASC;
                """,
                    (p_id,),
                )
                history_rows = [dict(h) for h in cur.fetchall()]
                p_dict["history"] = history_rows

                # 3. Related Invoices (Latest 50)
                cur.execute(
                    """
                    SELECT 
                        id, direction, invoice_number, invoice_series, issue_date,
                        seller_name, seller_tax_code, buyer_name, buyer_tax_code,
                        subtotal_amount_vnd, vat_amount_vnd, total_amount_vnd,
                        matched_project_id
                    FROM erp_invoices
                    WHERE seller_tax_code = %s OR buyer_tax_code = %s
                    ORDER BY issue_date DESC, invoice_number DESC
                    LIMIT 50;
                """,
                    (p_tax, p_tax),
                )
                invoices = [dict(i) for i in cur.fetchall()]
                p_dict["related_invoices"] = invoices

                # 4. Linked Projects
                cur.execute(
                    """
                    SELECT DISTINCT p.id, p.project_code, p.project_name, p.status, p.contract_value
                    FROM projects p
                    JOIN erp_invoices i ON i.matched_project_id = p.id
                    WHERE i.seller_tax_code = %s OR i.buyer_tax_code = %s;
                """,
                    (p_tax, p_tax),
                )
                projects = [dict(pr) for pr in cur.fetchall()]
                p_dict["linked_projects"] = projects

                return p_dict

    def update_partner(
        self, partner_id: str, updates: dict[str, Any]
    ) -> dict[str, Any] | None:
        """
        Cập nhật thông tin đối tác thủ công (số điện thoại, email, đại diện, ghi chú, vai trò đối tác).
        """
        with self.get_connection() as conn, conn.cursor() as cur:
            allowed_fields = [
                "short_name",
                "phone",
                "email",
                "representative_name",
                "is_partner",
                "status",
                "notes",
            ]
            set_clauses = []
            params: list[Any] = []

            for k, v in updates.items():
                if k in allowed_fields:
                    set_clauses.append(f"{k} = %s")
                    params.append(v)

            if not set_clauses:
                return self.get_partner_detail(partner_id)

            set_clauses.append("updated_at = NOW()")
            params.append(partner_id)

            cur.execute(
                f"""
                    UPDATE erp_partners
                    SET {", ".join(set_clauses)}
                    WHERE id = %s
                    RETURNING id;
                """,
                tuple(params),
            )
            conn.commit()

            return self.get_partner_detail(partner_id)