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('Abankstatementphonepe10-06.xlsx')

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

moments_pay_bank_df = moments_pay_bank_df.drop_duplicates(subset=['TransactionUTR'], keep='first')
transaction_details_df = transaction_details_df.drop_duplicates(subset=['ChequeorCardNo'], keep='first')

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

moments_pay_bank_df['PhonePeReferenceId'] = moments_pay_bank_df['PhonePeReferenceId'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['TransactionUTR'] = moments_pay_bank_df['TransactionUTR'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['Amount'] = moments_pay_bank_df['Amount'].astype(str).replace(r'\.0$', '', regex=True)

#transaction_details_df['BillNo'] = transaction_details_df['BillNo'].astype(str).replace(r'\.0$', '', regex=True)
#transaction_details_df['BillNo']= transaction_details_df['BillNo'].apply(lambda x: x if x.startswith('REC') else x)

# Convert 'BillNo' to string and remove trailing '.0'
transaction_details_df['BillNo'] = transaction_details_df['BillNo'].astype(str).replace(r'\.0$', '', regex=True)

# Keep only values that start with "REC", replace others with NaN or keep unchanged
transaction_details_df['BillNo'] = transaction_details_df['BillNo'].apply(lambda x: x if x.startswith('REC') else None)

# Optionally, drop rows where 'BillNo' is NaN
#transaction_details_df = transaction_details_df.dropna(subset=['BillNo'])

print(transaction_details_df)
# Unit mapping dictionary
unit_mapping = {
    'ADYAR ANDERSON': 'ADYAR ANDERSON',
    'ANDERSON GUDUVANCHERI':'ANDERSON GUDUVANCHERI',
    'ANDERSON HYDERABAD':'ANDERSON HYDERABAD',
    'ANDERSON KANCHIPURAM':'ANDERSON KANCHIPURAM',
    'ANDERSON VADAPALANI':'ANDERSON VADAPALANI',
    'ANDERSON VELLORE':'ANDERSON VELLORE',
    'ANDERSON VIJAYAWADA':'ANDERSON VIJAYAWADA',
    'ANDHRA B2B CENTRE':'ANDHRA B2B CENTRE',
    'ANGAI CLINIC ADYAR':'ANGAI CLINIC ADYAR',
    'ANNANAGAR CC':'ANNANAGAR CC',
    'CHENNAI SPECIALITY CLINIC - CHOOLAIMEDU':'CHENNAI SPECIALITY CLINIC - CHOOLAIMEDU',
    'CHILD CARE HOSPITAL':'CHILD CARE HOSPITAL',
    'CHILDRENS MEDICAL CENTRE':'CHILDRENS MEDICAL CENTRE',
    'CHROMEPET MAIN CENTRE':'CHROMEPET MAIN CENTRE',
    'CLRI':'CLRI',
    'DATA CENTER':'DATA CENTER',
    'DEEN ORTHO CENTER':'DEEN ORTHO CENTER',
    'DR P MUTHUKUMAR NEUROLOGY CLINIC VALASARAVAKKAM':'DR P MUTHUKUMAR NEUROLOGY CLINIC VALASARAVAKKAM',
    'GENETICS KILPAUK':'GENETICS KILPAUK',
    'GREAMS ROAD':'GREAMS ROAD',
    'HUBLI ANDERSON':'HUBLI ANDERSON',
    'I CARE CANCER CLINIC':'I CARE CANCER CLINIC',
    'ICF ANDERSON':'ICF ANDERSON',
    'JAYA EYE CARE CENTRE':'JAYA EYE CARE CENTRE',
    'KOVAI PETCT':'KOVAI PETCT',
    'HOME COLLECTION CENTRE':'HOME COLLECTION CENTRE',
    'NAGERCOIL CENTRE':'NAGERCOIL CENTRE',
    'NANGANALLUR CENTRE':'NANGANALLUR CENTRE',
    'NELLORE':'NELLORE',
    'NIRAM HOSPITAL':'NIRAM HOSPITAL',
    'NUNGAMBAKKAM':'NUNGAMBAKKAM',
    'PDR ORTHOPAEDIC HOSPITAL':'PDR ORTHOPAEDIC HOSPITAL',
    'PERAMBUR COLLECTION POINT':'PERAMBUR COLLECTION POINT',
    'PONDICHERRY COLLECTION POINT':'PONDICHERRY COLLECTION POINT',
    'RGGH(MMC)':'RGGH(MMC)',
    'SRIRANGAM ANDERSON':'SRIRANGAM ANDERSON',
    'SRN NURO CLINIC':'SRN NURO CLINIC',
    'TAMBARAM CC':'TAMBARAM CC',
    'THANJAVUR PROCESSING CENTRE':'THANJAVUR PROCESSING CENTRE',
    'THIRUMULLAIVOYIL':'THIRUMULLAIVOYIL',
    'THIRUNELVELI PALAYAMKOTTAI':'THIRUNELVELI PALAYAMKOTTAI',
    'TIRUPATHI CC':'TIRUPATHI CC',
    'VELLORE NALAM HOSPITAL':'VELLORE NALAM HOSPITAL',
    'VILLUPURAM ANDERSON':'VILLUPURAM ANDERSON',
    'WEST MAMBALAM ANDERSON':'WEST MAMBALAM ANDERSON',
    'ANDERSON MADURAI': 'ANDERSON MADURAI',
    'ANDERSON COIMBATORE':'ANDERSON COIMBATORE',
    'ANDERSON DELHI NCR':'ANDERSON DELHI NCR',
    'BANGALORE MAIN LAB - ANDERSON':'BANGALORE MAIN LAB - ANDERSON',
    'CAMP 4 ONLINE':'CAMP 4 ONLINE',
    'CHENNAI B2B CENTRE':'CHENNAI B2B CENTRE',
    'ENRICH LIFESTYLE CLINIC':'ENRICH LIFESTYLE CLINIC',
    'KARNATAKA B2B':'KARNATAKA B2B',
    'KODAMBAKKAM ANDERSON':'KODAMBAKKAM ANDERSON',
    'MUMBAI B2B CENTRE':'MUMBAI B2B CENTRE',
    'TRICHY ANDERSON':'TRICHY ANDERSON',
    'SALEM PETCT':'SALEM PETCT',
    'SUPERLIFE PHYSIO CLINIC - VEPERY':'SUPERLIFE PHYSIO CLINIC - VEPERY',
}

# Function to normalize whitespace
def normalize_whitespace(value):
    if isinstance(value, str):
        return ' '.join(value.split()).upper()
    return value

# Normalize TenentName in transaction_details_df
transaction_details_df['TenentName'] = transaction_details_df['TenentName'].apply(normalize_whitespace)

# Normalize keys in unit_mapping
unit_mapping = {normalize_whitespace(k): normalize_whitespace(v) for k, v in unit_mapping.items()}

# Apply normalization to mapped tenant names
transaction_details_df['TenentName'] = transaction_details_df['TenentName'].replace(unit_mapping)


#def get_mapping_key(value):
#    return 'TransactionUTR' if len(value) == 12 else 'PhonePeReferenceId'

#transaction_details_df['MappingKey'] = transaction_details_df['ChequeorCardNo'].apply(get_mapping_key)

# Merge transactions with bank statement
#merged_df = transaction_details_df.merge(
#    moments_pay_bank_df,
#    left_on=['ChequeorCardNo', 'Amount'],
#    right_on=['TransactionUTR', 'Amount'],
#    how='left'
#)

# Drop helper column
#merged_df.drop(columns=['MappingKey'], inplace=True)

# Determine mapping key dynamically
def get_mapping_key(value):
    return 'TransactionUTR' if len(value) == 12 else 'PhonePeReferenceId'

# Create a new column for mapping
transaction_details_df['MappingKey'] = transaction_details_df['ChequeorCardNo'].apply(get_mapping_key)

# Perform the merge dynamically based on the mapping key
#merged_df = transaction_details_df.merge(
#    moments_pay_bank_df,
#    left_on=['ChequeorCardNo', 'Amount'],
#    right_on=[transaction_details_df['MappingKey'], 'Amount'],
#    how='left'
#)

merge_utr = transaction_details_df[transaction_details_df['ChequeorCardNo'].str.len() == 12].merge(
    moments_pay_bank_df,
    left_on=['ChequeorCardNo', 'Amount'],
    right_on=['TransactionUTR', 'Amount'],
    how='left'
)

# Merge where ChequeorCardNo length != 12 (match with PhonePeReferenceId)
merge_phonepe = transaction_details_df[transaction_details_df['ChequeorCardNo'].str.len() != 12].merge(
    moments_pay_bank_df,
    left_on=['ChequeorCardNo', 'Amount'],
    right_on=['PhonePeReferenceId', 'Amount'],
    how='left'
)

merged_df = pd.concat([merge_utr, merge_phonepe])

# **Segregate Matched and Unmatched Records**
equal_records = merged_df.dropna(subset=['TransactionUTR']).copy()
#equal_records.insert(0, 'S.No.', range(1, len(matched_df) + 1))
equal_records['momentpay matched'] = 'YES'

unequal_records = merged_df[merged_df['TransactionUTR'].isnull()].copy()
#unequal_records.insert(0, 'S.No.', range(1, len(unmatched_df) + 1))
unequal_records['momentpay matched'] = 'NO'

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

# File name
output_file = 'Anderson-HISBANK10-06.xlsx'

# Save matched and unmatched records
sheet_name_matched = 'M-HISPhonepeBank-Matched'
sheet_name_unmatched = 'M-HISPhonepeBank-Unmatched'

create_or_overwrite_sheet(output_file, sheet_name_matched, equal_records)
create_or_overwrite_sheet(output_file, sheet_name_unmatched, unequal_records)

# Load workbook and save to ensure changes persist
wb = load_workbook(output_file)
#wb.save(output_file)
wb = load_workbook('Anderson-HISBANK10-06.xlsx')
Summary_Sheet = wb['Summary']

#settled_date = moments_pay_bank_df['SettlementDate'].mode()[0]
#Summary_Sheet['11'] = settled_date

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

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

# Update summary sheet with matched transaction details
HIS_Matched_df = pd.read_excel('Anderson-HISBANK10-06.xlsx', sheet_name='M-HISPhonepeBank-Matched')
num_rows = len(HIS_Matched_df)
Summary_Sheet['N102'] = num_rows
Summary_Sheet['N40'] = num_rows

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

# Update summary sheet with unmatched transaction details
HIS_UnMatched_df = pd.read_excel('Anderson-HISBANK10-06.xlsx', sheet_name='M-HISPhonepeBank-Unmatched')
num_rows = len(HIS_UnMatched_df)
Summary_Sheet['P102'] = num_rows
Summary_Sheet['P40'] = num_rows

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

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

for sheet_name in ['M-HISPhonepeBank-Matched', 'M-HISPhonepeBank-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('Anderson-HISBANK10-06.xlsx')

# Combine the matched and unmatched DataFrames to calculate total counts and amounts
#combined_df = pd.concat([HIS_Matched_df], ignore_index=True)
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 = 'TenentName'
total_amount_col = 'Amount'

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

# Create a dataframe for all the locations from unit_mapping with default 0 values
location_df = pd.DataFrame(list(unit_mapping.items()), columns=[unit_location_col, 'dummy'])
location_df['total_count'] = 0
location_df['total_amount'] = 0.0
location_df['matched_count'] = 0
location_df['matched_amount'] = 0.0
location_df['unmatched_count'] = 0
location_df['unmatched_amount'] = 0.0

# Merge the grouped data with the location_df to ensure all locations from unit_mapping are included
summary_df = total_grouped.merge(matched_grouped, on=unit_location_col, how='left')\
                              .merge(unmatched_grouped, on=unit_location_col, how='left')

# Merge with location_df to ensure all unit_mapping locations are included, even with 0 values
summary_df = summary_df.merge(location_df, on=unit_location_col, how='right', suffixes=('_data', '_mapped'))

# Fill NaN values with 0 (in case there are locations that are only in unit_mapping and not in the data)
summary_df['total_count'] = summary_df['total_count_data'].fillna(0).astype(int)
summary_df['total_amount'] = summary_df['total_amount_data'].fillna(0.0)
summary_df['matched_count'] = summary_df['matched_count_data'].fillna(0).astype(int)
summary_df['matched_amount'] = summary_df['matched_amount_data'].fillna(0.0)
summary_df['unmatched_count'] = summary_df['unmatched_count_data'].fillna(0).astype(int)
summary_df['unmatched_amount'] = summary_df['unmatched_amount_data'].fillna(0.0)

# Drop unnecessary columns
summary_df.drop(columns=[col for col in summary_df.columns if col.endswith('_data')], inplace=True)

# Reorder the columns
summary_df = summary_df[['TenentName', 'total_count', 'total_amount', 'matched_count', 'matched_amount', 'unmatched_count', 'unmatched_amount']]

# 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 = 'Anderson-HISBANK10-06.xlsx'
book = load_workbook(summary_sheet_path)
summary_sheet = book['Summary']

# Define the starting row and column for updating the Summary sheet
#start_row = 88  
#start_col = 11

#start_row = 39
#start_col = 11

# 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
#        for row in rows_to_update:
#            r, c = find_top_left_cell(merged_ranges, row + index, start_col + col_num)

        #r, c = find_top_left_cell(merged_ranges, rows_to_update + [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)

rows_to_update = [103,41]  # List of rows where the data should be written
start_col = 11  # Column remains the same

# 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 in both locations
merged_ranges = summary_sheet.merged_cells.ranges

for start_row in rows_to_update:  # Loop through both row positions
    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)