scripts/setup_database.py

import sqlite3
import pandas as pd
import os
from pathlib import Path

RAW = Path("data/raw")
DB = Path("data/olist.db")

TABLE_MAP = {
    "olist_customers_dataset.csv": "customers",
    "olist_orders_dataset.csv": "orders",
    "olist_order_items_dataset.csv": "order_items",
    "olist_order_payments_dataset.csv": "order_payments",
    "olist_order_reviews_dataset.csv": "order_reviews",
    "olist_products_dataset.csv": "products",
    "olist_sellers_dataset.csv": "sellers",
    "olist_geolocation_dataset.csv": "geolocation",
    "product_category_name_translation.csv": "category_translation",
}

def load_tables(conn):
    """Load CSV files into SQLite tables"""
    for filename, table in TABLE_MAP.items():
        filepath = RAW / filename
        if not filepath.exists():
            print(f"  ⚠ Skipping {table} - file not found: {filepath}")
            continue
        
        df = pd.read_csv(filepath, low_memory=False)
        df.to_sql(table, conn, if_exists="replace", index=False)
        print(f"  ✓ Loaded {table} ({len(df)} rows)")

def create_indexes(conn):
    """Create indexes for faster queries"""
    indexes = [
        "CREATE INDEX IF NOT EXISTS idx_orders_id ON orders(order_id)",
        "CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id)",
        "CREATE INDEX IF NOT EXISTS idx_items_order ON order_items(order_id)",
        "CREATE INDEX IF NOT EXISTS idx_items_product ON order_items(product_id)",
        "CREATE INDEX IF NOT EXISTS idx_items_seller ON order_items(seller_id)",
        "CREATE INDEX IF NOT EXISTS idx_reviews_order ON order_reviews(order_id)",
        "CREATE INDEX IF NOT EXISTS idx_payments_order ON order_payments(order_id)",
    ]
    for idx in indexes:
        conn.execute(idx)
    print("  ✓ Indexes created")

def create_views(conn):
    """Create composite views for common queries"""
    # Use dataset reference date (2018-09-03 - last order date in dataset)
    DEMO_DATE = '2018-09-03'
    
    # Main composite view — single query for full order info
    conn.execute(f"""
    CREATE VIEW IF NOT EXISTS v_order_full AS
    SELECT
        o.order_id,
        o.customer_id,
        o.order_status,
        o.order_purchase_timestamp,
        o.order_approved_at,
        o.order_delivered_carrier_date,
        o.order_delivered_customer_date,
        o.order_estimated_delivery_date,
        ROUND(SUM(oi.price + oi.freight_value), 2) AS total_amount,
        COUNT(oi.order_item_id) AS item_count,
        GROUP_CONCAT(DISTINCT oi.seller_id) AS seller_ids,
        GROUP_CONCAT(DISTINCT p.payment_type) AS payment_methods,
        CASE
            WHEN o.order_delivered_customer_date IS NOT NULL
             AND o.order_delivered_customer_date > o.order_estimated_delivery_date
            THEN 1
            WHEN o.order_delivered_customer_date IS NULL
             AND o.order_status NOT IN ('delivered', 'canceled')
             AND DATE('{DEMO_DATE}') > DATE(o.order_estimated_delivery_date)
            THEN 1
            ELSE 0
        END AS is_late,
        CASE
            WHEN o.order_delivered_customer_date IS NOT NULL AND o.order_delivered_customer_date > o.order_estimated_delivery_date
            THEN ROUND(JULIANDAY(o.order_delivered_customer_date) - JULIANDAY(o.order_estimated_delivery_date), 1)
            WHEN o.order_delivered_customer_date IS NULL AND o.order_status NOT IN ('delivered', 'canceled')
            THEN ROUND(JULIANDAY('{DEMO_DATE}') - JULIANDAY(o.order_estimated_delivery_date), 1)
            ELSE 0
        END AS days_overdue
    FROM orders o
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN order_payments p ON o.order_id = p.order_id
    GROUP BY o.order_id
    """)

    # Seller performance view
    conn.execute("""
    CREATE VIEW IF NOT EXISTS v_seller_summary AS
    SELECT
        oi.seller_id,
        s.seller_city,
        s.seller_state,
        COUNT(DISTINCT oi.order_id) AS total_orders,
        ROUND(AVG(r.review_score), 2) AS avg_rating,
        SUM(CASE WHEN r.review_score <= 2 THEN 1 ELSE 0 END) AS negative_reviews,
        SUM(CASE WHEN r.review_score >= 4 THEN 1 ELSE 0 END) AS positive_reviews
    FROM order_items oi
    JOIN sellers s ON oi.seller_id = s.seller_id
    LEFT JOIN order_reviews r ON oi.order_id = r.order_id
    GROUP BY oi.seller_id
    """)

    print("  ✓ Views created")

if __name__ == "__main__":
    print("Setting up Olist database...")
    
    # Create data directory if it doesn't exist
    DB.parent.mkdir(parents=True, exist_ok=True)
    
    conn = sqlite3.connect(DB)
    
    try:
        load_tables(conn)
        create_indexes(conn)
        create_views(conn)
        conn.commit()
        print(f"\n✅ Database ready at {DB}")
    except Exception as e:
        print(f"\n❌ Error: {e}")
        conn.rollback()
    finally:
        conn.close()