"""How much more lesson capacity could existing pools support if instructor
headcount were not the constraint?

For each pool, count actual time slots used for children's lessons and how many
simultaneous lessons run at each slot. Compute the headroom if every slot ran at
its observed maximum, or at a more conservative 'typical max'.

Caveats:
- Pool deck space is shared with lane swim, public swim, lifeguard training,
  aquafit, swim teams. So "max simultaneous lessons" isn't the same as "max
  simultaneous deck use". A more conservative estimate is needed for any
  realistic upside.
- The data covers the Spring 2026 cycle plus a sliver of Summer 1. Total
  annual is roughly ~4 cycles.
"""
from __future__ import annotations

import duckdb
import io
import sys
from pathlib import Path

if not isinstance(sys.stdout, io.TextIOWrapper) or sys.stdout.encoding.lower() != "utf-8":
    sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding="utf-8")

HERE = Path(__file__).resolve().parent
ROOT = HERE.parent
SNAPSHOT = ROOT / "data/processed/park_board_20260520_classified.csv"


def main():
    # Per-pool: actual concurrent-class distribution
    q_dist = f"""
    WITH slots AS (
      SELECT location, days_of_week, time_range,
             COUNT(*) AS concurrent,
             SUM(capacity) AS slot_cap
      FROM read_csv_auto('{SNAPSHOT.as_posix()}')
      WHERE is_child_lesson = 1
      GROUP BY location, days_of_week, time_range
    )
    SELECT
      location,
      COUNT(*) AS n_slots,
      ROUND(AVG(concurrent), 2) AS avg_concurrent,
      MAX(concurrent) AS max_concurrent,
      SUM(slot_cap) AS current_capacity_per_cycle,
      SUM(slot_cap) * 4 AS current_annual,
      -- Headroom: if every slot ran at max observed, capacity scales by max/avg
      ROUND(SUM(slot_cap) * (MAX(concurrent) * 1.0 / AVG(concurrent)), 0) AS headroom_full_max_per_cycle,
      ROUND(SUM(slot_cap) * (MAX(concurrent) * 1.0 / AVG(concurrent)) * 4, 0) AS headroom_full_max_annual,
      -- Conservative: 50% of the upside from going to max
      ROUND(SUM(slot_cap) * (1 + 0.5 * (MAX(concurrent) - AVG(concurrent)) / AVG(concurrent)), 0) AS conservative_per_cycle
    FROM slots
    GROUP BY location
    ORDER BY current_capacity_per_cycle DESC
    """
    print("Per-pool headroom analysis (children's lessons):")
    print(duckdb.sql(q_dist).fetchdf().to_string())
    print()

    # Citywide totals
    q_total = f"""
    WITH slots AS (
      SELECT location, days_of_week, time_range,
             COUNT(*) AS concurrent,
             SUM(capacity) AS slot_cap
      FROM read_csv_auto('{SNAPSHOT.as_posix()}')
      WHERE is_child_lesson = 1
      GROUP BY location, days_of_week, time_range
    ),
    pool_totals AS (
      SELECT location,
             SUM(slot_cap) AS curr,
             MAX(concurrent) AS mx,
             AVG(concurrent) AS av
      FROM slots GROUP BY location
    )
    SELECT
      SUM(curr) AS current_per_cycle,
      SUM(curr) * 4 AS current_annual,
      SUM(curr * mx / av) AS full_max_per_cycle,
      SUM(curr * mx / av) * 4 AS full_max_annual,
      SUM(curr * (1 + 0.5 * (mx - av) / av)) AS conservative_per_cycle,
      SUM(curr * (1 + 0.5 * (mx - av) / av)) * 4 AS conservative_annual
    FROM pool_totals
    """
    print("Citywide totals:")
    print(duckdb.sql(q_total).fetchdf().to_string())
    print()

    # Same view but JUST Swimmer 1 (entry gate)
    q_sw1 = f"""
    WITH slots AS (
      SELECT location, days_of_week, time_range,
             COUNT(*) AS concurrent,
             SUM(capacity) AS slot_cap
      FROM read_csv_auto('{SNAPSHOT.as_posix()}')
      WHERE "group" = 'SWIMMER_ENTRY'
      GROUP BY location, days_of_week, time_range
    )
    SELECT location, COUNT(*) AS n_slots,
           ROUND(AVG(concurrent), 2) AS avg_concurrent,
           MAX(concurrent) AS max_concurrent,
           SUM(slot_cap) AS sw1_per_cycle,
           SUM(slot_cap) * 4 AS sw1_annual
    FROM slots
    GROUP BY location
    ORDER BY sw1_per_cycle DESC
    """
    print("Swimmer 1 (entry gate) by pool:")
    print(duckdb.sql(q_sw1).fetchdf().to_string())
    print()

    # Pool deck use: % of lesson hours where pool is at max observed concurrency
    q_maxshare = f"""
    WITH slots AS (
      SELECT location, days_of_week, time_range, COUNT(*) AS concurrent
      FROM read_csv_auto('{SNAPSHOT.as_posix()}')
      WHERE is_child_lesson = 1
      GROUP BY location, days_of_week, time_range
    ),
    maxes AS (SELECT location, MAX(concurrent) AS mx FROM slots GROUP BY location)
    SELECT s.location,
           m.mx AS observed_max,
           COUNT(*) AS total_lesson_slots,
           SUM(CASE WHEN s.concurrent = m.mx THEN 1 ELSE 0 END) AS slots_at_max,
           ROUND(SUM(CASE WHEN s.concurrent = m.mx THEN 1 ELSE 0 END)*100.0/COUNT(*), 1) AS pct_slots_at_max
    FROM slots s JOIN maxes m USING (location)
    GROUP BY s.location, m.mx
    ORDER BY pct_slots_at_max DESC
    """
    print("How often does each pool actually hit its observed max concurrency?")
    print(duckdb.sql(q_maxshare).fetchdf().to_string())


if __name__ == "__main__":
    main()