from reportlab.lib.pagesizes import A4
from reportlab.lib import colors
from reportlab.lib.units import mm
from reportlab.platypus import (
    SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, HRFlowable
)
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.enums import TA_CENTER, TA_LEFT
from reportlab.platypus import KeepTogether

OUTPUT = "/tmp/workspace/tooth-eruption/Tooth_Eruption_Quick_Reference.pdf"

# ── Colours ────────────────────────────────────────────────────────────────
C_BLUE_DARK   = colors.HexColor("#1A3A6B")
C_BLUE_MED    = colors.HexColor("#2F6BAD")
C_BLUE_LIGHT  = colors.HexColor("#D6E8FA")
C_GREEN_DARK  = colors.HexColor("#1A6B3A")
C_GREEN_MED   = colors.HexColor("#2FAD6B")
C_GREEN_LIGHT = colors.HexColor("#D6FAE8")
C_PURPLE_DARK = colors.HexColor("#4A1A6B")
C_PURPLE_MED  = colors.HexColor("#7B2FAD")
C_PURPLE_LIGHT= colors.HexColor("#EBD6FA")
C_AMBER       = colors.HexColor("#FFF3CD")
C_AMBER_DARK  = colors.HexColor("#856404")
C_GREY_LIGHT  = colors.HexColor("#F4F6F8")
C_GREY_MED    = colors.HexColor("#BFC9D1")
C_TEXT        = colors.HexColor("#1C2B3A")
C_WHITE       = colors.white

W, H = A4

doc = SimpleDocTemplate(
    OUTPUT,
    pagesize=A4,
    topMargin=14*mm,
    bottomMargin=14*mm,
    leftMargin=15*mm,
    rightMargin=15*mm,
)

styles = getSampleStyleSheet()

def sty(name, **kw):
    return ParagraphStyle(name, **kw)

title_style = sty("Title2",
    fontName="Helvetica-Bold", fontSize=20, textColor=C_WHITE,
    alignment=TA_CENTER, spaceAfter=2)

subtitle_style = sty("Sub2",
    fontName="Helvetica", fontSize=9, textColor=colors.HexColor("#BDD7F5"),
    alignment=TA_CENTER, spaceAfter=0)

section_style = sty("SecHdr",
    fontName="Helvetica-Bold", fontSize=11, textColor=C_WHITE,
    alignment=TA_LEFT, leftPadding=4)

body_style = sty("Body2",
    fontName="Helvetica", fontSize=8.5, textColor=C_TEXT,
    leading=12)

note_style = sty("Note",
    fontName="Helvetica-Oblique", fontSize=7.5, textColor=C_AMBER_DARK,
    leading=11)

footer_style = sty("Footer",
    fontName="Helvetica", fontSize=7, textColor=colors.HexColor("#8899AA"),
    alignment=TA_CENTER)

col_hdr_style = sty("ColHdr",
    fontName="Helvetica-Bold", fontSize=8, textColor=C_WHITE,
    alignment=TA_CENTER, leading=10)

cell_style = sty("Cell",
    fontName="Helvetica", fontSize=8, textColor=C_TEXT,
    alignment=TA_LEFT, leading=10)

cell_bold = sty("CellB",
    fontName="Helvetica-Bold", fontSize=8, textColor=C_TEXT,
    alignment=TA_LEFT, leading=10)

badge_ok  = sty("BadgeOK",  fontName="Helvetica-Bold", fontSize=7.5,
    textColor=C_GREEN_DARK, alignment=TA_CENTER)
badge_war = sty("BadgeWar", fontName="Helvetica-Bold", fontSize=7.5,
    textColor=C_AMBER_DARK, alignment=TA_CENTER)

# ── Story ──────────────────────────────────────────────────────────────────
story = []
TW = W - 30*mm   # total usable width

# ── BANNER ─────────────────────────────────────────────────────────────────
banner_data = [[
    Paragraph("Child Teeth Eruption — Quick Reference", title_style),
], [
    Paragraph("For GP Practice Use · Based on AAPD Reference Manual of Pediatric Dentistry", subtitle_style),
]]
banner = Table(banner_data, colWidths=[TW])
banner.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1,-1), C_BLUE_DARK),
    ("TOPPADDING",    (0,0), (-1, 0), 10),
    ("BOTTOMPADDING", (0,0), (-1, 0), 4),
    ("TOPPADDING",    (0,1), (-1, 1), 2),
    ("BOTTOMPADDING", (0,1), (-1, 1), 8),
    ("LEFTPADDING",   (0,0), (-1,-1), 6),
    ("RIGHTPADDING",  (0,0), (-1,-1), 6),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [6,6,6,6]),
]))
story.append(banner)
story.append(Spacer(1, 5*mm))

# ── HELPER: section header ──────────────────────────────────────────────────
def section_header(text, bg_color):
    t = Table([[Paragraph(text, section_style)]], colWidths=[TW])
    t.setStyle(TableStyle([
        ("BACKGROUND",    (0,0), (-1,-1), bg_color),
        ("TOPPADDING",    (0,0), (-1,-1), 5),
        ("BOTTOMPADDING", (0,0), (-1,-1), 5),
        ("LEFTPADDING",   (0,0), (-1,-1), 8),
        ("ROUNDEDCORNERS",(0,0), (-1,-1), [4,4,0,0]),
    ]))
    return t

# ══════════════════════════════════════════════════════════════════════════
# SECTION 1 — Primary (Milk) Teeth Eruption
# ══════════════════════════════════════════════════════════════════════════
story.append(section_header("① Primary (Milk) Teeth — Eruption", C_BLUE_MED))

hdr = [
    Paragraph("Tooth", col_hdr_style),
    Paragraph("Upper (Maxillary)", col_hdr_style),
    Paragraph("Lower (Mandibular)", col_hdr_style),
    Paragraph("Combined Range", col_hdr_style),
    Paragraph("Note", col_hdr_style),
]
rows_1 = [
    ["Central incisors",  "6–10 mo",  "5–8 mo",  "5–10 months",  "First teeth to appear"],
    ["Lateral incisors",  "8–12 mo",  "7–10 mo", "7–12 months",  "Lower before upper"],
    ["First molars",      "11–18 mo", "11–18 mo","11–18 months", "Gaps normal before canines"],
    ["Canines (cuspids)", "16–20 mo", "16–20 mo","16–20 months", "Upper often later"],
    ["Second molars",     "20–30 mo", "20–30 mo","20–30 months", "Complete primary set"],
]

def make_data_rows(rows, alt_bg):
    result = []
    for i, r in enumerate(rows):
        bg = alt_bg if i % 2 == 0 else C_WHITE
        result.append([
            Paragraph(r[0], cell_bold),
            Paragraph(r[1], cell_style),
            Paragraph(r[2], cell_style),
            Paragraph(r[3], cell_style),
            Paragraph(r[4], cell_style),
        ])
    return result

col_w1 = [44*mm, 32*mm, 35*mm, 36*mm, TW-147*mm]

data1 = [hdr] + make_data_rows(rows_1, C_BLUE_LIGHT)
t1 = Table(data1, colWidths=col_w1)
t1.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1, 0), C_BLUE_MED),
    ("BACKGROUND",    (0,1), (-1, 1), C_BLUE_LIGHT),
    ("BACKGROUND",    (0,2), (-1, 2), C_WHITE),
    ("BACKGROUND",    (0,3), (-1, 3), C_BLUE_LIGHT),
    ("BACKGROUND",    (0,4), (-1, 4), C_WHITE),
    ("BACKGROUND",    (0,5), (-1, 5), C_BLUE_LIGHT),
    ("ROWBACKGROUNDS",(0,0), (-1,-1), [C_BLUE_MED]),
    ("GRID",          (0,0), (-1,-1), 0.4, C_GREY_MED),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 5),
    ("RIGHTPADDING",  (0,0), (-1,-1), 5),
    ("VALIGN",        (0,0), (-1,-1), "MIDDLE"),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [0,0,4,4]),
]))
# Manual alternating rows
for i in range(1, len(data1)):
    bg = C_BLUE_LIGHT if i % 2 == 1 else C_WHITE
    t1.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))

story.append(t1)

# milestone note
note1 = Table([[
    Paragraph("★  All 20 primary teeth are usually present by 2½–3 years of age.", note_style)
]], colWidths=[TW])
note1.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1,-1), C_AMBER),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 8),
    ("BOX",           (0,0), (-1,-1), 0.5, C_AMBER_DARK),
]))
story.append(note1)
story.append(Spacer(1, 4*mm))

# ══════════════════════════════════════════════════════════════════════════
# SECTION 2 — Primary Teeth Shedding
# ══════════════════════════════════════════════════════════════════════════
story.append(section_header("② Primary Teeth — Shedding / Falling Out", C_GREEN_MED))

hdr2 = [
    Paragraph("Tooth", col_hdr_style),
    Paragraph("Shedding Age (Upper)", col_hdr_style),
    Paragraph("Shedding Age (Lower)", col_hdr_style),
    Paragraph("Combined Range", col_hdr_style),
    Paragraph("Clinical Note", col_hdr_style),
]
rows_2 = [
    ["Central incisors",  "7–8 yrs",  "6–7 yrs",  "6–8 years",  "Lower shed first"],
    ["Lateral incisors",  "8–9 yrs",  "7–8 yrs",  "7–9 years",  "Lower before upper"],
    ["Canines (cuspids)", "11–12 yrs","9–11 yrs", "9–12 years", ""],
    ["First molars",      "9–11 yrs", "9–11 yrs", "9–11 years", "Replaced by premolars"],
    ["Second molars",     "9–12 yrs", "11–13 yrs","9–13 years", "Last primary to shed"],
]

def make_data_rows2(rows, alt_bg):
    result = []
    for i, r in enumerate(rows):
        result.append([
            Paragraph(r[0], cell_bold),
            Paragraph(r[1], cell_style),
            Paragraph(r[2], cell_style),
            Paragraph(r[3], cell_style),
            Paragraph(r[4], cell_style),
        ])
    return result

col_w2 = [44*mm, 34*mm, 34*mm, 32*mm, TW-144*mm]

data2 = [hdr2] + make_data_rows2(rows_2, C_GREEN_LIGHT)
t2 = Table(data2, colWidths=col_w2)
for i in range(1, len(data2)):
    bg = C_GREEN_LIGHT if i % 2 == 1 else C_WHITE
    t2.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))
t2.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1, 0), C_GREEN_MED),
    ("GRID",          (0,0), (-1,-1), 0.4, C_GREY_MED),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 5),
    ("RIGHTPADDING",  (0,0), (-1,-1), 5),
    ("VALIGN",        (0,0), (-1,-1), "MIDDLE"),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [0,0,4,4]),
]))
for i in range(1, len(data2)):
    bg = C_GREEN_LIGHT if i % 2 == 1 else C_WHITE
    t2.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))

story.append(t2)
story.append(Spacer(1, 4*mm))

# ══════════════════════════════════════════════════════════════════════════
# SECTION 3 — Permanent Teeth Eruption
# ══════════════════════════════════════════════════════════════════════════
story.append(section_header("③ Permanent Teeth — Eruption", C_PURPLE_MED))

hdr3 = [
    Paragraph("Tooth", col_hdr_style),
    Paragraph("Upper (Maxillary)", col_hdr_style),
    Paragraph("Lower (Mandibular)", col_hdr_style),
    Paragraph("Combined Range", col_hdr_style),
    Paragraph("Note", col_hdr_style),
]
rows_3 = [
    ["First molars",             "5.5–7 yrs", "5.5–7 yrs", "5.5–7 years",  "First permanent teeth"],
    ["Central incisors",         "7–8 yrs",   "6–7 yrs",   "6–8 years",    "Replace milk incisors"],
    ["Lateral incisors",         "8–9 yrs",   "7–8 yrs",   "7–9 years",    ""],
    ["Canines (cuspids)",        "11–12 yrs", "9–11 yrs",  "9–12 years",   "Upper often later"],
    ["First premolars",          "10–11 yrs", "10–12 yrs", "10–12 years",  "Replace first molars"],
    ["Second premolars",         "10–12 yrs", "11–13 yrs", "10–13 years",  "Replace second molars"],
    ["Second molars",            "12–14 yrs", "12–14 yrs", "12–14 years",  "'12-year molars'"],
    ["Third molars (wisdom)",    "17–30 yrs", "17–30 yrs", "17–30 years",  "May not erupt at all"],
]

col_w3 = [48*mm, 30*mm, 33*mm, 32*mm, TW-143*mm]

data3 = [hdr3]
for r in rows_3:
    data3.append([
        Paragraph(r[0], cell_bold),
        Paragraph(r[1], cell_style),
        Paragraph(r[2], cell_style),
        Paragraph(r[3], cell_style),
        Paragraph(r[4], cell_style),
    ])

t3 = Table(data3, colWidths=col_w3)
t3.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1, 0), C_PURPLE_MED),
    ("GRID",          (0,0), (-1,-1), 0.4, C_GREY_MED),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 5),
    ("RIGHTPADDING",  (0,0), (-1,-1), 5),
    ("VALIGN",        (0,0), (-1,-1), "MIDDLE"),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [0,0,4,4]),
]))
for i in range(1, len(data3)):
    bg = C_PURPLE_LIGHT if i % 2 == 1 else C_WHITE
    t3.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))

story.append(t3)

note3 = Table([[
    Paragraph("★  Adults have 28 permanent teeth (32 including wisdom teeth).", note_style)
]], colWidths=[TW])
note3.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1,-1), C_AMBER),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 8),
    ("BOX",           (0,0), (-1,-1), 0.5, C_AMBER_DARK),
]))
story.append(note3)
story.append(Spacer(1, 4*mm))

# ══════════════════════════════════════════════════════════════════════════
# SECTION 4 — Timeline at a Glance
# ══════════════════════════════════════════════════════════════════════════
story.append(section_header("④ Age-wise Timeline at a Glance", C_BLUE_DARK))

