"""
Layanan Export Excel (.xlsx) Terintegrasi HRIS Pengupahan Karyawan - Bagian HRD.
Menghasilkan laporan multi-sheet lengkap dengan rumus Excel otomatis:
- Akumulasi 3 Hari Terlambat = 1 Kali Alfa (=INT(Terlambat/3))
- Total Alfa Efektif HRIS (=Alfa_Murni + INT(Terlambat/3))
"""

import io
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter


def generate_hris_excel(rekap_data: dict, logs_data: list, locations_data: list, start_date: str, end_date: str) -> bytes:
    wb = Workbook()

    # Palet warna profesional HRD
    header_fill = PatternFill(start_color="0F2942", end_color="0F2942", fill_type="solid")
    sub_fill = PatternFill(start_color="1E4976", end_color="1E4976", fill_type="solid")
    zebra_fill = PatternFill(start_color="F8FAFC", end_color="F8FAFC", fill_type="solid")
    alert_fill = PatternFill(start_color="FEE2E2", end_color="FEE2E2", fill_type="solid")
    warn_fill = PatternFill(start_color="FEF3C7", end_color="FEF3C7", fill_type="solid")

    title_font = Font(name="Calibri", size=14, bold=True, color="0F2942")
    motto_font = Font(name="Calibri", size=10, italic=True, color="334155")
    header_font = Font(name="Calibri", size=10, bold=True, color="FFFFFF")
    regular_font = Font(name="Calibri", size=10, color="0F172A")
    bold_font = Font(name="Calibri", size=10, bold=True, color="0F172A")
    red_bold_font = Font(name="Calibri", size=10, bold=True, color="B91C1C")

    thin_side = Side(style="thin", color="CBD5E1")
    border_all = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

    # =========================================================================
    # SHEET 1: REKAPITULASI ABSEN & INTEGRASI HRIS PENGUPAHAN
    # =========================================================================
    ws1 = wb.active
    ws1.title = "Rekap Absen & Integrasi HRIS"

    ws1["A1"] = "REKAPITULASI ABSENSI KARYAWAN ONLINE & INTEGRASI HRIS PENGUPAHAN"
    ws1["A1"].font = title_font
    ws1["A2"] = (
        "Moto HRD: Memberi pelayanan yang ramah kepada seluruh karyawan dengan integritas tinggi "
        "dan disiplin untuk mencapai satu tujuan bersama."
    )
    ws1["A2"].font = motto_font
    periode_str = f"Periode Rekapitulasi: {start_date or 'Semua'} s/d {end_date or 'Semua'} | Rumus Disiplin: 3 Hari Terlambat = 1 Kali Alfa"
    ws1["A3"] = periode_str
    ws1["A3"].font = bold_font

    headers_s1 = [
        "No",
        "NIP",
        "Nama Karyawan",
        "Perusahaan",
        "Departemen",
        "Jabatan",
        "Kategori Kerja",
        "Lokasi Kerja Default",
        "Hadir Tepat Waktu",
        "Terlambat (Hari)",
        "Total Menit Terlambat",
        "Sakit",
        "Izin",
        "Cuti",
        "Alfa Murni",
        "Akumulasi Alfa (3x Terlambat = 1 Alfa)",
        "Total Alfa Final (HRIS)",
        "Total Jam Kerja Aktual",
        "Potongan / Alfa (Rp)",
        "Total Potongan Absen HRIS (Rp)",
        "Status Disiplin HRD",
    ]

    start_row = 5
    for col_idx, h in enumerate(headers_s1, start=1):
        cell = ws1.cell(row=start_row, column=col_idx, value=h)
        cell.fill = header_fill if col_idx not in (16, 17, 20) else sub_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = border_all
    ws1.row_dimensions[start_row].height = 30

    rows = rekap_data.get("rows", [])
    for idx, r in enumerate(rows, start=1):
        row_num = start_row + idx
        # Rumus Excel langsung:
        # Kolom J (10) = Terlambat (Hari)
        # Kolom O (15) = Alfa Murni
        # Kolom P (16) = =INT(J{row_num}/3)
        # Kolom Q (17) = =O{row_num}+P{row_num}
        # Kolom S (19) = Potongan / Alfa
        # Kolom T (20) = =Q{row_num}*S{row_num}
        values = [
            idx,
            r["nip"],
            r["nama"],
            r["perusahaan"],
            r["departemen"],
            r["jabatan"],
            r["kategori_kerja"],
            r["nama_lokasi_default"],
            r["hadir_tepat"],
            r["terlambat_hari"],
            r["total_menit_terlambat"],
            r["sakit"],
            r["izin"],
            r["cuti"],
            r["alfa_murni"],
            f"=INT(J{row_num}/3)",
            f"=O{row_num}+P{row_num}",
            r["total_jam_kerja"],
            r["potongan_per_alfa"],
            f"=Q{row_num}*S{row_num}",
            r["status_disiplin"],
        ]

        for col_idx, val in enumerate(values, start=1):
            c = ws1.cell(row=row_num, column=col_idx, value=val)
            c.font = regular_font
            c.border = border_all
            if idx % 2 == 0:
                c.fill = zebra_fill

            if col_idx in (1, 2, 4, 7) or (9 <= col_idx <= 18):
                c.alignment = Alignment(horizontal="center", vertical="center")
            elif col_idx in (19, 20):
                c.alignment = Alignment(horizontal="right", vertical="center")
                c.number_format = "#,##0"
            else:
                c.alignment = Alignment(horizontal="left", vertical="center")

            if col_idx in (16, 17) and r["alfa_akumulasi_terlambat"] > 0:
                c.fill = alert_fill
                c.font = red_bold_font
            elif col_idx == 10 and r["terlambat_hari"] > 0:
                c.fill = warn_fill
                c.font = bold_font

    widths_s1 = [6, 16, 28, 12, 24, 24, 14, 28, 14, 14, 16, 10, 10, 10, 12, 22, 18, 16, 18, 22, 28]
    for i, w in enumerate(widths_s1, start=1):
        ws1.column_dimensions[get_column_letter(i)].width = w

    # =========================================================================
    # SHEET 2: LOG DETAIL ABSENSI HARIAN & KOORDINAT GPS
    # =========================================================================
    ws2 = wb.create_sheet(title="Log Detail Absensi GPS")
    ws2["A1"] = "LOG DETAIL KEHADIRAN HARIAN KARYAWAN & TITIK KOORDINAT GPS (HP)"
    ws2["A1"].font = title_font

    headers_s2 = [
        "No",
        "Tanggal",
        "NIP",
        "Nama Karyawan",
        "Perusahaan",
        "Departemen",
        "Kategori",
        "Lokasi Kerja",
        "Mode Radius",
        "Shift",
        "Jadwal Masuk",
        "Jadwal Pulang",
        "Absen Masuk",
        "Absen Keluar",
        "Durasi (Jam)",
        "Status Kehadiran",
        "Menit Terlambat",
        "Koordinat GPS Masuk (Lat, Lng)",
        "Jarak (m)",
        "Status Validasi GPS",
        "Catatan",
    ]

    for col_idx, h in enumerate(headers_s2, start=1):
        cell = ws2.cell(row=3, column=col_idx, value=h)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = border_all
    ws2.row_dimensions[3].height = 28

    for idx, lg in enumerate(logs_data, start=1):
        r_num = 3 + idx
        coord_str = (
            f"{lg['lat_masuk']}, {lg['lng_masuk']}"
            if lg.get("lat_masuk") is not None and lg.get("lng_masuk") is not None
            else "-"
        )
        vals = [
            idx,
            lg["tanggal"],
            lg["nip"],
            lg["nama_karyawan"],
            lg["perusahaan"],
            lg["departemen"],
            lg["kategori_kerja"],
            lg["nama_lokasi"],
            lg["mode_radius"],
            lg["shift_kode"],
            lg["jam_jadwal_masuk"],
            lg["jam_jadwal_pulang"],
            lg["jam_masuk"] or "-",
            lg["jam_keluar"] or "-",
            lg["durasi_jam_kerja"],
            lg["status_kehadiran"],
            lg["menit_terlambat"],
            coord_str,
            lg["jarak_masuk_meter"],
            lg["status_gps_masuk"],
            lg["catatan"],
        ]
        for col_idx, val in enumerate(vals, start=1):
            c = ws2.cell(row=r_num, column=col_idx, value=val)
            c.font = regular_font
            c.border = border_all
            if lg["status_kehadiran"] == "Terlambat" and col_idx in (16, 17):
                c.fill = warn_fill
                c.font = bold_font
            elif lg["status_kehadiran"] == "Alfa" and col_idx == 16:
                c.fill = alert_fill
                c.font = red_bold_font

    for i in range(1, len(headers_s2) + 1):
        ws2.column_dimensions[get_column_letter(i)].width = 18

    # =========================================================================
    # SHEET 3: MASTER LOKASI KERJA (OFFICE & PROYEK)
    # =========================================================================
    ws3 = wb.create_sheet(title="Master Lokasi & Jam Kerja")
    ws3["A1"] = "DAFTAR LOKASI KERJA (OFFICE & PROYEK) BESERTA PENGATURAN JAM KERJA & RADIUS GPS"
    ws3["A1"].font = title_font

    headers_s3 = [
        "Kode",
        "Nama Lokasi Kerja",
        "Kategori",
        "Pengaturan Radius GPS",
        "Latitude",
        "Longitude",
        "Radius (Meter)",
        "Shift Default",
        "Jam Kerja Senin-Jumat",
        "Jam Kerja Sabtu",
        "Total Jam Kerja",
        "Keterangan",
    ]
    for col_idx, h in enumerate(headers_s3, start=1):
        cell = ws3.cell(row=3, column=col_idx, value=h)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
        cell.border = border_all

    for idx, loc in enumerate(locations_data, start=1):
        r_num = 3 + idx
        vals = [
            loc["kode_lokasi"],
            loc["nama_lokasi"],
            loc["kategori"],
            loc["mode_radius"],
            loc["latitude"],
            loc["longitude"],
            "Bebas Radius (Free GPS)" if loc["mode_radius"] == "Bebas Radius" else loc["radius_meter"],
            loc["shift_default"],
            f"{loc['jam_masuk_senin_jumat']} - {loc['jam_pulang_senin_jumat']}",
            f"{loc['jam_masuk_sabtu']} - {loc['jam_pulang_sabtu']}",
            f"{loc['total_jam_kerja']} Jam",
            loc["keterangan"],
        ]
        for col_idx, val in enumerate(vals, start=1):
            c = ws3.cell(row=r_num, column=col_idx, value=val)
            c.font = regular_font
            c.border = border_all

    for i in range(1, len(headers_s3) + 1):
        ws3.column_dimensions[get_column_letter(i)].width = 22

    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return buf.getvalue()