-- =====================================================================
-- 008: loyalty rewards & offers — customers identified by mobile number,
--      visit-milestone and spend-threshold rules, staff-applied redemptions.
-- =====================================================================

ALTER TABLE restaurant_settings
  ADD COLUMN loyalty_enabled TINYINT(1) NOT NULL DEFAULT 0 AFTER prepay_parcel_percent;

-- Returning customers per restaurant (mobile number, no OTP)
CREATE TABLE customers (
  id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id INT UNSIGNED NOT NULL,
  phone         VARCHAR(20)  NOT NULL,                       -- normalised: digits with optional leading +
  name          VARCHAR(100) NULL,
  visit_count   INT UNSIGNED NOT NULL DEFAULT 0,             -- paid visits
  total_spent   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  last_visit_at DATETIME 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_customers_restaurant_phone (restaurant_id, phone),
  KEY idx_customers_restaurant_visits (restaurant_id, visit_count),
  CONSTRAINT fk_customers_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which identified customers took part in a session (a visit is counted once, when its bill is paid)
CREATE TABLE session_customers (
  table_session_id INT UNSIGNED NOT NULL,
  customer_id      INT UNSIGNED NOT NULL,
  restaurant_id    INT UNSIGNED NOT NULL,
  device_id        VARCHAR(64) NULL,
  joined_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  visit_counted    TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (table_session_id, customer_id),
  KEY idx_session_customers_customer (customer_id),
  CONSTRAINT fk_session_customers_session  FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_session_customers_customer FOREIGN KEY (customer_id)      REFERENCES customers (id)      ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin-defined rules
CREATE TABLE loyalty_rules (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id       INT UNSIGNED NOT NULL,
  name                VARCHAR(100) NOT NULL,                  -- "10th visit treat"
  description         VARCHAR(255) NULL,
  type                ENUM('visit_milestone','spend_threshold') NOT NULL,
  visit_count         INT UNSIGNED NULL,                      -- visit_milestone: N
  repeat_every        TINYINT(1) NOT NULL DEFAULT 0,          -- 1 = every Nth visit, 0 = only the Nth visit
  min_amount          DECIMAL(12,2) NULL,                     -- spend_threshold: bill item subtotal >= this
  reward_type         ENUM('percent_discount','flat_discount','free_item') NOT NULL,
  reward_value        DECIMAL(12,2) NULL,                     -- percent (1..100) or flat amount
  max_discount        DECIMAL(12,2) NULL,                     -- cap for percent discounts
  reward_menu_item_id INT UNSIGNED NULL,                      -- free_item
  valid_from          DATE NULL,
  valid_to            DATE NULL,
  is_active           TINYINT(1) NOT NULL DEFAULT 1,
  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_loyalty_rules_restaurant (restaurant_id, is_active, sort_order),
  CONSTRAINT fk_loyalty_rules_restaurant FOREIGN KEY (restaurant_id)       REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_loyalty_rules_item       FOREIGN KEY (reward_menu_item_id) REFERENCES menu_items (id)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A reward applied by staff to a bill (snapshot of the rule at redemption time)
CREATE TABLE reward_redemptions (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id       INT UNSIGNED NOT NULL,
  bill_id             INT UNSIGNED NOT NULL,
  rule_id             INT UNSIGNED NULL,
  customer_id         INT UNSIGNED NULL,                      -- NULL for spend-threshold offers
  visit_number        INT UNSIGNED NULL,                      -- visit_milestone: which visit it was redeemed on
  rule_name           VARCHAR(100) NOT NULL,
  reward_type         ENUM('percent_discount','flat_discount','free_item') NOT NULL,
  reward_value        DECIMAL(12,2) NULL,
  max_discount        DECIMAL(12,2) NULL,
  reward_item_name    VARCHAR(150) NULL,
  order_id            INT UNSIGNED NULL,                      -- free_item: the ₹0 order created for it
  redeemed_by_user_id INT UNSIGNED NULL,
  created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_redemptions_bill (bill_id),
  KEY idx_redemptions_customer_rule (customer_id, rule_id, visit_number),
  KEY idx_redemptions_restaurant (restaurant_id, created_at),
  CONSTRAINT fk_redemptions_bill FOREIGN KEY (bill_id) REFERENCES bills (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE bills
  ADD COLUMN offer_discount_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER discount_reason;
