# recon_clearing_grouped.py

import pandas as pd

def reconcile_clearing_account(input_file, output_file):
    """
    Reads an Oracle cash book clearing account file,
    groups by Invoice/Payment ID (and other identifiers),
    identifies unreconciled transactions (DR ≠ CR),
    flags exceptions (DR only or CR only),
    and exports them to a new Excel file.
    """

    # Load the Excel file
    df = pd.read_excel(input_file)

    # Ensure required columns exist
    required_cols = [
        "Transaction date", "Invoice/Payment ID", "Customer Name",
        "Invoice/Payment Description", "Amount Debit", "Amount Credit",
        "Category", "Created By"
    ]

    for col in required_cols:
        if col not in df.columns:
            raise ValueError(f"Missing column in input file: {col}")

    # Replace NaN with 0 for DR and CR
    df["Amount Debit"] = df["Amount Debit"].fillna(0)
    df["Amount Credit"] = df["Amount Credit"].fillna(0)

    # Group by identifiers
    grouped = df.groupby(
        ["Invoice/Payment ID", "Customer Name", "Invoice/Payment Description"],
        as_index=False
    ).agg({
        "Amount Debit": "sum",
        "Amount Credit": "sum",
        "Transaction date": "min",  # earliest transaction date
        "Category": "first",
        "Created By": "first"
    })

    # Calculate balance
    grouped["Balance"] = grouped["Amount Debit"] - grouped["Amount Credit"]

    # Flag exceptions
    def flag_exception(row):
        if row["Amount Debit"] > 0 and row["Amount Credit"] == 0:
            return "DR only – Missing CR"
        elif row["Amount Credit"] > 0 and row["Amount Debit"] == 0:
            return "CR only – Missing DR"
        elif row["Balance"] != 0:
            return "Unreconciled (DR ≠ CR)"
        else:
            return "Reconciled"

    grouped["Exception Flag"] = grouped.apply(flag_exception, axis=1)

    # Filter unreconciled/exception cases
    exceptions = grouped[grouped["Exception Flag"] != "Reconciled"]

    # Save to Excel
    exceptions.to_excel(output_file, index=False)

    print(f"✅ Reconciliation complete! {len(exceptions)} exception/unreconciled groups exported to {output_file}")


if __name__ == "__main__":
    # Example usage
    input_file = "cashbook_clearing.xlsx"   # Your input Excel file
    output_file = "unreconciled_transactions.xlsx"  # Output file

    reconcile_clearing_account(input_file, output_file)