"""
clean_csv.py - tidy up a messy CSV export in one pass.

Usage:
    python clean_csv.py input.csv                 # writes input_clean.csv
    python clean_csv.py input.csv -o tidy.csv     # choose the output name
"""
import argparse
import re
from pathlib import Path

import pandas as pd


def clean_header(name: str) -> str:
    """'  Order Date (UTC) ' -> 'order_date_utc'"""
    name = re.sub(r"[^0-9a-zA-Z]+", "_", str(name).strip().lower())
    return name.strip("_") or "column"


def clean(df: pd.DataFrame) -> pd.DataFrame:
    # 1. Consistent, code-friendly column names
    df.columns = [clean_header(c) for c in df.columns]

    # 2. Trim stray whitespace and turn blank strings into real missing values
    for col in df.select_dtypes(include="object"):
        df[col] = df[col].str.strip().replace("", pd.NA)

    # 3. Drop rows and columns that are completely empty
    df = df.dropna(how="all").dropna(axis=1, how="all")

    # 4. Remove exact duplicate rows
    df = df.drop_duplicates()

    # 5. Convert text columns that are really numbers or dates
    for col in df.select_dtypes(include="object"):
        as_num = pd.to_numeric(df[col].str.replace(r"[,$%]", "", regex=True), errors="coerce")
        if as_num.notna().sum() >= 0.9 * df[col].notna().sum():
            df[col] = as_num
            continue
        as_date = pd.to_datetime(df[col], errors="coerce", format="mixed")
        if as_date.notna().sum() >= 0.9 * df[col].notna().sum():
            df[col] = as_date

    return df.reset_index(drop=True)


def main() -> None:
    parser = argparse.ArgumentParser(description="Clean a messy CSV file.")
    parser.add_argument("input", type=Path)
    parser.add_argument("-o", "--output", type=Path)
    args = parser.parse_args()

    out = args.output or args.input.with_name(f"{args.input.stem}_clean.csv")

    raw = pd.read_csv(args.input, dtype=str, keep_default_na=False)
    tidy = clean(raw.copy())
    tidy.to_csv(out, index=False)

    print(f"Rows:    {len(raw):>6} -> {len(tidy)}")
    print(f"Columns: {raw.shape[1]:>6} -> {tidy.shape[1]}")
    print("Types:")
    for col, dtype in tidy.dtypes.items():
        print(f"  {col:<24} {dtype}")
    print(f"Saved to {out}")


if __name__ == "__main__":
    main()