timeline_hdr = [
    Paragraph("Age", col_hdr_style),
    Paragraph("Expected Event", col_hdr_style),
    Paragraph("Teeth", col_hdr_style),
    Paragraph("Action for GP", col_hdr_style),
]
timeline_rows = [
    ["5–10 months",  "1st primary teeth erupt",         "Lower central incisors",              "Start brushing; first dental visit by age 1"],
    ["7–12 months",  "More primaries erupt",             "All incisors (upper & lower)",        "Check for early childhood caries"],
    ["11–18 months", "Primary molars appear",            "First primary molars",                "Advise fluoride toothpaste (pea-size)"],
    ["16–20 months", "Canines erupt",                    "All 4 canines",                       "Reassure parents re: teething discomfort"],
    ["20–30 months", "Full primary set complete",        "Second primary molars",               "20 teeth present by age 2.5–3 yrs"],
    ["5.5–7 years",  "First permanent teeth erupt",      "Lower 6-year molars + lower incisors","Refer to dentist for fissure sealants"],
    ["6–9 years",    "Permanent incisors replace milk",  "All 4 central + 4 lateral incisors",  "Mixed dentition phase — normal gaps"],
    ["9–13 years",   "Premolars + canines erupt",        "All premolars & canines",             "Orthodontic referral if crowding"],
    ["12–14 years",  "Second permanent molars",          "'12-year molars'",                    "Fissure sealants recommended"],
    ["17–30 years",  "Wisdom teeth (variable)",          "Third molars — may not erupt",        "Refer if impacted/symptomatic"],
]

col_wt = [28*mm, 47*mm, 50*mm, TW-125*mm]
tl_data = [timeline_hdr]
for r in timeline_rows:
    tl_data.append([
        Paragraph(r[0], cell_bold),
        Paragraph(r[1], cell_style),
        Paragraph(r[2], cell_style),
        Paragraph(r[3], cell_style),
    ])

tl = Table(tl_data, colWidths=col_wt)
tl.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1, 0), C_BLUE_DARK),
    ("GRID",          (0,0), (-1,-1), 0.4, C_GREY_MED),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 5),
    ("RIGHTPADDING",  (0,0), (-1,-1), 5),
    ("VALIGN",        (0,0), (-1,-1), "MIDDLE"),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [0,0,4,4]),
]))
for i in range(1, len(tl_data)):
    bg = C_GREY_LIGHT if i % 2 == 1 else C_WHITE
    tl.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))

story.append(tl)
story.append(Spacer(1, 4*mm))

# ══════════════════════════════════════════════════════════════════════════
# SECTION 5 — Red Flags
# ══════════════════════════════════════════════════════════════════════════
story.append(section_header("⑤ Red Flags — When to Refer", colors.HexColor("#C0392B")))

rf_data = [
    [Paragraph("⚠ Red Flag", col_hdr_style), Paragraph("Definition / Threshold", col_hdr_style)],
    [Paragraph("No tooth by 13 months", cell_bold),
     Paragraph("Evaluate for ectodermal dysplasia, hypothyroidism, or rickets", cell_style)],
    [Paragraph("Delayed full primary set", cell_bold),
     Paragraph("No teeth by age 3.5 — systemic workup warranted", cell_style)],
    [Paragraph("Asymmetric eruption", cell_bold),
     Paragraph(">6 month difference between contralateral teeth — dental X-ray", cell_style)],
    [Paragraph("Very early eruption (<4 months)", cell_bold),
     Paragraph("Natal/neonatal teeth — check for Riga-Fede syndrome risk", cell_style)],
    [Paragraph("No permanent teeth by age 8", cell_bold),
     Paragraph("Consider hypodontia or retained primary teeth — OPG X-ray", cell_style)],
    [Paragraph("Crowding / malocclusion", cell_bold),
     Paragraph("Orthodontic referral by age 7–8 for early assessment", cell_style)],
]

col_wrf = [55*mm, TW-55*mm]
rf = Table(rf_data, colWidths=col_wrf)
rf.setStyle(TableStyle([
    ("BACKGROUND",    (0,0), (-1, 0), colors.HexColor("#C0392B")),
    ("GRID",          (0,0), (-1,-1), 0.4, C_GREY_MED),
    ("TOPPADDING",    (0,0), (-1,-1), 4),
    ("BOTTOMPADDING", (0,0), (-1,-1), 4),
    ("LEFTPADDING",   (0,0), (-1,-1), 5),
    ("RIGHTPADDING",  (0,0), (-1,-1), 5),
    ("VALIGN",        (0,0), (-1,-1), "MIDDLE"),
    ("ROUNDEDCORNERS",(0,0), (-1,-1), [0,0,4,4]),
]))
for i in range(1, len(rf_data)):
    bg = colors.HexColor("#FDECEA") if i % 2 == 1 else C_WHITE
    rf.setStyle(TableStyle([("BACKGROUND", (0,i), (-1,i), bg)]))

story.append(rf)
story.append(Spacer(1, 5*mm))

# ── FOOTER ─────────────────────────────────────────────────────────────────
footer_line = HRFlowable(width=TW, thickness=0.5, color=C_GREY_MED)
story.append(footer_line)
story.append(Spacer(1, 2*mm))
story.append(Paragraph(
    "Data source: AAPD Reference Manual of Pediatric Dentistry (Logan-Kronfeld chart, adapted) · "
    "Color Atlas of Human Anatomy Vol.2 · Pfenninger & Fowler's Procedures for Primary Care  "
    "Ages are approximate and vary by sex, ethnicity, and nutrition. Consult a dentist for concerns.",
    footer_style
))

# ── BUILD ──────────────────────────────────────────────────────────────────
doc.build(story)
print("PDF created:", OUTPUT)

"""
Dental Milestones Tracking Sheet
- Sheet 1: Patient Tracker (one row per tooth, with date observed, status, notes)
- Sheet 2: Reference Chart (AAPD eruption/shedding data)
- Sheet 3: Instructions
"""

import openpyxl
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, GradientFill
)
from openpyxl.styles.numbers import FORMAT_DATE_DDMMYY
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.formatting.rule import ColorScaleRule, FormulaRule
from openpyxl.chart import BarChart, Reference
from openpyxl.drawing.image import Image
from openpyxl.worksheet.table import Table, TableStyleInfo
import datetime

OUTPUT = "/tmp/workspace/tooth-eruption/Dental_Milestones_Tracker.xlsx"

wb = openpyxl.Workbook()

# ── Colour palette ──────────────────────────────────────────────────────────
BLUE_DARK    = "1A3A6B"
BLUE_MED     = "2F6BAD"
BLUE_LIGHT   = "D6E8FA"
BLUE_XLIGHT  = "EEF5FC"
GREEN_DARK   = "1A6B3A"
GREEN_MED    = "2FAD6B"
GREEN_LIGHT  = "D6FAE8"
GREEN_XLIGHT = "EEF9F3"
PURPLE_DARK  = "4A1A6B"
PURPLE_MED   = "7B2FAD"
PURPLE_LIGHT = "EBD6FA"
AMBER        = "FFF3CD"
AMBER_DARK   = "856404"
RED_LIGHT    = "FDECEA"
RED_DARK     = "C0392B"
GREY_LIGHT   = "F4F6F8"
GREY_MED     = "BFC9D1"
WHITE        = "FFFFFF"
TEXT_DARK    = "1C2B3A"

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

def font(bold=False, size=10, color=TEXT_DARK, italic=False, name="Arial"):
    return Font(bold=bold, size=size, color=color, italic=italic, name=name)

def align(h="left", v="center", wrap=False):
    return Alignment(horizontal=h, vertical=v, wrap_text=wrap)

def border_thin(sides="all"):
    s = Side(style="thin", color=GREY_MED)
    n = Side(style=None)
    if sides == "all":
        return Border(left=s, right=s, top=s, bottom=s)
    if sides == "bottom":
        return Border(bottom=s)
    if sides == "outer":
        return Border(left=s, right=s, top=s, bottom=s)
    return Border()

def border_medium():
    s = Side(style="medium", color=BLUE_DARK)
    return Border(left=s, right=s, top=s, bottom=s)

def set_cell(ws, row, col, value, bold=False, size=10, bg=None, fg=TEXT_DARK,
             h_align="left", wrap=False, italic=False, border=True, number_format=None):
    c = ws.cell(row=row, column=col, value=value)
    c.font = font(bold=bold, size=size, color=fg, italic=italic)
    c.alignment = align(h=h_align, wrap=wrap)
    if bg:
        c.fill = fill(bg)
    if border:
        c.border = border_thin()
    if number_format:
        c.number_format = number_format
    return c

# ════════════════════════════════════════════════════════════════════════════
# SHEET 1: PATIENT TRACKER
# ════════════════════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "Patient Tracker"
ws1.sheet_view.showGridLines = False
ws1.freeze_panes = "A7"

