from __future__ import annotations
import collections, hashlib, json, math, os, re, sqlite3, statistics, unicodedata
from datetime import date
from pathlib import Path

SRC=Path('/home/agent/jarvis_runtime/finance-system/sprint16.1-uat/sprint16.1-real-preview-v2-private.json')
DB=Path('/home/agent/jarvis_runtime/finance-system/data/finance.sqlite3')
OUT=Path('/home/agent/jarvis_runtime/finance-system/sprint16.2-analysis/open-rows-root-cause-private.json')
CAUSES=(
 'merchant_clear_rule_missing','merchant_normalization_insufficient','bank_envelope_counterparty_not_separated',
 'clear_income_pattern','refund_or_reversal','stable_recurring_payment','possible_internal_transfer',
 'generic_payment_provider','marketplace_or_multi_retailer','genuine_business_ambiguity','missing_source_details',
)

def norm(v):
 s=unicodedata.normalize('NFKD',str(v or '').casefold());s=''.join(c for c in s if not unicodedata.combining(c));s=re.sub(r'[^\w&+]+',' ',s);return ' '.join(s.split())
def stable(v):
 s=norm(v);s=re.sub(r'\b(?:terminal|term|tid|txn|trx|transaktion|beleg|receipt|ref|filiale|branch|store)\s*(?:nr|no|id)?\s*[a-z0-9-]{2,}\b',' ',s);s=re.sub(r'\b\d{2,}\b',' ',s);s=re.sub(r'\b(?:ch|schweiz|suisse|switzerland|de|fr|it|com)\b',' ',s);return ' '.join(s.split())
def has(s,patterns): return any(re.search(p,s) for p in patterns)
def cadence(rows):
 if len(rows)<3:return False
 dates=sorted({date.fromisoformat(str(r['transaction_date'])) for r in rows if r.get('transaction_date')})
 if len(dates)<3:return False
 gaps=[(b-a).days for a,b in zip(dates,dates[1:])]
 rhythmic=sum(any(abs(g-x)<=4 for x in (7,14,28,30,31,90,365)) for g in gaps)>=max(2,math.ceil(len(gaps)*.6))
 vals=[abs(float(r.get('signed_amount') or 0)) for r in rows if float(r.get('signed_amount') or 0)!=0]
 stable_amount=len(vals)>=3 and (statistics.pstdev(vals)/(statistics.mean(vals) or 1))<=.08
 return rhythmic and stable_amount

def category_hint(s,semantics,active):
 rules=[
  (r'\b(?:rueckerstattung|ruckerstattung|refund|storno|reversal|gutschrift)\b','Rückerstattungen'),
  (r'\b(?:strom|elektrizitat|energie|wasserwerk|gasversorgung)\b','Wohnen'),
  (r'\b(?:telefon|mobile|internet|telecom)\b','Elektronische Medien'),
  (r'\b(?:schule|schul|kita|kindergarten|spielgruppe)\b','Kinder/Familie'),
  (r'\b(?:versicherung|assurance)\b','Versicherungen Wohnen'),
  (r'\b(?:steueramt|steuerverwaltung|bundessteuer)\b','Steuern'),
  (r'\b(?:bahn|bus|ticket|flug|hotel|camping)\b','Ferien/Reisen'),
  (r'\b(?:apothek|pharma|arzt|spital|klinik|physio|zahnarzt)\b','Gesundheit'),
  (r'\b(?:tierarzt|tierklinik|tierbedarf|petshop)\b','Haustiere'),
 ]
 for pat,name in rules:
  if re.search(pat,s) and name in active.get(semantics,set()): return name,'positive_keyword'
 return None,None

