-- Seeds 3 real Web Store products (dim_web_products + one default variant each) for LarCare,
-- closing the "no cart/checkout, Lorem ipsum copy" gap on /products. This is the SAME cart +
-- Stripe Checkout Session infrastructure already built generically for any ABOS storefront
-- (PublicWebController /store/products, /store/cart, /store/cart/{token}/checkout) — LarCare
-- just never had any dim_web_products rows before this. Also replaces the placeholder
-- Lorem-ipsum descriptions on the old affiliate-link "products" collection entries, in case
-- anything still reads from there.
-- Idempotent (guarded by slug existence per site), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/larcare_store_products_seed.sql

USE db_abos_v0.1;

SET @site_id = (SELECT id FROM dim_web_sites WHERE primary_domain = 'larcareservices.com' LIMIT 1);
SET @tenant_id = (SELECT tenant_id FROM dim_web_sites WHERE id = @site_id);
SET @business_id = (SELECT business_id FROM dim_web_sites WHERE id = @site_id);

-- ---- Standard Folding Wheelchair ----
INSERT INTO dim_web_products (tenant_id, business_id, site_id, slug, name, description, images_json, status, category_key, currency, base_price, product_type, sort_order)
SELECT @tenant_id, @business_id, @site_id, 'standard-wheelchair', 'Standard Folding Wheelchair',
  'Lightweight, foldable wheelchair suited for everyday indoor and outdoor use — durable steel frame, padded armrests, and swing-away footrests for easy transfers.',
  JSON_ARRAY('/images/products/wheelchair.jpg'), 'active', 'mobility-aids', 'USD', 249.99, 'PHYSICAL', 1
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_products WHERE site_id = @site_id AND slug = 'standard-wheelchair');

SET @wheelchair_id = (SELECT id FROM dim_web_products WHERE site_id = @site_id AND slug = 'standard-wheelchair');

INSERT INTO dim_web_product_variants (tenant_id, business_id, site_id, product_id, sku, price, stock_qty, is_default, sort_order)
SELECT * FROM (SELECT @tenant_id AS a, @business_id AS b, @site_id AS c, @wheelchair_id AS d, 'WHEELCHAIR-STD' AS e, 249.99 AS f, 40 AS g, 1 AS h, 1 AS i) AS t
WHERE @wheelchair_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_product_variants WHERE product_id = @wheelchair_id AND sku = 'WHEELCHAIR-STD');

-- ---- Touchless Infrared Thermometer ----
INSERT INTO dim_web_products (tenant_id, business_id, site_id, slug, name, description, images_json, status, category_key, currency, base_price, product_type, sort_order)
SELECT @tenant_id, @business_id, @site_id, 'infrared-thermometer', 'Touchless Infrared Thermometer',
  'Fast, accurate, no-contact temperature readings in one second — ideal for at-home care, with a fever alert light and memory recall for the last 32 readings.',
  JSON_ARRAY('/images/products/thermometer.jpg'), 'active', 'health-monitoring', 'USD', 34.99, 'PHYSICAL', 2
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_products WHERE site_id = @site_id AND slug = 'infrared-thermometer');

SET @thermometer_id = (SELECT id FROM dim_web_products WHERE site_id = @site_id AND slug = 'infrared-thermometer');

INSERT INTO dim_web_product_variants (tenant_id, business_id, site_id, product_id, sku, price, stock_qty, is_default, sort_order)
SELECT * FROM (SELECT @tenant_id AS a, @business_id AS b, @site_id AS c, @thermometer_id AS d, 'THERMOMETER-IR' AS e, 34.99 AS f, 120 AS g, 1 AS h, 1 AS i) AS t
WHERE @thermometer_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_product_variants WHERE product_id = @thermometer_id AND sku = 'THERMOMETER-IR');

-- ---- 7-Day Pill Organizer ----
INSERT INTO dim_web_products (tenant_id, business_id, site_id, slug, name, description, images_json, status, category_key, currency, base_price, product_type, sort_order)
SELECT @tenant_id, @business_id, @site_id, 'daily-pill-organizer', '7-Day Pill Organizer',
  'Easy-to-read weekly organizer with AM/PM compartments for every day of the week — large, labeled lids and a compact case that travels well.',
  JSON_ARRAY('/images/products/pill-organizer.jpg'), 'active', 'daily-living-aids', 'USD', 12.99, 'PHYSICAL', 3
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_products WHERE site_id = @site_id AND slug = 'daily-pill-organizer');

SET @pillorg_id = (SELECT id FROM dim_web_products WHERE site_id = @site_id AND slug = 'daily-pill-organizer');

INSERT INTO dim_web_product_variants (tenant_id, business_id, site_id, product_id, sku, price, stock_qty, is_default, sort_order)
SELECT * FROM (SELECT @tenant_id AS a, @business_id AS b, @site_id AS c, @pillorg_id AS d, 'PILLORG-7DAY' AS e, 12.99 AS f, 200 AS g, 1 AS h, 1 AS i) AS t
WHERE @pillorg_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_product_variants WHERE product_id = @pillorg_id AND sku = 'PILLORG-7DAY');

-- ---- Replace Lorem-ipsum copy on the old affiliate-link collection entries ----
UPDATE fact_web_entries e
JOIN dim_web_collections c ON e.collection_id = c.id
SET e.data_json = JSON_SET(e.data_json, '$.description',
  'Lightweight, foldable wheelchair suited for everyday indoor and outdoor use.')
WHERE c.collection_key = 'products' AND e.site_id = @site_id AND e.slug = 'standard-wheelchair';

UPDATE fact_web_entries e
JOIN dim_web_collections c ON e.collection_id = c.id
SET e.data_json = JSON_SET(e.data_json, '$.description',
  'Fast, accurate, no-contact temperature readings for at-home care.')
WHERE c.collection_key = 'products' AND e.site_id = @site_id AND e.slug = 'infrared-thermometer';

UPDATE fact_web_entries e
JOIN dim_web_collections c ON e.collection_id = c.id
SET e.data_json = JSON_SET(e.data_json, '$.description',
  'Easy-to-read weekly organizer with AM/PM compartments.')
WHERE c.collection_key = 'products' AND e.site_id = @site_id AND e.slug = 'daily-pill-organizer';