#!/usr/bin/env python3
"""GHL Architect Dashboard v3 — ArkSheets-style premium Excel dashboard."""

import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter
from openpyxl.chart import BarChart, PieChart, LineChart, Reference
from openpyxl.chart.series import DataPoint
from openpyxl.chart.label import DataLabelList
from copy import copy
import datetime

wb = Workbook()

# ── COLOR PALETTE ──
C_BG       = "0F172A"
C_PANEL    = "1E293B"
C_BORDER   = "334155"
C_GREEN    = "10B981"
C_BLUE     = "3B82F6"
C_CORAL    = "F43F5E"
C_GOLD     = "F59E0B"
C_CYAN     = "06B6D4"
C_PURPLE   = "8B5CF6"
C_WHITE    = "FFFFFF"
C_LIGHT    = "E2E8F0"
C_MUTED    = "64748B"
C_DIM      = "475569"

# ── FILLS ──
fill_bg    = PatternFill("solid", fgColor=C_BG)
fill_panel = PatternFill("solid", fgColor=C_PANEL)
fill_hdr   = PatternFill("solid", fgColor="162032")

# ── FONTS ──
f_title    = Font(name="Calibri", size=18, bold=True, color=C_WHITE)
f_subtitle = Font(name="Calibri", size=10, color=C_MUTED)
f_kpi_lbl  = Font(name="Calibri", size=9, bold=True, color=C_MUTED)
f_kpi_val  = Font(name="Calibri", size=26, bold=True, color=C_WHITE)
f_kpi_sub  = Font(name="Calibri", size=9, color=C_DIM)
f_section  = Font(name="Calibri", size=12, bold=True, color=C_CYAN)
f_tbl_hdr  = Font(name="Calibri", size=9, bold=True, color=C_MUTED)
f_data     = Font(name="Calibri", size=10, color=C_LIGHT)
f_data_w   = Font(name="Calibri", size=10, color=C_WHITE)
f_green    = Font(name="Calibri", size=10, color=C_GREEN)
f_blue     = Font(name="Calibri", size=10, color=C_BLUE)
f_coral    = Font(name="Calibri", size=10, color=C_CORAL)
f_gold     = Font(name="Calibri", size=10, color=C_GOLD)
f_muted_sm = Font(name="Calibri", size=9, color=C_DIM)
f_name     = Font(name="Calibri", size=11, color=C_MUTED)

# ── BORDERS ──
thin_border_color = Side(style="thin", color=C_BORDER)
border_all  = Border(left=thin_border_color, right=thin_border_color,
                     top=thin_border_color, bottom=thin_border_color)
border_left_green  = Border(left=Side(style="thick", color=C_GREEN),
                            right=thin_border_color, top=thin_border_color, bottom=thin_border_color)
border_left_blue   = Border(left=Side(style="thick", color=C_BLUE),
                            right=thin_border_color, top=thin_border_color, bottom=thin_border_color)
border_left_purple = Border(left=Side(style="thick", color=C_PURPLE),
                            right=thin_border_color, top=thin_border_color, bottom=thin_border_color)
border_left_emerald= Border(left=Side(style="thick", color=C_GREEN),
                            right=thin_border_color, top=thin_border_color, bottom=thin_border_color)

align_l = Alignment(horizontal="left", vertical="center")
align_c = Alignment(horizontal="center", vertical="center")
align_r = Alignment(horizontal="right", vertical="center")
align_wrap = Alignment(horizontal="left", vertical="center", wrap_text=True)

