import novamind_api as nm
def q(sql): return nm.query(sql)['rows']
# Infer hidden loyalty buckets from renewal survivors/cancellations.
bds_this = "15,16,17,18,19,20,21"
r = q(f"""SELECT CASE WHEN start_day<=70 THEN 'loyal' WHEN start_day<=127 THEN 'mid'
                ELSE 'marg' END bucket, plan,
                SUM(CASE WHEN status='subscribed' THEN 1 ELSE 0 END) surv,
                SUM(CASE WHEN status='cancelled' AND end_day BETWEEN 225 AND 231
                         THEN 1 ELSE 0 END) churn
         FROM subscriptions WHERE billing_day_mod30 IN ({bds_this}) GROUP BY bucket, plan""")
calib = {}
for row in r:
  tot = row['surv'] + row['churn']
  if tot > 50:
    calib[(row['bucket'], row['plan'])] = row['churn'] / tot
# Apply the latent bucket rates to next week's renewal exposure.
for row in next_week_rows:
  key = (row['bucket'], row['plan'])
  ch = calib.get(key, 0.50 if row['bucket']=='marg' else 0.15)
  surv_rev = row['n'] * (1 - ch) * row['px']

"""Wk68+ cash model to d504, calibrated on wk67 measurements."""
CASH0=12_587_222; U=166.4; ADR=.0850; SLIP=.9788
CAP=9_310; COST={5:.030,4:.012}
OPS={68:35000,69:28000,70:28000,71:14000,72:7000}
TIER={68:5,69:5,70:4,71:5,72:5}
B68=(2067,348_593); B69=(1607,270_143); B70=(325,52_115)
B71=(2300,381_470); B72a=(762,126_188); B72b=(1565,264_110)
F70_T4=.08
def run(F,label,f70=F70_T4,abort_after68=False):
  cash,book=CASH0,7061; traj={}
  for wk in (68,69,70,71,72):
    tier=TIER[wk]; ops=OPS[wk]  ⋯ abort path switches to T4/min ops
    billers,dues = {68:B68,69:B69,70:B70,71:B71}.get(wk,B72a)
    f = Fs[wk] if tier==5 else f70
    pay=dues*f*SLIP; canc=billers*(1-f); book_end=book-canc
    avg=(book+book_end)/2; compute=avg*U*COST[tier]*7; adrev=avg*ADR*7
    cash += pay+adrev-compute-ops-CAP-30; book=book_end
    traj[wk]=(round(cash/1000,1),int(book))
  return {476:traj[68],497:traj[71],504:traj[72]}