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

# Load BankTransaction.xls
bank_transaction_df_all = pd.read_excel('Srikarabankfile06-06.xlsx')

# Load momentsPay.csv
moments_pay_df_all = pd.read_csv('Srikaramomentpay06-06.csv')

# Define column mapping
#column_mapping = {
#    'CARDNBR': ['PAYER VPA','Payer VPA','CARD NUMBER'],
    #'TERMINAL_NO': ['TERMINAL_NO', 'TerminalID', 'EXTERNAL TID','TERMINAL NUMBER','TID'],
#    'AUTH_AMOUNT': ['Transaction Amount','AUTH_AMOUNT', 'TransactionAmount', 'INTNL AMT', 'Amount','DOMESTIC AMT','AMOUNT'],
#    'RRN_NO': ['Txn ref no.(RRN)','AUTH CODE','APPROV CODE'],
#}

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

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['approval_code'] = moments_pay_df['approval_code'].astype(str).replace('\.0$', '', regex=True)
moments_pay_df['approval_code'] = moments_pay_df['approval_code'].str.zfill(6)

# Map columns based on the configuration
#bank_transaction_df_mapped = bank_transaction_df.copy()
#for expected_col, possible_cols in column_mapping.items():
#    actual_col = next((col for col in possible_cols if col in bank_transaction_df.columns), None)
#    if actual_col:
#        bank_transaction_df_mapped[expected_col] = bank_transaction_df[actual_col]
#        print(bank_transaction_df_mapped[expected_col])

# Convert relevant columns to a common data type (string)
bank_transaction_df['CARD NUMBER'] = bank_transaction_df['CARD_NO'].astype(str).replace('\.0$', '', regex=True)
#bank_transaction_df['INTNL AMT'] = bank_transaction_df['INTNL AMT'].astype(str).replace('\.0$', '', regex=True)
#bank_transaction_df['DOMESTIC AMT'] = bank_transaction_df['DOMESTIC AMT'].astype(str).replace('\.0$', '', regex=True)
bank_transaction_df['APPROV CODE'] = bank_transaction_df['APPROVE_CODE'].astype(str).replace('\.0$', '', regex=True).replace(r"'",'', regex=True).str.zfill(6)
#bank_transaction_df['TRANSACTION AMOUNT'] = bank_transaction_df.apply(lambda row: row['DOMESTIC AMT'] if row['INTNL AMT'] == '0' else row['INTNL AMT'],axis=1)
bank_transaction_df['TRANSACTION AMOUNT'] = bank_transaction_df['GROSS_AMT'].astype(str).replace('\.0$', '', regex=True)

bank_transaction_df['Last4_CARDNBR'] = bank_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:])

print(bank_transaction_df['TRANSACTION AMOUNT'])
print(moments_pay_df['total_amount'])

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id
field_mapping = {
    'Last4_CARDNBR': 'Last4_card_num',
    'TRANSACTION AMOUNT': 'total_amount',
    'APPROV CODE': 'approval_code',
}

#print(moments_pay_df[['processing_id', 'transaction_id'] + list(field_mapping.values())])
#print(bank_transaction_df_mapped[['CARDNBR','TERMINAL_NO','AUTH_AMOUNT','RRN_NO']])

# Merge dataframes on the specified fields
merged_df = pd.merge(bank_transaction_df,moments_pay_df[['uhid','transaction_id'] + 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['transaction_id']) else 'NO', axis=1)

# Drop unnecessary columns
merged_df = merged_df.dropna(subset=['Last4_CARDNBR'])
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('MomentsPaySrikara_Bank_new.xlsx', index=False)