#!/usr/bin/env python3
"""GHL Architect Dashboard v4 — ArkSheets-style dark dashboard."""

import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.chart.series import DataPoint
from openpyxl.chart.label import DataLabelList
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice
from copy import copy

wb = openpyxl.Workbook()

# ── Color palette ──
C_BG       = "0F172A"
C_SIDEBAR  = "111827"
C_PANEL    = "1E293B"
C_BORDER   = "334155"
C_WHITE    = "E2E8F0"
C_MUTED    = "64748B"
C_CYAN     = "06B6D4"
C_GREEN    = "10B981"
C_BLUE     = "3B82F6"
C_YELLOW   = "F59E0B"
C_DARKGRAY = "374151"

# KPI card colors
KPI_COLORS = ["064E3B", "1E3A5F", "134E4A", "3B0764", "7F1D1D", "1E3A8A"]

# ── Fills ──
fill_bg      = PatternFill("solid", fgColor=C_BG)
fill_sidebar = PatternFill("solid", fgColor=C_SIDEBAR)
fill_panel   = PatternFill("solid", fgColor=C_PANEL)
fill_highlight = PatternFill("solid", fgColor="1E293B")

# ── Fonts ──
font_white     = Font(name="Calibri", color=C_WHITE, size=10)
font_muted     = Font(name="Calibri", color=C_MUTED, size=8)
font_cyan      = Font(name="Calibri", color=C_CYAN, size=11, bold=True)
font_title     = Font(name="Calibri", color=C_WHITE, size=18, bold=True)
font_kpi_val   = Font(name="Calibri", color="FFFFFF", size=22, bold=True)
font_kpi_label = Font(name="Calibri", color=C_MUTED, size=8)
font_kpi_sub   = Font(name="Calibri", color="94A3B8", size=8)
font_logo      = Font(name="Calibri", color="FFFFFF", size=14, bold=True)
font_menu      = Font(name="Calibri", color=C_MUTED, size=8)
font_menu_item = Font(name="Calibri", color=C_WHITE, size=10)
font_sidebar_sel = Font(name="Calibri", color="FFFFFF", size=10, bold=True)
font_metric_val = Font(name="Calibri", color="FFFFFF", size=14, bold=True)
font_metric_lbl = Font(name="Calibri", color=C_MUTED, size=9)
font_small_white = Font(name="Calibri", color=C_WHITE, size=9)

border_panel = Border(
    left=Side(style="thin", color=C_BORDER),
    right=Side(style="thin", color=C_BORDER),
    top=Side(style="thin", color=C_BORDER),
    bottom=Side(style="thin", color=C_BORDER),
)
align_center = Alignment(horizontal="center", vertical="center", wrap_text=True)
align_left   = Alignment(horizontal="left", vertical="center")

# ════════════════════════════════════════════
# DASHBOARD SHEET
# ════════════════════════════════════════════
ws = wb.active
ws.title = "Dashboard"
ws.sheet_properties.tabColor = "0F172A"

# Hide gridlines + headers
ws.sheet_view.showGridLines = False
ws.sheet_view.showRowColHeaders = False

# ── Column widths ──
ws.column_dimensions['A'].width = 2
ws.column_dimensions['B'].width = 18
for col_letter in ['C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R']:
    ws.column_dimensions[col_letter].width = 14

# ── Fill EVERY cell with background ──
for row in range(1, 52):
    for col in range(1, 19):  # A-R
        cell = ws.cell(row=row, column=col)
        cell.fill = fill_bg
        cell.font = font_white

# ── LEFT SIDEBAR (cols A-B, rows 1-50) ──
for row in range(1, 51):
    for col in [1, 2]:
        cell = ws.cell(row=row, column=col)
        cell.fill = fill_sidebar

# Logo
ws.merge_cells('B2:B3')
ws['B2'] = "📊 GHL STUDY"
ws['B2'].font = font_logo
ws['B2'].alignment = Alignment(horizontal="left", vertical="center")

# Menu label
ws['B6'] = "Menu"
ws['B6'].font = font_menu

