from __future__ import annotations
import datetime as dt
import hashlib
import json
import os
import sqlite3
from pathlib import Path

SRC = Path('/home/agent/jarvis_runtime/finance-system/data/finance.sqlite3')
ROOT = Path('/home/agent/jarvis_runtime/finance-system/legacy_fk_repair')
ROOT.mkdir(parents=True, exist_ok=True)
os.chmod(ROOT, 0o700)
stamp = dt.datetime.now(dt.timezone.utc).strftime('%Y%m%dT%H%M%SZ')
backup = ROOT / f'preflight-restored-copy-{stamp}.sqlite3'
report_path = ROOT / f'preflight-private-{stamp}.json'
fd = os.open(backup, os.O_CREAT | os.O_EXCL | os.O_WRONLY, 0o600); os.close(fd)
src = sqlite3.connect(f'file:{SRC}?mode=ro', uri=True)
dst = sqlite3.connect(backup)
src.backup(dst); dst.close(); src.close(); os.chmod(backup, 0o600)
conn = sqlite3.connect(backup)
conn.row_factory = sqlite3.Row
integrity = conn.execute('PRAGMA integrity_check').fetchone()[0]
fks = [dict(zip(('table','rowid','parent','fkid'), row)) for row in conn.execute('PRAGMA foreign_key_check')]
plan_cols = [dict(row) for row in conn.execute("PRAGMA table_info('broker_import_execution_plans')")]
plan_fk = [dict(row) for row in conn.execute("PRAGMA foreign_key_list('broker_import_execution_plans')")]
findings=[]
for fk in fks:
    if fk['table'] != 'broker_import_execution_plans':
        findings.append({'fk':fk,'unexpected':True}); continue
    row = conn.execute('SELECT * FROM broker_import_execution_plans WHERE rowid=?',(fk['rowid'],)).fetchone()
    if row is None:
        findings.append({'fk':fk,'missing_plan_row':True}); continue
    d=dict(row)
    txid=d.get('transaction_id')
    parent_count=conn.execute('SELECT COUNT(*) FROM transactions WHERE transaction_id=?',(txid,)).fetchone()[0]
    findings.append({'fk':fk,'plan':d,'parent_count':parent_count})
transaction_cols=[dict(row) for row in conn.execute("PRAGMA table_info('transactions')")]
# Candidate uniqueness evidence by exact shared identity values only. Never fuzzy-match.
identity_names=('source_id','external_id','external_transaction_id','source_row_hash','row_hash','source_hash','provider_transaction_id')
identity_evidence=[]
tx_colnames={c['name'] for c in transaction_cols}
plan_colnames={c['name'] for c in plan_cols}
for item in findings:
    p=item.get('plan') or {}
    per={}
    for name in identity_names:
        if name in p and name in tx_colnames and p.get(name) not in (None,''):
            per[name]=conn.execute(f'SELECT COUNT(*) FROM transactions WHERE "{name}"=?',(p[name],)).fetchone()[0]
    identity_evidence.append(per)
report={'created_at_utc':stamp,'source_db':str(SRC),'backup_copy':str(backup),'backup_mode':oct(backup.stat().st_mode & 0o777),'integrity_check':integrity,'foreign_key_findings':fks,'plan_table_info':plan_cols,'plan_foreign_keys':plan_fk,'transaction_table_info':transaction_cols,'finding_details':findings,'exact_identity_match_counts':identity_evidence}
fd=os.open(report_path,os.O_CREAT|os.O_EXCL|os.O_WRONLY,0o600)
with os.fdopen(fd,'w',encoding='utf-8') as fh: json.dump(report,fh,ensure_ascii=False,indent=2,default=str)
conn.close()
summary={
 'backup_copy':str(backup),'private_report':str(report_path),'modes':[oct(backup.stat().st_mode&0o777),oct(report_path.stat().st_mode&0o777)],
 'integrity':integrity,'fk_count':len(fks),'all_target_table':all(x['table']=='broker_import_execution_plans' for x in fks),
 'all_parent_missing':all(x.get('parent_count')==0 for x in findings),
 'transaction_id_nullable':next((c['notnull']==0 for c in plan_cols if c['name']=='transaction_id'),False),
 'statuses':[x.get('plan',{}).get('execution_status') for x in findings],
 'exact_identity_match_counts':identity_evidence,
 'plan_columns':[c['name'] for c in plan_cols], 'transaction_columns':[c['name'] for c in transaction_cols],
 'fk_definitions':plan_fk,
}
print(json.dumps(summary,ensure_ascii=False,indent=2,default=str))