import pandas as pd

# Load files
moments_pay_df = pd.read_csv('Twmomentpay11-06.csv')
hospital_transaction_df = pd.read_excel('Twyesbank11-06.xlsx')

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

moments_pay_df['total_amount'] = moments_pay_df['total_amount'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_df['rrn_no'] = moments_pay_df['rrn_no'].astype(str).replace(r'\.0$', '', regex=True)

# Mapping of fields for matching
field_mapping = {'Txn Amt': 'total_amount', 'RRN No.': 'rrn_no'}

# Drop duplicates to ensure one-to-one mapping
moments_pay_df = moments_pay_df.drop_duplicates(subset=list(field_mapping.values()))
hospital_transaction_df = hospital_transaction_df.drop_duplicates(subset=list(field_mapping.keys()))

# Merge the dataframes to check matching status
merged_df = pd.merge(
    hospital_transaction_df, 
    moments_pay_df[list(field_mapping.values())], 
    left_on=list(field_mapping.keys()), 
    right_on=list(field_mapping.values()), 
    how='left'
)

# Assign 'YES' to matched and 'NO' to unmatched
merged_df['momentpay matched'] = merged_df['rrn_no'].apply(lambda x: 'YES' if pd.notna(x) else 'NO')

# Drop extra columns from the merge
merged_df = merged_df.drop(columns=list(field_mapping.values()), errors='ignore')

# Save back to Excel
output_file = 'Updated_Twyesbank.xlsx'
merged_df.to_excel(output_file, index=False)

print(f"Updated file saved as {output_file} with 'momentpay matched' column.")