# Menu items
menu_items = [
    ("B7", "⚙ Settings", font_menu_item, fill_sidebar),
    ("B8", "📊 Dashboard", font_sidebar_sel, fill_highlight),
    ("B9", "📋 Study Plan", font_menu_item, fill_sidebar),
    ("B10", "📝 Daily Log", font_menu_item, fill_sidebar),
    ("B11", "📈 Progress", font_menu_item, fill_sidebar),
    ("B12", "📖 Glossary", font_menu_item, fill_sidebar),
]
for ref, text, fnt, fl in menu_items:
    ws[ref] = text
    ws[ref].font = fnt
    ws[ref].fill = fl
    ws[ref].alignment = align_left

# ── TOP BAR (Row 2-3, cols C onward) ──
ws.merge_cells('C2:G3')
ws['C2'] = "STUDY PLAN DASHBOARD"
ws['C2'].font = font_title
ws['C2'].alignment = Alignment(horizontal="left", vertical="center")

# Year / Month selectors
ws.merge_cells('L2:M2')
ws['L2'] = "Year: 2026"
ws['L2'].font = Font(name="Calibri", color=C_WHITE, size=10)
ws['L2'].alignment = align_center
ws['L2'].border = border_panel
ws['L2'].fill = fill_panel

ws.merge_cells('N2:O2')
ws['N2'] = "Month: April"
ws['N2'].font = Font(name="Calibri", color=C_WHITE, size=10)
ws['N2'].alignment = align_center
ws['N2'].border = border_panel
ws['N2'].fill = fill_panel

# Student name
ws.merge_cells('P2:Q3')
ws['P2'] = "(Student Name)\nMelanie Elver"
ws['P2'].font = Font(name="Calibri", color=C_WHITE, size=10)
ws['P2'].alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws['P2'].fill = fill_panel
ws['P2'].border = border_panel

# ── KPI CARDS (Rows 5-7, 6 cards) ──
kpi_data = [
    ("Days Complete", "1", "of 30 days", "064E3B"),
    ("All-time Study Hours", "1h 00m", "total logged", "1E3A5F"),
    ("Study Today", "60 min", "Apr 1 session", "134E4A"),
    ("Modules Done", "0/10", "modules complete", "3B0764"),
    ("Quizzes Passed", "1", "quiz scores", "7F1D1D"),
    ("Completion", "3.3%", "overall progress", "1E3A8A"),
]

# Each card spans 2 columns, starting at col C (3)
for i, (label, value, subtitle, color) in enumerate(kpi_data):
    start_col = 3 + i * 2
    end_col = start_col + 1
    sc = get_column_letter(start_col)
    ec = get_column_letter(end_col)
    kpi_fill = PatternFill("solid", fgColor=color)
    
    # Fill all cells in card area
    for r in range(5, 8):
        for c in range(start_col, end_col + 1):
            cell = ws.cell(row=r, column=c)
            cell.fill = kpi_fill
            cell.border = border_panel
    
    # Row 5: Label
    ws.merge_cells(f'{sc}5:{ec}5')
    cell = ws[f'{sc}5']
    cell.value = label
    cell.font = font_kpi_label
    cell.alignment = Alignment(horizontal="center", vertical="bottom")
    cell.fill = kpi_fill
    
    # Row 6: Big value
    ws.merge_cells(f'{sc}6:{ec}6')
    cell = ws[f'{sc}6']
    cell.value = value
    cell.font = font_kpi_val
    cell.alignment = align_center
    cell.fill = kpi_fill
    ws.row_dimensions[6].height = 36
    
    # Row 7: Subtitle
    ws.merge_cells(f'{sc}7:{ec}7')
    cell = ws[f'{sc}7']
    cell.value = subtitle
    cell.font = font_kpi_sub
    cell.alignment = Alignment(horizontal="center", vertical="top")
    cell.fill = kpi_fill

# ── CHART DATA AREA (hidden, rows 53+) ──
# Module progress data
modules = ["M0: What is GHL", "M1: Foundation", "M2: Setup & Config",
           "M3: Contacts", "M4: Opportunities", "M5: Marketing",
           "M6: Automation", "M7: Reporting", "M8: Advanced", "M9: Go Live"]
module_done = [1, 0, 0, 0, 0, 0, 0, 0, 0, 0]
module_total = [3, 8, 10, 9, 8, 12, 10, 8, 12, 8]

