import hashlib, json, os
from pathlib import Path
from jarvis_finance.storage.database import connect

DB='/home/agent/jarvis_runtime/finance-system/data/finance.sqlite3'
OUT=Path('/home/agent/jarvis_runtime/finance-system/quarantine/sprint22_raiffeisen_cash_inventory_private.json')

def opaque(value):
    return hashlib.sha256(('s22-inventory|'+str(value)).encode()).hexdigest()

c=connect(DB)
accounts=[]
rows=c.execute("""SELECT a.*,p.name platform_name,b.budget_account_id,b.name budget_name
FROM accounts a JOIN platforms p ON p.platform_id=a.platform_id
LEFT JOIN budget_accounts b ON b.linked_account_id=a.account_id
WHERE lower(p.name) LIKE '%raiffeisen%' OR lower(a.account_name) LIKE '%raiffeisen%'
ORDER BY a.account_name,a.account_id""").fetchall()
all_account_sources=[str(r['account_source'] or '') for r in c.execute("SELECT DISTINCT account_source FROM budget_transaction_candidates WHERE source_type='raiffeisen_bank'")]
for row in rows:
    mappings=[dict(x) for x in c.execute("SELECT * FROM household_account_source_mappings WHERE canonical_account_id=? OR budget_account_id=? ORDER BY created_at,mapping_id",(row['account_id'],row['budget_account_id'])).fetchall()]
    snapshots=[dict(x) for x in c.execute("SELECT snapshot_id,snapshot_type,balance_date,amount_chf,source,note,created_at,audit_id FROM cash_account_snapshots WHERE account_id=? ORDER BY balance_date,created_at,snapshot_id",(row['account_id'],)).fetchall()]
    matched_sources=[]
    for source in all_account_sources:
        if any(source and source in str(v or '') for v in (row['account_name'],row['notes'],row['budget_name'])) or any(source and source in str(m.get('reference_hint') or '') for m in mappings):
            matched_sources.append(source)
    source_stats=[]
    for source in matched_sources:
        s=c.execute("""SELECT COUNT(*) n,MIN(transaction_date) d0,MAX(transaction_date) d1,
          SUM(CASE WHEN status='confirmed' THEN 1 ELSE 0 END) confirmed,
          SUM(CASE WHEN requires_review=1 THEN 1 ELSE 0 END) review
          FROM budget_transaction_candidates WHERE source_type='raiffeisen_bank' AND account_source=?""",(source,)).fetchone()
        source_stats.append({'account_source':source,'count':s['n'],'from':s['d0'],'to':s['d1'],'confirmed':s['confirmed'],'review':s['review']})
    accounts.append({'account_id':row['account_id'],'account_id_opaque':opaque(row['account_id']),'account_name':row['account_name'],'account_type':row['account_type'],'currency':row['currency'],'balance_mode':row['balance_mode'],'is_active':row['is_active'],'notes':row['notes'],'budget_account_id':row['budget_account_id'],'budget_name':row['budget_name'],'mappings':mappings,'snapshots':snapshots,'matched_source_stats':source_stats})
report={'schema':c.execute('select max(version) from schema_migrations').fetchone()[0], 'accounts':accounts,
 'raiffeisen_candidate_sources':[{'account_source':s,'opaque':opaque(s)} for s in all_account_sources],
 'generic_candidate_stats':[dict(r) for r in c.execute("""SELECT account_source,COUNT(*) n,MIN(transaction_date) d0,MAX(transaction_date) d1,
 SUM(CASE WHEN status='confirmed' THEN 1 ELSE 0 END) confirmed,SUM(CASE WHEN requires_review=1 THEN 1 ELSE 0 END) review
 FROM budget_transaction_candidates WHERE source_type='raiffeisen_bank' GROUP BY account_source ORDER BY account_source""").fetchall()]}
OUT.write_text(json.dumps(report,ensure_ascii=False,indent=2,default=str),encoding='utf-8');os.chmod(OUT,0o600)
c.close()
# Privacy-safe summary only.
summary=[]
for a in accounts:
    suffix='5632' if '5632' in str(a['account_name']) else '5031' if '5031' in str(a['account_name']) else 'generic'
    latest=max(a['snapshots'],key=lambda x:(str(x['balance_date']),str(x['created_at']),str(x['snapshot_id']))) if a['snapshots'] else None
    summary.append({'account':suffix,'active':bool(a['is_active']),'mapping_count':len(a['mappings']),'snapshot_count':len(a['snapshots']),'latest_snapshot':({'type':latest['snapshot_type'],'date':latest['balance_date'],'amount_chf':latest['amount_chf']} if latest else None),'matched_source_groups':len(a['matched_source_stats'])})
print(json.dumps({'schema':report['schema'],'accounts':summary,'candidate_source_group_count':len(all_account_sources),'private_report_mode':'0600'},ensure_ascii=False))