-- ============================================
-- GEPREK BRO — Database Setup
-- Jalankan file ini di phpMyAdmin atau MySQL CLI
-- ============================================

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

-- ============================================
-- TABEL: menu
-- ============================================
CREATE TABLE IF NOT EXISTS menu (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(100)  NOT NULL,
  emoji       VARCHAR(10)   NOT NULL DEFAULT '🍽️',
  base_price  INT           NOT NULL DEFAULT 0,
  cat         VARCHAR(50)   NOT NULL DEFAULT 'ayam',
  level       VARCHAR(100)  NOT NULL DEFAULT '',
  badge       VARCHAR(20)   NOT NULL DEFAULT '',
  has_variant TINYINT(1)    NOT NULL DEFAULT 0,
  is_active   TINYINT(1)    NOT NULL DEFAULT 1,
  created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- TABEL: transaksi
-- ============================================
CREATE TABLE IF NOT EXISTS transaksi (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  no_struk      VARCHAR(30)  NOT NULL,
  nama_pelanggan VARCHAR(100) NOT NULL DEFAULT 'Pelanggan',
  jenis_order   ENUM('meja','takeaway','gofood') NOT NULL DEFAULT 'meja',
  no_meja       VARCHAR(20)  NOT NULL DEFAULT '',
  pay_method    ENUM('cash','qris','debit') NOT NULL DEFAULT 'cash',
  subtotal      INT          NOT NULL DEFAULT 0,
  total         INT          NOT NULL DEFAULT 0,
  bayar         INT          NOT NULL DEFAULT 0,
  kembalian     INT          NOT NULL DEFAULT 0,
  kasir         VARCHAR(50)  NOT NULL DEFAULT 'Rini',
  created_at    TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- TABEL: transaksi_item
-- ============================================
CREATE TABLE IF NOT EXISTS transaksi_item (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  transaksi_id  INT          NOT NULL,
  menu_id       INT          NOT NULL,
  menu_name     VARCHAR(100) NOT NULL,
  menu_emoji    VARCHAR(10)  NOT NULL DEFAULT '',
  variant       VARCHAR(100) NOT NULL DEFAULT '',
  addons        VARCHAR(255) NOT NULL DEFAULT '',
  unit_price    INT          NOT NULL DEFAULT 0,
  qty           INT          NOT NULL DEFAULT 1,
  subtotal      INT          NOT NULL DEFAULT 0,
  FOREIGN KEY (transaksi_id) REFERENCES transaksi(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- DATA AWAL MENU
-- ============================================
INSERT INTO menu (name, emoji, base_price, cat, level, badge, has_variant) VALUES
('Ayam Geprek Bro',    '🍗', 18000, 'ayam',    'Pilih Varian',        'pop', 1),
('Paket Geprek Hemat', '🍛', 22000, 'ayam',    'Ayam + Nasi + Minum', 'pop', 1),
('Ayam Double',        '🍗', 32000, 'ayam',    '2 Potong Ayam',       '',    1),
('Geprek Jumbo',       '🦴', 25000, 'ayam',    'Porsi XL',            '',    1),
('Es Teh Manis',       '🧋',  5000, 'minuman', '',                    '',    0),
('Es Jeruk',           '🍊',  7000, 'minuman', '',                    '',    0),
('Lemon Tea',          '🍋',  8000, 'minuman', '',                    '',    0),
('Es Matcha Bro',      '🍵', 15000, 'minuman', '',                    'pop', 0),
('Kentang Goreng',     '🍟', 12000, 'snack',   '',                    '',    0),
('Tahu Crispy',        '🟡',  8000, 'snack',   '',                    '',    0),
('Jamur Crispy',       '🍄', 10000, 'snack',   '',                    '',    0);