import os
import pandas as pd
import numpy as np

def clean_data():
    raw_path = os.path.join("Dataset", "DataCoSupplyChainDataset.csv")
    cleaned_dir = "Data"
    os.makedirs(cleaned_dir, exist_ok=True)
    cleaned_path = os.path.join(cleaned_dir, "cleaned_data.csv")
    
    print(f"Reading raw dataset from {raw_path}...")
    # Read raw dataset with latin-1 encoding
    df = pd.read_csv(raw_path, encoding="latin-1")
    initial_shape = df.shape
    print(f"Initial shape: {initial_shape}")
    
    # 1. Clean Column Names
    # Convert to lowercase, replace space/hyphen/brackets with underscore
    df.columns = (
        df.columns.str.strip()
        .str.lower()
        .str.replace(" ", "_")
        .str.replace("(", "")
        .str.replace(")", "")
        .str.replace("-", "_")
    )
    
    # Rename specifically confusing names
    df = df.rename(columns={
        "order_date_dateorders": "order_date",
        "shipping_date_dateorders": "shipping_date",
        "days_for_shipping_real": "days_for_shipping_real",
        "days_for_shipment_scheduled": "days_for_shipping_scheduled"
    })
    
    print("Column names cleaned and standardized.")
    
    # 2. Handle Redundant, Private, or Uninformative Columns
    # E.g., customer_email, customer_password, product_description (all nulls/masked)
    cols_to_drop = [
        "customer_email", 
        "customer_password", 
        "product_description", 
        "product_image"
    ]
    # Check if they exist in the dataframe before dropping
    cols_to_drop = [col for col in cols_to_drop if col in df.columns]
    if cols_to_drop:
        df = df.drop(columns=cols_to_drop)
        print(f"Dropped redundant/uninformative columns: {cols_to_drop}")
    
    # 3. Handle Missing Values
    # Check null percentages
    null_counts = df.isnull().sum()
    null_cols = null_counts[null_counts > 0]
    print(f"Columns with missing values:\n{null_cols}")
    
    # Fill customer_lname with empty string
    if "customer_lname" in df.columns:
        df["customer_lname"] = df["customer_lname"].fillna("")
        print("Filled missing values in 'customer_lname' with empty string.")
        
    # Fill order_zipcode and customer_zipcode
    for zip_col in ["customer_zipcode", "order_zipcode"]:
        if zip_col in df.columns:
            # Convert float zips (like 725.0) to string and remove .0
            df[zip_col] = df[zip_col].astype(str).str.replace(".0", "", regex=False)
            df[zip_col] = df[zip_col].replace("nan", "Unknown")
            print(f"Formatted and filled missing values in '{zip_col}' with 'Unknown'.")
            
    # 4. Correct Data Types
    # Dates to datetime format
    for date_col in ["order_date", "shipping_date"]:
        if date_col in df.columns:
            df[date_col] = pd.to_datetime(df[date_col], errors="coerce")
            print(f"Converted '{date_col}' to datetime format.")
            
    # Check duplicates
    duplicate_count = df.duplicated().sum()
    if duplicate_count > 0:
        df = df.drop_duplicates()
        print(f"Dropped {duplicate_count} duplicate rows.")
    else:
        print("No duplicate rows found.")
        
    final_shape = df.shape
    print(f"Final shape after cleaning: {final_shape}")
    print(f"Saving cleaned dataset to {cleaned_path}...")
    df.to_csv(cleaned_path, index=False, encoding="utf-8")
    print("Data cleaning completed successfully!")

if __name__ == "__main__":
    clean_data()