import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# Load input files
moments_pay_bank_df = pd.read_excel('DMDSCstatement.xlsx')
transaction_details_df = pd.read_excel('DrmohansHIS.xlsx')

# Preprocess HIS Data
transaction_details_df['CCAUTHNO'] = transaction_details_df['CCAUTHNO'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['CHEQUENO'] = transaction_details_df['CHEQUENO'].astype(str).replace(r'\.0$', '', regex=True)
transaction_details_df['Amount'] = transaction_details_df['PAYMODEAMOUNT'].astype(float).round(2)
transaction_details_df['TransactionId'] = (
    transaction_details_df['CCAUTHNO'].where(transaction_details_df['CCAUTHNO'].str.fullmatch(r'\d{1,12}'))
    .fillna(transaction_details_df['CHEQUENO'].where(transaction_details_df['CHEQUENO'].str.fullmatch(r'\d{1,12}')))
    .replace(r"'", '', regex=True).apply(lambda x: x.zfill(12) if pd.notnull(x) else x)
)

# Preprocess Moments Pay Bank Data
moments_pay_bank_df['RRN'] = moments_pay_bank_df['RRN'].astype(str).replace(r'\.0$', '', regex=True)
moments_pay_bank_df['Txn Amt'] = moments_pay_bank_df['GROSS_AMT'].astype(float).round(2)

# Filter by ENTITY
transaction_details_df = transaction_details_df[transaction_details_df['ENTITY'] == 'DMDSC']

# Filter by payment modes
upi_transactions_df = transaction_details_df[transaction_details_df['PAYMODECODE'].isin(['EWALLET', 'CREDITCARD', 'DEBITCARD'])]

# Strip and deduplicate
upi_transactions_df['TransactionId'] = upi_transactions_df['TransactionId'].str.strip()
moments_pay_bank_df['RRN'] = moments_pay_bank_df['RRN'].str.strip()

upi_transactions_df = upi_transactions_df.drop_duplicates(subset=['TransactionId', 'Amount'], keep='first')
moments_pay_bank_df_dedup = moments_pay_bank_df.drop_duplicates(subset=['RRN', 'Txn Amt'], keep='first')

# Merge to find matched/unmatched
merged_df = pd.merge(
    upi_transactions_df,
    moments_pay_bank_df_dedup[['RRN', 'Txn Amt']],
    left_on=['TransactionId', 'Amount'],
    right_on=['RRN', 'Txn Amt'],
    how='left',
    indicator=True
)

# Matched records
matched_records = merged_df[merged_df['_merge'] == 'both'].copy()
matched_records.insert(0, 'S.No.', range(1, len(matched_records) + 1))
matched_records['momentpay matched'] = 'YES'

# Unmatched records
unmatched_records = merged_df[merged_df['_merge'] != 'both'].copy()
unmatched_records.insert(0, 'S.No.', range(1, len(unmatched_records) + 1))
unmatched_records['momentpay matched'] = 'NO'
print(unmatched_records.columns)
# Excel file path
excel_path = 'Drmohan-HISBANK03-06.xlsx'
wb = load_workbook(excel_path)

# Add Branch for summary
upi_transactions_df['Branch'] = upi_transactions_df['BRANCH']

# Summary generation
matched_summary = matched_records.groupby('BRANCH').agg(
    Matched_Count=('Amount', 'count'),
    Matched_Amount=('Amount', 'sum')
)

unmatched_summary = unmatched_records.groupby('BRANCH').agg(
    Unmatched_Count=('Amount', 'count'),
    Unmatched_Amount=('Amount', 'sum')
)

summary = pd.merge(matched_summary, unmatched_summary, left_index=True, right_index=True, how='outer').fillna(0)
summary['Total_Count'] = summary['Matched_Count'] + summary['Unmatched_Count']
summary['Total_Amount'] = summary['Matched_Amount'] + summary['Unmatched_Amount']
summary = summary.reset_index()

# Write summary to "Matched Unmatched Details-DMDSC"
summary_ws = wb['Matched Unmatched Details-DMDSC']
start_row = 10
start_col = 1
for r_idx, row in enumerate(dataframe_to_rows(summary, index=False, header=True), start=start_row):
    for c_idx, value in enumerate(row, start=start_col):
        summary_ws.cell(row=r_idx, column=c_idx, value=value)

# --- Write unmatched details to "POS Reco-DSC LLP" ---

# Map columns and prepare final unmatched data
unmatched_records = unmatched_records.rename(columns={
    'PAYMODEAMOUNT': 'PAYMODEAMOUNT',
    'PATIENTNUMBER': 'PATIENTNUMBER',
    'PATIENTNAME': 'PATIENTNAME',
    'DOCNUM': 'DOCNUM',
    'RRN': 'RRN',
    'Date': 'DOCDATE',
    'CLUSTER': 'CLUSTER',
    'BRANCH': 'BRANCH',
    'ENTITY': 'ENTITY'
})

# Add MDR and Diff
unmatched_records['MDR Amount'] = 0.0
unmatched_records['PAYMODEAMOUNT'] = unmatched_records['PAYMODEAMOUNT'].astype(float)
unmatched_records['Diff'] = unmatched_records['MDR Amount'] - unmatched_records['PAYMODEAMOUNT']

# Final selection for writing
unmatched_output = unmatched_records[['CLUSTER', 'BRANCH', 'ENTITY', 'DOCDATE', 'DOCNUM', 'PATIENTNUMBER',
                                      'PATIENTNAME', 'RRN', 'PAYMODEAMOUNT', 'MDR Amount', 'Diff']]
unmatched_output = unmatched_output.loc[:, ~unmatched_output.columns.duplicated()]

# Write unmatched output to POS Reco-DSC LLP sheet
ws = wb['POS Reco-DMDSC']
start_row = 7
start_col = 2
for r_idx, row in enumerate(dataframe_to_rows(unmatched_output, index=False, header=True), start=start_row):
    for c_idx, value in enumerate(row, start=start_col):
        ws.cell(row=r_idx, column=c_idx, value=value)

# Save the workbook
wb.save(excel_path)