USE db_abos_v0.1;

-- =============================================================================================
-- web_store_facets_and_checkout.sql — shipping-rate + Cash-on-Delivery configuration on
-- dim_web_sites, and merchandising facets on dim_web_products (companion to web_store_module.sql,
-- applied after it).
--
-- Closes the following gaps for the allBETs storefront (allbetsgo.com):
--   - Shipping-rate calculation (STANDARD/EXPRESS, free-shipping threshold) — see
--     WebShippingRateService / PublicWebService.checkout.
--   - Cash-on-Delivery as a second payment method alongside Stripe — see
--     PublicWebService.checkout's COD branch.
--   - Product merchandising facets (fabric/outfit/gender/age group/badge) for storefront
--     filtering and display.
--
-- Safe to re-run: every ALTER below is information_schema-guarded, same pattern as
-- hr_events_notes_column.sql / web_store_stock_reservation.sql. No history table — every script
-- re-runs on every deploy.
-- =============================================================================================

-- ── dim_web_sites: shipping-rate configuration (A3) ─────────────────────────────────────────

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'standard_shipping_cents'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN standard_shipping_cents INT NULL DEFAULT 800 AFTER notify_emails',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'free_shipping_threshold_cents'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN free_shipping_threshold_cents INT NULL DEFAULT 12000 AFTER standard_shipping_cents',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'express_shipping_cents'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN express_shipping_cents INT NULL DEFAULT 1800 AFTER free_shipping_threshold_cents',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ── dim_web_sites: Cash-on-Delivery configuration (A4) ──────────────────────────────────────

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'cod_fee_cents'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN cod_fee_cents INT NULL DEFAULT 300 AFTER express_shipping_cents',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'cod_max_order_cents'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN cod_max_order_cents INT NULL DEFAULT 40000 AFTER cod_fee_cents',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- JSON columns cannot carry a DEFAULT in either engine — the application supplies '["US"]' when
-- this is NULL (see WebShippingRateService / PublicWebService.checkout's COD branch).
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_sites' AND COLUMN_NAME = 'cod_countries_json'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_sites ADD COLUMN cod_countries_json JSON NULL AFTER cod_max_order_cents',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ── dim_web_products: merchandising facets (A5) ─────────────────────────────────────────────

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_products' AND COLUMN_NAME = 'fabric'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_products ADD COLUMN fabric VARCHAR(100) NULL AFTER sort_order',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_products' AND COLUMN_NAME = 'outfit_key'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_products ADD COLUMN outfit_key VARCHAR(50) NULL AFTER fabric',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_products' AND COLUMN_NAME = 'gender'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_products ADD COLUMN gender VARCHAR(20) NULL AFTER outfit_key',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_products' AND COLUMN_NAME = 'age_group'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_products ADD COLUMN age_group VARCHAR(20) NULL AFTER gender',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_products' AND COLUMN_NAME = 'badge'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_web_products ADD COLUMN badge VARCHAR(20) NULL AFTER age_group',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;