import pandas as pd

# Step 1: Load the Excel files
drmohan_df = pd.read_excel("CollectionreportofDrmohans03-06.xlsx")
mastercard_df = pd.read_excel("CollectionOUlocationMaster.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={"SITE NAME": "SITE NAME"})

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

enriched_df = enriched_df.drop_duplicates(subset=["DOCNUM","SERVICERECEIPTDATE", "FIRSTNAME", "PAYMODEAMOUNT"])

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

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