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

# File paths
excel_path = 'Drmohan-HISBANK04-06.xlsx'
his_file = 'DrmohansHIS.xlsx'
momentpay_file = 'Drmomentpay04-06.csv'

# Load data
transaction_details_df = pd.read_excel(his_file)
moments_pay_bank_df = pd.read_csv(momentpay_file)

# Filter HIS for relevant payment modes
transaction_details_df = transaction_details_df[transaction_details_df['PAYMENTMODE'].isin(["Bank", "Bank Transfer"])]

# Ensure numeric and rounded amounts
transaction_details_df['PAYMODEAMOUNT'] = pd.to_numeric(transaction_details_df['PAYMODEAMOUNT'], errors='coerce').round(2)
moments_pay_bank_df['total_amount'] = pd.to_numeric(moments_pay_bank_df['total_amount'], errors='coerce').round(2)

# Merge with selected HIS columns
merged_df = pd.merge(
    transaction_details_df[['CLUSTER', 'BRANCH', 'DOCDATE', 'PATIENTNAME', 'PATIENTNUMBER', 'PAYMODEAMOUNT']],
    moments_pay_bank_df[['total_amount']],
    left_on='PAYMODEAMOUNT',
    right_on='total_amount',
    how='left'
)

# Clean and prepare fields
merged_df['Cluster'] = merged_df['CLUSTER'].astype(str).str.strip()
merged_df['Branch'] = merged_df['BRANCH'].astype(str).str.strip()
merged_df['Date'] = pd.to_datetime(merged_df['DOCDATE']).dt.date
merged_df['patientname'] = merged_df['PATIENTNAME']
merged_df['Patientnumber'] = merged_df['PATIENTNUMBER']
merged_df['MDR'] = merged_df['total_amount'].fillna(0)
merged_df['Difference'] = merged_df['MDR'] - merged_df['PAYMODEAMOUNT']

# Select final columns
final_summary = merged_df[['Cluster', 'Branch', 'Date', 'patientname', 'Patientnumber',
                           'PAYMODEAMOUNT', 'MDR', 'Difference']].copy()

# Sort by cluster and branch for neatness
final_summary = final_summary.sort_values(by=['Cluster', 'Branch'])

# Load workbook and write to "Bank Transfer Reco" sheet starting at B6
wb = load_workbook(excel_path)
ws = wb['Bank Transfer Reco']

# Clear previous content from B6 onwards
for row in ws.iter_rows(min_row=6, min_col=2, max_row=ws.max_row, max_col=2 + len(final_summary.columns)):
    for cell in row:
        cell.value = None

# Write DataFrame to Excel starting from B6
for i, row in enumerate(dataframe_to_rows(final_summary, index=False, header=True), start=6):
    for j, val in enumerate(row, start=2):  # Column B = 2
        ws.cell(row=i, column=j, value=val)

# Save workbook
wb.save(excel_path)
print("✅ Bank transfer reconciliation updated in Excel.")