"""
Generate Excel report of backup runs and file events.

Usage: python report.py [output.xlsx]

Single sheet with all events from all runs.
Skips runs where total events > THRESHOLD (mass initial imports).
"""

import os
import sys
from datetime import datetime as dt
import pymysql
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from indexer.config import DB_CONFIG

THRESHOLD = 5000  # skip runs with more events than this


def generate_report(output_path: str):
    conn = pymysql.connect(**DB_CONFIG)
    cur = conn.cursor()

    # ── 1. Load runs ──
    cur.execute("SELECT * FROM runs ORDER BY id")
    runs = cur.fetchall()

    # ── 2. Collect all events from non-skipped runs ──
    all_events = []
    skipped_runs = []
    for run in runs:
        run_id, started, finished, status, total, new, mod, deleted, unchanged = run
        total_changes = new + mod + deleted
        if total_changes > THRESHOLD:
            skipped_runs.append(run_id)
            continue
        cur.execute(
            """SELECT fe.event_type, f.relative_path, f.file_name, f.directory,
                      fe.old_size, fe.new_size
               FROM file_events fe
               JOIN files f ON fe.file_id = f.id
               WHERE fe.run_id = %s
               ORDER BY fe.event_type, f.relative_path""",
            (run_id,)
        )
        for ev in cur.fetchall():
            all_events.append((run_id, started, *ev))

    conn.close()

    # ── 3. Build Excel — single sheet ──
    wb = Workbook()
    ws = wb.active
    ws.title = "Events"

    header_font = Font(bold=True, color="FFFFFF", size=11)
    header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
    thin_border = Border(
        left=Side(style="thin"), right=Side(style="thin"),
        top=Side(style="thin"), bottom=Side(style="thin"),
    )

    type_fills = {
        "CREATED": PatternFill(start_color="E2EFDA", end_color="E2EFDA", fill_type="solid"),
        "MODIFIED": PatternFill(start_color="FFF2CC", end_color="FFF2CC", fill_type="solid"),
        "DELETED": PatternFill(start_color="FCE4EC", end_color="FCE4EC", fill_type="solid"),
    }

    headers = ["Run #", "Run Time", "Event", "Path", "File Name", "Directory",
               "Old Size", "New Size", "Size Change"]
    for col, h in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=h)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="center")
        cell.border = thin_border

    for row_idx, ev in enumerate(all_events, 2):
        run_id, started, event_type, rel_path, file_name, directory, old_size, new_size = ev

        size_change = ""
        if old_size is not None and new_size is not None:
            diff = new_size - old_size
            if diff != 0:
                size_change = f"{'+' if diff > 0 else ''}{diff:,}"
        elif new_size is not None:
            size_change = f"+{new_size:,}"
        elif old_size is not None:
            size_change = f"-{old_size:,}"

        values = [run_id, started, event_type, rel_path, file_name, directory,
                  old_size, new_size, size_change]

        fill = type_fills.get(event_type)
        for col, val in enumerate(values, 1):
            cell = ws.cell(row=row_idx, column=col, value=val)
            cell.border = thin_border
            if fill:
                cell.fill = fill

    # Auto-width
    widths = [8, 18, 10, 60, 30, 40, 12, 12, 14]
    for col, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(col)].width = w

    # Autofilter
    ws.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{len(all_events) + 1}"

    wb.save(output_path)
    print(f"Report saved to {output_path}")
    print(f"  Runs total: {len(runs)}, skipped: {len(skipped_runs)} (threshold: {THRESHOLD})")
    print(f"  Events: {len(all_events)} rows")


if __name__ == "__main__":
    REPORT_DIR = r"u:\Dropbox\!!!Days\Downloads Z230"
    timestamp = dt.now().strftime("%Y-%m-%d %H_%M")
    default_name = f"{timestamp} DropboxBackupReport.xlsx"
    output = sys.argv[1] if len(sys.argv) > 1 else os.path.join(REPORT_DIR, default_name)
    generate_report(output)