import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.cell import coordinate_to_tuple

# Load files
#moments_pay_bank_df = pd.read_csv('Drmomentpay02-06.csv')
transaction_details_df = pd.read_excel('DrmohansHIS.xlsx')

# Ensure CCAUTHNO and CHEQUENO are strings FIRST before using .str
#transaction_details_df['CCAUTHNO'] = transaction_details_df['CCAUTHNO'].astype(str)
#transaction_details_df['CHEQUENO'] = transaction_details_df['CHEQUENO'].astype(str)

# Replace actual 'nan' strings (from str conversion) with empty
#transaction_details_df['CCAUTHNO'] = transaction_details_df['CCAUTHNO'].replace('nan', '')
#transaction_details_df['CHEQUENO'] = transaction_details_df['CHEQUENO'].replace('nan', '')

# Create boolean masks using fullmatch AFTER casting
#ccauth_valid = transaction_details_df['CCAUTHNO'].str.fullmatch(r'\d{12}', na=False)
#chequeno_valid = transaction_details_df['CHEQUENO'].str.fullmatch(r'\d{12}', na=False)

# Use CCAUTHNO if valid, else CHEQUENO
#transaction_details_df['RRNNO'] = transaction_details_df['CCAUTHNO'].where(ccauth_valid, '')
#transaction_details_df['RRNNO'] = transaction_details_df['RRNNO'].where(transaction_details_df['RRNNO'] != '', transaction_details_df['CHEQUENO'].where(chequeno_valid, ''))

# Final clean up
#transaction_details_df['RRNNO'] = transaction_details_df['RRNNO'].str.replace("'", '').str.zfill(12)

# Clean MomentPay RRN
#moments_pay_bank_df['rrn_no'] = moments_pay_bank_df['rrn_no'].astype(str).replace('nan', '')
#moments_pay_bank_df['rrn_no'] = moments_pay_bank_df['rrn_no'].str.replace(r'\.0$', '', regex=True).str.replace("'", '').str.zfill(12)

# Merge
#merged_df = pd.merge(
#    transaction_details_df,
#    moments_pay_bank_df,
#    left_on='RRNNO',
#    right_on='rrn_no',
#    how='left'
#).drop(columns=['rrn_no'])

#merged_df = pd.merge(
 #   transaction_details_df,
#    moments_pay_bank_df[['rrn_no']],  # Only keep rrn_no to avoid duplicates
#    left_on='RRNNO',
#    right_on='rrn_no',
#    how='left'
#).drop(columns=['rrn_no'])

# Save merged output
output_file = 'DrmohansHIS.xlsx'
#merged_df.to_excel(output_file, index=False)
print(f"✅ Reconciled file saved to: {output_file}")

# Summary Calculation
transaction_details_df['PAYMODEAMOUNT'] = pd.to_numeric(transaction_details_df['PAYMODEAMOUNT'], errors='coerce')

clusters = ['Andra Pradesh', 'Chennai', 'KARNATAKA', 'Kerala', 'ROI', 'ROTN', 'Telengana']
cell_mapping = {
    'Andra Pradesh': {'Cash': 'B8', 'Digital': 'C8', 'NEFT': 'D8', 'RazorPay': 'E8'},
    'Chennai':       {'Cash': 'B9', 'Digital': 'C9', 'NEFT': 'D9', 'RazorPay': 'E9'},
    'KARNATAKA':     {'Cash': 'B10', 'Digital': 'C10', 'NEFT': 'D10', 'RazorPay': 'E10'},
    'Kerala':        {'Cash': 'B11', 'Digital': 'C11', 'NEFT': 'D11', 'RazorPay': 'E11'},
    'ROI':           {'Cash': 'B12', 'Digital': 'C12', 'NEFT': 'D12', 'RazorPay': 'E12'},
    'ROTN':          {'Cash': 'B13', 'Digital': 'C13', 'NEFT': 'D13', 'RazorPay': 'E13'},
    'Telengana':     {'Cash': 'B14', 'Digital': 'C14', 'NEFT': 'D14', 'RazorPay': 'E14'},
}

summary_totals = {cluster: {'Cash': 0.0, 'Digital': 0.0, 'NEFT': 0.0, 'RazorPay': 0.0} for cluster in clusters}

# Accumulate amounts
for _, row in transaction_details_df.iterrows():
    cluster = row.get('CLUSTER')
    mode = row.get('PAYMENTMODE')
    usercode = row.get('USERCODE')
    amount = row.get('PAYMODEAMOUNT', 0.0)

    #if cluster in summary_totals and pd.notna(amount):
    #    if mode == 'Cash':
    #        summary_totals[cluster]['Cash'] += amount
    #    elif mode in ['Credit Card','Debit Card', 'eWallet'] and usercode != ['ewt_razorpay','diala']:
    #        summary_totals[cluster]['Digital'] += amount
    #    elif mode in['Bank Transfer','Bank']:
    #        summary_totals[cluster]['NEFT'] += amount
    #    if usercode in ['ewt_razorpay','diala']:
    #        summary_totals[cluster]['RazorPay'] += amount

    if cluster in summary_totals and pd.notna(amount):
        if mode == 'Cash':
            summary_totals[cluster]['Cash'] += amount
        elif usercode in ['ewt_razorpay', 'diala']:
            summary_totals[cluster]['RazorPay'] += amount
        elif mode in ['Credit Card', 'Debit Card', 'eWallet'] and usercode not in ['ewt_razorpay', 'diala']:
            summary_totals[cluster]['Digital'] += amount
        elif mode in ['Bank Transfer', 'Bank']:
            summary_totals[cluster]['NEFT'] += amount
# Write summary
access_file = 'Drmohan-HISBANK03-06.xlsx'
wb_access = load_workbook(access_file)
ws_access = wb_access['Summary'] if 'Summary' in wb_access.sheetnames else wb_access.create_sheet('Summary')

for cluster, values in summary_totals.items():
    for mode, amount in values.items():
        cell = cell_mapping[cluster][mode]
        for merged_range in ws_access.merged_cells.ranges:
            if cell in merged_range and cell != merged_range.coord.split(":")[0]:
                print(f"⚠️ Skipping merged cell: {cell} (mode: {mode}, cluster: {cluster})")
                break
        else:
            ws_access[cell] = round(amount, 2)

        if mode == 'Cash':
            row_num = coordinate_to_tuple(cell)[0]
            ws_access[f'A{row_num}'] = cluster

wb_access.save(access_file)
print(f"📊 Summary updated in file: {access_file}")