-- Chiya Munch menu recommender: tables (MySQL 8 / MariaDB 10.4+, utf8mb4)
CREATE TABLE IF NOT EXISTS menu_items (
  id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code        VARCHAR(8)   NOT NULL UNIQUE,            -- stable id used by the API, e.g. i022
  section     VARCHAR(60)  NOT NULL,                   -- menu section, e.g. "Milk Chiya · CTC"
  page_no     TINYINT      NOT NULL DEFAULT 0,
  name        VARCHAR(120) NOT NULL,
  name_np     VARCHAR(120) NULL,
  description VARCHAR(255) NOT NULL DEFAULT '',
  price       INT          NOT NULL,                   -- Rs
  diet        ENUM('veg','egg','nonveg') NOT NULL,     -- CHECK THESE: they decide who sees the dish
  role        ENUM('main','drink','starter','dessert') NOT NULL,
  course      VARCHAR(20)  NOT NULL,                   -- tea, coffee, breakfast, momo, pizza, nepali, side ...
  tags        TEXT         NOT NULL,                   -- JSON array: comfort, healthy, hungry, quick, sweet, refreshing, spicy, energy, sharing, indulgent
  times       TEXT         NOT NULL,                   -- JSON array: morning, afternoon, evening
  spice       TINYINT      NOT NULL DEFAULT 0,         -- 0 none, 1 medium, 2 hot
  prep_min    SMALLINT     NOT NULL DEFAULT 10,
  serves      TINYINT      NOT NULL DEFAULT 1,         -- how many people one order feeds
  temp        VARCHAR(8)   NOT NULL DEFAULT '',        -- hot | cold | ''
  min_group   TINYINT      NOT NULL DEFAULT 1,         -- e.g. Nepali Thali needs 3
  is_pick     TINYINT(1)   NOT NULL DEFAULT 0,         -- Chef's Pick
  is_new      TINYINT(1)   NOT NULL DEFAULT 0,
  is_combo    TINYINT(1)   NOT NULL DEFAULT 0,         -- 15% combo discount
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,         -- set 0 when a dish is unavailable today
  updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS menu_addons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL, price INT NOT NULL,
  diet ENUM('veg','egg','nonveg') NOT NULL DEFAULT 'veg',
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tunable weights (override data/rules.json). Keys: mood, pick, time_match, ... cap_main, cap_drink, combo_discount_percent
CREATE TABLE IF NOT EXISTS reco_rules (
  rule_key VARCHAR(40) NOT NULL PRIMARY KEY, rule_value DECIMAL(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every recommendation shown, and what was finally chosen (use later to learn from real orders)
CREATE TABLE IF NOT EXISTS reco_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  mode ENUM('customer','staff') NOT NULL,
  user_ref VARCHAR(40) NULL,            -- staff user id or loyalty member id, if you have one
  inputs TEXT NOT NULL,                 -- JSON of the form answers
  shown_ids TEXT NOT NULL,              -- JSON array of item codes shown
  chosen_ids TEXT NULL,                 -- JSON array of item codes actually ordered
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;