# ── Banner ──────────────────────────────────────────────────────────────────
ws1.merge_cells("A1:N1")
c = ws1["A1"]
c.value = "CHILD DENTAL MILESTONES TRACKER"
c.font = Font(name="Arial", bold=True, size=18, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = align(h="center", v="center")
ws1.row_dimensions[1].height = 36

ws1.merge_cells("A2:N2")
c = ws1["A2"]
c.value = "For GP Practice Use · Based on AAPD Reference Manual of Pediatric Dentistry"
c.font = Font(name="Arial", size=9, color="BDD7F5", italic=True)
c.fill = fill(BLUE_DARK)
c.alignment = align(h="center", v="center")
ws1.row_dimensions[2].height = 18

# ── Patient info block ───────────────────────────────────────────────────────
ws1.merge_cells("A3:N3")
ws1["A3"].value = ""
ws1["A3"].fill = fill(BLUE_XLIGHT)
ws1.row_dimensions[3].height = 6

info_labels = [
    ("A4", "Patient Name:", "D4", ""),
    ("E4", "DOB:", "G4", ""),
    ("H4", "Sex:", "I4", ""),
    ("J4", "Visit Date:", "L4", ""),
    ("M4", "Clinician:", "N4", ""),
]
for lbl_cell, lbl_val, val_cell, _ in info_labels:
    c = ws1[lbl_cell]
    c.value = lbl_val
    c.font = Font(name="Arial", bold=True, size=9, color=BLUE_DARK)
    c.fill = fill(BLUE_XLIGHT)
    c.alignment = align(h="right", v="center")

    v = ws1[val_cell]
    v.fill = fill(WHITE)
    v.border = Border(bottom=Side(style="thin", color=BLUE_MED))
    v.font = Font(name="Arial", size=9)

ws1.row_dimensions[4].height = 22

ws1.merge_cells("A5:N5")
ws1["A5"].value = ""
ws1["A5"].fill = fill(BLUE_XLIGHT)
ws1.row_dimensions[5].height = 6

# ── Column headers ────────────────────────────────────────────────────────────
headers = [
    "Tooth Name",
    "Phase",
    "Expected Age\n(Lower Bound)",
    "Expected Age\n(Upper Bound)",
    "Units",
    "Date\nObserved",
    "Child Age\nat Observation",
    "Status",
    "Early / On Time /\nDelayed",
    "Upper / Lower",
    "Left / Right",
    "Caries\nPresent?",
    "Referred?",
    "Notes / Observations",
]

col_widths = [22, 16, 14, 14, 8, 14, 16, 16, 16, 12, 12, 12, 12, 30]

for i, (h, w) in enumerate(zip(headers, col_widths), start=1):
    c = ws1.cell(row=6, column=i, value=h)
    c.font = Font(name="Arial", bold=True, size=9, color=WHITE)
    c.fill = fill(BLUE_MED)
    c.alignment = align(h="center", v="center", wrap=True)
    c.border = border_thin()
    ws1.column_dimensions[get_column_letter(i)].width = w

ws1.row_dimensions[6].height = 32

# ── Data rows ────────────────────────────────────────────────────────────────
# (tooth_name, phase, exp_low, exp_high, units, upper_lower, left_right, phase_color)
teeth_data = [
    # Primary eruption
    ("Central Incisor (Lower)",   "Primary Eruption",  5,  10,  "months", "Lower", "Both", BLUE_LIGHT),
    ("Central Incisor (Upper)",   "Primary Eruption",  6,  10,  "months", "Upper", "Both", BLUE_LIGHT),
    ("Lateral Incisor (Lower)",   "Primary Eruption",  7,  10,  "months", "Lower", "Both", BLUE_LIGHT),
    ("Lateral Incisor (Upper)",   "Primary Eruption",  8,  12,  "months", "Upper", "Both", BLUE_LIGHT),
    ("First Molar (Primary)",     "Primary Eruption",  11, 18,  "months", "Both",  "Both", BLUE_LIGHT),
    ("Canine / Cuspid (Primary)", "Primary Eruption",  16, 20,  "months", "Both",  "Both", BLUE_LIGHT),
    ("Second Molar (Primary)",    "Primary Eruption",  20, 30,  "months", "Both",  "Both", BLUE_LIGHT),
    # Primary shedding
    ("Central Incisor (Lower)",   "Primary Shedding",  6,  7,   "years",  "Lower", "Both", GREEN_LIGHT),
    ("Central Incisor (Upper)",   "Primary Shedding",  7,  8,   "years",  "Upper", "Both", GREEN_LIGHT),
    ("Lateral Incisor (Lower)",   "Primary Shedding",  7,  8,   "years",  "Lower", "Both", GREEN_LIGHT),
    ("Lateral Incisor (Upper)",   "Primary Shedding",  8,  9,   "years",  "Upper", "Both", GREEN_LIGHT),
    ("Canine / Cuspid (Lower)",   "Primary Shedding",  9,  11,  "years",  "Lower", "Both", GREEN_LIGHT),
    ("Canine / Cuspid (Upper)",   "Primary Shedding",  11, 12,  "years",  "Upper", "Both", GREEN_LIGHT),
    ("First Molar (Primary)",     "Primary Shedding",  9,  11,  "years",  "Both",  "Both", GREEN_LIGHT),
    ("Second Molar (Primary)",    "Primary Shedding",  9,  13,  "years",  "Both",  "Both", GREEN_LIGHT),
    # Permanent eruption
    ("First Molar (Permanent)",   "Permanent Eruption", 5.5, 7,  "years",  "Both",  "Both", PURPLE_LIGHT),
    ("Central Incisor (Lower)",   "Permanent Eruption", 6,   7,  "years",  "Lower", "Both", PURPLE_LIGHT),
    ("Central Incisor (Upper)",   "Permanent Eruption", 7,   8,  "years",  "Upper", "Both", PURPLE_LIGHT),
    ("Lateral Incisor (Lower)",   "Permanent Eruption", 7,   8,  "years",  "Lower", "Both", PURPLE_LIGHT),
    ("Lateral Incisor (Upper)",   "Permanent Eruption", 8,   9,  "years",  "Upper", "Both", PURPLE_LIGHT),
    ("Canine / Cuspid (Lower)",   "Permanent Eruption", 9,  11,  "years",  "Lower", "Both", PURPLE_LIGHT),
    ("Canine / Cuspid (Upper)",   "Permanent Eruption", 11, 12,  "years",  "Upper", "Both", PURPLE_LIGHT),
    ("First Premolar (Upper)",    "Permanent Eruption", 10, 11,  "years",  "Upper", "Both", PURPLE_LIGHT),
    ("First Premolar (Lower)",    "Permanent Eruption", 10, 12,  "years",  "Lower", "Both", PURPLE_LIGHT),
    ("Second Premolar (Upper)",   "Permanent Eruption", 10, 12,  "years",  "Upper", "Both", PURPLE_LIGHT),
    ("Second Premolar (Lower)",   "Permanent Eruption", 11, 13,  "years",  "Lower", "Both", PURPLE_LIGHT),
    ("Second Molar (Permanent)",  "Permanent Eruption", 12, 14,  "years",  "Both",  "Both", PURPLE_LIGHT),
    ("Third Molar / Wisdom",      "Permanent Eruption", 17, 30,  "years",  "Both",  "Both", PURPLE_LIGHT),
]

# Status dropdown
dv_status = DataValidation(
    type="list",
    formula1='"Not yet,Erupted/Shed,Partial,Absent,Impacted"',
    allow_blank=True,
    showDropDown=False,
    showErrorMessage=True,
    errorTitle="Invalid entry",
    error="Choose from the list."
)
ws1.add_data_validation(dv_status)

# Early/Delayed dropdown
dv_timing = DataValidation(
    type="list",
    formula1='"Early,On Time,Delayed,Not assessed"',
    allow_blank=True,
    showDropDown=False
)
ws1.add_data_validation(dv_timing)

# Caries dropdown
dv_caries = DataValidation(
    type="list",
    formula1='"No,Yes - early,Yes - cavitated,Suspected"',
    allow_blank=True,
    showDropDown=False
)
ws1.add_data_validation(dv_caries)

# Referred dropdown
dv_referred = DataValidation(
    type="list",
    formula1='"No,Yes - Dentist,Yes - Orthodontist,Yes - Oral surgeon"',
    allow_blank=True,
    showDropDown=False
)
ws1.add_data_validation(dv_referred)

phase_colors = {
    "Primary Eruption":  BLUE_LIGHT,
    "Primary Shedding":  GREEN_LIGHT,
    "Permanent Eruption": PURPLE_LIGHT,
}
phase_dark = {
    "Primary Eruption":  BLUE_MED,
    "Primary Shedding":  GREEN_MED,
    "Permanent Eruption": PURPLE_MED,
}

# Phase section headers
current_phase = None
data_start_row = 7
row = data_start_row

for tooth in teeth_data:
    tooth_name, phase, low, high, units, ul, lr, bg_col = tooth

    # Phase group header row
    if phase != current_phase:
        current_phase = phase
        ws1.merge_cells(f"A{row}:N{row}")
        c = ws1[f"A{row}"]
        c.value = f"  {phase.upper()}"
        c.font = Font(name="Arial", bold=True, size=9, color=WHITE)
        c.fill = fill(phase_dark[phase])
        c.alignment = align(h="left", v="center")
        ws1.row_dimensions[row].height = 18
        row += 1

    # Data row
    row_bg = bg_col
    cols_values = [
        tooth_name, phase, low, high, units,
        None,   # Date Observed - user fills
        None,   # Age at observation - user fills
        None,   # Status - dropdown
        None,   # Early/Delayed - dropdown
        ul, lr,
        None,   # Caries - dropdown
        None,   # Referred - dropdown
        "",     # Notes
    ]
    for ci, val in enumerate(cols_values, start=1):
        c = ws1.cell(row=row, column=ci, value=val)
        c.font = Font(name="Arial", size=9, color=TEXT_DARK)
        c.alignment = align(h="left" if ci not in [3,4,5,7] else "center", v="center", wrap=(ci==14))
        c.fill = fill(row_bg)
        c.border = border_thin()

    # Format date column
    ws1.cell(row=row, column=6).number_format = "DD-MMM-YYYY"
    ws1.cell(row=row, column=6).fill = fill(WHITE)
    ws1.cell(row=row, column=7).fill = fill(WHITE)

    # Register dropdowns
    dv_status.add(ws1.cell(row=row, column=8))
    dv_timing.add(ws1.cell(row=row, column=9))
    dv_caries.add(ws1.cell(row=row, column=12))
    dv_referred.add(ws1.cell(row=row, column=13))

    ws1.row_dimensions[row].height = 18
    row += 1

# ── Conditional formatting: Status column ────────────────────────────────────
# "Erupted/Shed" = green; "Absent/Impacted" = red; "Partial" = amber
last_data_row = row - 1
green_rule = FormulaRule(
    formula=['=$H7="Erupted/Shed"'],
    fill=fill("C8F7C5"),
    font=Font(color="1A6B3A", name="Arial", size=9, bold=True)
)
red_rule = FormulaRule(
    formula=['=OR($H7="Absent",$H7="Impacted")'],
    fill=fill(RED_LIGHT),
    font=Font(color=RED_DARK, name="Arial", size=9, bold=True)
)
amber_rule = FormulaRule(
    formula=['=$H7="Partial"'],
    fill=fill(AMBER),
    font=Font(color=AMBER_DARK, name="Arial", size=9, bold=True)
)
delay_rule = FormulaRule(
    formula=['=$I7="Delayed"'],
    fill=fill(RED_LIGHT),
    font=Font(color=RED_DARK, name="Arial", size=9)
)
early_rule = FormulaRule(
    formula=['=$I7="Early"'],
    fill=fill(AMBER),
    font=Font(color=AMBER_DARK, name="Arial", size=9)
)

range_str = f"H7:H{last_data_row}"
ws1.conditional_formatting.add(range_str, green_rule)
ws1.conditional_formatting.add(range_str, red_rule)
ws1.conditional_formatting.add(range_str, amber_rule)
timing_range = f"I7:I{last_data_row}"
ws1.conditional_formatting.add(timing_range, delay_rule)
ws1.conditional_formatting.add(timing_range, early_rule)

# ── Summary box ──────────────────────────────────────────────────────────────
sr = last_data_row + 2
ws1.merge_cells(f"A{sr}:N{sr}")
c = ws1[f"A{sr}"]
c.value = "⚠  Ages are approximate and vary by sex, ethnicity, and nutrition. Consult a dentist if eruption is delayed by more than 6 months relative to the expected range."
c.font = Font(name="Arial", size=8.5, italic=True, color=AMBER_DARK)
c.fill = fill(AMBER)
c.alignment = align(h="left", v="center", wrap=True)
c.border = Border(
    left=Side(style="thin", color=AMBER_DARK),
    right=Side(style="thin", color=AMBER_DARK),
    top=Side(style="thin", color=AMBER_DARK),
    bottom=Side(style="thin", color=AMBER_DARK),
)
ws1.row_dimensions[sr].height = 22

# ════════════════════════════════════════════════════════════════════════════
# SHEET 2: REFERENCE CHART
# ════════════════════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("Reference Chart")
ws2.sheet_view.showGridLines = False

# Banner
ws2.merge_cells("A1:H1")
c = ws2["A1"]
c.value = "AAPD TOOTH ERUPTION REFERENCE — CORRECTED DATA"
c.font = Font(name="Arial", bold=True, size=14, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = align(h="center", v="center")
ws2.row_dimensions[1].height = 28

ws2.merge_cells("A2:H2")
c = ws2["A2"]
c.value = "Source: American Academy of Pediatric Dentistry Reference Manual (Logan-Kronfeld chart, adapted)"
c.font = Font(name="Arial", size=8.5, italic=True, color="BDD7F5")
c.fill = fill(BLUE_DARK)
c.alignment = align(h="center")
ws2.row_dimensions[2].height = 16

def write_ref_section(ws, start_row, title, bg_hdr, bg_alt, col_headers, data_rows, col_widths_ref):
    # Section header
    ws.merge_cells(f"A{start_row}:H{start_row}")
    c = ws[f"A{start_row}"]
    c.value = title
    c.font = Font(name="Arial", bold=True, size=11, color=WHITE)
    c.fill = fill(bg_hdr)
    c.alignment = align(h="left", v="center")
    ws.row_dimensions[start_row].height = 22
    r = start_row + 1

    # Column headers
    for ci, h in enumerate(col_headers, start=1):
        c = ws.cell(row=r, column=ci, value=h)
        c.font = Font(name="Arial", bold=True, size=9, color=WHITE)
        c.fill = fill(bg_hdr)
        c.alignment = align(h="center", v="center", wrap=True)
        c.border = border_thin()
    ws.row_dimensions[r].height = 28
    r += 1

    for i, row_data in enumerate(data_rows):
        bg = bg_alt if i % 2 == 0 else WHITE
        for ci, val in enumerate(row_data, start=1):
            c = ws.cell(row=r, column=ci, value=val)
            c.font = Font(name="Arial", size=9, color=TEXT_DARK,
                          bold=(ci == 1))
            c.fill = fill(bg)
            c.alignment = align(h="center" if ci > 1 else "left", v="center")
            c.border = border_thin()
        ws.row_dimensions[r].height = 16
        r += 1

    # set col widths
    for ci, w in enumerate(col_widths_ref, start=1):
        ws.column_dimensions[get_column_letter(ci)].width = w

    return r + 1  # next start row with gap

ref_col_w = [26, 14, 14, 14, 14, 16, 16, 20]

# Section 1 – Primary Eruption
prim_erupt_headers = [
    "Tooth", "Upper\n(months)", "Lower\n(months)", "Combined\nRange",
    "Units", "All 20 primary teeth present by:", "", "Notes"
]
prim_erupt_data = [
    ["Central incisors",  "6–10", "5–8",  "5–10 months", "months", "Age 2.5–3 years", "", "Lower erupt first"],
    ["Lateral incisors",  "8–12", "7–10", "7–12 months", "months", "", "", ""],
    ["First molars",      "11–18","11–18","11–18 months","months", "", "", "Gaps normal before canines"],
    ["Canines (cuspids)", "16–20","16–20","16–20 months","months", "", "", ""],
    ["Second molars",     "20–30","20–30","20–30 months","months", "", "", "Completes primary set"],
]
next_row = write_ref_section(ws2, 4, "① PRIMARY (MILK) TEETH — ERUPTION", BLUE_MED, BLUE_LIGHT,
                              prim_erupt_headers, prim_erupt_data, ref_col_w)

# Section 2 – Primary Shedding
prim_shed_headers = [
    "Tooth", "Upper\nShedding", "Lower\nShedding", "Combined\nRange",
    "Units", "", "", "Notes"
]
prim_shed_data = [
    ["Central incisors",  "7–8 yrs",  "6–7 yrs",  "6–8 years",  "years", "", "", "Lower shed first"],
    ["Lateral incisors",  "8–9 yrs",  "7–8 yrs",  "7–9 years",  "years", "", "", ""],
    ["Canines (cuspids)", "11–12 yrs","9–11 yrs", "9–12 years", "years", "", "", ""],
    ["First molars",      "9–11 yrs", "9–11 yrs", "9–11 years", "years", "", "", "Replaced by premolars"],
    ["Second molars",     "9–12 yrs", "11–13 yrs","9–13 years", "years", "", "", "Last primary to shed"],
]
next_row = write_ref_section(ws2, next_row, "② PRIMARY TEETH — SHEDDING / FALLING OUT", GREEN_MED, GREEN_LIGHT,
                              prim_shed_headers, prim_shed_data, ref_col_w)

# Section 3 – Permanent Eruption
perm_erupt_headers = [
    "Tooth", "Upper\n(years)", "Lower\n(years)", "Combined\nRange",
    "Units", "", "", "Notes"
]
perm_erupt_data = [
    ["First molars",             "5.5–7",  "5.5–7", "5.5–7 years",  "years", "", "", "First permanent teeth"],
    ["Central incisors",         "7–8",    "6–7",   "6–8 years",    "years", "", "", "Replace milk incisors"],
    ["Lateral incisors",         "8–9",    "7–8",   "7–9 years",    "years", "", "", ""],
    ["Canines (cuspids)",        "11–12",  "9–11",  "9–12 years",   "years", "", "", "Upper often later"],
    ["First premolars",          "10–11",  "10–12", "10–12 years",  "years", "", "", "Replace first molars"],
    ["Second premolars",         "10–12",  "11–13", "10–13 years",  "years", "", "", "Replace second molars"],
    ["Second molars",            "12–14",  "12–14", "12–14 years",  "years", "", "", "'12-year molars'"],
    ["Third molars (wisdom)",    "17–30",  "17–30", "17–30 years",  "years", "", "", "May not erupt — refer if impacted"],
]
next_row = write_ref_section(ws2, next_row, "③ PERMANENT TEETH — ERUPTION", PURPLE_MED, PURPLE_LIGHT,
                              perm_erupt_headers, perm_erupt_data, ref_col_w)

# Red flags section
ws2.merge_cells(f"A{next_row}:H{next_row}")
c = ws2[f"A{next_row}"]
c.value = "④ RED FLAGS — WHEN TO REFER"
c.font = Font(name="Arial", bold=True, size=11, color=WHITE)
c.fill = fill(RED_DARK)
c.alignment = align(h="left", v="center")
ws2.row_dimensions[next_row].height = 22
next_row += 1

rf_hdr = ["Red Flag / Finding", "Threshold", "Action / Referral", "", "", "", "", ""]
for ci, h in enumerate(rf_hdr[:3], start=1):
    c = ws2.cell(row=next_row, column=ci, value=h)
    c.font = Font(name="Arial", bold=True, size=9, color=WHITE)
    c.fill = fill(RED_DARK)
    c.alignment = align(h="center", v="center")
    c.border = border_thin()
ws2.merge_cells(f"D{next_row}:H{next_row}")
ws2.row_dimensions[next_row].height = 22
next_row += 1

red_flags = [
    ["No tooth by 13 months",         "Evaluate for ectodermal dysplasia, hypothyroidism, rickets",       "Investigate + refer"],
    ["No full primary set by 3.5 yrs","All 20 teeth should be present by 30–36 months",                   "Systemic workup"],
    ["Asymmetric eruption",           ">6 months between contralateral teeth",                             "Dental X-ray + refer"],
    ["Very early eruption (<4 months)","Natal/neonatal teeth — Riga-Fede risk",                            "Paediatric dental referral"],
    ["No permanent teeth by age 8",   "Consider hypodontia or retained primary teeth",                    "OPG X-ray + dental referral"],
    ["Crowding / malocclusion",       "Visible crowding in mixed dentition",                               "Orthodontic referral by age 7–8"],
    ["Impacted wisdom teeth",         "Pain, pericoronitis, cysts, resorption of 2nd molar",              "Oral surgeon referral"],
]
for i, row_data in enumerate(red_flags):
    bg = RED_LIGHT if i % 2 == 0 else WHITE
    for ci, val in enumerate(row_data, start=1):
        c = ws2.cell(row=next_row, column=ci, value=val)
        c.font = Font(name="Arial", size=9, color=TEXT_DARK, bold=(ci == 1))
        c.fill = fill(bg)
        c.alignment = align(h="left" if ci < 3 else "left", v="center", wrap=True)
        c.border = border_thin()
    ws2.merge_cells(f"D{next_row}:H{next_row}")
    ws2.row_dimensions[next_row].height = 20
    next_row += 1

# ════════════════════════════════════════════════════════════════════════════
# SHEET 3: INSTRUCTIONS
# ════════════════════════════════════════════════════════════════════════════
ws3 = wb.create_sheet("How to Use")
ws3.sheet_view.showGridLines = False

ws3.column_dimensions["A"].width = 4
ws3.column_dimensions["B"].width = 28
ws3.column_dimensions["C"].width = 60

ws3.merge_cells("A1:C1")
c = ws3["A1"]
c.value = "HOW TO USE THIS TRACKER"
c.font = Font(name="Arial", bold=True, size=16, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = align(h="center", v="center")
ws3.row_dimensions[1].height = 32

instructions = [
    ("", "", ""),
    ("", "SHEET: Patient Tracker", ""),
    ("", "Patient Info (Row 4)", "Fill in Name, Date of Birth, Sex, Visit Date, and Clinician name in the yellow input cells."),
    ("", "Date Observed (Col F)", "Enter the date when you observed the tooth erupting or shedding. Format: DD-MMM-YYYY."),
    ("", "Child Age at Observation (Col G)", "Enter the child's age in months (for primary eruption) or years (for shedding/permanent)."),
    ("", "Status (Col H)", "Select from dropdown: Not yet / Erupted-Shed / Partial / Absent / Impacted. Colour-coded automatically."),
    ("", "Early / On Time / Delayed (Col I)", "Select from dropdown. 'Delayed' highlights red, 'Early' highlights amber automatically."),
    ("", "Caries Present? (Col L)", "Note any caries at the same visit: No / Yes-early / Yes-cavitated / Suspected."),
    ("", "Referred? (Col M)", "Log any referrals made: Dentist / Orthodontist / Oral surgeon."),
    ("", "Notes (Col N)", "Free text for clinical observations, parental concerns, or follow-up actions."),
    ("", "", ""),
    ("", "SHEET: Reference Chart", ""),
    ("", "What is it?", "AAPD-based corrected reference data for all 3 phases: primary eruption, primary shedding, and permanent eruption."),
    ("", "Red Flags table", "Use to decide when to refer. Key threshold: >6 months outside expected range = dental review."),
    ("", "", ""),
    ("", "COLOUR CODING GUIDE", ""),
    ("", "Blue rows",   "Primary (milk) teeth eruption phase"),
    ("", "Green rows",  "Primary teeth shedding / falling out phase"),
    ("", "Purple rows", "Permanent teeth eruption phase"),
    ("", "Green cell",  "Status = Erupted / Shed (normal, on track)"),
    ("", "Amber cell",  "Status = Partial, or Timing = Early"),
    ("", "Red cell",    "Status = Absent / Impacted, or Timing = Delayed — review needed"),
    ("", "", ""),
    ("", "DATA SOURCE", ""),
    ("", "Primary source", "American Academy of Pediatric Dentistry (AAPD) Reference Manual of Pediatric Dentistry — Logan-Kronfeld chart (adapted)."),
    ("", "Supporting sources", "Color Atlas of Human Anatomy Vol.2 (Thieme); Pfenninger & Fowler's Procedures for Primary Care (3rd ed); Tintinalli's Emergency Medicine."),
    ("", "Disclaimer", "Ages are approximate and vary by sex, ethnicity, and nutrition. This tracker is a clinical aide, not a diagnostic tool. Always apply clinical judgement."),
]

for i, (a, b, c_val) in enumerate(instructions, start=2):
    is_section = (b.isupper() and b.strip() and not c_val)
    r = i
    if is_section:
        ws3.merge_cells(f"B{r}:C{r}")
        cell = ws3[f"B{r}"]
        cell.value = b
        cell.font = Font(name="Arial", bold=True, size=10, color=WHITE)
        cell.fill = fill(BLUE_MED)
        cell.alignment = align(h="left", v="center")
        cell.border = Border(bottom=Side(style="thin", color=BLUE_DARK))
        ws3.row_dimensions[r].height = 20
    elif b:
        bc = ws3.cell(row=r, column=2, value=b)
        bc.font = Font(name="Arial", bold=True, size=9, color=BLUE_DARK)
        bc.alignment = align(h="right", v="top")
        bc.fill = fill(BLUE_XLIGHT)
        bc.border = border_thin()

        cc = ws3.cell(row=r, column=3, value=c_val)
        cc.font = Font(name="Arial", size=9, color=TEXT_DARK)
        cc.alignment = align(h="left", v="top", wrap=True)
        cc.fill = fill(WHITE)
        cc.border = border_thin()
        ws3.row_dimensions[r].height = 28
    else:
        ws3.row_dimensions[r].height = 8

# ── Tab colours ─────────────────────────────────────────────────────────────
ws1.sheet_properties.tabColor = BLUE_MED
ws2.sheet_properties.tabColor = GREEN_MED
ws3.sheet_properties.tabColor = PURPLE_MED

# ── Print settings ───────────────────────────────────────────────────────────
for ws in [ws1, ws2]:
    ws.page_setup.orientation = "landscape"
    ws.page_setup.paperSize = 9  # A4
    ws.page_setup.fitToPage = True
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.print_title_rows = "1:6"

wb.save(OUTPUT)
print("Excel saved:", OUTPUT)

"""
Dental Milestones Tracker v2
KEY UPGRADE:
  - DOB entered in G4 (date cell)
  - "Date Observed" entered in Col F per tooth row
  - Col G: AUTO-CALCULATES child's age from DOB + Col F date
  - Col I: AUTO-ASSESSES timing vs AAPD bounds:
       Early       → age < lower_bound
       On Time     → lower_bound ≤ age ≤ upper_bound
       Delayed     → upper_bound < age ≤ upper_bound + 6 months (grace)
       ⚠ REFER    → age > upper_bound + 6 months (referral trigger)
  - Col J: "Days Since Upper Bound" — shows how many days past deadline
  - Conditional formatting: REFER rows turn bright red across entire row
"""

import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.formatting.rule import FormulaRule

OUTPUT = "/tmp/workspace/tooth-eruption/Dental_Milestones_Tracker_v2.xlsx"

# ── Palette ──────────────────────────────────────────────────────────────────
BLUE_DARK    = "1A3A6B"
BLUE_MED     = "2F6BAD"
BLUE_LIGHT   = "D6E8FA"
BLUE_XLIGHT  = "EEF5FC"
GREEN_DARK   = "1A6B3A"
GREEN_MED    = "2FAD6B"
GREEN_LIGHT  = "D6FAE8"
GREEN_XLIGHT = "EEF9F3"
PURPLE_MED   = "7B2FAD"
PURPLE_LIGHT = "EBD6FA"
AMBER        = "FFF3CD"
AMBER_DARK   = "856404"
RED_LIGHT    = "FDECEA"
RED_DARK     = "C0392B"
RED_BRIGHT   = "FF4444"
GREY_LIGHT   = "F4F6F8"
GREY_MED     = "BFC9D1"
WHITE        = "FFFFFF"
TEXT_DARK    = "1C2B3A"

def fill(c): return PatternFill("solid", fgColor=c)
def thin(): return Side(style="thin", color=GREY_MED)
def bdr(): return Border(left=thin(), right=thin(), top=thin(), bottom=thin())
def fnt(bold=False, size=9, color=TEXT_DARK, italic=False):
    return Font(name="Arial", bold=bold, size=size, color=color, italic=italic)
def aln(h="left", v="center", wrap=False):
    return Alignment(horizontal=h, vertical=v, wrap_text=wrap)

def sc(ws, row, col, value, bold=False, size=9, bg=None, fg=TEXT_DARK,
       h="left", wrap=False, italic=False, num_fmt=None, border=True):
    c = ws.cell(row=row, column=col, value=value)
    c.font  = fnt(bold=bold, size=size, color=fg, italic=italic)
    c.alignment = aln(h=h, wrap=wrap)
    if bg: c.fill = fill(bg)
    if border: c.border = bdr()
    if num_fmt: c.number_format = num_fmt
    return c

# ════════════════════════════════════════════════════════════════════════════
# Tooth data
# (tooth_name, phase, low, high, units, upper_lower)
# units = "months" or "years"
# grace = 6 months added for REFER threshold
# ════════════════════════════════════════════════════════════════════════════
teeth_data = [
    # Phase, Tooth, Low, High, Units
    ("Primary Eruption",  "Central Incisor (Lower)",     5,   10,  "months"),
    ("Primary Eruption",  "Central Incisor (Upper)",     6,   10,  "months"),
    ("Primary Eruption",  "Lateral Incisor (Lower)",     7,   10,  "months"),
    ("Primary Eruption",  "Lateral Incisor (Upper)",     8,   12,  "months"),
    ("Primary Eruption",  "First Molar (Primary)",       11,  18,  "months"),
    ("Primary Eruption",  "Canine / Cuspid (Primary)",   16,  20,  "months"),
    ("Primary Eruption",  "Second Molar (Primary)",      20,  30,  "months"),
    ("Primary Shedding",  "Central Incisor (Lower)",     6,   7,   "years"),
    ("Primary Shedding",  "Central Incisor (Upper)",     7,   8,   "years"),
    ("Primary Shedding",  "Lateral Incisor (Lower)",     7,   8,   "years"),
    ("Primary Shedding",  "Lateral Incisor (Upper)",     8,   9,   "years"),
    ("Primary Shedding",  "Canine / Cuspid (Lower)",     9,   11,  "years"),
    ("Primary Shedding",  "Canine / Cuspid (Upper)",     11,  12,  "years"),
    ("Primary Shedding",  "First Molar (Primary)",       9,   11,  "years"),
    ("Primary Shedding",  "Second Molar (Primary)",      9,   13,  "years"),
    ("Permanent Eruption","First Molar (Permanent)",     5.5, 7,   "years"),
    ("Permanent Eruption","Central Incisor (Lower)",     6,   7,   "years"),
    ("Permanent Eruption","Central Incisor (Upper)",     7,   8,   "years"),
    ("Permanent Eruption","Lateral Incisor (Lower)",     7,   8,   "years"),
    ("Permanent Eruption","Lateral Incisor (Upper)",     8,   9,   "years"),
    ("Permanent Eruption","Canine / Cuspid (Lower)",     9,   11,  "years"),
    ("Permanent Eruption","Canine / Cuspid (Upper)",     11,  12,  "years"),
    ("Permanent Eruption","First Premolar (Upper)",      10,  11,  "years"),
    ("Permanent Eruption","First Premolar (Lower)",      10,  12,  "years"),
    ("Permanent Eruption","Second Premolar (Upper)",     10,  12,  "years"),
    ("Permanent Eruption","Second Premolar (Lower)",     11,  13,  "years"),
    ("Permanent Eruption","Second Molar (Permanent)",    12,  14,  "years"),
    ("Permanent Eruption","Third Molar / Wisdom Tooth",  17,  30,  "years"),
]

phase_bg   = {"Primary Eruption": BLUE_LIGHT,  "Primary Shedding": GREEN_LIGHT,  "Permanent Eruption": PURPLE_LIGHT}
phase_hdr  = {"Primary Eruption": BLUE_MED,    "Primary Shedding": GREEN_MED,    "Permanent Eruption": PURPLE_MED}
phase_lbl  = {"Primary Eruption": "① PRIMARY (MILK) TEETH — ERUPTION",
              "Primary Shedding": "② PRIMARY TEETH — SHEDDING / FALLING OUT",
              "Permanent Eruption": "③ PERMANENT TEETH — ERUPTION"}

wb = openpyxl.Workbook()

# ════════════════════════════════════════════════════════════════════════════
# SHEET 1: PATIENT TRACKER
# ════════════════════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "Patient Tracker"
ws1.sheet_view.showGridLines = False
ws1.sheet_properties.tabColor = BLUE_MED

# ── Column layout ─────────────────────────────────────────────────────────────
# A: Tooth Name (22)
# B: Phase (15)
# C: Expected Low (12)
# D: Expected High (12)
# E: Units (8)
# F: Date Observed (14)  ← user enters
# G: Age at Observation (17) ← AUTO from DOB + F
# H: Status (15)  ← dropdown
# I: Assessment (18)  ← AUTO formula vs thresholds
# J: Days Past Deadline (16)  ← AUTO: days beyond upper+grace
# K: Caries? (12) ← dropdown
# L: Referred? (16) ← dropdown
# M: Notes (30)

col_widths = [24, 17, 12, 12, 9, 14, 17, 16, 20, 18, 13, 18, 32]
col_labels = [
    "Tooth Name",
    "Phase",
    "Expected\nAge (Low)",
    "Expected\nAge (High)",
    "Units",
    "Date\nObserved",
    "Child Age\nat Observation\n(auto)",
    "Status",
    "Assessment\n(auto)",
    "Days Past\nReferral Deadline\n(auto)",
    "Caries\nPresent?",
    "Referred?",
    "Notes / Observations",
]
for i, w in enumerate(col_widths, 1):
    ws1.column_dimensions[get_column_letter(i)].width = w

# ── BANNER ────────────────────────────────────────────────────────────────────
ws1.merge_cells("A1:M1")
c = ws1["A1"]
c.value = "CHILD DENTAL MILESTONES TRACKER  —  Smart Age Assessment"
c.font = Font(name="Arial", bold=True, size=17, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center", v="center")
ws1.row_dimensions[1].height = 34

ws1.merge_cells("A2:M2")
c = ws1["A2"]
c.value = (
    "Enter DOB once in G4. Enter Date Observed per row. "
    "Assessment (Col I) and Days Past Deadline (Col J) calculate automatically."
)
c.font = Font(name="Arial", size=8.5, color="BDD7F5", italic=True)
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center", v="center")
ws1.row_dimensions[2].height = 16

# ── Patient info bar ──────────────────────────────────────────────────────────
info_bg = BLUE_XLIGHT
ws1.row_dimensions[3].height = 7
for col in range(1, 14):
    ws1.cell(row=3, column=col).fill = fill(info_bg)

ws1.row_dimensions[4].height = 24

info_fields = [
    (1, "A4", "Patient Name:"),
    (3, "D4", ""),     # name value spans C4:D4
    (4, "E4", "DOB (Child):"),
    (6, "G4", None),   # DOB value — date input cell, highlighted
    (8, "H4", "Sex:"),
    (9, "I4", ""),
    (10, "J4", "Clinician:"),
    (12, "L4", ""),
]

# Labels
for col, cell_addr, label in [
    ("A", "A4", "Patient Name:"),
    ("C", "C4", ""),
    ("E", "E4", "DOB (Child):"),
    ("H", "H4", "Sex:"),
    ("J", "J4", "Clinician:"),
]:
    c = ws1[cell_addr]
    c.value = label
    c.font = Font(name="Arial", bold=True, size=9, color=BLUE_DARK)
    c.fill = fill(info_bg)
    c.alignment = aln(h="right", v="center")

# Value cells
for cell_addr, is_dob in [("B4", False), ("C4", False), ("F4", False), ("I4", False), ("K4", False)]:
    c = ws1[cell_addr]
    c.fill = fill(WHITE)
    c.border = Border(bottom=Side(style="thin", color=BLUE_MED))
    c.font = Font(name="Arial", size=9)

# DOB input cell G4 — special highlight
dob_cell = ws1["G4"]
dob_cell.fill = fill("FFFACD")   # light yellow
dob_cell.border = Border(
    left=Side(style="medium", color=AMBER_DARK),
    right=Side(style="medium", color=AMBER_DARK),
    top=Side(style="medium", color=AMBER_DARK),
    bottom=Side(style="medium", color=AMBER_DARK),
)
dob_cell.font = Font(name="Arial", bold=True, size=10, color=AMBER_DARK)
dob_cell.alignment = aln(h="center", v="center")
dob_cell.number_format = "DD-MMM-YYYY"

# DOB label arrow
ws1["F4"].value = "► DOB ►"
ws1["F4"].font = Font(name="Arial", bold=True, size=9, color=AMBER_DARK)
ws1["F4"].fill = fill(AMBER)
ws1["F4"].alignment = aln(h="center", v="center")

# Name value
ws1.merge_cells("B4:D4")
ws1["B4"].fill = fill(WHITE)
ws1["B4"].border = Border(bottom=Side(style="thin", color=BLUE_MED))

# Sex value
ws1["I4"].value = ""
dv_sex = DataValidation(type="list", formula1='"Male,Female,Other"', allow_blank=True, showDropDown=False)
ws1.add_data_validation(dv_sex)
dv_sex.add(ws1["I4"])

ws1.merge_cells("K4:M4")
ws1["K4"].fill = fill(WHITE)
ws1["K4"].border = Border(bottom=Side(style="thin", color=BLUE_MED))

ws1.row_dimensions[5].height = 7
for col in range(1, 14):
    ws1.cell(row=5, column=col).fill = fill(info_bg)

# ── Column headers row 6 ──────────────────────────────────────────────────────
ws1.row_dimensions[6].height = 40
ws1.freeze_panes = "A7"

for i, lbl in enumerate(col_labels, 1):
    c = ws1.cell(row=6, column=i, value=lbl)
    c.font = Font(name="Arial", bold=True, size=8.5, color=WHITE)
    c.fill = fill(BLUE_MED)
    c.alignment = aln(h="center", v="center", wrap=True)
    c.border = bdr()

# Mark auto-columns with a tint in header
for col in [7, 9, 10]:  # Age (auto), Assessment (auto), Days (auto)
    ws1.cell(row=6, column=col).fill = fill("1A5490")
    ws1.cell(row=6, column=col).font = Font(
        name="Arial", bold=True, size=8.5, color="C8E6FF")

# ── Data rows ─────────────────────────────────────────────────────────────────
# DOB is in G4 (absolute ref: $G$4)
# F_col = 6 (Date Observed), G_col = 7 (Age auto), I_col = 9 (Assessment), J_col = 10 (Days past)
# Units col = 5

DATA_START = 7
dv_status   = DataValidation(type="list", formula1='"Not yet observed,Erupted / Shed,Partial eruption,Absent,Impacted"', allow_blank=True, showDropDown=False)
dv_caries   = DataValidation(type="list", formula1='"No,Yes - early (white spot),Yes - cavitated,Suspected"', allow_blank=True, showDropDown=False)
dv_referred = DataValidation(type="list", formula1='"No,Yes - Dentist,Yes - Orthodontist,Yes - Oral surgeon,Yes - Paediatric dentist"', allow_blank=True, showDropDown=False)
ws1.add_data_validation(dv_status)
ws1.add_data_validation(dv_caries)
ws1.add_data_validation(dv_referred)

current_phase = None
row = DATA_START

# We'll collect row numbers for conditional formatting
refer_rows = []   # collect formula-based, handle via CF instead
all_data_rows = []

for phase, tooth, low, high, units in teeth_data:
    # Phase section header
    if phase != current_phase:
        current_phase = phase
        ws1.merge_cells(f"A{row}:M{row}")
        c = ws1[f"A{row}"]
        c.value = f"  {phase_lbl[phase]}"
        c.font = Font(name="Arial", bold=True, size=9.5, color=WHITE)
        c.fill = fill(phase_hdr[phase])
        c.alignment = aln(h="left", v="center")
        ws1.row_dimensions[row].height = 18
        row += 1

    bg = phase_bg[phase]

    # Col A: Tooth name
    sc(ws1, row, 1, tooth, bold=True, bg=bg)
    # Col B: Phase
    sc(ws1, row, 2, phase, bg=bg, fg="555555", italic=True)
    # Col C: Low bound
    sc(ws1, row, 3, low, bg=bg, h="center")
    # Col D: High bound
    sc(ws1, row, 4, high, bg=bg, h="center")
    # Col E: Units
    sc(ws1, row, 5, units, bg=bg, h="center", italic=True, fg="666666")

    # Col F: Date Observed — user input, white
    f_cell = ws1.cell(row=row, column=6)
    f_cell.number_format = "DD-MMM-YYYY"
    f_cell.fill = fill(WHITE)
    f_cell.border = Border(
        left=thin(), right=thin(), top=thin(), bottom=thin()
    )
    f_cell.font = fnt(size=9)
    f_cell.alignment = aln(h="center")

    # Col G: Age at Observation — AUTO FORMULA
    # If units = months: age = (F - DOB) / 30.4375
    # If units = years:  age = (F - DOB) / 365.25
    # Only calculate if both F and DOB are present
    f_ref = f"F{row}"
    dob_ref = "$G$4"
    if units == "months":
        age_formula = (
            f'=IF(AND(ISNUMBER({f_ref}),ISNUMBER({dob_ref})),'
            f'ROUND(({f_ref}-{dob_ref})/30.4375,1),"")'
        )
        age_fmt = '0.0" mo"'
    else:
        age_formula = (
            f'=IF(AND(ISNUMBER({f_ref}),ISNUMBER({dob_ref})),'
            f'ROUND(({f_ref}-{dob_ref})/365.25,2),"")'
        )
        age_fmt = '0.00" yrs"'

    g_cell = ws1.cell(row=row, column=7, value=age_formula)
    g_cell.number_format = age_fmt
    g_cell.fill = fill("E8F4FF")
    g_cell.border = bdr()
    g_cell.font = Font(name="Arial", size=9, color="1A3A6B", italic=True)
    g_cell.alignment = aln(h="center")

    # Col H: Status dropdown
    h_cell = ws1.cell(row=row, column=8)
    h_cell.fill = fill(WHITE)
    h_cell.border = bdr()
    h_cell.font = fnt(size=9)
    dv_status.add(h_cell)

    # Col I: Assessment — AUTO FORMULA
    # Logic (age in G_row, bounds in C/D, units in E):
    # If age blank → ""
    # If units=months: grace=6 months; if years: grace=0.5 years
    # Early: age < low
    # On Time: low <= age <= high
    # Delayed: high < age <= high+grace
    # ⚠ REFER: age > high+grace
    g_ref = f"G{row}"
    c_ref = f"C{row}"   # low
    d_ref = f"D{row}"   # high
    e_ref = f"E{row}"   # units — "months" or "years"
    # Grace: 6 months → 6 if units=months, 0.5 if units=years
    if units == "months":
        grace = 6
    else:
        grace = 0.5

    assess_formula = (
        f'=IF({g_ref}="","—",'
        f'IF({g_ref}<{c_ref},"Early",'
        f'IF({g_ref}<={d_ref},"On Time",'
        f'IF({g_ref}<={d_ref}+{grace},"Delayed",'
        f'"⚠ REFER"))))'
    )
    i_cell = ws1.cell(row=row, column=9, value=assess_formula)
    i_cell.fill = fill("E8F4FF")
    i_cell.border = bdr()
    i_cell.font = Font(name="Arial", size=9, bold=True, color="1A3A6B")
    i_cell.alignment = aln(h="center")

    # Col J: Days Past Referral Deadline — AUTO
    # Deadline = DOB + (high+grace) converted to days
    # Only show if age > high+grace, else ""
    if units == "months":
        # convert high+grace months → days: (high+grace)*30.4375
        deadline_days = f"({d_ref}+{grace})*30.4375"
    else:
        deadline_days = f"({d_ref}+{grace})*365.25"

    days_formula = (
        f'=IF({g_ref}="","—",'
        f'IF({i_cell.coordinate}="⚠ REFER",'
        f'ROUND(({f_ref}-({dob_ref}+{deadline_days})),0),"—"))'
    )
    j_cell = ws1.cell(row=row, column=10, value=days_formula)
    j_cell.fill = fill("E8F4FF")
    j_cell.border = bdr()
    j_cell.font = Font(name="Arial", size=9, color="1A3A6B")
    j_cell.alignment = aln(h="center")

    # Col K: Caries
    k_cell = ws1.cell(row=row, column=11)
    k_cell.fill = fill(WHITE)
    k_cell.border = bdr()
    k_cell.font = fnt(size=9)
    dv_caries.add(k_cell)

    # Col L: Referred
    l_cell = ws1.cell(row=row, column=12)
    l_cell.fill = fill(WHITE)
    l_cell.border = bdr()
    l_cell.font = fnt(size=9)
    dv_referred.add(l_cell)

    # Col M: Notes
    m_cell = ws1.cell(row=row, column=13)
    m_cell.fill = fill(WHITE)
    m_cell.border = bdr()
    m_cell.font = fnt(size=9)
    m_cell.alignment = aln(wrap=True)

    ws1.row_dimensions[row].height = 18
    all_data_rows.append(row)
    row += 1

last_row = row - 1

# ── Conditional Formatting ─────────────────────────────────────────────────────
# Applied to ENTIRE row range A:M based on Col I value

# ⚠ REFER → full row bright red
refer_rule = FormulaRule(
    formula=[f'=$I7="⚠ REFER"'],
    fill=PatternFill("solid", fgColor="FF9999"),
    font=Font(name="Arial", size=9, color=RED_DARK, bold=True)
)

# Delayed → full row amber
delayed_rule = FormulaRule(
    formula=[f'=$I7="Delayed"'],
    fill=PatternFill("solid", fgColor=AMBER),
    font=Font(name="Arial", size=9, color=AMBER_DARK)
)

# On Time → green tint
ontime_rule = FormulaRule(
    formula=[f'=$I7="On Time"'],
    fill=PatternFill("solid", fgColor="C8F7C5"),
    font=Font(name="Arial", size=9, color=GREEN_DARK)
)

# Early → light blue tint
early_rule = FormulaRule(
    formula=[f'=$I7="Early"'],
    fill=PatternFill("solid", fgColor="CCE5FF"),
    font=Font(name="Arial", size=9, color=BLUE_MED)
)

# Status = Absent/Impacted → col H red
absent_rule = FormulaRule(
    formula=[f'=OR($H7="Absent",$H7="Impacted")'],
    fill=PatternFill("solid", fgColor=RED_LIGHT),
    font=Font(name="Arial", size=9, color=RED_DARK, bold=True)
)
erupted_rule = FormulaRule(
    formula=[f'=$H7="Erupted / Shed"'],
    fill=PatternFill("solid", fgColor="C8F7C5"),
    font=Font(name="Arial", size=9, color=GREEN_DARK, bold=True)
)

full_range   = f"A7:M{last_row}"
assess_range = f"I7:I{last_row}"
status_range = f"H7:H{last_row}"

# Priority: REFER > Delayed > On Time > Early
ws1.conditional_formatting.add(full_range, refer_rule)
ws1.conditional_formatting.add(full_range, delayed_rule)
ws1.conditional_formatting.add(full_range, ontime_rule)
ws1.conditional_formatting.add(full_range, early_rule)
ws1.conditional_formatting.add(status_range, absent_rule)
ws1.conditional_formatting.add(status_range, erupted_rule)

# ── Legend ─────────────────────────────────────────────────────────────────────
legend_row = last_row + 2
ws1.merge_cells(f"A{legend_row}:M{legend_row}")
c = ws1[f"A{legend_row}"]
c.value = "COLOUR LEGEND — Assessment (Col I)"
c.font = Font(name="Arial", bold=True, size=9, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center")
ws1.row_dimensions[legend_row].height = 18
legend_row += 1

legend_items = [
    ("Early",    "CCE5FF", BLUE_MED,   "Tooth observed before the lower bound of expected age range"),
    ("On Time",  "C8F7C5", GREEN_DARK, "Tooth observed within the expected AAPD age range — normal"),
    ("Delayed",  AMBER,    AMBER_DARK, "Tooth observed within 6-month grace period beyond upper bound — monitor closely"),
    ("⚠ REFER",  "FF9999", RED_DARK,   "Tooth not observed >6 months beyond upper bound — refer to dentist / specialist"),
    ("—",        GREY_LIGHT, "555555", "Date not yet entered — waiting for observation"),
]
for bg, fg, label_fg, desc in legend_items:
    c_badge = ws1.cell(row=legend_row, column=1, value=label_fg)
    c_badge.value = f"  {label_fg}"  # fix: use the actual label not label_fg
    ws1.merge_cells(f"A{legend_row}:B{legend_row}")
    c = ws1[f"A{legend_row}"]
    c.value = f"  {'Early' if bg=='CCE5FF' else 'On Time' if bg=='C8F7C5' else 'Delayed' if bg==AMBER else '⚠ REFER' if bg=='FF9999' else '—'}"
    c.font = Font(name="Arial", bold=True, size=9, color=fg)
    c.fill = fill(bg)
    c.border = bdr()
    c.alignment = aln(h="center")

    ws1.merge_cells(f"C{legend_row}:M{legend_row}")
    d = ws1[f"C{legend_row}"]
    d.value = desc
    d.font = Font(name="Arial", size=9, color=TEXT_DARK)
    d.fill = fill(bg)
    d.border = bdr()
    d.alignment = aln(h="left")
    ws1.row_dimensions[legend_row].height = 16
    legend_row += 1

# Disclaimer
legend_row += 1
ws1.merge_cells(f"A{legend_row}:M{legend_row}")
c = ws1[f"A{legend_row}"]
c.value = (
    "⚠  Ages are approximate and vary by sex, ethnicity, and nutrition. "
    "Grace period = 6 months. 'Delayed' = needs monitoring. '⚠ REFER' = dental referral indicated. "
    "This tracker is a clinical aide — always apply clinical judgement. "
    "Source: AAPD Reference Manual of Pediatric Dentistry (Logan-Kronfeld chart, adapted)."
)
c.font = Font(name="Arial", size=8, italic=True, color=AMBER_DARK)
c.fill = fill(AMBER)
c.alignment = aln(h="left", v="center", wrap=True)
c.border = Border(
    left=Side(style="thin", color=AMBER_DARK), right=Side(style="thin", color=AMBER_DARK),
    top=Side(style="thin", color=AMBER_DARK), bottom=Side(style="thin", color=AMBER_DARK),
)
ws1.row_dimensions[legend_row].height = 28

# ── Print settings ──────────────────────────────────────────────────────────
ws1.page_setup.orientation = "landscape"
ws1.page_setup.paperSize = 9
ws1.page_setup.fitToPage = True
ws1.page_setup.fitToWidth = 1
ws1.page_setup.fitToHeight = 0
ws1.print_title_rows = "1:6"

# ════════════════════════════════════════════════════════════════════════════
# SHEET 2: REFERENCE CHART (same as v1, condensed)
# ════════════════════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("Reference Chart")
ws2.sheet_view.showGridLines = False
ws2.sheet_properties.tabColor = GREEN_MED

ref_col_w = [27, 14, 14, 16, 7, 16, 16, 22]
for i, w in enumerate(ref_col_w, 1):
    ws2.column_dimensions[get_column_letter(i)].width = w

ws2.merge_cells("A1:H1")
c = ws2["A1"]
c.value = "AAPD TOOTH ERUPTION REFERENCE — CORRECTED DATA"
c.font = Font(name="Arial", bold=True, size=14, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center", v="center")
ws2.row_dimensions[1].height = 28
ws2.merge_cells("A2:H2")
c = ws2["A2"]
c.value = "Source: AAPD Reference Manual of Pediatric Dentistry (Logan-Kronfeld). Grace period for referral = 6 months past upper bound."
c.font = Font(name="Arial", size=8.5, italic=True, color="BDD7F5")
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center")
ws2.row_dimensions[2].height = 16

def write_ref_section(ws, start_row, title, bg_hdr, bg_alt, col_headers, data_rows):
    ws.merge_cells(f"A{start_row}:H{start_row}")
    c = ws[f"A{start_row}"]
    c.value = title
    c.font = Font(name="Arial", bold=True, size=10, color=WHITE)
    c.fill = fill(bg_hdr)
    c.alignment = aln(h="left", v="center")
    ws.row_dimensions[start_row].height = 20
    r = start_row + 1
    for ci, h in enumerate(col_headers, 1):
        c = ws.cell(row=r, column=ci, value=h)
        c.font = Font(name="Arial", bold=True, size=8.5, color=WHITE)
        c.fill = fill(bg_hdr)
        c.alignment = aln(h="center", v="center", wrap=True)
        c.border = bdr()
    ws.row_dimensions[r].height = 28
    r += 1
    for i, row_data in enumerate(data_rows):
        bg = bg_alt if i % 2 == 0 else WHITE
        for ci, val in enumerate(row_data, 1):
            c = ws.cell(row=r, column=ci, value=val)
            c.font = Font(name="Arial", size=8.5, color=TEXT_DARK, bold=(ci == 1))
            c.fill = fill(bg)
            c.alignment = aln(h="center" if ci > 1 else "left", v="center")
            c.border = bdr()
        ws.row_dimensions[r].height = 16
        r += 1
    return r + 1

hdr1 = ["Tooth", "Upper\n(months)", "Lower\n(months)", "Combined\nRange", "Units", "Referral if no eruption by:", "", "Notes"]
d1 = [
    ["Central incisors",  "6–10", "5–8",  "5–10 months", "mo", "16 months (10+6 grace)", "", "Lower first"],
    ["Lateral incisors",  "8–12", "7–10", "7–12 months", "mo", "18 months (12+6 grace)", "", ""],
    ["First molars",      "11–18","11–18","11–18 months","mo", "24 months (18+6 grace)", "", ""],
    ["Canines",           "16–20","16–20","16–20 months","mo", "26 months (20+6 grace)", "", ""],
    ["Second molars",     "20–30","20–30","20–30 months","mo", "36 months (30+6 grace)", "", "Last primary"],
]
nr = write_ref_section(ws2, 4, "① PRIMARY TEETH — ERUPTION (Grace: +6 months)", BLUE_MED, BLUE_LIGHT, hdr1, d1)

hdr2 = ["Tooth", "Upper (yrs)", "Lower (yrs)", "Combined", "Units", "Referral if not shed by:", "", "Notes"]
d2 = [
    ["Central incisors (lower)", "7–8",  "6–7",  "6–8 yrs",  "yrs", "8.5 yrs (8+0.5 grace)",  "", ""],
    ["Central incisors (upper)", "8–9",  "7–8",  "7–9 yrs",  "yrs", "9.5 yrs (9+0.5 grace)",  "", ""],
    ["Lateral incisors (lower)", "7–8",  "7–8",  "7–8 yrs",  "yrs", "8.5 yrs",  "", ""],
    ["Lateral incisors (upper)", "8–9",  "8–9",  "8–9 yrs",  "yrs", "9.5 yrs",  "", ""],
    ["Canines (lower)",          "9–11", "9–11", "9–11 yrs", "yrs", "11.5 yrs", "", ""],
    ["Canines (upper)",          "11–12","11–12","11–12 yrs","yrs", "12.5 yrs", "", ""],
    ["First molars",             "9–11", "9–11", "9–11 yrs", "yrs", "11.5 yrs", "", ""],
    ["Second molars",            "9–13", "9–13", "9–13 yrs", "yrs", "13.5 yrs", "", "Last primary"],
]
nr = write_ref_section(ws2, nr, "② PRIMARY TEETH — SHEDDING (Grace: +6 months)", GREEN_MED, GREEN_LIGHT, hdr2, d2)

hdr3 = ["Tooth", "Upper (yrs)", "Lower (yrs)", "Combined", "Units", "Referral if no eruption by:", "", "Notes"]
d3 = [
    ["First molars",          "5.5–7","5.5–7","5.5–7 yrs","yrs","7.5 yrs", "", "First permanent"],
    ["Central incisors (L)",  "6–7",  "6–7",  "6–7 yrs",  "yrs","7.5 yrs", "", ""],
    ["Central incisors (U)",  "7–8",  "7–8",  "7–8 yrs",  "yrs","8.5 yrs", "", ""],
    ["Lateral incisors (L)",  "7–8",  "7–8",  "7–8 yrs",  "yrs","8.5 yrs", "", ""],
    ["Lateral incisors (U)",  "8–9",  "8–9",  "8–9 yrs",  "yrs","9.5 yrs", "", ""],
    ["Canines (lower)",       "9–11", "9–11", "9–11 yrs", "yrs","11.5 yrs","", ""],
    ["Canines (upper)",       "11–12","11–12","11–12 yrs","yrs","12.5 yrs","", ""],
    ["First premolars (U)",   "10–11","10–11","10–11 yrs","yrs","11.5 yrs","", ""],
    ["First premolars (L)",   "10–12","10–12","10–12 yrs","yrs","12.5 yrs","", ""],
    ["Second premolars (U)",  "10–12","10–12","10–12 yrs","yrs","12.5 yrs","", ""],
    ["Second premolars (L)",  "11–13","11–13","11–13 yrs","yrs","13.5 yrs","", ""],
    ["Second molars",         "12–14","12–14","12–14 yrs","yrs","14.5 yrs","", "'12-yr molars'"],
    ["Third molars (wisdom)", "17–30","17–30","17–30 yrs","yrs","30.5 yrs","", "May not erupt"],
]
nr = write_ref_section(ws2, nr, "③ PERMANENT TEETH — ERUPTION (Grace: +6 months)", PURPLE_MED, PURPLE_LIGHT, hdr3, d3)

# ════════════════════════════════════════════════════════════════════════════
# SHEET 3: HOW TO USE
# ════════════════════════════════════════════════════════════════════════════
ws3 = wb.create_sheet("How to Use")
ws3.sheet_view.showGridLines = False
ws3.sheet_properties.tabColor = PURPLE_MED
ws3.column_dimensions["A"].width = 3
ws3.column_dimensions["B"].width = 30
ws3.column_dimensions["C"].width = 62

ws3.merge_cells("A1:C1")
c = ws3["A1"]
c.value = "HOW TO USE — SMART DENTAL TRACKER v2"
c.font = Font(name="Arial", bold=True, size=14, color=WHITE)
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center", v="center")
ws3.row_dimensions[1].height = 30

steps = [
    ("SECTION", "QUICK START — 3 STEPS", ""),
    ("STEP", "Step 1 — Enter DOB",       "Go to Patient Tracker. Click cell G4 (yellow, marked ► DOB ►). Enter the child's date of birth in DD-MMM-YYYY format (e.g. 15-Jan-2022)."),
    ("STEP", "Step 2 — Enter Visit Date","In Col F for any tooth row, enter the date you observed (or checked for) that tooth. The child's age in Col G calculates instantly."),
    ("STEP", "Step 3 — Read Assessment", "Col I shows Early / On Time / Delayed / ⚠ REFER automatically. Col J shows days past the referral deadline if ⚠ REFER triggers."),
    ("BLANK", "", ""),
    ("SECTION", "COLUMN GUIDE", ""),
    ("STEP", "Col A — Tooth Name",       "Pre-filled. All 28 teeth across 3 phases."),
    ("STEP", "Col C/D — Expected Range", "AAPD lower and upper age bounds (months or years)."),
    ("STEP", "Col F — Date Observed",    "Enter date you examined or noted this tooth. Leave blank if not yet checked."),
    ("STEP", "Col G — Age (auto)",       "Auto-calculated from DOB (G4) and Date Observed (F). Shows months or years depending on phase."),
    ("STEP", "Col H — Status",           "Your clinical observation: select from dropdown (Erupted/Partial/Absent/Impacted)."),
    ("STEP", "Col I — Assessment (auto)","Formula-driven. Early / On Time / Delayed / ⚠ REFER. Changes colour automatically."),
    ("STEP", "Col J — Days Past Deadline (auto)", "Shows number of days beyond the referral threshold (upper bound + 6 months). Only visible when ⚠ REFER triggers."),
    ("STEP", "Col K — Caries?",          "Record caries findings at the same visit."),
    ("STEP", "Col L — Referred?",        "Log any referrals made."),
    ("BLANK", "", ""),
    ("SECTION", "REFERRAL LOGIC", ""),
    ("STEP", "Grace Period",             "6 months (or 0.5 years) is added beyond the AAPD upper bound before triggering ⚠ REFER. This accounts for normal variation."),
    ("STEP", "Delayed vs ⚠ REFER",       "Delayed = within the 6-month grace window — monitor. ⚠ REFER = beyond grace — refer to dentist/specialist."),
    ("STEP", "Absent / Impacted",        "If you select Absent or Impacted in Status (Col H), that cell turns red regardless of age. Always refer."),
    ("BLANK", "", ""),
    ("SECTION", "COLOUR CODING", ""),
    ("STEP", "Green row",               "On Time — tooth erupted/shed within expected range."),
    ("STEP", "Light blue row",          "Early — tooth appeared earlier than expected."),
    ("STEP", "Amber row",               "Delayed — within 6-month grace period. Monitor."),
    ("STEP", "Red row",                 "⚠ REFER — past grace period. Refer to dentist."),
    ("BLANK", "", ""),
    ("SECTION", "DATA SOURCE", ""),
    ("STEP", "Primary",                 "AAPD Reference Manual of Pediatric Dentistry — Logan-Kronfeld chart (adapted)."),
    ("STEP", "Supporting",              "Color Atlas of Human Anatomy Vol.2 (Thieme); Pfenninger & Fowler Procedures for Primary Care (3rd ed)."),
    ("STEP", "Disclaimer",              "Ages are approximate. This is a clinical aide, not a diagnostic tool. Apply clinical judgement always."),
]

r = 2
for item_type, label, desc in steps:
    if item_type == "BLANK":
        ws3.row_dimensions[r].height = 7
        r += 1
        continue
    if item_type == "SECTION":
        ws3.merge_cells(f"B{r}:C{r}")
        c = ws3[f"B{r}"]
        c.value = label
        c.font = Font(name="Arial", bold=True, size=10, color=WHITE)
        c.fill = fill(BLUE_MED)
        c.alignment = aln(h="left", v="center")
        c.border = Border(bottom=Side(style="thin", color=BLUE_DARK))
        ws3.row_dimensions[r].height = 20
    else:
        b = ws3.cell(row=r, column=2, value=label)
        b.font = Font(name="Arial", bold=True, size=9, color=BLUE_DARK)
        b.fill = fill(BLUE_XLIGHT)
        b.alignment = aln(h="right", v="top")
        b.border = bdr()
        c = ws3.cell(row=r, column=3, value=desc)
        c.font = Font(name="Arial", size=9, color=TEXT_DARK)
        c.fill = fill(WHITE)
        c.alignment = aln(h="left", v="top", wrap=True)
        c.border = bdr()
        ws3.row_dimensions[r].height = 30
    r += 1

wb.save(OUTPUT)
print("Saved:", OUTPUT)

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/make_excel_v2.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/make_excel_v2.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/make_excel_v2.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/make_excel_v2.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/make_excel_v2.py'

"""
Add a Summary Dashboard sheet to the existing Dental_Milestones_Tracker_v2.xlsx
Data source: 'Patient Tracker' sheet
  - All data rows: 8–37  (col I = Assessment, col A = Tooth, col B = Phase,
                           col F = Date Observed, col H = Status, col J = Days Past)
  - Phase ranges:
      Primary Eruption:  rows 8–14
      Primary Shedding:  rows 16–23
      Permanent Eruption: rows 25–37
  - Key columns (1-based):
      A=1 Tooth, B=2 Phase, F=6 Date Observed, G=7 Age, H=8 Status,
      I=9 Assessment, J=10 Days Past Deadline
"""

import openpyxl
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import FormulaRule

INPUT  = "/tmp/workspace/tooth-eruption/Dental_Milestones_Tracker_v2.xlsx"
OUTPUT = "/tmp/workspace/tooth-eruption/Dental_Milestones_Tracker_v3.xlsx"

wb = openpyxl.load_workbook(INPUT)

# ── Remove old dashboard if re-running ───────────────────────────────────────
if "Dashboard" in wb.sheetnames:
    del wb["Dashboard"]

# Insert Dashboard as first sheet
ws = wb.create_sheet("Dashboard", 0)
ws.sheet_view.showGridLines = False
ws.sheet_properties.tabColor = "E63946"   # accent red — stands out

# ── Helpers ──────────────────────────────────────────────────────────────────
BLUE_DARK    = "FF1A3A6B"
BLUE_MED     = "FF2F6BAD"
BLUE_LIGHT   = "FFD6E8FA"
BLUE_XLIGHT  = "FFEEF5FC"
GREEN_DARK   = "FF1A6B3A"
GREEN_MED    = "FF2FAD6B"
GREEN_LIGHT  = "FFD6FAE8"
PURPLE_MED   = "FF7B2FAD"
PURPLE_LIGHT = "FFEAD6FA"
AMBER        = "FFFFF3CD"
AMBER_DARK   = "FF856404"
RED_DARK     = "FFC0392B"
RED_LIGHT    = "FFFDECEA"
RED_BRIGHT   = "FFFF9999"
ORANGE       = "FFFD7E14"
TEAL         = "FF0D9488"
TEAL_LIGHT   = "FFD0F5F0"
GREY_LIGHT   = "FFF4F6F8"
GREY_MED     = "FFBFC9D1"
WHITE        = "FFFFFFFF"
TEXT_DARK    = "FF1C2B3A"

def fill(c):
    if not c.startswith("FF") or len(c) != 8:
        c = "FF" + c.lstrip("FF")[:6]
    return PatternFill("solid", fgColor=c)

def fnt(bold=False, size=9, color="FF1C2B3A", italic=False):
    return Font(name="Arial", bold=bold, size=size, color=color, italic=italic)

def aln(h="left", v="center", wrap=False):
    return Alignment(horizontal=h, vertical=v, wrap_text=wrap)

def thin_side(): return Side(style="thin", color="FFBFC9D1")
def bdr(): return Border(left=thin_side(), right=thin_side(),
                         top=thin_side(), bottom=thin_side())
def med_side(c="FF1A3A6B"): return Side(style="medium", color=c)

PT = "'Patient Tracker'"  # sheet reference prefix

# ── Column widths ─────────────────────────────────────────────────────────────
col_w = {
    "A": 3, "B": 22, "C": 18, "D": 18, "E": 18, "F": 18,
    "G": 18, "H": 18, "I": 4, "J": 22, "K": 18, "L": 18, "M": 4,
    "N": 22, "O": 18, "P": 18, "Q": 4,
}
for col, w in col_w.items():
    ws.column_dimensions[col].width = w

def rh(r, h): ws.row_dimensions[r].height = h

# ════════════════════════════════════════════════════════════════════════════
# ROW 1-2: BANNER
# ════════════════════════════════════════════════════════════════════════════
ws.merge_cells("A1:Q1")
c = ws["A1"]
c.value = "CHILD DENTAL MILESTONES — SUMMARY DASHBOARD"
c.font = Font(name="Arial", bold=True, size=18, color="FFFFFFFF")
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="center", v="center")
rh(1, 38)

ws.merge_cells("A2:Q2")
c = ws["A2"]
c.value = (
    f"Patient: {PT}!B4    ·    DOB: {PT}!G4    ·    "
    "All metrics update automatically as you fill in the Patient Tracker"
)
# Use formula to pull patient name & DOB dynamically
ws["A2"].value = None
ws.merge_cells("A2:Q2")
c2 = ws["A2"]
c2.value = "All metrics below update automatically as you fill in the Patient Tracker sheet"
c2.font = Font(name="Arial", size=9, color="FFBDD7F5", italic=True)
c2.fill = fill(BLUE_DARK)
c2.alignment = aln(h="center")
rh(2, 16)

# ════════════════════════════════════════════════════════════════════════════
# ROW 3-4: PATIENT INFO PULL
# ════════════════════════════════════════════════════════════════════════════
rh(3, 8)
for col in range(1, 18):
    ws.cell(row=3, column=col).fill = fill(BLUE_XLIGHT)

rh(4, 24)
info_items = [
    ("B4", "Patient Name:", "C4", f"='Patient Tracker'!B4"),
    ("E4", "Date of Birth:", "F4", f"='Patient Tracker'!G4"),
    ("H4", "Sex:", "I4", None),  # skip — will handle manually
    ("K4", "Clinician:", "L4", f"='Patient Tracker'!K4"),
]
# Label cells
for lbl_addr, lbl_val, val_addr, formula in info_items:
    lc = ws[lbl_addr]
    lc.value = lbl_val
    lc.font = Font(name="Arial", bold=True, size=9, color="FF1A3A6B")
    lc.fill = fill(BLUE_XLIGHT)
    lc.alignment = aln(h="right", v="center")

    if formula:
        vc = ws[val_addr]
        vc.value = formula
        vc.font = Font(name="Arial", bold=True, size=9, color=TEXT_DARK)
        vc.fill = fill(WHITE)
        vc.border = Border(bottom=Side(style="thin", color="FF2F6BAD"))
        vc.alignment = aln(h="left", v="center")
        if "G4" in formula:
            vc.number_format = "dd-mm-yyyy"

# Sex
ws["H4"].value = "Sex:"
ws["H4"].font = Font(name="Arial", bold=True, size=9, color="FF1A3A6B")
ws["H4"].fill = fill(BLUE_XLIGHT)
ws["H4"].alignment = aln(h="right", v="center")
ws["I4"].value = "='Patient Tracker'!I4"
ws["I4"].font = Font(name="Arial", bold=True, size=9, color=TEXT_DARK)
ws["I4"].fill = fill(WHITE)
ws["I4"].border = Border(bottom=Side(style="thin", color="FF2F6BAD"))

rh(5, 8)
for col in range(1, 18):
    ws.cell(row=5, column=col).fill = fill(BLUE_XLIGHT)

# ════════════════════════════════════════════════════════════════════════════
# Helper: draw a KPI card
# ════════════════════════════════════════════════════════════════════════════
def kpi_card(ws, title_row, val_row, sub_row,
             col_start, col_end,
             title, formula, subtitle,
             bg_top, bg_val, fg_val, fg_title="FFFFFFFF"):
    # Title bar
    ws.merge_cells(
        start_row=title_row, start_column=col_start,
        end_row=title_row,   end_column=col_end
    )
    tc = ws.cell(row=title_row, column=col_start, value=title)
    tc.font = Font(name="Arial", bold=True, size=9, color=fg_title)
    tc.fill = fill(bg_top)
    tc.alignment = aln(h="center", v="center")
    tc.border = bdr()

    # Value
    ws.merge_cells(
        start_row=val_row, start_column=col_start,
        end_row=val_row,   end_column=col_end
    )
    vc = ws.cell(row=val_row, column=col_start, value=formula)
    vc.font = Font(name="Arial", bold=True, size=26, color=fg_val)
    vc.fill = fill(bg_val)
    vc.alignment = aln(h="center", v="center")
    vc.border = bdr()

    # Subtitle
    ws.merge_cells(
        start_row=sub_row, start_column=col_start,
        end_row=sub_row,   end_column=col_end
    )
    sc = ws.cell(row=sub_row, column=col_start, value=subtitle)
    sc.font = Font(name="Arial", size=8, color="FF555555", italic=True)
    sc.fill = fill(bg_val)
    sc.alignment = aln(h="center", v="center", wrap=True)
    sc.border = bdr()

# ════════════════════════════════════════════════════════════════════════════
# ROWS 6-10: KPI CARDS  (5 cards across)
# Each card = cols B:C, D:E, F:G, J:K, L:M  (with spacer col I, M)
# Layout: [spacer A] [card1 B:C] [gap D] [card2 E:F] [gap G] [card3 H:I]
#         [gap J] [card4 K:L] [gap M] [card5 N:O] [spacer P:Q]
# ════════════════════════════════════════════════════════════════════════════
rh(6, 6)  # top spacer
rh(7, 22) # title
rh(8, 40) # big number
rh(9, 20) # subtitle
rh(10, 6) # bottom spacer

# I_range for assessment col = I8:I37 on Patient Tracker
I_ALL   = "'Patient Tracker'!I8:I37"
H_ALL   = "'Patient Tracker'!H8:H37"
F_ALL   = "'Patient Tracker'!F8:F37"
J_ALL   = "'Patient Tracker'!J8:J37"

# Card 1 — Total Assessed (have a date entered)
kpi_card(ws,
    title_row=7, val_row=8, sub_row=9,
    col_start=2, col_end=4,
    title="TOTAL ASSESSED",
    formula=f'=COUNTIF({F_ALL},"<>"&"")',
    subtitle="teeth with date observed",
    bg_top=BLUE_MED, bg_val=BLUE_XLIGHT,
    fg_val=BLUE_MED, fg_title="FFFFFFFF"
)

# Card 2 — On Time
kpi_card(ws,
    title_row=7, val_row=8, sub_row=9,
    col_start=5, col_end=7,
    title="ON TIME",
    formula=f'=COUNTIF({I_ALL},"On Time")',
    subtitle="within expected range",
    bg_top=GREEN_MED, bg_val=GREEN_LIGHT,
    fg_val=GREEN_MED, fg_title="FFFFFFFF"
)

# Card 3 — Early
kpi_card(ws,
    title_row=7, val_row=8, sub_row=9,
    col_start=8, col_end=10,
    title="EARLY",
    formula=f'=COUNTIF({I_ALL},"Early")',
    subtitle="before expected age",
    bg_top=BLUE_MED, bg_val=BLUE_LIGHT,
    fg_val=BLUE_MED, fg_title="FFFFFFFF"
)

# Card 4 — Delayed
kpi_card(ws,
    title_row=7, val_row=8, sub_row=9,
    col_start=11, col_end=13,
    title="DELAYED",
    formula=f'=COUNTIF({I_ALL},"Delayed")',
    subtitle="within 6-month grace period",
    bg_top=AMBER_DARK, bg_val=AMBER,
    fg_val=AMBER_DARK, fg_title="FFFFFFFF"
)

# Card 5 — REFER
kpi_card(ws,
    title_row=7, val_row=8, sub_row=9,
    col_start=14, col_end=16,
    title="⚠ NEEDS REFERRAL",
    formula=f'=COUNTIF({I_ALL},"⚠ REFER")',
    subtitle="past referral deadline",
    bg_top=RED_DARK, bg_val=RED_LIGHT,
    fg_val=RED_DARK, fg_title="FFFFFFFF"
)

# ════════════════════════════════════════════════════════════════════════════
# ROWS 11-12: Section divider
# ════════════════════════════════════════════════════════════════════════════
rh(11, 10)
rh(12, 20)
ws.merge_cells("B12:P12")
c = ws["B12"]
c.value = "PHASE-WISE BREAKDOWN"
c.font = Font(name="Arial", bold=True, size=10, color="FFFFFFFF")
c.fill = fill(BLUE_DARK)
c.alignment = aln(h="left", v="center")
c.border = bdr()
ws["B12"].value = "  PHASE-WISE BREAKDOWN"

# ════════════════════════════════════════════════════════════════════════════
# ROWS 13-22: Phase breakdown table  +  Status breakdown table (side by side)
# ════════════════════════════════════════════════════════════════════════════
rh(13, 22)  # header
for r in range(14, 22):
    rh(r, 18)

# --- Phase breakdown (cols B:H) ---
phase_hdr_cols = [
    (2, "Phase"),
    (3, "Teeth\nTracked"),
    (4, "On Time"),
    (5, "Early"),
    (6, "Delayed"),
    (7, "⚠ REFER"),
    (8, "% Complete"),
]
for col, label in phase_hdr_cols:
    c = ws.cell(row=13, column=col, value=label)
    c.font = Font(name="Arial", bold=True, size=9, color="FFFFFFFF")
    c.fill = fill(BLUE_MED)
    c.alignment = aln(h="center", v="center", wrap=True)
    c.border = bdr()

phase_data = [
    ("① Primary Eruption",   "I8:I14",  "F8:F14",  BLUE_LIGHT),
    ("② Primary Shedding",   "I16:I23", "F16:F23", GREEN_LIGHT),
    ("③ Permanent Eruption", "I25:I37", "F25:F37", PURPLE_LIGHT),
    ("All Phases",           "I8:I37",  "F8:F37",  GREY_LIGHT),
]

for i, (label, i_range, f_range, row_bg) in enumerate(phase_data):
    r = 14 + i
    if label == "All Phases":
        r = 18  # leave gap
        rh(17, 6)
        rh(18, 20)

    pt_i = f"'Patient Tracker'!{i_range}"
    pt_f = f"'Patient Tracker'!{f_range}"
    total_teeth = int(i_range.split("I")[1].split(":")[1]) - int(i_range.split("I")[1].split(":")[0].split("I")[-1]) + 1
    # Actually count from the range sizes
    r1 = int(i_range.replace("I","").split(":")[0])
    r2 = int(i_range.replace("I","").split(":")[1])
    total = r2 - r1 + 1

    bold_row = (label == "All Phases")
    bg = BLUE_DARK if bold_row else row_bg

    cells_data = [
        (2, label),
        (3, f"=COUNTA({pt_f})"),
        (4, f'=COUNTIF({pt_i},"On Time")'),
        (5, f'=COUNTIF({pt_i},"Early")'),
        (6, f'=COUNTIF({pt_i},"Delayed")'),
        (7, f'=COUNTIF({pt_i},"⚠ REFER")'),
        (8, f'=IFERROR(COUNTA({pt_f})/{total},"—")'),
    ]
    for col, val in cells_data:
        c = ws.cell(row=r, column=col, value=val)
        c.font = Font(name="Arial", bold=bold_row, size=9,
                      color="FFFFFFFF" if bold_row else TEXT_DARK)
        c.fill = fill(bg)
        c.alignment = aln(h="center" if col > 2 else "left", v="center")
        c.border = bdr()
        if col == 8 and not bold_row:
            c.number_format = "0%"
    if not bold_row:
        rh(r, 18)

# ════════════════════════════════════════════════════════════════════════════
# Status breakdown (cols J:P) — same rows 13–18
# ════════════════════════════════════════════════════════════════════════════
status_hdr = [
    (10, "Status"),
    (11, "Primary\nEruption"),
    (12, "Primary\nShedding"),
    (13, "Permanent\nEruption"),
    (14, "Total"),
    (15, "Visual"),
]
for col, label in status_hdr:
    c = ws.cell(row=13, column=col, value=label)
    c.font = Font(name="Arial", bold=True, size=9, color="FFFFFFFF")
    c.fill = fill(BLUE_MED)
    c.alignment = aln(h="center", v="center", wrap=True)
    c.border = bdr()

status_rows = [
    ("Erupted / Shed",    H_ALL,  "'Patient Tracker'!H8:H14", "'Patient Tracker'!H16:H23", "'Patient Tracker'!H25:H37", GREEN_LIGHT,   GREEN_MED,   "█"),
    ("Not yet observed",  H_ALL,  "'Patient Tracker'!H8:H14", "'Patient Tracker'!H16:H23", "'Patient Tracker'!H25:H37", BLUE_XLIGHT,   BLUE_MED,    "░"),
    ("Partial eruption",  H_ALL,  "'Patient Tracker'!H8:H14", "'Patient Tracker'!H16:H23", "'Patient Tracker'!H25:H37", AMBER,         AMBER_DARK,  "▒"),
    ("Absent",            H_ALL,  "'Patient Tracker'!H8:H14", "'Patient Tracker'!H16:H23", "'Patient Tracker'!H25:H37", RED_LIGHT,     RED_DARK,    "▓"),
    ("Impacted",          H_ALL,  "'Patient Tracker'!H8:H14", "'Patient Tracker'!H16:H23", "'Patient Tracker'!H25:H37", RED_LIGHT,     RED_DARK,    "▓"),
]
for i, (status, _, pe_range, ps_range, perm_range, row_bg, bar_color, bar_char) in enumerate(status_rows):
    r = 14 + i
    pe  = f"'Patient Tracker'!H8:H14"
    ps  = f"'Patient Tracker'!H16:H23"
    prm = f"'Patient Tracker'!H25:H37"

    pe_cnt  = f'=COUNTIF({pe},"{status}")'
    ps_cnt  = f'=COUNTIF({ps},"{status}")'
    prm_cnt = f'=COUNTIF({prm},"{status}")'
    total_cnt = f'=COUNTIF({H_ALL},"{status}")'
    # Visual bar: repeat char based on total count (max ~10)
    bar_formula = (
        f'=IFERROR(REPT("{bar_char}",COUNTIF({H_ALL},"{status}")),"")'
    )

    row_data = [
        (10, status),
        (11, pe_cnt),
        (12, ps_cnt),
        (13, prm_cnt),
        (14, total_cnt),
        (15, bar_formula),
    ]
    for col, val in row_data:
        c = ws.cell(row=r, column=col, value=val)
        c.font = Font(name="Arial", size=9, color=bar_color if col == 15 else TEXT_DARK,
                      bold=(col == 10))
        c.fill = fill(row_bg)
        c.alignment = aln(h="center" if col > 10 else "left", v="center")
        c.border = bdr()
    rh(r, 18)

# ════════════════════════════════════════════════════════════════════════════
# ROW 19: Section divider
# ════════════════════════════════════════════════════════════════════════════
rh(19, 10)
rh(20, 20)
ws.merge_cells("B20:P20")
c = ws["B20"]
c.value = "  ⚠  REFERRAL ALERT LIST — Teeth Past Referral Deadline"
c.font = Font(name="Arial", bold=True, size=10, color="FFFFFFFF")
c.fill = fill(RED_DARK)
c.alignment = aln(h="left", v="center")
c.border = bdr()

# ════════════════════════════════════════════════════════════════════════════
# ROWS 21-22: Referral table header
# ════════════════════════════════════════════════════════════════════════════
rh(21, 22)
ref_hdr = [
    (2,  "#"),
    (3,  "Tooth Name"),
    (5,  "Phase"),
    (7,  "Expected Range"),
    (9,  "Units"),
    (10, "Date Observed"),
    (11, "Age at Observation"),
    (12, "Days Past Deadline"),
    (14, "Status"),
    (15, "Caries?"),
    (16, "Referred?"),
]
ref_hdr_spans = {
    3: (3, 4), 5: (5, 6), 7: (7, 8), 12: (12, 13)
}
for col, label in ref_hdr:
    end_col = ref_hdr_spans.get(col, (col, col))[1]
    if end_col > col:
        ws.merge_cells(start_row=21, start_column=col,
                       end_row=21, end_column=end_col)
    c = ws.cell(row=21, column=col, value=label)
    c.font = Font(name="Arial", bold=True, size=9, color="FFFFFFFF")
    c.fill = fill(RED_DARK)
    c.alignment = aln(h="center", v="center", wrap=True)
    c.border = bdr()

# ════════════════════════════════════════════════════════════════════════════
# ROWS 22–34: REFER rows using IF formulas
# We pull each of the 28 data rows and show it only if Assessment = "⚠ REFER"
# ════════════════════════════════════════════════════════════════════════════
# Map: data row in PT → dashboard display row
# We'll show up to 13 potential REFER slots (one per row in PT that could REFER)
# Using IF(assessment="⚠ REFER", value, "") for each possible data row

PT_DATA_ROWS = list(range(8, 38))  # 8–37, skip section headers 15, 24
ACTUAL_DATA_ROWS = [r for r in PT_DATA_ROWS
                    if r not in (15, 24)]  # 15=shedding header, 24=perm header

rh(22, 16)

# We build one display row per actual data row, but only display if REFER
# To avoid blank rows cluttering, we'll use a helper approach:
# Show all 28 rows but colour them grey/invisible if not REFER
# and red if REFER — conditional formatting handles the hiding visually

for disp_idx, pt_row in enumerate(ACTUAL_DATA_ROWS):
    r = 22 + disp_idx
    rh(r, 16)

    assess_ref = f"'Patient Tracker'!I{pt_row}"
    is_refer = f'{assess_ref}="⚠ REFER"'

    def if_refer(val_formula, fallback='""'):
        return f"=IF({is_refer},{val_formula},{fallback})"

    # Col B: row number (only if REFER)
    c = ws.cell(row=r, column=2,
                value=f"=IF({is_refer},{disp_idx+1},\"\")")
    c.font = Font(name="Arial", bold=True, size=9, color=RED_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col C-D: Tooth name
    ws.merge_cells(start_row=r, start_column=3, end_row=r, end_column=4)
    c = ws.cell(row=r, column=3,
                value=if_refer(f"'Patient Tracker'!A{pt_row}"))
    c.font = Font(name="Arial", bold=True, size=9, color=RED_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="left")

    # Col E-F: Phase
    ws.merge_cells(start_row=r, start_column=5, end_row=r, end_column=6)
    c = ws.cell(row=r, column=5,
                value=if_refer(f"'Patient Tracker'!B{pt_row}"))
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="left")

    # Col G-H: Expected range  (e.g. "6 – 10 months")
    ws.merge_cells(start_row=r, start_column=7, end_row=r, end_column=8)
    range_formula = (
        f"=IF({is_refer},"
        f"'Patient Tracker'!C{pt_row}&\" – \"&'Patient Tracker'!D{pt_row},\"\")"
    )
    c = ws.cell(row=r, column=7, value=range_formula)
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col I: Units
    c = ws.cell(row=r, column=9,
                value=if_refer(f"'Patient Tracker'!E{pt_row}"))
    c.font = Font(name="Arial", size=9, italic=True, color="FF666666")
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col J: Date Observed
    c = ws.cell(row=r, column=10,
                value=if_refer(f"'Patient Tracker'!F{pt_row}", "0"))
    c.number_format = 'dd-mm-yyyy'
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col K: Age at observation
    c = ws.cell(row=r, column=11,
                value=if_refer(f"'Patient Tracker'!G{pt_row}", '""'))
    c.font = Font(name="Arial", size=9, italic=True, color="FF1A3A6B")
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col L-M: Days past deadline — highlight bright
    ws.merge_cells(start_row=r, start_column=12, end_row=r, end_column=13)
    c = ws.cell(row=r, column=12,
                value=if_refer(f"'Patient Tracker'!J{pt_row}", '""'))
    c.font = Font(name="Arial", bold=True, size=10, color=RED_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col N: Status
    c = ws.cell(row=r, column=14,
                value=if_refer(f"'Patient Tracker'!H{pt_row}", '""'))
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col O: Caries
    c = ws.cell(row=r, column=15,
                value=if_refer(f"'Patient Tracker'!K{pt_row}", '""'))
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

    # Col P: Referred
    c = ws.cell(row=r, column=16,
                value=if_refer(f"'Patient Tracker'!L{pt_row}", '""'))
    c.font = Font(name="Arial", size=9, color=TEXT_DARK)
    c.fill = fill(WHITE)
    c.border = bdr()
    c.alignment = aln(h="center")

# Conditional formatting on referral list:
# If col B (row number) is blank → grey out row; if not blank → red tint
last_ref_row = 22 + len(ACTUAL_DATA_ROWS) - 1
ref_range = f"B22:P{last_ref_row}"

refer_row_rule = FormulaRule(
    formula=['$B22<>""'],
    fill=PatternFill("solid", fgColor="FFFFE0E0"),
    font=Font(name="Arial", size=9, color=RED_DARK)
)
blank_row_rule = FormulaRule(
    formula=['$B22=""'],
    fill=PatternFill("solid", fgColor="FFF9F9F9"),
    font=Font(name="Arial", size=9, color="FFCCCCCC")
)
ws.conditional_formatting.add(ref_range, refer_row_rule)
ws.conditional_formatting.add(ref_range, blank_row_rule)

# ════════════════════════════════════════════════════════════════════════════
# "No referrals" message when count = 0
# ════════════════════════════════════════════════════════════════════════════
no_ref_row = last_ref_row + 1
rh(no_ref_row, 20)
ws.merge_cells(f"B{no_ref_row}:P{no_ref_row}")
c = ws[f"B{no_ref_row}"]
c.value = (
    f"=IF(COUNTIF('Patient Tracker'!I8:I37,\"⚠ REFER\")=0,"
    f"\"✓  No teeth past referral deadline — all assessed teeth are on track or within grace period.\",\"\")"
)
c.font = Font(name="Arial", bold=True, size=10, color=GREEN_DARK)
c.fill = fill(GREEN_LIGHT)
c.alignment = aln(h="center", v="center")
c.border = bdr()

# ════════════════════════════════════════════════════════════════════════════
# FOOTER
# ════════════════════════════════════════════════════════════════════════════
footer_row = no_ref_row + 2
rh(footer_row, 14)
ws.merge_cells(f"B{footer_row}:P{footer_row}")
c = ws[f"B{footer_row}"]
c.value = (
    "Source: AAPD Reference Manual of Pediatric Dentistry (Logan-Kronfeld chart, adapted). "
    "Grace period = 6 months past upper bound. This dashboard is a clinical aide — always apply clinical judgement."
)
c.font = Font(name="Arial", size=7.5, italic=True, color="FF856404")
c.fill = fill(AMBER)
c.border = Border(
    left=Side(style="thin", color="FF856404"),
    right=Side(style="thin", color="FF856404"),
    top=Side(style="thin", color="FF856404"),
    bottom=Side(style="thin", color="FF856404"),
)
c.alignment = aln(h="left", v="center", wrap=True)

# ════════════════════════════════════════════════════════════════════════════
# Print settings
# ════════════════════════════════════════════════════════════════════════════
ws.page_setup.orientation = "landscape"
ws.page_setup.paperSize = 9
ws.page_setup.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0

# Re-order sheets: Dashboard first
wb._sheets.sort(key=lambda s: (0 if s.title == "Dashboard" else
                                1 if s.title == "Patient Tracker" else
                                2 if s.title == "Reference Chart" else 3))

wb.save(OUTPUT)
print("Saved:", OUTPUT)

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'

Successfully replaced 1 instance(s) of the string in '/tmp/workspace/tooth-eruption/add_dashboard.py'