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

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

# Load Transaction details as on 14.12.2023
transaction_details_df = pd.read_excel('ContinentalintegratedHIS.xlsx')

# Convert relevant columns to a common data type (string)
transaction_details_df['Credit Card Number'] = transaction_details_df['Credit Card Number'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Pay Mode Amount'] = transaction_details_df['Pay Mode Amount'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Credit Card Autherization Number'] = transaction_details_df['Credit Card Autherization Number'].astype(str).replace(r'\.0$', '', regex=True)

moments_pay_bank_df['Card Pan Number'] = moments_pay_bank_df['Card Pan Number'].astype(str).replace(r'\.0$', '', regex=True).replace(r"UHID", '', regex=True)
moments_pay_bank_df['Gross Txn Amount'] = moments_pay_bank_df['Gross Txn Amount'].astype(str).replace('\.0$', '', regex=True)
moments_pay_bank_df['Auth Code'] = moments_pay_bank_df['Auth Code'].astype(str).replace('\.0$', '', regex=True)

moments_pay_bank_df['Last4_CARDNBR'] = moments_pay_bank_df['Card Pan Number'].apply(lambda x: x if '@' in x else x[-4:])
transaction_details_df['Last4_card_num'] = transaction_details_df['Credit Card Number'].apply(lambda x: x if '@' in x else x[-4:])

transaction_details_df = transaction_details_df[transaction_details_df['MomentsPay Matched']=="NO"]

transaction_details_df = transaction_details_df[transaction_details_df['Payment Mode'].isin(["Debit Card","Credit Card"])]

# Specify the mapping between fields excluding 'processing_id' and 'transaction_id'
field_mapping = {'Last4_card_num': 'Last4_CARDNBR','Pay Mode Amount':'Gross Txn Amount','Credit Card Autherization Number':'Auth Code'}

moments_pay_bank_df = moments_pay_bank_df.drop_duplicates(subset=list(field_mapping.values()))

merged_df = pd.merge(transaction_details_df,
                     moments_pay_bank_df[list(field_mapping.values())],
                     left_on=list(field_mapping.keys()),
                     right_on=list(field_mapping.values()),
                     how='outer', indicator=True)
equal_records = merged_df[merged_df['_merge'] == 'both'].copy()
equal_records['momentpay matched'] = 'YES'
equal_records = equal_records.drop_duplicates(subset=['User Code','Doc Num','Pay Mode Amount'], keep='first')

# Unmatched records (only on left side i.e., HIS unmatched)
unequal_records = merged_df[merged_df['_merge'] == 'left_only'].copy()
unequal_records['momentpay matched'] = 'NO'
unequal_records = unequal_records.drop_duplicates(subset=['User Code','Doc Num','Pay Mode Amount'], keep='first')

#merged_df = pd.merge(transaction_details_df, moments_pay_bank_df[list(field_mapping.values())],
#                     left_on=list(field_mapping.keys()),
#                     right_on=list(field_mapping.values()),
#                     how='left')
#unmerged_df = pd.merge(transaction_details_df, moments_pay_bank_df[list(field_mapping.values())],
#                      left_on=list(field_mapping.keys()),
#                       right_on=list(field_mapping.values()),
#                       how='outer', indicator=True)
#print(merged_df.columns)
#equal_records = merged_df[~merged_df['Pay Mode Amount'].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=['Last4_CARDNBR','Auth Code','Gross Txn Amount'], keep='first')
print(equal_records)

#unequal_records = unmerged_df[unmerged_df['_merge'] == 'left_only']
#unequal_records.insert(0, 'S.No.', range(1, len(unequal_records) + 1))
#unequal_records['momentpay matched'] = 'NO'
#print(unequal_records.columns)
#unequal_records = unequal_records.drop_duplicates(subset=['Last4_card_num','MRN','Credit Card Autherization Number','Pay Mode Amount'], keep='first')
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 = 'M-HISBankCard-Matched'
sheet_name_unmatched = 'M-HISBankCard-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']

settled_date = moments_pay_bank_df['Settlement Date'].mode()[0]
#Summary_Sheet['A11'] = settled_date
print(settled_date)
# Update summary sheet with total transaction details
num_rows = len(transaction_details_df)
Summary_Sheet['K10'] = num_rows

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

# Update summary sheet with matched transaction details
HIS_Matched_df = pd.read_excel('Continental-HISBANK25-05.xlsx', sheet_name='M-HISBankCard-Matched')
num_rows = len(HIS_Matched_df)
Summary_Sheet['M10'] = num_rows

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

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

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

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

for sheet_name in ['M-HISBankCard-Matched', 'M-HISBankCard-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-HISBANK15-10.xlsx')

# Combine the matched and unmatched DataFrames to calculate total counts and amounts
combined_df = pd.concat([HIS_Matched_df, HIS_UnMatched_df], ignore_index=True)

# Define the column name for unit/location and total amount
unit_location_col = 'Unit/Location'
total_amount_col = 'Amount'

if unit_location_col in combined_df.columns:

    # Calculate total count and total amount for each unique location
    total_grouped = combined_df.groupby(unit_location_col).agg(
        total_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'),
        total_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum')
    ).reset_index()

    # Calculate matched count and matched total amount for each unique location
    matched_grouped = HIS_Matched_df.groupby(unit_location_col).agg(
        matched_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'),
        matched_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum')
    ).reset_index()

    # Calculate unmatched count and unmatched total amount for each unique location
    unmatched_grouped = HIS_UnMatched_df.groupby(unit_location_col).agg(
        unmatched_count=pd.NamedAgg(column=unit_location_col, aggfunc='count'),
        unmatched_amount=pd.NamedAgg(column=total_amount_col, aggfunc='sum')
    ).reset_index()

    # Merge the results into a single DataFrame
    summary_df = total_grouped.merge(matched_grouped, on=unit_location_col, how='left')\
                          .merge(unmatched_grouped, on=unit_location_col, how='left')

    # Fill NaN values with 0 (in case there are locations that are only in matched or unmatched)
    summary_df = summary_df.fillna(0)
    
    # Load the summary sheet where you want to update the values
    summary_sheet_path = 'Continental-HISBANK25-05.xlsx'
    book = load_workbook(summary_sheet_path)
    summary_sheet = book['Summary']

    # Define the starting row and column for updating the Summary sheet
    start_row = 5
    start_col = 2

    # Function to find the top-left cell of a merged cell range
    def find_top_left_cell(merged_ranges, row, col):
        for merged_range in merged_ranges:
            min_col, min_row, max_col, max_row = range_boundaries(str(merged_range))
            if min_row <= row <= max_row and min_col <= col <= max_col:
                return min_row, min_col
        return row, col

    # Update the Summary sheet with the new summary data
    merged_ranges = summary_sheet.merged_cells.ranges
    for index, row in summary_df.iterrows():
        for col_num, value in enumerate(row):
            # Find the top-left cell if the cell is part of a merged range
            r, c = find_top_left_cell(merged_ranges, start_row + index, start_col + col_num)
            # Write the value to the correct cell
            summary_sheet.cell(row=r, column=c, value=value)

    # Save the workbook without deleting existing sheets
    book.save(summary_sheet_path)

    print(summary_df)
else:
    print(f"'{unit_location_col}' column not found in the combined DataFrame. No updates made.")
    wb.save('Continental-HISBANK25-05.xlsx')