-- allBETs: add Small/Medium/Large/X-Large size variants to every wearable product.
-- =============================================================================
-- "Wearable" = dim_web_products.gender IS NOT NULL (Men/Women/Unisex) — this already
-- exactly matches the catalog's own tagging: 14 apparel products carry a gender, the other
-- 15 (paddles, bags, drinkware, cards, dice, baseball cap, etc.) don't and are left unsized
-- per instruction ("rackets/bags won't have sizes").
--
-- For each wearable product's EXISTING variant (color, or no options at all), this creates
-- 4 new sibling variants — same options plus a `size` key — each with 20 units stock, and
-- soft-deletes the original sizeless variant. Non-wearable products keep their single
-- variant as-is; only its stock is reset to 20 units.
--
-- Idempotent — guards on SKU existence and on whether a source variant already carries a
-- `size` key, so re-running after new colors are added later only sizes the new ones.
-- Run directly against the target DB:
--   mysql -u <user> -p 'stacie_Aggie_v1.0' < db/allbets_add_apparel_sizes.sql
-- =============================================================================

SET @site_id := (SELECT id FROM dim_web_sites WHERE primary_domain = 'allbetsgo.com' LIMIT 1);

DROP TEMPORARY TABLE IF EXISTS tmp_sizes;
CREATE TEMPORARY TABLE tmp_sizes (
    code VARCHAR(5) COLLATE utf8mb4_unicode_ci,
    label VARCHAR(20) COLLATE utf8mb4_unicode_ci,
    sort_order INT
);
INSERT INTO tmp_sizes VALUES ('S','Small',1), ('M','Medium',2), ('L','Large',3), ('XL','X-Large',4);

-- ── 1. Create sized variants from each current sizeless variant of a wearable product ──
INSERT INTO dim_web_product_variants
    (tenant_id, business_id, site_id, product_id, sku, option_values_json, price, stock_qty, is_default, sort_order)
SELECT
    v.tenant_id, v.business_id, v.site_id, v.product_id,
    CONCAT(v.sku, '_', s.code),
    JSON_MERGE_PATCH(v.option_values_json, JSON_OBJECT('size', s.label)),
    v.price, 20,
    IF(v.is_default = 1 AND s.sort_order = 1, 1, 0),
    v.sort_order * 10 + s.sort_order
FROM dim_web_product_variants v
JOIN dim_web_products p ON p.id = v.product_id
CROSS JOIN tmp_sizes s
WHERE v.site_id = @site_id
  AND v.is_deleted = 0
  AND p.gender IS NOT NULL
  AND JSON_CONTAINS_PATH(v.option_values_json, 'one', '$.size') = 0
  AND NOT EXISTS (
      SELECT 1 FROM dim_web_product_variants existing
      WHERE existing.product_id = v.product_id AND existing.sku = CONCAT(v.sku, '_', s.code)
  );

-- ── 2. Soft-delete the old sizeless variants for wearable products (now superseded) ──
UPDATE dim_web_product_variants v
JOIN dim_web_products p ON p.id = v.product_id
SET v.is_deleted = 1
WHERE v.site_id = @site_id
  AND v.is_deleted = 0
  AND p.gender IS NOT NULL
  AND JSON_CONTAINS_PATH(v.option_values_json, 'one', '$.size') = 0;

-- ── 3. Non-wearable products: reset existing variant stock to 20 units ──
-- Snapshot "before" quantities first so the movement audit trail records the real delta.
DROP TEMPORARY TABLE IF EXISTS tmp_nonwearable_before;
CREATE TEMPORARY TABLE tmp_nonwearable_before AS
SELECT ii.id AS inventory_item_id, fs.warehouse_id, fs.quantity_on_hand AS old_qty, v.tenant_id
FROM dim_web_product_variants v
JOIN dim_web_products p ON p.id = v.product_id
JOIN dim_inventory_items ii ON ii.linked_variant_id = v.id
JOIN fact_inventory_stock fs ON fs.inventory_item_id = ii.id
WHERE v.site_id = @site_id AND v.is_deleted = 0 AND p.gender IS NULL AND fs.quantity_on_hand <> 20;

UPDATE dim_web_product_variants v
JOIN dim_web_products p ON p.id = v.product_id
SET v.stock_qty = 20
WHERE v.site_id = @site_id AND v.is_deleted = 0 AND p.gender IS NULL AND v.stock_qty <> 20;

UPDATE fact_inventory_stock fs
JOIN tmp_nonwearable_before t ON t.inventory_item_id = fs.inventory_item_id AND t.warehouse_id = fs.warehouse_id
SET fs.quantity_on_hand = 20;

INSERT INTO fact_inventory_movements (tenant_id, warehouse_id, inventory_item_id, movement_type, quantity_delta, reference_type, notes)
SELECT tenant_id, warehouse_id, inventory_item_id, 'ADJUSTMENT', 20 - old_qty, 'SEED', 'Reset to 20 units — allbets_add_apparel_sizes.sql'
FROM tmp_nonwearable_before;

-- ── 4. Inventory bridge for the newly created sized variants (same pattern the original
--       catalog seed's section 3 used — WEB_VARIANT items, on-hand stock, RECEIPT movement) ──
INSERT INTO dim_inventory_items (tenant_id, business_id, sku, name, item_kind, linked_variant_id, reorder_point)
SELECT v.tenant_id, v.business_id, v.sku, CONCAT(p.name, ' — ', v.sku), 'WEB_VARIANT', v.id, 0
FROM dim_web_product_variants v
JOIN dim_web_products p ON v.product_id = p.id
WHERE v.site_id = @site_id AND v.is_deleted = 0
  AND NOT EXISTS (SELECT 1 FROM dim_inventory_items ii WHERE ii.linked_variant_id = v.id);

SET @warehouse_id := (SELECT id FROM dim_warehouses WHERE site_id = @site_id AND is_default = 1 AND is_deleted = 0 LIMIT 1);

INSERT INTO fact_inventory_stock (tenant_id, warehouse_id, inventory_item_id, quantity_on_hand, quantity_reserved)
SELECT v.tenant_id, @warehouse_id, ii.id, v.stock_qty, 0
FROM dim_web_product_variants v
JOIN dim_inventory_items ii ON ii.linked_variant_id = v.id
WHERE v.site_id = @site_id AND v.is_deleted = 0 AND @warehouse_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_inventory_stock fs WHERE fs.warehouse_id = @warehouse_id AND fs.inventory_item_id = ii.id);

INSERT INTO fact_inventory_movements (tenant_id, warehouse_id, inventory_item_id, movement_type, quantity_delta, reference_type, notes)
SELECT v.tenant_id, @warehouse_id, ii.id, 'RECEIPT', v.stock_qty, 'SEED', 'Initial stock — allbets_add_apparel_sizes.sql'
FROM dim_web_product_variants v
JOIN dim_inventory_items ii ON ii.linked_variant_id = v.id
WHERE v.site_id = @site_id AND v.is_deleted = 0 AND @warehouse_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM fact_inventory_movements fm
    WHERE fm.warehouse_id = @warehouse_id AND fm.inventory_item_id = ii.id AND fm.reference_type = 'SEED'
  );

DROP TEMPORARY TABLE IF EXISTS tmp_sizes;
DROP TEMPORARY TABLE IF EXISTS tmp_nonwearable_before;