feature_engineering.py

# feature_engineering.py
import sqlite3
import pandas as pd

def extract_and_engineer_features():
    conn = sqlite3.connect('store_data.db')
    query = "SELECT date, sku, units_sold FROM sales_history ORDER BY date ASC"
    df = pd.read_sql_query(query, conn)
    conn.close()
    
    # Ensure explicit chronological datetime ordering
    df['date'] = pd.to_datetime(df['date'])
    df = df.sort_values('date').reset_index(drop=True)
    
    # Calendar features
    df['day_of_week'] = df['date'].dt.weekday
    df['month'] = df['date'].dt.month
    df['is_weekend'] = df['day_of_week'].isin([5, 6]).astype(int)
    
    # Lag Features (What did we sell x days ago?)
    df['sales_lag_7'] = df['units_sold'].shift(7)
    df['sales_lag_14'] = df['units_sold'].shift(14)
    
    # Rolling Statistics (Moving windows)
    df['rolling_mean_7'] = df['units_sold'].shift(1).rolling(window=7).mean()
    df['rolling_mean_14'] = df['units_sold'].shift(1).rolling(window=14).mean()
    
    # Drop rows containing NaN values generated from chronological shifting steps
    df = df.dropna().reset_index(drop=True)
    
    return df

if __name__ == '__main__':
    data = extract_and_engineer_features()
    print("✅ Phase 2 Complete: Features engineered.")
    print(data[['date', 'units_sold', 'rolling_mean_7', 'sales_lag_7']].tail())