-- =============================================================================
-- seed_nsp_foundation_business_and_site.sql — Onboard NSP Foundation as a
-- public website inside Aggie's tenant/business-aware Web CMS module.
-- Follows the same pattern as seed_techmac_business_and_site.sql, adapted:
-- this file runs via apply-migrations.sh (which selects the target database
-- on the mysql command line, not via a `USE` statement in this file) against
-- the real Aggie database (stacie_Aggie_v1.0 on Server 1 — the default
-- DB_NAME), NOT the Windows Server's separate db_abos_v0.1 dev database.
--
-- NSP Foundation is a 501(c)(3) nonprofit venture studio & think tank. The
-- business record is expected to ALREADY EXIST — confirmed present as
-- businesses.id=1 / tenant_id=3 in the shared dev database, but this file
-- deliberately does NOT hardcode those IDs: a separate database (this is
-- one) has its own auto-increment history, so the same logical business can
-- have different numeric IDs there. Resolved by NAME instead (see §0). This
-- file does NOT insert into `businesses` in any case — it only reuses the
-- row it finds. If no business named 'NSP Foundation' exists in the target
-- database, every guarded INSERT below silently no-ops rather than writing
-- against a wrong/nonexistent business — verify after running that the site
-- row actually got created (§ Verify at the bottom).
--
-- Source of truth for the seeded content: the NSP Foundation onboarding
-- brief (a single-page marketing site — Why NSP / Where We Focus / Fiscal
-- Sponsorship / In Action / Get Involved are anchors on one page, not
-- separate routes).
--
-- IMPORTANT: public_site_key below is the fixed X-Site-Key
-- NSPFoundation/frontend (ABOS_SITE_KEY env var) must send to Aggie's public
-- Web API. Value: PTfrDRwhx2h3GYBlfJrrjMxgJPMpI2LrNsObjNvA5AZufmJS
--
-- Idempotent: every INSERT is guarded with `WHERE NOT EXISTS (...)` against
-- the table's natural/unique key, matching the TechMAC seed file. Safe to
-- re-run on every deploy (see apply-migrations.sh's convention).
--
-- Deploy: via abos-api/db/apply-migrations.sh (default DB_NAME=stacie_Aggie_v1.0)
-- — this file must be listed in that script's FILES array to actually run.
-- =============================================================================

-- ── 0. Resolve the existing tenant/business by NAME (do NOT insert into
-- `businesses`, and do NOT hardcode IDs — see header). If this returns NULL,
-- every subsequent guarded INSERT below is a no-op.
SET @business_id := (SELECT id FROM businesses WHERE name = 'NSP Foundation' AND is_deleted = 0 ORDER BY id LIMIT 1);
SET @tenant_id := (SELECT tenant_id FROM businesses WHERE id = @business_id LIMIT 1);

-- ── 1. Site ──────────────────────────────────────────────────────────────────
-- primary_domain: confirmed by the user as 'wensp.org' (nspfoundation.org was
-- an initial guess inferred from the contact-email domain — the org's
-- contact/partnership emails still use @nspfoundation.org, which is
-- unrelated to and does not need to match its website domain).
INSERT INTO dim_web_sites (
    tenant_id, business_id, name, slug, primary_domain, additional_domains_json, 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, 'NSP Foundation', 'nsp-foundation', 'wensp.org',
    JSON_ARRAY('www.wensp.org'),
    'PTfrDRwhx2h3GYBlfJrrjMxgJPMpI2LrNsObjNvA5AZufmJS',
    'en', 1,
    'NSP Foundation', 'Never Stop Pushing',
    '(804) 479-4784', 'contact@nspfoundation.org', 'Washington, D.C.',
    JSON_ARRAY('United States'),
    NULL,
    NULL, NULL, 0
WHERE @business_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'nsp-foundation')
       OR public_site_key = 'PTfrDRwhx2h3GYBlfJrrjMxgJPMpI2LrNsObjNvA5AZufmJS'
       OR primary_domain = 'wensp.org'
);

