import os
import sqlite3
from datetime import datetime
from typing import Dict, Optional, List
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side


LEGACY_CATEGORY_LABELS = {
    "ban_hang": "Bán hàng",
    "dich_vu": "Dịch vụ",
    "khac": "Khác",
}


def export_s1a_hkd_revenue(
    year: int,
    month: Optional[int] = None,
    quarter: Optional[int] = None,
    output_path: Optional[str] = None,
    db_path: Optional[str] = None,
) -> str:
    """Xuất Sổ Doanh Thu Mẫu số S1a-HKD (3 cột: Ngày tháng, Diễn giải, Số tiền)."""
    from core.revenue_db import get_entries_by_month, CATEGORIES, get_revenue_db_path

    if db_path is None:
        db_path = get_revenue_db_path()

    if quarter and not month:
        months = [quarter * 3 - 2, quarter * 3 - 1, quarter * 3]
    elif month:
        months = [month]
    else:
        months = list(range(1, 13))

    entries: list[dict] = []
    for m in months:
        entries.extend(get_entries_by_month(year, m, db_path=db_path))

    entries.sort(key=lambda e: (e.get("entry_date") or "", e.get("id") or 0))

    wb = Workbook()
    ws = wb.active
    ws.title = "S1a-HKD"

    settings_info = {}
    try:
        import json
        from pathlib import Path
        settings_path = Path(__file__).resolve().parents[3] / "settings.json"
        if settings_path.exists():
            settings_info = json.loads(settings_path.read_text(encoding="utf-8"))
    except Exception:
        settings_info = {}

    bold_font = Font(name="Times New Roman", size=12, bold=True)
    head_font = Font(name="Times New Roman", size=11, bold=True)
    body_font = Font(name="Times New Roman", size=11)
    center = Alignment(horizontal="center", vertical="center")
    left_wrap = Alignment(horizontal="left", vertical="center", wrap_text=True)
    right = Alignment(horizontal="right", vertical="center")
    thin = Side(style="thin")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    ws.merge_cells("A1:C1")
    ws["A1"] = "SỔ CHI TIẾT DOANH THU BÁN HÀNG HÓA, DỊCH VỤ (MẪU S1a-HKD)"
    ws["A1"].font = bold_font
    ws["A1"].alignment = center

    ws.merge_cells("A2:C2")
    if month:
        ws["A2"] = f"Kỳ báo cáo: Tháng {month:02d}/{year}"
    elif quarter:
        ws["A2"] = f"Kỳ báo cáo: Quý {quarter}/{year}"
    else:
        ws["A2"] = f"Kỳ báo cáo: Năm {year}"
    ws["A2"].alignment = center

    ws["A4"] = "Ngày tháng"
    ws["B4"] = "Diễn giải"
    ws["C4"] = "Số tiền"
    for c in ("A4", "B4", "C4"):
        ws[c].font = head_font
        ws[c].alignment = center
        ws[c].border = border

    row_idx = 5
    total = 0.0
    for e in entries:
        raw_date = str(e.get("entry_date") or "")
        try:
            dt_obj = datetime.strptime(raw_date, "%Y-%m-%d")
            date_text = f"{dt_obj.month}/{dt_obj.day}/{dt_obj.year}"
        except Exception:
            date_text = raw_date

        category = str(e.get("category") or "")
        amount = float(e.get("amount") or 0.0)
        if category.startswith("thu_tra_hang"):
            amount = -amount

        partner_name = str(e.get("partner_name") or "").strip()
        invoice_code = str(e.get("invoice_code") or "").strip()

        category_label = CATEGORIES.get(category, LEGACY_CATEGORY_LABELS.get(category, category)).strip()
        if category_label and " " in category_label and not category_label[0].isalnum():
            # Chỉ bỏ token đầu nếu đó là emoji/ký hiệu.
            category_label = category_label.split(" ", 1)[1]

        desc_parts = [category_label] if category_label else []

        if partner_name:
            desc_parts.append(f"khách hàng {partner_name}")

        if invoice_code:
            desc_parts.append(f"- {invoice_code}")

        desc = " ".join(p for p in desc_parts if p).strip()

        ws.cell(row=row_idx, column=1, value=date_text).font = body_font
        ws.cell(row=row_idx, column=1).alignment = center
        ws.cell(row=row_idx, column=1).border = border

        ws.cell(row=row_idx, column=2, value=desc).font = body_font
        ws.cell(row=row_idx, column=2).alignment = left_wrap
        ws.cell(row=row_idx, column=2).border = border

        amt_cell = ws.cell(row=row_idx, column=3, value=amount)
        amt_cell.font = body_font
        amt_cell.alignment = right
        amt_cell.number_format = "#,##0"
        amt_cell.border = border

        total += amount
        row_idx += 1

    ws.cell(row=row_idx, column=1, value="")
    total_label = ws.cell(row=row_idx, column=2, value="Tổng cộng")
    total_label.font = head_font
    total_label.alignment = left_wrap
    total_label.border = border
    ws.cell(row=row_idx, column=1).border = border

    total_cell = ws.cell(row=row_idx, column=3, value=total)
    total_cell.font = head_font
    total_cell.alignment = right
    total_cell.number_format = "#,##0"
    total_cell.border = border

    rep_name = settings_info.get("representative") or "{Người đại diện hộ}"
    rep_pos = settings_info.get("position") or "{giám đốc}"

    sign_row = row_idx + 3
    ws.merge_cells(f"B{sign_row}:C{sign_row}")
    ws.cell(row=sign_row, column=2, value="Ngày.......tháng......năm........")
    ws.cell(row=sign_row, column=2).font = body_font
    ws.cell(row=sign_row, column=2).alignment = center

    ws.merge_cells(f"B{sign_row + 1}:C{sign_row + 1}")
    ws.cell(row=sign_row + 1, column=2, value=rep_name)
    ws.cell(row=sign_row + 1, column=2).font = head_font
    ws.cell(row=sign_row + 1, column=2).alignment = center

    ws.merge_cells(f"B{sign_row + 2}:C{sign_row + 2}")
    ws.cell(row=sign_row + 2, column=2, value="(Ký, họ tên, đóng dấu (nếu có))")
    ws.cell(row=sign_row + 2, column=2).font = body_font
    ws.cell(row=sign_row + 2, column=2).alignment = center

    ws.merge_cells(f"B{sign_row + 5}:C{sign_row + 5}")
    ws.cell(row=sign_row + 5, column=2, value=rep_pos)
    ws.cell(row=sign_row + 5, column=2).font = head_font
    ws.cell(row=sign_row + 5, column=2).alignment = center

    ws.column_dimensions["A"].width = 14
    ws.column_dimensions["B"].width = 62
    ws.column_dimensions["C"].width = 18

    if not output_path:
        os.makedirs("tmp", exist_ok=True)
        output_path = os.path.join("tmp", f"Mau-So-S1a-HKD_{year}.xlsx")

    wb.save(output_path)
    return os.path.abspath(output_path)

