import pandas as pd
from openpyxl import load_workbook

def process_aging_report(file_path, output_path):
    # Read the input file
    df = pd.read_excel(file_path)
    
    # Debug: Print column names
    print("Columns in file:", df.columns)
    
    # Ensure required columns exist
    required_columns = ['Txn Date', 'PAID Date', 'momentpay matched', 'Txn Amt']
    if not all(col in df.columns for col in required_columns):
        print("Error: One or more required columns are missing")
        return
    
    # Filter only unmatched records ("momentpay matched" == "NO")
    df = df[df['momentpay matched'] == 'NO']
    print(f"Processing {len(df)} unmatched records.")

    # Convert dates
    df['transaction_date'] = pd.to_datetime(df['Txn Date'], errors='coerce')
    df['settlement_date'] = pd.to_datetime(df['PAID Date'], errors='coerce')

    # Drop invalid date rows
    df.dropna(subset=['transaction_date', 'settlement_date'], inplace=True)

    # Calculate days to settle
    df['days_to_settle'] = (df['settlement_date'] - df['transaction_date']).dt.days

    # Define aging categories
    def categorize_settlement_days(days):
        if days == 1:
            return '1 Day'
        elif days == 2:
            return '2 Days'
        elif days == 3:
            return '3 Days'
        else:
            return 'More than 3 Days'

    df['settlement_category'] = df['days_to_settle'].apply(categorize_settlement_days)

    # Convert 'Txn Amt' to numeric
    df['Txn Amt'] = pd.to_numeric(df['Txn Amt'], errors='coerce')

    # Calculate count and total amount for each category
    summary_table = df.groupby('settlement_category').agg(
        total_count=('settlement_category', 'count'),
        total_amount=('Txn Amt', 'sum')
    ).reset_index()

    # Ensure all categories exist
    categories = ['1 Day', '2 Days', '3 Days', 'More than 3 Days']
    summary_table = summary_table.set_index('settlement_category').reindex(categories, fill_value=0).reset_index()

    # Load existing Excel file
    book = load_workbook(output_path)
    sheet = book['Summary']

    # Define row mapping
    row_mapping = {'1 Day': 26, '2 Days': 27, '3 Days': 28, 'More than 3 Days': 29}

    for index, row in summary_table.iterrows():
        row_num = row_mapping[row['settlement_category']]
        sheet[f'B{row_num}'] = row['total_count']  # Update count
        sheet[f'C{row_num}'] = row['total_amount']  # Update total amount

    book.save(output_path)
    print(f"Aging report updated in {output_path} (only unmatched records considered).")

# Example usage
file_path = "Updated_Twyesbank.xlsx"
output_path = "TRUSTWELL-HISBANK11-06.xlsx"
process_aging_report(file_path, output_path)