"""Mark confirmed server cleanup on existing completed tasks, preserving all local files/records."""
import contextlib,datetime,hashlib,json,pathlib,sqlite3
ROOT=pathlib.Path(__file__).resolve().parent.parent;DATA=ROOT/'huobao-data';OUT=ROOT/'integration-work/historical-video-cleanup-20261112'
approved=json.loads((OUT/'approved.json').read_text());state={r['task']:r for r in json.loads((OUT/'server-status.json').read_text())}
inventory={r['id']:r for r in json.loads((OUT/'inventory.json').read_text())}
assert len(approved)==61 and all(state.get(a['task'],{}).get('status')=='cleaned' for a in approved),'Server cleanup incomplete'
def digest(file):
 with file.open('rb') as f:h=hashlib.file_digest(f,'sha256').hexdigest()
 return {'sha256':h,'bytes':file.stat().st_size}
for a in approved:
 r=inventory[a['task']];f=(DATA/r['local_path']).resolve(strict=True)
 assert f.is_relative_to((DATA/'static/videos').resolve()) and digest(f)==a['receipt'],'Retained video changed'
with contextlib.closing(sqlite3.connect(DATA/'huobao.sqlite3',timeout=15)) as db:
 db.row_factory=sqlite3.Row
 backup=OUT/'before-local-status-sync.sqlite3'
 if not backup.exists():
  with contextlib.closing(sqlite3.connect(backup)) as target:db.backup(target)
 db.execute('BEGIN IMMEDIATE')
 for a in approved:
  r=inventory[a['task']];current=db.execute('select * from sys_task where id=?',(a['task'],)).fetchone()
  assert current and all(current[k]==r[k] for k in ('status','local_path','task_id','config_id','config_base_url','provider','model'))
  params=json.loads(current['params'] or '{}')
  params['remoteCleanup']={'version':1,'status':'cleaned','receipt':a['receipt'],'localReceipt':a['receipt'],'index':a['index'],'attempts':0,'completedAt':datetime.datetime.now(datetime.timezone.utc).isoformat()}
  params.setdefault('downloadReceipt',a['receipt'])
  db.execute('update sys_task set params=? where id=?',(json.dumps(params,ensure_ascii=False,separators=(',',':')),a['task']))
 db.commit()
 for a in approved:
  r=db.execute('select status,local_path,params from sys_task where id=?',(a['task'],)).fetchone()
  assert r['status']=='completed' and r['local_path']==inventory[a['task']]['local_path'] and json.loads(r['params'])['remoteCleanup']['status']=='cleaned'
 assert db.execute('pragma quick_check').fetchone()[0]=='ok'
 before_media=json.loads((OUT/'media-before.json').read_text())
 missing=[];changed=[]
 for relative,receipt in before_media.items():
  p=DATA/relative
  if not p.is_file():missing.append(relative)
  elif digest(p)!=receipt:changed.append(relative)
 assert not missing and not changed,'A pre-existing local asset changed'
 # Prove this status sync changed only the two cleanup receipt fields.
 with contextlib.closing(sqlite3.connect(backup)) as old:
  old.row_factory=sqlite3.Row
  for a in approved:
   left=dict(old.execute('select * from sys_task where id=?',(a['task'],)).fetchone());right=dict(db.execute('select * from sys_task where id=?',(a['task'],)).fetchone())
   lp=json.loads(left.pop('params') or '{}');rp=json.loads(right.pop('params') or '{}')
   for k in ('remoteCleanup','downloadReceipt'):lp.pop(k,None);rp.pop(k,None)
   assert left==right and lp==rp,'Unexpected modification to local task'
 report={'tasksMarkedCleaned':len(approved),'completedTasksRetained':len(approved),'localVideosUnchanged':len(approved),'originalLocalMediaUnchanged':len(before_media),'databaseIntegrity':'ok','onlyCleanupReceiptsChanged':True,'backup':str(OUT/'before.sqlite3')}
(OUT/'final-local-report.json').write_text(json.dumps(report,indent=2),'utf-8')
print(json.dumps(report))