def export_hkd_01_cnkd(data: Dict, output_path: Optional[str] = None) -> str:
    """
    Xuất dữ liệu thuế HKD ra file Excel mẫu 01/CNKD.
    
    Args:
        data: Dict trả về từ calculate_hkd_tax
        output_path: Đường dẫn lưu file, nếu None sẽ lưu vào tmp/
        
    Returns:
        Đường dẫn file đã xuất
    """
    wb = Workbook()
    ws = wb.active
    ws.title = "Mau 01-CNKD"
    
    # Định dạng cơ bản
    bold_font = Font(bold=True)
    center_align = Alignment(horizontal="center", vertical="center")
    border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
    
    # Tiêu đề
    ws.merge_cells('A1:E1')
    ws['A1'] = "TỜ KHAI THUẾ ĐỐI VỚI HỘ KINH DOANH, CÁ NHÂN KINH DOANH"
    ws['A1'].font = Font(bold=True, size=14)
    ws['A1'].alignment = center_align
    
    ws.merge_cells('A2:E2')
    ws['A2'] = "(Mẫu số: 01/CNKD - Cập nhật theo Quy định 2026 & NQ 198/2025/QH15)"
    ws['A2'].alignment = center_align
    
    # Thông tin chung
    ws['A4'] = "Ngành nghề:"
    ws['B4'] = data['industry_name']
    ws['A5'] = "Doanh thu trong kỳ:"
    ws['B5'] = f"{data['revenue']:,.0f} VNĐ"
    
    # Bảng tính thuế
    ws['A7'] = "STT"
    ws['B7'] = "Chỉ tiêu"
    ws['C7'] = "Tỷ lệ (%)"
    ws['D7'] = "Doanh thu tính thuế"
    ws['E7'] = "Số thuế phải nộp"
    
    for cell in ['A7', 'B7', 'C7', 'D7', 'E7']:
        ws[cell].font = bold_font
        ws[cell].border = border
        ws[cell].alignment = center_align
        
    # Dòng 1: Thuế GTGT
    ws['A8'] = "1"
    ws['B8'] = "Thuế giá trị gia tăng (GTGT)"
    ws['C8'] = data['gtgt_rate']
    ws['D8'] = data['revenue']
    ws['E8'] = data['gtgt_due']
    
    # Dòng 2: Thuế TNCN
    ws['A9'] = "2"
    ws['B9'] = "Thuế thu nhập cá nhân (TNCN)"
    ws['C9'] = data['tncn_rate']
    ws['D9'] = data['revenue']
    ws['E9'] = data['tncn_due']
    
    # Tổng cộng
    ws['A10'] = ""
    ws['B10'] = "TỔNG CỘNG"
    ws['B10'].font = bold_font
    ws['E10'] = data['total_due']
    ws['E10'].font = bold_font
    
    # Kẻ border cho bảng
    for row in range(8, 11):
        for col in range(1, 6):
            ws.cell(row=row, column=col).border = border
            if col in [4, 5]:
                ws.cell(row=row, column=col).number_format = '#,##0'

    # Lưu file
    if not output_path:
        os.makedirs("tmp", exist_ok=True)
        filename = f"ToKhai_HKD_{datetime.now().strftime('%Y%m%d_%H%M%S')}.xlsx"
        output_path = os.path.join("tmp", filename)
    
    wb.save(output_path)
    return os.path.abspath(output_path)

