-- =============================================================================
-- seed_larcare_business_and_site.sql — Onboard LarCare as a Business + public
-- website inside ABOS's tenant/business-aware Web CMS module.
--
-- Source of truth for the seeded content: LarCare\frontend\src\lib\content\
-- {site,services,blog,products,misc}.json and LarCare\frontend\src\lib\types.ts
-- (as of the LarCare-to-ABOS migration).
--
-- Idempotent: every INSERT is guarded with `WHERE NOT EXISTS (...)` against the
-- table's natural/unique key, matching the pattern used by app_settings.sql.
-- Safe to re-run.
--
-- IMPORTANT: public_site_key below is the fixed, hardcoded X-Site-Key LarCare's
-- frontend must send to ABOS's public Web API. Value:
--   pSlfeq9OCm5MvZyoFguIK7jy0uz2aKShJkxZxTSWXwzPOpYJ
-- =============================================================================

SET @tenant_id := 1;

-- ── 1. Business ──────────────────────────────────────────────────────────────
INSERT INTO businesses (tenant_id, name, code, industry, country, email, phone, address, status, created_by, updated_by, is_deleted)
SELECT @tenant_id, 'LarCare Services', 'LARCARE', 'Healthcare', 'United States',
       'info@larcareservices.com', '803-209-8667', '178 Columbia St, Chester, SC 29706',
       'Active', NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM businesses WHERE tenant_id = @tenant_id AND name = 'LarCare Services'
);

SET @business_id := (SELECT id FROM businesses WHERE tenant_id = @tenant_id AND name = 'LarCare Services' LIMIT 1);

-- ── 2. Site ──────────────────────────────────────────────────────────────────
INSERT INTO dim_web_sites (
    tenant_id, business_id, name, slug, primary_domain, public_site_key,
    default_locale, is_published,
    company_name, tagline, contact_phone, contact_email, address,
    regions_served_json, social_links_json,
    created_by, updated_by, is_deleted
)
SELECT
    @tenant_id, @business_id, 'LarCare Services', 'larcare', 'larcareservices.com',
    'pSlfeq9OCm5MvZyoFguIK7jy0uz2aKShJkxZxTSWXwzPOpYJ',
    'en', 1,
    'LarCare Services', 'Personal home care for every unique situation',
    '803-209-8667', 'info@larcareservices.com', '178 Columbia St, Chester, SC 29706',
    JSON_ARRAY('South Carolina', 'North Carolina', 'Georgia'),
    JSON_ARRAY(
        JSON_OBJECT('platform', 'facebook', 'url', 'https://facebook.com/larcareservices'),
        JSON_OBJECT('platform', 'x', 'url', 'https://x.com/larcareservices'),
        JSON_OBJECT('platform', 'instagram', 'url', 'https://instagram.com/larcareservices'),
        JSON_OBJECT('platform', 'linkedin', 'url', 'https://linkedin.com/company/larcareservices')
    ),
    NULL, NULL, 0
WHERE NOT EXISTS (
    -- dim_web_sites has three independent UNIQUE KEYs (public_site_key, primary_domain,
    -- and (tenant_id, slug)) — this guard originally checked only slug, so a row already
    -- present under a different slug but the same public_site_key/primary_domain still hit
    -- ERROR 1062 on uq_dim_web_sites_key. Check all three so this INSERT is skipped
    -- whenever the row already exists by any of them.
    SELECT 1 FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'larcare')
       OR public_site_key = 'pSlfeq9OCm5MvZyoFguIK7jy0uz2aKShJkxZxTSWXwzPOpYJ'
       OR primary_domain = 'larcareservices.com'
);

-- Resolve by the same three keys the guard above checks — the production row was found
-- to already exist under a different slug than 'larcare' (matched instead on
-- public_site_key/primary_domain), which made a slug-only lookup here return NULL and
-- broke every downstream INSERT that requires site_id NOT NULL.
SET @site_id := (
    SELECT id FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'larcare')
       OR public_site_key = 'pSlfeq9OCm5MvZyoFguIK7jy0uz2aKShJkxZxTSWXwzPOpYJ'
       OR primary_domain = 'larcareservices.com'
    LIMIT 1
);

