#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Expenses Ordinace – Report Generator (2025)
------------------------------------------
Reads JSON with tab definitions + SQL queries.
Creates one Excel workbook with multiple sheets.
Uniform formatting for all tabs.
Deletes old reports before saving the new one.
"""

import json
import pandas as pd
import pymysql
from datetime import datetime
from pathlib import Path
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows


# ==============================
# CONFIG
# ==============================

JSON_TABS = r"expenses_tabs.json"

MYSQL = {
    "host": "192.168.1.76",
    "port": 3307,
    "user": "root",
    "password": "Vlado9674+",
    "database": "fio",
    "charset": "utf8mb4"
}

EXPORT_DIR = Path(r"z:\Dropbox\Ordinace\Reporty")
EXPORT_DIR.mkdir(exist_ok=True, parents=True)


# ==============================
# DELETE OLD REPORTS (OPTION C)
# ==============================

def delete_all_old_reports(directory: Path):
    """Deletes all previously generated ordinace expense reports."""
    pattern = "*fio ordinace expenses.xlsx"
    deleted = 0

    for f in directory.glob(pattern):
        try:
            f.unlink()
            deleted += 1
            print(f"🗑 Deleted old report: {f.name}")
        except Exception as e:
            print(f"❌ Could not delete {f.name}: {e}")

    if deleted == 0:
        print("ℹ No old reports to delete.")
    else:
        print(f"✓ Deleted {deleted} old reports.")


# ==============================
# FORMATTING HELPERS
# ==============================

def format_sheet(ws):
    """Apply column widths, header styling, borders, autofilter."""

    # Yellow header (Option A)
    header_fill = PatternFill("solid", fgColor="FFF200")
    bold_font = Font(bold=True, color="000000")
    center_align = Alignment(horizontal="center", vertical="center")

    thin = Side(border_style="thin", color="000000")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    # Autofilter
    if ws.max_row > 1:
        ws.auto_filter.ref = ws.dimensions

    # Auto column widths
    for col in ws.columns:
        max_len = 0
        letter = col[0].column_letter
        for cell in col:
            try:
                max_len = max(max_len, len(str(cell.value)))
            except Exception:
                pass
        ws.column_dimensions[letter].width = min(max_len + 2, 50)

    # Style header row
    for cell in ws[1]:
        cell.font = bold_font
        cell.fill = header_fill
        cell.alignment = center_align
        cell.border = border

    # Border for all body cells
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.border = border


# ==============================
# MAIN
# ==============================

def main():
    print("=== Expenses Ordinace Report (with cleanup) ===")

    # Load JSON tabs
    with open(JSON_TABS, "r", encoding="utf-8") as f:
        config = json.load(f)

    tabs = config.get("tabs", [])
    print(f"Loaded {len(tabs)} tab definitions.")

    # Connect DB
    conn = pymysql.connect(**MYSQL)

    # Prepare workbook
    wb = Workbook()
    wb.remove(wb.active)

    # Process each tab
    for tab in tabs:
        name = tab["name"]
        sql = tab["sql"]

        print(f"→ Running tab: {name}")

        df = pd.read_sql(sql, conn)
        df = df.fillna("")

        # Swap columns N (index 13) and O (index 14)
        cols = df.columns.tolist()
        if len(cols) >= 15:
            cols[13], cols[14] = cols[14], cols[13]
            df = df[cols]

        # Create sheet
        sheet_name = name[:31]
        ws = wb.create_sheet(sheet_name)

        # Write DataFrame
        for row in dataframe_to_rows(df, index=False, header=True):
            ws.append(row)

        # Apply formatting
        format_sheet(ws)

    conn.close()

    # Delete older reports
    delete_all_old_reports(EXPORT_DIR)

    # Save new report
    OUTFILE = EXPORT_DIR / f"{datetime.now():%Y-%m-%d %H-%M-%S} FIO ordinace expenses.xlsx"
    wb.save(OUTFILE)

    print(f"\n✔ Report generated:\n{OUTFILE}")


if __name__ == "__main__":
    main()