"""Reconcile only user-cleared upstream jobs; preserve records and all existing media."""
import json,sqlite3,datetime
from pathlib import Path
root=Path(__file__).resolve().parent.parent
report=json.loads((root/'integration-work/cleared-task-states.json').read_text())
assert len(report)==21 and all(not r['in_queue'] and r['remote_status'] is None for r in report.values())
database=root/'huobao-data/huobao.sqlite3'
backup=root/'huobao-data/backups'/('before-cancel-reconcile-'+datetime.datetime.now().strftime('%Y%m%d-%H%M%S')+'.sqlite3')
changed=[]
with sqlite3.connect(database) as db,sqlite3.connect(backup) as dest:
 db.backup(dest)
 for tid,row in report.items():
  cur=db.execute("UPDATE sys_task SET status='failed',stage='failed',error_code='user_cancelled',error_msg=?,updated_at=? WHERE id=? AND task_id=? AND status IN ('processing','failed') AND local_path IS NULL AND result_url IS NULL",('已按你的要求清除服务器任务；已完成素材保持不变。',datetime.datetime.now(datetime.timezone.utc).isoformat().replace('+00:00','Z'),int(tid),row['encoded']))
  if cur.rowcount:changed.append(int(tid))
 assert db.execute('PRAGMA quick_check').fetchone()[0]=='ok'
print('Reconciled cleared jobs without deleting records or media:',changed)
print('Database backup:',backup)