import os import sqlite3 import pandas as pd import yaml DB_PATH = "olist.sqlite" def initialize_db(): if os.path.exists(DB_PATH): print("✅ DB already exists, skipping rebuild.") return conn = sqlite3.connect(DB_PATH) csv_files = { "orders": "data/olist_orders_dataset.csv", "order_items": "data/olist_order_items_dataset.csv", "order_payments": "data/olist_order_payments_dataset.csv", "order_reviews": "data/olist_order_reviews_dataset.csv", "customers": "data/olist_customers_dataset.csv", "products": "data/olist_products_dataset.csv", "sellers": "data/olist_sellers_dataset.csv", "geolocation": "data/olist_geolocation_dataset.csv", "product_category_name_translation": "data/product_category_name_translation.csv" } for table, path in csv_files.items(): df = pd.read_csv(path) df.to_sql(table, conn, if_exists="replace", index=False) # Users table conn.execute(""" CREATE TABLE IF NOT EXISTS users ( user_id TEXT PRIMARY KEY, email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL, customer_id TEXT, role TEXT CHECK(role IN ('user','admin')) NOT NULL ); """) conn.execute("INSERT OR IGNORE INTO users VALUES ('U001','alice@example.com','hashed_pw1','CUST12345','user');") conn.execute("INSERT OR IGNORE INTO users VALUES ('A001','admin@example.com','hashed_pw_admin',NULL,'admin');") conn.commit() conn.close() print("✅ DB initialized")