import pandas as pd
from openpyxl import load_workbook
from openpyxl.cell.cell import MergedCell

# File paths
his_file = 'DSCstatement.xlsx'
summary_file = 'Drmohan-HISBANK03-06.xlsx'

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

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

# Group by Cluster and sum GROSS_AMT
cluster_total_amounts = (
    transaction_details_df.groupby('CLUSTER')['GROSS_AMT']
    .sum()
    .round(2)
    .reset_index()
    .rename(columns={'GROSS_AMT': 'Total Amount'})
)

# Load summary workbook
wb = load_workbook(summary_file)
ws = wb['Summary'] if 'Summary' in wb.sheetnames else wb.create_sheet('Summary')

# Clear columns A and B from row 33 onward
#for row in ws.iter_rows(min_row=33, max_col=2):
#    for cell in row:
#        cell.value = None

for row in ws.iter_rows(min_row=33, max_col=2):
    for cell in row:
        if not isinstance(cell, MergedCell):
            cell.value = None

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

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

# Save workbook
wb.save(summary_file)
print(f"✅ Cluster total amounts updated in summary: {summary_file}")