#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.10"
# dependencies = ["openpyxl"]
# ///
"""
generate_gantt.py — Gemma Analytics Gantt Chart Generator

Usage:
    uv run generate_gantt.py '<json_string>'
    uv run generate_gantt.py '<json_string>' <output_dir>

Output: <output_dir>/<project_name>_Gantt.xlsx  (output_dir defaults to the
current working directory). See references/gantt_structure.md for the input
JSON schema.
"""

import json
import re
import sys
import os
from datetime import date, timedelta
from itertools import groupby
import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# ── Color palette ──────────────────────────────────────────────────────────────
# Gemma brand colours (see document-branding/references/brand_guide.md).
# Using several accents together in one chart is a deliberate data-viz exception
# to the brand's "accents not combined" rule — recorded as an [IMPL] exception in
# brand_guide.md. Keep these in sync with the table in references/gantt_structure.md.
COLORS = {
    "Data Loading":              "2D9B8B",  # Fresh Teal
    "Data Exploration & Design": "FFE000",  # Sunny Yellow
    "Data Modelling":            "A976E5",  # Lavender
    "BI Reporting":              "ED5C58",  # Tingy Coral
    "Holidays":                  "D9D9D9",  # neutral grey
    "Break":                     "D9D9D9",
}

HEADER_FILL   = PatternFill("solid", fgColor="404040")
MONTH_FILLS   = [PatternFill("solid", fgColor="595959"),
                 PatternFill("solid", fgColor="737373")]
ROW_BG_EVEN   = PatternFill("solid", fgColor="F9F9F9")
ROW_BG_ODD    = PatternFill("solid", fgColor="FFFFFF")

def make_fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)

def working_days(start: date, end: date):
    days = []
    cur = start
    while cur <= end:
        if cur.weekday() < 5:
            days.append(cur)
        cur += timedelta(days=1)
    return days

def month_label(d: date):
    return d.strftime("%B %Y")

HOURS_PER_DAY = 8  # convert "Nh" estimates to days for the totals row

def parse_est_days(est: str, task_name: str = "") -> float:
    """Parse an est_time string ("6h", "5d", "2.5d") into working days.

    Hours are converted at HOURS_PER_DAY. An empty value counts as 0 silently;
    anything else that does not parse also counts as 0 but warns on stderr,
    because a value like "2d 4h" or "6 hours" would otherwise understate the
    "Total estimated effort" figure without a trace.
    """
    raw = (est or "").strip()
    if not raw:
        return 0.0
    m = re.match(r"^\s*([\d.]+)\s*([hd])\s*$", raw.lower())
    if not m:
        print(
            f"Warning: could not parse est_time {raw!r} for task "
            f"{task_name or '<unnamed>'} — counted as 0 in the effort total. "
            'Use one number plus "h" or "d", e.g. "6h" or "2.5d".',
            file=sys.stderr,
        )
        return 0.0
    val, unit = float(m.group(1)), m.group(2)
    return val / HOURS_PER_DAY if unit == "h" else val

def fmt_days(d: float) -> str:
    """Format a day count without trailing zeros: 5.0 -> '5d', 2.5 -> '2.5d'."""
    return f"{d:g}d"

# NOTE: intentionally duplicated in
# product-ticket-writer/scripts/clickup_export.py. Each skill is an independently
# installable, self-contained `uv run --script` unit, so it can't import a shared
# module without fragile cross-skill path hacks. Keep the two copies in sync if
# the character rules change (the only intended difference is the default name).
def safe_filename(name: str) -> str:
    """Reduce a name to filesystem-safe characters (alnum, '_' and '-')."""
    name = name or "Project"
    for a, b in ((" ", "_"), ("/", "-"), ("\\", "-"), ("–", "-"), ("—", "-")):
        name = name.replace(a, b)
    return "".join(c for c in name if c.isalnum() or c in "_-")

def thin_side():
    return Side(style="thin", color="D9D9D9")

def cell_border():
    s = thin_side()
    return Border(left=s, right=s, top=s, bottom=s)

