-- =====================================================================
-- 004: parcel / takeaway orders, packing charges, service requests
--      (call waiter, request bill, take my order) and the online-payments switch.
-- =====================================================================

-- Restaurant-level switches
ALTER TABLE restaurant_settings
  ADD COLUMN online_payments_enabled  TINYINT(1)    NOT NULL DEFAULT 1    AFTER ordering_enabled,
  ADD COLUMN service_requests_enabled TINYINT(1)    NOT NULL DEFAULT 1    AFTER online_payments_enabled,
  ADD COLUMN parcel_orders_enabled    TINYINT(1)    NOT NULL DEFAULT 1    AFTER service_requests_enabled,
  ADD COLUMN parcel_packing_charge    DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER parcel_orders_enabled;

-- A takeaway counter is a "table" whose every guest gets an own session + bill
ALTER TABLE dining_tables
  ADD COLUMN kind ENUM('dine_in','takeaway') NOT NULL DEFAULT 'dine_in' AFTER area;

-- Who the session belongs to (required for takeaway tickets, optional for dine-in)
ALTER TABLE table_sessions
  ADD COLUMN customer_name  VARCHAR(100) NULL AFTER table_id,
  ADD COLUMN customer_phone VARCHAR(30)  NULL AFTER customer_name;

-- Dine-in vs parcel per order; packing charge snapshot taken at placement
ALTER TABLE orders
  ADD COLUMN order_type     ENUM('dine_in','parcel') NOT NULL DEFAULT 'dine_in' AFTER source,
  ADD COLUMN customer_phone VARCHAR(30)  NULL AFTER customer_name,
  ADD COLUMN packing_charge DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER subtotal,
  ADD KEY idx_orders_restaurant_type (restaurant_id, order_type, placed_at);

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

-- Guest → staff requests
CREATE TABLE service_requests (
  id                      INT UNSIGNED NOT NULL AUTO_INCREMENT,
  restaurant_id           INT UNSIGNED NOT NULL,
  location_id             INT UNSIGNED NOT NULL,
  table_id                INT UNSIGNED NOT NULL,
  table_session_id        INT UNSIGNED NULL,
  type                    ENUM('call_waiter','request_bill','take_order') NOT NULL,
  status                  ENUM('pending','acknowledged','completed','cancelled') NOT NULL DEFAULT 'pending',
  note                    VARCHAR(255) NULL,
  payment_preference      ENUM('cash','card','upi') NULL,          -- request_bill only
  customer_device_id      VARCHAR(64)  NULL,
  customer_name           VARCHAR(100) NULL,
  acknowledged_by_user_id INT UNSIGNED NULL,
  acknowledged_at         DATETIME NULL,
  resolved_by_user_id     INT UNSIGNED NULL,
  resolved_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),
  KEY idx_service_requests_restaurant (restaurant_id, status, created_at),
  KEY idx_service_requests_table (table_id, status),
  KEY idx_service_requests_session (table_session_id, status),
  CONSTRAINT fk_service_requests_restaurant FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE,
  CONSTRAINT fk_service_requests_table      FOREIGN KEY (table_id)      REFERENCES dining_tables (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
