# ============================================================
# core/ar_ap.py
# Báo cáo công nợ: AR (phải thu) + AP (phải trả) Aging
# CLI: python core/cli.py company CTY001 cong-no
# ============================================================

import sqlite3
import os
from datetime import datetime, date
from typing import Optional

try:
    import openpyxl
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    HAS_OPENPYXL = True
except ImportError:
    HAS_OPENPYXL = False


# ─── Aging buckets ─────────────────────────────────────────
AGING_BUCKETS = [
    (0,   30,  "0-30 ngày"),
    (31,  60,  "31-60 ngày"),
    (61,  90,  "61-90 ngày"),
    (91,  180, "91-180 ngày"),
    (181, 999, ">180 ngày"),
]


def calc_ar_ap(conn: sqlite3.Connection, ref_date: Optional[date] = None) -> dict:
    """
    Tính công nợ phải thu (AR - TK 131) và phải trả (AP - TK 331)
    theo từng đối tác. Không hỗ trợ aging chi tiết vì cần ngày đến hạn.
    """
    if ref_date is None:
        ref_date = date.today()

    c = conn.cursor()

    # ── AR: Phải thu khách hàng (TK 131) ───────────────────
    ar_rows = c.execute("""
        SELECT
            p.code,
            p.name,
            p.tax_code,
            COALESCE(SUM(je.debit - je.credit), 0) AS balance,
            MAX(v.voucher_date) AS last_tx
        FROM journal_entries je
        JOIN vouchers v ON v.id = je.voucher_id
        LEFT JOIN partners p ON p.id = v.partner_id
        WHERE je.account_code LIKE '131%'
          AND v.status = 'posted'
        GROUP BY v.partner_id
        HAVING ABS(COALESCE(SUM(je.debit - je.credit), 0)) > 0.01
        ORDER BY balance DESC
    """).fetchall()

    # ── AP: Phải trả nhà cung cấp (TK 331) ────────────────
    ap_rows = c.execute("""
        SELECT
            p.code,
            p.name,
            p.tax_code,
            COALESCE(SUM(je.credit - je.debit), 0) AS balance,
            MAX(v.voucher_date) AS last_tx
        FROM journal_entries je
        JOIN vouchers v ON v.id = je.voucher_id
        LEFT JOIN partners p ON p.id = v.partner_id
        WHERE je.account_code LIKE '331%'
          AND v.status = 'posted'
        GROUP BY v.partner_id
        HAVING ABS(COALESCE(SUM(je.credit - je.debit), 0)) > 0.01
        ORDER BY balance DESC
    """).fetchall()

    def age_days(last_tx_str):
        if not last_tx_str:
            return 999
        try:
            last_tx = datetime.strptime(last_tx_str[:10], "%Y-%m-%d").date()
            return (ref_date - last_tx).days
        except Exception:
            return 999

    def bucket_label(days):
        for lo, hi, label in AGING_BUCKETS:
            if lo <= days <= hi:
                return label
        return ">180 ngày"

    def enrich(rows):
        result = []
        for code, name, tax_code, bal, last_tx in rows:
            days = age_days(last_tx)
            result.append({
                "code": code or "—",
                "name": name or "Không rõ",
                "tax_code": tax_code or "",
                "balance": bal,
                "last_tx": last_tx,
                "age_days": days,
                "bucket": bucket_label(days),
            })
        return result

    ar_list = enrich(ar_rows)
    ap_list = enrich(ap_rows)

    return {
        "ref_date": ref_date.isoformat(),
        "ar": {
            "items": ar_list,
            "total": sum(x["balance"] for x in ar_list),
        },
        "ap": {
            "items": ap_list,
            "total": sum(x["balance"] for x in ap_list),
        },
    }


def print_ar_ap(data: dict) -> None:
    fmt = lambda x: f"{x:>18,.0f}"
    print(f"\n{'='*62}")
    print(f"  BÁO CÁO CÔNG NỢ — Ngày {data['ref_date']}")
    print(f"{'='*62}")

    def section(title, items, total):
        print(f"\n  📋 {title}")
        print(f"  {'Mã':<8} {'Tên đối tác':<25} {'Số dư':>14} {'Tuổi nợ':<14}")
        print(f"  {'─'*62}")
        for x in items:
            print(f"  {x['code']:<8} {x['name'][:24]:<25} {fmt(x['balance'])} {x['bucket']}")
        print(f"  {'─'*62}")
        print(f"  {'TỔNG':>34} {fmt(total)}")

    section("PHẢI THU KHÁCH HÀNG (AR — TK 131)", data["ar"]["items"], data["ar"]["total"])
    section("PHẢI TRẢ NHÀ CUNG CẤP (AP — TK 331)", data["ap"]["items"], data["ap"]["total"])
    net = data["ar"]["total"] - data["ap"]["total"]
    print(f"\n  NET (AR - AP): {fmt(net)} VND")
    print(f"{'='*62}\n")