-- ── 3. Collections ───────────────────────────────────────────────────────────
INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'care-services', 'Care Services', 'Care Service', 'collection', '/healthcare/{slug}', 1, 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'care-services');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'blog-posts', 'Blog Posts', 'Blog Post', 'collection', '/blog/{slug}', 1, 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'blog-posts');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'products', 'Products', 'Product', 'collection', '/products/{slug}', 0, 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'products');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'testimonials', 'Testimonials', 'Testimonial', 'collection', NULL, 0, 1, 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'testimonials');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'core-values', 'Core Values', 'Core Value', 'collection', NULL, 0, 1, 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'core-values');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'about-stats', 'About Stats', 'About Stat', 'collection', NULL, 0, 1, 6, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'about-stats');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, route_pattern, has_seo_fields, is_system, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'job-openings', 'Job Openings', 'Job Opening', 'collection', '/about-us/careers/{slug}', 0, 1, 7, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'job-openings');

SET @coll_care_services := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'care-services' LIMIT 1);
SET @coll_blog_posts    := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'blog-posts' LIMIT 1);
SET @coll_products      := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'products' LIMIT 1);
SET @coll_testimonials  := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'testimonials' LIMIT 1);
SET @coll_core_values   := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'core-values' LIMIT 1);
SET @coll_about_stats   := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'about-stats' LIMIT 1);
SET @coll_job_openings  := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'job-openings' LIMIT 1);

-- ── 4. Collection fields ─────────────────────────────────────────────────────
-- care-services: shortDescription, heroImage, bodyHtml, highlights[], isFeaturedOnHome
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'shortDescription', 'Short Description', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_care_services AND field_key = 'shortDescription');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'heroImage', 'Hero Image', 'image', 0, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_care_services AND field_key = 'heroImage');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'bodyHtml', 'Body HTML', 'richtext', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_care_services AND field_key = 'bodyHtml');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'highlights', 'Highlights', 'json_array', 0, 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_care_services AND field_key = 'highlights');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'isFeaturedOnHome', 'Featured on Home', 'boolean', 0, 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_care_services AND field_key = 'isFeaturedOnHome');

-- blog-posts: excerpt, coverImage, bodyHtml, author, publishedAt, tags[]
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'excerpt', 'Excerpt', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'excerpt');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'coverImage', 'Cover Image', 'image', 0, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'coverImage');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'bodyHtml', 'Body HTML', 'richtext', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'bodyHtml');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'author', 'Author', 'text', 0, 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'author');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'publishedAt', 'Published At', 'date', 0, 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'publishedAt');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'tags', 'Tags', 'json_array', 0, 6, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blog_posts AND field_key = 'tags');

-- products: description, image, price, externalUrl
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'description', 'Description', 'textarea', 0, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_products AND field_key = 'description');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'image', 'Image', 'image', 0, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_products AND field_key = 'image');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'price', 'Price', 'number', 0, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_products AND field_key = 'price');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'externalUrl', 'External URL', 'url', 0, 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_products AND field_key = 'externalUrl');

-- testimonials: authorName, rating, quote
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'authorName', 'Author Name', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_testimonials AND field_key = 'authorName');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'rating', 'Rating', 'number', 0, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_testimonials AND field_key = 'rating');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'quote', 'Quote', 'textarea', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_testimonials AND field_key = 'quote');

-- core-values: icon, title(reuse entry title), description
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'icon', 'Icon', 'text', 0, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_core_values AND field_key = 'icon');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_core_values AND field_key = 'description');

-- about-stats: icon, title
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_about_stats, 'icon', 'Icon', 'text', 0, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_about_stats AND field_key = 'icon');

-- job-openings: type, location, summary
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'type', 'Type', 'text', 0, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_job_openings AND field_key = 'type');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'location', 'Location', 'text', 0, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_job_openings AND field_key = 'location');
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'summary', 'Summary', 'textarea', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_job_openings AND field_key = 'summary');

