import pandas as pd

# Step 1: Load the Excel files
drmohan_df = pd.read_excel("DMDSCbankstatement03-06.xlsx")
mastercard_df = pd.read_excel("MDROUMaster.xlsx")

# Step 2: Inspect column names (optional - for debugging)
print("Drmohan Columns:", drmohan_df.columns.tolist())
print("MasterCard Columns:", mastercard_df.columns.tolist())

# Clean and normalize column names
drmohan_df.columns = drmohan_df.columns.str.strip().str.upper()
mastercard_df.columns = mastercard_df.columns.str.strip().str.upper()

# Rename for merge
drmohan_df = drmohan_df.rename(columns={"TERM_ID": "TID"})

# Merge to enrich Drmohan data
enriched_df = pd.merge(
    drmohan_df,
    mastercard_df[["TID", "ENTITY", "BRANCH", "CLUSTER"]],
    on="TID",
    how="left"  # keep all Drmohan rows, even if unmatched
)

# Save enriched file (change path if needed)
enriched_df.to_excel("DMDSCstatement.xlsx", index=False)

print("✅ Drmohan file enriched and saved as DMDSCstatement.xlsx")