"""
Tạo file Excel với Power Query đã cấu hình sẵn.
Chạy: python create_excel.py
File xuất: D:\Du_an_Cham_cong\BaoCao_ChamCong.xlsx
"""
import os
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter
from datetime import datetime

OUTPUT = os.path.join(os.path.dirname(__file__), 'BaoCao_ChamCong.xlsx')

def create_report():
    wb = Workbook()

    # === Sheet 1: Hướng dẫn ===
    ws_guide = wb.active
    ws_guide.title = "Hướng dẫn"
    ws_guide.sheet_properties.tabColor = "00B0F0"

    title_font = Font(name='Arial', bold=True, size=16, color='003366')
    head_font = Font(name='Arial', bold=True, size=12, color='FFFFFF')
    head_fill = PatternFill(start_color='0066CC', end_color='0066CC', fill_type='solid')
    body_font = Font(name='Arial', size=11)
    code_font = Font(name='Consolas', size=10, color='006600')
    bd = Border(left=Side('thin'), right=Side('thin'), top=Side('thin'), bottom=Side('thin'))

    ws_guide.merge_cells('A1:F1')
    ws_guide['A1'] = "📋 HƯỚNG DẪN KẾT NỐI EXCEL VỚI API SERVER"
    ws_guide['A1'].font = title_font
    ws_guide['A1'].alignment = Alignment(horizontal='center')

    steps = [
        ("Bước", "Thao tác", "Chi tiết"),
        ("1", "Khởi động API Server", "Chạy file start_server.bat hoặc: python attendance_api.py"),
        ("2", "Mở Excel → Data → Get Data", "From Other Sources → From Web"),
        ("3", "Nhập URL", "http://localhost:5000/api/attendance"),
        ("4", "Chọn Anonymous", "Không cần đăng nhập"),
        ("5", "Expand data → Close & Load", "Dữ liệu sẽ load vào sheet"),
        ("", "", ""),
        ("⚡", "REFRESH DỮ LIỆU", "Data → Refresh All (Ctrl+Alt+F5)"),
        ("🔄", "SYNC DỮ LIỆU MỚI", "Trước Refresh, gọi sync qua browser hoặc PowerShell:"),
        ("", "", 'PowerShell: Invoke-RestMethod -Method POST -Uri "http://localhost:5000/api/sync" -ContentType "application/json" -Body \'{"from":"2026-06-17","to":"2026-06-17"}\''),
        ("", "", ""),
        ("", "M-CODE (Advanced Editor)", "Dán vào Power Query Advanced Editor:"),
    ]

    for i, (s, a, d) in enumerate(steps, 3):
        ws_guide.cell(row=i, column=1, value=s).font = body_font if i > 3 else head_font
        ws_guide.cell(row=i, column=2, value=a).font = body_font if i > 3 else head_font
        ws_guide.cell(row=i, column=3, value=d).font = body_font if i > 3 else head_font
        if i == 3:
            for c in range(1, 4):
                ws_guide.cell(row=i, column=c).fill = head_fill

    # M-code
    mcode_row = len(steps) + 5
    ws_guide.merge_cells(f'A{mcode_row}:F{mcode_row}')
    ws_guide[f'A{mcode_row}'] = "M-CODE: Toàn bộ chấm công"
    ws_guide[f'A{mcode_row}'].font = Font(name='Arial', bold=True, size=12, color='006600')

    mcode = '''let
    Source = Json.Document(Web.Contents("http://localhost:5000/api/attendance")),
    data = Source[data],
    table = Table.FromRecords(data),
    clean = Table.SelectColumns(table, {"date", "time", "employee_no", "employee_name", "status"}),
    renamed = Table.RenameColumns(clean, {
        {"date", "Ngay"}, {"time", "Gio"},
        {"employee_no", "Ma_NV"}, {"employee_name", "Ten_NV"},
        {"status", "Trang_thai"}
    }),
    typed = Table.TransformColumnTypes(renamed, {{"Ngay", type text}, {"Gio", type text}})
in typed'''

    for j, line in enumerate(mcode.split('\n'), mcode_row + 1):
        ws_guide.merge_cells(f'A{j}:F{j}')
        ws_guide[f'A{j}'] = line
        ws_guide[f'A{j}'].font = code_font

    mcode2_row = mcode_row + len(mcode.split('\n')) + 3
    ws_guide.merge_cells(f'A{mcode2_row}:F{mcode2_row}')
    ws_guide[f'A{mcode2_row}'] = "M-CODE: Theo tháng (thay year/month)"
    ws_guide[f'A{mcode2_row}'].font = Font(name='Arial', bold=True, size=12, color='006600')

    mcode_month = '''let
    Source = Json.Document(Web.Contents("http://localhost:5000/api/attendance/month?year=2026&month=6")),
    data = Source[data],
    table = Table.FromRecords(data),
    clean = Table.SelectColumns(table, {"date", "time", "employee_no", "employee_name", "status"})
in clean'''

    for j, line in enumerate(mcode_month.split('\n'), mcode2_row + 1):
        ws_guide.merge_cells(f'A{j}:F{j}')
        ws_guide[f'A{j}'] = line
        ws_guide[f'A{j}'].font = code_font

    ws_guide.column_dimensions['A'].width = 8
    ws_guide.column_dimensions['B'].width = 30
    ws_guide.column_dimensions['C'].width = 80

    # === Sheet 2: Data Template ===
    ws_data = wb.create_sheet("ChamCong_Data")
    ws_data.sheet_properties.tabColor = "10B981"

    ws_data.merge_cells('A1:E1')
    ws_data[f'A1'] = f"BÁO CÁO CHẤM CÔNG — Cập nhật: {datetime.now().strftime('%d/%m/%Y %H:%M')}"
    ws_data['A1'].font = title_font
    ws_data['A1'].alignment = Alignment(horizontal='center')

    headers = ['Ngày', 'Giờ', 'Mã NV', 'Tên NV', 'Trạng thái']
    for col, h in enumerate(headers, 1):
        cell = ws_data.cell(row=3, column=col, value=h)
        cell.font = head_font; cell.fill = head_fill
        cell.alignment = Alignment(horizontal='center'); cell.border = bd

    ws_data['A4'] = "← Dữ liệu sẽ xuất hiện ở đây sau khi kết nối Power Query"
    ws_data['A4'].font = Font(name='Arial', size=10, color='888888', italic=True)

    ws_data.column_dimensions['A'].width = 14
    ws_data.column_dimensions['B'].width = 10
    ws_data.column_dimensions['C'].width = 8
    ws_data.column_dimensions['D'].width = 20
    ws_data.column_dimensions['E'].width = 14

    # === Sheet 3: Nhân viên ===
    ws_emp = wb.create_sheet("NhanVien")
    ws_emp.sheet_properties.tabColor = "7C3AED"
    ws_emp.merge_cells('A1:B1')
    ws_emp['A1'] = "DANH SÁCH NHÂN VIÊN"
    ws_emp['A1'].font = title_font

    for col, h in enumerate(['Mã NV', 'Tên NV'], 1):
        cell = ws_emp.cell(row=3, column=col, value=h)
        cell.font = head_font; cell.fill = head_fill; cell.border = bd

    # Load actual employees
    try:
        import sqlite3
        db = os.path.join(os.path.dirname(__file__), 'data', 'attendance.db')
        if os.path.exists(db):
            conn = sqlite3.connect(db)
            c = conn.cursor()
            c.execute("SELECT employee_no, name FROM employees ORDER BY CAST(employee_no AS INTEGER)")
            for i, (eno, name) in enumerate(c.fetchall(), 4):
                ws_emp.cell(row=i, column=1, value=eno).font = body_font
                ws_emp.cell(row=i, column=1).border = bd
                ws_emp.cell(row=i, column=2, value=name or '').font = body_font
                ws_emp.cell(row=i, column=2).border = bd
            conn.close()
    except: pass

    ws_emp.column_dimensions['A'].width = 10
    ws_emp.column_dimensions['B'].width = 25

    # Save
    wb.save(OUTPUT)
    print(f"✅ Đã tạo: {OUTPUT}")
    print(f"   Mở file → Data → Get Data → From Web → paste URL")
    print(f"   Hoặc: Data → Get Data → Blank Query → Advanced Editor → paste M-code")

if __name__ == '__main__':
    create_report()