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

moments_pay_bank_df = pd.read_excel('DSCstatement.xlsx')
print(moments_pay_bank_df)

transaction_details_df = pd.read_excel('DrmohansHIS.xlsx')
print(transaction_details_df)

transaction_details_df['CCAUTHNO'] = transaction_details_df['CCAUTHNO'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['CHEQUENO'] = transaction_details_df['CHEQUENO'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Amount'] = transaction_details_df['PAYMODEAMOUNT'].astype(float).replace(r'\.0$', '', regex=True).round(2)
transaction_details_df['TransactionId'] = (
    transaction_details_df['CCAUTHNO'].where(transaction_details_df['CCAUTHNO'].str.fullmatch(r'\d{1,12}'))
    .fillna(transaction_details_df['CHEQUENO'].where(transaction_details_df['CHEQUENO'].str.fullmatch(r'\d{1,12}')))
    .replace(r"'", '', regex=True).apply(lambda x: x.zfill(12) if pd.notnull(x) else x)
)

moments_pay_bank_df['transaction_id'] = moments_pay_bank_df['RRN'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['Txn Amt'] = moments_pay_bank_df['GROSS_AMT'].astype(float).replace(r'\.0$', '', regex=True)
print(moments_pay_bank_df['transaction_id'])
print(transaction_details_df['Amount'])
print(moments_pay_bank_df['Txn Amt'])
field_mapping = {'TransactionId': 'transaction_id','Amount':'Txn Amt'}

transaction_details_df = transaction_details_df[transaction_details_df['ENTITY'].isin(["DSC","DSC LLP"])]

upi_transactions_df = transaction_details_df[transaction_details_df['PAYMODECODE'].isin(['EWALLET','CREDITCARD','DEBITCARD'])]

moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['transaction_id','Txn Amt'], keep='first')

upi_transactions_df['TransactionId'] = upi_transactions_df['TransactionId'].str.strip()
moments_pay_bank_df_dedup['transaction_id'] = moments_pay_bank_df_dedup['transaction_id'].str.strip()

upi_transactions_df = upi_transactions_df.drop_duplicates(subset=['TransactionId', 'Amount'], keep='first')
moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['transaction_id', 'Txn Amt'], keep='first')

merged_df = pd.merge(
    upi_transactions_df,
    moments_pay_bank_df_dedup[list(field_mapping.values())],
    left_on=['TransactionId', 'Amount'],
    right_on=['transaction_id', 'Txn Amt'],
    how='left',
    indicator=True
)

matched_records = merged_df[merged_df['_merge'] == 'both'].copy()
matched_records.insert(0, 'S.No.', range(1, len(matched_records) + 1))
matched_records['momentpay matched'] = 'YES'

unmatched_records = merged_df[merged_df['_merge'] != 'both'].copy()
unmatched_records.insert(0, 'S.No.', range(1, len(unmatched_records) + 1))
unmatched_records.loc[:, 'momentpay matched'] = 'NO'

excel_path = 'Drmohan-HISBANK03-06.xlsx'
wb = load_workbook(excel_path)

# Add 'Branch' column for summary (adjust if it's already present or named differently)
upi_transactions_df['Cluster'] = upi_transactions_df['CLUSTER']  # Adjust if needed

# Separate matched and unmatched records from merged_df
matched_records = merged_df[merged_df['_merge'] == 'both'].copy()
matched_records.insert(0, 'S.No.', range(1, len(matched_records) + 1))
matched_records['momentpay matched'] = 'YES'

unmatched_records = merged_df[merged_df['_merge'] != 'both'].copy()
unmatched_records.insert(0, 'S.No.', range(1, len(unmatched_records) + 1))
unmatched_records['momentpay matched'] = 'NO'

# Group by Branch and compute summary for matched
matched_summary = matched_records.groupby('CLUSTER').agg(
    Matched_Count=('Amount', 'count'),
    Matched_Amount=('Amount', 'sum')
)

# Group by Branch and compute summary for unmatched
unmatched_summary = unmatched_records.groupby('CLUSTER').agg(
    Unmatched_Count=('Amount', 'count'),
    Unmatched_Amount=('Amount', 'sum')
)

# Merge summaries on Branch (outer join)
summary = pd.merge(matched_summary, unmatched_summary, left_index=True, right_index=True, how='outer').fillna(0)

# Calculate totals
summary['Total_Count'] = summary['Matched_Count'] + summary['Unmatched_Count']
summary['Total_Amount'] = summary['Matched_Amount'] + summary['Unmatched_Amount']

# Reset index to bring Branch into a column
summary = summary.reset_index()
summary.rename(columns={'Cluster': 'Cluster'}, inplace=True)

# Load the worksheet
summary_ws = wb['Summary']

# Start writing after a blank row
start_row = 73
start_col = 1  # Starting from column B for layout

# Write DataFrame to worksheet
#for r_idx, row in enumerate(dataframe_to_rows(summary, index=False, header=True), start=start_row):
#    for c_idx, value in enumerate(row, start=start_col):
#        summary_ws.cell(row=r_idx, column=c_idx, value=value)

for r_idx, row in enumerate(dataframe_to_rows(summary, index=False, header=True), start=start_row):
    for c_idx, value in enumerate(row, start=start_col):
        cell = summary_ws.cell(row=r_idx, column=c_idx)
        if isinstance(cell, MergedCell):
            continue  # Skip writing to merged cells
        cell.value = value

# Save the workbook
wb.save(excel_path)