import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
from sklearn.datasets import make_classification, make_regression
from sklearn.preprocessing import MinMaxScaler

def generate_sales_data(n_rows=1000):
    """Generate synthetic sales data using scikit-learn."""
    
    # Generate synthetic features using make_classification for categorical-like data
    X_cat, _ = make_classification(n_samples=n_rows, n_features=2, n_informative=2, 
                                 n_redundant=0, n_classes=5, random_state=42)
    
    # Generate synthetic features using make_regression for numerical data
    X_num, _ = make_regression(n_samples=n_rows, n_features=2, random_state=42)
    
    # Scale numerical features to reasonable ranges
    scaler = MinMaxScaler()
    X_num_scaled = scaler.fit_transform(X_num)
    
    # Generate dates
    start_date = datetime(2022, 1, 1)
    dates = [start_date + timedelta(days=i % 365) for i in range(n_rows)]
    dates = [d.strftime('%Y-%m-%d') for d in dates]
    
    # Generate IDs
    order_ids = [f'ORD-{i+1:05d}' for i in range(n_rows)]
    customer_ids = [f'CUST-{random.randint(1000, 9999)}' for _ in range(n_rows)]
    product_ids = [f'PROD-{random.randint(100, 999)}' for _ in range(n_rows)]
    
    # Create the dataframe
    sales_df = pd.DataFrame({
        'OrderID': order_ids,
        'Date': dates,
        'CustomerID': customer_ids,
        'ProductID': product_ids,
        'Quantity': np.round(X_cat[:, 0] * 5 + 5).astype(int),  # Scale to 1-10 range
        'Price': np.round(X_num_scaled[:, 0] * 990 + 10, 2),    # Scale to 10-1000 range
        'Cost': np.round(X_num_scaled[:, 1] * 500 + 5, 2)       # Scale to 5-505 range
    })
    
    # Add some realistic data quality issues
    # 1. Missing values
    mask = np.random.random(n_rows) < 0.05  # 5% missing rate
    sales_df.loc[mask, 'Price'] = np.nan
    
    mask = np.random.random(n_rows) < 0.07  # 7% missing rate
    sales_df.loc[mask, 'Cost'] = np.nan
    
    # 2. Duplicate orders (10%)
    n_duplicates = int(n_rows * 0.1)
    duplicate_indices = np.random.choice(n_rows, n_duplicates, replace=False)
    original_indices = np.random.choice(n_rows, n_duplicates, replace=False)
    sales_df.iloc[duplicate_indices] = sales_df.iloc[original_indices]
    
    # 3. Inconsistent case in ProductIDs (10%)
    mask = np.random.random(n_rows) < 0.1
    sales_df.loc[mask, 'ProductID'] = sales_df.loc[mask, 'ProductID'].str.lower()
    
    return sales_df

def generate_sql_query():
    """Generate a sample inefficient SQL query that could be optimized."""
    return """
    WITH customer_stats AS (
        SELECT 
            customer_id,
            COUNT(*) as order_count,
            SUM(total_amount) as total_spent,
            MAX(order_date) as latest_order_date,
            AVG(items_per_order) as avg_items
        FROM orders o
        JOIN (
            SELECT order_id, COUNT(*) as items_per_order
            FROM order_items
            GROUP BY order_id
        ) oi ON o.order_id = oi.order_id
        WHERE order_date >= '2022-01-01'
        GROUP BY customer_id
    )
    SELECT 
        c.customer_name,
        c.customer_id,
        cs.total_spent,
        cs.order_count,
        cs.latest_order_date,
        cs.avg_items as avg_items_per_order
    FROM customers c
    JOIN customer_stats cs ON c.customer_id = cs.customer_id
    JOIN customer_types ct ON c.customer_type = ct.type_id
    LEFT JOIN returns r ON c.customer_id = r.customer_id
    WHERE 
        ct.type_name = 'Premium'
        AND NOT EXISTS (
            SELECT 1 
            FROM blacklist b 
            WHERE b.customer_id = c.customer_id
        )
        AND cs.order_count > 5
    ORDER BY cs.total_spent DESC;
    """

if __name__ == "__main__":
    # Generate and save sample data
    sales_df = generate_sales_data()
    sales_df.to_csv('sample_sales_data.csv', index=False)
    
    print("Sample data generated and saved to 'sample_sales_data.csv'")
    print("Sample SQL query is available via the generate_sql_query() function")