import pandas as pd
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell
from openpyxl.utils.dataframe import dataframe_to_rows

# File paths
his_file = 'DrmohansRazorpayDSC.xlsx'
summary_file = 'Drmohan-HISBANK04-06.xlsx'

# Load all transactions
transaction_details_df = pd.read_excel(his_file)

# Convert AMOUNT to numeric
transaction_details_df['AMOUNT'] = pd.to_numeric(transaction_details_df['AMOUNT'], errors='coerce')

# Separate excess rows (where CLUSTER is empty)
excess_df = transaction_details_df[transaction_details_df['CLUSTER'].isna()]

# Calculate total excess amount
excess_amount = excess_df['AMOUNT'].sum()

# Group by non-empty CLUSTER and sum AMOUNT
cluster_total_amounts = (
    transaction_details_df.dropna(subset=['CLUSTER'])  # Exclude empty clusters from grouping
    .groupby('CLUSTER')['AMOUNT']
    .sum()
    .round(2)
    .reset_index()
    .rename(columns={'AMOUNT': 'Total Amount'})
)

# Load summary workbook
wb = load_workbook(summary_file)

# Access or create Summary sheet
ws = wb['Summary'] if 'Summary' in wb.sheetnames else wb.create_sheet('Summary')

# Clear columns A and B from row 21 onward (skip merged cells)
for row in ws.iter_rows(min_row=34, max_col=2):
    for cell in row:
        if isinstance(cell, MergedCell):
            continue
        cell.value = None

# Write headers
ws['A33'] = 'CLUSTER'
ws['E33'] = 'Total Amount'

# Write CLUSTER values and totals
start_row = 34
for i, row in cluster_total_amounts.iterrows():
    ws[f'A{start_row + i}'] = row['CLUSTER']
    ws[f'E{start_row + i}'] = row['Total Amount']

# Write Excess total in Summary sheet
#ws['D28'] = 'Excess'
ws['E41'] = round(excess_amount, 2)

# Add or replace Excess sheet
if 'Excess-Razorpay' in wb.sheetnames:
    del wb['Excess-Razorpay']
excess_ws = wb.create_sheet('Excess-Razorpay')

# Write excess_df to the Excess sheet
for r_idx, row in enumerate(dataframe_to_rows(excess_df, index=False, header=True), start=1):
    for c_idx, value in enumerate(row, start=1):
        excess_ws.cell(row=r_idx, column=c_idx, value=value)

# Save workbook
wb.save(summary_file)
print(f"✅ Summary and 'Excess' sheet updated in: {summary_file}")