-- ============================================================
--  Restaurant Menu System - Database Schema
--  Import this file into MySQL/MariaDB before running the app.
-- ============================================================

CREATE DATABASE IF NOT EXISTS restaurant_menu
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE restaurant_menu;

-- ------------------------------------------------------------
-- Categories
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(120) NOT NULL UNIQUE,
    description VARCHAR(255) DEFAULT NULL,
    sort_order  INT NOT NULL DEFAULT 0,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Foods
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS foods (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NOT NULL,
    name        VARCHAR(150) NOT NULL,
    description VARCHAR(500) DEFAULT NULL,
    price       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    image       VARCHAR(255) DEFAULT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_foods_category
        FOREIGN KEY (category_id) REFERENCES categories(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------------------
-- Sample data (optional - safe to delete)
-- ------------------------------------------------------------
INSERT INTO categories (name, slug, description, sort_order, is_active) VALUES
('Appetizers', 'appetizers', 'Start your meal right', 1, 1),
('Main Course', 'main-course', 'Hearty and delicious mains', 2, 1),
('Desserts', 'desserts', 'Sweet treats to finish', 3, 1),
('Beverages', 'beverages', 'Refreshing drinks', 4, 1);

INSERT INTO foods (category_id, name, description, price, image, is_active) VALUES
(1, 'Crispy Spring Rolls', 'Vegetable filled spring rolls served with sweet chili sauce', 120.00, NULL, 1),
(1, 'Garlic Bread', 'Toasted bread with garlic butter and herbs', 90.00, NULL, 1),
(2, 'Grilled Chicken', 'Tender grilled chicken breast with seasonal vegetables', 320.00, NULL, 1),
(2, 'Beef Burger', 'Juicy beef patty with cheese, lettuce and tomato', 260.00, NULL, 1),
(3, 'Chocolate Cake', 'Rich chocolate layered cake with ganache', 150.00, NULL, 1),
(4, 'Fresh Orange Juice', 'Freshly squeezed orange juice', 80.00, NULL, 1);