import pandas as pd
import openpyxl
from openpyxl.styles import PatternFill

# Load BankTransaction.xls
hospital_transaction_df = pd.read_excel('Twyesbank11-06.xlsx')

print(hospital_transaction_df.columns)
# Load momentsPay.csv
moments_pay_df_all = pd.read_csv('Twmomentpay11-06.csv')

# Create copies of the original data frames
hospital_transaction_df = hospital_transaction_df.copy()
moments_pay_df = moments_pay_df_all.copy()

hospital_transaction_df = hospital_transaction_df[hospital_transaction_df['Channel Type'].isin(['BQR', 'POS'])]

moments_pay_df['card_num'] = moments_pay_df['card_num'].astype(str).replace('\.0$', '', regex=True)
moments_pay_df['terminal_id'] = moments_pay_df['terminal_id'].astype(str).replace('\.0$', '', regex=True)
moments_pay_df['total_amount'] = moments_pay_df['total_amount'].astype(str).replace('\.0$', '', regex=True) # amount decimal take consider
moments_pay_df['rrn_no'] = moments_pay_df['rrn_no'].astype(str).replace('\.0$', '', regex=True)
print(moments_pay_df[['card_num','terminal_id','total_amount','rrn_no']])

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

#hospital_transaction_df['INTNL AMT'] = hospital_transaction_df['INTNL AMT'].astype(str).replace('\.0$', '', regex=True)
#hospital_transaction_df['TRANSACTION AMOUNT'] = hospital_transaction_df.apply(lambda row: row['DOMESTIC AMT'] if row['INTNL AMT'] == '0' else row['INTNL AMT'],axis=1)

print(hospital_transaction_df[['Card Number','TID','RRN No.','Txn Amt']])
hospital_transaction_df['Last4_CARDNBR'] = hospital_transaction_df['Card Number'].apply(lambda x: x if '@' in x else x[-4:])
moments_pay_df['Last4_card_num'] = moments_pay_df['card_num'].apply(lambda x: x if '@' in x else x[-4:])

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
# Field mapping dictionary
field_mapping = {
    'Last4_CARDNBR': 'Last4_card_num',
    'TID': 'terminal_id',
    'RRN No.': 'rrn_no',
    'Txn Amt': 'total_amount',
}

# Merge dataframes on the specified fields
merged_df = pd.merge(hospital_transaction_df, moments_pay_df[['processing_id', 'transaction_id', 'email'] + list(field_mapping.values())], left_on=list(field_mapping.keys()), right_on=list(field_mapping.values()), how='left')

# Create a new column 'Matched' based on the conditions
merged_df['MomentsPay Matched'] = merged_df.apply(lambda row: 'YES' if not pd.isnull(row['processing_id']) else 'NO', axis=1)

# Drop unnecessary columns
merged_df = merged_df.dropna(subset=['Card Number'])
merged_df = merged_df.drop(list(field_mapping.values()),axis=1)
print(merged_df)

# Write the modified DataFrame to a new Excel file
merged_df.to_excel('MomentsPayTrustwell_Bankcard_new.xlsx', index=False)