-- =====================================================================
-- 009: flexible loyalty rewards — every rule has 1..4 reward OPTIONS
--      (discount, fixed free item, or a free item the guest chooses from
--      selected items / a category / any item up to a price). With several
--      options the guest picks one; staff apply it (and may override).
-- =====================================================================

CREATE TABLE loyalty_reward_options (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id  INT UNSIGNED NOT NULL,
  rule_id        INT UNSIGNED NOT NULL,
  reward_type    ENUM('percent_discount','flat_discount','free_item') NOT NULL,
  value          DECIMAL(12,2) NULL,                         -- percent (1..100) or flat amount
  max_discount   DECIMAL(12,2) NULL,                         -- cap for percent discounts
  free_item_mode ENUM('fixed','items','category','price_cap') NULL,   -- free_item only
  menu_item_ids  JSON NULL,                                  -- fixed: [id]; items: [id, …]
  category_id    INT UNSIGNED NULL,                          -- category mode
  max_item_price DECIMAL(12,2) NULL,                         -- price_cap mode (optional extra cap for category)
  sort_order     INT NOT NULL DEFAULT 0,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_reward_options_rule (rule_id, sort_order),
  CONSTRAINT fk_reward_options_rule FOREIGN KEY (rule_id) REFERENCES loyalty_rules (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backfill: each existing rule becomes a rule with one option (same reward as before)
INSERT INTO loyalty_reward_options
  (restaurant_id, rule_id, reward_type, value, max_discount, free_item_mode, menu_item_ids, sort_order)
SELECT restaurant_id, id, reward_type, reward_value, max_discount,
       IF(reward_type = 'free_item', 'fixed', NULL),
       IF(reward_menu_item_id IS NULL, NULL, JSON_ARRAY(reward_menu_item_id)),
       0
FROM loyalty_rules;

-- The guest's pick for a reward they qualify for (before staff apply it)
CREATE TABLE reward_selections (
  id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id    INT UNSIGNED NOT NULL,
  table_session_id INT UNSIGNED NOT NULL,
  rule_id          INT UNSIGNED NOT NULL,
  customer_key     INT UNSIGNED NOT NULL DEFAULT 0,          -- customer id for visit rewards, 0 for spend offers
  visit_number     INT UNSIGNED NULL,
  option_id        INT UNSIGNED NOT NULL,
  menu_item_id     INT UNSIGNED NULL,                        -- chosen free item (choice modes)
  device_id        VARCHAR(64) NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_reward_selection (table_session_id, rule_id, customer_key),
  CONSTRAINT fk_reward_selections_session FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_reward_selections_rule    FOREIGN KEY (rule_id)          REFERENCES loyalty_rules (id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Redemptions remember which option / item was given
ALTER TABLE reward_redemptions
  ADD COLUMN option_id    INT UNSIGNED NULL AFTER rule_id,
  ADD COLUMN option_label VARCHAR(150) NULL AFTER rule_name,
  ADD COLUMN menu_item_id INT UNSIGNED NULL AFTER reward_item_name;
