import subprocess, re, json, csv, os, sys
from datetime import datetime

MYSQLBINLOG = r"C:\Program Files\MySQL\MySQL Server 8.4\bin\mysqlbinlog.exe"
BINLOG = r"C:\ProgramData\MySQL\MySQL Server 8.4\Data\VMI3392416-bin.000003"
OUTDIR = r"C:\MCSJAgent\proof"
os.makedirs(OUTDIR, exist_ok=True)

SCHEMAS = {
    'utwtrpayments': ['Tran_Date','Random_Seq','Utm_Id','Tran_Type','Bill_Year','Bill_Prd','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Cancel_Rvt_Ref_Num','Cancel_Date','Nsf_Flag','Rvt_Updt_Flag','Rvt_Ref_Num','Ref_Ldgr','Ref_Num','Ref_Seq','Overpay_Ref_Num','Overpay_Ref_Seq','Overpay_Pay_Type','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Postmark_Flag','Pymt_Origin_Id','Postmark_Date'],
    'utswrpayments': ['Tran_Date','Random_Seq','Utm_Id','Tran_Type','Bill_Year','Bill_Prd','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Cancel_Rvt_Ref_Num','Cancel_Date','Nsf_Flag','Rvt_Updt_Flag','Rvt_Ref_Num','Ref_Ldgr','Ref_Num','Ref_Seq','Overpay_Ref_Num','Overpay_Ref_Seq','Overpay_Pay_Type','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Postmark_Flag','Pymt_Origin_Id','Postmark_Date'],
    'utelcpayments': ['Tran_Date','Random_Seq','Utm_Id','Tran_Type','Bill_Year','Bill_Prd','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Cancel_Rvt_Ref_Num','Cancel_Date','Nsf_Flag','Rvt_Updt_Flag','Rvt_Ref_Num','Ref_Ldgr','Ref_Num','Ref_Seq','Overpay_Ref_Num','Overpay_Ref_Seq','Overpay_Pay_Type','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Postmark_Flag','Pymt_Origin_Id','Postmark_Date'],
    'utotrpayments': ['Tran_Date','Random_Seq','Utm_Id','Tran_Type','Bill_Year','Bill_Prd','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Cancel_Rvt_Ref_Num','Cancel_Date','Nsf_Flag','Rvt_Updt_Flag','Rvt_Ref_Num','Ref_Ldgr','Ref_Num','Ref_Seq','Overpay_Ref_Num','Overpay_Ref_Seq','Overpay_Pay_Type','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Postmark_Flag','Pymt_Origin_Id','Postmark_Date'],
    'utproppayments': ['Tran_Date','Random_Seq','Service_Type','Account_Id','Blq_Id','Tran_Type','Bill_Year','Bill_Prd','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Cancel_Rvt_Ref_Num','Cancel_Date','Nsf_Flag','Rvt_Updt_Flag','Rvt_Ref_Num','Ref_Ldgr','Ref_Num','Ref_Seq','Overpay_Ref_Num','Overpay_Ref_Seq','Overpay_Pay_Type','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Postmark_Flag','Pymt_Origin_Id','Postmark_Date'],
    'pwmiscpayments': ['Mp_Date','Random_Seq','Tran_Type','Pay_Code','Prn_Amt','Int_Amt','Method','Chk_Num','Descript','Comment1','Comment2','Blq_Id','Pay_Rvrsl_Flag','Pay_Rvrsl_Date','Pay_Rvrsl_Ref_Num','Pay_Rvrsl_Ref_Seq','Cancel_Flag','Nsf_Flag','Ref_Num','Ref_Seq','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','Pay_Info','Acct_Type','Acct_Id','User_Entered_Id','Utm_Id','Pymt_Origin_Id'],
    'arpayment': ['Ref_Ldgr','Ref_Num','Ref_Seq','Pymt_Date','Type','Prn_Amt','Int_Amt','Bank_Id','Cust_Id','Method','Chk_Num','Pay_Code','Nsf_Flag','Descript','Stat_Flag','Void_Date','Applied_Amt','Refund_Amt','Payment_Id','Inv_Id','Item_Seq','Purged_Paid_Amt','Batch_Id','User_Update_Id','Time_Entered','Time_Updated','User_Entered_Id','Overpay_Fund_Id','Pymt_Origin_Id','Postmark_Date'],
}
PAY_TABLES = set(SCHEMAS)
insert_re = re.compile(r"^### INSERT INTO `chestervillagenydb`\.`([^`]+)`")
val_re = re.compile(r"^###\s+@(\d+)=(.*?)(?: /\*|$)")
rows=[]
current_table=None
current={}

