-- Ember & Vine — Digital QR Menu demo schema
-- Import via phpMyAdmin or: mysql -u USER -p DBNAME < schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS suggestion_rules;
DROP TABLE IF EXISTS item_allergens;
DROP TABLE IF EXISTS allergens;
DROP TABLE IF EXISTS items;
DROP TABLE IF EXISTS categories;

CREATE TABLE categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(80) NOT NULL,
    sort_order  SMALLINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE items (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id     INT UNSIGNED NOT NULL,
    name            VARCHAR(120) NOT NULL,
    description     VARCHAR(400) NOT NULL,
    ingredients     VARCHAR(500) NOT NULL DEFAULT '',
    price           DECIMAL(6,2) NOT NULL,
    image_url       VARCHAR(500) NOT NULL DEFAULT '',
    calories        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    protein_g       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    carbs_g         SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    fat_g           SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    spice_level     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_vegan        TINYINT(1) NOT NULL DEFAULT 0,
    is_vegetarian   TINYINT(1) NOT NULL DEFAULT 0,
    is_gluten_free  TINYINT(1) NOT NULL DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE,
    INDEX idx_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE allergens (
    id      VARCHAR(30) PRIMARY KEY,   -- e.g. 'gluten', 'dairy'
    name    VARCHAR(60) NOT NULL,
    icon    VARCHAR(10) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE item_allergens (
    item_id     INT UNSIGNED NOT NULL,
    allergen_id VARCHAR(30) NOT NULL,
    PRIMARY KEY (item_id, allergen_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (allergen_id) REFERENCES allergens(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE suggestion_rules (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    trigger_item_id     INT UNSIGNED NULL,  -- NULL = global rule (threshold/time)
    suggested_item_id   INT UNSIGNED NOT NULL,
    type                ENUM('pairing','upsell','threshold','time') NOT NULL,
    message             VARCHAR(200) NOT NULL,
    threshold_cents     INT UNSIGNED NULL,  -- used when type = 'threshold'
    time_window         ENUM('breakfast','happy_hour') NULL, -- used when type = 'time'
    active              TINYINT(1) NOT NULL DEFAULT 1,
    FOREIGN KEY (trigger_item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (suggested_item_id) REFERENCES items(id) ON DELETE CASCADE,
    INDEX idx_trigger (trigger_item_id),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_code  VARCHAR(20) NOT NULL,
    status      ENUM('received','preparing','ready') NOT NULL DEFAULT 'received',
    notes       VARCHAR(400) NOT NULL DEFAULT '',
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_table (table_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id    INT UNSIGNED NOT NULL,
    item_id     INT UNSIGNED NOT NULL,
    quantity    SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    chef_note   VARCHAR(200) NOT NULL DEFAULT '',
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE RESTRICT,
    INDEX idx_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;