-- Converts LarCare's remaining hardcoded-JSX pages into real CMS Pages (dim_web_pages),
-- same pattern as About Us / Contact Us / Portal / Privacy Policy: Home, Healthcare index,
-- Careers, Products, and the Forms index. Uses the new serviceGrid/statsGrid/coreValueGrid/
-- jobGrid/trustBadges block types added to BlockRenderer + WEB-THEME-DESIGN-CONTEXT.md
-- alongside this migration, so the previously-dynamic-but-hardcoded-copy grids on these
-- pages are now fully authored in layout_json.
-- Also registers every existing static image under public/images/ as a dim_web_media row,
-- so the Media library actually lists what the site uses (previously 0 rows).
-- Idempotent (guarded by slug/storage_path existence), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/larcare_pages_media_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);

-- ================= Home =================
INSERT INTO dim_web_pages (tenant_id, business_id, site_id, slug, title, route, status, layout_json, meta_title)
SELECT @tenant_id, @business_id, @site_id, 'home', 'Home', '/', 'published', JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'home-hero', 'type', 'hero', 'props', JSON_OBJECT(
      'heading', 'Personal home care for every unique situation',
      'subheading', 'We take tender loving care every step of the way. Larry Puts the Care in LarCare',
      'imageUrl', '/images/hero/home-hero.jpg',
      'ctaLabel', 'Contact Us', 'ctaHref', '/about-us/contact-us'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-intro', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>Excellent Senior Care for Extraordinary Families</h2><p>At LarCare, we&#39;re dedicated to enriching the lives of our seniors beyond healthcare. Our personalized care plans include safe transportation to church, BINGO, and more. We ensure a seamless blend of comfort, joy, and independence for you and your loved ones.</p><p>With every detail managed with compassion and professionalism, LarCare stands as your partner in creating an experience that&#39;s not just satisfactory, but truly heartwarming. Your well-being is our mission.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-services-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>Our LarCare Services</h2><p>Your well-being is our utmost priority. At LarCare, we pledge to offer unwavering support and comprehensive care tailored to meet your unique needs.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-services-grid', 'type', 'serviceGrid', 'props', JSON_OBJECT('columns', 3, 'featuredOnly', true, 'limit', 3), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-services-more', 'type', 'button', 'props', JSON_OBJECT('label', 'View All Services', 'href', '/healthcare', 'variant', 'outline'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-about-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>About LarCare</h2><p>We are a team of highly-skilled healthcare professionals focused on delivering a wide range of specialized home healthcare services to patients who wish to maintain their independence at home.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-stats-grid', 'type', 'statsGrid', 'props', JSON_OBJECT('columns', 3), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-about-more', 'type', 'button', 'props', JSON_OBJECT('label', 'More About Us', 'href', '/about-us', 'variant', 'outline'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-values-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>We are the Heart of Personal Care!</h2><p>LarCare was named after Larry the visionary. Larry Cares is at the heart of all of the core values. LarCare believes in:</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-values-grid', 'type', 'coreValueGrid', 'props', JSON_OBJECT(), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-growing', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>We are Growing.</h2><p>We&#39;re expanding the states we serve — need assistance? <a href="/about-us/contact-us">Complete the contact form</a>, or share feedback via our <a href="/forms/patient-satisfaction-survey">Patient Satisfaction Survey</a>.</p>'
    ), 'children', JSON_ARRAY())
  )
), 'LarCare Services | Personal Home Care for Every Unique Situation'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'home');

-- ================= Healthcare index =================
INSERT INTO dim_web_pages (tenant_id, business_id, site_id, slug, title, route, status, layout_json, meta_title)
SELECT @tenant_id, @business_id, @site_id, 'healthcare', 'Healthcare Services', '/healthcare', 'published', JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'healthcare-hero', 'type', 'hero', 'props', JSON_OBJECT(
      'heading', 'Personal home care for every unique situation',
      'subheading', 'We take tender loving care every step of the way. Larry Puts the Care in LarCare.',
      'imageUrl', '/images/hero/home-hero.jpg'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'healthcare-grid', 'type', 'serviceGrid', 'props', JSON_OBJECT('columns', 3), 'children', JSON_ARRAY())
  )
), 'Healthcare Services | LarCare Services'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'healthcare');

