"""Hisobot fayllari: oylik va kunlik Excel, xodim uchun PDF hisob varaqasi."""
import calendar

import os
from datetime import datetime

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

import config
import repo

HEADER_FILL = PatternFill(start_color="1F4E78", end_color="1F4E78", fill_type="solid")
SUNDAY_HEADER_FILL = PatternFill(start_color="B45309", end_color="B45309", fill_type="solid")
SUNDAY_FILL = PatternFill(start_color="FFF3CD", end_color="FFF3CD", fill_type="solid")
ABSENT_FILL = PatternFill(start_color="FDEAEA", end_color="FDEAEA", fill_type="solid")
LEAVE_FILL = PatternFill(start_color="E8F0FE", end_color="E8F0FE", fill_type="solid")
HEADER_FONT = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
ABS_SHORT = {"tatil": "T", "kasal": "K", "otgul": "R"}
ABS_LABEL = {"tatil": "TA'TIL", "kasal": "KASALLIK", "otgul": "RUXSAT"}


def _style_header(ws, ncols, sunday_cols=()):
    for col in range(1, ncols + 1):
        cell = ws.cell(row=1, column=col)
        cell.fill = SUNDAY_HEADER_FILL if col in sunday_cols else HEADER_FILL
        cell.font = HEADER_FONT
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)


def _autowidth(ws, min_w=10, pad=2):
    for col in ws.columns:
        max_len = max(len(str(c.value or "")) for c in col)
        ws.column_dimensions[get_column_letter(col[0].column)].width = min(max(max_len + pad, min_w), 40)


def generate_monthly_excel(month_prefix: str, file_path: str):
    year, month = (int(x) for x in month_prefix.split("-"))
    dim = calendar.monthrange(year, month)[1]
    summaries = repo.month_summary_all(month_prefix)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Oylik Hisobot"
    fixed = ["Xodim F.I.Sh", "Belgilangan oylik", "Norma kun", "Kunlik soat", "Kelgan kun",
             "Ishlangan soat", "To'lanadigan soat (2x bilan)", "Kechikish (daq)", "Yo'qliklar (T/K/R)",
             "Hisoblangan maosh", "Bonus", "Jarima", "Avans", "Sof beriladigan oylik"]
    headers = list(fixed)
    sunday_cols = set()
    for day in range(1, dim + 1):
        sun = datetime(year, month, day).weekday() == 6
        suf = " (Yak)" if sun else ""
        headers += [f"{day}-kun Kelgan{suf}", f"{day}-kun Ketgan{suf}"]
        if sun:
            base = len(fixed) + (day - 1) * 2 + 1
            sunday_cols |= {base, base + 1}
    ws.append(headers)
    _style_header(ws, len(headers), sunday_cols)

    for s in summaries:
        hist = {h["date"]: h for h in repo.daily_history(s["user_id"], month_prefix)}
        ab = s["absences"]
        name = s["name"] + ("" if s["is_active"] else " (arxiv)")
        row = [name, s["salary"], s["norm_days"], s["daily_hours"], s["days_came"],
               round(s["work_hours"], 1), round(s["paid_hours"], 1), s["lateness_total"],
               f"{ab.get('tatil', 0)}/{ab.get('kasal', 0)}/{ab.get('otgul', 0)}",
               round(s["earned"]), round(s["bonuses"]), round(s["fines"]), round(s["advances"]), round(s["net"])]
        for day in range(1, dim + 1):
            h = hist.get(f"{month_prefix}-{day:02d}")
            if h and h["absence"] and not h["check_in"]:
                code = ABS_SHORT.get(h["absence"], "?")
                row += [code, code]
            else:
                row += [(h["check_in"] if h and h["check_in"] else "-"), (h["check_out"] if h and h["check_out"] else "-")]
        ws.append(row)
        r = ws.max_row
        for col in sunday_cols:
            ws.cell(row=r, column=col).fill = SUNDAY_FILL

    ws.append([])
    ws.append(["Belgilar: T - ta'til, K - kasallik, R - ruxsat (otgul). Yo'qlik kunlari to'lanmaydi."])
    _autowidth(ws)
    ws.freeze_panes = "B2"
    wb.save(file_path)
    return file_path


def generate_daily_excel(date_str: str, file_path: str):
    rows, kelmagan, sababli = repo.daily_rows(date_str)
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Kunlik Hisobot"
    headers = ["Xodim F.I.Sh", "Kelgan vaqti", "Ketgan vaqti", "Kechikish (daq)", "Kechikish sababi",
               "Tanaffus (daq)", "Ishlangan soat", "To'lanadigan soat (2x bilan)", "Kunlik topilgan pul", "Izoh"]
    ws.append(headers)
    _style_header(ws, len(headers))
    for x in rows:
        note = "⚠️ joylashuv shubhali" if x.get("location_flag") else ""
        ws.append([x["name"], x["check_in"] or "-", x["check_out"] or "Hali ketmagan", x["lateness"],
                   x["reason"], x["break_minutes"], round(x["work_hours"], 2), round(x["paid_hours"], 2),
                   round(x["earned"]), note])
    for x in sababli:
        ws.append([x["name"], ABS_LABEL.get(x["absence"], x["absence"]), "-", 0, x["note"], 0, 0, 0, 0, ""])
        for col in range(1, len(headers) + 1):
            ws.cell(row=ws.max_row, column=col).fill = LEAVE_FILL
    for x in kelmagan:
        ws.append([x["name"], "KELMADI", "-", 0, "", 0, 0, 0, 0, ""])
        for col in range(1, len(headers) + 1):
            ws.cell(row=ws.max_row, column=col).fill = ABSENT_FILL
    _autowidth(ws, min_w=12, pad=4)
    wb.save(file_path)
    return file_path


