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

# Load BankTransaction.xls
bank_transaction_df_all = pd.read_excel('23845-10072024.xlsx')

# Load momentsPay.csv
moments_pay_df_all = pd.read_csv('hdfc09.csv')

# Define column mapping
column_mapping = {
    'CARDNBR': ['CARDNBR', 'CardNumber', 'Card_Num', 'CARD NUMBER'],
    'TERMINAL_NO': ['TERMINAL_NO', 'TerminalID', 'POS_ID','TERMINAL NUMBER'],
    'AUTH_AMOUNT': ['AUTH_AMOUNT', 'TransactionAmount', 'Amount','DOMESTIC AMT'],
    'APP_CODE': ['APP_CODE', 'ApprovalCode', '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['email'] = moments_pay_df['email'].astype(str).replace('\.0$', '', regex=True)

# 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['APP_CODE'] = bank_transaction_df_mapped['APP_CODE'].astype(str).replace('\.0$', '', regex=True)


# Remove leading single quote from APP_CODE if present
#bank_transaction_df_mapped['APP_CODE'] = bank_transaction_df_mapped['APP_CODE'].str.lstrip("'")
# Append '0' as a prefix to APP_CODE or approval_code if they are 4 digits
bank_transaction_df_mapped['APP_CODE'] = bank_transaction_df_mapped['APP_CODE'].apply(lambda x: x.zfill(6) if len(x) != 6 else x)
moments_pay_df['approval_code'] = moments_pay_df['approval_code'].apply(lambda x: x.zfill(6) if len(x) != 6 else x)

# Extract last 4 digits for CARDNBR and card_num
bank_transaction_df_mapped['Last4_CARDNBR'] = bank_transaction_df_mapped['CARDNBR'].str[-4:]
moments_pay_df['Last4_card_num'] = moments_pay_df['card_num'].str[-4:]

# Filter out rows starting with specified patterns in the first column
exclude_patterns = ['Bank PAN No', 'Merchant PAN No.', 'Curr Ac GSTN', 'Acc No2 GSTN']
bank_transaction_df_mapped = bank_transaction_df_mapped[~bank_transaction_df_mapped.iloc[:, 0].astype(str).str.startswith(tuple(exclude_patterns))]

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {
    'Last4_CARDNBR': 'Last4_card_num',
    'TERMINAL_NO': 'terminal_id',
    'AUTH_AMOUNT': 'total_amount',
    'APP_CODE': 'approval_code',
}

print(moments_pay_df[['processing_id', 'transaction_id'] + list(field_mapping.values())])
print(bank_transaction_df_mapped[['Last4_CARDNBR','TERMINAL_NO','AUTH_AMOUNT','APP_CODE']])

# Merge dataframes on the specified fields
merged_df = pd.merge(bank_transaction_df_mapped, 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['momentPay 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=['CARDNBR'])
merged_df = merged_df.drop(['Last4_CARDNBR'] + list(field_mapping.values()), axis=1)
print(merged_df)

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