-- ================= Careers =================
INSERT INTO dim_web_pages (tenant_id, business_id, site_id, slug, title, route, status, layout_json, meta_title)
SELECT @tenant_id, @business_id, @site_id, 'careers', 'Careers', '/about-us/careers', 'published', JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'careers-hero', 'type', 'hero', 'props', JSON_OBJECT(
      'heading', 'Careers: In-Home Care Nursing & Caregiver Jobs',
      'subheading', 'Working in in-home care is more than just a job — it''s a chance to make a real difference in someone''s daily life.',
      'imageUrl', '/images/hero/careers-hero.jpg'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'careers-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2 style="text-align:center">Types of Home Health Care Jobs</h2>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'careers-grid', 'type', 'jobGrid', 'props', JSON_OBJECT('columns', 4), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'careers-apply', 'type', 'button', 'props', JSON_OBJECT('label', 'Employment Application', 'href', '/forms', 'variant', 'primary'), 'children', JSON_ARRAY())
  )
), 'Careers | LarCare Services'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'careers');

-- ================= Products =================
INSERT INTO dim_web_pages (tenant_id, business_id, site_id, slug, title, route, status, layout_json, meta_title)
SELECT @tenant_id, @business_id, @site_id, 'products', 'Products', '/products', 'published', JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'products-hero', 'type', 'hero', 'props', JSON_OBJECT(
      'heading', 'Discover Products That Fit Your Lifestyle',
      'subheading', 'Everyday essentials for care at home — browse, add to cart, and check out securely.',
      'imageUrl', '/images/hero/products-hero.jpg'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'products-grid', 'type', 'productGrid', 'props', JSON_OBJECT('columns', 3), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'products-trust', 'type', 'trustBadges', 'props', JSON_OBJECT('badges', JSON_ARRAY(
      JSON_OBJECT('icon', '✅', 'title', '100% High Quality Product', 'text', 'Thoroughly inspected for top performance.'),
      JSON_OBJECT('icon', '🚚', 'title', 'Free Shipping', 'text', 'Fast shipping within 3–5 days.'),
      JSON_OBJECT('icon', '💰', 'title', 'Money Back Guarantee', 'text', '30-day hassle-free refunds.')
    )), 'children', JSON_ARRAY())
  )
), 'Products | LarCare Services'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'products');

-- Products page also gets a testimonialGrid block (added after the initial products page
-- insert above was already deployed elsewhere — appended via UPDATE so re-running this file
-- against an environment that already has the page still converges to the same end state).
UPDATE dim_web_pages
SET layout_json = JSON_ARRAY_APPEND(layout_json, '$.blocks', JSON_OBJECT(
  'id', 'products-reviews', 'type', 'testimonialGrid', 'props', JSON_OBJECT(), 'children', JSON_ARRAY()
))
WHERE site_id = @site_id AND slug = 'products'
  AND NOT JSON_CONTAINS(JSON_EXTRACT(layout_json, '$.blocks[*].type'), '"testimonialGrid"');

-- ================= Forms index =================
INSERT INTO dim_web_pages (tenant_id, business_id, site_id, slug, title, route, status, layout_json, meta_title)
SELECT @tenant_id, @business_id, @site_id, 'forms', 'Forms', '/forms', 'published', JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'forms-hero', 'type', 'hero', 'props', JSON_OBJECT('heading', 'Forms', 'imageUrl', '/images/hero/forms-hero.jpg'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'forms-employment-links', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>Employment Application</h2><ul><li><a href="/forms/employment-application">Employment Application</a></li><li><a href="/forms/employment-direct-deposit">Employment Direct Deposit</a></li><li><a href="/forms/employment-w9">Employment W9</a></li></ul><h2>Patient Satisfaction &amp; Outcomes Survey</h2><ul><li><a href="/forms/patient-satisfaction-survey">Patient Satisfaction &amp; Outcomes Survey</a></li></ul>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'forms-sms-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2 style="text-align:center">SMS Consent</h2><p style="text-align:center">Consent to receive SMS updates from LarCare related to customer support, service updates, and occasional marketing communications.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'forms-sms-embed', 'type', 'formEmbed', 'props', JSON_OBJECT('formKey', 'sms-consent'), 'children', JSON_ARRAY())
  )
), 'Forms | LarCare Services'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'forms');