for i, (m, d, t) in enumerate(zip(modules, module_done, module_total)):
    ws.cell(row=53+i, column=1, value=m).font = font_white
    ws.cell(row=53+i, column=2, value=d)
    ws.cell(row=53+i, column=3, value=t)
    for c in [1,2,3]:
        ws.cell(row=53+i, column=c).fill = fill_bg

# Daily study data
planned = [60,60,60,60,60,120,120,60,60,60,60,60,120,120,60,60,60,60,60,120,120,60,60,60,60,60,120,120,60,106]
actual = [60] + [0]*29

ws.cell(row=64, column=1, value="Day").fill = fill_bg
ws.cell(row=64, column=2, value="Planned").fill = fill_bg
ws.cell(row=64, column=3, value="Actual").fill = fill_bg
for i in range(30):
    ws.cell(row=65+i, column=1, value=f"Day {i+1}").fill = fill_bg
    ws.cell(row=65+i, column=2, value=planned[i]).fill = fill_bg
    ws.cell(row=65+i, column=3, value=actual[i]).fill = fill_bg

# Weekly breakdown data
ws.cell(row=96, column=1, value="Week 1").fill = fill_bg
ws.cell(row=96, column=2, value=1).fill = fill_bg
ws.cell(row=97, column=1, value="Week 2").fill = fill_bg
ws.cell(row=97, column=2, value=0).fill = fill_bg
ws.cell(row=98, column=1, value="Week 3").fill = fill_bg
ws.cell(row=98, column=2, value=0).fill = fill_bg
ws.cell(row=99, column=1, value="Week 4").fill = fill_bg
ws.cell(row=99, column=2, value=0).fill = fill_bg

# Completion breakdown
ws.cell(row=101, column=1, value="Complete").fill = fill_bg
ws.cell(row=101, column=2, value=1).fill = fill_bg
ws.cell(row=102, column=1, value="In Progress").fill = fill_bg
ws.cell(row=102, column=2, value=1).fill = fill_bg
ws.cell(row=103, column=1, value="Not Started").fill = fill_bg
ws.cell(row=103, column=2, value=28).fill = fill_bg

# Weekly hours target data
ws.cell(row=105, column=1, value="Week").fill = fill_bg
ws.cell(row=105, column=2, value="Target").fill = fill_bg
ws.cell(row=105, column=3, value="Actual").fill = fill_bg
weekly_target = [5.5, 5.0, 5.2, 5.8]
weekly_actual = [1.0, 0, 0, 0]
for i in range(4):
    ws.cell(row=106+i, column=1, value=f"Week {i+1}").fill = fill_bg
    ws.cell(row=106+i, column=2, value=weekly_target[i]).fill = fill_bg
    ws.cell(row=106+i, column=3, value=weekly_actual[i]).fill = fill_bg

# ── SECTION HEADERS ──
# Module Progress header
ws.merge_cells('C9:H9')
ws['C9'] = "MODULE PROGRESS"
ws['C9'].font = font_cyan
ws['C9'].fill = fill_bg

# Daily Study Minutes header
ws.merge_cells('I9:O9')
ws['I9'] = "DAILY STUDY MINUTES"
ws['I9'].font = font_cyan
ws['I9'].fill = fill_bg

# ── CHARTS ──

# 1. MODULE PROGRESS — Horizontal bar chart
chart1 = BarChart()
chart1.type = "bar"  # horizontal
chart1.style = 10
chart1.title = None
chart1.y_axis.title = None
chart1.x_axis.title = None
chart1.legend = None
chart1.width = 20
chart1.height = 12

data1 = Reference(ws, min_col=2, min_row=53, max_row=62)
cats1 = Reference(ws, min_col=1, min_row=53, max_row=62)
chart1.add_data(data1)
chart1.set_categories(cats1)
chart1.series[0].graphicalProperties.solidFill = C_GREEN

# Dark background for chart
from openpyxl.chart.layout import Layout, ManualLayout
from openpyxl.drawing.fill import GradientFillProperties

chart1.plot_area.graphicalProperties = openpyxl.chart.shapes.GraphicalProperties()
chart1.plot_area.graphicalProperties.solidFill = C_BG

