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

# Load MomentPay CSV and HIS Excel
#moments_pay_bank_df = pd.read_csv('Drmomentpay04-06.csv')
transaction_details_df = pd.read_excel('DrmohansHIS.xlsx')

# Clean HIS fields
#transaction_details_df['CCAUTHNO'] = transaction_details_df['CCAUTHNO'].astype(str).str.replace(r'\.0$', '', regex=True).str.replace(' ', '')
#transaction_details_df['CHEQUENO'] = transaction_details_df['CHEQUENO'].astype(str).str.replace(r'\.0$', '', regex=True)

# Create RRNNO
#transaction_details_df['RRNNO'] = transaction_details_df['CCAUTHNO'].where(
#    transaction_details_df['CCAUTHNO'].str.fullmatch(r'\d{12}')
#).fillna(
#    transaction_details_df['CHEQUENO'].where(transaction_details_df['CHEQUENO'].str.fullmatch(r'\d{12}'))
#).str.replace("'", '').str.zfill(12)

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

# Merge HIS and MomentPay
#merged_df = pd.merge(
#    transaction_details_df,
#    moments_pay_bank_df[['processing_id', 'transaction_id', 'email', 'rrn_no']],
#    left_on='RRNNO',
#    right_on='rrn_no',
#    how='left'
#)

# Mark Matched records
#merged_df['MomentsPay Matched'] = merged_df['processing_id'].notnull().map({True: 'YES', False: 'NO'})

# Drop duplicate RRN column from MomentPay
#merged_df.drop(columns=['rrn_no'], inplace=True)

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

# Ensure numeric amount
transaction_details_df['PAYMODEAMOUNT'] = pd.to_numeric(transaction_details_df['PAYMODEAMOUNT'], errors='coerce')

# Define starting rows for Entity1 and Entity2
entity_start_rows = {
    'Entity1': 6,  # Start at row 6 for Entity1
    'Entity2': 19  # Start at row 19 for Entity2
}

# Define clusters to include
clusters_to_include = ['Andra Pradesh', 'Chennai', 'KARNATAKA', 'Kerala', 'ROI', 'ROTN', 'Telengana']

# Initialize summary for each entity
summary_totals = {
        'Entity1': {cluster: {'Cash': 0.0, 'Digital': 0.0, 'NEFT': 0.0, 'RazorPay': 0.0} for cluster in clusters_to_include},
        'Entity2': {cluster: {'Cash': 0.0, 'Digital': 0.0, 'NEFT': 0.0, 'RazorPay': 0.0} for cluster in clusters_to_include}
}

# Fill totals grouped by ENTITY, Cluster, and PAYMENTMODE
for _, row in transaction_details_df.iterrows():
    entity = row.get('ENTITY')
    cluster = row.get('CLUSTER')
    mode = row.get('PAYMENTMODE')
    usercode = row.get('USERCODE')
    amount = row.get('PAYMODEAMOUNT', 0.0)

    # Determine which entity to use
    entity_key = 'Entity1' if entity == 'DMDSC' else 'Entity2'

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


access_file = 'Drmohan-HISBANK03-06.xlsx'
wb_access = load_workbook(access_file)

# Create or get Summary sheet
if 'Entity Wise Details' not in wb_access.sheetnames:
    ws_access = wb_access.create_sheet('Entity Wise Details')
else:
    ws_access = wb_access['Entity Wise Details']

# Write totals into specified cells for each entity
for entity_key, clusters in summary_totals.items():
    start_row = entity_start_rows[entity_key]  # Get the starting row for the entity
    for i, (cluster, values) in enumerate(clusters.items()):
        row = start_row + i  # Calculate the row dynamically
        #for mode, amount in values.items():
        #    col = 'C' if mode == 'Cash' else 'D'  # Column B for Cash, Column C for Digital
        #    cell = f"{col}{row}"

        #for mode, amount in values.items():
        #    if mode == 'Cash':
        #        col = 'C'
        #    elif mode == 'Digital':
        #        col = 'D'
        #    elif mode == 'NEFT':
        #        col = 'E'
        #    else:
        #        continue  # skip unknown modes

        #    cell = f"{col}{row}"
        #    ws_access[cell] = round(amount, 2)

            # Write cluster name in column B once per row
        #    ws_access[f'B{row}'] = cluster
            # Write the amount to the specified cell
           # ws_access[cell] = round(amount, 2)

            # Write cluster name in column A
            #if mode == 'Cash':  # Only write cluster name once per row
            #    ws_access[f'B{row}'] = cluster

        # Write totals into specified cells for each entity
        for entity_key, clusters in summary_totals.items():
            start_row = entity_start_rows[entity_key]
            for i, (cluster, values) in enumerate(clusters.items()):
                row = start_row + i
                for mode, amount in values.items():
                    if mode == 'Cash':
                        col = 'C'
                    elif mode == 'Digital':
                        col = 'D'
                    elif mode == 'NEFT':
                        col = 'E'
                    elif mode == 'RazorPay':
                        col = 'F'
                    else:
                        continue

                    cell = f"{col}{row}"
                    ws_access[cell] = round(amount, 2)

                    ws_access[f'B{row}'] = cluster  # Cluster name

# Save AccessSummary.xlsx
wb_access.save(access_file)
print(f"📊 Entity Wise Details updated in file: {access_file}")