# ── STUDY DATA ──
STUDY_DAYS = [
    (1,  "2026-04-01", "1h 00m", "Start Here + M0", "L1 Welcome + L2 How To + M0-L1", "Watch", 46, 14, 60, "Big Picture", "🔄 In Progress", "", "", "Watch all 3 lessons"),
    (2,  "2026-04-02", "1h 00m", "Module 0", "M0-L2/L3/L4 + Quiz", "Watch+Quiz", 50, 10, 60, "Business Models", "✅ Complete", "58", "", "Pass M0 Quiz"),
    (3,  "2026-04-03", "1h 00m", "Module 1", "M1-L1/L2/L3", "Watch", 52, 8, 60, "Mindset Before Automation", "⬜ Not Started", "", "", "Watch M1 intro lessons"),
    (4,  "2026-04-04", "1h 00m", "Module 1", "M1-L3.1 Build Process Map", "Assignment", 0, 60, 60, "YOUR Process Map", "⬜ Not Started", "", "", "Complete process map"),
    (5,  "2026-04-05", "1h 00m", "M1→M2", "M1-L5 + M2-1.1/1.2/1.3", "Watch+Do", 20, 40, 60, "First GHL Setup", "⬜ Not Started", "", "", "GHL account created"),
    (6,  "2026-04-06", "2h 00m", "Module 1", "100K Workshop Part 1", "Workshop", 120, 0, 120, "Full CRM Picture", "⬜ Not Started", "", "", "Complete Part 1"),
    (7,  "2026-04-07", "2h 00m", "Module 1", "100K Workshop Part 2 + Review", "Workshop+Review", 96, 24, 120, "CRM Blueprint", "⬜ Not Started", "", "", "Blueprint document done"),
    (8,  "2026-04-08", "0h 52m", "Module 2", "Users/Permissions + Domains", "Watch+Do", 32, 20, 52, "Users & Domains", "⬜ Not Started", "", "", "Users configured"),
    (9,  "2026-04-09", "0h 59m", "Module 2", "Domain Connect + Email Sub-Domain", "Watch+Do", 29, 30, 59, "Deliverability", "⬜ Not Started", "", "", "Domain verified"),
    (10, "2026-04-10", "1h 00m", "Module 2", "Integrations Google/Facebook", "Watch+Do", 30, 30, 60, "Core Integrations", "⬜ Not Started", "", "", "Both connected"),
    (11, "2026-04-11", "1h 00m", "Module 2", "Zapier + A2P SMS", "Watch", 51, 9, 60, "3rd Party + SMS", "⬜ Not Started", "", "", "A2P registered"),
    (12, "2026-04-12", "1h 00m", "Module 2", "Phone + Email Systems", "Watch", 58, 2, 60, "Phone & Email", "⬜ Not Started", "", "", "Phone number active"),
    (13, "2026-04-13", "1h 19m", "Module 2", "Payment + Brand Board + Quiz", "Watch+Do+Quiz", 49, 30, 79, "M2 Complete!", "⬜ Not Started", "", "", "Pass M2 Quiz"),
    (14, "2026-04-14", "1h 34m", "Module 3", "Contacts Manual + Import + Custom Fields", "Watch+Do", 49, 45, 94, "Contacts Into GHL", "⬜ Not Started", "", "", "Contacts imported"),
    (15, "2026-04-15", "0h 55m", "Module 3", "Custom Fields + Values", "Watch+Do", 35, 20, 55, "Data Structure", "⬜ Not Started", "", "", "Custom fields set up"),
    (16, "2026-04-16", "0h 59m", "M3→M4", "Custom Values + First Form", "Do+Watch+Do", 19, 40, 59, "Values + First Form", "⬜ Not Started", "", "", "First form live"),
    (17, "2026-04-17", "0h 58m", "Module 4", "Surveys + Quizzes + Calendar Intro", "Watch+Do", 38, 20, 58, "Surveys & Calendar", "⬜ Not Started", "", "", "Survey created"),
    (18, "2026-04-18", "1h 00m", "Module 4", "Calendar Config + Build", "Watch+Do", 38, 22, 60, "Booking Calendar", "⬜ Not Started", "", "", "Calendar booking works"),
    (19, "2026-04-19", "1h 00m", "Module 4", "Funnel Theory + Watch Build", "Watch", 60, 0, 60, "Funnel Theory", "⬜ Not Started", "", "", "Theory notes complete"),
    (20, "2026-04-20", "1h 42m", "Module 4", "Funnel Build + Chat Widget", "Watch+Do", 42, 60, 102, "BUILD First Funnel!", "⬜ Not Started", "", "", "Funnel published"),
    (21, "2026-04-21", "1h 24m", "M4→M5", "QR Codes + SMS/Email Templates", "Watch+Do", 44, 40, 84, "QR + Sales Assets", "⬜ Not Started", "", "", "Templates saved"),
    (22, "2026-04-22", "0h 49m", "Module 5", "Email Snippets + Templates", "Do+Watch", 29, 20, 49, "Email Deep Dive", "⬜ Not Started", "", "", "3 email templates"),
    (23, "2026-04-23", "1h 00m", "Module 5", "Email Templates + Documents", "Do+Watch+Do", 20, 40, 60, "Documents & Contracts", "⬜ Not Started", "", "", "Contract template done"),
    (24, "2026-04-24", "0h 51m", "M5→M6", "Trigger Links + Pipeline Theory", "Watch+Do", 36, 15, 51, "Links + Pipeline", "⬜ Not Started", "", "", "Trigger links active"),
    (25, "2026-04-25", "1h 00m", "Module 6", "Pipeline + Opportunities", "Watch+Do", 35, 25, 60, "BUILD Pipeline!", "⬜ Not Started", "", "", "Pipeline configured"),
    (26, "2026-04-26", "1h 00m", "Module 7", "Automation & Workflows Theory", "Watch+Notes", 43, 17, 60, "Automation Mindset", "⬜ Not Started", "", "", "Notes documented"),
    (27, "2026-04-27", "1h 40m", "Module 7", "Workflow Builder + Knowledge Check", "Watch+Check", 88, 12, 100, "Automation in Action", "⬜ Not Started", "", "", "Knowledge check passed"),
    (28, "2026-04-28", "1h 25m", "Module 7", "Build & Test Automation", "BUILD", 5, 80, 85, "BUILD Automation!", "⬜ Not Started", "", "", "Automation tested"),
    (29, "2026-04-29", "1h 00m", "M7→M8", "Go Live + Conversations + Invoices", "GoLive+Watch", 43, 17, 60, "You're LIVE!", "⬜ Not Started", "", "", "Account live"),
    (30, "2026-04-30", "1h 46m", "M8+M9", "Products + Coupons + Dashboard + Final Quiz", "Watch+Do+Quiz", 66, 40, 106, "GRADUATION DAY!", "⬜ Not Started", "", "", "Final quiz passed"),
]

def make_progress_bar(pct, width=10):
    filled = int(pct * width)
    return "█" * filled + "░" * (width - filled)

# ═══════════════════════════════════════════════════════════
# SHEET 1: STUDY DATA
# ═══════════════════════════════════════════════════════════
ws_data = wb.active
ws_data.title = "Study Data"
ws_data.sheet_properties.tabColor = C_BLUE

