import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

# Load MomentsPay_Bank_new.xlsx
moments_pay_bank_df = pd.read_excel('Hisreport23-05.xlsx')
print(moments_pay_bank_df.columns)
# Load Transaction details as on 14.12.2023
transaction_details_df = pd.read_excel('Amexcard23-05.xlsx')
print(transaction_details_df.columns)
# Convert relevant columns to a common data type (string)
transaction_details_df['Card Number'] = transaction_details_df['Card number'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Charge Amount'] = transaction_details_df['Charge amount'].astype(str).replace(r'\.0$', '', regex=True)
#transaction_details_df['TID'] = transaction_details_df['TID'].astype(str).replace(r'\.0$', '', regex=True)

#moments_pay_bank_df['Terminal ID'] = moments_pay_bank_df['Terminal ID'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['CardNo'] = moments_pay_bank_df['CardNo'].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['Last4_CARDNBR'] = moments_pay_bank_df['CardNo'].apply(lambda x: x if '@' in x else x[-4:])
transaction_details_df['Last4_card_num'] = transaction_details_df['Card Number'].apply(lambda x: x if '@' in x else x[-4:])

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {'Last4_CARDNBR': 'Last4_card_num','Amount':'Charge Amount'}

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

# 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')

equal_records = merged_df[(~merged_df['Last4_card_num'].isnull())]
equal_records.insert(0, 'S.No.', range(1, len(equal_records) + 1))
equal_records['momentpay matched'] = 'YES'
equal_records = equal_records.drop_duplicates(subset=['AggregatorTransactionId'], keep='first')
print(equal_records)

# Filter unmatched card transactions
unequal_records = unmerged_df[unmerged_df['Last4_card_num'].isnull() & (unmerged_df['PayMode'] == 'cards-swipe')]
unequal_records.insert(0, 'S.No.', range(1, len(unequal_records) + 1))
unequal_records['momentpay matched'] = 'NO'

# Function to create or overwrite an Excel sheet
def create_or_overwrite_sheet(sheet_name, data):
    with pd.ExcelWriter('Rela-HISBANK23-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 = 'HisAmex-Matched'
sheet_name_unmatched = 'HisAmex-Unmatched'
create_or_overwrite_sheet(sheet_name_matched, equal_records)
create_or_overwrite_sheet(sheet_name_unmatched, unequal_records)

wb = load_workbook('Rela-HISBANK23-05.xlsx')
Summary_Sheet = wb['Summary']

transaction_date = moments_pay_bank_df['ReceiptDate'].mode()[0]
print(transaction_date)

# Update summary sheet with total card transaction details
num_rows = len(transaction_details_df)
Summary_Sheet['E21'] = num_rows

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

# Update summary sheet with matched card transaction details
HIS_Matched_df = pd.read_excel('Rela-HISBANK23-05.xlsx', sheet_name='HisAmex-Matched')
num_rows1 = len(HIS_Matched_df)
Summary_Sheet['G21'] = num_rows1

total_amount1 = HIS_Matched_df['Amount'].astype(float).sum()
Summary_Sheet['H21'] = f"₹{total_amount1:,.2f}"

# Update summary sheet with unmatched card transaction details
HIS_UnMatched_df = pd.read_excel('Rela-HISBANK23-05.xlsx', sheet_name='HisAmex-Unmatched')
num_rows2 = len(HIS_UnMatched_df)
Summary_Sheet['I21'] = num_rows2

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

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

for sheet_name in ['HisAmex-Matched', 'HisAmex-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('Rela-HISBANK23-05.xlsx')

#input_file = 'Rela-HISBANK13-03.xlsx'

# Function to update the summary sheet
#def update_summary_sheet(summary_sheet, matched_count, unmatched_count, matched_amount, unmatched_amount):
    # Retrieve current matched count and amount
#    matched_cell_count = int(summary_sheet['G11'].value or 0)  # Default to 0 if cell is empty
#    matched_cell_amount = float(summary_sheet['H11'].value.strip('₹').replace(',', '') or 0)  # Default to 0 if cell is empty

    # Add matched count and amount
#    summary_sheet['G11'] = matched_cell_count + matched_count
#    summary_sheet['H11'] = f"₹{matched_cell_amount + matched_amount:,.2f}"

    # Retrieve current unmatched count and amount
#    unmatched_cell_count = int(summary_sheet['I11'].value or 0)  # Default to 0 if cell is empty
#    unmatched_cell_amount = float(summary_sheet['J11'].value.strip('₹').replace(',', '') or 0)  # Default to 0 if cell is empty

    # Update unmatched count and amount
#    summary_sheet['I11'] = unmatched_cell_count - matched_count
#    summary_sheet['J11'] = f"₹{unmatched_cell_amount - matched_amount:,.2f}"

# Function to move matched data to a consolidated sheet
#def move_matched_data_to_consolidated(matched_sheet_name, consolidated_sheet_name, file_path):
#    matched_data = pd.read_excel(file_path, sheet_name=matched_sheet_name)

    # Check if the consolidated sheet exists
#    try:
#        consolidated_data = pd.read_excel(file_path, sheet_name=consolidated_sheet_name)
#    except ValueError:
#        consolidated_data = pd.DataFrame()

    # Append matched data to the consolidated sheet
#    updated_consolidated_data = pd.concat([consolidated_data, matched_data], ignore_index=True)

    # Save the updated consolidated sheet
#    with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
#        updated_consolidated_data.to_excel(writer, sheet_name=consolidated_sheet_name, index=False)

# Function to remove matched data from the unmatched sheet
#def delete_matched_data_from_unmatched(matched_sheet_name, unmatched_sheet_name1, file_path):
#    matched_data = pd.read_excel(file_path, sheet_name=matched_sheet_name)
#    unmatched_data = pd.read_excel(file_path, sheet_name=unmatched_sheet_name1)

    # Remove rows in unmatched data that are present in matched data
#    unmatched_data_filtered = unmatched_data[
#        ~unmatched_data['AggregatorTransactionId'].isin(matched_data['AggregatorTransactionId'])
#    ]

    # Save the updated unmatched sheet
#    with pd.ExcelWriter(file_path, mode='a', engine='openpyxl', if_sheet_exists='replace') as writer:
#        unmatched_data_filtered.to_excel(writer, sheet_name=unmatched_sheet_name1, index=False)

# Function to delete specified sheets
#def delete_sheet_from_workbook(sheet_names, file_path):
#    wb = load_workbook(file_path)
#    for sheet_name in sheet_names:
#        if sheet_name in wb.sheetnames:
#            del wb[sheet_name]
#    wb.save(file_path)

# Load data for calculations
#matched_sheet_name = 'HisAmex-Matched'
#unmatched_sheet_name = 'HisAmex-Unmatched'
#consolidated_sheet_name = 'HISBankCard-Matched'
#unmatched_sheet_name1 = 'HISBankCard-Unmatched'

#matched_data = pd.read_excel(input_file, sheet_name=matched_sheet_name)
#unmatched_data = pd.read_excel(input_file, sheet_name=unmatched_sheet_name)

# Calculate matched and unmatched counts and amounts
#matched_count = len(matched_data)
#unmatched_count = len(unmatched_data)
#matched_amount = matched_data['Amount'].astype(float).sum()
#unmatched_amount = unmatched_data['Amount'].astype(float).sum()

# Load the workbook and summary sheet
#wb = load_workbook(input_file)
#summary_sheet = wb['Summary']

# Step 1: Update the summary sheet
#update_summary_sheet(summary_sheet, matched_count, unmatched_count, matched_amount, unmatched_amount)
#wb.save(input_file)  # Save after updating summary

# Step 2: Move matched data to the consolidated sheet
#move_matched_data_to_consolidated(matched_sheet_name, consolidated_sheet_name, input_file)

# Step 3: Remove matched data from the unmatched sheet
#delete_matched_data_from_unmatched(matched_sheet_name, unmatched_sheet_name1, input_file)

# Step 4: Delete the matched sheet
#delete_sheet_from_workbook([matched_sheet_name,unmatched_sheet_name], input_file)

#print("Matched data moved, unmatched data updated, and summary sheet updated successfully.")