"""Public-resource analyses of the FOI 2026-505/507 datasets for the site's data section.

Four analyses, each printed as a markdown-ready block:
  A. Requests-to-children conversion table (what "8,000 waitlisted" really means)
  B. The progression pipeline histogram (children at each stage, first class -> instructor)
  C. Growth decomposition (where the 2022->2025 seat expansion went, and didn't)
  D. Demand-proportional level mix (how far reallocating the same seats would cut the queue)

Data: newdocs/2026-505 (Activity Totals: seats/enrolments/waits) and
newdocs/2026-507 (registration extract: coded children; includes waitlisted
children indistinguishably - noted per analysis).

Usage:
    python analyze_for_site.py            # all four
"""
from __future__ import annotations

import duckdb

A505 = "newdocs/2026-505 - Working Copy (Excel).csv"
R507 = "newdocs/2026-507 - Working Copy (Excel).csv"

con = duckdb.connect()
con.execute(f'CREATE VIEW a AS SELECT *, year("Activity Start Date") y FROM \'{A505}\'')
con.execute(f'CREATE VIEW r AS SELECT * FROM \'{R507}\'')

LESSON_LEVELS = [
    ("Parent & Tot", "\"Activity Name\" ILIKE '%Parent%Tot%'"),
    ("Preschool 1", "\"Activity Name\" ILIKE '%Preschool 1 %'"),
    ("Preschool 2", "\"Activity Name\" ILIKE '%Preschool 2 %'"),
    ("Preschool 3", "\"Activity Name\" ILIKE '%Preschool 3 %'"),
    ("Preschool 4", "\"Activity Name\" ILIKE '%Preschool 4 %'"),
    ("Preschool 5", "\"Activity Name\" ILIKE '%Preschool 5 %'"),
    ("Swimmer 1", "\"Activity Name\" = 'Swimming - Swimmer 1'"),
    ("Swimmer 2", "\"Activity Name\" = 'Swimming - Swimmer 2'"),
    ("Swimmer 3", "\"Activity Name\" = 'Swimming - Swimmer 3'"),
    ("Swimmer 4", "\"Activity Name\" = 'Swimming - Swimmer 4'"),
    ("Swimmer 5", "\"Activity Name\" = 'Swimming - Swimmer 5'"),
    ("Swimmer 6", "\"Activity Name\" = 'Swimming - Swimmer 6'"),
    ("Swimmer 7 (Rookie)", "\"Activity Name\" ILIKE '%Swimmer 7%'"),
    ("Swimmer 8 (Ranger)", "\"Activity Name\" ILIKE '%Swimmer 8%'"),
    ("Swimmer 9 (Star)", "\"Activity Name\" ILIKE '%Swimmer 9%'"),
]
CERT_STAGES = [
    ("Bronze Medallion", "\"Activity Name\" ILIKE '%Bronze Medallion%' AND \"Activity Name\" NOT ILIKE '%retest%'"),
    ("Bronze Cross", "\"Activity Name\" ILIKE '%Bronze Cross%'"),
    ("National Lifeguard", "\"Activity Name\" ILIKE '%National Lifeguard%'"),
    ("Instructor cert (initial)", "\"Activity Name\" ILIKE '%Instructor%' AND \"Activity Name\" NOT ILIKE '%Recert%'"),
    ("Instructor recert", "(\"Activity Name\" ILIKE '%Instructor%Recert%' OR \"Activity Name\" ILIKE '%Recert%Swim for Life%' OR \"Activity Name\" = 'LSI Recert')"),
]
YEAR = 2024  # the last complete year in both datasets


def q1(sql):
    return con.execute(sql).fetchall()


print("=" * 70)
print("A. WHAT 'REQUESTS' MEAN IN CHILDREN (2024)")
print("=" * 70)
print(f"{'Level':<20}{'Seats':>7}{'Enrol':>7}{'Waits':>7}{'Demand':>8}{'Children':>9}{'Events/child':>13}")
for name, cond in LESSON_LEVELS:
    s = q1(f'SELECT SUM("Max Registration"), SUM("# of Registrations"), SUM(Waits) FROM a WHERE y={YEAR} AND {cond}')[0]
    u = q1(f'SELECT COUNT(DISTINCT "Customer ID") FROM r WHERE year("Start Date")={YEAR} AND {cond}')[0][0]
    if not s[0]:
        continue
    demand = (s[1] or 0) + (s[2] or 0)
    ratio = demand / u if u else float("nan")
    print(f"{name:<20}{s[0]:>7.0f}{s[1]:>7.0f}{s[2]:>7.0f}{demand:>8.0f}{u:>9}{ratio:>13.2f}")
print("Note: 'Children' counts unique coded IDs touching the level (enrolled or")
print("waitlisted - the extract does not distinguish). 'Events/child' converts")
print("the City's 'requests' language into people.")