-- ── 5. Entries: care-services (from services.json) ──────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_featured, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'home-and-personal-care-services', 'Home and Personal Care Services', 'published', 1, 1,
    JSON_OBJECT('shortDescription', 'Non-medical assistance, companionship, and daily living support delivered in the comfort of home.',
                'heroImage', '/images/services/home-personal-care.jpg',
                'bodyHtml', '<p>LarCare understands what families are going through and we are here to help. Home Care provides non-medical assistance to older adults or individuals with disabilities, directly within their homes. The core of companion care lies in offering emotional support and fostering social interaction.</p><p>Our certified nurse assistants (CNAs) offer a helping hand with essential activities of daily living such as bathing, dressing, and meal preparation. Beyond these, our services can include help with transportation, managing finances, scheduling appointments, and offering companionship and emotional support.</p>',
                'highlights', JSON_ARRAY('Meal planning & preparation', 'Bathing, grooming & personal hygiene', 'Light housekeeping & laundry', 'Medication reminders', 'Mobility, transferring & toileting assistance', 'Errands & transportation'),
                'isFeaturedOnHome', true),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_care_services AND slug = 'home-and-personal-care-services');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_featured, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'alzheimers-dementia-care', 'Alzheimer''s Disease and Dementia Care', 'published', 2, 0,
    JSON_OBJECT('shortDescription', 'Specialized, patient-centered memory care for every stage, tailored to each family''s experience.',
                'heroImage', '/images/services/dementia-care.jpg',
                'bodyHtml', '<p>Alzheimer''s disease, a degenerative and incurable disorder, progressively impairs memory, cognitive abilities, and the capacity to carry out daily activities. At LarCare, we recognize the challenges that come with caring for seniors with Alzheimer''s disease or dementia. Our team is specially trained to support individuals with these conditions, ensuring they receive compassionate and expert care.</p>',
                'highlights', JSON_ARRAY('Structured, memory-focused routines', 'Specially trained dementia caregivers', 'Behavioral and mood support', 'Family guidance and education', 'Safety-focused home environment checks'),
                'isFeaturedOnHome', false),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_care_services AND slug = 'alzheimers-dementia-care');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_featured, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'end-of-life-care', 'End-of-Life Care', 'published', 3, 1,
    JSON_OBJECT('shortDescription', 'Physical, emotional, and spiritual comfort for patients and families during life''s most crucial time.',
                'heroImage', '/images/services/end-of-life-care.jpg',
                'bodyHtml', '<p>LarCare is dedicated to providing both physical and emotional comfort to our patients and their families during crucial times. Our skilled team of compassionate professionals is committed to delivering exceptional medical, emotional, and spiritual support.</p><p>Hospice care is in-home medical care for people who are no longer seeking a cure for their advanced illness; instead, hospice care focuses on achieving comfort for mind, body and spirit.</p>',
                'highlights', JSON_ARRAY('Dignity-centered hospice support', '24/7 comfort-focused caregiving', 'Family emotional support', 'Coordination with medical providers'),
                'isFeaturedOnHome', true),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_care_services AND slug = 'end-of-life-care');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_featured, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'transitional-care', 'Transitional Care', 'published', 4, 0,
    JSON_OBJECT('shortDescription', 'Easing the move from hospital or rehab facility back home for a safer post-surgery recovery.',
                'heroImage', '/images/services/transitional-care.jpg',
                'bodyHtml', '<p>We can ease your loved one''s transition from the hospital or rehabilitation facility to home, which can lead to a safer, more comfortable post-surgery recovery. Professional in-home caregivers provide care that can help you avoid hospital readmission.</p>',
                'highlights', JSON_ARRAY('Medication reminders', 'Doctor-ordered diet support', 'Mobility & exercise monitoring', 'Fall-risk prevention'),
                'isFeaturedOnHome', false),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_care_services AND slug = 'transitional-care');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_featured, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_care_services, 'veteran-care', 'Veteran Care', 'published', 5, 1,
    JSON_OBJECT('shortDescription', 'Home Health Aide services for Veterans, coordinated with the VA to support daily living at home.',
                'heroImage', '/images/services/veteran-care.jpg',
                'bodyHtml', '<p>A Home Health Aide is a trained person who can come to a Veteran''s home and help the Veteran take care of themselves and their daily activities. Home Health Aides are not nurses, but they are supervised by a registered nurse who will help assess the Veteran''s daily living needs.</p>',
                'highlights', JSON_ARRAY('VA-contracted Home Health Aide services', 'Support for isolated Veterans or overburdened caregivers', 'Respite care for family caregivers', 'Available regardless of Veteran''s age'),
                'isFeaturedOnHome', true),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_care_services AND slug = 'veteran-care');