-- ================= Media library backfill =================
-- Registers every static image the site actually uses, so the Media tab reflects reality
-- instead of showing 0 rows. Images stay served as Next.js static /public assets either
-- way (storage_path mirrors the public URL rather than a real uploaded-file path) — this
-- is a catalog of what's in use, not a migration of file storage.
INSERT INTO dim_web_media (tenant_id, business_id, site_id, storage_path, public_url, original_filename, mime_type)
SELECT * FROM (SELECT t.a, t.b, t.c, t.d, t.e, t.f, t.g FROM (SELECT
  @tenant_id AS a, @business_id AS b, @site_id AS c,
  path_and_name.path AS d, path_and_name.path AS e, path_and_name.name AS f, 'image/jpeg' AS g
  FROM (
    SELECT '/images/hero/home-hero.jpg' AS path, 'home-hero.jpg' AS name UNION ALL
    SELECT '/images/hero/about-hero.jpg', 'about-hero.jpg' UNION ALL
    SELECT '/images/hero/careers-hero.jpg', 'careers-hero.jpg' UNION ALL
    SELECT '/images/hero/contact-hero.jpg', 'contact-hero.jpg' UNION ALL
    SELECT '/images/hero/contact-team.jpg', 'contact-team.jpg' UNION ALL
    SELECT '/images/hero/forms-hero.jpg', 'forms-hero.jpg' UNION ALL
    SELECT '/images/hero/forms-team.jpg', 'forms-team.jpg' UNION ALL
    SELECT '/images/hero/growing.jpg', 'growing.jpg' UNION ALL
    SELECT '/images/hero/mission.jpg', 'mission.jpg' UNION ALL
    SELECT '/images/hero/portal-hero.jpg', 'portal-hero.jpg' UNION ALL
    SELECT '/images/hero/products-hero.jpg', 'products-hero.jpg' UNION ALL
    SELECT '/images/products/pill-organizer.jpg', 'pill-organizer.jpg' UNION ALL
    SELECT '/images/products/thermometer.jpg', 'thermometer.jpg' UNION ALL
    SELECT '/images/products/wheelchair.jpg', 'wheelchair.jpg' UNION ALL
    SELECT '/images/services/dementia-care.jpg', 'dementia-care.jpg' UNION ALL
    SELECT '/images/services/end-of-life-care.jpg', 'end-of-life-care.jpg' UNION ALL
    SELECT '/images/services/home-personal-care.jpg', 'home-personal-care.jpg' UNION ALL
    SELECT '/images/services/transitional-care.jpg', 'transitional-care.jpg' UNION ALL
    SELECT '/images/services/veteran-care.jpg', 'veteran-care.jpg' UNION ALL
    SELECT '/images/blog/10-signs.jpg', '10-signs.jpg' UNION ALL
    SELECT '/images/blog/affordability.jpg', 'affordability.jpg' UNION ALL
    SELECT '/images/blog/aging-in-place.jpg', 'aging-in-place.jpg' UNION ALL
    SELECT '/images/blog/complete-guide.jpg', 'complete-guide.jpg' UNION ALL
    SELECT '/images/blog/dementia-guide.jpg', 'dementia-guide.jpg' UNION ALL
    SELECT '/images/blog/medicaid-guide.jpg', 'medicaid-guide.jpg' UNION ALL
    SELECT '/images/blog/medicare-guide.jpg', 'medicare-guide.jpg'
  ) AS path_and_name
) AS t) AS dedup
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM dim_web_media m WHERE m.site_id = @site_id AND m.storage_path = dedup.d
  );