def export_ar_ap_excel(data: dict, out_dir: str, company_name: str = "") -> str:
    if not HAS_OPENPYXL:
        raise ImportError("Cần cài openpyxl")

    os.makedirs(out_dir, exist_ok=True)
    fname = f"CongNo_{data['ref_date']}.xlsx"
    fpath = os.path.join(out_dir, fname)

    wb = openpyxl.Workbook()
    navy = "1F3864"; green_h = "375623"; red_h = "C55A11"
    thin = Side(style="thin", color="BFBFBF")
    bd   = Border(left=thin, right=thin, top=thin, bottom=thin)
    ca   = Alignment(horizontal="center")
    la   = Alignment(horizontal="left", indent=1)
    ra   = Alignment(horizontal="right")
    def fill(c): return PatternFill("solid", fgColor=c)

    def make_sheet(ws, title, color, items, total):
        ws.title = title[:31]
        ws.column_dimensions["A"].width = 10
        ws.column_dimensions["B"].width = 30
        ws.column_dimensions["C"].width = 16
        ws.column_dimensions["D"].width = 18
        ws.column_dimensions["E"].width = 14
        ws.column_dimensions["F"].width = 14

        ws.merge_cells("A1:F1")
        ws["A1"] = f"{title} — {company_name} | Ngày {data['ref_date']}"
        ws["A1"].font = Font(name="Arial", bold=True, size=12, color="FFFFFF")
        ws["A1"].fill = fill(color); ws["A1"].alignment = ca
        ws.row_dimensions[1].height = 26

        headers = ["Mã ĐT", "Tên đối tác", "MST", "Số dư (VND)", "Giao dịch cuối", "Tuổi nợ"]
        for col, h in enumerate(headers, 1):
            cell = ws.cell(row=3, column=col, value=h)
            cell.font = Font(name="Arial", bold=True, size=10, color="FFFFFF")
            cell.fill = fill(navy); cell.alignment = ca; cell.border = bd

        for r_idx, x in enumerate(items, 4):
            row_data = [x["code"], x["name"], x["tax_code"],
                        x["balance"], x["last_tx"] or "", x["bucket"]]
            for col, val in enumerate(row_data, 1):
                cell = ws.cell(row=r_idx, column=col, value=val)
                cell.font = Font(name="Arial", size=10)
                cell.alignment = ra if col == 4 else la
                cell.border = bd
                if col == 4:
                    cell.number_format = '#,##0'
                # Màu aging
                if col == 6 and ">180" in str(val):
                    cell.font = Font(name="Arial", size=10, bold=True, color="FF0000")
            ws.row_dimensions[r_idx].height = 16

        # Total row
        tot_row = len(items) + 4
        ws.merge_cells(f"A{tot_row}:C{tot_row}")
        ws[f"A{tot_row}"] = "TỔNG CỘNG"
        ws[f"A{tot_row}"].font = Font(name="Arial", bold=True, size=10)
        ws[f"A{tot_row}"].fill = fill("FFFF99"); ws[f"A{tot_row}"].border = bd
        cell_t = ws.cell(row=tot_row, column=4, value=total)
        cell_t.font = Font(name="Arial", bold=True, size=10)
        cell_t.fill = fill("FFFF99"); cell_t.border = bd
        cell_t.number_format = '#,##0'; cell_t.alignment = ra
        ws.freeze_panes = "A4"

    ws1 = wb.active
    make_sheet(ws1, "PHẢI THU (AR)", green_h, data["ar"]["items"], data["ar"]["total"])
    ws2 = wb.create_sheet()
    make_sheet(ws2, "PHẢI TRẢ (AP)", red_h, data["ap"]["items"], data["ap"]["total"])

    wb.save(fpath)
    return fpath


def run_ar_ap(db_path: str, out_dir: Optional[str] = None,
              company_name: str = "") -> dict:
    conn = sqlite3.connect(db_path)
    data = calc_ar_ap(conn)
    print_ar_ap(data)
    if out_dir:
        fpath = export_ar_ap_excel(data, out_dir, company_name)
        print(f"📄 Đã xuất: {fpath}")
    conn.close()
    return data
