import pandas as pd

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

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

# Strip unwanted spaces from PICKUP POINT CODE
drmohan_df['PICKUP POINT CODE'] = drmohan_df['PICKUP POINT CODE'].astype(str).str.strip()

# Merge to enrich Drmohan data
enriched_df = pd.merge(
    drmohan_df,
    mastercard_df[["ENTITY", "BRANCH", "CLSUTER", "PICKUP CODE"]],
    left_on="PICKUP POINT CODE",
    right_on="PICKUP CODE",
    how="left"
)

# Save enriched file
enriched_df.to_excel("DrmohansCashfileDSC.xlsx", index=False)

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