-- allBETs: "Hats" (slug='hats') isn't a fitted apparel item like a tank top or hoodie — it
-- shouldn't have been swept into the S/M/L/XL sizing by allbets_add_apparel_sizes.sql just
-- because it carries gender='Unisex'. Headwear here uses one standard size that fits
-- Men/Women/Boys/Girls, not the 4-size chart.
--
-- Soft-deletes the S/M/L/XL variants that migration created for this one product, and adds
-- a single "Standard" size variant per existing color instead (20 units, same inventory
-- bridge pattern as before).
--
-- Idempotent — guards on SKU existence.
-- Run directly against the target DB:
--   mysql -u <user> -p 'stacie_Aggie_v1.0' < db/allbets_fix_hats_standard_size.sql

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

-- ── 1. Soft-delete the S/M/L/XL variants (identified by having a `size` key) ──
UPDATE dim_web_product_variants
SET is_deleted = 1
WHERE product_id = @hats_product_id
  AND is_deleted = 0
  AND JSON_CONTAINS_PATH(option_values_json, 'one', '$.size') = 1;

-- ── 2. Create a "Standard" size variant per color, from the ORIGINAL (pre-size) variant ──
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, '_STD'),
    JSON_MERGE_PATCH(v.option_values_json, JSON_OBJECT('size', 'Standard')),
    v.price, 20, 1, v.sort_order
FROM dim_web_product_variants v
WHERE v.product_id = @hats_product_id
  AND v.is_deleted = 1
  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, '_STD')
  );

-- ── 3. Inventory bridge for the new "Standard" variant(s) ──
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.product_id = @hats_product_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.product_id = @hats_product_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_fix_hats_standard_size.sql'
FROM dim_web_product_variants v
JOIN dim_inventory_items ii ON ii.linked_variant_id = v.id
WHERE v.product_id = @hats_product_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'
  );