#!/usr/bin/env python3
"""
TODAY — Behavioral Analytics (Single Sheet)

One tab. Raw data at top, summary stats below, analysis below that, charts at bottom.
Design: white background, charcoal text, bronze accent. Goldman Sachs equity research.
All summary/analysis values use Excel formulas where possible.
"""
import json, os, math
from datetime import datetime
from collections import defaultdict
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import CellIsRule, DataBarRule, ColorScaleRule

# ── Paths ──
LOCAL_SYNC = os.path.expanduser('~/.today/today-sync.json')
ICLOUD_SYNC = os.path.expanduser(
    '~/Library/Mobile Documents/com~apple~CloudDocs/today-sync.json')
OUTPUTS = [
    os.path.expanduser('~/.today/today-report.xlsx'),
    os.path.expanduser('~/Library/Mobile Documents/com~apple~CloudDocs/today-report.xlsx'),
    os.path.expanduser('~/Desktop/02 Life/10 Tools/Today/today-report.xlsx'),
]

RESET_START = datetime(2026, 3, 16)
EXAM_DATE   = datetime(2026, 5, 30)

SCORE_CATS = [
    'Morning Ritual', 'CPA Study', 'Work Boundaries', 'Billable Hours',
    'Gym / Mobility', 'Evening Protocol', 'Emotional Regulation',
    'Shutdown Ritual', 'Observer Position', 'Sleep']
DOW = ['Mon','Tue','Wed','Thu','Fri','Sat','Sun']

# ── Design System ──
WHITE   = 'FFFFFF'
SNOW    = 'FAFAFA'
SMOKE   = 'F2F0ED'
INK     = '1A1A1A'
GRAY    = '737373'
LTGRAY  = 'D4D4D4'
BRONZE  = '8B7355'
FOREST  = '1B7340'
CLAY    = '9B2C2C'
SLATE   = '3B5998'

_white  = PatternFill('solid', fgColor=WHITE)
_snow   = PatternFill('solid', fgColor=SNOW)
_smoke  = PatternFill('solid', fgColor=SMOKE)
_bdr    = Border(
    bottom=Side('thin', color=LTGRAY), top=Side('thin', color=LTGRAY),
    left=Side('thin', color=LTGRAY), right=Side('thin', color=LTGRAY))
_bdr_b  = Border(bottom=Side('medium', color=LTGRAY))
_ctr    = Alignment(horizontal='center', vertical='center')
_lft    = Alignment(horizontal='left', vertical='center')
_rgt    = Alignment(horizontal='right', vertical='center')

def F(color=INK, bold=False, sz=10):
    return Font(name='Inter', color=color, bold=bold, size=sz)

def cell(ws, r, c, val, color=INK, bold=False, sz=10, fmt=None, fill=None, align=_ctr):
    cl = ws.cell(row=r, column=c, value=val)
    cl.fill = fill or _white
    cl.font = F(color, bold, sz)
    cl.alignment = align
    cl.border = _bdr
    if fmt: cl.number_format = fmt
    return cl

def fml(ws, r, c, expr, color=INK, bold=False, sz=10, fmt=None, fill=None, align=_ctr):
    cl = ws.cell(row=r, column=c)
    cl.value = expr
    cl.fill = fill or _white
    cl.font = F(color, bold, sz)
    cl.alignment = align
    cl.border = _bdr
    if fmt: cl.number_format = fmt
    return cl

def sect(ws, r, text, ncols):
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=ncols)
    cl = ws.cell(row=r, column=1, value=text)
    cl.fill = _smoke
    cl.font = F(BRONZE, True, 9)
    cl.alignment = _lft
    cl.border = _bdr
    for j in range(2, ncols+1):
        ws.cell(row=r, column=j).fill = _smoke
        ws.cell(row=r, column=j).border = _bdr

def hdr(ws, r, cols, ncols=None):
    for i, h in enumerate(cols, 1):
        cl = ws.cell(row=r, column=i, value=h)
        cl.fill = _smoke
        cl.font = F(BRONZE, True, 8)
        cl.alignment = _ctr
        cl.border = _bdr
    if ncols:
        for j in range(len(cols)+1, ncols+1):
            ws.cell(row=r, column=j).fill = _smoke
            ws.cell(row=r, column=j).border = _bdr

def note(ws, r, text, ncols):
    ws.merge_cells(start_row=r, start_column=1, end_row=r, end_column=ncols)
    cl = ws.cell(row=r, column=1, value=text)
    cl.fill = _white
    cl.font = Font(name='Inter', color=GRAY, italic=True, size=8)
    cl.alignment = _lft
    cl.border = Border()
    for j in range(2, ncols+1):
        ws.cell(row=r, column=j).border = Border()

