-- Grove QR Menu System – Database Setup
-- Run this once via phpMyAdmin or MySQL CLI

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

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  sort_order INT DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  base_price DECIMAL(10,3) NOT NULL DEFAULT 0,
  photo_url VARCHAR(500),
  badge VARCHAR(50),
  is_active TINYINT(1) DEFAULT 1,
  sort_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE sizes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  label VARCHAR(100) NOT NULL,
  price_add DECIMAL(10,3) DEFAULT 0,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE extras (
  id INT AUTO_INCREMENT PRIMARY KEY,
  item_id INT NOT NULL,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,3) DEFAULT 0,
  sort_order INT DEFAULT 0,
  FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE
);

CREATE TABLE settings (
  key_name VARCHAR(100) PRIMARY KEY,
  value TEXT
);

CREATE TABLE admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default settings
INSERT INTO settings (key_name, value) VALUES
  ('restaurant_name', 'Grove'),
  ('restaurant_tagline', 'Modern Kitchen · Kuwait City'),
  ('currency_code', 'KD'),
  ('currency_decimals', '3'),
  ('primary_color', '#1a1a1a'),
  ('accent_color', '#4ade80'),
  ('logo_url', ''),
  ('meta_description', 'Scan to browse our full menu');

-- Default categories
INSERT INTO categories (name, slug, sort_order) VALUES
  ('Starters', 'starters', 1),
  ('Mains', 'mains', 2),
  ('Bowls', 'bowls', 3),
  ('Drinks', 'drinks', 4);

-- Default admin user (password: admin123 — CHANGE IMMEDIATELY)
INSERT INTO admin_users (username, password_hash) VALUES
  ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');