"""The preschool ladder and the Swimmer 1 reboot (data page, section 2b).

Follows anonymized children through the FOI 2026-507 registration extract to answer:
  A. What share of each year's new Swimmer 1 entrants had any preschool history?
  B. For children who came up from preschool, where did they enter the school-age
     track, by the highest preschool level they reached first?
  C. What share of children who topped out at each preschool level ever reappear
     in the school-age track within the data window?

Usage:
    python analyze_preschool_ladder.py [path/to/2026-507.csv]

Definitions (stated so the numbers can be argued with):
  * Preschool level = the digit after "Preschool" in the activity name; combined
    classes ("Preschool 4/5") take the lower level. Parent & Tot is not preschool.
  * School-age track = "Swimmer 1" .. "Swimmer 9"; combined classes take the lower
    level; adult and teen variants are excluded.
  * A child's "entry" is their earliest Swimmer registration by start date.
  * "Topped out at Preschool k" = the highest preschool level among the child's
    registrations that started before their entry (or all of them, if they never
    entered). Children with no preschool registration before entry are "no history".
  * Registrations include waitlist entries (the extract carries no status flag), so
    "registered" means enrolled or waitlisted. 2022 is largely missing from the
    extract (legacy program names), which left-censors the earliest histories, and
    children whose preschool ended late in 2025 have had little time to reappear
    (right-censoring). Both biases are noted where they bite.

Needs duckdb (pip install duckdb).
"""
from __future__ import annotations

import sys
from pathlib import Path

import duckdb

CSV = sys.argv[1] if len(sys.argv) > 1 else "newdocs/2026-507 - Working Copy (Excel).csv"
if not Path(CSV).exists():
    sys.exit(f"extract not found: {CSV} (available on request; see the receipts page)")

con = duckdb.connect()
con.execute(f"""
CREATE VIEW regs AS
SELECT "Customer ID" AS cid,
       "Activity Name" AS name,
       CAST("Start Date" AS DATE) AS start,
       CASE WHEN "Activity Name" ILIKE '%Preschool %'
            THEN TRY_CAST(regexp_extract("Activity Name", 'Preschool (\\d)', 1) AS INTEGER) END AS p_level,
       CASE WHEN "Activity Name" ILIKE '%Swimmer %'
             AND "Activity Name" NOT ILIKE '%Adult%' AND "Activity Name" NOT ILIKE '%Teen%'
            THEN TRY_CAST(regexp_extract("Activity Name", 'Swimmer (\\d)', 1) AS INTEGER) END AS s_level
FROM '{CSV}'
""")

# earliest school-age registration per child (entry)
con.execute("""
CREATE VIEW entry AS
SELECT cid, MIN(start) AS entry_date,
       arg_min(s_level, start) AS entry_level
FROM regs WHERE s_level IS NOT NULL GROUP BY cid
""")

# highest preschool level reached before entry (or ever, if no entry)
con.execute("""
CREATE VIEW ladder AS
SELECT r.cid, MAX(r.p_level) AS p_max, MIN(r.start) AS first_preschool, MAX(r.start) AS last_preschool,
       e.entry_date, e.entry_level
FROM regs r LEFT JOIN entry e USING (cid)
WHERE r.p_level IS NOT NULL AND (e.entry_date IS NULL OR r.start < e.entry_date)
GROUP BY r.cid, e.entry_date, e.entry_level
""")


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


print("=" * 72)
print("A. SWIMMER 1 ENTRANTS WITH PRESCHOOL HISTORY, BY ENTRY YEAR")
print("=" * 72)
print(f"{'Entry year':<12}{'Swimmer 1 entrants':>20}{'with preschool history':>24}{'share':>8}")
for yr, n, hist in q("""
    SELECT year(e.entry_date) y, COUNT(*) n,
           SUM(CASE WHEN l.cid IS NOT NULL THEN 1 ELSE 0 END) hist
    FROM entry e LEFT JOIN ladder l USING (cid)
    WHERE e.entry_level = 1 GROUP BY 1 ORDER BY 1"""):
    print(f"{yr:<12}{n:>20,}{hist:>24,}{100*hist/n:>7.0f}%")
print("Entrants = children whose earliest school-age registration was Swimmer 1 and started")
print("in that year. 2022-23 shares are understated: most 2022 preschool rows are missing.")

print()
print("=" * 72)
print("B. WHERE CHILDREN WHO CAME UP FROM PRESCHOOL ENTERED THE SCHOOL-AGE TRACK")
print("=" * 72)
print(f"{'Topped out at':<16}{'entered':>9}{'at Swimmer 1':>14}{'share':>7}{'at Swimmer 2':>14}{'share':>7}{'Swimmer 3+':>12}")
for p, n, s1, s2, s3 in q("""
    SELECT p_max, COUNT(*) n,
           SUM(CASE WHEN entry_level = 1 THEN 1 ELSE 0 END) s1,
           SUM(CASE WHEN entry_level = 2 THEN 1 ELSE 0 END) s2,
           SUM(CASE WHEN entry_level >= 3 THEN 1 ELSE 0 END) s3
    FROM ladder WHERE entry_date IS NOT NULL GROUP BY 1 ORDER BY 1"""):
    print(f"Preschool {p:<6}{n:>9,}{s1:>14,}{100*s1/n:>6.0f}%{s2:>14,}{100*s2/n:>6.0f}%{s3:>12,}")
print("Children with at least one preschool registration before their first Swimmer registration.")

print()
print("=" * 72)
print("C. DO CHILDREN WHO TOP OUT AT EACH PRESCHOOL LEVEL REAPPEAR AT SCHOOL AGE?")
print("=" * 72)
print(f"{'Topped out at':<16}{'children':>10}{'reappeared':>12}{'share':>7}{'  (last preschool start <= 2024)':>34}")
for p, n, re_, n24, re24 in q("""
    SELECT p_max, COUNT(*) n,
           SUM(CASE WHEN entry_date IS NOT NULL THEN 1 ELSE 0 END) re,
           SUM(CASE WHEN last_preschool <= DATE '2024-12-31' THEN 1 ELSE 0 END) n24,
           SUM(CASE WHEN last_preschool <= DATE '2024-12-31' AND entry_date IS NOT NULL THEN 1 ELSE 0 END) re24
    FROM ladder GROUP BY 1 ORDER BY 1"""):
    print(f"Preschool {p:<6}{n:>10,}{re_:>12,}{100*re_/n:>6.0f}%{re24:>14,} of {n24:<7,}{100*re24/n24:>6.0f}%")
print("'Reappeared' = has any Swimmer registration after their preschool registrations. The")
print("right-hand columns drop children whose preschool history ran into 2025, who have had")
print("less than a year to come back; those are the fairer reappearance rates.")

print()
print("=" * 72)
print("D. PRESCHOOL 5 FINISHERS PER YEAR (the only rung that changes placement)")
print("=" * 72)
for yr, n in q("""
    SELECT year(start) y, COUNT(DISTINCT cid) FROM regs WHERE p_level = 5 GROUP BY 1 ORDER BY 1"""):
    print(f"{yr}: {n:,} distinct children registered (enrolled or waitlisted) in Preschool 5")