-- ============================================================================= -- MySQL Database Schema -- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci -- ============================================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET time_zone = '+07:00'; -- WIB -- ============================================================================= -- DROP ORDER (reverse FK dependency) -- ============================================================================= DROP TABLE IF EXISTS contact_messages; DROP TABLE IF EXISTS newsletter_subscribers; DROP TABLE IF EXISTS product_reviews; DROP TABLE IF EXISTS voucher_usages; DROP TABLE IF EXISTS order_items; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS cart_items; DROP TABLE IF EXISTS carts; DROP TABLE IF EXISTS product_variant_stock; DROP TABLE IF EXISTS product_variants; DROP TABLE IF EXISTS product_images; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS subcategories; DROP TABLE IF EXISTS categories; DROP TABLE IF EXISTS shipping_zone_methods; DROP TABLE IF EXISTS shipping_zones; DROP TABLE IF EXISTS shipping_methods; DROP TABLE IF EXISTS vouchers; DROP TABLE IF EXISTS cities; DROP TABLE IF EXISTS provinces; DROP TABLE IF EXISTS password_resets; DROP TABLE IF EXISTS users; -- ============================================================================= -- 1. USERS -- Sources: register.php, login.php, header.php (Masuk / Wishlist actions) -- ============================================================================= CREATE TABLE users ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, first_name VARCHAR(80) NOT NULL, last_name VARCHAR(80) DEFAULT NULL, email VARCHAR(191) NOT NULL, phone VARCHAR(30) DEFAULT NULL COMMENT 'WhatsApp number', password_hash VARCHAR(255) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, newsletter TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Opted-in via register or footer form', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Registered customer accounts'; -- ============================================================================= -- 2. PASSWORD RESETS -- Sources: lost-password.php → recovery-sent.php → password-updated.php -- 6-digit OTP, 60-second resend window, 24-hour expiry implied by UX copy -- ============================================================================= CREATE TABLE password_resets ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, otp_code CHAR(6) NOT NULL COMMENT '6-digit numeric OTP', is_used TINYINT(1) NOT NULL DEFAULT 0, expires_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_pr_user (user_id), KEY idx_pr_expires (expires_at), CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 3. PROVINCES -- Sources: shipping.php (provinsi field fixed to "Jawa Timur"), -- shipping-info.php (3 shipping zones mention region names) -- ============================================================================= CREATE TABLE provinces ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(100) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uq_provinces_name (name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 4. CITIES -- Sources: config.php CITIES_JAWA_TIMUR constant (15 cities) -- ============================================================================= CREATE TABLE cities ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, province_id SMALLINT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, postal_code VARCHAR(10) DEFAULT NULL, PRIMARY KEY (id), KEY idx_cities_province (province_id), CONSTRAINT fk_cities_province FOREIGN KEY (province_id) REFERENCES provinces (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 5. SHIPPING METHODS -- Sources: shipping.php ($shipping_rates), shipping-info.php ($shipping_options) -- ============================================================================= CREATE TABLE shipping_methods ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(30) NOT NULL COMMENT 'reguler | express | sameday', name VARCHAR(80) NOT NULL, courier VARCHAR(100) NOT NULL COMMENT 'e.g. JNE, J&T Express', eta_label VARCHAR(60) NOT NULL COMMENT 'Human-readable ETA', base_cost INT UNSIGNED NOT NULL COMMENT 'IDR, per order', is_active TINYINT(1) NOT NULL DEFAULT 1, PRIMARY KEY (id), UNIQUE KEY uq_sm_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 6. SHIPPING ZONES -- Sources: shipping-info.php (Jawa & Bali | Sumatera & Kalimantan | -- Sulawesi, NTT & Papua) -- ============================================================================= CREATE TABLE shipping_zones ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(80) NOT NULL, description TEXT DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- pivot: which methods are available per zone CREATE TABLE shipping_zone_methods ( zone_id TINYINT UNSIGNED NOT NULL, method_id TINYINT UNSIGNED NOT NULL, PRIMARY KEY (zone_id, method_id), CONSTRAINT fk_szm_zone FOREIGN KEY (zone_id) REFERENCES shipping_zones (id), CONSTRAINT fk_szm_method FOREIGN KEY (method_id) REFERENCES shipping_methods (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 7. CATEGORIES (top-level) -- Sources: category.php $category_map & $kategori_tree keys, -- header.php nav links, footer.php category links -- ============================================================================= CREATE TABLE categories ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(40) NOT NULL, name VARCHAR(80) NOT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_cat_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 8. SUBCATEGORIES -- Sources: category.php $kategori_tree values -- ============================================================================= CREATE TABLE subcategories ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, category_id TINYINT UNSIGNED NOT NULL, name VARCHAR(80) NOT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (id), KEY idx_subcat_cat (category_id), CONSTRAINT fk_subcat_cat FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 9. PRODUCTS -- Sources: product.php $product, home.php $produk_terbaru & $produk_terlaris, -- category.php $produk, cart.php & checkout.php $cart_items, -- order-success.php $items -- ============================================================================= CREATE TABLE products ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, category_id TINYINT UNSIGNED NOT NULL, subcategory_id SMALLINT UNSIGNED DEFAULT NULL, name VARCHAR(191) NOT NULL, slug VARCHAR(220) NOT NULL, description TEXT DEFAULT NULL, price INT UNSIGNED NOT NULL COMMENT 'Current selling price in IDR', old_price INT UNSIGNED DEFAULT NULL COMMENT 'Original price before discount', is_active TINYINT(1) NOT NULL DEFAULT 1, is_featured TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Shown in best-sellers / homepage', is_new_arrival TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Shown in new-arrivals carousel', is_sale TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Shown in sale category', rating_avg DECIMAL(2,1) DEFAULT NULL, review_count SMALLINT UNSIGNED NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_products_slug (slug), KEY idx_products_category (category_id), KEY idx_products_subcat (subcategory_id), KEY idx_products_sale (is_sale), KEY idx_products_featured (is_featured), CONSTRAINT fk_products_cat FOREIGN KEY (category_id) REFERENCES categories (id), CONSTRAINT fk_products_subcat FOREIGN KEY (subcategory_id) REFERENCES subcategories (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 10. PRODUCT IMAGES -- Sources: product.php $product['images'] (4-image gallery) -- ============================================================================= CREATE TABLE product_images ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, product_id INT UNSIGNED NOT NULL, url VARCHAR(500) NOT NULL, alt_text VARCHAR(191) DEFAULT NULL, sort_order TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0 = primary / thumbnail', PRIMARY KEY (id), KEY idx_pi_product (product_id), CONSTRAINT fk_pi_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 11. PRODUCT VARIANTS (size × color combinations) -- Sources: product.php $ukuran, $warna -- ============================================================================= CREATE TABLE product_variants ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, product_id INT UNSIGNED NOT NULL, size VARCHAR(10) DEFAULT NULL COMMENT 'S | M | L | XL | XXL | 40 | 41 …', color_hex CHAR(7) DEFAULT NULL COMMENT '#0d0d0d', color_name VARCHAR(40) DEFAULT NULL COMMENT 'Hitam, Putih, Biru …', sku VARCHAR(80) DEFAULT NULL, PRIMARY KEY (id), KEY idx_pv_product (product_id), CONSTRAINT fk_pv_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 12. PRODUCT VARIANT STOCK -- Sources: add-to-cart UX on product.php (qty input, stock implied) -- ============================================================================= CREATE TABLE product_variant_stock ( variant_id INT UNSIGNED NOT NULL, qty SMALLINT NOT NULL DEFAULT 0, PRIMARY KEY (variant_id), CONSTRAINT fk_pvs_variant FOREIGN KEY (variant_id) REFERENCES product_variants (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 13. PRODUCT REVIEWS -- Sources: product.php $product['rating'] (1–5), $product['reviews'] count, -- tabs "Ulasan (128)" -- ============================================================================= CREATE TABLE product_reviews ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, product_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = guest review', reviewer VARCHAR(80) NOT NULL, rating TINYINT UNSIGNED NOT NULL COMMENT '1–5', body TEXT DEFAULT NULL, is_approved TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_rev_product (product_id), KEY idx_rev_user (user_id), CONSTRAINT fk_rev_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE, CONSTRAINT fk_rev_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT chk_rev_rating CHECK (rating BETWEEN 1 AND 5) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 14. VOUCHERS -- Sources: cart.php $valid_vouchers (FORMA10 = 10%, FORMA20 = 20%) -- ============================================================================= CREATE TABLE vouchers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, code VARCHAR(30) NOT NULL, discount_type ENUM('percent','fixed') NOT NULL DEFAULT 'percent', discount_value DECIMAL(10,2) NOT NULL COMMENT '10.00 = 10% or Rp 10.000', min_purchase INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Minimum cart subtotal in IDR', max_uses INT UNSIGNED DEFAULT NULL COMMENT 'NULL = unlimited', used_count INT UNSIGNED NOT NULL DEFAULT 0, valid_from DATE DEFAULT NULL, valid_until DATE DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_voucher_code (code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 15. CARTS -- Sources: cart.php, header.php (cart count badge = 3) -- Supports both guest (session) and authenticated carts -- ============================================================================= CREATE TABLE carts ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = guest', session_id VARCHAR(128) DEFAULT NULL COMMENT 'PHP session ID for guests', voucher_id INT UNSIGNED DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_cart_user (user_id), KEY idx_cart_session (session_id), CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT fk_cart_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE cart_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, cart_id INT UNSIGNED NOT NULL, variant_id INT UNSIGNED NOT NULL, qty SMALLINT UNSIGNED NOT NULL DEFAULT 1, added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_cart_variant (cart_id, variant_id), CONSTRAINT fk_ci_cart FOREIGN KEY (cart_id) REFERENCES carts (id) ON DELETE CASCADE, CONSTRAINT fk_ci_variant FOREIGN KEY (variant_id) REFERENCES product_variants (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 16. ORDERS -- Sources: order-success.php $order, checkout.php review section -- Payment methods: transfer | card | ewallet | cod (payment.php) -- ============================================================================= CREATE TABLE orders ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, order_number VARCHAR(30) NOT NULL COMMENT 'FORMA-2026-XXXXX', user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = guest checkout', -- Snapshot shipping address (denormalised for immutability) recipient_name VARCHAR(160) NOT NULL, recipient_phone VARCHAR(30) NOT NULL, address_line TEXT NOT NULL, city_id SMALLINT UNSIGNED DEFAULT NULL, city_name VARCHAR(100) NOT NULL, province_name VARCHAR(100) NOT NULL, postal_code VARCHAR(10) DEFAULT NULL, -- Shipping shipping_method_id TINYINT UNSIGNED DEFAULT NULL, shipping_cost INT UNSIGNED NOT NULL DEFAULT 0, -- Payment payment_method ENUM('transfer','card','ewallet','cod') NOT NULL, payment_bank VARCHAR(20) DEFAULT NULL COMMENT 'BCA | BNI | Mandiri | BRI', -- Financials subtotal INT UNSIGNED NOT NULL, discount_amount INT UNSIGNED NOT NULL DEFAULT 0, total INT UNSIGNED NOT NULL, voucher_id INT UNSIGNED DEFAULT NULL, -- Status status ENUM('pending_payment','paid','processing','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending_payment', payment_deadline DATETIME DEFAULT NULL COMMENT '24h after order creation', tracking_number VARCHAR(60) DEFAULT NULL, notes TEXT DEFAULT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_order_number (order_number), KEY idx_orders_user (user_id), KEY idx_orders_status (status), KEY idx_orders_created (created_at), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT fk_orders_city FOREIGN KEY (city_id) REFERENCES cities (id) ON DELETE SET NULL, CONSTRAINT fk_orders_shipping FOREIGN KEY (shipping_method_id) REFERENCES shipping_methods (id) ON DELETE SET NULL, CONSTRAINT fk_orders_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 17. ORDER ITEMS -- Sources: order-success.php $items, checkout.php $cart_items -- Price is snapshotted at time of order (never linked back to live price) -- ============================================================================= CREATE TABLE order_items ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL if product deleted', variant_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL if variant deleted', -- Snapshots (protect against future price/name changes) product_name VARCHAR(191) NOT NULL, variant_label VARCHAR(80) DEFAULT NULL COMMENT 'e.g. Putih / L', unit_price INT UNSIGNED NOT NULL, qty SMALLINT UNSIGNED NOT NULL, subtotal INT UNSIGNED NOT NULL COMMENT 'unit_price × qty', PRIMARY KEY (id), KEY idx_oi_order (order_id), KEY idx_oi_product (product_id), CONSTRAINT fk_oi_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, CONSTRAINT fk_oi_product FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE SET NULL, CONSTRAINT fk_oi_variant FOREIGN KEY (variant_id) REFERENCES product_variants (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 18. VOUCHER USAGES (audit trail; vouchers.used_count is the fast counter) -- ============================================================================= CREATE TABLE voucher_usages ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, voucher_id INT UNSIGNED NOT NULL, order_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED DEFAULT NULL, used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_vu_order (order_id), KEY idx_vu_voucher (voucher_id), CONSTRAINT fk_vu_voucher FOREIGN KEY (voucher_id) REFERENCES vouchers (id), CONSTRAINT fk_vu_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, CONSTRAINT fk_vu_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 19. NEWSLETTER SUBSCRIBERS -- Sources: footer.php newsletter form, register.php newsletter checkbox -- ============================================================================= CREATE TABLE newsletter_subscribers ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(191) NOT NULL, user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = subscribed as guest', is_active TINYINT(1) NOT NULL DEFAULT 1, subscribed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_ns_email (email), CONSTRAINT fk_ns_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- 20. CONTACT MESSAGES -- Sources: contact.php form fields (topik, no_pesanan, pesan) -- ============================================================================= CREATE TABLE contact_messages ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED DEFAULT NULL COMMENT 'NULL = not logged in', name VARCHAR(160) NOT NULL, email VARCHAR(191) NOT NULL, topic ENUM('pesanan','pengiriman','retur','produk','voucher','lainnya') DEFAULT 'lainnya', order_number VARCHAR(30) DEFAULT NULL, message TEXT NOT NULL, is_resolved TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_cm_user (user_id), KEY idx_cm_created (created_at), CONSTRAINT fk_cm_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================= -- ============================================================================= -- SEED DATA -- ============================================================================= -- ============================================================================= -- --------------------------------------------------------------------------- -- PROVINCES (only Jawa Timur is strictly needed; add common ones for realism) -- --------------------------------------------------------------------------- INSERT INTO provinces (name) VALUES ('DKI Jakarta'), ('Jawa Barat'), ('Jawa Tengah'), ('DI Yogyakarta'), ('Jawa Timur'), ('Bali'), ('Sumatera Utara'), ('Sumatera Selatan'), ('Kalimantan Timur'), ('Sulawesi Selatan'); -- --------------------------------------------------------------------------- -- CITIES — Jawa Timur (province_id = 5, from config.php CITIES_JAWA_TIMUR) -- --------------------------------------------------------------------------- INSERT INTO cities (province_id, name, postal_code) VALUES (5, 'Surabaya', '60111'), (5, 'Malang', '65111'), (5, 'Sidoarjo', '61211'), (5, 'Gresik', '61111'), (5, 'Mojokerto', '61311'), (5, 'Pasuruan', '67111'), (5, 'Probolinggo', '67211'), (5, 'Jember', '68111'), (5, 'Banyuwangi', '68411'), (5, 'Kediri', '64111'), (5, 'Madiun', '63111'), (5, 'Blitar', '66111'), (5, 'Tulungagung', '66211'), (5, 'Lamongan', '62211'), (5, 'Bojonegoro', '62111'); -- A few cities for other provinces (useful for zone testing) INSERT INTO cities (province_id, name, postal_code) VALUES (1, 'Jakarta Pusat', '10110'), (1, 'Jakarta Selatan','12110'), (6, 'Denpasar', '80111'), (7, 'Medan', '20111'), (10,'Makassar', '90111'); -- --------------------------------------------------------------------------- -- SHIPPING METHODS (from shipping.php $shipping_rates & shipping-info.php) -- --------------------------------------------------------------------------- INSERT INTO shipping_methods (slug, name, courier, eta_label, base_cost) VALUES ('reguler', 'Reguler', 'JNE, J&T Express', '3–5 Hari Kerja', 15000), ('express', 'Express', 'JNE YES, J&T Express','1–2 Hari Kerja', 35000), ('sameday', 'Same Day (Surabaya Only)', 'GoSend, GrabExpress', 'Hari yang Sama (≤12.00)',25000); -- --------------------------------------------------------------------------- -- SHIPPING ZONES (from shipping-info.php) -- --------------------------------------------------------------------------- INSERT INTO shipping_zones (name, description) VALUES ('Jawa & Bali', 'Layanan Reguler, Express, dan Same Day (khusus Surabaya).'), ('Sumatera & Kalimantan', 'Layanan Reguler tersedia ke seluruh kota. Express untuk kota-kota utama.'), ('Sulawesi, NTT & Papua', 'Layanan Reguler dengan estimasi 5–10 hari kerja tergantung lokasi tujuan.'); -- Jawa & Bali: all three methods INSERT INTO shipping_zone_methods (zone_id, method_id) VALUES (1,1),(1,2),(1,3); -- Sumatera & Kalimantan: reguler + express INSERT INTO shipping_zone_methods (zone_id, method_id) VALUES (2,1),(2,2); -- Sulawesi, NTT & Papua: reguler only INSERT INTO shipping_zone_methods (zone_id, method_id) VALUES (3,1); -- --------------------------------------------------------------------------- -- CATEGORIES (from category.php $category_map / nav links) -- --------------------------------------------------------------------------- INSERT INTO categories (slug, name, sort_order) VALUES ('wanita', 'Pakaian Wanita', 1), ('pria', 'Pakaian Pria', 2), ('sepatu', 'Sepatu', 3), ('tas', 'Tas & Aksesoris', 4), ('sale', 'Sale', 5); -- --------------------------------------------------------------------------- -- SUBCATEGORIES (from category.php $kategori_tree) -- --------------------------------------------------------------------------- -- Pakaian Wanita (cat 1) INSERT INTO subcategories (category_id, name, sort_order) VALUES (1,'Dress', 1),(1,'Atasan', 2),(1,'Bawahan', 3), (1,'Outerwear', 4),(1,'Setelan', 5); -- Pakaian Pria (cat 2) INSERT INTO subcategories (category_id, name, sort_order) VALUES (2,'Kemeja', 1),(2,'Kaos', 2),(2,'Celana', 3), (2,'Jaket', 4),(2,'Setelan Jas',5); -- Sepatu (cat 3) INSERT INTO subcategories (category_id, name, sort_order) VALUES (3,'Sneakers', 1),(3,'Heels', 2),(3,'Flat Shoes', 3), (3,'Boots', 4),(3,'Sandal', 5); -- Tas & Aksesoris (cat 4) INSERT INTO subcategories (category_id, name, sort_order) VALUES (4,'Tote Bag', 1),(4,'Backpack', 2),(4,'Dompet', 3), (4,'Topi', 4),(4,'Ikat Pinggang',5); -- --------------------------------------------------------------------------- -- PRODUCTS (id 1–10 from home.php; 11–18 from category.php; cross-referenced -- with cart.php, checkout.php, order-success.php) -- --------------------------------------------------------------------------- INSERT INTO products (id, category_id, subcategory_id, name, slug, description, price, old_price, is_active, is_featured, is_new_arrival, is_sale, rating_avg, review_count) VALUES -- id 1 — Kemeja Linen (product.php detail page + cart + order) (1, 2, 6, 'Kemeja Linen Lengan Panjang', 'kemeja-linen-lengan-panjang', 'Kemeja berbahan linen premium dengan potongan regular fit, nyaman dipakai untuk aktivitas formal maupun santai sepanjang hari.', 249000, 329000, 1, 0, 1, 1, 4.0, 128), -- id 2 — Dress Midi (2, 1, 1, 'Dress Midi Motif Bunga', 'dress-midi-motif-bunga', 'Dress midi dengan motif bunga elegan, cocok untuk berbagai kesempatan.', 329000, NULL, 1, 0, 1, 0, NULL, 0), -- id 3 — Sneakers (cart + order) (3, 3, 11, 'Sneakers Canvas Low', 'sneakers-canvas-low', 'Sneakers kanvas low-cut yang ringan dan stylish untuk aktivitas harian.', 459000, 599000, 1, 0, 1, 1, NULL, 0), -- id 4 — Tote Bag (cart + order, qty 2) (4, 4, 16, 'Tote Bag Kanvas Minimalis', 'tote-bag-kanvas-minimalis', 'Tote bag kanvas dengan desain minimalis, kapasitas besar dan tahan lama.', 189000, NULL, 1, 0, 1, 0, NULL, 0), -- id 5 — Kaos Polos (best-seller) (5, 2, 7, 'Kaos Polos Premium Cotton', 'kaos-polos-premium-cotton', 'Kaos polos 100% cotton combed 30s dengan jahitan rapi dan bahan anti-kusut.', 99000, NULL, 1, 1, 1, 0, NULL, 0), -- id 6 — Celana Chino (best-seller + related) (6, 2, 8, 'Celana Chino Slim Fit', 'celana-chino-slim-fit', 'Celana chino slim fit berbahan katun stretch, nyaman dan mudah dipadukan.', 279000, 349000, 1, 1, 1, 1, NULL, 0), -- id 7 — Heels (best-seller) (7, 3, 12, 'Heels Strap Klasik', 'heels-strap-klasik', 'Heels strap klasik dengan hak 7 cm, cocok untuk penampilan formal dan semi-formal.', 199000, NULL, 1, 1, 1, 0, NULL, 0), -- id 8 — Jaket Denim (best-seller + new arrival) (8, 1, 4, 'Jaket Denim Oversize', 'jaket-denim-oversize', 'Jaket denim oversize dengan wash premium, tampil kasual namun stylish.', 389000, NULL, 1, 1, 1, 0, NULL, 0), -- id 9 — Rok Plisket (9, 1, 3, 'Rok Plisket Midi', 'rok-plisket-midi', 'Rok plisket midi dengan bahan flowy yang nyaman dan elegan.', 229000, NULL, 1, 0, 1, 0, NULL, 0), -- id 10 — Loafer (10, 3, 11, 'Sepatu Loafer Kulit', 'sepatu-loafer-kulit', 'Sepatu loafer berbahan kulit asli dengan sol karet anti-slip.', 599000, 749000, 1, 0, 1, 1, NULL, 0), -- id 11–18 from category.php $produk (Pakaian Wanita context) (11, 1, 4, 'Blazer Linen Oversize', 'blazer-linen-oversize', 'Blazer linen oversize dengan potongan modern, cocok untuk tampilan profesional.', 459000, 599000, 1, 0, 0, 1, NULL, 0), (12, 1, 3, 'Rok Plisket Midi (Edisi Baru)', 'rok-plisket-midi-edisi-baru', 'Rok plisket midi edisi terbaru dengan pilihan warna yang lebih beragam.', 229000, NULL, 1, 0, 0, 0, NULL, 0), (13, 2, 6, 'Kemeja Katun Garis', 'kemeja-katun-garis', 'Kemeja katun dengan motif garis klasik, tersedia dalam berbagai kombinasi warna.', 199000, NULL, 1, 0, 0, 0, NULL, 0), (14, 2, 8, 'Celana Wide Leg', 'celana-wide-leg', 'Celana wide leg berbahan kain yang jatuh, menciptakan siluet elegan.', 259000, 329000, 1, 0, 0, 1, NULL, 0), (15, 1, 2, 'Sweater Rajut Crewneck', 'sweater-rajut-crewneck', 'Sweater rajut crewneck tebal dan hangat, pilihan sempurna untuk hari dingin.', 239000, NULL, 1, 0, 0, 0, NULL, 0), (16, 1, 1, 'Jumpsuit Tali', 'jumpsuit-tali', 'Jumpsuit tali dengan desain simpel dan elegan untuk berbagai acara.', 349000, NULL, 1, 0, 0, 0, NULL, 0), (17, 1, 4, 'Cardigan Rib Knit', 'cardigan-rib-knit', 'Cardigan rajut rib dengan tekstur yang kaya, cocok dipakai berlapis.', 219000, 279000, 1, 0, 0, 1, NULL, 0), (18, 2, 8, 'Celana Kulot Linen', 'celana-kulot-linen', 'Celana kulot berbahan linen ringan yang mengalir indah saat bergerak.', 189000, NULL, 1, 0, 0, 0, NULL, 0); -- --------------------------------------------------------------------------- -- PRODUCT IMAGES (product.php shows 4 images for product id=1) -- --------------------------------------------------------------------------- INSERT INTO product_images (product_id, url, alt_text, sort_order) VALUES (1, 'https://placehold.co/600x750/f4f4f5/0d0d0d?text=Kemeja+1', 'Kemeja Linen — tampak depan', 0), (1, 'https://placehold.co/600x750/e4e4e7/0d0d0d?text=Kemeja+2', 'Kemeja Linen — tampak belakang', 1), (1, 'https://placehold.co/600x750/f4f4f5/0d0d0d?text=Kemeja+3', 'Kemeja Linen — detail kancing', 2), (1, 'https://placehold.co/600x750/e4e4e7/0d0d0d?text=Kemeja+4', 'Kemeja Linen — detail kerah', 3), -- Single thumbnail for remaining products (2, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Dress+Midi', 'Dress Midi Motif Bunga', 0), (3, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Sneakers', 'Sneakers Canvas Low', 0), (4, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Tote+Bag', 'Tote Bag Kanvas', 0), (5, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Kaos+Polos', 'Kaos Polos', 0), (6, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Chino', 'Celana Chino', 0), (7, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Heels', 'Heels Strap', 0), (8, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Jaket+Denim', 'Jaket Denim', 0), (9, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Rok+Plisket', 'Rok Plisket', 0), (10, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Loafer', 'Loafer Kulit', 0), (11, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Blazer', 'Blazer Linen', 0), (12, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Rok+Plisket', 'Rok Plisket', 0), (13, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Kemeja+Garis', 'Kemeja Garis', 0), (14, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Wide+Leg', 'Celana Wide Leg', 0), (15, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Sweater', 'Sweater Rajut', 0), (16, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Jumpsuit', 'Jumpsuit Tali', 0), (17, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Cardigan', 'Cardigan Rib Knit', 0), (18, 'https://placehold.co/480x600/f4f4f5/0d0d0d?text=Kulot', 'Celana Kulot Linen', 0); -- --------------------------------------------------------------------------- -- PRODUCT VARIANTS (product.php: sizes S M L XL XXL + 3 colors for product 1; -- size-only for footwear; color+size for apparel) -- --------------------------------------------------------------------------- -- Product 1: Kemeja Linen — 5 sizes × 3 colors = 15 variants INSERT INTO product_variants (product_id, size, color_hex, color_name, sku) VALUES (1,'S', '#0d0d0d','Hitam', 'KLL-S-HTM'), (1,'M', '#0d0d0d','Hitam', 'KLL-M-HTM'), (1,'L', '#0d0d0d','Hitam', 'KLL-L-HTM'), -- cart: Putih / L maps conceptually (1,'XL', '#0d0d0d','Hitam', 'KLL-XL-HTM'), (1,'XXL','#0d0d0d','Hitam', 'KLL-XXL-HTM'), (1,'S', '#e4e4e7','Putih', 'KLL-S-PTH'), (1,'M', '#e4e4e7','Putih', 'KLL-M-PTH'), (1,'L', '#e4e4e7','Putih', 'KLL-L-PTH'), -- the ordered variant (Putih / L) (1,'XL', '#e4e4e7','Putih', 'KLL-XL-PTH'), (1,'XXL','#e4e4e7','Putih', 'KLL-XXL-PTH'), (1,'S', '#2952e3','Biru', 'KLL-S-BRU'), (1,'M', '#2952e3','Biru', 'KLL-M-BRU'), (1,'L', '#2952e3','Biru', 'KLL-L-BRU'), (1,'XL', '#2952e3','Biru', 'KLL-XL-BRU'), (1,'XXL','#2952e3','Biru', 'KLL-XXL-BRU'); -- Product 3: Sneakers Canvas — sizes 39–44 INSERT INTO product_variants (product_id, size, color_hex, color_name, sku) VALUES (3,'39', NULL, NULL, 'SCL-39'), (3,'40', NULL, NULL, 'SCL-40'), (3,'41', NULL, NULL, 'SCL-41'), (3,'42', NULL, NULL, 'SCL-42'), -- ordered variant (3,'43', NULL, NULL, 'SCL-43'), (3,'44', NULL, NULL, 'SCL-44'); -- Product 4: Tote Bag — colors only INSERT INTO product_variants (product_id, size, color_hex, color_name, sku) VALUES (4, NULL, '#a8a29e', 'Krem', 'TBK-KRM'), -- ordered variant (4, NULL, '#0d0d0d', 'Hitam', 'TBK-HTM'), (4, NULL, '#e4e4e7', 'Abu', 'TBK-ABU'); -- Products 5,6,13,14,15,16,17,18: standard apparel sizing INSERT INTO product_variants (product_id, size, color_hex, color_name, sku) VALUES (5,'S','#0d0d0d','Hitam','KPP-S'),(5,'M','#0d0d0d','Hitam','KPP-M'), (5,'L','#0d0d0d','Hitam','KPP-L'),(5,'XL','#0d0d0d','Hitam','KPP-XL'), (6,'S',NULL,NULL,'CCF-S'),(6,'M',NULL,NULL,'CCF-M'), (6,'L',NULL,NULL,'CCF-L'),(6,'XL',NULL,NULL,'CCF-XL'); -- --------------------------------------------------------------------------- -- PRODUCT VARIANT STOCK -- --------------------------------------------------------------------------- -- Kemeja Linen variants (id 1–15) INSERT INTO product_variant_stock (variant_id, qty) VALUES (1,10),(2,15),(3,8),(4,6),(5,3), (6,12),(7,20),(8,18),(9,10),(10,5), (11,7),(12,9),(13,11),(14,4),(15,2); -- Sneakers variants (id 16–21) INSERT INTO product_variant_stock (variant_id, qty) VALUES (16,5),(17,8),(18,12),(19,10),(20,6),(21,3); -- Tote Bag variants (id 22–24) INSERT INTO product_variant_stock (variant_id, qty) VALUES (22,30),(23,25),(24,20); -- Kaos Polos (25–28), Chino (29–32) INSERT INTO product_variant_stock (variant_id, qty) VALUES (25,50),(26,45),(27,40),(28,35), (29,20),(30,22),(31,18),(32,15); -- --------------------------------------------------------------------------- -- VOUCHERS (from cart.php $valid_vouchers) -- --------------------------------------------------------------------------- INSERT INTO vouchers (code, discount_type, discount_value, min_purchase, max_uses, valid_from, valid_until) VALUES ('FORMA10', 'percent', 10.00, 0, NULL, '2026-01-01', '2026-12-31'), ('FORMA20', 'percent', 20.00, 250000, 500, '2026-01-01', '2026-06-30'), ('ONGKIR', 'fixed', 15000, 100000, NULL, '2026-01-01', '2026-12-31'); -- --------------------------------------------------------------------------- -- USERS (demo accounts; passwords are bcrypt of "password123") -- --------------------------------------------------------------------------- INSERT INTO users (first_name, last_name, email, phone, password_hash, newsletter) VALUES ('Budi', 'Santoso', 'budi.santoso@example.com', '081234567890', '$2y$12$demoHashForBudiSantoso0000000000000000000000000000000000', 1), ('Siti', 'Rahayu', 'siti.rahayu@example.com', '081298765432', '$2y$12$demoHashForSitiRahayu000000000000000000000000000000000000', 0), ('Admin', 'FORMA', 'admin@forma-store.test', '08001234567', '$2y$12$demoHashForAdmin00000000000000000000000000000000000000000', 0); -- --------------------------------------------------------------------------- -- ORDERS (the completed order from order-success.php: FORMA-2026-08421) -- --------------------------------------------------------------------------- INSERT INTO orders (order_number, user_id, recipient_name, recipient_phone, address_line, city_id, city_name, province_name, postal_code, shipping_method_id, shipping_cost, payment_method, payment_bank, subtotal, discount_amount, total, status, payment_deadline) VALUES ('FORMA-2026-08421', 1, 'Budi Santoso', '0812-3456-7890', 'Jl. Mawar No. 12, RT 03/RW 05', 1, 'Surabaya', 'Jawa Timur', '60111', 1, 15000, 'transfer', 'BCA', 1086000, 0, 1101000, 'paid', DATE_ADD(NOW(), INTERVAL 24 HOUR)); -- --------------------------------------------------------------------------- -- ORDER ITEMS (from order-success.php $items) -- --------------------------------------------------------------------------- INSERT INTO order_items (order_id, product_id, variant_id, product_name, variant_label, unit_price, qty, subtotal) VALUES (1, 1, 8, 'Kemeja Linen Lengan Panjang', 'Putih / L', 249000, 1, 249000), (1, 3, 19, 'Sneakers Canvas Low', 'Size 42', 459000, 1, 459000), (1, 4, 22, 'Tote Bag Kanvas Minimalis', 'Krem', 189000, 2, 378000); -- --------------------------------------------------------------------------- -- PRODUCT REVIEWS (product.php shows 128 reviews, rating 4/5 for product 1) -- --------------------------------------------------------------------------- INSERT INTO product_reviews (product_id, user_id, reviewer, rating, body, is_approved) VALUES (1, 1, 'Budi S.', 5, 'Bahan linennya nyaman banget, jahitannya juga rapi. Sangat puas!', 1), (1, 2, 'Siti R.', 4, 'Bagus, tapi ukuran L agak sedikit longgar. Overall oke!', 1), (1, NULL, 'Andi P.', 4, 'Cocok untuk kerja dan casual, mudah dipadukan.', 1), (1, NULL, 'Dewi K.', 3, 'Warnanya sedikit berbeda dari foto, tapi kualitas oke.', 1); -- --------------------------------------------------------------------------- -- NEWSLETTER SUBSCRIBERS -- --------------------------------------------------------------------------- INSERT INTO newsletter_subscribers (email, user_id) VALUES ('budi.santoso@example.com', 1), ('newsletter.guest@example.com', NULL); -- --------------------------------------------------------------------------- -- CONTACT MESSAGES -- --------------------------------------------------------------------------- INSERT INTO contact_messages (user_id, name, email, topic, order_number, message) VALUES (1, 'Budi Santoso', 'budi.santoso@example.com', 'pengiriman', 'FORMA-2026-08421', 'Halo, kapan kira-kira pesanan saya tiba? Sudah 3 hari sejak pembayaran dikonfirmasi.'), (NULL, 'Calon Pembeli', 'calon@example.com', 'produk', NULL, 'Apakah kemeja linen tersedia dalam warna hijau muda?'); SET FOREIGN_KEY_CHECKS = 1; -- ============================================================================= -- END OF FORMA SCHEMA -- =============================================================================