#!/usr/bin/env python3
"""Normalize presentation metadata only, after real UI seeding and with server stopped.
Never changes a title, prompt, plan or response. Uses the documented SQLite helper.
"""
import datetime,json,pathlib,re,sqlite3,subprocess,sys
checkout=pathlib.Path(sys.argv[1]);root=pathlib.Path(__file__).parent
con=sqlite3.connect(f'file:{checkout}/.t3/userdata/statev2.sqlite?mode=ro',uri=True)
threads={}
for tid,title in con.execute('select thread_id,title from orchestration_v2_projection_threads'):
 index=4 if title.startswith('Implement') else 3 if title.startswith('Evidence') else 2 if title.startswith('short') else 1 if title.startswith('Keep') else 0
 threads[tid]=f'2026-10-27T14:0{index}:00.000Z'

def norm(x,t):
 if isinstance(x,dict):
  return {k:(t if isinstance(v,str) and re.fullmatch(r'\d{4}-\d\d-\d\dT[0-9:.]+Z',v) else norm(v,t)) for k,v in x.items()}
 if isinstance(x,list):return [norm(v,t) for v in x]
 return x
for short in ['threads','runs','messages','turn_items']:
 table='orchestration_v2_projection_'+short
 columns=[r[1] for r in con.execute(f'pragma table_info({table})')]
 rows=[dict(zip(columns,r)) for r in con.execute(f'select * from {table}')]
 changes=[]
 for r in rows:
  t=threads[r['thread_id']];p=norm(json.loads(r['payload_json']),t)
  if short=='threads':
   p['branch']=None
   p['settledOverride']='active'
   p['settledAt']=None
   p['unsettledAt']=t
   p['autoSettleDisabledAt']=t
   p['lastVisitedAt']='2026-10-27T15:00:00.000Z'
  sets={k:t for k,v in r.items() if k.endswith('_at') and isinstance(v,str)}
  sets['payload_json']=json.dumps(p,ensure_ascii=True,separators=(',',':'))
  key=columns[0]
  for k,v in sets.items(): changes.append((k,r[key],v))
 def lit(s):return "'"+s.replace("'","''")+"'"
 assignments=[]
 for col in dict.fromkeys(k for k,_,_ in changes):
  cases=' '.join('WHEN '+lit(keyval)+' THEN '+lit(value) for k,keyval,value in changes if k==col)
  assignments.append(col+'=CASE '+columns[0]+' '+cases+' ELSE '+col+' END')
 sql='UPDATE '+table+' SET '+', '.join(assignments)+';\n'
 path=root/(checkout.name+'-normalize-'+short+'.sql');path.write_text(sql)
 subprocess.run(['node','apps/server/scripts/t3-sqlite-state.ts','exec','--base-dir',str(checkout/'.t3'),'--file',str(path)],cwd=checkout,check=True)
print('Normalized timestamps and removed checkout-specific branch metadata; original title bytes preserved.')