# Style axes
chart1.y_axis.tickLblPos = "low"
chart1.y_axis.delete = False
chart1.x_axis.delete = False
try:
    chart1.y_axis.txPr = None  # will style via numFmt
except:
    pass

ws.add_chart(chart1, "C10")

# 2. DAILY STUDY MINUTES — Line chart
chart2 = LineChart()
chart2.style = 10
chart2.title = None
chart2.y_axis.title = None
chart2.x_axis.title = None
chart2.width = 28
chart2.height = 14

planned_ref = Reference(ws, min_col=2, min_row=64, max_row=94)
actual_ref = Reference(ws, min_col=3, min_row=64, max_row=94)
cats2 = Reference(ws, min_col=1, min_row=65, max_row=94)

chart2.add_data(planned_ref, titles_from_data=True)
chart2.add_data(actual_ref, titles_from_data=True)
chart2.set_categories(cats2)

chart2.series[0].graphicalProperties.line.solidFill = C_BLUE
chart2.series[0].graphicalProperties.line.width = 22000
chart2.series[1].graphicalProperties.line.solidFill = C_GREEN
chart2.series[1].graphicalProperties.line.width = 22000

chart2.plot_area.graphicalProperties = openpyxl.chart.shapes.GraphicalProperties()
chart2.plot_area.graphicalProperties.solidFill = C_BG

ws.add_chart(chart2, "I10")

# ── BOTTOM ROW SECTION HEADERS (row 23) ──
ws.merge_cells('C23:E23')
ws['C23'] = "WEEKLY BREAKDOWN"
ws['C23'].font = font_cyan
ws['C23'].fill = fill_bg

ws.merge_cells('F23:I23')
ws['F23'] = "COMPLETION BREAKDOWN"
ws['F23'].font = font_cyan
ws['F23'].fill = fill_bg

ws.merge_cells('J23:N23')
ws['J23'] = "WEEKLY HOURS TARGET"
ws['J23'].font = font_cyan
ws['J23'].fill = fill_bg

# 3. WEEKLY BREAKDOWN — Horizontal bar
chart3 = BarChart()
chart3.type = "bar"
chart3.style = 10
chart3.title = None
chart3.legend = None
chart3.width = 14
chart3.height = 10

data3 = Reference(ws, min_col=2, min_row=96, max_row=99)
cats3 = Reference(ws, min_col=1, min_row=96, max_row=99)
chart3.add_data(data3)
chart3.set_categories(cats3)

# Color each bar differently
from openpyxl.chart.series import DataPoint
from openpyxl.drawing.fill import PatternFillProperties as ChartPatternFill
week_colors = [C_GREEN, C_BLUE, C_YELLOW, "8B5CF6"]
for idx, color in enumerate(week_colors):
    pt = DataPoint(idx=idx)
    pt.graphicalProperties.solidFill = color
    chart3.series[0].data_points.append(pt)

chart3.plot_area.graphicalProperties = openpyxl.chart.shapes.GraphicalProperties()
chart3.plot_area.graphicalProperties.solidFill = C_BG

ws.add_chart(chart3, "C24")

# 4. COMPLETION BREAKDOWN — Donut/Pie chart
chart4 = PieChart()
chart4.style = 10
chart4.title = None
chart4.width = 12
chart4.height = 10

data4 = Reference(ws, min_col=2, min_row=101, max_row=103)
cats4 = Reference(ws, min_col=1, min_row=101, max_row=103)
chart4.add_data(data4)
chart4.set_categories(cats4)

# Color slices
slice_colors = [C_GREEN, C_YELLOW, C_DARKGRAY]
for idx, color in enumerate(slice_colors):
    pt = DataPoint(idx=idx)
    pt.graphicalProperties.solidFill = color
    chart4.series[0].data_points.append(pt)

chart4.plot_area.graphicalProperties = openpyxl.chart.shapes.GraphicalProperties()
chart4.plot_area.graphicalProperties.solidFill = C_BG

# Data labels
chart4.series[0].dLbls = DataLabelList()
chart4.series[0].dLbls.showCatName = True
chart4.series[0].dLbls.showVal = True

ws.add_chart(chart4, "F24")

# 5. WEEKLY HOURS TARGET — Grouped bar
chart5 = BarChart()
chart5.type = "col"
chart5.style = 10
chart5.title = None
chart5.width = 18
chart5.height = 10

