import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import range_boundaries

# Load MomentsPay_Bank_new.xlsx
moments_pay_bank_df = pd.read_csv('Continentalmomentpay25-05.csv')

# Load Transaction details as on 14.12.2023
transaction_details_df = pd.read_excel('ContinentalintegratedHIS.xlsx')

# Convert relevant columns to a common data type (string)
transaction_details_df['Umr No'] = transaction_details_df['Cheque Number/UTR No'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Amount'] = transaction_details_df['Pay Mode Amount'].astype(str).replace(r'\.0$', '', regex=True)

moments_pay_bank_df['uhid'] = moments_pay_bank_df['rrn_no'].astype(str).replace(r'\.0$', '', regex=True).replace(r"UHID", '', regex=True) 
moments_pay_bank_df['total_amount'] = moments_pay_bank_df['total_amount'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['email'] = moments_pay_bank_df['email'].astype(str).replace(r'\.0$', '', regex=True)

transaction_details_df = transaction_details_df[transaction_details_df['MomentsPay Matched']=="YES"]

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {'Umr No': 'uhid','Amount': 'total_amount',}

moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['uhid','total_amount'], keep='first')

# Merge dataframes on the specified fields
merged_df = pd.merge(transaction_details_df, moments_pay_bank_df_dedup[list(field_mapping.values())],
                     left_on=list(field_mapping.keys()),
                     right_on=list(field_mapping.values()),
                     how='left')
unmerged_df = pd.merge(transaction_details_df, moments_pay_bank_df_dedup[list(field_mapping.values())],
                       left_on=list(field_mapping.keys()),
                       right_on=list(field_mapping.values()),
                       how='outer', indicator=True)

equal_records = merged_df[~merged_df['uhid'].isnull()]
#equal_records.insert(0, 'S.No.', range(1, len(equal_records) + 1))
equal_records['momentpay matched'] = 'YES'
equal_records['txn_identifier'] = equal_records.apply(lambda row: moments_pay_bank_df[
    (moments_pay_bank_df['uhid'] == row['Umr No']) &
    (moments_pay_bank_df['total_amount'] == row['Amount']) ]
    .reset_index(drop=True)['email'].values[0], axis=1)
print(equal_records)

unequal_records = unmerged_df[unmerged_df['_merge'] == 'left_only']
#unequal_records.insert(0, 'S.No.', range(1, len(unequal_records) + 1))
unequal_records['momentpay matched'] = 'NO'
print(unequal_records)
# Function to create or overwrite an Excel sheet
def create_or_overwrite_sheet(sheet_name, data):
    with pd.ExcelWriter('Continental-HISBANK25-05.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
        data.to_excel(writer, sheet_name=sheet_name, index=False)

# Generate sheet names
sheet_name_matched = 'HIS-Matched'
sheet_name_unmatched = 'HIS-Unmatched'
create_or_overwrite_sheet(sheet_name_matched, equal_records)
create_or_overwrite_sheet(sheet_name_unmatched, unequal_records)

wb  = load_workbook('Continental-HISBANK25-05.xlsx')
Summary_Sheet = wb['Summary']

transaction_date = transaction_details_df['Service Reciept Date'].mode()[0]
print(transaction_date)

# Update summary sheet with total transaction details
num_rows = len(transaction_details_df)
Summary_Sheet['E4'] = num_rows

total_amount = transaction_details_df['Amount'].astype(float).sum()
Summary_Sheet['F4'] = f"₹{total_amount:,.2f}"

# Update summary sheet with matched transaction details
HIS_Matched_df = pd.read_excel('Continental-HISBANK25-05.xlsx', sheet_name='HIS-Matched')
num_rows = len(HIS_Matched_df)
Summary_Sheet['G4'] = num_rows

total_amount = HIS_Matched_df['Amount'].astype(float).sum()
Summary_Sheet['H4'] = f"₹{total_amount:,.2f}"

# Update summary sheet with unmatched transaction details
HIS_UnMatched_df = pd.read_excel('Continental-HISBANK25-05.xlsx', sheet_name='HIS-Unmatched')
num_rows = len(HIS_UnMatched_df)
Summary_Sheet['I4'] = num_rows

HIS_UnMatched_df['Amount'] = pd.to_numeric(HIS_UnMatched_df['Amount'], errors='coerce')
total_amount = HIS_UnMatched_df['Amount'].sum()
#total_amount = HIS_UnMatched_df['Amount'].astype(float).sum()
Summary_Sheet['J4'] = f"₹{total_amount:,.2f}"

# Define the color for the header
header_fill = PatternFill(fgColor='1274bd', fill_type='solid')

for sheet_name in ['HIS-Matched', 'HIS-Unmatched']:
    worksheet = wb[sheet_name]

    # Apply style to the header row
    for row in worksheet.iter_rows(min_row=1, max_row=1):
        for cell in row:
            cell.fill = header_fill

# Save the workbook
wb.save('Continental-HISBANK25-05.xlsx')