import os
from glob import glob
import pandas as pd

# Function to detect schema drift across multiple CSV files
def detect_schema_drift(file_paths):
    schema_dict = {}
    
    for file in file_paths:
        df = pd.read_csv(file)  # Read only headers
        schema_dict[file] = set(df.columns)  # Store column names as a set
        print(df.describe())  # Print DataFrame info for debugging
    # Identify all unique columns across all files
    all_columns = set().union(*schema_dict.values())

    # Create a DataFrame showing which files contain which columns
    schema_comparison = pd.DataFrame(index=file_paths, columns=sorted(all_columns))
    
    for file, columns in schema_dict.items():
        for col in all_columns:
            schema_comparison.loc[file, col] = "✅" if col in columns else "❌"

    return schema_comparison

def dynamically_standardize_schema(file_paths):
    # Extract all unique column names across all files
    all_columns = set()
    
    for file in file_paths:
        df = pd.read_csv(file, nrows=0)  # Read only headers
        all_columns.update(df.columns)  # Collect all unique column names

    # Convert to sorted list for consistent ordering
    standard_schema = sorted(all_columns)

    standardized_dfs = []

    for file in file_paths:
        df = pd.read_csv(file)

        # Rename common schema drifts
        rename_mapping = {
            "sales_date": "date_sold",
            # Example: Standardizing column names dynamically
        }
        df.rename(columns=rename_mapping, inplace=True)

        # Ensure all columns exist and fill missing ones with None
        for col in standard_schema:
            if col not in df.columns:
                df[col] = None

        # Ensure correct column order
        df = df[standard_schema]

        # Convert common data types for consistency
        if "order_id" in df.columns:
            df["order_id"] = df["order_id"].astype(str)  # Convert order_id to string

        standardized_dfs.append(df)

    # Concatenate all cleaned DataFrames into one
    final_df = pd.concat(standardized_dfs, ignore_index=True)
    
    return final_df

file_paths = glob('YourFilePath/sales*complex.csv')
# Detect schema drift across the historical sales files

schema_drift_df = detect_schema_drift(file_paths)
print(schema_drift_df)
schema_drift_df.to_excel('YourFilePath/schema_drift_complex3.xlsx')


# Dynamically detect schema and standardize the data
cleaned_dynamic_data = dynamically_standardize_schema(file_paths)
cleaned_dynamic_data.to_csv('YourFilePath/cleaned_dynamic_data_complex.csv', index=False)