def clean(v):
    v=v.strip()
    if v.startswith("'") and "'" in v[1:]:
        # strip first and last quote before comment; mysqlbinlog date uses quoted strings
        if v.endswith("'"):
            v=v[1:-1]
        else:
            v=v[1:v.rfind("'")]
    return v

def flush():
    global current_table,current
    if current_table in PAY_TABLES and current:
        cols=SCHEMAS[current_table]
        d={'table': current_table}
        for idx,val in current.items():
            if 1 <= idx <= len(cols): d[cols[idx-1]]=val
            else: d[f'@{idx}']=val
        # only payment-like rows, exclude obvious reversals/cancellations when flag says yes, but keep Tran_Type visible
        rows.append(d)
    current_table=None; current={}

cmd=[MYSQLBINLOG,'--base64-output=DECODE-ROWS','-vv',BINLOG]
proc=subprocess.Popen(cmd, stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True, errors='replace')
for line in proc.stdout:
    m=insert_re.match(line)
    if m:
        flush(); current_table=m.group(1).lower(); continue
    if current_table:
        m=val_re.match(line)
        if m:
            current[int(m.group(1))]=clean(m.group(2))
flush()
err=proc.stderr.read(); rc=proc.wait()

def parse_dt(s):
    if not s or s in ('1000:01:01','0000:00:00'): return datetime.min
    for fmt in ('%Y-%m-%d %H:%M:%S','%Y:%m:%d %H:%M:%S','%Y:%m:%d','%Y-%m-%d'):
        try: return datetime.strptime(s, fmt)
        except Exception: pass
    return datetime.min

for r in rows:
    r['amount_total'] = str(round(float(r.get('Prn_Amt','0') or 0)+float(r.get('Int_Amt','0') or 0),2))
    r['_sort_time'] = parse_dt(r.get('Time_Entered') or r.get('Time_Updated') or r.get('Tran_Date') or r.get('Mp_Date') or r.get('Pymt_Date'))
    r['_pay_date'] = r.get('Tran_Date') or r.get('Mp_Date') or r.get('Pymt_Date') or ''

latest=sorted(rows, key=lambda r: (r['_sort_time'], parse_dt(r.get('_pay_date'))), reverse=True)[:50]
# drop helper dt before writing json
json_rows=[]
for r in latest:
    rr={k:v for k,v in r.items() if not k.startswith('_')}
    json_rows.append(rr)

stamp=datetime.now().strftime('%Y%m%d_%H%M%S')
json_path=os.path.join(OUTDIR, f'latest_payments_binlog_{stamp}.json')
csv_path=os.path.join(OUTDIR, f'latest_payments_binlog_{stamp}.csv')
summary_path=os.path.join(OUTDIR, f'latest_payments_binlog_{stamp}_summary.txt')
with open(json_path,'w',encoding='utf-8') as f: json.dump({'source_binlog': BINLOG, 'row_count_scanned': len(rows), 'latest': json_rows}, f, indent=2)
fields=['table','_pay_date','Time_Entered','Time_Updated','Tran_Type','Type','Pay_Code','Pay_Adj_Code','Bill_Code','Prn_Amt','Int_Amt','amount_total','Method','Chk_Num','Descript','Batch_Id','User_Update_Id','Utm_Id','Account_Id','Blq_Id','Cust_Id','Payment_Id','Ref_Num','Ref_Seq','Pymt_Origin_Id']
with open(csv_path,'w',newline='',encoding='utf-8') as f:
    w=csv.DictWriter(f, fieldnames=fields, extrasaction='ignore'); w.writeheader();
    for r in latest: w.writerow(r)
