-- ============================================================
-- BAKMI YAYO x ICHI GOURMET — Database Schema
-- Import file ini ke phpMyAdmin / MySQL sebelum menjalankan web
-- ============================================================

CREATE DATABASE IF NOT EXISTS bakmiyayo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE bakmiyayo;

-- ------------------------------------------------------------
-- Tabel Cabang (setiap cabang punya kasir sendiri)
-- ------------------------------------------------------------
CREATE TABLE branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ------------------------------------------------------------
-- Tabel Akun Kasir (login per cabang) & Admin
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','cashier') NOT NULL DEFAULT 'cashier',
    branch_id INT DEFAULT NULL,          -- NULL untuk admin (akses semua cabang)
    full_name VARCHAR(100) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Kategori Menu (Bakmi, Dimsum, Minuman, dll)
-- ------------------------------------------------------------
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0
);

-- ------------------------------------------------------------
-- Menu — bisa di-update langsung lewat halaman Admin (CRUD ke SQL)
-- ------------------------------------------------------------
CREATE TABLE menu_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT DEFAULT NULL,
    branch_id INT DEFAULT NULL,          -- NULL = tersedia di semua cabang
    name VARCHAR(150) NOT NULL,
    description TEXT,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    image_url VARCHAR(255) DEFAULT NULL,
    is_available TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- Order — dibuat dari klik menu di website customer
-- ------------------------------------------------------------
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_code VARCHAR(20) UNIQUE NOT NULL,   -- kode unik utk struk, mis: BY-20260808-0001
    branch_id INT NOT NULL,
    customer_name VARCHAR(100) DEFAULT NULL,
    customer_phone VARCHAR(30) DEFAULT NULL,
    table_number VARCHAR(20) DEFAULT NULL,
    order_type ENUM('dine_in','takeaway','delivery') DEFAULT 'dine_in',
    status ENUM('pending','confirmed','cooking','ready','completed','cancelled') DEFAULT 'pending',
    total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id)
);

-- ------------------------------------------------------------
-- Detail item per order
-- ------------------------------------------------------------
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    menu_item_id INT NOT NULL,
    item_name VARCHAR(150) NOT NULL,   -- disimpan snapshot, jaga2 menu diubah/dihapus
    price DECIMAL(12,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    subtotal DECIMAL(12,2) NOT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (menu_item_id) REFERENCES menu_items(id)
);

-- ============================================================
-- SAMPLE DATA
-- ============================================================

INSERT INTO branches (name, address, phone) VALUES
('Bakmi Yayo - Cabang Kelapa Gading', 'Jl. Boulevard Raya, Kelapa Gading, Jakarta Utara', '021-1234567'),
('Bakmi Yayo - Cabang BSD', 'Jl. BSD Grand Boulevard, Tangerang', '021-7654321'),
('Ichi Gourmet - Cabang Pasarkemis', 'Jl. Raya Pasarkemis, Tangerang', '021-5556677');

-- password untuk semua akun contoh di bawah: "password123"
-- hash dibuat dengan password_hash('password123', PASSWORD_DEFAULT)
INSERT INTO users (username, password_hash, role, branch_id, full_name) VALUES
('admin', '$2y$10$92Iv2mK1sVYQ8G4W1u2coeQY2r6C8N1YkYyv9m6t9y0kzYq3z1G9G', 'admin', NULL, 'Super Admin'),
('kasir_gading', '$2y$10$92Iv2mK1sVYQ8G4W1u2coeQY2r6C8N1YkYyv9m6t9y0kzYq3z1G9G', 'cashier', 1, 'Kasir Kelapa Gading'),
('kasir_bsd', '$2y$10$92Iv2mK1sVYQ8G4W1u2coeQY2r6C8N1YkYyv9m6t9y0kzYq3z1G9G', 'cashier', 2, 'Kasir BSD'),
('kasir_ichi', '$2y$10$92Iv2mK1sVYQ8G4W1u2coeQY2r6C8N1YkYyv9m6t9y0kzYq3z1G9G', 'cashier', 3, 'Kasir Ichi Gourmet');

INSERT INTO categories (name, sort_order) VALUES
('Bakmi', 1),
('Dimsum', 2),
('Sushi & Gourmet', 3),
('Minuman', 4);

INSERT INTO menu_items (category_id, branch_id, name, description, price, is_available) VALUES
(1, NULL, 'Bakmi Yayo Original', 'Mie ayam homemade dengan pangsit dan bakso', 28000, 1),
(1, NULL, 'Bakmi Yayo Jumbo', 'Porsi jumbo dengan ekstra ayam dan pangsit goreng', 38000, 1),
(1, NULL, 'Bakmi Yayo Extra Pedas', 'Bakmi original dengan sambal spesial', 30000, 1),
(2, NULL, 'Dimsum Ayam (5pcs)', 'Dimsum kukus isi ayam', 22000, 1),
(2, NULL, 'Siomay Udang (5pcs)', 'Siomay udang premium', 25000, 1),
(3, 3, 'Sushi Salmon Roll', 'Gulungan sushi salmon segar, 8pcs', 45000, 1),
(3, 3, 'Chirashi Bowl', 'Nasi sushi dengan aneka sashimi', 55000, 1),
(4, NULL, 'Es Teh Manis', 'Teh manis dingin', 8000, 1),
(4, NULL, 'Es Jeruk', 'Jeruk peras segar', 10000, 1);