-- =====================================================================
-- POS COFFEE SHOP & RESTO - DATABASE SCHEMA
-- Hybrid Majoo x ESB Architecture
-- MySQL 5.7+ / MariaDB 10.3+
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';

-- =====================================================================
-- 1. USERS & ROLE-BASED ACCESS CONTROL
-- =====================================================================

CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE,
  can_void TINYINT(1) NOT NULL DEFAULT 0,
  can_discount TINYINT(1) NOT NULL DEFAULT 0,
  can_open_cash_drawer TINYINT(1) NOT NULL DEFAULT 0,
  can_manage_inventory TINYINT(1) NOT NULL DEFAULT 0,
  can_manage_users TINYINT(1) NOT NULL DEFAULT 0,
  can_view_reports TINYINT(1) NOT NULL DEFAULT 0,
  can_close_shift TINYINT(1) NOT NULL DEFAULT 0,
  is_supervisor_level TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roles (name, can_void, can_discount, can_open_cash_drawer, can_manage_inventory, can_manage_users, can_view_reports, can_close_shift, is_supervisor_level) VALUES
('Owner',      1,1,1,1,1,1,1,1),
('Manager',    1,1,1,1,1,1,1,1),
('Supervisor', 1,1,1,0,0,1,1,1),
('Kasir',      0,0,1,0,0,0,0,0),
('Barista',    0,0,0,0,0,0,0,0),
('Dapur',      0,0,0,0,0,0,0,0),
('Waiter',     0,0,0,0,0,0,0,0);

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  role_id INT UNSIGNED NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  pin VARCHAR(255) NULL COMMENT 'hashed 4-6 digit PIN for supervisor override',
  phone VARCHAR(20) NULL,
  photo_path VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_activity_log (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL COMMENT 'e.g. VOID_TRANSACTION, GIVE_DISCOUNT, LOGIN, OPEN_DRAWER',
  ref_table VARCHAR(50) NULL,
  ref_id BIGINT UNSIGNED NULL,
  detail TEXT NULL,
  authorized_by INT UNSIGNED NULL COMMENT 'supervisor user id if PIN override used',
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (authorized_by) REFERENCES users(id),
  INDEX idx_action (action),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 2. OUTLET / AREA / TABLE MANAGEMENT
-- =====================================================================

CREATE TABLE IF NOT EXISTS outlets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  address VARCHAR(255) NULL,
  phone VARCHAR(20) NULL,
  tax_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  service_charge_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  receipt_footer VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS floor_areas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  outlet_id INT UNSIGNED NOT NULL,
  name VARCHAR(50) NOT NULL COMMENT 'e.g. Indoor, Outdoor, VIP',
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tables_master (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  floor_area_id INT UNSIGNED NOT NULL,
  code VARCHAR(20) NOT NULL COMMENT 'e.g. A1, VIP-2',
  capacity TINYINT UNSIGNED NOT NULL DEFAULT 4,
  pos_x INT NOT NULL DEFAULT 0 COMMENT 'position on floor plan canvas',
  pos_y INT NOT NULL DEFAULT 0,
  shape ENUM('square','round','rect') NOT NULL DEFAULT 'square',
  status ENUM('empty','occupied','waiting_food','unpaid','reserved') NOT NULL DEFAULT 'empty',
  qr_token VARCHAR(64) NULL UNIQUE COMMENT 'token for self-order QR link',
  merged_with_table_id INT UNSIGNED NULL COMMENT 'if merged, points to primary table',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  FOREIGN KEY (floor_area_id) REFERENCES floor_areas(id),
  FOREIGN KEY (merged_with_table_id) REFERENCES tables_master(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 3. PRODUCT CATALOG, MODIFIERS, RECIPES (INGREDIENT-LEVEL)
-- =====================================================================

CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  routing ENUM('bar','kitchen','none') NOT NULL DEFAULT 'kitchen' COMMENT 'auto KDS routing target',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS units (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(20) NOT NULL UNIQUE COMMENT 'e.g. gr, ml, pcs, kg, liter'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO units (name) VALUES ('gr'),('kg'),('ml'),('liter'),('pcs'),('shot');

CREATE TABLE IF NOT EXISTS ingredients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  unit_id INT UNSIGNED NOT NULL,
  stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0,
  min_stock_qty DECIMAL(12,3) NOT NULL DEFAULT 0 COMMENT 'reorder point',
  cost_per_unit DECIMAL(12,2) NOT NULL DEFAULT 0,
  supplier_id INT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (unit_id) REFERENCES units(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS suppliers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  contact_person VARCHAR(100) NULL,
  phone VARCHAR(20) NULL,
  address VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  sku VARCHAR(30) NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  description VARCHAR(255) NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  image_path VARCHAR(255) NULL,
  is_favorite TINYINT(1) NOT NULL DEFAULT 0,
  track_stock TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'if 0, always available regardless of recipe stock',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Recipe: ingredient consumption per product (ingredient-level BOM)
CREATE TABLE IF NOT EXISTS product_recipes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  ingredient_id INT UNSIGNED NOT NULL,
  qty_used DECIMAL(12,3) NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
  UNIQUE KEY uq_product_ingredient (product_id, ingredient_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modifier groups: e.g. "Temperature", "Sugar Level", "Ice Level", "Extra Shot"
CREATE TABLE IF NOT EXISTS modifier_groups (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  max_select TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1 = single choice, >1 = multi choice'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS modifier_options (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  modifier_group_id INT UNSIGNED NOT NULL,
  name VARCHAR(50) NOT NULL COMMENT 'e.g. Hot, Ice, 25%, Less Ice, Extra Shot',
  price_delta DECIMAL(12,2) NOT NULL DEFAULT 0 COMMENT 'additional price, can be 0',
  extra_ingredient_id INT UNSIGNED NULL COMMENT 'optional: consumes extra ingredient e.g. extra shot',
  extra_ingredient_qty DECIMAL(12,3) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  FOREIGN KEY (modifier_group_id) REFERENCES modifier_groups(id) ON DELETE CASCADE,
  FOREIGN KEY (extra_ingredient_id) REFERENCES ingredients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS product_modifier_groups (
  product_id INT UNSIGNED NOT NULL,
  modifier_group_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, modifier_group_id),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (modifier_group_id) REFERENCES modifier_groups(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 4. SHIFT MANAGEMENT & CASH DRAWER
-- =====================================================================

CREATE TABLE IF NOT EXISTS shifts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  outlet_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL COMMENT 'kasir who opened the shift',
  opening_cash DECIMAL(12,2) NOT NULL DEFAULT 0,
  closing_cash_system DECIMAL(12,2) NULL COMMENT 'calculated by system',
  closing_cash_actual DECIMAL(12,2) NULL COMMENT 'physically counted by kasir',
  cash_variance DECIMAL(12,2) NULL COMMENT 'actual - system, selisih kasir',
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at DATETIME NULL,
  notes VARCHAR(255) NULL,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS cash_movements (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shift_id INT UNSIGNED NOT NULL,
  type ENUM('in','out') NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  reason VARCHAR(255) NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (shift_id) REFERENCES shifts(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 5. ORDERS / TRANSACTIONS (DINE-IN, TAKEAWAY, SELF-ORDER QR)
-- =====================================================================

CREATE TABLE IF NOT EXISTS orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_no VARCHAR(30) NOT NULL UNIQUE,
  outlet_id INT UNSIGNED NOT NULL,
  shift_id INT UNSIGNED NULL,
  order_mode ENUM('dine_in','takeaway','self_order_qr') NOT NULL DEFAULT 'takeaway',
  table_id INT UNSIGNED NULL,
  customer_name VARCHAR(100) NULL,
  pax TINYINT UNSIGNED NULL COMMENT 'number of guests',
  status ENUM('open','sent_to_kitchen','completed','paid','void') NOT NULL DEFAULT 'open',
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  discount_reason VARCHAR(100) NULL,
  discount_authorized_by INT UNSIGNED NULL,
  tax_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  service_charge_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  change_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','qris','debit','credit','ewallet','split') NULL,
  parent_order_id BIGINT UNSIGNED NULL COMMENT 'used for split bill: points to original order',
  created_by INT UNSIGNED NULL,
  void_reason VARCHAR(255) NULL,
  void_authorized_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  FOREIGN KEY (outlet_id) REFERENCES outlets(id),
  FOREIGN KEY (shift_id) REFERENCES shifts(id),
  FOREIGN KEY (table_id) REFERENCES tables_master(id),
  FOREIGN KEY (created_by) REFERENCES users(id),
  FOREIGN KEY (discount_authorized_by) REFERENCES users(id),
  FOREIGN KEY (void_authorized_by) REFERENCES users(id),
  FOREIGN KEY (parent_order_id) REFERENCES orders(id),
  INDEX idx_status (status),
  INDEX idx_table (table_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  product_name VARCHAR(100) NOT NULL COMMENT 'snapshot at time of order',
  qty INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price DECIMAL(12,2) NOT NULL,
  modifier_price_total DECIMAL(12,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(12,2) NOT NULL,
  notes VARCHAR(255) NULL COMMENT 'free text note e.g. tidak pedas',
  routing ENUM('bar','kitchen','none') NOT NULL DEFAULT 'kitchen',
  kds_status ENUM('new','in_progress','ready','served') NOT NULL DEFAULT 'new',
  kds_started_at DATETIME NULL,
  kds_ready_at DATETIME NULL,
  kds_served_at DATETIME NULL,
  is_void TINYINT(1) NOT NULL DEFAULT 0,
  paid_by_split_id INT UNSIGNED NULL COMMENT 'links item to a split-bill sub-payment group',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id),
  INDEX idx_kds_status (kds_status),
  INDEX idx_routing (routing)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_item_modifiers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_item_id BIGINT UNSIGNED NOT NULL,
  modifier_option_id INT UNSIGNED NOT NULL,
  option_name VARCHAR(50) NOT NULL COMMENT 'snapshot',
  price_delta DECIMAL(12,2) NOT NULL DEFAULT 0,
  FOREIGN KEY (order_item_id) REFERENCES order_items(id) ON DELETE CASCADE,
  FOREIGN KEY (modifier_option_id) REFERENCES modifier_options(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Split bill groups: each split group represents one "sub bill" within an order
CREATE TABLE IF NOT EXISTS order_splits (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  split_label VARCHAR(50) NOT NULL COMMENT 'e.g. Split 1, Budi, Ani',
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_method ENUM('cash','qris','debit','credit','ewallet') NULL,
  status ENUM('unpaid','paid') NOT NULL DEFAULT 'unpaid',
  paid_at DATETIME NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ingredient waste/spoilage log
CREATE TABLE IF NOT EXISTS ingredient_waste_log (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ingredient_id INT UNSIGNED NOT NULL,
  qty DECIMAL(12,3) NOT NULL,
  reason VARCHAR(100) NOT NULL COMMENT 'e.g. spill, expired, failed shot',
  user_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stock movement ledger (all in/out for audit trail)
CREATE TABLE IF NOT EXISTS stock_movements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ingredient_id INT UNSIGNED NOT NULL,
  movement_type ENUM('sale_deduction','waste','purchase_in','adjustment','po_receive') NOT NULL,
  qty_change DECIMAL(12,3) NOT NULL COMMENT 'negative for deduction, positive for addition',
  ref_table VARCHAR(50) NULL,
  ref_id BIGINT UNSIGNED NULL,
  balance_after DECIMAL(12,3) NOT NULL,
  user_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id),
  INDEX idx_ingredient (ingredient_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Purchase Orders (auto/manual PO to supplier)
CREATE TABLE IF NOT EXISTS purchase_orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_no VARCHAR(30) NOT NULL UNIQUE,
  supplier_id INT UNSIGNED NOT NULL,
  status ENUM('draft','sent','partial_received','received','cancelled') NOT NULL DEFAULT 'draft',
  is_auto_generated TINYINT(1) NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  received_at DATETIME NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS purchase_order_items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  po_id INT UNSIGNED NOT NULL,
  ingredient_id INT UNSIGNED NOT NULL,
  qty_ordered DECIMAL(12,3) NOT NULL,
  qty_received DECIMAL(12,3) NOT NULL DEFAULT 0,
  unit_cost DECIMAL(12,2) NOT NULL,
  FOREIGN KEY (po_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
  FOREIGN KEY (ingredient_id) REFERENCES ingredients(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- 6. SETTINGS
-- =====================================================================

CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(50) PRIMARY KEY,
  setting_value VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO app_settings (setting_key, setting_value) VALUES
('app_name', 'Coffee Shop POS'),
('currency_symbol', 'Rp'),
('order_no_prefix', 'INV'),
('kds_warning_minutes', '10'),
('kds_danger_minutes', '20'),
('kds_poll_interval_seconds', '4'),
('table_poll_interval_seconds', '5');

-- =====================================================================
-- SEED DATA (default admin, outlet, sample data)
-- =====================================================================

INSERT INTO outlets (name, address, tax_percent, service_charge_percent) VALUES
('Outlet Utama', 'Jl. Contoh No. 1', 10, 5);

-- default password: admin123 (bcrypt hash generated at app first-run script, placeholder here)
INSERT INTO users (role_id, full_name, username, password_hash, pin) VALUES
(1, 'Administrator', 'admin', '$2y$10$placeholderplaceholderplaceholderplaceh', NULL);

INSERT INTO floor_areas (outlet_id, name, sort_order) VALUES (1,'Indoor',1),(1,'Outdoor',2);

INSERT INTO categories (name, routing, sort_order) VALUES
('Coffee', 'bar', 1),
('Non-Coffee', 'bar', 2),
('Pastry', 'bar', 3),
('Makanan Berat', 'kitchen', 4),
('Snack', 'kitchen', 5);

SET FOREIGN_KEY_CHECKS = 1;