-- ====================================================================
-- Cart recovery links, customer email language, cookie consent (2026-09-29)
--   1. carts.recovery_token_hash / recovery_expires_at — the SHA-256 of the
--      secret token in an abandoned-cart email link (the token itself is
--      never stored). The link restores the cart in any browser, signed in
--      or not. carts.recovered_at = last time a link was used (reporting).
--   2. carts.recovered_from_cart_id — on a guest cart restored from a link:
--      the cart it was copied from, so signing in afterwards merges the two
--      without doubling quantities.
--   3. carts.locale, orders.locale, users.preferred_locale — the language the
--      customer was using, so emails sent later (order status, cart
--      reminders, security notices) go out in that language.
--   4. site_settings.cookie_consent_enabled — show the cookie banner and hold
--      analytics/ad pixels until the shopper accepts (on by default).
-- Safe to run more than once.
-- ====================================================================

SET NAMES utf8mb4;

-- carts.recovery_token_hash
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'carts' AND COLUMN_NAME = 'recovery_token_hash');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `carts` ADD COLUMN `recovery_token_hash` CHAR(64) NULL DEFAULT NULL, ADD COLUMN `recovery_expires_at` DATETIME NULL DEFAULT NULL, ADD UNIQUE KEY `uk_carts_recovery_token` (`recovery_token_hash`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- carts.recovered_at
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'carts' AND COLUMN_NAME = 'recovered_at');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `carts` ADD COLUMN `recovered_at` DATETIME NULL DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- carts.recovered_from_cart_id (no FK: the source cart is deleted when its owner orders)
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'carts' AND COLUMN_NAME = 'recovered_from_cart_id');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `carts` ADD COLUMN `recovered_from_cart_id` VARCHAR(100) NULL DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- carts.locale
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'carts' AND COLUMN_NAME = 'locale');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `carts` ADD COLUMN `locale` VARCHAR(10) NULL DEFAULT NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- orders.locale
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders' AND COLUMN_NAME = 'locale');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `orders` ADD COLUMN `locale` VARCHAR(10) NULL DEFAULT NULL COMMENT ''Language the order was placed in; order emails use it'' AFTER `currency`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- users.preferred_locale
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'users' AND COLUMN_NAME = 'preferred_locale');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `users` ADD COLUMN `preferred_locale` VARCHAR(10) NULL DEFAULT NULL COMMENT ''Last storefront language used; account emails use it'' AFTER `phone`',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- site_settings.cookie_consent_enabled
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'site_settings' AND COLUMN_NAME = 'cookie_consent_enabled');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `site_settings` ADD COLUMN `cookie_consent_enabled` TINYINT(1) NOT NULL DEFAULT 1',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