# ================= PDF HISOB VARAQASI =================
def generate_payslip_pdf(user_id: int, month_prefix: str, file_path: str):
    """Xodimning oy bo'yicha hisob varaqasi (PDF). Kirill/lotin harflari
    to'g'ri chiqishi uchun DejaVuSans shrifti fonts/ papkasidan olinadi."""
    from fpdf import FPDF

    s = repo.salary_summary(user_id, month_prefix)
    if not s:
        raise ValueError("Xodim topilmadi")
    hist = repo.daily_history(user_id, month_prefix)
    advances = repo.list_advances(user_id, month_prefix)
    adjs = repo.list_adjustments(user_id, month_prefix)

    pdf = FPDF(format="A4")
    pdf.set_auto_page_break(True, margin=15)
    pdf.add_font("DejaVu", "", os.path.join(config.FONTS_DIR, "DejaVuSans.ttf"))
    pdf.add_font("DejaVu", "B", os.path.join(config.FONTS_DIR, "DejaVuSans-Bold.ttf"))
    pdf.add_page()
    W = pdf.epw

    pdf.set_font("DejaVu", "B", 15)
    pdf.cell(W, 9, "Ish haqi hisob varaqasi", new_x="LMARGIN", new_y="NEXT", align="C")
    pdf.set_font("DejaVu", "", 11)
    pdf.cell(W, 7, f"{s['name']}  •  {config.month_label(month_prefix)}", new_x="LMARGIN", new_y="NEXT", align="C")
    pdf.ln(4)

    def kv(label, value, bold=False):
        pdf.set_font("DejaVu", "B" if bold else "", 10.5)
        pdf.cell(W * 0.62, 7, label, border="B")
        pdf.cell(W * 0.38, 7, value, border="B", align="R", new_x="LMARGIN", new_y="NEXT")

    ab = s["absences"]
    kv("Belgilangan oylik", f"{config.money(s['salary'])} so'm")
    kv("Ish kuni normasi", f"{s['norm_days']} kun × {s['daily_hours']:g} soat")
    kv("1 soatlik stavka", f"{config.money(s['hourly_rate'])} so'm")
    kv("Kelgan kunlar", f"{s['days_came']} kun")
    kv("Ishlangan soat", f"{s['work_hours']:.1f} soat")
    if round(s["paid_hours"], 2) != round(s["work_hours"], 2):
        kv("To'lanadigan soat (2x bilan)", f"{s['paid_hours']:.1f} soat")
    kv("Kechikishlar", f"{s['late_days']} marta, jami {s['lateness_total']} daqiqa")
    if ab:
        kv("Yo'qliklar (to'lanmaydi)",
           f"ta'til {ab.get('tatil', 0)}, kasallik {ab.get('kasal', 0)}, ruxsat {ab.get('otgul', 0)}")
    pdf.ln(2)
    kv("Hisoblangan maosh", f"{config.money(s['earned'])} so'm")
    kv("Bonus", f"+{config.money(s['bonuses'])} so'm")
    kv("Jarima", f"-{config.money(s['fines'])} so'm")
    kv("Avans", f"-{config.money(s['advances'])} so'm")
    kv("QO'LGA TEGADIGAN SOF OYLIK", f"{config.money(s['net'])} so'm", bold=True)

    if advances or adjs:
        pdf.ln(5)
        pdf.set_font("DejaVu", "B", 11)
        pdf.cell(W, 7, "Avans, bonus va jarimalar", new_x="LMARGIN", new_y="NEXT")
        pdf.set_font("DejaVu", "", 9.5)
        for a in advances:
            pdf.cell(W, 6, f"{a['date']}  avans  -{config.money(a['amount'])} so'm  {a['note']}", new_x="LMARGIN", new_y="NEXT")
        for a in adjs:
            kind = "bonus" if a["amount"] > 0 else "jarima"
            pdf.cell(W, 6, f"{a['date']}  {kind}  {config.money(a['amount'])} so'm  {a['note']}", new_x="LMARGIN", new_y="NEXT")

    pdf.ln(5)
    pdf.set_font("DejaVu", "B", 11)
    pdf.cell(W, 7, "Kunma-kun", new_x="LMARGIN", new_y="NEXT")
    cols = [("Sana", .2), ("Keldi", .15), ("Ketdi", .15), ("Soat", .15), ("Kechikish", .17), ("Izoh", .18)]
    pdf.set_font("DejaVu", "B", 9)
    for t, w in cols:
        pdf.cell(W * w, 6, t, border=1, align="C")
    pdf.ln()
    pdf.set_font("DejaVu", "", 9)
    for h in hist:
        note = ABS_LABEL.get(h["absence"], "") if h["absence"] else ""
        vals = [h["date"], h["check_in"] or "-", h["check_out"] or "-",
                f"{h['work_hours']:.1f}" if h["check_in"] else "-",
                f"{h['lateness']} daq" if h["lateness"] else "-", note]
        for (t, w), v in zip(cols, vals):
            pdf.cell(W * w, 6, str(v), border=1, align="C")
        pdf.ln()

    pdf.ln(4)
    pdf.set_font("DejaVu", "", 8)
    pdf.cell(W, 5, f"Yaratilgan: {config.get_now().strftime('%Y-%m-%d %H:%M')}", new_x="LMARGIN", new_y="NEXT")
    pdf.output(file_path)
    return file_path
