import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from datetime import datetime, timedelta

# Load MomentsPay_Bank_new.xlsx
moments_pay_bank_df = pd.read_excel('ContinentalPine25-05.xlsx')

# Load Transaction details as on 14.12.2023
transaction_details_df = pd.read_excel('Continentalbankfile25-05.xlsx')
print(transaction_details_df.columns)

# Convert relevant columns to a common data type (string)
#transaction_details_df['Credit Card Number'] = transaction_details_df['Card Pan Number'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Pay Mode Amount'] = transaction_details_df['Gross Txn Amount'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['RRNo'] = transaction_details_df['RRN number'].astype(str).replace(r'\.0$', '', regex=True).replace(r"'", '',regex=True).str.zfill(12)

#moments_pay_bank_df['Customer Payment Mode ID'] = moments_pay_bank_df['Customer Payment Mode ID'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['Amount'] = moments_pay_bank_df['Amount'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['Approval Code'] = moments_pay_bank_df['RRN'].astype(str).replace(r'\.0$', '', regex=True).replace(r"'",'', regex=True).str.zfill(12)

# Extract last 4 digits for CARDNBR and card_num
#transaction_details_df['Last4_Credit_Card_Number'] = transaction_details_df['Credit Card Number'].str[-4:]
#moments_pay_bank_df['Last4_Customer_Payment_Mode_ID'] = moments_pay_bank_df['Customer Payment Mode ID'].str[-4:]

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {'Amount': 'Pay Mode Amount', 'Approval Code':'RRNo'}

# Filter only card transactions
#transaction_details_df = transaction_details_df[transaction_details_df['Payment Mode'] == 'Credit Card']
moments_pay_bank_df = moments_pay_bank_df[moments_pay_bank_df['Payment Mode'] == 'CARD']

# Remove duplicates from moments_pay_bank_df based on 'approval_code' and 'total_amount'
#moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['Approval Code'], keep='first')

merged_df = pd.merge(
    moments_pay_bank_df,
    transaction_details_df[list(field_mapping.values())],
    left_on=list(field_mapping.keys()),
    right_on=list(field_mapping.values()),
    how='left',
    indicator=True
)

equal_records = merged_df[merged_df['_merge'] == 'both'].copy()
equal_records['momentpay matched'] = 'YES'

unequal_records = merged_df[merged_df['_merge'] == 'left_only'].copy()
unequal_records['momentpay matched'] = 'NO'

# Merge dataframes on the specified fields
#merged_df = pd.merge(moments_pay_bank_df, transaction_details_df[list(field_mapping.values())],
#                     left_on=list(field_mapping.keys()),
#                     right_on=list(field_mapping.values()),
#                     how='left')
#unmerged_df = pd.merge(moments_pay_bank_df, transaction_details_df[list(field_mapping.values())],
#                       left_on=list(field_mapping.keys()),
#                       right_on=list(field_mapping.values()),
#                       how='outer')
#print(merged_df.columns)
#equal_records = merged_df[~merged_df['Approval Code'].isnull()]
#equal_records.insert(0, 'S.No.', range(1, len(equal_records) + 1))
#equal_records['momentpay matched'] = 'YES'
#equal_records['txn_identifier'] = equal_records.apply(
#    lambda row: transaction_details_df[
#        (transaction_details_df['Approval Code'] == row['APPROV CODE']) &
#        (transaction_details_df['Amount'] == row['TRANSACTION AMOUNT'])
#    ]
#    .reset_index(drop=True)['Bill Invoice'].values[0] + "@pinelab.com", 
#    axis=1
#)
print(equal_records)
# Filter unmatched card transactions
#unequal_records = unmerged_df[unmerged_df['Approval Code'].isnull()]
#unequal_records.insert(-1, 'S.No.', range(1, len(unequal_records) + 1))
#unequal_records['momentpay matched'] = 'NO'
print(unequal_records)

# Function to create or overwrite an Excel sheet
def create_or_overwrite_sheet(sheet_name, data):
    with pd.ExcelWriter('Continental-HISBANK25-05.xlsx', mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
        data.to_excel(writer, sheet_name=sheet_name, index=False)

# Generate sheet names
sheet_name_matched = 'PineBankCARD-Matched'
sheet_name_unmatched = 'PineBankCARD-Unmatched'
create_or_overwrite_sheet(sheet_name_matched, equal_records)
create_or_overwrite_sheet(sheet_name_unmatched, unequal_records)

wb = load_workbook('Continental-HISBANK25-05.xlsx')
Summary_Sheet = wb['Summary']

transaction_date = transaction_details_df['Settlement Date'].mode()[0]
print(transaction_date)

# Update summary sheet with total card transaction details
num_rows = len(moments_pay_bank_df)
print(num_rows)
Summary_Sheet['E15'] = num_rows

total_amount = moments_pay_bank_df['Amount'].astype(float).sum()
Summary_Sheet['F15'] = f"₹{total_amount:,.2f}"
print(total_amount)
# Update summary sheet with matched card transaction details
HIS_Matched_df = pd.read_excel('Continental-HISBANK25-05.xlsx', sheet_name='PineBankCARD-Matched')
num_rows = len(HIS_Matched_df)
Summary_Sheet['G15'] = num_rows

total_amount = HIS_Matched_df['Pay Mode Amount'].astype(float).sum()
Summary_Sheet['H15'] = f"₹{total_amount:,.2f}"

# Update summary sheet with unmatched card transaction details
HIS_UnMatched_df = pd.read_excel('Continental-HISBANK25-05.xlsx', sheet_name='PineBankCARD-Unmatched')
num_rows = len(HIS_UnMatched_df)
Summary_Sheet['I15'] = num_rows

total_amount = HIS_UnMatched_df['Amount'].astype(float).sum()
Summary_Sheet['J15'] = f"₹{total_amount:,.2f}"

# Define the color for the header
header_fill = PatternFill(fgColor='1274bd', fill_type='solid')

for sheet_name in ['PineBankCARD-Matched', 'PineBankCARD-Unmatched']:
    worksheet = wb[sheet_name]

    # Apply style to the header row
    for row in worksheet.iter_rows(min_row=1, max_row=1):
        for cell in row:
            cell.fill = header_fill

# Save the workbook
wb.save('Continental-HISBANK25-05.xlsx')

# Save matched and unmatched records to separate files
#def save_to_new_excel_file(file_name, sheet_name, data):
#    with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
#        data.to_excel(writer, sheet_name=sheet_name, index=False)

#save_to_new_excel_file('HISPINE_matched.xlsx', 'Matched', equal_records)
#save_to_new_excel_file('HISPINE_unmatched.xlsx', 'Unmatched', unequal_records)