# Dark background for entire sheet
for row in range(1, 35):
    for col in range(1, 16):
        c = ws_data.cell(row=row, column=col)
        c.fill = fill_bg
        c.font = f_data

# Headers
data_headers = ["Day", "Date", "Time Budget", "Module", "Lessons", "Type",
                "Video Min", "Task Min", "Total Min", "Focus Theme",
                "Status", "Actual Time", "Notes", "Done Criteria"]
for ci, h in enumerate(data_headers, 1):
    c = ws_data.cell(row=1, column=ci, value=h)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

# Data rows
for ri, day in enumerate(STUDY_DAYS, 2):
    for ci, val in enumerate(day, 1):
        c = ws_data.cell(row=ri, column=ci, value=val)
        c.fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
        c.font = f_data
        c.border = border_all
        c.alignment = align_l
        # Format date column
        if ci == 2 and isinstance(val, str) and val:
            try:
                c.value = datetime.datetime.strptime(val, "%Y-%m-%d").date()
                c.number_format = "MMM D, YYYY"
            except:
                pass
        # Numeric columns
        if ci in (1, 7, 8, 9):
            c.alignment = align_c

# Column widths
ws_data.column_dimensions['A'].width = 6
ws_data.column_dimensions['B'].width = 14
ws_data.column_dimensions['C'].width = 12
ws_data.column_dimensions['D'].width = 16
ws_data.column_dimensions['E'].width = 35
ws_data.column_dimensions['F'].width = 16
ws_data.column_dimensions['G'].width = 12
ws_data.column_dimensions['H'].width = 12
ws_data.column_dimensions['I'].width = 12
ws_data.column_dimensions['J'].width = 24
ws_data.column_dimensions['K'].width = 16
ws_data.column_dimensions['L'].width = 14
ws_data.column_dimensions['M'].width = 20
ws_data.column_dimensions['N'].width = 24

ws_data.sheet_view.showGridLines = False

# ═══════════════════════════════════════════════════════════
# SHEET 2: CALC ENGINE
# ═══════════════════════════════════════════════════════════
ws_calc = wb.create_sheet("Calc Engine")
ws_calc.sheet_properties.tabColor = C_PURPLE

# Background
for row in range(1, 50):
    for col in range(1, 10):
        c = ws_calc.cell(row=row, column=col)
        c.fill = fill_bg
        c.font = f_data

