-- POS RESTO - Database MySQL
-- Jalankan di phpMyAdmin atau MySQL CLI

-- 1. Users
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','kasir') NOT NULL DEFAULT 'kasir',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Categories
CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT NULL,
  image VARCHAR(500) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Products
CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT NULL,
  image VARCHAR(500) NULL,
  price VARCHAR(20) NOT NULL DEFAULT '0',
  stock INT NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=active 0=inactive',
  is_favorite TINYINT(1) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Orders
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_number INT NOT NULL DEFAULT 1,
  order_type VARCHAR(50) NOT NULL DEFAULT 'Dine In',
  table_number VARCHAR(20) NULL,
  payment_method VARCHAR(50) NOT NULL DEFAULT 'Cash',
  nama_kasir VARCHAR(100) NOT NULL DEFAULT 'Admin',
  sub_total INT NOT NULL DEFAULT 0,
  discount INT NOT NULL DEFAULT 0,
  tax INT NOT NULL DEFAULT 0,
  service_charge INT NOT NULL DEFAULT 0,
  total DOUBLE NOT NULL DEFAULT 0,
  payment_amount DOUBLE NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Order Items
CREATE TABLE order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NULL,
  product_name VARCHAR(200) NOT NULL,
  price INT NOT NULL DEFAULT 0,
  quantity INT NOT NULL DEFAULT 1,
  sub_total INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Sync Log
CREATE TABLE sync_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  device_id VARCHAR(100) NOT NULL,
  last_sync_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY (device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================
-- FOREIGN KEYS
-- =====================

ALTER TABLE products ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;

ALTER TABLE order_items ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

ALTER TABLE order_items ADD CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;

-- =====================
-- SEED DATA
-- =====================

INSERT INTO users (name, email, password, role) VALUES
('Admin Resto', 'admin@posresto.com', '$2y$10$Dg/2d3yeOtPi7b07xMTvx.Ph0sOQta4168psUKcdgy7SpqppAmR/y', 'admin'),
('Kasir 1', 'kasir@posresto.com', '$2y$10$Dg/2d3yeOtPi7b07xMTvx.Ph0sOQta4168psUKcdgy7SpqppAmR/y', 'kasir');

INSERT INTO categories (name, description) VALUES
('Makanan', 'Menu makanan berat'),
('Minuman', 'Menu minuman segar'),
('Snack', 'Menu snack dan cemilan');

INSERT INTO products (category_id, name, description, image, price, stock, status, is_favorite) VALUES
(1, 'Nasi Goreng', 'Nasi goreng spesial dengan telur dan ayam', '', '25000', 50, 1, 1),
(1, 'Mie Goreng', 'Mie goreng bumbu oriental', '', '22000', 40, 1, 0),
(1, 'Ayam Bakar', 'Ayam bakar bumbu kecap', '', '35000', 30, 1, 1),
(1, 'Sate Ayam', 'Sate ayam dengan bumbu kacang', '', '30000', 25, 1, 0),
(1, 'Nasi Padang', 'Nasi padang komplit', '', '28000', 35, 1, 0),
(2, 'Es Teh Manis', 'Es teh manis segar', '', '8000', 100, 1, 1),
(2, 'Es Jeruk', 'Es jeruk peras segar', '', '10000', 80, 1, 0),
(2, 'Jus Alpukat', 'Jus alpukat creamy', '', '15000', 40, 1, 1),
(2, 'Kopi Hitam', 'Kopi hitam pilihan', '', '12000', 60, 1, 0),
(3, 'Kentang Goreng', 'Kentang goreng crispy', '', '15000', 50, 1, 0),
(3, 'Pisang Goreng', 'Pisang goreng crispy keju', '', '12000', 45, 1, 1),
(3, 'Tahu Crispy', 'Tahu goreng crispy sambal', '', '10000', 40, 1, 0);