def generate(data: dict, output_dir="."):
    project_name = data.get("project_name", "Project")
    tasks = data.get("tasks", [])

    if not os.path.isdir(output_dir):
        print(f"Error: output directory '{output_dir}' does not exist.", file=sys.stderr)
        sys.exit(1)

    # ── Collect all working days ───────────────────────────────────────────────
    all_days = set()
    for t in tasks:
        s = date.fromisoformat(t["start_date"])
        e = date.fromisoformat(t["end_date"])
        all_days.update(working_days(s, e))

    if not all_days:
        print("No working days found.")
        sys.exit(1)

    all_days = sorted(all_days)
    # Expand to full months
    first_day = all_days[0].replace(day=1)
    last = all_days[-1]
    if last.month == 12:
        last_day = date(last.year + 1, 1, 1) - timedelta(days=1)
    else:
        last_day = date(last.year, last.month + 1, 1) - timedelta(days=1)
    all_days = working_days(first_day, last_day)

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Gantt Chart"

    # ── Fixed columns A-D ─────────────────────────────────────────────────────
    FIXED = 4
    fixed_headers = ["Task", "Phase", "Est. Time", "Actual Time"]
    fixed_widths  = [32, 10, 9, 9]

    for ci, (h, w) in enumerate(zip(fixed_headers, fixed_widths), 1):
        cell = ws.cell(row=1, column=ci, value=h)
        cell.fill = HEADER_FILL
        cell.font = Font(color="FFFFFF", bold=True, size=9, name="Calibri")
        cell.alignment = Alignment(horizontal="center", vertical="center")
        cell.border = cell_border()
        ws.column_dimensions[get_column_letter(ci)].width = w

    # ── Month / day header rows (rows 1 & 2) ──────────────────────────────────
    month_groups = []
    for month_key, grp in groupby(all_days, key=lambda d: (d.year, d.month)):
        month_groups.append((month_key, list(grp)))

    col = FIXED + 1
    month_fill_idx = 0
    day_col_map = {}  # date -> column index

    for (year, month), days in month_groups:
        start_col = col
        for d in days:
            day_col_map[d] = col
            ws.column_dimensions[get_column_letter(col)].width = 2.2

            # Day number (row 2)
            dc = ws.cell(row=2, column=col, value=d.day)
            dc.fill = MONTH_FILLS[month_fill_idx % 2]
            dc.font = Font(color="FFFFFF", bold=True, size=7, name="Calibri")
            dc.alignment = Alignment(horizontal="center", vertical="center")
            dc.border = cell_border()
            col += 1

        # Month header (row 1) — merged
        end_col = col - 1
        ws.merge_cells(start_row=1, start_column=start_col, end_row=1, end_column=end_col)
        mc = ws.cell(row=1, column=start_col,
                     value=month_label(date(year, month, 1)))
        mc.fill = MONTH_FILLS[month_fill_idx % 2]
        mc.font = Font(color="FFFFFF", bold=True, size=9, name="Calibri")
        mc.alignment = Alignment(horizontal="center", vertical="center")
        month_fill_idx += 1

    ws.row_dimensions[1].height = 16
    ws.row_dimensions[2].height = 14

    # ── Task rows ─────────────────────────────────────────────────────────────
    TASK_ROW_START = 3
    for ri, t in enumerate(tasks):
        row = TASK_ROW_START + ri
        category = t.get("category", "Data Loading")
        task_fill = make_fill(COLORS.get(category, "EFEFEF"))
        row_bg = ROW_BG_EVEN if ri % 2 == 0 else ROW_BG_ODD

        # Fixed columns
        name_cell = ws.cell(row=row, column=1, value=t["name"])
        name_cell.alignment = Alignment(vertical="center", wrap_text=True)
        name_cell.font = Font(size=9, name="Calibri")
        name_cell.fill = row_bg
        name_cell.border = cell_border()

        for ci, key in [(2, "phase"), (3, "est_time"), (4, "actual_time")]:
            c = ws.cell(row=row, column=ci, value=t.get(key, ""))
            c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            c.font = Font(size=9, name="Calibri")
            c.fill = row_bg
            c.border = cell_border()

        # Day columns — color task days, neutral background for others
        task_day_set = set(working_days(
            date.fromisoformat(t["start_date"]),
            date.fromisoformat(t["end_date"])
        ))
        for d, col_i in day_col_map.items():
            c = ws.cell(row=row, column=col_i)
            if d in task_day_set:
                c.fill = task_fill
            else:
                c.fill = row_bg
            c.border = cell_border()

        ws.row_dimensions[row].height = 16

    # ── Summary rows: daily load + totals ──────────────────────────────────────
    # "Days planned" = number of tasks active per day (overlap / resourcing load).
    # Holidays and breaks are not planned work, so they are excluded.
    daily_load = {d: 0 for d in day_col_map}
    total_est_days = 0.0
    for t in tasks:
        if t.get("category") in ("Holidays", "Break"):
            continue
        total_est_days += parse_est_days(t.get("est_time", ""), t.get("name", ""))
        for d in working_days(date.fromisoformat(t["start_date"]),
                              date.fromisoformat(t["end_date"])):
            if d in daily_load:
                daily_load[d] += 1

    tasks_end = TASK_ROW_START + len(tasks) - 1
    load_row = tasks_end + 2  # one blank row between tasks and the summary
    summary_fill = PatternFill("solid", fgColor="ECECEC")

    lc = ws.cell(row=load_row, column=1, value="Days planned")
    lc.font = Font(bold=True, size=9, name="Calibri")
    lc.alignment = Alignment(vertical="center")
    lc.fill = summary_fill
    lc.border = cell_border()
    for ci in range(2, FIXED + 1):
        c = ws.cell(row=load_row, column=ci)
        c.fill = summary_fill
        c.border = cell_border()
    for d, col_i in day_col_map.items():
        c = ws.cell(row=load_row, column=col_i,
                    value=daily_load[d] if daily_load[d] else None)
        c.font = Font(bold=True, size=7, name="Calibri")
        c.alignment = Alignment(horizontal="center", vertical="center")
        c.fill = summary_fill
        c.border = cell_border()
    ws.row_dimensions[load_row].height = 14

    # Totals row — total estimated effort in days
    total_row = load_row + 1
    tc = ws.cell(row=total_row, column=1, value="Total estimated effort")
    tc.font = Font(bold=True, size=9, name="Calibri")
    tc.alignment = Alignment(vertical="center")
    tc.border = cell_border()
    ws.cell(row=total_row, column=2).border = cell_border()
    ev = ws.cell(row=total_row, column=3, value=fmt_days(total_est_days))
    ev.font = Font(bold=True, size=9, name="Calibri")
    ev.alignment = Alignment(horizontal="center", vertical="center")
    ev.border = cell_border()
    ws.cell(row=total_row, column=4).border = cell_border()
    ws.row_dimensions[total_row].height = 14

    # ── Legend ────────────────────────────────────────────────────────────────
    legend_start = total_row + 2
    ws.cell(row=legend_start, column=1, value="Legend:").font = Font(bold=True, size=9, name="Calibri")

    legend_items = [
        ("Data Loading",              COLORS["Data Loading"]),
        ("Data Exploration & Design", COLORS["Data Exploration & Design"]),
        ("Data Modelling",            COLORS["Data Modelling"]),
        ("BI Reporting",              COLORS["BI Reporting"]),
        ("Holidays / Public Holiday", COLORS["Holidays"]),
    ]
    for i, (label, hex_c) in enumerate(legend_items):
        r = legend_start + 1 + i
        cell = ws.cell(row=r, column=1, value=f"  {label}")
        cell.fill = make_fill(hex_c)
        cell.font = Font(size=9, name="Calibri")
        cell.alignment = Alignment(vertical="center")
        ws.row_dimensions[r].height = 14

    # ── Freeze panes & zoom ───────────────────────────────────────────────────
    ws.freeze_panes = ws.cell(row=3, column=FIXED + 1)
    ws.sheet_view.zoomScale = 85

    # ── Save ──────────────────────────────────────────────────────────────────
    output_path = os.path.join(output_dir, f"{safe_filename(project_name)}_Gantt.xlsx")
    wb.save(output_path)
    print(f"Saved: {output_path}")
    return output_path


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: uv run generate_gantt.py '<json_string>' [output_dir]")
        sys.exit(1)
    try:
        data = json.loads(sys.argv[1])
    except json.JSONDecodeError as e:
        print(f"Error: invalid JSON — {e}", file=sys.stderr)
        sys.exit(1)
    out_dir = sys.argv[2] if len(sys.argv) > 2 else "."
    generate(data, out_dir)