target_ref = Reference(ws, min_col=2, min_row=105, max_row=109)
actual_hours_ref = Reference(ws, min_col=3, min_row=105, max_row=109)
cats5 = Reference(ws, min_col=1, min_row=106, max_row=109)

chart5.add_data(target_ref, titles_from_data=True)
chart5.add_data(actual_hours_ref, titles_from_data=True)
chart5.set_categories(cats5)

chart5.series[0].graphicalProperties.solidFill = C_BLUE
chart5.series[1].graphicalProperties.solidFill = C_GREEN

chart5.plot_area.graphicalProperties = openpyxl.chart.shapes.GraphicalProperties()
chart5.plot_area.graphicalProperties.solidFill = C_BG

ws.add_chart(chart5, "J24")

# ── RIGHT SIDEBAR (cols P-R) ──
# STUDY METRICS section
ws.merge_cells('P9:R9')
ws['P9'] = "STUDY METRICS"
ws['P9'].font = font_cyan
ws['P9'].fill = fill_panel
ws['P9'].border = border_panel

metrics = [
    ("Items Studied", "2 of 88 videos"),
    ("Hours This Week", "1h (target: 5.5h)"),
    ("Completion", "3.3%"),
]
for i, (lbl, val) in enumerate(metrics):
    r = 10 + i * 2
    ws.merge_cells(f'P{r}:R{r}')
    ws[f'P{r}'] = lbl
    ws[f'P{r}'].font = font_metric_lbl
    ws[f'P{r}'].fill = fill_panel
    ws[f'P{r}'].border = border_panel
    
    ws.merge_cells(f'P{r+1}:R{r+1}')
    ws[f'P{r+1}'] = val
    ws[f'P{r+1}'].font = font_metric_val
    ws[f'P{r+1}'].fill = fill_panel
    ws[f'P{r+1}'].border = border_panel

# MILESTONE STATUS section
ws.merge_cells('P17:R17')
ws['P17'] = "MILESTONE STATUS"
ws['P17'].font = font_cyan
ws['P17'].fill = fill_panel
ws['P17'].border = border_panel

milestones = [
    "🔴 Process Map — Not Done",
    "🔴 Account Setup — Not Done",
    "🔴 First Funnel — Not Done",
    "🔴 Pipeline — Not Done",
    "🔴 Automation — Not Done",
    "🔴 Go Live — Not Done",
    "🔴 Graduated — Not Done",
]
for i, ms in enumerate(milestones):
    r = 18 + i
    ws.merge_cells(f'P{r}:R{r}')
    ws[f'P{r}'] = ms
    ws[f'P{r}'].font = font_small_white
    ws[f'P{r}'].fill = fill_panel
    ws[f'P{r}'].border = border_panel
    ws[f'P{r}'].alignment = align_left

# ARCHITECT SCORE section
ws.merge_cells('P26:R26')
ws['P26'] = "ARCHITECT SCORE"
ws['P26'].font = font_cyan
ws['P26'].fill = fill_panel
ws['P26'].border = border_panel

ws.merge_cells('P27:R28')
ws['P27'] = "0%"
ws['P27'].font = Font(name="Calibri", color="FFFFFF", size=28, bold=True)
ws['P27'].alignment = align_center
ws['P27'].fill = fill_panel
ws['P27'].border = border_panel

ws.merge_cells('P29:R29')
ws['P29'] = "Current: 0/45  |  Goal: 45/45"
ws['P29'].font = font_muted
ws['P29'].fill = fill_panel
ws['P29'].border = border_panel
ws['P29'].alignment = align_center

# ── Row heights ──
ws.row_dimensions[1].height = 6
ws.row_dimensions[2].height = 24
ws.row_dimensions[3].height = 24
ws.row_dimensions[4].height = 8
ws.row_dimensions[5].height = 18
ws.row_dimensions[7].height = 18
ws.row_dimensions[8].height = 8

# ════════════════════════════════════════════
# STUDY DATA SHEET
# ════════════════════════════════════════════
sd = wb.create_sheet("Study Data")
sd.sheet_view.showGridLines = False