# Labels and formulas
calcs = [
    ("A1", "METRIC", "B1", "VALUE"),
    ("A2", "Days Complete", "B2", '=COUNTIF(\'Study Data\'!K2:K31,"✅ Complete")'),
    ("A3", "Days In Progress", "B3", '=COUNTIF(\'Study Data\'!K2:K31,"🔄 In Progress")'),
    ("A4", "Days Not Started", "B4", '=COUNTIF(\'Study Data\'!K2:K31,"⬜ Not Started")'),
    ("A5", "Total Days", "B5", "=COUNTA('Study Data'!A2:A31)"),
    ("A6", "Completion %", "B6", "=IF(B5>0,B2/B5,0)"),
    ("A7", "Total Planned Min", "B7", "=SUM('Study Data'!I2:I31)"),
    ("A8", "Total Actual Min", "B8", "=SUM('Study Data'!L2:L31)"),
    ("A9", "Total Actual Hours", "B9", "=B8/60"),
    ("A10", "Total Planned Hours", "B10", "=B7/60"),
    # Week 1 (Days 1-7)
    ("A12", "Week 1 Total", "B12", "=COUNTA('Study Data'!A2:A8)"),
    ("A13", "Week 1 Done", "B13", '=COUNTIF(\'Study Data\'!K2:K8,"✅ Complete")'),
    # Week 2 (Days 8-14)
    ("A14", "Week 2 Total", "B14", "=COUNTA('Study Data'!A9:A15)"),
    ("A15", "Week 2 Done", "B15", '=COUNTIF(\'Study Data\'!K9:K15,"✅ Complete")'),
    # Week 3 (Days 15-21)
    ("A16", "Week 3 Total", "B16", "=COUNTA('Study Data'!A16:A22)"),
    ("A17", "Week 3 Done", "B17", '=COUNTIF(\'Study Data\'!K16:K22,"✅ Complete")'),
    # Week 4 (Days 22-30)
    ("A18", "Week 4 Total", "B18", "=COUNTA('Study Data'!A23:A31)"),
    ("A19", "Week 4 Done", "B19", '=COUNTIF(\'Study Data\'!K23:K31,"✅ Complete")'),
    # Module counts (M0-M9) - count by checking if Module column contains the module name
    ("A21", "M0 Total", "B21", '=COUNTIF(\'Study Data\'!D2:D31,"*M0*")+COUNTIF(\'Study Data\'!D2:D31,"*Module 0*")'),
    ("A22", "M0 Done", "B22", '=SUMPRODUCT((ISNUMBER(SEARCH("M0",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 0",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A23", "M1 Total", "B23", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 1*")+COUNTIF(\'Study Data\'!D2:D31,"*M1*")'),
    ("A24", "M1 Done", "B24", '=SUMPRODUCT((ISNUMBER(SEARCH("M1",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A25", "M2 Total", "B25", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 2*")'),
    ("A26", "M2 Done", "B26", '=SUMPRODUCT((ISNUMBER(SEARCH("Module 2",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A27", "M3 Total", "B27", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 3*")+COUNTIF(\'Study Data\'!D2:D31,"*M3*")'),
    ("A28", "M3 Done", "B28", '=SUMPRODUCT((ISNUMBER(SEARCH("M3",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 3",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A29", "M4 Total", "B29", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 4*")+COUNTIF(\'Study Data\'!D2:D31,"*M4*")'),
    ("A30", "M4 Done", "B30", '=SUMPRODUCT((ISNUMBER(SEARCH("M4",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 4",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A31", "M5 Total", "B31", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 5*")+COUNTIF(\'Study Data\'!D2:D31,"*M5*")'),
    ("A32", "M5 Done", "B32", '=SUMPRODUCT((ISNUMBER(SEARCH("M5",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 5",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A33", "M6 Total", "B33", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 6*")+COUNTIF(\'Study Data\'!D2:D31,"*M6*")'),
    ("A34", "M6 Done", "B34", '=SUMPRODUCT((ISNUMBER(SEARCH("M6",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 6",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A35", "M7 Total", "B35", '=COUNTIF(\'Study Data\'!D2:D31,"*Module 7*")+COUNTIF(\'Study Data\'!D2:D31,"*M7*")'),
    ("A36", "M7 Done", "B36", '=SUMPRODUCT((ISNUMBER(SEARCH("M7",\'Study Data\'!D2:D31))+ISNUMBER(SEARCH("Module 7",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A37", "M8 Total", "B37", '=COUNTIF(\'Study Data\'!D2:D31,"*M8*")'),
    ("A38", "M8 Done", "B38", '=SUMPRODUCT((ISNUMBER(SEARCH("M8",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    ("A39", "M9 Total", "B39", '=COUNTIF(\'Study Data\'!D2:D31,"*M9*")'),
    ("A40", "M9 Done", "B40", '=SUMPRODUCT((ISNUMBER(SEARCH("M9",\'Study Data\'!D2:D31))>0)*(\'Study Data\'!K2:K31="✅ Complete")*1)'),
    # Streak
    ("A42", "Current Streak", "B42", "=B2"),
]

for a_ref, a_val, b_ref, b_val in calcs:
    ca = ws_calc[a_ref]
    ca.value = a_val
    ca.font = f_tbl_hdr if a_ref == "A1" else f_data
    ca.fill = fill_panel if a_ref == "A1" else fill_bg
    cb = ws_calc[b_ref]
    cb.value = b_val
    cb.font = f_tbl_hdr if b_ref == "B1" else f_data
    cb.fill = fill_panel if b_ref == "B1" else fill_bg

# Format completion %
ws_calc["B6"].number_format = "0.0%"

ws_calc.column_dimensions['A'].width = 20
ws_calc.column_dimensions['B'].width = 50
ws_calc.sheet_view.showGridLines = False
ws_calc.sheet_state = 'hidden'

# ═══════════════════════════════════════════════════════════
# SHEET 3: DASHBOARD
# ═══════════════════════════════════════════════════════════
ws = wb.create_sheet("Dashboard", 0)  # Insert as first sheet
ws.sheet_properties.tabColor = C_GREEN

# ── Helper: fill a range with bg ──
def fill_range(ws, r1, r2, c1, c2, fill=fill_bg, font=None, brd=None):
    for r in range(r1, r2+1):
        for c in range(c1, c2+1):
            cell = ws.cell(row=r, column=c)
            cell.fill = fill
            if font:
                cell.font = font
            if brd:
                cell.border = brd

# Fill entire visible area with dark bg (rows 1-80, cols A-Q = 1-17)
fill_range(ws, 1, 80, 1, 17, fill_bg)

# ── Column widths ──
ws.column_dimensions['A'].width = 2    # spacer
ws.column_dimensions['B'].width = 16
ws.column_dimensions['C'].width = 16
ws.column_dimensions['D'].width = 16
ws.column_dimensions['E'].width = 2    # spacer
ws.column_dimensions['F'].width = 16
ws.column_dimensions['G'].width = 16
ws.column_dimensions['H'].width = 16
ws.column_dimensions['I'].width = 2    # spacer
ws.column_dimensions['J'].width = 16
ws.column_dimensions['K'].width = 16
ws.column_dimensions['L'].width = 16
ws.column_dimensions['M'].width = 2    # spacer
ws.column_dimensions['N'].width = 16
ws.column_dimensions['O'].width = 16
ws.column_dimensions['P'].width = 16
ws.column_dimensions['Q'].width = 2    # spacer

# ── Row heights ──
ws.row_dimensions[1].height = 10
ws.row_dimensions[2].height = 10
ws.row_dimensions[3].height = 30
ws.row_dimensions[4].height = 18
ws.row_dimensions[5].height = 12
for r in range(6, 10):
    ws.row_dimensions[r].height = 28
ws.row_dimensions[10].height = 12

# ── ROW 3: Title ──
ws.merge_cells('B3:D3')
c = ws['B3']
c.value = "GHL ARCHITECT DASHBOARD"
c.font = f_title
c.fill = fill_bg
c.alignment = align_l

ws.merge_cells('J3:L3')
c = ws['J3']
c.value = "Melanie Elver"
c.font = f_name
c.fill = fill_bg
c.alignment = align_r

ws.merge_cells('N3:P3')
c = ws['N3']
c.value = "Apr 1 – Apr 30, 2026"
c.font = f_muted_sm
c.fill = fill_bg
c.alignment = align_r

# ── ROW 4: Subtitle ──
ws.merge_cells('B4:H4')
c = ws['B4']
c.value = "30-Day CRM Mastery Program  ·  Apr 1 – Apr 30, 2026"
c.font = f_subtitle
c.fill = fill_bg
c.alignment = align_l

# ═══════════════════════════════════════════════════════════
# KPI CARDS (Rows 6-9)
# ═══════════════════════════════════════════════════════════
def make_kpi_card(ws, row_start, col_start, label, value, sub_text, left_border):
    """Create a 4-row × 3-col KPI card."""
    cols = [col_start, col_start+1, col_start+2]
    for r in range(row_start, row_start+4):
        for ci, c in enumerate(cols):
            cell = ws.cell(row=r, column=c)
            cell.fill = fill_panel
            if ci == 0:
                cell.border = left_border
            else:
                cell.border = border_all
    # Label (row 1 of card)
    ws.merge_cells(start_row=row_start, start_column=col_start,
                   end_row=row_start, end_column=col_start+2)
    c = ws.cell(row=row_start, column=col_start)
    c.value = label
    c.font = f_kpi_lbl
    c.alignment = Alignment(horizontal="left", vertical="bottom", indent=1)
    c.border = left_border
    
    # Value (row 2-3 of card)
    ws.merge_cells(start_row=row_start+1, start_column=col_start,
                   end_row=row_start+2, end_column=col_start+2)
    c = ws.cell(row=row_start+1, column=col_start)
    c.value = value
    c.font = f_kpi_val
    c.alignment = Alignment(horizontal="left", vertical="center", indent=1)
    c.fill = fill_panel
    c.border = left_border
    
    # Sub text (row 4 of card)
    ws.merge_cells(start_row=row_start+3, start_column=col_start,
                   end_row=row_start+3, end_column=col_start+2)
    c = ws.cell(row=row_start+3, column=col_start)
    c.value = sub_text
    c.font = f_kpi_sub
    c.alignment = Alignment(horizontal="left", vertical="top", indent=1)
    c.fill = fill_panel
    c.border = left_border

# Card formulas reference Calc Engine
make_kpi_card(ws, 6, 2, "DAYS COMPLETE", '=\'Calc Engine\'!B2', "of 30 days", border_left_green)
make_kpi_card(ws, 6, 6, "COMPLETION", '=TEXT(\'Calc Engine\'!B6,"0.0%")', "target: 100%", border_left_blue)
make_kpi_card(ws, 6, 10, "HOURS LOGGED", '=TEXT(INT(\'Calc Engine\'!B9),"0")&"h "&TEXT(MOD(\'Calc Engine\'!B8,60),"00")&"m"', '=TEXT(INT(\'Calc Engine\'!B10),"0")&"h "&TEXT(MOD(\'Calc Engine\'!B7,60),"00")&"m total planned"', border_left_purple)
make_kpi_card(ws, 6, 14, "PACE STATUS", "ON TRACK", "Day 1 of 30", border_left_emerald)

# ═══════════════════════════════════════════════════════════
# ROWS 11-25: WEEKLY PROGRESS (Left) + MODULE MASTERY (Right)
# ═══════════════════════════════════════════════════════════

# ── WEEKLY PROGRESS (B-H, rows 11-25) ──
ws.merge_cells('B11:H11')
c = ws['B11']
c.value = "⬡  WEEKLY PROGRESS"
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

# Table headers row 12
wk_headers = ["WEEK", "DAYS", "DONE", "TOTAL", "PROGRESS", "STATUS", ""]
wk_cols = [2, 3, 4, 5, 6, 7, 8]  # B through H (E=spacer normally but we use it in table)
# Actually let's use B-H as one continuous panel
for ci, (col, hdr) in enumerate(zip(range(2, 9), ["WEEK", "DAYS", "DONE", "TOTAL MIN", "PROGRESS", "STATUS", ""])):
    c = ws.cell(row=12, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

# Week data (rows 13-16)
weeks = [
    ("Week 1", "Days 1-7", '=\'Calc Engine\'!B13', '=\'Calc Engine\'!B12', None, None),
    ("Week 2", "Days 8-14", '=\'Calc Engine\'!B15', '=\'Calc Engine\'!B14', None, None),
    ("Week 3", "Days 15-21", '=\'Calc Engine\'!B17', '=\'Calc Engine\'!B16', None, None),
    ("Week 4", "Days 22-30", '=\'Calc Engine\'!B19', '=\'Calc Engine\'!B18', None, None),
]
for ri, (wk, days, done, total, _, __) in enumerate(weeks, 13):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    ws.cell(row=ri, column=2, value=wk).font = f_data_w
    ws.cell(row=ri, column=3, value=days).font = f_data
    ws.cell(row=ri, column=4, value=done).font = f_green
    ws.cell(row=ri, column=5, value=total).font = f_data
    # Progress bar formula
    ws.cell(row=ri, column=6, value=f'=IF({get_column_letter(5)}{ri}>0,REPT("█",INT({get_column_letter(4)}{ri}/{get_column_letter(5)}{ri}*10))&REPT("░",10-INT({get_column_letter(4)}{ri}/{get_column_letter(5)}{ri}*10)),"░░░░░░░░░░")').font = Font(name="Calibri", size=10, color=C_GREEN)
    # Status
    ws.cell(row=ri, column=7, value=f'=IF({get_column_letter(4)}{ri}={get_column_letter(5)}{ri},"✅ Done",IF({get_column_letter(4)}{ri}>0,"🔄 Active","⬜ Pending"))').font = f_data
    ws.cell(row=ri, column=8).font = f_data
    for col in range(2, 9):
        cell = ws.cell(row=ri, column=col)
        cell.fill = row_fill
        cell.border = border_all
        cell.alignment = align_c if col in (4, 5) else align_l

# ── BAR CHART: Weekly Completion ──
# Create chart data area in Calc Engine for the chart
ws_calc["D1"].value = "Week"
ws_calc["E1"].value = "Done"
ws_calc["F1"].value = "Total"
ws_calc["D2"].value = "Week 1"
ws_calc["D3"].value = "Week 2"
ws_calc["D4"].value = "Week 3"
ws_calc["D5"].value = "Week 4"
ws_calc["E2"].value = "=B13"
ws_calc["E3"].value = "=B15"
ws_calc["E4"].value = "=B17"
ws_calc["E5"].value = "=B19"
ws_calc["F2"].value = "=B12"
ws_calc["F3"].value = "=B14"
ws_calc["F4"].value = "=B16"
ws_calc["F5"].value = "=B18"
for r in range(1, 6):
    for c in range(4, 7):
        ws_calc.cell(row=r, column=c).font = f_data
        ws_calc.cell(row=r, column=c).fill = fill_bg

chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = None
chart1.y_axis.title = None
chart1.x_axis.title = None
chart1.width = 22
chart1.height = 10

cats = Reference(ws_calc, min_col=4, min_row=2, max_row=5)
data_done = Reference(ws_calc, min_col=5, min_row=1, max_row=5)
data_total = Reference(ws_calc, min_col=6, min_row=1, max_row=5)
chart1.add_data(data_done, titles_from_data=True)
chart1.add_data(data_total, titles_from_data=True)
chart1.set_categories(cats)
chart1.shape = 4

# Style the chart
from openpyxl.chart.layout import Layout, ManualLayout
chart1.legend.position = 'b'

s1 = chart1.series[0]
s1.graphicalProperties.solidFill = C_GREEN
s2 = chart1.series[1]
s2.graphicalProperties.solidFill = C_BORDER

ws.add_chart(chart1, "B18")

# ── MODULE MASTERY (J-P, rows 11-25) ──
ws.merge_cells('J11:P11')
c = ws['J11']
c.value = "⬡  MODULE MASTERY"
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

# Table headers
mod_headers = ["MODULE", "PROGRESS", "STATUS", "VISUAL", "", "", ""]
for ci, (col, hdr) in enumerate(zip(range(10, 17), ["MODULE", "DONE", "TOTAL", "PROGRESS", "STATUS", "", ""])):
    c = ws.cell(row=12, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

# Module data (M0-M9), rows 13-22
modules = [
    ("M0: Start Here", 21, 22),
    ("M1: Foundation", 23, 24),
    ("M2: Setup & Config", 25, 26),
    ("M3: Contacts & Data", 27, 28),
    ("M4: Build Assets", 29, 30),
    ("M5: Sales Tools", 31, 32),
    ("M6: Pipeline", 33, 34),
    ("M7: Automation", 35, 36),
    ("M8: Go Live", 37, 38),
    ("M9: Dashboard", 39, 40),
]
for ri, (mod_name, total_row, done_row) in enumerate(modules, 13):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    ws.cell(row=ri, column=10, value=mod_name).font = f_data_w
    ws.cell(row=ri, column=11, value=f"='Calc Engine'!B{done_row}").font = f_green
    ws.cell(row=ri, column=12, value=f"='Calc Engine'!B{total_row}").font = f_data
    # Progress bar
    ws.cell(row=ri, column=13, value=f'=IF(L{ri}>0,REPT("█",INT(K{ri}/L{ri}*10))&REPT("░",10-INT(K{ri}/L{ri}*10)),"░░░░░░░░░░")').font = Font(name="Calibri", size=10, color=C_GREEN)
    # Status
    ws.cell(row=ri, column=14, value=f'=IF(K{ri}=L{ri},"✅ Complete",IF(K{ri}>0,"🔄 Active","⬜ Locked"))').font = f_data
    for col in range(10, 17):
        cell = ws.cell(row=ri, column=col)
        cell.fill = row_fill
        cell.border = border_all
        cell.alignment = align_c if col in (11, 12) else align_l

# ═══════════════════════════════════════════════════════════
# ROWS 27-41: MILESTONES (Left) + SKILL ASSESSMENT (Right)
# ═══════════════════════════════════════════════════════════
ws.row_dimensions[26].height = 12

# ── MILESTONES (B-H, rows 27-41) ──
ws.merge_cells('B27:H27')
c = ws['B27']
c.value = "⬡  MILESTONES"
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

# Headers
for ci, (col, hdr) in enumerate(zip(range(2, 9), ["#", "MILESTONE", "", "TARGET", "STATUS", "DATE", ""])):
    c = ws.cell(row=28, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

milestones = [
    ("1", "GHL Account Created", "", "Day 5", "⬜", ""),
    ("2", "100K Workshop Complete", "", "Day 7", "⬜", ""),
    ("3", "First Funnel Published", "", "Day 20", "⬜", ""),
    ("4", "Pipeline Configured", "", "Day 25", "⬜", ""),
    ("5", "First Automation Live", "", "Day 28", "⬜", ""),
    ("6", "Account Goes Live", "", "Day 29", "⬜", ""),
    ("7", "🎓 GRADUATION", "", "Day 30", "⬜", ""),
]
for ri, (num, name, _, target, status, date) in enumerate(milestones, 29):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    vals = [num, name, _, target, status, date, ""]
    for ci, (col, val) in enumerate(zip(range(2, 9), vals)):
        cell = ws.cell(row=ri, column=col, value=val)
        cell.font = f_data_w if ci == 1 else (f_gold if ci == 4 else f_data)
        cell.fill = row_fill
        cell.border = border_all
        cell.alignment = align_c

# ── DONUT CHART: Status Breakdown ──
# Data in Calc Engine
ws_calc["D8"].value = "Status"
ws_calc["E8"].value = "Count"
ws_calc["D9"].value = "Complete"
ws_calc["D10"].value = "In Progress"
ws_calc["D11"].value = "Not Started"
ws_calc["E9"].value = "=B2"
ws_calc["E10"].value = "=B3"
ws_calc["E11"].value = "=B4"
for r in range(8, 12):
    for c in range(4, 6):
        ws_calc.cell(row=r, column=c).font = f_data
        ws_calc.cell(row=r, column=c).fill = fill_bg

chart2 = PieChart()
chart2.style = 10
chart2.title = None
chart2.width = 14
chart2.height = 10

labels = Reference(ws_calc, min_col=4, min_row=9, max_row=11)
data2 = Reference(ws_calc, min_col=5, min_row=8, max_row=11)
chart2.add_data(data2, titles_from_data=True)
chart2.set_categories(labels)

# Make it a donut
from openpyxl.chart import DoughnutChart
chart2_donut = DoughnutChart()
chart2_donut.title = None
chart2_donut.width = 22
chart2_donut.height = 8
chart2_donut.add_data(data2, titles_from_data=True)
chart2_donut.set_categories(labels)
chart2_donut.style = 10

# Color the slices
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice
from openpyxl.chart.series import DataPoint
pt0 = DataPoint(idx=0)
pt0.graphicalProperties.solidFill = C_GREEN
pt1 = DataPoint(idx=1)
pt1.graphicalProperties.solidFill = C_BLUE
pt2 = DataPoint(idx=2)
pt2.graphicalProperties.solidFill = C_BORDER
chart2_donut.series[0].data_points = [pt0, pt1, pt2]

chart2_donut.dataLabels = DataLabelList()
chart2_donut.dataLabels.showPercent = True
chart2_donut.dataLabels.showCatName = True

ws.add_chart(chart2_donut, "B36")

# ── SKILL ASSESSMENT (J-P, rows 27-41) ──
ws.merge_cells('J27:P27')
c = ws['J27']
c.value = "⬡  SKILL ASSESSMENT"
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

# Headers
for ci, (col, hdr) in enumerate(zip(range(10, 17), ["SKILL", "", "RATING", "VISUAL", "", "", ""])):
    c = ws.cell(row=28, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

skills = [
    "CRM Strategy",
    "Contact Management",
    "Funnel Building",
    "Email Marketing",
    "Pipeline Management",
    "Automation Design",
    "Calendar Setup",
    "Reporting",
    "Client Onboarding",
]
for ri, skill in enumerate(skills, 29):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    ws.merge_cells(start_row=ri, start_column=10, end_row=ri, end_column=11)
    ws.cell(row=ri, column=10, value=skill).font = f_data_w
    ws.cell(row=ri, column=12, value=1).font = Font(name="Calibri", size=10, color=C_GOLD)
    # Visual bar based on rating
    ws.cell(row=ri, column=13, value=f'=REPT("★",L{ri})&REPT("☆",5-L{ri})').font = Font(name="Calibri", size=10, color=C_GOLD)
    for col in range(10, 17):
        cell = ws.cell(row=ri, column=col)
        cell.fill = row_fill
        cell.border = border_all
        cell.alignment = align_c if col >= 12 else align_l

# Architect Score
ws.merge_cells('J39:L39')
ws.cell(row=39, column=10, value="ARCHITECT SCORE").font = Font(name="Calibri", size=11, bold=True, color=C_CYAN)
ws.cell(row=39, column=10).fill = fill_panel
ws.cell(row=39, column=10).border = border_all
ws.merge_cells('M39:P39')
ws.cell(row=39, column=13, value='=ROUND(AVERAGE(L29:L37)/5*100,0)&"%"').font = Font(name="Calibri", size=14, bold=True, color=C_GOLD)
ws.cell(row=39, column=13).fill = fill_panel
ws.cell(row=39, column=13).border = border_all
ws.cell(row=39, column=13).alignment = align_c

# ═══════════════════════════════════════════════════════════
# ROWS 43-55: 30-DAY STUDY LOG
# ═══════════════════════════════════════════════════════════
ws.row_dimensions[42].height = 12

ws.merge_cells('B43:P43')
c = ws['B43']
c.value = "⬡  30-DAY STUDY LOG"
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

# Headers row 44
log_headers = ["DAY", "DATE", "BUDGET", "MODULE", "LESSONS", "TYPE",
               "VID", "TASK", "TOTAL", "FOCUS THEME", "STATUS", "ACTUAL", "NOTES", "", ""]
for ci, (col, hdr) in enumerate(zip(range(2, 17), log_headers)):
    c = ws.cell(row=44, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

# Data rows 45-74 (30 days)
for ri, day in enumerate(STUDY_DAYS, 45):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    # Day, Date, Time Budget, Module, Lessons, Type, Video, Task, Total, Focus, Status, Actual, Notes
    vals = [day[0], day[1], day[2], day[3], day[4], day[5],
            day[6], day[7], day[8], day[9], day[10], day[11], day[12]]
    for ci, (col, val) in enumerate(zip(range(2, 15), vals)):
        cell = ws.cell(row=ri, column=col, value=val)
        cell.fill = row_fill
        cell.border = border_all
        cell.font = f_data
        cell.alignment = align_c if ci in (0, 6, 7, 8, 11) else align_l
        # Date formatting
        if ci == 1 and isinstance(val, str) and val:
            try:
                cell.value = datetime.datetime.strptime(val, "%Y-%m-%d").date()
                cell.number_format = "MMM D"
            except:
                pass
        # Status coloring
        if ci == 10:
            if "Complete" in str(val):
                cell.font = f_green
            elif "In Progress" in str(val):
                cell.font = f_blue
            else:
                cell.font = f_data
    # Fill remaining cols
    for col in range(15, 17):
        cell = ws.cell(row=ri, column=col)
        cell.fill = row_fill
        cell.border = border_all

# ═══════════════════════════════════════════════════════════
# LINE CHART: Planned vs Actual (in Calc Engine)
# ═══════════════════════════════════════════════════════════
# Put planned minutes data
ws_calc["H1"].value = "Day"
ws_calc["I1"].value = "Planned Min"
for r in range(1, 3):
    for c in range(8, 10):
        ws_calc.cell(row=r, column=c).font = f_data
        ws_calc.cell(row=r, column=c).fill = fill_bg

for i, day in enumerate(STUDY_DAYS, 2):
    ws_calc.cell(row=i, column=8, value=day[0]).font = f_data
    ws_calc.cell(row=i, column=8).fill = fill_bg
    ws_calc.cell(row=i, column=9, value=day[8]).font = f_data
    ws_calc.cell(row=i, column=9).fill = fill_bg

chart3 = LineChart()
chart3.title = None
chart3.style = 10
chart3.width = 45
chart3.height = 10
chart3.y_axis.title = "Minutes"

cats3 = Reference(ws_calc, min_col=8, min_row=2, max_row=31)
planned = Reference(ws_calc, min_col=9, min_row=1, max_row=31)
chart3.add_data(planned, titles_from_data=True)
chart3.set_categories(cats3)
chart3.series[0].graphicalProperties.line.solidFill = C_BLUE

ws.add_chart(chart3, "B76")

# ═══════════════════════════════════════════════════════════
# ROW 76+: PRE-FLIGHT CHECKLIST
# ═══════════════════════════════════════════════════════════
checklist_start = 88
fill_range(ws, checklist_start-1, checklist_start+14, 1, 17, fill_bg)

ws.merge_cells(f'B{checklist_start}:P{checklist_start}')
c = ws.cell(row=checklist_start, column=2, value="⬡  PRE-FLIGHT CHECKLIST")
c.font = f_section
c.fill = fill_bg
c.alignment = align_l

checklist_hdr = checklist_start + 1
for ci, (col, hdr) in enumerate(zip(range(2, 9), ["#", "ITEM", "", "", "", "STATUS", ""])):
    c = ws.cell(row=checklist_hdr, column=col, value=hdr)
    c.font = f_tbl_hdr
    c.fill = fill_panel
    c.border = border_all
    c.alignment = align_c

checklist_items = [
    ("1", "GHL account created & verified"),
    ("2", "Custom domain connected"),
    ("3", "Email deliverability configured"),
    ("4", "Google & Facebook integrated"),
    ("5", "A2P SMS registration complete"),
    ("6", "Contact import done (50+ contacts)"),
    ("7", "First funnel published & tested"),
    ("8", "Pipeline with 3+ stages built"),
    ("9", "Automation workflow tested end-to-end"),
    ("10", "Final quiz passed — CERTIFIED! 🎓"),
]
for ri, (num, item) in enumerate(checklist_items, checklist_hdr + 1):
    row_fill = fill_bg if ri % 2 == 0 else PatternFill("solid", fgColor="111B2E")
    ws.cell(row=ri, column=2, value=num).font = f_data
    ws.merge_cells(start_row=ri, start_column=3, end_row=ri, end_column=6)
    ws.cell(row=ri, column=3, value=item).font = f_data_w
    ws.cell(row=ri, column=7, value="✗").font = f_coral
    ws.cell(row=ri, column=8).font = f_data
    for col in range(2, 9):
        cell = ws.cell(row=ri, column=col)
        cell.fill = row_fill
        cell.border = border_all
        cell.alignment = align_c if col in (2, 7) else align_l

# ═══════════════════════════════════════════════════════════
# FINAL FORMATTING
# ═══════════════════════════════════════════════════════════

# Hide gridlines
ws.sheet_view.showGridLines = False

# Freeze panes at row 5
ws.freeze_panes = "A6"

# Set active sheet
wb.active = wb.sheetnames.index("Dashboard")

# ── SAVE ──
output_path = "/home/melai/Documents/GHL_Architect_Dashboard_v3.xlsx"
wb.save(output_path)
print(f"✅ Dashboard saved to {output_path}")
print(f"   Sheets: {wb.sheetnames}")
print(f"   Charts: 3 (Bar, Donut, Line)")