from __future__ import annotations
import json, os, sqlite3
from pathlib import Path
DB=Path('/home/agent/jarvis_runtime/finance-system/legacy_fk_repair/preflight-restored-copy-20260729T075746Z.sqlite3')
OUT=DB.parent/'deterministic-identity-private.json'
conn=sqlite3.connect(f'file:{DB}?mode=ro',uri=True); conn.row_factory=sqlite3.Row
cols={t:[r['name'] for r in conn.execute(f"PRAGMA table_info('{t}')")] for t in ('broker_import_execution_plans','broker_import_review_items','broker_import_dry_runs','transactions')}
rows=conn.execute("SELECT * FROM broker_import_execution_plans WHERE transaction_id IS NOT NULL AND NOT EXISTS(SELECT 1 FROM transactions t WHERE t.transaction_id=broker_import_execution_plans.transaction_id) ORDER BY execution_plan_id").fetchall()

def flatten(obj,prefix=''):
 out={}
 if isinstance(obj,dict):
  for k,v in obj.items(): out.update(flatten(v,f'{prefix}.{k}' if prefix else k))
 elif isinstance(obj,list):
  for i,v in enumerate(obj): out.update(flatten(v,f'{prefix}[{i}]'))
 else: out[prefix]=obj
 return out
results=[]
for row in rows:
 p=dict(row); checks={}
 # Explicit exact cross-field identities.
 checks['plan_source_row_hash_to_transaction_row_hash']=conn.execute('SELECT COUNT(*) FROM transactions WHERE row_hash=?',(p.get('source_row_hash'),)).fetchone()[0]
 review=conn.execute('SELECT * FROM broker_import_review_items WHERE review_item_id=?',(p['review_item_id'],)).fetchone()
 dry=conn.execute('SELECT * FROM broker_import_dry_runs WHERE dry_run_id=?',(p['dry_run_id'],)).fetchone()
 evidence={'plan':p,'review':dict(review) if review else None,'dry_run':dict(dry) if dry else None}
 flattened={}
 for section,obj in evidence.items():
  if obj:
   for k,v in flatten(obj).items(): flattened[f'{section}.{k}']=v
 # Search only exact values into canonical transaction identity columns.
 candidate_fields=[]
 for key,value in flattened.items():
  low=key.lower()
  if value not in (None,'') and any(token in low for token in ('source_id','external','row_hash','source_hash','transaction_id')):
   candidate_fields.append(key)
   for txcol in ('source_id','external_transaction_id','row_hash','transaction_id'):
    checks[f'{key}->{txcol}']=conn.execute(f'SELECT COUNT(*) FROM transactions WHERE {txcol}=?',(value,)).fetchone()[0]
 results.append({'evidence':evidence,'candidate_field_names':candidate_fields,'exact_match_counts':checks})
report={'db':str(DB),'table_columns':cols,'rows':results}
fd=os.open(OUT,os.O_CREAT|os.O_EXCL|os.O_WRONLY,0o600)
with os.fdopen(fd,'w') as f: json.dump(report,f,ensure_ascii=False,indent=2,default=str)
conn.close()
print(json.dumps({'private_report':str(OUT),'mode':oct(OUT.stat().st_mode&0o777),'orphan_count':len(results),'table_columns':cols,'candidate_field_names_per_row':[r['candidate_field_names'] for r in results],'all_exact_identity_matches_zero':all(all(v==0 for v in r['exact_match_counts'].values()) for r in results),'checks_per_row':[len(r['exact_match_counts']) for r in results]},indent=2))