# ============================================================
# core/reports/trial_balance.py
# Xuất Bảng Cân Đối Số Phát Sinh → Excel
# ============================================================

import os
from datetime import datetime
from core.ledger import get_trial_balance, get_period_summary
from core.reports.excel_base import (
    new_workbook, write_title, write_header_row,
    write_data_row, write_total_row, write_subtotal_row,
    auto_width, freeze_header, fmt_vnd_zero, thin_border,
    make_normal_font
)
from openpyxl.styles import Alignment, Font, PatternFill


def export_trial_balance(db_path: str, year: int, month: int,
                         company_name: str = "", output_path: str = None) -> str:
    """
    Xuất Bảng CĐSP ra file Excel.
    Returns: đường dẫn file xuất.
    """
    rows = get_trial_balance(db_path, year, month)
    summary = get_period_summary(db_path, year, month)

    wb = new_workbook()
    ws = wb.create_sheet("BCĐSP")
    ws.sheet_view.showGridLines = False
    ws.freeze_panes = "A5"

    # Tiêu đề
    ws.merge_cells("A1:H1")
    ws["A1"] = f"BẢNG CÂN ĐỐI SỐ PHÁT SINH"
    ws["A1"].font = Font(name="Arial", bold=True, size=14, color="1F4E79")
    ws["A1"].alignment = Alignment(horizontal="center")

    ws.merge_cells("A2:H2")
    ws["A2"] = f"Kỳ: Tháng {month:02d}/{year}   |   {company_name}"
    ws["A2"].font = Font(name="Arial", size=10, color="444444")
    ws["A2"].alignment = Alignment(horizontal="center")

    ws.merge_cells("A3:H3")
    ws["A3"] = f"Xuất lúc: {datetime.now().strftime('%d/%m/%Y %H:%M')}   |   Trạng thái kỳ: {summary.get('period_status','open').upper()}"
    ws["A3"].font = Font(name="Arial", size=9, italic=True, color="888888")
    ws["A3"].alignment = Alignment(horizontal="center")

    # Header
    headers = ["Mã TK", "Tên tài khoản", "Loại", "Đầu kỳ Nợ", "Đầu kỳ Có",
               "PS Nợ", "PS Có", "Cuối kỳ Nợ", "Cuối kỳ Có"]
    write_header_row(ws, 4, headers)

    num_cols = {3, 4, 5, 6, 7, 8}  # 0-indexed từ 0

    tot_ob_dr = tot_ob_cr = 0
    tot_ps_dr = tot_ps_cr = 0
    tot_cl_dr = tot_cl_cr = 0

    for i, r in enumerate(rows, start=5):
        values = [
            r["code"], r["name"][:40], r["type"],
            r["opening_debit"] or None, r["opening_credit"] or None,
            r["period_debit"] or None,  r["period_credit"] or None,
            r["closing_debit"] or None, r["closing_credit"] or None,
        ]
        write_data_row(ws, i, values, number_cols=list(num_cols), alt=i % 2 == 0)
        tot_ob_dr += r["opening_debit"]
        tot_ob_cr += r["opening_credit"]
        tot_ps_dr += r["period_debit"]
        tot_ps_cr += r["period_credit"]
        tot_cl_dr += r["closing_debit"]
        tot_cl_cr += r["closing_credit"]

    total_row = len(rows) + 5
    write_total_row(ws, total_row, [
        "TỔNG CỘNG", "", "",
        tot_ob_dr or None, tot_ob_cr or None,
        tot_ps_dr or None, tot_ps_cr or None,
        tot_cl_dr or None, tot_cl_cr or None,
    ])

    # Column widths
    ws.column_dimensions["A"].width = 10
    ws.column_dimensions["B"].width = 40
    ws.column_dimensions["C"].width = 12
    for col in ["D","E","F","G","H","I"]:
        ws.column_dimensions[col].width = 18

    # Format số VND
    for row in ws.iter_rows(min_row=5, min_col=4, max_col=9):
        for cell in row:
            cell.number_format = fmt_vnd_zero

    if not output_path:
        db_dir = os.path.dirname(db_path)
        export_dir = os.path.join(db_dir, "..", "exports", "bctc")
        os.makedirs(export_dir, exist_ok=True)
        output_path = os.path.join(export_dir, f"BDSP_{year}{month:02d}.xlsx")

    wb.save(output_path)
    print(f"✅ Đã xuất BĐSP: {output_path}")
    return output_path


def export_ledger_card(db_path: str, account_code: str,
                       year: int, month: int,
                       company_name: str = "",
                       output_path: str = None) -> str:
    """Xuất Sổ Cái 1 tài khoản → Excel."""
    from core.ledger import get_ledger_card, get_account_balance
    from core.db_init import get_conn

    conn = get_conn(db_path)
    acc_info = conn.execute(
        "SELECT name, type FROM accounts WHERE code=?", (account_code,)
    ).fetchone()
    conn.close()

    acc_name = acc_info[0] if acc_info else account_code
    entries = get_ledger_card(db_path, account_code, year, month)

    wb = new_workbook()
    ws = wb.create_sheet(f"SC_{account_code}")
    ws.sheet_view.showGridLines = False

    ws.merge_cells("A1:G1")
    ws["A1"] = f"SỔ CÁI TÀI KHOẢN {account_code} — {acc_name.upper()}"
    ws["A1"].font = Font(name="Arial", bold=True, size=13, color="1F4E79")
    ws["A1"].alignment = Alignment(horizontal="center")

    ws.merge_cells("A2:G2")
    ws["A2"] = f"Tháng {month:02d}/{year}   |   {company_name}"
    ws["A2"].font = Font(name="Arial", size=10)
    ws["A2"].alignment = Alignment(horizontal="center")

    headers = ["Ngày", "Số CT", "Loại CT", "Diễn giải", "Nợ", "Có", "Số dư"]
    write_header_row(ws, 4, headers)

    for i, e in enumerate(entries, start=5):
        write_data_row(ws, i, [
            e["voucher_date"], e["voucher_no"], e["voucher_type"],
            (e.get("description") or "")[:60],
            e["debit"] or None, e["credit"] or None,
            e.get("running_balance"),
        ], number_cols=[4, 5, 6], alt=i % 2 == 0)

    ws.column_dimensions["A"].width = 12
    ws.column_dimensions["B"].width = 14
    ws.column_dimensions["C"].width = 10
    ws.column_dimensions["D"].width = 45
    for col in ["E","F","G"]:
        ws.column_dimensions[col].width = 18

    for row in ws.iter_rows(min_row=5, min_col=5, max_col=7):
        for cell in row:
            cell.number_format = fmt_vnd_zero

    if not output_path:
        db_dir = os.path.dirname(db_path)
        export_dir = os.path.join(db_dir, "..", "exports", "bctc")
        os.makedirs(export_dir, exist_ok=True)
        output_path = os.path.join(export_dir, f"SoCai_{account_code}_{year}{month:02d}.xlsx")

    wb.save(output_path)
    print(f"✅ Đã xuất Sổ Cái TK{account_code}: {output_path}")
    return output_path