-- ── 6. Entries: blog-posts (from blog.json) ─────────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, '10-signs-your-loved-one-needs-home-health-care-chester-sc', '10 Signs Your Loved One Needs Home Health Care in Chester, SC', 'published', 1, '2026-06-01',
    JSON_OBJECT('excerpt', 'Caring for an aging parent or loved one is one of life''s most meaningful responsibilities. Here are 10 signs it might be time for professional support.',
                'coverImage', '/images/blog/10-signs.jpg',
                'bodyHtml', '<p>Caring for an aging parent or loved one is one of life''s most meaningful responsibilities — but it can also be overwhelming. Here are ten important signs that it might be time to consider professional home health care assistance.</p><h3>1. Difficulty with Daily Activities</h3><p>If your loved one is struggling with daily routines like dressing, bathing, grooming, or cooking, it''s often one of the earliest signs that extra help is needed.</p><h3>2. Social Isolation or Depression</h3><p>Loneliness is one of the most common challenges older adults face. Caregivers offer companionship, conversation, and social engagement.</p><h3>3. Frequent Falls or Safety Concerns</h3><p>Falls are among the most common causes of injury for seniors. Our caregivers provide mobility support, home safety checks, and fall-prevention strategies.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-06-01',
                'tags', JSON_ARRAY('home care', 'caregiving', 'chester-sc')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = '10-signs-your-loved-one-needs-home-health-care-chester-sc');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'complete-guide-home-care-services-chester-county', 'The Complete Guide to Home Care Services in Chester County', 'published', 2, '2026-05-15',
    JSON_OBJECT('excerpt', 'Everything you need to know about home care services in Chester County — what they are, how they work, and how to choose the right plan.',
                'coverImage', '/images/blog/complete-guide.jpg',
                'bodyHtml', '<p>Caring for a loved one is one of life''s greatest responsibilities. At LarCare Services in Chester, SC, we understand that every family''s situation is unique. This complete guide walks you through everything you need to know about home care services in Chester County.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-05-15',
                'tags', JSON_ARRAY('home care', 'guide', 'chester-county')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'complete-guide-home-care-services-chester-county');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'dementia-care-compassionate-guide-families-caregivers', 'Dementia Care: A Compassionate Guide for Families & Caregivers', 'published', 3, '2026-04-22',
    JSON_OBJECT('excerpt', 'Understand the stages of dementia and how compassionate, structured in-home care supports both patients and families.',
                'coverImage', '/images/blog/dementia-guide.jpg',
                'bodyHtml', '<p>Dementia care requires patience, structure, and specialized training. This guide walks families through what to expect at each stage.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-04-22',
                'tags', JSON_ARRAY('dementia', 'alzheimers')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'dementia-care-compassionate-guide-families-caregivers');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'aging-in-place-comfort-independence-dignity', 'Aging in Place: Embracing Comfort, Independence, and Dignity', 'published', 4, '2026-03-10',
    JSON_OBJECT('excerpt', 'Why more seniors are choosing to age in place, and how the right support system makes it possible.',
                'coverImage', '/images/blog/aging-in-place.jpg',
                'bodyHtml', '<p>Aging in place lets seniors stay in the home they love while receiving the professional support they need.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-03-10',
                'tags', JSON_ARRAY('aging in place')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'aging-in-place-comfort-independence-dignity');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'elder-care-affordability-guide-families', 'Elder Care Affordability: A Compassionate Guide for Families', 'published', 5, '2026-02-18',
    JSON_OBJECT('excerpt', 'Understanding the true cost of elder care and the financial options available to families in South Carolina.',
                'coverImage', '/images/blog/affordability.jpg',
                'bodyHtml', '<p>The cost of care depends on the level of assistance required, frequency of visits, and type of care needed.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-02-18',
                'tags', JSON_ARRAY('affordability', 'cost')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'elder-care-affordability-guide-families');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'medicare-home-care-compassionate-guide-families', 'Medicare Home Care: A Compassionate Guide for Families', 'published', 6, '2026-01-29',
    JSON_OBJECT('excerpt', 'A breakdown of what Medicare does and doesn''t cover for in-home care, and how to plan around it.',
                'coverImage', '/images/blog/medicare-guide.jpg',
                'bodyHtml', '<p>Understanding Medicare''s coverage of home care services can help families plan and budget with confidence.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-01-29',
                'tags', JSON_ARRAY('medicare')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'medicare-home-care-compassionate-guide-families');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_blog_posts, 'understanding-medicaid-support-services-caring-guide', 'Understanding Medicaid Support Services: A Caring Guide for Families', 'published', 7, '2026-01-05',
    JSON_OBJECT('excerpt', 'How Medicaid waivers and support programs can help offset the cost of home care.',
                'coverImage', '/images/blog/medicaid-guide.jpg',
                'bodyHtml', '<p>Medicaid support services vary by state — here''s what South Carolina families should know.</p>',
                'author', 'LarCare Services',
                'publishedAt', '2026-01-05',
                'tags', JSON_ARRAY('medicaid')),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blog_posts AND slug = 'understanding-medicaid-support-services-caring-guide');