print()
print("=" * 70)
print("B. THE PROGRESSION PIPELINE, 2024 (children at each stage)")
print("=" * 70)
bars = []
for name, cond in LESSON_LEVELS:
    u = q1(f'SELECT COUNT(DISTINCT "Customer ID") FROM r WHERE year("Start Date")={YEAR} AND {cond}')[0][0]
    bars.append((name, u, "children (unique, incl. waitlisted)"))
for name, cond in CERT_STAGES:
    reg = q1(f'SELECT COALESCE(SUM("# of Registrations"),0) FROM a WHERE y={YEAR} AND {cond}')[0][0]
    bars.append((name, int(reg), "course registrations"))
peak = max(b[1] for b in bars) or 1
for name, n, unit in bars:
    bar = "#" * max(0, round(44 * n / peak))
    print(f"{name:<26}{n:>6}  {bar}")
print("Lesson stages: unique children (507). Certification stages: course")
print("registrations (505). 'Instructor cert (initial)': the number of initial")
print("teaching-certification seats the public could register for.")

print()
print("=" * 70)
print("C. WHERE THE 2023->2025 SEAT GROWTH WENT")
print("=" * 70)
print("(2022 ran under the legacy Red Cross program names and converts to the")
print("current levels imperfectly; system-wide, seats grew 16,088 -> 23,841")
print("from 2022 to 2025. Per-level decomposition starts at 2023, the first")
print("full Swim for Life year.)")
print(f"{'Level':<20}{'2023':>7}{'2024':>7}{'2025':>7}{'chg 23-25':>11}")
for name, cond in LESSON_LEVELS:
    row = {}
    for yy in (2023, 2024, 2025):
        row[yy] = q1(f'SELECT COALESCE(SUM("Max Registration"),0) FROM a WHERE y={yy} AND {cond}')[0][0]
    if row[2023] == 0 and row[2025] == 0:
        continue
    chg = (row[2025] - row[2023])
    pct = f"{100*chg/row[2023]:+.0f}%" if row[2023] else "new"
    print(f"{name:<20}{row[2023]:>7.0f}{row[2024]:>7.0f}{row[2025]:>7.0f}{chg:>+8.0f} {pct:>4}")

print()
print("=" * 70)
print("D. DEMAND-PROPORTIONAL REALLOCATION (2024, same total seats)")
print("=" * 70)
rows = []
for name, cond in LESSON_LEVELS:
    s = q1(f'SELECT COALESCE(SUM("Max Registration"),0), COALESCE(SUM("# of Registrations"),0), COALESCE(SUM(Waits),0) FROM a WHERE y={YEAR} AND {cond}')[0]
    if s[0]:
        rows.append((name, s[0], s[1] + s[2]))
S = sum(x[1] for x in rows)
D = sum(x[2] for x in rows)
cur_unserved = sum(max(0, d - c) for _, c, d in rows)
prop_unserved = sum(max(0, d - S * d / D) for _, c, d in rows)
print(f"Total seats {S:.0f}; total demand events {D:.0f}")
print(f"Unserved demand events, actual mix:      {cur_unserved:>7.0f}")
print(f"Unserved demand events, demand-prop mix: {prop_unserved:>7.0f}  ({100*(cur_unserved-prop_unserved)/cur_unserved:.0f}% lower)")

print("Assumes seats are fungible across levels (instructor-hours roughly are;")
print("class-size ratios differ modestly by level) and demand stays fixed.")
print("This is an upper bound on what pure reallocation buys - the remaining")
print("unserved demand requires added capacity, not rearrangement.")

print()
print("=" * 70)
print("E. SEATS PER 100 KINDERGARTNERS: WHAT 'EVERY CHILD THROUGH THREE LEVELS' NEEDS")
print("=" * 70)
K = 3274  # VSB public-school kindergarten headcount 2025/26 (data/denominators.csv)
ATTEMPTS = [1.5, 2.5]  # attempts a child needs to clear a level: design target / measured demand events per child (upper bound; includes re-queuing)
def seats(cond, year):
    return q1(f'SELECT COALESCE(SUM("Max Registration"),0) FROM a WHERE y={year} AND {cond}')[0][0]