def blank_row(ws, r, ncols):
    for j in range(1, ncols+1):
        c = ws.cell(row=r, column=j)
        c.fill = _white
        c.border = Border()

# ── Stats helpers ──
def mean(v): return sum(v)/len(v) if v else 0
def stdev(v):
    if len(v) < 2: return 0
    m = mean(v); return math.sqrt(sum((x-m)**2 for x in v)/(len(v)-1))
def pearson(x, y):
    n = min(len(x), len(y))
    if n < 3: return 0
    mx, my = mean(x[:n]), mean(y[:n])
    num = sum((x[i]-mx)*(y[i]-my) for i in range(n))
    dx = math.sqrt(sum((x[i]-mx)**2 for i in range(n)))
    dy = math.sqrt(sum((y[i]-my)**2 for i in range(n)))
    return num/(dx*dy) if dx*dy else 0
def slope(y):
    n = len(y)
    if n < 2: return 0
    mx = (n-1)/2; my = mean(y)
    num = sum((i-mx)*(y[i]-my) for i in range(n))
    den = sum((i-mx)**2 for i in range(n))
    return num/den if den else 0
def streak(v, t=True):
    c = 0
    for x in reversed(v):
        if x == t: c += 1
        else: break
    return c
def longest(v, t=True):
    b = c = 0
    for x in v:
        if x == t: c += 1; b = max(b, c)
        else: c = 0
    return b

# ── Data loading ──
def load():
    for p in [LOCAL_SYNC, ICLOUD_SYNC]:
        try:
            with open(p) as f: return json.load(f)
        except: continue
    return {}

def cap_for(ds):
    return 13 if datetime.strptime(ds, '%Y-%m-%d').weekday() >= 5 else 17

def parse_day(dd, ds):
    m, a, h, sk, sc = dd.get('m',{}), dd.get('a',{}), dd.get('h',{}), dd.get('sk',{}), dd.get('sc',{})
    done = sum(1 for v in m.values() if v) + sum(1 for v in a.values() if v)
    cap = cap_for(ds)
    skipped = sum(1 for v in sk.values() if v)
    pct = round((done/cap)*100) if cap else 0
    scores = [sc.get(str(i),0) for i in range(10)]
    d = datetime.strptime(ds, '%Y-%m-%d')
    day_r = max(0, (d - RESET_START).days + 1)
    phase = ('Pre' if d < RESET_START else
             'Foundation' if day_r <= 10 else
             'Deepening' if day_r <= 20 else 'Integration')
    return dict(
        date=ds, dow=d.weekday(), dn=DOW[d.weekday()], day_r=day_r, phase=phase,
        wknd=d.weekday()>=5, done=done, cap=cap, skip=skipped,
        miss=max(0, cap-done-skipped), pct=pct,
        score=sum(scores), scores=scores,
        h_n=sum(1 for v in h.values() if v),
        med=bool(m.get('meditation')), pages=bool(m.get('pages')),
        bible=bool(m.get('bible')), study=bool(m.get('study')),
        shower=bool(m.get('shower')), decl=bool(m.get('declarations')),
        ritual_h=bool(h.get('ritual')), cpa_h=bool(h.get('cpa')),
        gym_h=bool(h.get('gym')), journal_h=bool(h.get('journal')),
        override=dd.get('no',{}).get('r','') if dd.get('no') else '',
        memo=dd.get('memo',''))