-- Force the correct domain on every re-run for a site row created by an
-- earlier version of this script (before 'wensp.org' was confirmed) — same
-- "safe to re-run, this row is owned by this seed file" convention as
-- seed_techmac_business_and_site.sql's forced nav sort_order UPDATEs.
UPDATE dim_web_sites
   SET primary_domain = 'wensp.org', additional_domains_json = JSON_ARRAY('www.wensp.org')
 WHERE tenant_id = @tenant_id AND slug = 'nsp-foundation' AND primary_domain <> 'wensp.org';

SET @site_id := (
    SELECT id FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'nsp-foundation')
       OR public_site_key = 'PTfrDRwhx2h3GYBlfJrrjMxgJPMpI2LrNsObjNvA5AZufmJS'
       OR primary_domain = 'wensp.org'
    LIMIT 1
);

-- ── 2. 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, 'focus-areas', 'Focus Areas', 'Focus Area', 'collection', NULL, 0, 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'focus-areas');

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, 'sponsorship-benefits', 'Sponsorship Benefits', 'Benefit', 'collection', NULL, 0, 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'sponsorship-benefits');

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, 'onboarding-steps', 'Onboarding Steps', 'Step', 'collection', NULL, 0, 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'onboarding-steps');

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, 'portfolio-areas', 'Portfolio Areas', 'Portfolio Area', '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 = 'portfolio-areas');

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, 'get-involved-audiences', 'Get Involved Audiences', 'Audience', '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 = 'get-involved-audiences');

SET @coll_focus       := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'focus-areas' LIMIT 1);
SET @coll_benefits     := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'sponsorship-benefits' LIMIT 1);
SET @coll_onboarding   := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'onboarding-steps' LIMIT 1);
SET @coll_portfolio    := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'portfolio-areas' LIMIT 1);
SET @coll_audiences    := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'get-involved-audiences' LIMIT 1);

-- ── 3. Collection fields ─────────────────────────────────────────────────────
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_focus, 'number', 'Number', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_focus AND field_key = 'number');
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_focus, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_focus 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_benefits, 'description', 'Description', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_benefits 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_onboarding, 'number', 'Number', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_onboarding AND field_key = 'number');
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_onboarding, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_onboarding 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_portfolio, 'description', 'Description', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_portfolio 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_audiences, 'description', 'Description', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_audiences AND field_key = 'description');