CHILD = "\"Activity Name\" NOT ILIKE '%Adult%' AND \"Activity Name\" NOT ILIKE '%Teen%'"
s1 = {y: seats(f"\"Activity Name\" ILIKE 'Swimming - Swimmer 1%' AND {CHILD}", y) for y in (2023, 2024, 2025)}
s13 = {y: sum(seats(f"\"Activity Name\" ILIKE 'Swimming - Swimmer {l}%' AND {CHILD}", y) for l in (1, 2, 3)) for y in (2024, 2025)}
upper = sum(seats(f"\"Activity Name\" ILIKE 'Swimming - Swimmer {l}%' AND {CHILD}", 2024) for l in range(4, 10))
fall_s1 = {y: q1(f'SELECT COALESCE(SUM("Max Registration"),0) FROM a WHERE y={y} AND "Term" ILIKE \'Fall%\' AND "Activity Name" ILIKE \'Swimming - Swimmer 1%\' AND {CHILD}')[0][0] for y in (2023, 2024, 2025)}
fall_share = sum(fall_s1[y] for y in (2023, 2024)) / sum(s1[y] for y in (2023, 2024))
print(f"Kindergarten cohort K = {K:,}. Steady state: one cohort must clear each of Swimmer 1, 2, 3 every year,")
print(f"so seats needed per level = K x attempts-per-level. Attempts bands: {ATTEMPTS[0]} (design target) and {ATTEMPTS[1]} (measured).")
print(f"Fall share of the year's Swimmer 1 seats (2023-24 average): {100*fall_share:.0f}%")
print()
print(f"{'Measure':<40}{'now':>8}{'per100K':>9}{'need@1.5':>10}{'need@2.5':>10}{'met':>12}")
rows = [
    ("Swimmer 1 seats, whole year (2024)", s1[2024], 1, 1.0),
    ("Swimmer 1 seats, whole year (2025)", s1[2025], 1, 1.0),
    ("Swimmer 1-3 seats, whole year (2024)", s13[2024], 3, 1.0),
    ("Swimmer 1-3 seats, whole year (2025)", s13[2025], 3, 1.0),
    ("Swimmer 1 seats, fall term (2025)", fall_s1[2025], 1, fall_share),
    ("Swimmer 1 seats, fall term (2026, 690 at release)", 690, 1, fall_share),
]
for label, now, levels, share in rows:
    per100 = 100 * now / K
    need = [100 * levels * a * share for a in ATTEMPTS]
    print(f"{label:<40}{now:>8,}{per100:>9.0f}{need[0]:>10.0f}{need[1]:>10.0f}{100*per100/need[1]:>7.0f}-{100*per100/need[0]:.0f}%")
add = [(levels_a := 3) * a * K - s13[2024] for a in ATTEMPTS]
print()
print(f"Additional Swimmer 1-3 seats needed per year, on top of 2024's {s13[2024]:,}: {add[0]:,.0f} (at 1.5) to {add[1]:,.0f} (at 2.5);")
print(f"the whole aquatics program offered {q1('SELECT SUM(\"Max Registration\") FROM a WHERE y=2025')[0][0]:,.0f} seats in 2025.")
print(f"ADDITIVITY: the need is counted as new seats. Upper levels are held constant - {upper:,} Swimmer 4-9 seats in 2024")
print("plus the Patrol, Bronze and National Lifeguard courses - because they are the path by which swimmers become")
print("instructors (section B). Filling beginner levels by reallocating from the top would shrink future instructor supply.")

# convert added seats to instructor time using the current Swimmer 1-3 class format
h13, prog_seats, prog_hours = q1(f"""SELECT SUM("Total Hours"),
    (SELECT SUM("Max Registration") FROM a WHERE y=2024 AND "Activity Name" ILIKE 'Swimming - %'),
    (SELECT SUM("Total Hours") FROM a WHERE y=2024 AND "Activity Name" ILIKE 'Swimming - %')
    FROM a WHERE y=2024 AND ("Activity Name" ILIKE 'Swimming - Swimmer 1%' OR "Activity Name" ILIKE 'Swimming - Swimmer 2%' OR "Activity Name" ILIKE 'Swimming - Swimmer 3%') AND {CHILD}""")[0]
hps = h13 / s13[2024]
print()
print(f"INSTRUCTOR TIME: Swimmer 1-3 classes used {h13:,.0f} contact hours for {s13[2024]:,} seats in 2024 = {hps:.2f} hours per seat (6 children per class).")
print(f"{'attempts':>9}{'added seats':>13}{'added hours':>13}{'FTE @1650h':>12}{'part-timers @400h':>19}{'pay @$30+20%':>14}{'x whole program hours':>23}")
for a, add in zip(ATTEMPTS, add):
    hrs = add * hps
    print(f"{a:>9}{add:>13,.0f}{hrs:>13,.0f}{hrs/1650:>12.1f}{hrs/400:>19.0f}{'$'+format(hrs*36, ',.0f'):>14}{(prog_hours+hrs)/prog_hours:>23.2f}")
print(f"(whole Swimming-prefixed program 2024: {prog_seats:,.0f} seats, {prog_hours:,.0f} contact hours)")
print("FORMAT LEVER: attempts per level ~ 1 / progression rate per attempt. Measured (analyze_format_progression.py,")
print("observational, waitlist-blended): weekly 20.1%, intensive 28.1% -> intensives cut attempts by a factor of ~0.72,")
print("i.e. from 2.5 to ~1.8; reaching 1.5 needs ~33% per attempt, beyond format alone (placement and no-show fixes).")