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

# Load BankTransaction.xls
bank_transaction_df_all = pd.read_excel('ContinentalPaytm25-05.xlsx')

# Load momentsPay.csv
moments_pay_df_all = pd.read_csv('Continentalmomentpay25-05.csv')

# Define column mapping
column_mapping = {
    #'CARDNBR': ['PAYER VPA','Payer VPA'],
    #'TERMINAL_NO': ['TERMINAL_NO', 'TerminalID', 'EXTERNAL TID','TERMINAL NUMBER'],
    'AUTH_AMOUNT': ['Amount','Settled_Amount','AUTH_AMOUNT', 'TransactionAmount','DOMESTIC AMT'],
    'RRN_NO': ['Order ID','Order_ID'],
}

# 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['transaction_id'] = moments_pay_df['transaction_id'].astype(str).replace('\.0$', '', regex=True).str.strip("'")
print(moments_pay_df['total_amount'])
print(moments_pay_df['transaction_id'])

# 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_mapped['CARDNBR'] = bank_transaction_df_mapped['CARDNBR'].astype(str).replace('\.0$', '', regex=True)
#bank_transaction_df_mapped['TERMINAL_NO'] = bank_transaction_df_mapped['TERMINAL_NO'].astype(str).replace('\.0$', '', regex=True)
bank_transaction_df_mapped['AUTH_AMOUNT'] = bank_transaction_df_mapped['AUTH_AMOUNT'].astype(str).replace('\.0$', '', regex=True)
bank_transaction_df_mapped['RRN_NO'] = bank_transaction_df_mapped['RRN_NO'].astype(str).replace('\.0$', '', regex=True).str.strip("'")
print(bank_transaction_df_mapped['AUTH_AMOUNT'])
print(bank_transaction_df_mapped['RRN_NO'])

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {
   # 'CARDNBR': 'card_num',
   # 'TERMINAL_NO': 'terminal_id',
   'AUTH_AMOUNT':'total_amount',
   'RRN_NO' : 'transaction_id',
}

# Merge dataframes on the specified fields
#merged_df = pd.merge(bank_transaction_df_mapped[list(field_mapping.values())],moments_pay_df, left_on=list(field_mapping.keys()), right_on=list(field_mapping.values()), how='left')
merged_df = pd.merge(
    bank_transaction_df_mapped,
    moments_pay_df[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.drop(list(field_mapping.values()),axis=1)
print(merged_df)

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