-- ============================================================
-- NAFESA ORDER MANAGEMENT — Skema Lengkap
-- ============================================================
-- Perubahan utama berbanding versi asal:
--   - orders  : hanya simpan maklumat customer + jumlah keseluruhan (header)
--   - order_items (BARU) : setiap ROW = satu SKU dalam order tersebut
--   Ini membolehkan SATU order mengandungi PELBAGAI produk/SKU,
--   dan senang nak "detect" SKU apa dalam mana-mana order (tengok query contoh di bawah).
-- ============================================================

CREATE DATABASE IF NOT EXISTS sasia_order;
USE sasia_order;

-- ------------------------------------------------------------
-- 0) COMPANIES
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS companies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    currency_code VARCHAR(10) NOT NULL,
    currency_symbol VARCHAR(10) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO companies (slug, name, currency_code, currency_symbol)
VALUES ('my', 'MY', 'MYR', 'RM'), ('sg', 'SG', 'SGD', 'S$')
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    currency_code = VALUES(currency_code),
    currency_symbol = VALUES(currency_symbol),
    is_active = 1;

-- ------------------------------------------------------------
-- 1) PRODUCTS
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(10) NOT NULL DEFAULT 'my',
    company_id INT NOT NULL DEFAULT 1,
    sku VARCHAR(255) NOT NULL,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(100) DEFAULT NULL,
    image_path VARCHAR(255) DEFAULT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    pv DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    bv DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_products_company_sku (company, sku),
    KEY idx_products_company_id (company_id),
    CONSTRAINT fk_products_company FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- ------------------------------------------------------------
-- 2) ORDERS (header) — maklumat customer & ringkasan jumlah sahaja
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(10) NOT NULL DEFAULT 'my',
    company_id INT NOT NULL DEFAULT 1,
    order_no VARCHAR(50) NOT NULL,
    member_id VARCHAR(100) NULL,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30) NOT NULL,
    email VARCHAR(150) NOT NULL,
    address VARCHAR(255) NULL,
    postcode VARCHAR(20) NULL,
    state VARCHAR(100) NULL,
    country VARCHAR(100) NULL,
    subtotal_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    paid_method VARCHAR(100) NULL,
    paid_ref_last4 VARCHAR(25) NULL,
    paid_time DATETIME NULL,
    status ENUM('PENDING','PAID','COLLECTED','CANCELLED') DEFAULT 'PENDING',
    created_by VARCHAR(100) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    UNIQUE KEY uk_orders_company_order_no (company, order_no),
    KEY idx_orders_company_id (company_id),
    CONSTRAINT fk_orders_company FOREIGN KEY (company_id) REFERENCES companies(id)
);

-- ------------------------------------------------------------
-- 3) ORDER_ITEMS (BARU) — satu row = satu SKU dalam satu order
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(10) NOT NULL DEFAULT 'my',
    company_id INT NOT NULL DEFAULT 1,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    sku VARCHAR(255) NOT NULL,
    product_name VARCHAR(150) NOT NULL,   -- snapshot nama produk masa order dibuat
    unit_price DECIMAL(10,2) NOT NULL,    -- snapshot harga masa order (elak berubah kalau harga produk diubah lepas ni)
    quantity INT NOT NULL DEFAULT 1,
    subtotal DECIMAL(10,2) NOT NULL,      -- unit_price x quantity
    KEY idx_order_items_company_id (company_id),
    KEY idx_order_items_product_id (product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT
);

-- ------------------------------------------------------------
-- 4) INDEXES
-- ------------------------------------------------------------
CREATE INDEX idx_order_no ON orders(order_no);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_phone ON orders(phone);
CREATE INDEX idx_email ON orders(email);
CREATE INDEX idx_created_at ON orders(created_at);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_sku ON order_items(sku);


-- ============================================================
-- TABLE: admin_users
-- ============================================================

CREATE TABLE `admin_users` (
  `id`         int            NOT NULL AUTO_INCREMENT,
  `username`   varchar(100)   NOT NULL,
  `password`   varchar(255)   NOT NULL COMMENT 'bcrypt hashed',
  `created_at` timestamp      NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed: default admin accounts
INSERT INTO `admin_users` (`id`, `username`, `password`, `created_at`) VALUES
(1, 'admin', '$2y$12$cdp0ujQEXGDIrm/2RR3LZ.WBGgYnHctr/updH2p9vTvFHmqW8TJnq', '2026-05-29 01:31:01'),
(2, 'Faizz',  '$2b$12$wFpcQ6EFWYeMxhusR06.3OT2Rf75XMAhpOnNvOCPX6GzfjvhivQwq', '2026-05-29 01:31:01');



-- ------------------------------------------------------------
-- 5) SEED DATA — products
-- ------------------------------------------------------------
INSERT INTO products (sku, name, category, image_path, price, pv, bv, stock) VALUES

('ZEKY-BH', 'BRAIN HERO x1', 'ZEKY', '', 52.50, 25.00, 25.00, 500);
-- ============================================================
-- CONTOH: bagaimana simpan satu order dengan 2 SKU berlainan
-- ============================================================
-- INSERT INTO orders (order_no, name, phone, email, address, postcode, state, country,
--                      subtotal_amount, discount_amount, discount_percent, total_amount, status)
-- VALUES ('NAF-0001', 'Aisyah Binti Ali', '0123456789', 'aisyah@email.com',
--         'No 1, Jalan Mawar', '50000', 'Kuala Lumpur', 'Malaysia',
--         158.00, 0.00, 0.00, 158.00, 'PENDING');
--
-- SET @order_id = LAST_INSERT_ID();
--
-- INSERT INTO order_items (order_id, sku, product_name, unit_price, quantity, subtotal) VALUES
-- (@order_id, 'NSR01-KN', 'SRIKANDI KENCANA', 79.00, 1, 79.00),
-- (@order_id, 'NSR02-CD', 'SRIKANDI CANDRAMAWA', 79.00, 1, 79.00);

-- ============================================================
-- CONTOH QUERY: "detect" SKU apa ada dalam sesuatu order
-- ============================================================

-- (a) Semua SKU + kuantiti dalam SATU order (guna order_no)
-- SELECT oi.sku, oi.product_name, oi.quantity, oi.unit_price, oi.subtotal
-- FROM order_items oi
-- JOIN orders o ON o.id = oi.order_id
-- WHERE o.order_no = 'NAF-0001';

-- (b) Semua order yang mengandungi SKU tertentu (contoh: nak tahu order mana beli NSR01-KN)
-- SELECT o.order_no, o.name, o.status, oi.quantity, o.created_at
-- FROM order_items oi
-- JOIN orders o ON o.id = oi.order_id
-- WHERE oi.sku = 'NSR01-KN'
-- ORDER BY o.created_at DESC;

-- (c) Ringkasan penuh order (header + semua item sekali, guna GROUP_CONCAT)
-- SELECT o.order_no, o.name, o.status, o.total_amount,
--        GROUP_CONCAT(CONCAT(oi.sku, ' x', oi.quantity) SEPARATOR ', ') AS items
-- FROM orders o
-- JOIN order_items oi ON oi.order_id = o.id
-- GROUP BY o.id
-- ORDER BY o.created_at DESC;