"""PostgreSQL Repository for Debt Settlement and Bank Reconciliations."""

from __future__ import annotations

import logging
from decimal import Decimal
from typing import Any
from uuid import UUID

from app.core.postgres.base_pkg.base_client import BasePostgresClient
from app.modules.financial.domain.settlement_entities import (
    ClearingMethod,
    DebtSettlement,
    SettlementStatus,
)

logger = logging.getLogger("dscons.financial.settlement_repository")


class PostgresSettlementRepository:
    """Infrastructure repository managing database persistence for Debt Settlements."""

    def __init__(self, db_client: Any | None = None) -> None:
        self._client = db_client or BasePostgresClient()

    def _get_conn(self) -> Any:
        return getattr(self._client, "get_connection", None) or getattr(
            self._client, "_open_connection", None
        )

    def save_settlement(self, settlement: DebtSettlement) -> DebtSettlement:
        """Lưu trữ một bản ghi cấn trừ gạch nợ vào erp_debt_settlements."""
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                if settlement.invoice_id is None:
                    conflict_clause = "ON CONFLICT (bank_transaction_id) WHERE invoice_id IS NULL"
                else:
                    conflict_clause = "ON CONFLICT (bank_transaction_id, invoice_id)"

                sql = f"""
                    INSERT INTO erp_debt_settlements (
                        id, company_id, bank_transaction_id, invoice_id,
                        partner_id, partner_tax_code, partner_name, account_code,
                        settlement_amount_vnd, settlement_date, clearing_method,
                        confidence_score, status, notes, created_at
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                    {conflict_clause} DO UPDATE SET
                        settlement_amount_vnd = EXCLUDED.settlement_amount_vnd,
                        confidence_score = EXCLUDED.confidence_score,
                        status = EXCLUDED.status,
                        notes = EXCLUDED.notes
                    RETURNING id;
                """
                cur.execute(
                    sql,
                    (
                        str(settlement.id),
                        str(settlement.company_id),
                        str(settlement.bank_transaction_id),
                        str(settlement.invoice_id) if settlement.invoice_id else None,
                        str(settlement.partner_id) if settlement.partner_id else None,
                        settlement.partner_tax_code,
                        settlement.partner_name,
                        settlement.account_code,
                        settlement.settlement_amount_vnd,
                        settlement.settlement_date,
                        settlement.clearing_method.value
                        if isinstance(settlement.clearing_method, ClearingMethod)
                        else str(settlement.clearing_method),
                        settlement.confidence_score,
                        settlement.status.value
                        if isinstance(settlement.status, SettlementStatus)
                        else str(settlement.status),
                        settlement.notes,
                        settlement.created_at,
                    ),
                )
                conn.commit()
        return settlement

    def update_invoice_settlement(
        self, invoice_id: UUID, settlement_amount: Decimal
    ) -> dict[str, Any]:
        """Cập nhật số dư nợ còn lại và trạng thái thanh toán của hóa đơn."""
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    SELECT id, total_amount_vnd, COALESCE(remaining_amount_vnd, total_amount_vnd) AS remaining_amount_vnd, payment_status
                    FROM erp_invoices
                    WHERE id = %s
                    FOR UPDATE;
                    """,
                    (str(invoice_id),),
                )
                row = cur.fetchone()
                if not row:
                    raise ValueError(f"Hóa đơn {invoice_id} không tồn tại.")

                total = Decimal(str(row["total_amount_vnd"] or 0))
                current_rem = Decimal(str(row["remaining_amount_vnd"] or 0))

                new_remaining = max(Decimal("0"), current_rem - settlement_amount)
                if new_remaining == Decimal("0"):
                    new_status = "settled"
                else:
                    new_status = "partially_paid"

                cur.execute(
                    """
                    UPDATE erp_invoices
                    SET remaining_amount_vnd = %s,
                        payment_status = %s,
                        reconciliation_status = 'reconciled',
                        updated_at = NOW()
                    WHERE id = %s
                    RETURNING id, remaining_amount_vnd, payment_status;
                    """,
                    (new_remaining, new_status, str(invoice_id)),
                )
                res = dict(cur.fetchone())
                conn.commit()
                return res

    def link_bank_transaction_invoice(
        self, transaction_id: UUID, invoice_id: UUID
    ) -> None:
        """Cập nhật matched_invoice_id trên giao dịch ngân hàng."""
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    UPDATE erp_bank_transactions
                    SET matched_invoice_id = %s
                    WHERE id = %s;
                    """,
                    (str(invoice_id), str(transaction_id)),
                )
                conn.commit()

    def get_unreconciled_transactions(
        self, limit: int = 300, direction: str | None = None
    ) -> list[dict[str, Any]]:
        """Lấy danh sách các giao dịch ngân hàng chưa được gạch nợ hoàn tất."""
        query = """
            SELECT b.id, b.company_id, b.bank_name, b.account_number, b.transaction_date,
                   b.reference_number, b.direction, b.amount, b.counterparty_name,
                   b.description, b.transaction_category, b.matched_invoice_id
            FROM erp_bank_transactions b
            LEFT JOIN (
                SELECT bank_transaction_id, SUM(settlement_amount_vnd) AS total_settled
                FROM erp_debt_settlements
                WHERE status = 'settled'
                GROUP BY bank_transaction_id
            ) s ON b.id = s.bank_transaction_id
            WHERE (s.total_settled IS NULL OR s.total_settled < b.amount)
              AND b.transaction_category NOT IN ('INTERNAL_TRANSFER', 'OWNER_DRAW_CAPITAL')
        """
        params: list[Any] = []
        if direction:
            query += " AND b.direction = %s"
            params.append(direction)
        query += " ORDER BY b.transaction_date ASC LIMIT %s;"
        params.append(limit)

        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(query, tuple(params))
                return [dict(r) for r in cur.fetchall()]

    def get_unpaid_invoices(
        self, direction: str = "input", limit: int = 1500
    ) -> list[dict[str, Any]]:
        """Lấy danh sách hóa đơn còn nợ (unpaid hoặc partially_paid)."""
        dir_val = "input" if direction in ("input", "inbound") else "output"
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    SELECT id, direction, invoice_number, invoice_series,
                           issue_date, seller_tax_code, seller_name, buyer_tax_code,
                           buyer_name, total_amount_vnd,
                           COALESCE(remaining_amount_vnd, total_amount_vnd) AS remaining_amount_vnd,
                           payment_status
                    FROM erp_invoices
                    WHERE direction = %s
                      AND (payment_status IN ('unpaid', 'partially_paid') OR payment_status IS NULL)
                      AND COALESCE(remaining_amount_vnd, total_amount_vnd) > 0
                    ORDER BY issue_date ASC
                    LIMIT %s;
                    """,
                    (dir_val, limit),
                )
                return [dict(r) for r in cur.fetchall()]

    def get_settlements(
        self,
        status: str | None = None,
        partner_tax_code: str | None = None,
        limit: int = 100,
        offset: int = 0,
    ) -> list[dict[str, Any]]:
        """Tra cứu danh sách các bản ghi gạch nợ."""
        query = """
            SELECT s.id, s.company_id, s.bank_transaction_id, s.invoice_id,
                   s.partner_id, s.partner_tax_code, s.partner_name, s.account_code,
                   s.settlement_amount_vnd, s.settlement_date, s.clearing_method,
                   s.confidence_score, s.status, s.notes, s.created_at,
                   b.bank_name, b.reference_number, b.description AS tx_description,
                   i.invoice_number, i.total_amount_vnd AS invoice_total_amount,
                   i.remaining_amount_vnd AS invoice_remaining_amount
            FROM erp_debt_settlements s
            LEFT JOIN erp_bank_transactions b ON s.bank_transaction_id = b.id
            LEFT JOIN erp_invoices i ON s.invoice_id = i.id
            WHERE 1=1
        """
        params: list[Any] = []
        if status:
            query += " AND s.status = %s"
            params.append(status)
        if partner_tax_code:
            query += " AND s.partner_tax_code = %s"
            params.append(partner_tax_code)

        query += " ORDER BY s.created_at DESC LIMIT %s OFFSET %s;"
        params.extend([limit, offset])

        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(query, tuple(params))
                return [dict(r) for r in cur.fetchall()]

    def reverse_settlement(self, settlement_id: UUID) -> dict[str, Any]:
        """Hủy bút toán cấn trừ gạch nợ và phục hồi số dư hóa đơn."""
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                cur.execute(
                    """
                    SELECT id, invoice_id, bank_transaction_id, settlement_amount_vnd, status
                    FROM erp_debt_settlements
                    WHERE id = %s
                    FOR UPDATE;
                    """,
                    (str(settlement_id),),
                )
                settle = cur.fetchone()
                if not settle:
                    raise ValueError(f"Bản ghi cấn trừ {settlement_id} không tồn tại.")

                if settle["status"] == "reversed":
                    return {"status": "already_reversed", "id": str(settlement_id)}

                settle_amount = Decimal(str(settle["settlement_amount_vnd"]))
                invoice_id = settle["invoice_id"]
                tx_id = settle["bank_transaction_id"]

                # 1. Khôi phục remaining_amount_vnd trên hóa đơn
                if invoice_id:
                    cur.execute(
                        """
                        SELECT total_amount_vnd, COALESCE(remaining_amount_vnd, 0) AS remaining_amount_vnd
                        FROM erp_invoices
                        WHERE id = %s
                        FOR UPDATE;
                        """,
                        (str(invoice_id),),
                    )
                    inv = cur.fetchone()
                    if inv:
                        tot = Decimal(str(inv["total_amount_vnd"]))
                        rem = Decimal(str(inv["remaining_amount_vnd"]))
                        restored_rem = min(tot, rem + settle_amount)
                        new_status = "unpaid" if restored_rem == tot else "partially_paid"
                        cur.execute(
                            """
                            UPDATE erp_invoices
                            SET remaining_amount_vnd = %s,
                                payment_status = %s,
                                updated_at = NOW()
                            WHERE id = %s;
                            """,
                            (restored_rem, new_status, str(invoice_id)),
                        )

                # 2. Cập nhật trạng thái bản ghi settlement -> reversed
                cur.execute(
                    """
                    UPDATE erp_debt_settlements
                    SET status = 'reversed',
                        notes = COALESCE(notes, '') || ' [Đã hủy cấn trừ lúc ' || NOW()::text || ']'
                    WHERE id = %s;
                    """,
                    (str(settlement_id),),
                )

                # 3. Kiểm tra xem giao dịch ngân hàng còn settlement nào active không
                cur.execute(
                    """
                    SELECT COUNT(*) AS active_count
                    FROM erp_debt_settlements
                    WHERE bank_transaction_id = %s AND status = 'settled';
                    """,
                    (str(tx_id),),
                )
                if cur.fetchone()["active_count"] == 0:
                    cur.execute(
                        """
                        UPDATE erp_bank_transactions
                        SET matched_invoice_id = NULL
                        WHERE id = %s;
                        """,
                        (str(tx_id),),
                    )

                conn.commit()
                return {
                    "status": "success",
                    "message": f"Đã hủy bút toán gạch nợ {settlement_id} thành công.",
                    "id": str(settlement_id),
                }

    def get_clearing_summary(self) -> dict[str, Any]:
        """Tổng hợp số liệu thống kê gạch nợ toàn hệ thống."""
        get_conn = self._get_conn()
        with get_conn() as conn:
            with conn.cursor() as cur:
                # 1. Tổng tiền và số lượng đã gạch nợ
                cur.execute(
                    """
                    SELECT account_code,
                           COUNT(*) AS count_settlements,
                           COALESCE(SUM(settlement_amount_vnd), 0) AS total_settled_amount
                    FROM erp_debt_settlements
                    WHERE status = 'settled'
                    GROUP BY account_code;
                    """
                )
                by_account = {r["account_code"]: dict(r) for r in cur.fetchall()}

                # 2. Thống kê hóa đơn theo trạng thái thanh toán
                cur.execute(
                    """
                    SELECT direction,
                           COALESCE(payment_status, 'unpaid') AS payment_status,
                           COUNT(*) AS count_invoices,
                           COALESCE(SUM(total_amount_vnd), 0) AS total_amount,
                           COALESCE(SUM(remaining_amount_vnd), 0) AS total_remaining
                    FROM erp_invoices
                    GROUP BY direction, payment_status;
                    """
                )
                invoices_breakdown = [dict(r) for r in cur.fetchall()]

                # 3. Số giao dịch ngân hàng chưa đối soát
                cur.execute(
                    """
                    SELECT direction, COUNT(*) AS count_tx, COALESCE(SUM(amount), 0) AS total_amount
                    FROM erp_bank_transactions
                    WHERE matched_invoice_id IS NULL
                      AND transaction_category NOT IN ('INTERNAL_TRANSFER', 'OWNER_DRAW_CAPITAL')
                    GROUP BY direction;
                    """
                )
                unreconciled_tx = [dict(r) for r in cur.fetchall()]

                return {
                    "settlements_by_account": by_account,
                    "invoices_breakdown": invoices_breakdown,
                    "unreconciled_transactions": unreconciled_tx,
                }