def main():
 d=json.loads(SRC.read_text()); rows=[r for r in d['rows'] if r.get('user_state')=='decision_needed']
 assert len(rows)==439, len(rows)
 by_exact=collections.defaultdict(list);by_stable=collections.defaultdict(list)
 for r in rows:
  raw=r.get('merchant') or r.get('description') or ''
  by_exact[(r.get('source_type'),norm(raw),r.get('transaction_semantics'))].append(r)
  by_stable[(r.get('source_type'),stable(raw),r.get('transaction_semantics'))].append(r)
 conn=sqlite3.connect(f'file:{DB}?mode=ro',uri=True);conn.row_factory=sqlite3.Row
 active=collections.defaultdict(set)
 for x in conn.execute("SELECT name,category_type FROM budget_categories WHERE is_active=1"):active[str(x['category_type'])].add(str(x['name']))
 conn.close()
 items=[];groups=collections.defaultdict(list)
 for r in rows:
  raw=str(r.get('merchant') or r.get('description') or ''); n=norm(raw); st=stable(raw); sem=str(r.get('transaction_semantics') or '')
  exact=by_exact[(r.get('source_type'),n,sem)]; strows=by_stable[(r.get('source_type'),st,sem)]
  provider=has(n,(r'\bpaypal\b',r'\btwint\b',r'\bstripe\b',r'\bsumup\b',r'\bworldline\b'))
  market=has(n,(r'\bamazon\b',r'\bgalaxus\b',r'\bdigitec\b',r'\bmanor\b',r'\baliexpress\b',r'\bricardo\b',r'\bikea\b',r'\bjumbo\b'))
  transfer=bool(r.get('unmatched_transfer_candidate')) or has(n,(r'\beigen(?:es|e|er)? konto\b',r'\binterner transfer\b',r'\bumbuchung\b'))
  refund=has(n,(r'\brueckerstattung\b',r'\bruckerstattung\b',r'\brefund\b',r'\bstorno\b',r'\breversal\b'))
  clear_income=sem=='income' and has(n,(r'\blohn\b',r'\bgehalt\b',r'\bsalary\b',r'\bzins\b',r'\binterest\b',r'\bdividende\b'))
  envelope=str(r.get('source_type')) in {'akb_bank','raiffeisen_bank'} and has(norm(r.get('description')),(r'\bbelastung e banking\b',r'\bzahlungseingang\b',r'\bursprunglicher auftraggeber\b',r'\bmitteilung\b',r'\bref nr\b'))
  missing=(not n or len(n)<3 or n in {'zahlung','belastung','gutschrift','e banking','kartenzahlung','unbekannt','unknown'})
  if transfer:c='possible_internal_transfer'
  elif refund:c='refund_or_reversal'
  elif provider:c='generic_payment_provider'
  elif market:c='marketplace_or_multi_retailer'
  elif clear_income:c='clear_income_pattern'
  elif missing:c='missing_source_details'
  elif envelope:c='bank_envelope_counterparty_not_separated'
  elif st and len(strows)>=2 and len({norm(x.get('merchant') or x.get('description')) for x in strows})>1:c='merchant_normalization_insufficient'
  elif cadence(exact):c='stable_recurring_payment'
  elif len(exact)>=2:c='merchant_clear_rule_missing'
  else:c='genuine_business_ambiguity'
  assert c in CAUSES
  cat,ev=category_hint(n,sem,active)
  income_kind=(
   'possible_internal_transfer' if sem=='income' and transfer else
   'salary' if sem=='income' and has(n,(r'\blohn\b',r'\bgehalt\b',r'\bsalary\b',r'\bpayroll\b')) else
   'interest' if sem=='income' and has(n,(r'\bzins\b',r'\binterest\b')) else
   'refund' if sem=='income' and refund else
   'payment_incoming' if sem=='income' else None
  )
  item={'row_token':r['row_token'],'source_type':r.get('source_type'),'primary_cause':c,'transaction_semantics':sem,'income_kind':income_kind,'decision_unit_count':len(exact),'private_merchant_family':st or n,'proposed_existing_category':cat,'category_evidence':ev,'date':r.get('transaction_date')}
  items.append(item);groups[(c,r.get('source_type'),st or n,sem,cat)].append(item)
 cause_counts=collections.Counter(x['primary_cause'] for x in items)
 source_counts=collections.Counter(x['source_type'] for x in items)
 rule_causes={'merchant_clear_rule_missing','merchant_normalization_insufficient','bank_envelope_counterparty_not_separated','clear_income_pattern','refund_or_reversal','stable_recurring_payment'}
 risks={
  'merchant_clear_rule_missing':'merchant may have multiple business contexts','merchant_normalization_insufficient':'normalization may merge distinct counterparties','bank_envelope_counterparty_not_separated':'transport text may hide the true counterparty','clear_income_pattern':'incoming own-account transfer must not become income','refund_or_reversal':'must reduce original category when provable, otherwise review','stable_recurring_payment':'rhythm alone does not establish category',
 }
 guards={
  'merchant_clear_rule_missing':['same semantics','same source/account role','negative counterexamples'],
  'merchant_normalization_insufficient':['preserve domain words','require unique normalized family','negative near-name case'],
  'bank_envelope_counterparty_not_separated':['strip only explicit envelope markers','preserve counterparty and semantic words'],
  'clear_income_pattern':['exclude own-account/card-settlement markers','require income-compatible active category'],
  'refund_or_reversal':['exclude ordinary positive transfers','link original category only when unique'],
  'stable_recurring_payment':['require >=3 observations','stable cadence and amount band','no provider/marketplace families'],
 }
 rule_groups=[]
 for (c,src,fam,sem,cat),members in groups.items():
  if c not in rule_causes or not cat: continue
  rule_groups.append({'primary_cause':c,'source_type':src,'private_merchant_family':fam,'count':len(members),'proposed_existing_category':cat,'evidence':members[0]['category_evidence'],'possible_misclassification':risks[c],'negative_guardrails':guards[c],'expected_coverage_improvement_rows':len(members)})
 income_rows=[x for x in items if x['transaction_semantics']=='income']
 income_singletons=[x for x in income_rows if x['decision_unit_count']==1]
 income_analysis={'total_open_rows':len(income_rows),'clustered_rows':len(income_rows)-len(income_singletons),'singleton_rows':len(income_singletons),'singleton_expected_count_verified':len(income_singletons)==51,'singleton_kinds':dict(sorted(collections.Counter(x['income_kind'] for x in income_singletons).items())),'all_income_kinds':dict(sorted(collections.Counter(x['income_kind'] for x in income_rows).items()))}
 report={'report_version':'sprint16.2_open_rows_root_cause_v1','source_preview_sha256':hashlib.sha256(SRC.read_bytes()).hexdigest(),'row_count':len(items),'one_primary_cause_per_row':len(items)==len({x['row_token'] for x in items})==439,'cause_counts':dict(sorted(cause_counts.items())),'source_counts':dict(sorted(source_counts.items())),'income_open_count':len(income_rows),'income_analysis':income_analysis,'expense_open_count':sum(x['transaction_semantics']=='expense' for x in items),'rule_solvable_groups':sorted(rule_groups,key=lambda x:(-x['count'],x['primary_cause'],x['source_type'])),'rule_solvable_coverage_rows':sum(x['count'] for x in rule_groups),'rows':items}
 OUT.parent.mkdir(parents=True,exist_ok=True);tmp=OUT.with_suffix('.tmp');tmp.write_text(json.dumps(report,ensure_ascii=False,indent=2));os.chmod(tmp,0o600);os.replace(tmp,OUT);os.chmod(OUT,0o600)
 print(json.dumps({'row_count':report['row_count'],'one_primary_cause_per_row':report['one_primary_cause_per_row'],'cause_counts':report['cause_counts'],'source_counts':report['source_counts'],'income_open_count':report['income_open_count'],'expense_open_count':report['expense_open_count'],'rule_solvable_group_count':len(rule_groups),'rule_solvable_coverage_rows':report['rule_solvable_coverage_rows'],'output':str(OUT),'mode':oct(OUT.stat().st_mode&0o777)},indent=2))
if __name__=='__main__':main()