"""
Export DB -> file cho Power Query đọc (Kênh B trong skills/excel_powerquery.md).

Tạo:
  data/export/attendance.xlsx   (kèm sheet Employees, đã JOIN tên NV)
  data/export/attendance.csv    (utf-8-sig, mở Excel OK)
  data/export/employees.csv

Không đụng tới thiết bị Hikvision (R6.1, R6.3).
Có thể đặt vào Windows Task Scheduler chạy mỗi 5-15 phút.

Chạy:  python export_for_powerquery.py
"""
import os, sys, io, sqlite3, csv
from datetime import datetime

try:
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
except Exception:
    pass

BASE = os.path.dirname(os.path.abspath(__file__))
DB = os.path.join(BASE, 'data', 'attendance.db')
OUT = os.path.join(BASE, 'data', 'export')
os.makedirs(OUT, exist_ok=True)


def fetch():
    # R6.5: open read-only via URI to avoid locking DB while sync writes
    uri = f'file:{DB}?mode=ro'
    conn = sqlite3.connect(uri, uri=True)
    conn.row_factory = sqlite3.Row
    c = conn.cursor()
    # R3.7: JOIN attendance with employees to get employee_name
    c.execute('''
        SELECT a.date, a.time, a.employee_no,
               COALESCE(NULLIF(a.employee_name,''), e.name, '') AS employee_name,
               a.status, a.card_no, a.raw_time,
               a.card_reader_no, a.event_type, a.sub_event_type, a.current_verify_mode
        FROM attendance a
        LEFT JOIN employees e ON a.employee_no = e.employee_no
        ORDER BY a.date, a.time
    ''')
    att = [dict(r) for r in c.fetchall()]
    c.execute('SELECT employee_no, name FROM employees ORDER BY CAST(employee_no AS INTEGER)')
    emps = [dict(r) for r in c.fetchall()]
    conn.close()
    return att, emps


def write_csv(rows, path, fields):
    with open(path, 'w', newline='', encoding='utf-8-sig') as f:
        w = csv.DictWriter(f, fieldnames=fields)
        w.writeheader()
        for r in rows:
            w.writerow({k: r.get(k, '') for k in fields})


def write_xlsx(att, emps, path):
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment
    wb = Workbook()

    ws = wb.active
    ws.title = 'attendance'
    headers = ['date', 'time', 'employee_no', 'employee_name', 'status', 'card_no', 'raw_time',
               'card_reader_no', 'event_type', 'sub_event_type', 'current_verify_mode']
    head_font = Font(bold=True, color='FFFFFF')
    head_fill = PatternFill(start_color='0066CC', end_color='0066CC', fill_type='solid')
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = head_font
        cell.fill = head_fill
        cell.alignment = Alignment(horizontal='center')
    for i, r in enumerate(att, 2):
        for col, k in enumerate(headers, 1):
            ws.cell(row=i, column=col, value=r.get(k, ''))
    widths = [12, 10, 10, 22, 14, 14, 26, 8, 10, 12, 18]
    for col, w in enumerate(widths, 1):
        ws.column_dimensions[chr(64 + col)].width = w
    ws.freeze_panes = 'A2'

    ws2 = wb.create_sheet('employees')
    for col, h in enumerate(['employee_no', 'name'], 1):
        cell = ws2.cell(row=1, column=col, value=h)
        cell.font = head_font
        cell.fill = head_fill
    for i, r in enumerate(emps, 2):
        ws2.cell(row=i, column=1, value=r['employee_no'])
        ws2.cell(row=i, column=2, value=r['name'])
    ws2.column_dimensions['A'].width = 12
    ws2.column_dimensions['B'].width = 30
    ws2.freeze_panes = 'A2'

    meta = wb.create_sheet('_meta')
    meta['A1'] = 'exported_at'
    meta['B1'] = datetime.now().isoformat(timespec='seconds')
    meta['A2'] = 'rows'
    meta['B2'] = len(att)
    meta['A3'] = 'employees'
    meta['B3'] = len(emps)
    meta['A4'] = 'source'
    meta['B4'] = DB

    wb.save(path)


def main():
    print(f'[1/3] Reading {DB} (read-only)...')
    att, emps = fetch()
    print(f'      attendance={len(att)}  employees={len(emps)}')

    csv_path = os.path.join(OUT, 'attendance.csv')
    emp_csv = os.path.join(OUT, 'employees.csv')
    xlsx = os.path.join(OUT, 'attendance.xlsx')

    print(f'[2/3] CSV -> {csv_path}')
    write_csv(att, csv_path, ['date', 'time', 'employee_no', 'employee_name', 'status', 'card_no', 'raw_time',
                              'card_reader_no', 'event_type', 'sub_event_type', 'current_verify_mode'])
    write_csv(emps, emp_csv, ['employee_no', 'name'])

    print(f'[3/3] XLSX -> {xlsx}')
    try:
        write_xlsx(att, emps, xlsx)
    except ImportError:
        print('      (openpyxl not installed - skipping XLSX)')

    print('Done. Excel can refresh from:')
    print(f'  - {csv_path}')
    print(f'  - {xlsx}')


if __name__ == '__main__':
    main()