def generate_report():
    raw = load()
    if not raw: return
    dates = sorted(d for d in raw if raw[d].get('m') or raw[d].get('a') or raw[d].get('h'))
    if not dates: return
    S = [parse_day(raw[d], d) for d in dates]
    N = len(S)
    now = datetime.now()
    wb = Workbook()

    ws = wb.active
    ws.title = 'TODAY'
    ws.sheet_properties.tabColor = BRONZE
    ws.sheet_view.showGridLines = False

    # Total columns in data table: 22
    # A=Date B=Day C=Reset# D=Phase E=Done F=Cap G=Completion%
    # H=Score I=Habits J=Med K=Pages L=Bible M=Study N=Shower O=Decl
    # P=Ritual Q=CPA R=Gym S=Journal T=7dAvg U=Trend V=Override
    NC = 22
    GCOL = 7; HCOL = 8; ICOL = 9; JCOL = 10
    R7COL = 20; TRCOL = 21; OVCOL = 22

    # Column widths — fitted to content
    col_widths = {
        'A': 11, 'B': 5, 'C': 7, 'D': 12,
        'E': 6, 'F': 5, 'G': 11,
        'H': 9, 'I': 8,
        'J': 5, 'K': 7, 'L': 6, 'M': 7, 'N': 8, 'O': 6,
        'P': 7, 'Q': 5, 'R': 5, 'S': 8,
        'T': 8, 'U': 7, 'V': 10,
    }
    for letter, w in col_widths.items():
        ws.column_dimensions[letter].width = w

    # ═══════════════════════════════════════════════
    # SECTION 1: TITLE
    # ═══════════════════════════════════════════════
    r = 1
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=NC)
    cl = ws.cell(row=1, column=1,
                 value=f'TODAY   Behavioral Analytics   {now.strftime("%b %d, %Y  %I:%M %p")}')
    cl.fill = _smoke
    cl.font = F(BRONZE, True, 11)
    cl.alignment = _lft
    cl.border = _bdr
    for j in range(2, NC+1):
        ws.cell(row=1, column=j).fill = _smoke
        ws.cell(row=1, column=j).border = _bdr
    r = 2

    # ═══════════════════════════════════════════════
    # SECTION 2: DAILY DATA TABLE
    # ═══════════════════════════════════════════════
    data_cols = [
        'Date', 'Day', 'Reset #', 'Phase',
        'Done', 'Cap', 'Compl %',
        'Score', 'Habits',
        'Med', 'Pages', 'Bible', 'Study', 'Shower', 'Decl',
        'Ritual', 'CPA', 'Gym', 'Journal',
        '7d Avg', 'Trend', 'Override',
    ]
    hdr(ws, r, data_cols)
    DATA_HDR = r
    r += 1
    DATA_START = r  # first data row

    for ri_idx, s in enumerate(S):
        row = DATA_START + ri_idx
        alt = _snow if ri_idx % 2 == 0 else _white
        cell(ws, row, 1, s['date'], sz=9, fill=alt, align=_lft)
        cell(ws, row, 2, s['dn'], color=GRAY, sz=9, fill=alt)
        cell(ws, row, 3, s['day_r'] if s['phase']!='Pre' else '', color=GRAY, sz=9, fill=alt)
        cell(ws, row, 4, s['phase'], sz=9, fill=alt,
             color=BRONZE if s['phase']=='Foundation' else SLATE if s['phase']=='Deepening' else FOREST if s['phase']=='Integration' else GRAY)
        cell(ws, row, 5, s['done'], sz=9, fill=alt)
        cell(ws, row, 6, s['cap'], color=GRAY, sz=9, fill=alt)
        fml(ws, row, GCOL, f'=E{row}/F{row}', bold=True, sz=10, fmt='0%', fill=alt)
        cell(ws, row, HCOL, s['score'], sz=10, fill=alt,
             color=BRONZE if s['score'] >= 70 else INK)
        cell(ws, row, ICOL, s['h_n'], sz=10, fill=alt,
             color=FOREST if s['h_n'] == 4 else INK)

        bools = [s['med'], s['pages'], s['bible'], s['study'], s['shower'], s['decl'],
                 s['ritual_h'], s['cpa_h'], s['gym_h'], s['journal_h']]
        for ci, v in enumerate(bools):
            cell(ws, row, JCOL+ci, 1 if v else 0,
                 color=FOREST if v else LTGRAY, sz=9, fill=alt)

        start = max(DATA_START, row - 6)
        fml(ws, row, R7COL, f'=AVERAGE(G{start}:G{row})', color=GRAY, sz=9, fmt='0%', fill=alt)

        if row > DATA_START:
            fml(ws, row, TRCOL, f'=G{row}-G{row-1}', color=GRAY, sz=9, fmt='+0%;-0%;"--"', fill=alt)
        else:
            cell(ws, row, TRCOL, '', color=GRAY, sz=9, fill=alt)

        cell(ws, row, OVCOL, s['override'] if s['override'] else '',
             color=BRONZE if s['override'] else LTGRAY, sz=8, fill=alt)

    DATA_END = DATA_START + N - 1  # last data row

    # Conditional formatting on data
    ws.conditional_formatting.add(
        f'G{DATA_START}:G{DATA_END}',
        DataBarRule(start_type='num', start_value=0, end_type='num', end_value=1, color=BRONZE))
    ws.conditional_formatting.add(
        f'H{DATA_START}:H{DATA_END}',
        ColorScaleRule(start_type='num', start_value=0, start_color='F5F5F5',
                       mid_type='num', mid_value=50, mid_color='D4C5A0',
                       end_type='num', end_value=100, end_color=BRONZE))
    ws.conditional_formatting.add(
        f'U{DATA_START}:U{DATA_END}',
        CellIsRule(operator='greaterThan', formula=['0'], font=F(FOREST, True, 9)))
    ws.conditional_formatting.add(
        f'U{DATA_START}:U{DATA_END}',
        CellIsRule(operator='lessThan', formula=['0'], font=F(CLAY, True, 9)))

    ws.freeze_panes = f'E{DATA_START}'
    ws.auto_filter.ref = f'A{DATA_HDR}:V{DATA_END}'

    # ═══════════════════════════════════════════════
    # SECTION 3: SUMMARY STATISTICS (Excel formulas)
    # ═══════════════════════════════════════════════
    r = DATA_END + 2
    blank_row(ws, r, NC)
    r += 1
    sect(ws, r, '   Summary Statistics', NC); r += 1
    hdr(ws, r, ['Metric', 'Compl %', 'Score', 'Habits',
                'Med', 'Pages', 'Bible', 'Study', 'Shower', 'Decl',
                'Ritual', 'CPA', 'Gym', 'Journal', '', '', '', '', '', '', '', ''], NC)
    STAT_HDR = r; r += 1

    # Map stat columns: B=Compl(G), C=Score(H), D=Habits(I), E..N = booleans J..S
    stat_src = ['G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S']
    stat_fmts = ['0%', '0', '0.0', '0%', '0%', '0%', '0%', '0%', '0%', '0%', '0%', '0%', '0%']

    for stat_label, stat_fn in [('AVERAGE', 'AVERAGE'), ('STDEV', 'STDEV'),
                                 ('MIN', 'MIN'), ('MAX', 'MAX'), ('MEDIAN', 'MEDIAN')]:
        cell(ws, r, 1, stat_label, bold=True, sz=9, fill=_smoke, align=_lft, color=BRONZE)
        for ci, src_col in enumerate(stat_src):
            fml(ws, r, ci+2, f'={stat_fn}({src_col}{DATA_START}:{src_col}{DATA_END})',
                sz=9, fmt=stat_fmts[ci], fill=_smoke,
                color=FOREST if stat_label == 'MAX' else CLAY if stat_label == 'MIN' else INK,
                bold=stat_label == 'AVERAGE')
        for j in range(len(stat_src)+2, NC+1):
            ws.cell(row=r, column=j).fill = _smoke
            ws.cell(row=r, column=j).border = _bdr
        r += 1

    # SLOPE and COUNT
    cell(ws, r, 1, 'SLOPE', bold=True, sz=9, fill=_smoke, align=_lft, color=BRONZE)
    fml(ws, r, 2, f'=SLOPE(G{DATA_START}:G{DATA_END},ROW(G{DATA_START}:G{DATA_END}))',
        sz=9, fmt='0.00%', fill=_smoke, bold=True, color=BRONZE)
    for j in range(3, NC+1):
        ws.cell(row=r, column=j).fill = _smoke
        ws.cell(row=r, column=j).border = _bdr
    r += 1

    cell(ws, r, 1, 'COUNT', sz=9, fill=_smoke, align=_lft, color=GRAY)
    fml(ws, r, 2, f'=COUNT(G{DATA_START}:G{DATA_END})', sz=9, fill=_smoke, color=GRAY)
    for j in range(3, NC+1):
        ws.cell(row=r, column=j).fill = _smoke
        ws.cell(row=r, column=j).border = _bdr
    r += 1

    note(ws, r, f'   Slope > 0 = improving over time. CV = STDEV/AVERAGE (lower = more consistent).', NC)
    r += 2

    # ═══════════════════════════════════════════════
    # SECTION 4: SCORE CATEGORIES
    # ═══════════════════════════════════════════════
    # Write score category data in a small table for chart reference
    sect(ws, r, '   Score Categories  /10', NC); r += 1
    hdr(ws, r, ['Category', 'Mean', 'SD', 'Min', 'Max', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''], NC)
    r += 1
    SCAT_START = r

    # Score categories are in DATA columns T..AC (columns 20..29) — wait, we removed those.
    # Score categories are NOT in the data table anymore. Compute from Python and write values.
    # But we want formulas... Score categories aren't in the streamlined 22-col layout.
    # Solution: write Python-computed values here. The raw scores live in the JSON, not the sheet.
    for i, cat in enumerate(SCORE_CATS):
        vals = [s['scores'][i] for s in S]
        cell(ws, r, 1, cat, sz=9, align=_lft)
        cell(ws, r, 2, round(mean(vals), 1), sz=10, bold=True, fmt='0.0')
        cell(ws, r, 3, round(stdev(vals), 1), sz=9, color=GRAY, fmt='0.0')
        cell(ws, r, 4, min(vals) if vals else 0, sz=9, color=CLAY)
        cell(ws, r, 5, max(vals) if vals else 0, sz=9, color=FOREST)
        sl = slope(vals)
        cell(ws, r, 6, f'{"+" if sl > 0 else ""}{sl:.2f}/d' if sl != 0 else 'flat',
             color=FOREST if sl > 0.1 else CLAY if sl < -0.1 else GRAY, sz=9)
        for j in range(7, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1
    SCAT_END = r - 1

    ws.conditional_formatting.add(
        f'B{SCAT_START}:B{SCAT_END}',
        DataBarRule(start_type='num', start_value=0, end_type='num', end_value=10, color=BRONZE))
    r += 1

    # ═══════════════════════════════════════════════
    # SECTION 5: HABIT IMPACT ANALYSIS
    # ═══════════════════════════════════════════════
    sect(ws, r, '   Habit Impact  (completion with vs without each behavior)', NC); r += 1
    hdr(ws, r, ['Behavior', 'n(yes)', 'Mean(yes)', 'n(no)', 'Mean(no)',
                'Effect pp', "Cohen's d", 'Size', '', '', '', '', '', '', '', '', '', '', '', '', '', ''], NC)
    r += 1

    habits_analysis = [
        ('med', 'Meditation'), ('pages', 'Morning Pages'), ('bible', 'Bible'),
        ('study', 'CPA Study'), ('shower', 'Shower'), ('decl', 'Declarations'),
        ('ritual_h', 'Full Ritual'), ('cpa_h', 'CPA Habit'),
        ('gym_h', 'Gym'), ('journal_h', 'Journal')]

    for key, label in habits_analysis:
        w = [s for s in S if s[key]]
        wo = [s for s in S if not s[key]]
        if not w or not wo: continue
        mw = round(mean([s['pct'] for s in w]), 1)
        mwo = round(mean([s['pct'] for s in wo]), 1)
        effect = round(mw - mwo, 1)
        sdw = stdev([s['pct'] for s in w])
        sdwo = stdev([s['pct'] for s in wo])
        pooled = math.sqrt(((len(w)-1)*sdw**2 + (len(wo)-1)*sdwo**2)/max(1,len(w)+len(wo)-2))
        d_val = round(effect/pooled, 2) if pooled else 0
        d_interp = ('Large' if abs(d_val)>=0.8 else 'Medium' if abs(d_val)>=0.5
                    else 'Small' if abs(d_val)>=0.2 else 'Negligible')

        cell(ws, r, 1, label, sz=9, align=_lft)
        cell(ws, r, 2, len(w), sz=9)
        cell(ws, r, 3, mw/100, sz=9, fmt='0%', color=FOREST if mw >= 70 else INK)
        cell(ws, r, 4, len(wo), sz=9)
        cell(ws, r, 5, mwo/100, sz=9, fmt='0%', color=CLAY if mwo < 40 else INK)
        cell(ws, r, 6, effect, sz=9, bold=True,
             color=FOREST if effect > 0 else CLAY if effect < 0 else GRAY)
        cell(ws, r, 7, d_val, sz=9, fmt='0.00',
             color=FOREST if abs(d_val)>=0.5 else GRAY)
        cell(ws, r, 8, d_interp, sz=8, color=GRAY, align=_lft)
        for j in range(9, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1

    note(ws, r, "   Cohen's d: 0.2 small, 0.5 medium, 0.8 large. Effect = pp difference in completion.", NC)
    r += 2

    # ═══════════════════════════════════════════════
    # SECTION 6: CORRELATION MATRIX
    # ═══════════════════════════════════════════════
    sect(ws, r, '   Correlation Matrix  (Pearson r)', NC); r += 1
    corr_vars = [
        ('Compl', [s['pct'] for s in S]),
        ('Score', [s['score'] for s in S]),
        ('Habits', [s['h_n'] for s in S]),
        ('Med', [int(s['med']) for s in S]),
        ('Pages', [int(s['pages']) for s in S]),
        ('Study', [int(s['study']) for s in S]),
        ('Gym', [int(s['gym_h']) for s in S]),
    ]
    labels = [v[0] for v in corr_vars]
    corr_cols = [''] + labels
    hdr(ws, r, corr_cols + ['']*(NC - len(corr_cols)), NC)
    r += 1

    for i, (lbl, vi) in enumerate(corr_vars):
        cell(ws, r, 1, lbl, bold=True, sz=8, align=_lft, color=BRONZE)
        for j, (_, vj) in enumerate(corr_vars):
            if i == j:
                cell(ws, r, j+2, 1.00, color=LTGRAY, sz=9, fmt='0.00')
            else:
                rv = round(pearson(vi, vj), 2)
                color = FOREST if rv >= 0.5 else CLAY if rv <= -0.5 else GRAY if abs(rv) < 0.2 else INK
                cell(ws, r, j+2, rv, color=color, sz=9, fmt='0.00', bold=abs(rv) >= 0.5)
        for j in range(len(corr_vars)+2, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1

    note(ws, r, f'   n = {N}. ' + ('Correlations unreliable below n=10.' if N < 10
         else 'Values above |0.5| highlighted.'), NC)
    r += 2

    # ═══════════════════════════════════════════════
    # SECTION 7: TREND + WEEKDAY/WEEKEND + AUTOCORRELATION
    # ═══════════════════════════════════════════════
    sect(ws, r, '   Trend Analysis', NC); r += 1
    hdr(ws, r, ['Window', 'Slope /day', 'Direction', 'R-squared',
                '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''], NC)
    r += 1
    pcts = [s['pct'] for s in S]
    for label, window in [('Full', pcts), ('Last 7d', pcts[-7:]), ('Last 14d', pcts[-14:])]:
        if len(window) < 2: continue
        sl = slope(window)
        m = mean(window)
        ss_tot = sum((y-m)**2 for y in window) or 1
        pred = [m + sl*(i-(len(window)-1)/2) for i in range(len(window))]
        ss_res = sum((window[i]-pred[i])**2 for i in range(len(window)))
        r2 = round(max(0, 1-ss_res/ss_tot), 3)
        cell(ws, r, 1, label, sz=9, align=_lft)
        cell(ws, r, 2, sl/100, bold=True, sz=9, fmt='+0.00%;-0.00%',
             color=FOREST if sl > 0.5 else CLAY if sl < -0.5 else GRAY)
        cell(ws, r, 3, 'Up' if sl > 0.5 else 'Down' if sl < -0.5 else 'Flat',
             color=FOREST if sl > 0.5 else CLAY if sl < -0.5 else GRAY, sz=9)
        cell(ws, r, 4, r2, fmt='0.000', color=FOREST if r2 > 0.5 else GRAY, sz=9)
        for j in range(5, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1
    r += 1

    # Weekday vs Weekend
    sect(ws, r, '   Weekday vs Weekend', NC); r += 1
    hdr(ws, r, ['', 'n', 'Mean %', 'SD', 'Score', 'Habits',
                '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''], NC)
    r += 1
    for label, grp in [('Weekday', [s for s in S if not s['wknd']]),
                       ('Weekend', [s for s in S if s['wknd']])]:
        if not grp: continue
        cell(ws, r, 1, label, bold=True, sz=9, align=_lft)
        cell(ws, r, 2, len(grp), sz=9)
        ga = round(mean([s['pct'] for s in grp]))
        cell(ws, r, 3, ga/100, bold=True, sz=9, fmt='0%',
             color=FOREST if ga >= 70 else CLAY if ga < 40 else INK)
        cell(ws, r, 4, round(stdev([s['pct'] for s in grp]),1), color=GRAY, sz=9)
        cell(ws, r, 5, round(mean([s['score'] for s in grp])), sz=9)
        cell(ws, r, 6, round(mean([s['h_n'] for s in grp]),1), fmt='0.0', sz=9)
        for j in range(7, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1
    r += 1

    # Autocorrelation
    if N >= 4:
        sect(ws, r, '   Autocorrelation  (does yesterday predict today?)', NC); r += 1
        lag1 = round(pearson(pcts[:-1], pcts[1:]), 2)
        interp = ('Strong momentum' if lag1 > 0.5
                  else 'Moderate carry-over' if lag1 > 0.3
                  else 'Weak dependency' if lag1 > 0
                  else 'Bounce-back pattern' if lag1 < -0.3
                  else 'No serial pattern')
        cell(ws, r, 1, 'Lag-1', sz=9, bold=True, align=_lft)
        cell(ws, r, 2, lag1, bold=True, fmt='0.00', sz=10,
             color=FOREST if lag1 > 0.3 else CLAY if lag1 < -0.3 else GRAY)
        cell(ws, r, 3, interp, color=GRAY, sz=9, align=_lft)
        for j in range(4, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1
    r += 1

    # ═══════════════════════════════════════════════
    # SECTION 8: PHASE COMPARISON
    # ═══════════════════════════════════════════════
    phases = defaultdict(list)
    for s in S:
        if s['phase'] != 'Pre': phases[s['phase']].append(s)
    if phases:
        sect(ws, r, '   Phase Comparison', NC); r += 1
        hdr(ws, r, ['Phase', 'n', 'Mean %', 'SD', 'Score', 'Habits', 'Med %', 'Pages %',
                     '', '', '', '', '', '', '', '', '', '', '', '', '', ''], NC)
        r += 1
        for pname in ['Foundation', 'Deepening', 'Integration']:
            ps = phases.get(pname, [])
            if not ps: continue
            cell(ws, r, 1, pname, bold=True, align=_lft, sz=9,
                 color=BRONZE if pname=='Foundation' else SLATE if pname=='Deepening' else FOREST)
            cell(ws, r, 2, len(ps), sz=9)
            pa = round(mean([s['pct'] for s in ps]))
            cell(ws, r, 3, pa/100, bold=True, fmt='0%', sz=9,
                 color=FOREST if pa >= 70 else CLAY if pa < 40 else INK)
            cell(ws, r, 4, round(stdev([s['pct'] for s in ps]),1), color=GRAY, sz=9)
            cell(ws, r, 5, round(mean([s['score'] for s in ps])), sz=9)
            cell(ws, r, 6, round(mean([s['h_n'] for s in ps]),1), fmt='0.0', sz=9)
            cell(ws, r, 7, round(mean([s['med'] for s in ps])*100), fmt='0"%"', sz=9)
            cell(ws, r, 8, round(mean([s['pages'] for s in ps])*100), fmt='0"%"', sz=9)
            for j in range(9, NC+1):
                ws.cell(row=r, column=j).fill = _white
                ws.cell(row=r, column=j).border = Border()
            r += 1
    r += 1

    # ═══════════════════════════════════════════════
    # SECTION 9: KEY METRICS (quick reference)
    # ═══════════════════════════════════════════════
    days_to_exam = (EXAM_DATE - now).days
    days_in_reset = max(0, (now - RESET_START).days + 1)

    sect(ws, r, '   Key Dates', NC); r += 1
    kv = [
        ('Days to CPA Exam', f'{days_to_exam}', EXAM_DATE.strftime('%b %d, %Y')),
        ('Reset Day', f'{days_in_reset} / 90', S[-1]['phase'] if S else ''),
        ('Current Streak', f'{streak([s["pct"] >= 50 for s in S])}d above 50%',
         f'Best: {longest([s["pct"] >= 50 for s in S])}d'),
    ]
    for label, val, desc in kv:
        cell(ws, r, 1, label, bold=True, sz=9, align=_lft)
        cell(ws, r, 2, val, bold=True, sz=10, color=BRONZE, align=_lft)
        cell(ws, r, 3, desc, color=GRAY, sz=8, align=_lft)
        for j in range(4, NC+1):
            ws.cell(row=r, column=j).fill = _white
            ws.cell(row=r, column=j).border = Border()
        r += 1

    r += 2  # gap before charts

    # ═══════════════════════════════════════════════
    # SECTION 10: CHART DATA (hidden helper rows) + CHARTS
    # ═══════════════════════════════════════════════
    # Charts reference the main DATA columns directly (G for completion, H for score, etc.)
    # But we need a helper area for rolling averages and score categories bar chart.

    # Helper: rolling 7d averages for score (write small table for chart)
    CH_DATA_START = r
    ch_label_row = r
    cell(ws, r, 1, 'Date', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 2, 'Compl%', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 3, '7d Avg', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 4, 'Score', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 5, '7d Score', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 6, 'Habits', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 7, 'Med', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 8, 'Pages', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 9, 'Study', color=GRAY, sz=7, fill=_smoke)
    cell(ws, r, 10, 'Gym', color=GRAY, sz=7, fill=_smoke)
    r += 1

    scores_raw = [s['score'] for s in S]
    pcts_raw = [s['pct'] for s in S]
    r7p = [mean(pcts_raw[max(0,i-6):i+1]) for i in range(N)]
    r7s = [mean(scores_raw[max(0,i-6):i+1]) for i in range(N)]

    for idx, s in enumerate(S):
        ws.cell(row=r, column=1, value=s['date']).font = F(GRAY, False, 7)
        ws.cell(row=r, column=2, value=s['pct'])
        ws.cell(row=r, column=3, value=round(r7p[idx], 1))
        ws.cell(row=r, column=4, value=s['score'])
        ws.cell(row=r, column=5, value=round(r7s[idx], 1))
        ws.cell(row=r, column=6, value=s['h_n'])
        ws.cell(row=r, column=7, value=int(s['med']))
        ws.cell(row=r, column=8, value=int(s['pages']))
        ws.cell(row=r, column=9, value=int(s['study']))
        ws.cell(row=r, column=10, value=int(s['gym_h']))
        r += 1

    CH_DATA_END = r - 1
    dates_ref = Reference(ws, min_col=1, min_row=CH_DATA_START+1, max_row=CH_DATA_END)

    # Chart dimensions and spacing
    # Each chart: width=28cm, height=12cm (~25 rows)
    # Gap between chart anchor points: 30 rows (safe clearance)
    CHART_W = 28
    CHART_H = 12
    CHART_GAP = 30

    def make_line(title, cols, colors, max_y=100):
        ch = LineChart()
        ch.title = title
        ch.style = 2
        ch.width, ch.height = CHART_W, CHART_H
        ch.y_axis.scaling.min = 0
        ch.y_axis.scaling.max = max_y
        ch.y_axis.numFmt = '0'
        ch.y_axis.majorGridlines = None
        ch.y_axis.delete = False
        ch.x_axis.tickLblPos = 'low'
        for col in cols:
            ref = Reference(ws, min_col=col, min_row=CH_DATA_START, max_row=CH_DATA_END)
            ch.add_data(ref, titles_from_data=True)
        ch.set_categories(dates_ref)
        for i, c in enumerate(colors):
            s = ch.series[i]
            s.graphicalProperties.line.solidFill = c
            s.graphicalProperties.line.width = 20000 if i == 0 else 28000
            if i > 0: s.graphicalProperties.line.dashStyle = 'dash'
        ch.legend.position = 'b'
        return ch

    def make_bar(title, cols, colors, max_y=4, stacked=False):
        ch = BarChart()
        ch.type = 'col'
        if stacked: ch.grouping = 'stacked'
        ch.title = title
        ch.style = 2
        ch.width, ch.height = CHART_W, CHART_H
        ch.y_axis.scaling.min = 0
        ch.y_axis.scaling.max = max_y
        ch.y_axis.numFmt = '0'
        ch.y_axis.majorGridlines = None
        ch.x_axis.tickLblPos = 'low'
        for col in cols:
            ref = Reference(ws, min_col=col, min_row=CH_DATA_START, max_row=CH_DATA_END)
            ch.add_data(ref, titles_from_data=True)
        ch.set_categories(dates_ref)
        for i, c in enumerate(colors):
            ch.series[i].graphicalProperties.solidFill = c
        ch.legend.position = 'b'
        return ch

    # Place charts below chart data
    chart_anchor = CH_DATA_END + 2

    # Chart 1: Completion Rate
    ws.add_chart(
        make_line('Completion Rate  %', [2, 3], [FOREST, GRAY]),
        f'A{chart_anchor}')

    # Chart 2: Daily Score
    ws.add_chart(
        make_line('Daily Score  /100', [4, 5], [BRONZE, GRAY]),
        f'A{chart_anchor + CHART_GAP}')

    # Chart 3: Behaviors stacked
    ws.add_chart(
        make_bar('Daily Behaviors', [7, 8, 9, 10],
                 [FOREST, BRONZE, SLATE, 'A78BFA'],
                 max_y=4, stacked=True),
        f'A{chart_anchor + CHART_GAP * 2}')

    # Chart 4: Score Categories (horizontal bar)
    cat_data_start = chart_anchor + CHART_GAP * 3
    ws.cell(row=cat_data_start, column=1, value='Category').font = F(GRAY, True, 7)
    ws.cell(row=cat_data_start, column=2, value='Mean').font = F(GRAY, True, 7)
    for i, cat in enumerate(SCORE_CATS):
        ws.cell(row=cat_data_start+1+i, column=1, value=cat).font = F(GRAY, False, 7)
        ws.cell(row=cat_data_start+1+i, column=2,
                value=round(mean([s['scores'][i] for s in S]), 1))

    c4 = BarChart()
    c4.type = 'bar'; c4.style = 2; c4.width = CHART_W; c4.height = CHART_H
    c4.title = 'Score Categories  /10'
    c4.x_axis.scaling.min = 0; c4.x_axis.scaling.max = 10
    c4.y_axis.majorGridlines = None
    cats_ref = Reference(ws, min_col=1, min_row=cat_data_start+1, max_row=cat_data_start+10)
    vals_ref = Reference(ws, min_col=2, min_row=cat_data_start, max_row=cat_data_start+10)
    c4.add_data(vals_ref, titles_from_data=True)
    c4.set_categories(cats_ref)
    c4.series[0].graphicalProperties.solidFill = BRONZE
    c4.legend = None
    ws.add_chart(c4, f'A{cat_data_start + 12}')

    # ═══════════════ SAVE ═══════════════
    for path in OUTPUTS:
        try:
            os.makedirs(os.path.dirname(path), exist_ok=True)
            wb.save(path)
            print(f'OK  {path}')
        except Exception as e:
            print(f'SKIP {path}: {e}')


if __name__ == '__main__':
    generate_report()