-- =====================================================================
-- 005: shared table cart (one live cart per table session) and
--      pay-before-order (prepayment percentage for dine-in / parcel orders).
-- =====================================================================

-- Admin switches: guests must pay <percent>% of the order total before the order is created
ALTER TABLE restaurant_settings
  ADD COLUMN prepay_dine_in_enabled TINYINT(1)   NOT NULL DEFAULT 0      AFTER parcel_packing_charge,
  ADD COLUMN prepay_dine_in_percent DECIMAL(5,2) NOT NULL DEFAULT 100.00 AFTER prepay_dine_in_enabled,
  ADD COLUMN prepay_parcel_enabled  TINYINT(1)   NOT NULL DEFAULT 0      AFTER prepay_dine_in_percent,
  ADD COLUMN prepay_parcel_percent  DECIMAL(5,2) NOT NULL DEFAULT 100.00 AFTER prepay_parcel_enabled;

-- One shared cart per table session (created lazily)
CREATE TABLE session_carts (
  table_session_id INT UNSIGNED NOT NULL,
  restaurant_id    INT UNSIGNED NOT NULL,
  order_type       ENUM('dine_in','parcel') NOT NULL DEFAULT 'dine_in',
  notes            VARCHAR(500) NULL,
  version          INT UNSIGNED NOT NULL DEFAULT 0,               -- bumped on every change (realtime sync)
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (table_session_id),
  KEY idx_session_carts_restaurant (restaurant_id),
  CONSTRAINT fk_session_carts_session FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE session_cart_items (
  id                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id        INT UNSIGNED NOT NULL,
  table_session_id     INT UNSIGNED NOT NULL,
  menu_item_id         INT UNSIGNED NOT NULL,
  variant_id           INT UNSIGNED NULL,
  addon_ids            JSON NOT NULL,                             -- sorted unique ids
  quantity             INT UNSIGNED NOT NULL,
  special_instructions VARCHAR(300) NULL,
  added_by_device_id   VARCHAR(64)  NULL,
  added_by_name        VARCHAR(100) NULL,
  checkout_payment_id  INT UNSIGNED NULL,                         -- locked into a pending pay-before-order checkout
  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_cart_items_session (table_session_id),
  KEY idx_cart_items_checkout (checkout_payment_id),
  CONSTRAINT fk_cart_items_session   FOREIGN KEY (table_session_id) REFERENCES table_sessions (id) ON DELETE CASCADE,
  CONSTRAINT fk_cart_items_menu_item FOREIGN KEY (menu_item_id)     REFERENCES menu_items (id)     ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A payment is either for the bill or a pay-before-order deposit that creates an order on success
ALTER TABLE payments
  ADD COLUMN purpose           ENUM('bill','order_prepayment') NOT NULL DEFAULT 'bill' AFTER method,
  ADD COLUMN order_id          INT UNSIGNED NULL AFTER purpose,  -- the order created by a prepayment
  ADD COLUMN checkout_snapshot JSON NULL AFTER metadata;         -- priced cart snapshot (order_prepayment only)

ALTER TABLE orders
  ADD COLUMN prepaid_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER packing_charge,
  ADD COLUMN prepayment_id  INT UNSIGNED NULL AFTER prepaid_amount;
