-- =============================================================================
-- MySQL Database Schema
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- =============================================================================

SET time_zone = '+07:00';  -- WIB

-- =============================================================================
-- DROP ORDER (reverse FK dependency)
-- =============================================================================


-- =============================================================================
-- 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;

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');


-- ---------------------------------------------------------------------------
-- 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?');



-- =============================================================================
-- END OF FORMA SCHEMA
-- =============================================================================