def get_S2a_data(db_path: str, year: int, month: int) -> List[Dict]:
    """Sổ doanh thu bán hàng hóa, dịch vụ (Mẫu S2a-HKD)."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    sql = """
    SELECT v.voucher_date, v.voucher_no, v.description, j.credit as amount
    FROM vouchers v
    JOIN journal_entries j ON v.id = j.voucher_id
    WHERE v.period_year = ? AND v.period_month = ?
      AND j.account_code LIKE '511%'
    ORDER BY v.voucher_date, v.voucher_no
    """
    rows = conn.execute(sql, (year, month)).fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_S2c_data(db_path: str, year: int, month: int) -> List[Dict]:
    """Sổ chi tiết doanh thu, chi phí (Mẫu S2c-HKD)."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    sql = """
    SELECT v.voucher_date, v.voucher_no, v.description, 
           CASE WHEN j.account_code LIKE '511%' THEN j.credit ELSE 0 END as revenue,
           CASE WHEN j.account_code LIKE '15%' OR j.account_code LIKE '6%' THEN j.debit ELSE 0 END as cost
    FROM vouchers v
    JOIN journal_entries j ON v.id = j.voucher_id
    WHERE v.period_year = ? AND v.period_month = ?
    ORDER BY v.voucher_date, v.voucher_no
    """
    rows = conn.execute(sql, (year, month)).fetchall()
    conn.close()
    return [dict(r) for r in rows]

def get_S2d_data(db_path: str):
    """Sổ chi tiết vật liệu, dụng cụ, sản phẩm, hàng hóa (Mẫu S2d-HKD)."""
    from core.inventory import get_stock_summary
    return get_stock_summary(db_path)

def get_S2e_data(db_path: str, year: int, month: int) -> List[Dict]:
    """Sổ chi tiết tiền (Mẫu S2e-HKD)."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    sql = """
    SELECT v.voucher_date, v.voucher_no, v.description, j.debit, j.credit
    FROM vouchers v
    JOIN journal_entries j ON v.id = j.voucher_id
    WHERE v.period_year = ? AND v.period_month = ?
      AND (j.account_code LIKE '111%' OR j.account_code LIKE '112%')
    ORDER BY v.voucher_date, v.voucher_no
    """
    rows = conn.execute(sql, (year, month)).fetchall()
    conn.close()
    return [dict(r) for r in rows]
