-- =============================================================================
-- SliceHub Pro — Migration 017: Online Module Extensions
--
-- Adds support for:
--   1. Optimistic locking on sh_visual_layers (version column)
--   2. Library metadata (library_category, library_sub_type) on sh_visual_layers
--   3. Idempotency / checkout locks (sh_checkout_locks table)
--   4. Guest order tracking (tracking_token on sh_orders)
--   5. Default tenant settings for online module
--
-- IDEMPOTENT: safe to re-run (INFORMATION_SCHEMA checks + IF NOT EXISTS).
-- Respects: tenant_id isolation, soft-delete, multi-tenancy.
-- =============================================================================

USE slicehub_pro_v2;
SET @sql_vl_ver = IF(@col_exists_vl_ver = 0,
  'ALTER TABLE sh_visual_layers ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0 COMMENT ''Optimistic locking — incremented on every save'' AFTER is_active',
  'SELECT ''version already exists on sh_visual_layers'''
);
PREPARE stmt FROM @sql_vl_ver; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql_vl_lc = IF(@col_exists_vl_lc = 0,
  'ALTER TABLE sh_visual_layers ADD COLUMN library_category VARCHAR(64) NULL COMMENT ''Library filter: base/sauce/cheese/meat/veg/herb/etc'' AFTER layer_sku',
  'SELECT ''library_category already exists on sh_visual_layers'''
);
PREPARE stmt FROM @sql_vl_lc; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql_vl_lst = IF(@col_exists_vl_lst = 0,
  'ALTER TABLE sh_visual_layers ADD COLUMN library_sub_type VARCHAR(64) NULL COMMENT ''Library sub-filter: tomato/mozzarella/pepperoni/etc'' AFTER library_category',
  'SELECT ''library_sub_type already exists on sh_visual_layers'''
);
PREPARE stmt FROM @sql_vl_lst; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql_vl_lib_idx = IF(@idx_exists_vl_lib = 0,
  'CREATE INDEX idx_vl_library ON sh_visual_layers (tenant_id, library_category, library_sub_type)',
  'SELECT ''idx_vl_library already exists'''
);
PREPARE stmt FROM @sql_vl_lib_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql_o_track = IF(@col_exists_o_track = 0,
  'ALTER TABLE sh_orders ADD COLUMN tracking_token CHAR(16) NULL COMMENT ''Short token for guest tracker URLs (16 hex chars)'' AFTER customer_phone',
  'SELECT ''tracking_token already exists on sh_orders'''
);
PREPARE stmt FROM @sql_o_track; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql_o_track_idx = IF(@idx_exists_o_track = 0,
  'CREATE INDEX idx_orders_tracking ON sh_orders (tracking_token)',
  'SELECT ''idx_orders_tracking already exists'''
);
PREPARE stmt FROM @sql_o_track_idx; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- ---------------------------------------------------------------------------
-- 5. sh_tenant_settings: default online module flags (per-tenant)
-- ---------------------------------------------------------------------------

INSERT IGNORE INTO sh_tenant_settings (tenant_id, setting_key, setting_value)
SELECT t.id, 'online_min_order_value', '0.00' FROM sh_tenant t
WHERE NOT EXISTS (
  SELECT 1 FROM sh_tenant_settings ts
  WHERE ts.tenant_id = t.id AND ts.setting_key = 'online_min_order_value'
);

INSERT IGNORE INTO sh_tenant_settings (tenant_id, setting_key, setting_value)
SELECT t.id, 'online_default_eta_min', '30' FROM sh_tenant t
WHERE NOT EXISTS (
  SELECT 1 FROM sh_tenant_settings ts
  WHERE ts.tenant_id = t.id AND ts.setting_key = 'online_default_eta_min'
);

INSERT IGNORE INTO sh_tenant_settings (tenant_id, setting_key, setting_value)
SELECT t.id, 'online_guest_checkout', '1' FROM sh_tenant t
WHERE NOT EXISTS (
  SELECT 1 FROM sh_tenant_settings ts
  WHERE ts.tenant_id = t.id AND ts.setting_key = 'online_guest_checkout'
);

INSERT IGNORE INTO sh_tenant_settings (tenant_id, setting_key, setting_value)
SELECT t.id, 'online_apple_pay_enabled', '0' FROM sh_tenant t
WHERE NOT EXISTS (
  SELECT 1 FROM sh_tenant_settings ts
  WHERE ts.tenant_id = t.id AND ts.setting_key = 'online_apple_pay_enabled'
);

INSERT IGNORE INTO sh_tenant_settings (tenant_id, setting_key, setting_value)
SELECT t.id, 'online_promotion_banner', '' FROM sh_tenant t
WHERE NOT EXISTS (
  SELECT 1 FROM sh_tenant_settings ts
  WHERE ts.tenant_id = t.id AND ts.setting_key = 'online_promotion_banner'
);