-- ── 7. Entries: products (from products.json) ───────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'standard-wheelchair', 'Standard Folding Wheelchair', 'published', 1,
    JSON_OBJECT('description', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent at metus ac risus vehicula consectetur.',
                'image', '/images/products/wheelchair.jpg', 'price', 99.99, 'externalUrl', 'https://example-partner-store.com/products/standard-wheelchair'),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_products AND slug = 'standard-wheelchair');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'infrared-thermometer', 'Touchless Infrared Thermometer', 'published', 2,
    JSON_OBJECT('description', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent at metus ac risus vehicula consectetur.',
                'image', '/images/products/thermometer.jpg', 'price', 99.99, 'externalUrl', 'https://example-partner-store.com/products/infrared-thermometer'),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_products AND slug = 'infrared-thermometer');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_products, 'daily-pill-organizer', '7-Day Pill Organizer', 'published', 3,
    JSON_OBJECT('description', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Praesent at metus ac risus vehicula consectetur.',
                'image', '/images/products/pill-organizer.jpg', 'price', 99.99, 'externalUrl', 'https://example-partner-store.com/products/daily-pill-organizer'),
    NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_products AND slug = 'daily-pill-organizer');

-- ── 8. Entries: testimonials (from misc.json) ───────────────────────────────
-- No natural slug in source data; synthesize stable slugs testimonial-1..4.
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'testimonial-1', 'Ayesha Malik', 'published', 1,
    JSON_OBJECT('authorName', 'Ayesha Malik', 'rating', 5, 'quote', 'Customer support was extremely helpful and responsive.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_testimonials AND slug = 'testimonial-1');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'testimonial-2', 'Ayesha Malik', 'published', 2,
    JSON_OBJECT('authorName', 'Ayesha Malik', 'rating', 5, 'quote', 'Customer support was extremely helpful and responsive.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_testimonials AND slug = 'testimonial-2');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'testimonial-3', 'Ayesha Malik', 'published', 3,
    JSON_OBJECT('authorName', 'Ayesha Malik', 'rating', 5, 'quote', 'Customer support was extremely helpful and responsive.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_testimonials AND slug = 'testimonial-3');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_testimonials, 'testimonial-4', 'Ayesha Malik', 'published', 4,
    JSON_OBJECT('authorName', 'Ayesha Malik', 'rating', 2, 'quote', 'Customer support was extremely helpful and responsive.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_testimonials AND slug = 'testimonial-4');

-- ── 9. Entries: core-values (from misc.json) ────────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'affordable-prices', 'Affordable Prices', 'published', 1,
    JSON_OBJECT('icon', 'dollar', 'description', 'Our prices are competitive and affordable. We aim to work with all budgets and promise to not overextend families financially.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_core_values AND slug = 'affordable-prices');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'experienced-care-experts', 'Experienced Care Experts', 'published', 2,
    JSON_OBJECT('icon', 'sparkle', 'description', 'We have seasoned, licensed, and experienced care agents with over 100 years of combined service caring for families.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_core_values AND slug = 'experienced-care-experts');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'privacy-is-important', 'Privacy is Important', 'published', 3,
    JSON_OBJECT('icon', 'lock', 'description', 'We ensure secure platforms to protect identity and secure processes that protect our families from harmful acts.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_core_values AND slug = 'privacy-is-important');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_core_values, 'dedication-and-loyalty', 'Dedication and Loyalty', 'published', 4,
    JSON_OBJECT('icon', 'heart', 'description', 'Your family becomes our family. Our management team and care experts will care for your family like our own.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_core_values AND slug = 'dedication-and-loyalty');

-- ── 10. Entries: about-stats (from misc.json) ───────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_about_stats, 'over-100-years-combined-experience', 'Over 100 years of combined licensed experience', 'published', 1,
    JSON_OBJECT('icon', 'sparkle'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_about_stats AND slug = 'over-100-years-combined-experience');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_about_stats, 'certified-care-agents', 'Certified Care Agents', 'published', 2,
    JSON_OBJECT('icon', 'shield'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_about_stats AND slug = 'certified-care-agents');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_about_stats, 'custom-care-plan', 'Custom Care Plan', 'published', 3,
    JSON_OBJECT('icon', 'sliders'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_about_stats AND slug = 'custom-care-plan');

-- ── 11. Entries: job-openings (from misc.json) ──────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'home-care-services', 'Home Care Services', 'published', 1,
    JSON_OBJECT('type', 'Full-time / Part-time', 'location', 'Chester, SC', 'summary', 'Support clients with non-medical daily living assistance.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_job_openings AND slug = 'home-care-services');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'personal-care-aide', 'Personal Care Aide', 'published', 2,
    JSON_OBJECT('type', 'Full-time / Part-time', 'location', 'Chester, SC', 'summary', 'Assist with bathing, dressing, grooming, and mobility.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_job_openings AND slug = 'personal-care-aide');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'companion-care', 'Companion Care', 'published', 3,
    JSON_OBJECT('type', 'Part-time', 'location', 'Chester, SC', 'summary', 'Provide companionship, conversation, and social engagement.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_job_openings AND slug = 'companion-care');

INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @coll_job_openings, 'certified-nurse-assistant', 'Certified Nurse Assistant (CNA)', 'published', 4,
    JSON_OBJECT('type', 'Full-time', 'location', 'Chester, SC', 'summary', 'Deliver hands-on personal care under RN supervision.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_job_openings AND slug = 'certified-nurse-assistant');

-- ── 12. Navigation: header (primaryNav) ──────────────────────────────────────
INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Home', '/', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Home');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Healthcare', '/healthcare', 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Healthcare');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'About Us', '/about-us', 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'About Us');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Blog', '/blog', 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Blog');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Products', '/products', 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Products');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Forms', '/forms', 6, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Forms');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'Portal', '/portal', 7, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Portal');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, open_in_new_tab, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'header', 'SamariCare', 'https://samaricare.com', 1, 8, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'SamariCare');

-- Healthcare children (parent resolved by label lookup — insert parents first, above)
SET @nav_healthcare := (SELECT id FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Healthcare' LIMIT 1);

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_healthcare, 'header', 'Alzheimer''s Disease and Dementia Care', '/healthcare/alzheimers-dementia-care', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_healthcare AND label = 'Alzheimer''s Disease and Dementia Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_healthcare, 'header', 'End-of-Life Care', '/healthcare/end-of-life-care', 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_healthcare AND label = 'End-of-Life Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_healthcare, 'header', 'Home and Personal Care Services', '/healthcare/home-and-personal-care-services', 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_healthcare AND label = 'Home and Personal Care Services');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_healthcare, 'header', 'Veteran Care', '/healthcare/veteran-care', 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_healthcare AND label = 'Veteran Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_healthcare, 'header', 'Transitional Care', '/healthcare/transitional-care', 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_healthcare AND label = 'Transitional Care');

-- About Us children
SET @nav_about_us := (SELECT id FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'About Us' LIMIT 1);

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_about_us, 'header', 'About LarCare', '/about-us', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_about_us AND label = 'About LarCare');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_about_us, 'header', 'Careers', '/about-us/careers', 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_about_us AND label = 'Careers');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, @nav_about_us, 'header', 'Contact Us', '/about-us/contact-us', 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND parent_id = @nav_about_us AND label = 'Contact Us');

-- ── 13. Navigation: footer (footerColumns) ──────────────────────────────────
INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'Home', '/', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'Home');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'About LarCare', '/about-us', 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'About LarCare');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'Careers', '/about-us/careers', 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'Careers');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'Contact Us', '/about-us/contact-us', 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'Contact Us');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'Privacy Policy & Terms', '/privacy-policy', 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'Privacy Policy & Terms');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Our company', 'Products', '/products', 6, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Our company' AND label = 'Products');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Care Services', 'Home and Personal Care Services', '/healthcare/home-and-personal-care-services', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Care Services' AND label = 'Home and Personal Care Services');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Care Services', 'Veteran Care', '/healthcare/veteran-care', 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Care Services' AND label = 'Veteran Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Care Services', 'End-of-Life Care', '/healthcare/end-of-life-care', 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Care Services' AND label = 'End-of-Life Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Care Services', 'Transitional Care', '/healthcare/transitional-care', 4, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Care Services' AND label = 'Transitional Care');

INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, parent_id, location, column_title, label, href, sort_order, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, NULL, 'footer', 'Care Services', 'Alzheimer''s Disease and Dementia Care', '/healthcare/alzheimers-dementia-care', 5, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Care Services' AND label = 'Alzheimer''s Disease and Dementia Care');

-- ── 14. Forms: contact + sms-consent ─────────────────────────────────────────
-- Table definitions live in web_forms_module.sql (must run before this file).
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, notify_emails, is_active, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'contact', 'Contact Form', 'Thanks for reaching out — a member of our team will get back to you shortly.', 'info@larcareservices.com', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'contact');

INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, notify_emails, is_active, created_by, updated_by, is_deleted)
SELECT @tenant_id, @business_id, @site_id, 'sms-consent', 'SMS Consent', 'Thank you — your SMS consent preferences have been saved.', 'info@larcareservices.com', 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'sms-consent');

SET @form_contact := (SELECT id FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'contact' LIMIT 1);
SET @form_sms_consent := (SELECT id FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'sms-consent' LIMIT 1);

-- contact fields: firstName, lastName, email, phone, message (required)
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_contact, 'firstName', 'First Name', 'text', 0, 1
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact AND field_key = 'firstName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_contact, 'lastName', 'Last Name', 'text', 0, 2
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact AND field_key = 'lastName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_contact, 'email', 'Email', 'email', 0, 3
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact AND field_key = 'email');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_contact, 'phone', 'Phone', 'tel', 0, 4
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact AND field_key = 'phone');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_contact, 'message', 'Message', 'textarea', 1, 5
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact AND field_key = 'message');

-- sms-consent fields: firstName, lastName, email, phone, consent (all required)
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_sms_consent, 'firstName', 'First Name', 'text', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_sms_consent AND field_key = 'firstName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_sms_consent, 'lastName', 'Last Name', 'text', 1, 2
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_sms_consent AND field_key = 'lastName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_sms_consent, 'email', 'Email', 'email', 1, 3
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_sms_consent AND field_key = 'email');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_sms_consent, 'phone', 'Phone', 'tel', 1, 4
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_sms_consent AND field_key = 'phone');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT @form_sms_consent, 'consent', 'SMS Consent', 'checkbox', 1, 5
WHERE NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_sms_consent AND field_key = 'consent');