verify_all_orders.py

import pandas as pd
import numpy as np
from app.services.data_service import data_service

def audit_calculations():
    print("Loading Data...")
    data_service.load_data()
    df = data_service.master_df
    
    print(f"\nTotal Records: {len(df)}")
    
    # 1. Sale Order Level Audit
    print("\n--- Sale Order Audit ---")
    sale_orders = df['Sale Order'].unique()
    print(f"Unique Sale Orders: {len(sale_orders)}")
    
    anomalies = []
    total_volume_dedup = 0
    total_volume_sum = 0
    total_policy_gap = 0
    
    for so_id in sale_orders:
        so_df = df[df['Sale Order'] == so_id]
        
        # Calculate Order Qty (Correct: Deduplicated)
        if 'COPS_LINENO' in so_df.columns:
            order_qty_correct = so_df.groupby('COPS_LINENO')['DORQT1'].first().sum()
        else:
            order_qty_correct = so_df['DORQT1'].drop_duplicates().sum()
            
        # Calculate Order Qty (Incorrect: Sum All)
        order_qty_sum = so_df['DORQT1'].sum()
        
        # Reserved & Issued
        reserved = so_df[so_df['is_input'] == True]['RES_QTY'].sum()
        issued = so_df[so_df['is_input'] == True]['ISS_QTY'].sum()
        
        # Policy Gap
        gap = reserved - order_qty_correct
        
        total_volume_dedup += order_qty_correct
        total_volume_sum += order_qty_sum
        total_policy_gap += gap
        
        # Check for massive discrepancies (Order Qty Sum vs Correct > 2x)
        if order_qty_sum > (order_qty_correct * 1.5):
             # Keep track of offenders
             anomalies.append({
                 "id": so_id, 
                 "rows": len(so_df), 
                 "correct": order_qty_correct, 
                 "wrong": order_qty_sum
             })
             
    print(f"Audit Complete.")
    print(f"Total True Volume (Deduplicated): {total_volume_dedup:,.0f}")
    print(f"Total Wrong Volume (Simple Sum): {total_volume_sum:,.0f}")
    print(f"Inflation Factor: {total_volume_sum / total_volume_dedup:.2f}x")
    
    print(f"\nOrders with Inflated Volume (sample 5):")
    for a in anomalies[:5]:
        print(f"  {a['id']}: True={a['correct']:,.0f}, Wrong={a['wrong']:,.0f} (Rows: {a['rows']})")
        
    # 2. Global KPI Audit
    print("\n--- Global KPI Audit ---")
    analytics = data_service.get_enhanced_analytics()
    api_volume = analytics['kpis']['total_volume_m']
    
    print(f"API Reported Volume: {api_volume:,.0f}")
    
    if abs(api_volume - total_volume_dedup) < 1000:
        print("✓ API Volume matches Deduplicated Sum")
    else:
        print(f"✗ API Volume Mismatch! Diff: {api_volume - total_volume_dedup:,.0f}")

    # 3. Global Yield Audit
    # Definition: Total Pack Fresh / Total Issued
    total_pack_fresh = df['pack_fresh'].sum()
    total_issued_global = df[df['is_input']==True]['ISS_QTY'].sum() 
    # Note: ISS_QTY is per PO. Summing ISS_QTY from input rows is correct?
    # Actually 'Actual Gr Opening' column in data_service might be mapped from ISS_QTY.
    # detail_df uses clean names. 
    # Let's check detail_df sums directly from data_service logic
    
    ds_detail = data_service.detail_df
    ds_pack = ds_detail['pack_fresh'].sum()
    ds_issued = ds_detail['Actual Gr Opening'].sum()
    
    calc_yield = (ds_pack / ds_issued * 100)
    api_yield = analytics['kpis']['global_yield_pct']
    
    print(f"Raw Pack Fresh Sum: {ds_pack:,.0f}")
    print(f"Raw Issued Sum: {ds_issued:,.0f}")
    print(f"Calculated Yield: {calc_yield:.2f}%")
    print(f"API Reported Yield: {api_yield}%")
    
    if abs(calc_yield - api_yield) < 0.2:
        print("✓ Global Yield matches")
    else:
        print("✗ Global Yield Mismatch")

if __name__ == "__main__":
    audit_calculations()