headers = ["Day", "Date", "TimeBudget", "Module", "Lessons", "Type",
           "VideoMin", "TaskMin", "TotalMin", "Focus", "Status", "ActualTime", "Notes"]
for i, h in enumerate(headers, 1):
    cell = sd.cell(row=1, column=i, value=h)
    cell.font = Font(name="Calibri", color="FFFFFF", size=10, bold=True)
    cell.fill = PatternFill("solid", fgColor=C_PANEL)
    cell.border = border_panel

# Study plan data
study_plan = [
    # Day, Date, Budget, Module, Lessons, Type, VidMin, TaskMin, Total, Focus, Status, Actual, Notes
    [1, "Apr 1", "1hr", "Start Here+M0", "Welcome+HowTo+WhatIsGHL", "Watch", 46, 14, 60, "Big Picture", "🔄 In Progress", 60, ""],
    [2, "Apr 2", "1hr", "Module 0", "Money+CRM+AccountType+Quiz", "Watch+Quiz", 50, 10, 60, "Business Models", "✅ Complete", 60, "Quiz 10/10"],
]

# Days 3-30 planned data
day_modules = [
    (3, "Apr 3", "1hr", "Module 1", "GHL Overview+Pricing", "Watch", 45, 15, 60, "Foundation"),
    (4, "Apr 4", "1hr", "Module 1", "SaaS Mode+Snapshots", "Watch", 50, 10, 60, "Foundation"),
    (5, "Apr 5", "1hr", "Module 1", "Agency Dashboard+Quiz", "Watch+Quiz", 40, 20, 60, "Foundation"),
    (6, "Apr 6", "2hr", "Module 1+2", "Marketplace+Setup Start", "Watch+Do", 70, 50, 120, "Foundation+Setup"),
    (7, "Apr 7", "2hr", "Module 2", "Settings+Domains+Integrations", "Watch+Do", 60, 60, 120, "Setup"),
    (8, "Apr 8", "1hr", "Module 2", "Twilio+Mailgun Setup", "Watch+Do", 30, 30, 60, "Setup"),
    (9, "Apr 9", "1hr", "Module 2", "Stripe+Calendars", "Watch+Do", 30, 30, 60, "Setup"),
    (10, "Apr 10", "1hr", "Module 2", "Custom Values+Fields", "Watch+Do", 30, 30, 60, "Setup"),
    (11, "Apr 11", "1hr", "Module 2", "Teams+Permissions", "Watch+Do", 35, 25, 60, "Setup"),
    (12, "Apr 12", "1hr", "Module 2", "Review+Quiz", "Review+Quiz", 40, 20, 60, "Setup"),
    (13, "Apr 13", "2hr", "Module 3", "Contacts Overview+Smart Lists", "Watch+Do", 70, 50, 120, "Contacts"),
    (14, "Apr 14", "2hr", "Module 3", "Bulk Actions+Import+Tags", "Watch+Do", 60, 60, 120, "Contacts"),
    (15, "Apr 15", "1hr", "Module 3", "Tasks+Notes+Conversations", "Watch+Do", 30, 30, 60, "Contacts"),
    (16, "Apr 16", "1hr", "Module 4", "Opportunities Overview", "Watch", 45, 15, 60, "Opportunities"),
    (17, "Apr 17", "1hr", "Module 4", "Pipelines+Stages", "Watch+Do", 30, 30, 60, "Opportunities"),
    (18, "Apr 18", "1hr", "Module 4", "Pipeline Automation", "Watch+Do", 35, 25, 60, "Opportunities"),
    (19, "Apr 19", "1hr", "Module 5", "Funnels Overview", "Watch", 45, 15, 60, "Marketing"),
    (20, "Apr 20", "2hr", "Module 5", "Builder+Templates+Forms", "Watch+Do", 70, 50, 120, "Marketing"),
    (21, "Apr 21", "2hr", "Module 5", "Email+SMS Campaigns", "Watch+Do", 60, 60, 120, "Marketing"),
    (22, "Apr 22", "1hr", "Module 5", "Social Planner+Reviews", "Watch+Do", 30, 30, 60, "Marketing"),
    (23, "Apr 23", "1hr", "Module 6", "Workflows Overview", "Watch", 45, 15, 60, "Automation"),
    (24, "Apr 24", "1hr", "Module 6", "Triggers+Actions", "Watch+Do", 30, 30, 60, "Automation"),
    (25, "Apr 25", "1hr", "Module 6", "IF/Else+Wait+Goals", "Watch+Do", 30, 30, 60, "Automation"),
    (26, "Apr 26", "1hr", "Module 6", "Common Recipes", "Watch+Do", 35, 25, 60, "Automation"),
    (27, "Apr 27", "2hr", "Module 7+8", "Reporting+Dashboard+Advanced", "Watch+Do", 70, 50, 120, "Reporting+Adv"),
    (28, "Apr 28", "2hr", "Module 8", "API+Webhooks+Memberships", "Watch+Do", 60, 60, 120, "Advanced"),
    (29, "Apr 29", "1hr", "Module 9", "Go Live Checklist", "Watch+Do", 30, 30, 60, "Go Live"),
    (30, "Apr 30", "1h46m", "Module 9", "Final Review+Cert Quiz", "Review+Quiz", 60, 46, 106, "Graduation"),
]