with open(summary_path,'w',encoding='utf-8') as f:
    f.write(f"source_binlog={BINLOG}\nrow_count_scanned={len(rows)}\nreturn_code={rc}\nstderr={err[:1000]}\n\n")
    for i,r in enumerate(latest[:15],1):
        f.write(f"{i}. table={r.get('table')} pay_date={r.get('_pay_date')} time_entered={r.get('Time_Entered')} amount={r.get('amount_total')} method={r.get('Method')} code={r.get('Pay_Code') or r.get('Pay_Adj_Code')}/{r.get('Bill_Code')} batch={r.get('Batch_Id')} user={r.get('User_Update_Id')} acct={r.get('Utm_Id') or r.get('Account_Id') or r.get('Cust_Id') or r.get('Blq_Id')} ref={r.get('Ref_Num')}-{r.get('Ref_Seq')} desc={r.get('Descript')}\n")

# Incoming-payment view: exclude obvious overpayment-application rows (OVP) and non-positive rows.
incoming=[]
for r in rows:
    code=(r.get('Pay_Adj_Code') or r.get('Pay_Code') or '').strip()
    try:
        amt=float(r.get('amount_total','0') or 0)
    except Exception:
        amt=0
    if amt>0 and code != 'OVP' and r.get('Pay_Rvrsl_Flag','N') != 'Y' and r.get('Cancel_Flag','N') != 'Y':
        incoming.append(r)
incoming_latest=sorted(incoming, key=lambda r: (r['_sort_time'], parse_dt(r.get('_pay_date'))), reverse=True)[:50]
in_csv_path=os.path.join(OUTDIR, f'latest_incoming_payments_binlog_{stamp}.csv')
in_summary_path=os.path.join(OUTDIR, f'latest_incoming_payments_binlog_{stamp}_summary.txt')
with open(in_csv_path,'w',newline='',encoding='utf-8') as f:
    w=csv.DictWriter(f, fieldnames=fields, extrasaction='ignore'); w.writeheader();
    for r in incoming_latest: w.writerow(r)
with open(in_summary_path,'w',encoding='utf-8') as f:
    f.write(f"source_binlog={BINLOG}\nrow_count_scanned={len(rows)}\nincoming_filtered_count={len(incoming)}\nfilter=amount_total>0; Pay_Adj_Code/Pay_Code != OVP; reversal/cancel flags not Y\n\n")
    for i,r in enumerate(incoming_latest[:20],1):
        f.write(f"{i}. table={r.get('table')} pay_date={r.get('_pay_date')} time_entered={r.get('Time_Entered')} amount={r.get('amount_total')} method={r.get('Method')} code={r.get('Pay_Code') or r.get('Pay_Adj_Code')}/{r.get('Bill_Code')} batch={r.get('Batch_Id')} user={r.get('User_Update_Id')} acct={r.get('Utm_Id') or r.get('Account_Id') or r.get('Cust_Id') or r.get('Blq_Id')} ref={r.get('Ref_Num')}-{r.get('Ref_Seq')} desc={r.get('Descript')}\n")
print(json.dumps({'incoming_csv':in_csv_path,'incoming_summary':in_summary_path,'incoming_latest':[ {k:v for k,v in r.items() if not k.startswith('_')} for r in incoming_latest[:10] ]}, indent=2))

print(json.dumps({'row_count_scanned':len(rows),'json':json_path,'csv':csv_path,'summary':summary_path,'latest':json_rows[:10]}, indent=2))