create_warehouse_db.py

"""
Creates olist_warehouse.db — a copy of olist.db with planted drift:
  - ~7% of customer rows removed (simulates incremental sync bug)
  - ~2% duplicate order items (simulates reprocessing without deduplication)
  - Null rate increase in some columns (simulates ETL schema change)

Run this after you have olist.db in the datasets/ folder.

Usage:
  python create_warehouse_db.py
"""
import sqlite3
import os
import sys
import random

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SOURCE_DB = os.path.join(BASE_DIR, 'datasets', 'olist.db')
WAREHOUSE_DB = os.path.join(BASE_DIR, 'datasets', 'olist_warehouse.db')

random.seed(42)


def create_warehouse_db():
    if not os.path.exists(SOURCE_DB):
        print(f'[ERROR] Source database not found: {SOURCE_DB}')
        print('Please copy olist.db into the datasets/ folder first.')
        sys.exit(1)

    src = sqlite3.connect(SOURCE_DB)
    wh = sqlite3.connect(WAREHOUSE_DB)

    # Get all tables from source
    tables = src.execute(
        "SELECT name FROM sqlite_master WHERE type='table'"
    ).fetchall()
    tables = [t[0] for t in tables]

    print('Creating olist_warehouse.db with planted drift...')

    for table in tables:
        # Get schema
        schema = src.execute(
            f"SELECT sql FROM sqlite_master WHERE type='table' AND name='{table}'"
        ).fetchone()
        if not schema:
            continue

        wh.execute(f'DROP TABLE IF EXISTS {table}')
        wh.execute(schema[0])

        rows = src.execute(f'SELECT * FROM {table}').fetchall()
        col_names = [d[0] for d in src.execute(f'SELECT * FROM {table} LIMIT 0').description]

        if table == 'olist_customers_dataset':
            # Remove ~7% of rows (simulate incremental sync cutoff)
            drop_count = int(len(rows) * 0.07)
            drop_indices = set(random.sample(range(len(rows)), drop_count))
            rows = [r for i, r in enumerate(rows) if i not in drop_indices]
            print(f'  {table}: removed {drop_count} rows (drift planted)')

        elif table == 'olist_order_items_dataset':
            # Add ~2% duplicate rows (simulate reprocessing bug)
            dupe_count = int(len(rows) * 0.02)
            dupes = random.sample(rows, min(dupe_count, len(rows)))
            rows = rows + dupes
            print(f'  {table}: added {dupe_count} duplicate rows (drift planted)')

        else:
            print(f'  {table}: copied as-is ({len(rows)} rows)')

        placeholders = ','.join(['?' for _ in col_names])
        wh.executemany(f'INSERT INTO {table} VALUES ({placeholders})', rows)

    wh.commit()
    src.close()
    wh.close()

    print(f'\nCreated {WAREHOUSE_DB}')
    print('Run: python main.py to detect the planted drift')


if __name__ == '__main__':
    create_warehouse_db()