for d in day_modules:
    row = [d[0], d[1], d[2], d[3], d[4], d[5], d[6], d[7], d[8], d[9], "⬜ Not Started", 0, ""]
    study_plan.append(row)

for i, row_data in enumerate(study_plan, 2):
    for j, val in enumerate(row_data, 1):
        cell = sd.cell(row=i, column=j, value=val)
        cell.font = font_white
        cell.fill = fill_bg
        cell.border = border_panel

# Auto-width for Study Data
for col in range(1, 14):
    sd.column_dimensions[get_column_letter(col)].width = 16

# ════════════════════════════════════════════
# CALC ENGINE SHEET (hidden)
# ════════════════════════════════════════════
ce = wb.create_sheet("Calc Engine")

calc_labels = [
    ("A2", "Days Complete"),
    ("A3", "Completion %"),
    ("A4", "Total Hours"),
    ("A5", "Week 1 Complete"),
    ("A6", "Week 2 Complete"),
    ("A7", "Week 3 Complete"),
    ("A8", "Week 4 Complete"),
]
for ref, lbl in calc_labels:
    ce[ref] = lbl

# Formulas
ce['B2'] = '=COUNTIF(\'Study Data\'!K2:K31,"✅ Complete")'
ce['B3'] = '=ROUND(B2/30*100,1)'
ce['B4'] = '=SUM(\'Study Data\'!L2:L31)/60'

# Per-week COUNTIFS
ce['B5'] = '=COUNTIFS(\'Study Data\'!A2:A31,">="&1,\'Study Data\'!A2:A31,"<="&7,\'Study Data\'!K2:K31,"✅ Complete")'
ce['B6'] = '=COUNTIFS(\'Study Data\'!A2:A31,">="&8,\'Study Data\'!A2:A31,"<="&14,\'Study Data\'!K2:K31,"✅ Complete")'
ce['B7'] = '=COUNTIFS(\'Study Data\'!A2:A31,">="&15,\'Study Data\'!A2:A31,"<="&21,\'Study Data\'!K2:K31,"✅ Complete")'
ce['B8'] = '=COUNTIFS(\'Study Data\'!A2:A31,">="&22,\'Study Data\'!A2:A31,"<="&30,\'Study Data\'!K2:K31,"✅ Complete")'

# Per-module COUNTIFS
module_names = ["Start Here+M0", "Module 0", "Module 1", "Module 2", "Module 3",
                "Module 4", "Module 5", "Module 6", "Module 7+8", "Module 8", "Module 9"]
for i, mod in enumerate(module_names):
    ce[f'A{9+i}'] = f"M{i} done"
    ce[f'B{9+i}'] = f'=COUNTIFS(\'Study Data\'!D2:D31,"*{mod}*",\'Study Data\'!K2:K31,"✅ Complete")'

# Hide calc engine
ce.sheet_state = 'hidden'

# ── Save ──
output_path = "/home/melai/Documents/GHL_Architect_Dashboard_v4.xlsx"
wb.save(output_path)
print(f"✅ Saved to {output_path}")

import os
size = os.path.getsize(output_path)
print(f"📦 File size: {size:,} bytes ({size/1024:.1f} KB)")