-- ============================================================
-- clear-db-old.sql — DROP ALL TABLES of the PRE-SKU-SPLIT database
--
-- This targets a database migrated only up to:
--   0000_nifty_sauron.sql
--   0001_migrate_product_slots.sql
-- (i.e. 0002_sku_split.sql has NOT been applied)
--
-- In this state there are NO product_skus / sku_features /
-- product_market_stats / product_combos tables, and product_slots
-- has already been dropped by 0001 (hence its IF EXISTS below).
--
-- Usage:
--   sqlite3 <your.db> < scripts/clear-db-old.sql
--   wrangler d1 execute <db-name> --local  --file scripts/clear-db-old.sql
--   wrangler d1 execute <db-name> --remote --file scripts/clear-db-old.sql
--
-- WARNING: This DROPS every table in the database. There is no undo.
-- ============================================================

-- Don't enforce FK constraints while dropping (ordering below is
-- child-first anyway, so this is belt-and-braces).
PRAGMA defer_foreign_keys = on;
PRAGMA foreign_keys = OFF;

-- ---- leaf tables (no inbound references) ----
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS cart_items;
DROP TABLE IF EXISTS special_deals;
DROP TABLE IF EXISTS coupon_applicable_products;
DROP TABLE IF EXISTS product_reviews;
DROP TABLE IF EXISTS product_tags;
DROP TABLE IF EXISTS product_group_membership;

-- ---- product hierarchy (old flat product_info) ----
DROP TABLE IF EXISTS product_slots;
DROP TABLE IF EXISTS product_info;
DROP TABLE IF EXISTS store_info;

-- ---- orders & dependents ----
DROP TABLE IF EXISTS coupon_usage;
DROP TABLE IF EXISTS order_status;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS refunds;
DROP TABLE IF EXISTS complaints;
DROP TABLE IF EXISTS user_incidents;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS payment_info;
DROP TABLE IF EXISTS coupon_applicable_users;

-- ---- coupons & snippets ----
DROP TABLE IF EXISTS coupons;
DROP TABLE IF EXISTS reserved_coupons;
DROP TABLE IF EXISTS vendor_snippets;
DROP TABLE IF EXISTS delivery_slot_info;

-- ---- content/catalog ----
DROP TABLE IF EXISTS home_banners;
DROP TABLE IF EXISTS product_group_info;
DROP TABLE IF EXISTS product_tag_info;
DROP TABLE IF EXISTS product_categories;
DROP TABLE IF EXISTS units;
DROP TABLE IF EXISTS upload_url_status;

-- ---- notifications / misc ----
DROP TABLE IF EXISTS user_notifications;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS notif_creds;
DROP TABLE IF EXISTS key_val_store;

-- ---- addresses ----
DROP TABLE IF EXISTS addresses;
DROP TABLE IF EXISTS address_areas;
DROP TABLE IF EXISTS address_zones;

-- ---- users ----
DROP TABLE IF EXISTS unlogged_user_tokens;
DROP TABLE IF EXISTS user_creds;
DROP TABLE IF EXISTS user_details;
DROP TABLE IF EXISTS users;

-- ---- staff ----
DROP TABLE IF EXISTS staff_role_permissions;
DROP TABLE IF EXISTS staff_users;
DROP TABLE IF EXISTS staff_roles;
DROP TABLE IF EXISTS staff_permissions;

-- (sqlite_sequence is internal; SQLite drops it automatically once the last
--  AUTOINCREMENT table is gone)

PRAGMA foreign_keys = ON;
PRAGMA defer_foreign_keys = off;

-- Optionally reclaim disk space (sqlite3 CLI only; not supported in D1):
-- VACUUM;