-- ── 4. Entries: focus-areas ──────────────────────────────────────────────────
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_focus, 'small-business-entrepreneurship', 'Small Business & Entrepreneurship', 'published', 1,
    JSON_OBJECT('number', '01', 'description', 'Equipping underrepresented founders with incubation resources, back-office support, compliance management, and pathways to non-dilutive grant funding.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'small-business-entrepreneurship');

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_focus, 'student-financing-educational-access', 'Student Financing & Educational Access', 'published', 2,
    JSON_OBJECT('number', '02', 'description', 'Alternative, non-predatory funding models that help first-generation and low-to-moderate-income students close last-mile funding gaps and complete their degrees.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'student-financing-educational-access');

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_focus, 'housing-homeownership-equity', 'Housing & Homeownership Equity', 'published', 3,
    JSON_OBJECT('number', '03', 'description', 'Partnering with HUD-approved counseling agencies and local advocates to expand homeownership readiness, down-payment access, and foreclosure prevention.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'housing-homeownership-equity');

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_focus, 'faith-based-community-programming', 'Faith-Based Community Programming', 'published', 4,
    JSON_OBJECT('number', '04', 'description', 'Working through trusted faith institutions to deliver financial literacy, entrepreneurship workshops, and resource fairs where communities already gather.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'faith-based-community-programming');

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_focus, 'workforce-development', 'Workforce Development', 'published', 5,
    JSON_OBJECT('number', '05', 'description', 'Aligning digital literacy, re-skilling, and financial-health education with placement pipelines into living-wage industries.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'workforce-development');

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_focus, 'access-to-capital', 'Access to Capital', 'published', 6,
    JSON_OBJECT('number', '06', 'description', 'Acting as a capital aggregator — structuring public-private partnerships and philanthropic funding channels that direct capital where it is needed most.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_focus AND slug = 'access-to-capital');

-- ── 5. Entries: sponsorship-benefits ─────────────────────────────────────────
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_benefits, 'tax-deductible-fundraising', 'Tax-deductible fundraising', 'published', 1,
    JSON_OBJECT('description', 'Raise funds under NSP Foundation''s 501(c)(3) status, so your donors and grantors can give tax-deductibly from day one.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_benefits AND slug = 'tax-deductible-fundraising');

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_benefits, 'financial-compliance-oversight', 'Financial & compliance oversight', 'published', 2,
    JSON_OBJECT('description', 'Bookkeeping, reporting, and regulatory compliance handled to public-charity standards, so your team can focus on the mission, not the paperwork.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_benefits AND slug = 'financial-compliance-oversight');

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_benefits, 'operational-infrastructure', 'Operational infrastructure', 'published', 3,
    JSON_OBJECT('description', 'Shared back-office services — insurance, HR, contracts, and administrative systems — so your project launches without building an org from scratch.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_benefits AND slug = 'operational-infrastructure');

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_benefits, 'strategic-mentorship', 'Strategic mentorship', 'published', 4,
    JSON_OBJECT('description', 'Direct access to NSP''s venture-building team and board for guidance on strategy, partnerships, and scaling your program.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_benefits AND slug = 'strategic-mentorship');

-- ── 6. Entries: onboarding-steps ─────────────────────────────────────────────
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_onboarding, 'application-submission', 'Application submission', 'published', 1,
    JSON_OBJECT('number', '01', 'description', 'Tell us about your project, its mission, and how it advances economic mobility — a short application, not a grant essay.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_onboarding AND slug = 'application-submission');

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_onboarding, 'review-vetting', 'Review & vetting', 'published', 2,
    JSON_OBJECT('number', '02', 'description', 'Our team reviews fit, feasibility, and compliance requirements, and follows up with any clarifying questions.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_onboarding AND slug = 'review-vetting');

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_onboarding, 'agreement-onboarding', 'Agreement & onboarding', 'published', 3,
    JSON_OBJECT('number', '03', 'description', 'A fiscal sponsorship agreement is signed, and your project is onboarded into NSP''s financial and operational systems.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_onboarding AND slug = 'agreement-onboarding');

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_onboarding, 'launch-acceleration', 'Launch & acceleration', 'published', 4,
    JSON_OBJECT('number', '04', 'description', 'Your project goes live under NSP''s umbrella, with ongoing support to fundraise, deliver programming, and scale.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_onboarding AND slug = 'launch-acceleration');

-- ── 7. Entries: portfolio-areas ──────────────────────────────────────────────
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_portfolio, 'financial-services-financial-literacy', 'Financial Services & Financial Literacy', 'published', 1,
    JSON_OBJECT('description', 'Programs building practical money-management skills and connecting communities to fair, non-predatory financial products.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_portfolio AND slug = 'financial-services-financial-literacy');

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_portfolio, 'higher-education-access', 'Higher Education Access', 'published', 2,
    JSON_OBJECT('description', 'Initiatives closing last-mile funding and information gaps that keep first-generation students from enrolling or finishing.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_portfolio AND slug = 'higher-education-access');

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_portfolio, 'affordable-housing-economic-mobility', 'Affordable Housing & Economic Mobility', 'published', 3,
    JSON_OBJECT('description', 'Projects expanding homeownership readiness and stable housing as a foundation for long-term economic mobility.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_portfolio AND slug = 'affordable-housing-economic-mobility');

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_portfolio, 'events-community-economic-development', 'Events, Community & Economic Development', 'published', 4,
    JSON_OBJECT('description', 'Convenings and community events that bring residents, funders, and civic leaders together around shared economic-development priorities.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_portfolio AND slug = 'events-community-economic-development');

-- ── 8. Entries: get-involved-audiences ───────────────────────────────────────
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_audiences, 'founders-social-entrepreneurs', 'Founders & social entrepreneurs', 'published', 1,
    JSON_OBJECT('description', 'Apply for fiscal sponsorship and gain the infrastructure, mentorship, and compliance support to scale your idea.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_audiences AND slug = 'founders-social-entrepreneurs');

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_audiences, 'corporate-foundation-partners', 'Corporate & foundation partners', 'published', 2,
    JSON_OBJECT('description', 'Partner with NSP to direct philanthropic and CSR capital toward vetted, high-potential community programs.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_audiences AND slug = 'corporate-foundation-partners');

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_audiences, 'donors-advocates', 'Donors & advocates', 'published', 3,
    JSON_OBJECT('description', 'Support the mission directly with a tax-deductible gift, or help us reach the communities and partners we serve.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_audiences AND slug = 'donors-advocates');

-- ── 9. Navigation: header ────────────────────────────────────────────────────
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', 'Why NSP', '/#why-nsp', 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 = 'Why NSP');

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', 'Where We Focus', '/#focus', 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 = 'Where We Focus');

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', 'Fiscal Sponsorship', '/#sponsorship', 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 = 'Fiscal Sponsorship');

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', 'In Action', '/#in-action', 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 = 'In Action');

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', 'Get Involved', '/#get-involved', 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 = 'Get Involved');

-- Footer — flat rows grouped by column_title (PublicWebService.footerColumns()
-- skips any item with a parent and groups by column_title — see the note in
-- seed_techmac_business_and_site.sql section 13 for why this must be flat).
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', 'Company', 'Why NSP', '/#why-nsp', 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 = 'Company' AND label = 'Why NSP');
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', 'Company', 'Where We Focus', '/#focus', 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 = 'Company' AND label = 'Where We Focus');
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', 'Company', 'Contact', '/#get-involved', 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 = 'Company' AND label = 'Contact');

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', 'Get Involved', 'Fiscal Sponsorship', '/#sponsorship', 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 = 'Get Involved' AND label = 'Fiscal Sponsorship');
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', 'Get Involved', 'In Action', '/#in-action', 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 = 'Get Involved' AND label = 'In Action');
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', 'Get Involved', 'Start the Conversation', '/#get-involved', 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 = 'Get Involved' AND label = 'Start the Conversation');

-- ── 10. Form: connect ────────────────────────────────────────────────────────
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @tenant_id, @business_id, @site_id, 'connect', 'Start the Conversation',
       'Thank you for reaching out — a member of the NSP Foundation team will be in touch soon.', 1
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'connect');

SET @form_connect_id := (SELECT id FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'connect');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_connect_id AS a, 'name' AS b, 'Full Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @form_connect_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_connect_id AND field_key = 'name');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_connect_id AS a, 'email' AS b, 'Email Address' AS c, 'email' AS d, 1 AS e, 2 AS f) AS t
WHERE @form_connect_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_connect_id AND field_key = 'email');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_connect_id AS a, 'organization' AS b, 'Organization or Project' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @form_connect_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_connect_id AND field_key = 'organization');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @form_connect_id AS a, 'connectAs' AS b, 'How would you like to connect?' AS c, 'select' AS d, 1 AS e, 4 AS f,
    '{"options": ["Fiscal sponsorship application", "Corporate or foundation partnership", "Support the mission", "Media or speaking", "General inquiry"]}' AS g) AS t
WHERE @form_connect_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_connect_id AND field_key = 'connectAs');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_connect_id AS a, 'message' AS b, 'Tell us a little more' AS c, 'textarea' AS d, 0 AS e, 5 AS f) AS t
WHERE @form_connect_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_connect_id AND field_key = 'message');

-- ── 11. Deal template — tenant 3 currently has NONE (confirmed via
-- `SELECT * FROM dim_deal_templates WHERE tenant_id = 3` returning 0 rows).
-- fulfillment_type is 'NONE', NOT 'PROJECT': NSP's contact form is a general
-- inquiry/donor/partner form, not a service-delivery request, so it must NOT
-- trigger CrmDealFromWebService.createProjectForServiceDeal's auto-created
-- fulfillment project (that only fires when fulfillment_type='PROJECT' — see
-- that method, ~line 316: `if (FULFILLMENT_PROJECT.equals(template.getFulfillmentType()))`).
-- Note: dim_deal_templates.fulfillment_type is a DB enum('SHIPMENT','PROJECT','NONE')
-- — there is no 'INQUIRY' value, so 'NONE' is the correct (and only valid) choice
-- for "don't auto-create fulfillment work". default_stage/default_status reuse
-- 'Discovery'/'New', the same values tenant 1's web_service_request template uses,
-- both confirmed valid against OpportunityService's stage/status whitelists. ──
INSERT INTO dim_deal_templates (
    tenant_id, business_id, template_key, name, default_stage, default_status,
    title_pattern, line_item_type, fulfillment_type, created_by, updated_by, is_deleted
)
SELECT @tenant_id, NULL, 'web_service_request', 'NSP Foundation Inquiry', 'Discovery', 'New',
       'NSP Foundation Inquiry from {name}', 'Service', 'NONE', NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM dim_deal_templates WHERE tenant_id = @tenant_id AND template_key = 'web_service_request'
);

-- ── 12. CMS page: home (single-page site — anchors, not routes) ─────────────
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()), 'NSP Foundation | Never Stop Pushing'
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'home');

-- Always refresh layout_json to the current design (safe: this page is
-- entirely owned by this seed file, not hand-edited in the CMS). Every block
-- type used below (hero, ticker, richText, numberedGrid, pillarGrid,
-- ctaBanner, formEmbed) is one BlockRenderer.tsx actually implements.
UPDATE dim_web_pages SET layout_json = JSON_OBJECT(
  'version', 1,
  'blocks', JSON_ARRAY(
    JSON_OBJECT('id', 'home-hero', 'type', 'hero', 'props', JSON_OBJECT(
      'eyebrow', '501(c)(3) Nonprofit Venture Studio & Think Tank',
      'heading', 'Catalyzing equity, innovation, and economic mobility for underserved communities.',
      'subheading', 'NSP Foundation is a 501(c)(3) nonprofit venture studio and think tank dedicated to dismantling systemic economic barriers through fiscal sponsorship, entrepreneurial incubation, and community-centered programs.',
      'ctaLabel', 'Explore Our Work',
      'ctaHref', '/#focus',
      'secondaryLabel', 'Partner With Us',
      'secondaryHref', '/#get-involved',
      'statusItems', JSON_ARRAY('EST. 2023', 'WASHINGTON, D.C.')
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-vision', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<p style="font-family:var(--font-mono);font-size:12px;letter-spacing:.14em;text-transform:uppercase;color:var(--tw-prose-headings,inherit);">Our Vision</p><blockquote style="margin:0.75rem 0 0;font-size:1.5rem;font-weight:600;line-height:1.3;">A catalyst for economic empowerment and equity — so every community can overcome systemic barriers and thrive.</blockquote>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-ticker', 'type', 'ticker', 'props', JSON_OBJECT(
      'lines', JSON_ARRAY('FISCAL SPONSORSHIP', 'VENTURE INCUBATION', 'APPLIED RESEARCH', 'COMMUNITY PARTNERSHIP')
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'why-nsp', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>We don''t just fund good ideas — we help build them.</h2><p>Traditional social-impact models often move slower than the communities they''re meant to serve. NSP Foundation — Never Stop Pushing — was built to close that gap. As a 501(c)(3) public charity operating as a nonprofit venture studio and think tank, we pair venture-building discipline with the public trust of tax-exempt status — identifying high-potential social-impact ideas and equipping them with the infrastructure needed to scale, from applied research to direct-to-community programs.</p><p>NSP Foundation operates under federal and state governance standards that support transparency, fiscal responsibility, and donor accountability across every sponsored project. The Foundation is led by Founder &amp; President Stacie Whisonant and a board of directors.</p><div style="display:grid;grid-template-columns:1fr 1fr;gap:24px;margin-top:24px;"><div style="border:1px solid var(--color-neutral-400,#ddd);border-radius:14px;padding:24px;"><p style="font-family:var(--font-mono);font-size:11px;text-transform:uppercase;letter-spacing:.12em;">The Venture Studio Engine</p><p style="font-weight:600;margin-top:6px;">Hands-on program support</p><p style="margin-top:8px;">Incubation, fiscal sponsorship, shared services, and mentorship for social-impact ventures.</p></div><div style="border:1px solid var(--color-neutral-400,#ddd);border-radius:14px;padding:24px;"><p style="font-family:var(--font-mono);font-size:11px;text-transform:uppercase;letter-spacing:.12em;">The Think Tank Engine</p><p style="font-weight:600;margin-top:6px;">Applied research &amp; convening</p><p style="margin-top:8px;">Applied research and convening leaders on student finance, capital access, and housing.</p></div></div>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'focus', 'type', 'numberedGrid', 'props', JSON_OBJECT(
      'eyebrow', 'Where We Focus',
      'heading', 'Economic mobility is a connected system.',
      'collectionKey', 'focus-areas'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'sponsorship-intro', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>Fiscal Sponsorship &amp; Venture Studio</h2><p>Turn your social-impact vision into action.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'sponsorship', 'type', 'pillarGrid', 'props', JSON_OBJECT(
      'heading', 'What sponsored projects get',
      'description', 'Every sponsored project has access to the same core benefits.',
      'collectionKey', 'sponsorship-benefits'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'onboarding', 'type', 'numberedGrid', 'props', JSON_OBJECT(
      'eyebrow', 'Application & Onboarding',
      'heading', 'Four steps to launch',
      'collectionKey', 'onboarding-steps'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'sponsorship-cta', 'type', 'ctaBanner', 'props', JSON_OBJECT(
      'heading', 'Apply for fiscal sponsorship',
      'description', 'Tell us about your project and we will follow up on next steps.',
      'ctaLabel', 'Apply for fiscal sponsorship',
      'ctaHref', '/#get-involved',
      'secondaryLabel', 'See how it works',
      'secondaryHref', '/#sponsorship'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'in-action', 'type', 'pillarGrid', 'props', JSON_OBJECT(
      'heading', 'Current projects in flight.',
      'description', 'A look at where NSP Foundation''s sponsored work is active today.',
      'collectionKey', 'portfolio-areas'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'get-involved', 'type', 'pillarGrid', 'props', JSON_OBJECT(
      'heading', 'Join us in building an equitable future.',
      'description', 'Whichever way you want to get involved, there is a place for you at NSP Foundation.',
      'collectionKey', 'get-involved-audiences'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'contact-details', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<p><strong>General inquiries:</strong> contact@nspfoundation.org</p><p><strong>Partnerships:</strong> partnerships@nspfoundation.org</p><p><strong>Phone:</strong> (804) 479-4784</p><p><strong>Location:</strong> Washington, D.C.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'connect-form', 'type', 'formEmbed', 'props', JSON_OBJECT('formKey', 'connect'), 'children', JSON_ARRAY())
  )
)
WHERE site_id = @site_id AND slug = 'home';