import pandas as pd

# Load files
moments_pay_bank_df = pd.read_csv('Srikaramomentpay06-06.csv')
transaction_details_df = pd.read_excel('SrikaraHIS06-06.xlsx')

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

moments_pay_bank_df['uhid'] = moments_pay_bank_df['uhid'].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['Date'] = moments_pay_bank_df['date_added'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['email'] = moments_pay_bank_df['email'].astype(str).replace(r'\.0$', '', regex=True)

# 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 HIS with MomentPay on RRN and amount — LEFT join to keep all HIS records
merged_df = pd.merge(
    transaction_details_df,
    moments_pay_bank_df_dedup[['processing_id', 'transaction_id', 'email'] + list(field_mapping.values())],
    left_on=list(field_mapping.keys()),
    right_on=list(field_mapping.values()),
    how='left'
)

# Mark matches
merged_df['MomentsPay Matched'] = merged_df['processing_id'].notnull().map({True: 'YES', False: 'NO'})

# Drop MomentPay rrn_no and total_amount columns
merged_df = merged_df.drop(columns=list(field_mapping.values()))

# Save to Excel
merged_df.to_excel('SrikaraintegratedHIS.xlsx', index=False)