"""Cleans messy_export.xlsx the way the guide explains. pip install pandas openpyxl ; python clean_export.py"""
import pandas as pd

df = pd.read_excel("messy_export.xlsx", skiprows=3).dropna(how="all")
df.columns = df.columns.str.strip().str.lower().str.replace(r"[^\w]+", "_", regex=True).str.strip("_")
df["amount_inr"] = pd.to_numeric(df["amount_inr"].astype(str).str.replace(r"[₹,\s]", "", regex=True), errors="coerce")
df["invoice_date"] = pd.to_datetime(df["invoice_date"], dayfirst=True, errors="coerce")
df["customer"] = df["customer"].astype(str).str.strip().str.title()
df["city"] = df["city"].replace({"Bombay": "Mumbai", "Bangalore": "Bengaluru", "Dilli": "Delhi", "Gurgaon": "Gurugram"})
df["pan"] = df["remarks"].astype(str).str.extract(r"([A-Z]{5}\d{4}[A-Z])", expand=False)
df["mobile"] = df["remarks"].astype(str).str.extract(r"([6-9]\d{9})", expand=False)
bad_dates = df[df["invoice_date"].isna()]
dupes = df[df.duplicated("invoice_no", keep=False)]
df = df.drop_duplicates("invoice_no", keep="last")

print("rows:", len(df), "| bad dates:", len(bad_dates), "| duplicate invoice numbers:", len(dupes))
with pd.ExcelWriter("clean.xlsx") as xl:
    df.to_excel(xl, sheet_name="Clean", index=False)
    bad_dates.to_excel(xl, sheet_name="Check_dates", index=False)
    dupes.to_excel(xl, sheet_name="Check_duplicates", index=False)
print("Saved clean.xlsx")