-- =============================================================================
-- seed_techmac_business_and_site.sql — Onboard TechMAC as a Business + public
-- website inside ABOS's tenant/business-aware Web CMS module, on the Windows
-- Server's db_abos_v0.1 (NOT stacie_Aggie_v1.0 — separate database).
--
-- Source of truth for the seeded content: "TechMAC Website.html" (the bundled
-- landing-page export this site was built from) — a single-page marketing
-- site (Solutions/Services/Products/Industries/Pricing/About are anchors on
-- one page, not separate routes). The exception is section 16's legal pages
-- (Terms/Privacy/Cookies/Accessibility), which are real routes served by
-- TechMAC/frontend's [slug] catch-all, seeded as drafts pending legal review.
--
-- hamza@techmac.co.uk already exists as SUPER_ADMIN under tenant_id=1 ("ABOS
-- Platform") — confirmed via read-only diagnostic before writing this file.
-- No existing TechMAC business/site/domain row was found, so this creates
-- both from scratch.
--
-- Idempotent: every INSERT is guarded with `WHERE NOT EXISTS (...)` against
-- the table's natural/unique key, matching seed_larcare_business_and_site.sql.
-- Safe to re-run.
--
-- IMPORTANT: public_site_key below is the fixed X-Site-Key TechMAC's frontend
-- (TechMAC/frontend, ABOS_SITE_KEY env var) must send to ABOS's public Web
-- API. Value: tmKq7RvXpL3sYhWzNc9BdEaJf2GmU5oQ
--
-- Deploy: mysql --default-character-set=utf8mb4 -u root -p db_abos_v0.1 < db/seed_techmac_business_and_site.sql
-- =============================================================================

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, 'TechMAC', 'TECHMAC', 'Technology & AI Consulting', 'United Kingdom',
       'hello@technologymac.co.uk', NULL, NULL,
       'Active', NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM businesses WHERE tenant_id = @tenant_id AND name = 'TechMAC'
);

SET @business_id := (SELECT id FROM businesses WHERE tenant_id = @tenant_id AND name = 'TechMAC' 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, 'TechMAC', 'techmac', 'technologymac.co.uk',
    'tmKq7RvXpL3sYhWzNc9BdEaJf2GmU5oQ',
    'en', 1,
    'TechMAC', 'Architecting your agentic future',
    NULL, 'hello@technologymac.co.uk', NULL,
    JSON_ARRAY('United Kingdom', 'Global'),
    NULL,
    NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'techmac')
       OR public_site_key = 'tmKq7RvXpL3sYhWzNc9BdEaJf2GmU5oQ'
       OR primary_domain = 'technologymac.co.uk'
);

SET @site_id := (
    SELECT id FROM dim_web_sites
    WHERE (tenant_id = @tenant_id AND slug = 'techmac')
       OR public_site_key = 'tmKq7RvXpL3sYhWzNc9BdEaJf2GmU5oQ'
       OR primary_domain = 'technologymac.co.uk'
    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, 'problem-points', 'Problem Points', 'Problem Point', '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 = 'problem-points');

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, 'solutions', 'Solutions', 'Solution', '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 = 'solutions');

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, 'blueprint-steps', 'Blueprint Steps', 'Blueprint 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 = 'blueprint-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, 'convergence-pillars', 'Convergence Pillars', 'Pillar', '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 = 'convergence-pillars');

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, 'amber-demo', 'Amber Demo', 'Amber Demo', '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 = 'amber-demo');

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, 'services', 'Services', 'Service', '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 = '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, 'industries', 'Industries', 'Industry', 'collection', NULL, 0, 1, 7, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'industries');

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, 'faq', 'FAQ', 'Question', 'collection', NULL, 0, 1, 8, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'faq');

SET @coll_problem_points := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'problem-points' LIMIT 1);
SET @coll_solutions       := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'solutions' LIMIT 1);
SET @coll_blueprint_steps := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'blueprint-steps' LIMIT 1);
SET @coll_convergence     := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'convergence-pillars' LIMIT 1);
SET @coll_amber_demo      := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'amber-demo' LIMIT 1);
SET @coll_services        := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'services' LIMIT 1);
SET @coll_industries      := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'industries' LIMIT 1);
SET @coll_faq             := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'faq' LIMIT 1);

-- ── 4. 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_problem_points, 'number', 'Number', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_problem_points 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_problem_points, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_problem_points 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_solutions, 'tagline', 'Tagline', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_solutions AND field_key = 'tagline');
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_solutions, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_solutions 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_solutions, 'priceFrom', 'Price From', 'text', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_solutions AND field_key = 'priceFrom');

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_blueprint_steps, 'number', 'Number', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blueprint_steps 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_blueprint_steps, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_blueprint_steps 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_convergence, 'description', 'Description', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_convergence 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_amber_demo, 'tagline', 'Tagline', 'text', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_amber_demo AND field_key = 'tagline');
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_amber_demo, 'description', 'Description', 'textarea', 1, 2, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_amber_demo 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_amber_demo, 'lines', 'Conversation Lines', 'json_array', 1, 3, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_amber_demo AND field_key = 'lines');

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_services, 'priceFrom', 'Price From', 'text', 0, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_services AND field_key = 'priceFrom');

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_faq, 'answer', 'Answer', 'textarea', 1, 1, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @coll_faq AND field_key = 'answer');

-- ── 5. Entries: problem-points ───────────────────────────────────────────────
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_problem_points, 'disconnected-systems', 'Disconnected systems', 'published', 1,
    JSON_OBJECT('number', '01', 'description', 'CRM, ERP and finance holding three versions of the same customer.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_problem_points AND slug = 'disconnected-systems');

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_problem_points, 'manual-reporting', 'Manual reporting', 'published', 2,
    JSON_OBJECT('number', '02', 'description', 'A week of senior time spent compiling what should already exist.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_problem_points AND slug = 'manual-reporting');

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_problem_points, 'inventory-drift', 'Inventory drift', 'published', 3,
    JSON_OBJECT('number', '03', 'description', 'Stock figures that stop matching reality between counts.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_problem_points AND slug = 'inventory-drift');

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_problem_points, 'human-bridging', 'Human bridging', 'published', 4,
    JSON_OBJECT('number', '04', 'description', 'People employed to move data between tools that cannot talk.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_problem_points AND slug = 'human-bridging');

-- ── 6. Entries: solutions ────────────────────────────────────────────────────
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_solutions, 'go-digital', 'GO DIGITAL', 'published', 1,
    JSON_OBJECT('tagline', 'Your digital presence, engineered as an asset',
                'description', 'Brand positioning, conversion architecture and search structure, built as one platform.',
                'priceFrom', 'from £799'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_solutions AND slug = 'go-digital');

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_solutions, 'rdabs', 'RDABS', 'published', 2,
    JSON_OBJECT('tagline', 'Automation in weeks, not quarters',
                'description', 'Best-in-class no-code platforms configured into one automated environment, to vendor standards.',
                'priceFrom', 'from £499'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_solutions AND slug = 'rdabs');

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_solutions, 'iada', 'IADA', 'published', 3,
    JSON_OBJECT('tagline', 'From dashboards that report to systems that decide',
                'description', 'Predictive models and agentic reasoning layered onto your business intelligence.',
                'priceFrom', 'from £1,999'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_solutions AND slug = 'iada');

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_solutions, 'abos-solution', 'ABOS', 'published', 4,
    JSON_OBJECT('tagline', 'The operating system your business is currently doing by hand',
                'description', 'An AI-native core where autonomous agents plan and execute across departments.',
                'priceFrom', 'from £1,599'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_solutions AND slug = 'abos-solution');

-- Always refresh this entry's description to track what the ABOS platform
-- actually ships (its module set and role-based access model), same "safe to
-- re-run, this content is owned by this seed file" convention as the home
-- page's layout_json above. Modules per AppModule.java / the platform's
-- Enterprise plan seed (dim_subscription_plans): CRM, Finance, HRM, Projects,
-- Procurement, Real Estate, Legal, Communication, Web/CMS. Access is
-- role-scoped (Tenant Admin manages their tenant; Tenant Manager/staff only
-- see the businesses they're granted) rather than one flat login for
-- everyone — that's the "user journey" difference worth naming here, not a
-- specific price tier, since there is currently only one (all-inclusive,
-- not-yet-priced) plan in the platform's own subscription-plan table.
UPDATE fact_web_entries SET data_json = JSON_OBJECT(
    'tagline', 'The operating system your business is currently doing by hand',
    'description', 'One platform for CRM, Finance, HR, Projects, Procurement, and your public website — with role-based access so admins, managers and staff each only see what they''re provisioned for, from onboarding to daily use.',
    'priceFrom', 'from £1,599'
)
WHERE collection_id = @coll_solutions AND slug = 'abos-solution';

-- ── 7. Entries: blueprint-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_blueprint_steps, 'audit', 'Audit', 'published', 1,
    JSON_OBJECT('number', '01', 'description', 'We map where manual bridging happens and what it costs in hours.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blueprint_steps AND slug = 'audit');

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_blueprint_steps, 'agentic-architecture', 'Agentic architecture', 'published', 2,
    JSON_OBJECT('number', '02', 'description', 'A blueprint of the workflows, agents and integration layers for your operation.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blueprint_steps AND slug = 'agentic-architecture');

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_blueprint_steps, 'ecosystem-deployment', 'Ecosystem deployment', 'published', 3,
    JSON_OBJECT('number', '03', 'description', 'Built, integrated and put into production against acceptance criteria.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blueprint_steps AND slug = 'ecosystem-deployment');

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_blueprint_steps, 'continuous-scaling', 'Continuous scaling', 'published', 4,
    JSON_OBJECT('number', '04', 'description', 'New departments and workflows added as modules, not migrations.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_blueprint_steps AND slug = 'continuous-scaling');

-- ── 8. Entries: convergence-pillars ──────────────────────────────────────────
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_convergence, 'your-business', 'Your business', 'published', 1,
    JSON_OBJECT('description', 'Your operating logic, your constraints, your people. We map it rather than assume a template fits.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_convergence AND slug = 'your-business');

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_convergence, 'techmac-intelligence', 'TechMAC intelligence', 'published', 2,
    JSON_OBJECT('description', 'Integration engineering and agentic architecture — the layer that makes your systems reason instead of wait.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_convergence AND slug = 'techmac-intelligence');

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_convergence, 'industry-practices', 'Industry practices', 'published', 3,
    JSON_OBJECT('description', 'What already works in your sector, so we are not re-deriving a solved problem at your expense.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_convergence AND slug = 'industry-practices');

-- ── 9. Entry: amber-demo (single) ────────────────────────────────────────────
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_amber_demo, 'amber-conversation', 'Amber conversation', 'published', 1,
    JSON_OBJECT(
        'tagline', 'An agent that reads your stack, not a chat widget',
        'description', 'Amber runs the same diagnostic we run on a discovery call. At launch the conversation is scripted and server-rendered; the reasoning layer lands when the backend does.',
        'lines', JSON_ARRAY(
            JSON_OBJECT('speaker', 'you', 'text', 'Our stock numbers never match the finance report.'),
            JSON_OBJECT('speaker', 'amber', 'text', 'Two sources of truth, reconciled by hand. I can model one.'),
            JSON_OBJECT('speaker', 'amber', 'text', 'Proposed: single inventory record, agent-reconciled nightly, variance escalated at 2%.')
        )
    ), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_amber_demo AND slug = 'amber-conversation');

-- ── 10. Entries: 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_services, 'websites', 'Websites', 'published', 1, JSON_OBJECT('priceFrom', 'from £1,999'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'websites');
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_services, 'e-store', 'E-Store', 'published', 2, JSON_OBJECT('priceFrom', 'from £3,499'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'e-store');
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_services, 'app-engineering', 'App Engineering', 'published', 3, JSON_OBJECT('priceFrom', 'Scoped'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'app-engineering');
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_services, 'ai-automation', 'AI Automation', 'published', 4, JSON_OBJECT('priceFrom', 'from £3,099'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'ai-automation');
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_services, 'bi-dashboarding', 'BI Dashboarding', 'published', 5, JSON_OBJECT('priceFrom', 'from £1,999'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'bi-dashboarding');
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_services, 'cybersecurity', 'Cybersecurity', 'published', 6, JSON_OBJECT('priceFrom', 'Scoped'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'cybersecurity');
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_services, 'digital-marketing', 'Digital Marketing', 'published', 7, JSON_OBJECT('priceFrom', 'from £1,249'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'digital-marketing');
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_services, 'no-code', 'No-Code', 'published', 8, JSON_OBJECT('priceFrom', 'from £999'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_services AND slug = 'no-code');

-- ── 11. Entries: industries ──────────────────────────────────────────────────
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_industries, 'restaurants', 'Restaurants', 'published', 1, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'restaurants');
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_industries, 'qsr', 'QSR', 'published', 2, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'qsr');
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_industries, 'cloud-kitchens', 'Cloud kitchens', 'published', 3, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'cloud-kitchens');
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_industries, 'pubs-and-bars', 'Pubs & bars', 'published', 4, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'pubs-and-bars');
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_industries, 'franchises', 'Franchises', 'published', 5, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'franchises');
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_industries, 'retail-and-hospitality', 'Retail & hospitality', 'published', 6, JSON_OBJECT(), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_industries AND slug = 'retail-and-hospitality');

-- ── 12. Entries: faq ──────────────────────────────────────────────────────────
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_faq, 'what-does-an-audit-involve', 'What does an audit actually involve?', 'published', 1,
    JSON_OBJECT('answer', 'A 45-minute discovery call, then a review of the systems and processes you name. The output is an architecture blueprint listing the workflows worth automating and the integration layers required.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_faq AND slug = 'what-does-an-audit-involve');

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_faq, 'how-long-does-deployment-take', 'How long does deployment take?', 'published', 2,
    JSON_OBJECT('answer', 'Automation work on supported platforms runs two to eight weeks. Bespoke agentic systems start at six to eight weeks for one department, and the blueprint states the schedule before you commit.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_faq AND slug = 'how-long-does-deployment-take');

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_faq, 'can-you-integrate-with-what-we-run', 'Can you integrate with what we already run?', 'published', 3,
    JSON_OBJECT('answer', 'Yes — that is the core of the work. The integration layer holds explicit contracts to the systems you keep, validated at every boundary.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_faq AND slug = 'can-you-integrate-with-what-we-run');

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_faq, 'how-does-pricing-work', 'How does pricing work?', 'published', 4,
    JSON_OBJECT('answer', 'Packaged solutions carry published prices with three tiers each. Scoped engineering work is quoted after the discovery call. Third-party subscriptions are passed through at cost.'), NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE collection_id = @coll_faq AND slug = 'how-does-pricing-work');

-- ── 13. 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', 'Solutions', '/#solutions', 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 = 'Solutions');

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', 'Services', '/#services', 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 = '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, NULL, 'header', 'Products', '/#solutions', 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 = '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', 'Industries', '/#industries', 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 = 'Industries');

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', 'Pricing', '/#solutions', 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 = 'Pricing');

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', '/#convergence', 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 = 'About');

-- Header nav items created in earlier seed runs (Industries, About) keep their
-- original sort_order forever under the guarded-INSERT pattern above — only a
-- brand-new label gets the sort_order in its INSERT. Force the correct order
-- every run (matches the source design: Solutions, Services, Products,
-- Industries, Pricing, About).
UPDATE dim_web_navigation_items SET sort_order = 1 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Solutions';
UPDATE dim_web_navigation_items SET sort_order = 2 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Services';
UPDATE dim_web_navigation_items SET sort_order = 3 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Products';
UPDATE dim_web_navigation_items SET sort_order = 4 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Industries';
UPDATE dim_web_navigation_items SET sort_order = 5 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'Pricing';
UPDATE dim_web_navigation_items SET sort_order = 6 WHERE site_id = @site_id AND location = 'header' AND parent_id IS NULL AND label = 'About';

-- Footer columns — FLAT rows grouped by column_title, NOT parent/child. The
-- original version of this section modeled "Company" as a parent nav item
-- with two children (Book a discovery call / Contact the architects) under
-- it, but PublicWebService.footerColumns() explicitly skips any item with a
-- parent (`if (item.getParent() != null) continue; // footer columns are
-- flat, not nested`) and groups by column_title instead — confirmed live:
-- the footer was only ever rendering the single parent "Company" -> "#" row
-- and silently dropping both children. Clean up that broken shape and
-- replace it with the flat column_title rows the backend actually reads.
DELETE FROM dim_web_navigation_items
WHERE site_id = @site_id AND location = 'footer' AND label = 'Company' AND parent_id IS NULL;
DELETE FROM dim_web_navigation_items
WHERE site_id = @site_id AND location = 'footer' AND label IN ('Book a discovery call', 'Contact the architects') AND parent_id IS NOT NULL;

-- Solutions column
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', 'Solutions', 'Go Digital', '/#solutions', 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 = 'Solutions' AND label = 'Go Digital');
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', 'Solutions', 'RDABS', '/#solutions', 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 = 'Solutions' AND label = 'RDABS');
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', 'Solutions', 'IADA', '/#solutions', 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 = 'Solutions' AND label = 'IADA');
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', 'Solutions', 'ABOS', '/#solutions', 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 = 'Solutions' AND label = 'ABOS');
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', 'Solutions', 'Pricing', '/#solutions', 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 = 'Solutions' AND label = 'Pricing');

-- Services column
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', 'Services', 'Websites', '/#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 = 'Services' AND label = 'Websites');
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', 'Services', 'E-Store', '/#services', 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 = 'Services' AND label = 'E-Store');
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', 'Services', 'App Engineering', '/#services', 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 = 'Services' AND label = 'App Engineering');
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', 'Services', 'AI Automation', '/#services', 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 = 'Services' AND label = 'AI Automation');
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', 'Services', 'BI Dashboarding', '/#services', 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 = 'Services' AND label = 'BI Dashboarding');
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', 'Services', 'Cybersecurity', '/#services', 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 = 'Services' AND label = 'Cybersecurity');
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', 'Services', 'Digital Marketing', '/#services', 7, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Services' AND label = 'Digital Marketing');
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', 'Services', 'No-Code', '/#services', 8, NULL, NULL, 0
WHERE NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer' AND column_title = 'Services' AND label = 'No-Code');

-- Company column
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', 'About', '/#convergence', 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 = 'About');
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', 'Industries', '/#industries', 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 = 'Industries');
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', '/forms/contact', 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', 'Company', 'Book a call', '/forms/discovery-call', 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 = 'Company' AND label = 'Book a call');

-- Legal column — links to real (draft) pages seeded in section 16 below,
-- servable via TechMAC/frontend's src/app/[slug]/page.tsx catch-all.
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', 'Legal', 'Terms', '/terms', 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 = 'Legal' AND label = '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', 'Legal', 'Privacy', '/privacy', 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 = 'Legal' AND label = 'Privacy');
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', 'Legal', 'Cookies', '/cookies', 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 = 'Legal' AND label = 'Cookies');
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', 'Legal', 'Accessibility', '/accessibility', 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 = 'Legal' AND label = 'Accessibility');

-- ── 14. Forms: discovery-call, contact ───────────────────────────────────────
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, 'discovery-call', 'Book a Discovery Call',
       'Thanks — we will be in touch to schedule your discovery call (Mon-Fri, 18:00-22:00 UK).', 1
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'discovery-call');

SET @form_discovery_id := (SELECT id FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'discovery-call');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_discovery_id AS a, 'name' AS b, 'Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @form_discovery_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_discovery_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_discovery_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 2 AS f) AS t
WHERE @form_discovery_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_discovery_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_discovery_id AS a, 'company' AS b, 'Company' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @form_discovery_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_discovery_id AND field_key = 'company');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_discovery_id AS a, 'phone' AS b, 'Phone' AS c, 'text' AS d, 0 AS e, 4 AS f) AS t
WHERE @form_discovery_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_discovery_id AND field_key = 'phone');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_discovery_id AS a, 'message' AS b, 'Where''s the friction?' AS c, 'textarea' AS d, 0 AS e, 5 AS f) AS t
WHERE @form_discovery_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_discovery_id AND field_key = 'message');

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, 'contact', 'Contact the Architects',
       'Thanks — the architects will get back to you shortly.', 1
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @site_id AND form_key = 'contact');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_contact_id AS a, 'name' AS b, 'Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @form_contact_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact_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_contact_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 2 AS f) AS t
WHERE @form_contact_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact_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_contact_id AS a, 'company' AS b, 'Company' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @form_contact_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact_id AND field_key = 'company');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @form_contact_id AS a, 'message' AS b, 'Message' AS c, 'textarea' AS d, 1 AS e, 4 AS f) AS t
WHERE @form_contact_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @form_contact_id AND field_key = 'message');

-- ── 15. CMS page: home (single-page site — Solutions/Services/etc are anchors) ─
-- Block types/colors here match the real computed styles/CSS custom
-- properties read directly out of "TechMAC Website.html" (light design,
-- green --signal accent, flat hairline-divider grids, three dark "console"
-- sections: the ticker strip, the Amber demo, and the closing CTA).
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()), 'TechMAC | Architecting your agentic future'
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).
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', 'Ecosystem architecture · UK & global',
      'heading', 'Architecting your agentic future',
      'subheading', 'We engineer bespoke ecosystems that unify CRM, ERP and CMS into a single intelligent engine — so your systems stop needing a human to bridge them.',
      'ctaLabel', 'Book a discovery call',
      'ctaHref', '/forms/discovery-call',
      'secondaryLabel', 'See solutions & pricing',
      'secondaryHref', '/#solutions',
      'statusItems', JSON_ARRAY('CRM DATA', 'AI ORCHESTRATION', 'ERP SYNC')
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-ticker', 'type', 'ticker', 'props', JSON_OBJECT(
      'lines', JSON_ARRAY(
        'SYS.ERR — CRM and ERP disagree on 1,412 records',
        'DIAGNOSING — mapping manual bridges across 4 systems',
        'DEPLOYED — agentic sync live, 0 manual handovers'
      )
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-problem-heading', 'type', 'richText', 'props', JSON_OBJECT('html',
      '<h2>Diagnosing the friction in your scale</h2><p>Growth rarely stalls because a company lacks tools. It stalls because the tools do not talk, and someone senior spends their week moving data between them by hand. We start every engagement by mapping where that manual bridging happens, what it costs in hours, and which of it can be handed to systems that reason and act.</p>'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-problem-points', 'type', 'numberedGrid', 'props', JSON_OBJECT('collectionKey', 'problem-points'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'solutions', 'type', 'pricedGrid', 'props', JSON_OBJECT('eyebrow', 'Four packaged solutions', 'collectionKey', 'solutions', 'variant', 'cards', 'viewAllLabel', 'Compare all →', 'viewAllHref', '#solutions'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-blueprint-steps', 'type', 'numberedGrid', 'props', JSON_OBJECT('eyebrow', 'The blueprint process', 'heading', 'Four steps, in order', 'collectionKey', 'blueprint-steps'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'convergence', 'type', 'pillarGrid', 'props', JSON_OBJECT(
      'heading', 'Where the work actually converges',
      'description', 'A working system needs three things in the same room. We supply the middle one and refuse to guess at the other two.',
      'collectionKey', 'convergence-pillars'
    ), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-amber-demo', 'type', 'chatDemo', 'props', JSON_OBJECT('eyebrow', 'Amber · agent demo', 'collectionKey', 'amber-demo', 'primaryLabel', 'View blueprint', 'primaryHref', '#', 'secondaryLabel', 'Deploy staging', 'secondaryHref', '#'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'services', 'type', 'pricedGrid', 'props', JSON_OBJECT('eyebrow', 'Services · scoped per project', 'collectionKey', 'services', 'variant', 'list', 'viewAllLabel', 'All services →', 'viewAllHref', '#services'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'industries', 'type', 'flatList', 'props', JSON_OBJECT('eyebrow', 'Industries', 'collectionKey', 'industries', 'viewAllLabel', 'All industries →', 'viewAllHref', '#industries'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'home-faq', 'type', 'faqAccordion', 'props', JSON_OBJECT('heading', 'Questions we get first', 'collectionKey', 'faq'), 'children', JSON_ARRAY()),
    JSON_OBJECT('id', 'closing-cta', 'type', 'ctaBanner', 'props', JSON_OBJECT(
      'heading', 'Tell us where the friction is',
      'description', 'Discovery calls run 18:00-22:00 UK time, Monday to Friday.',
      'ctaLabel', 'Book a discovery call',
      'ctaHref', '/forms/discovery-call',
      'secondaryLabel', 'Contact the architects',
      'secondaryHref', '/forms/contact'
    ), 'children', JSON_ARRAY())
  )
)
WHERE site_id = @site_id AND slug = 'home';

-- ── 16. Legal pages (draft — the footer's Legal column above links here) ────
-- Seeded as 'draft', not 'published': PublicWebService.page() only ever
-- returns published, non-deleted pages, so these stay 404 on the live site
-- (via TechMAC/frontend's [slug] catch-all) until someone reviews the
-- placeholder copy below in the Page Builder and flips status to Published.
-- This is deliberately NOT a substitute for real legal review — it exists so
-- the footer's Legal links resolve to something editable instead of "#".
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, 'terms', 'Terms of Service', '/terms', 'draft',
    JSON_OBJECT('version', 1, 'blocks', JSON_ARRAY(JSON_OBJECT('id', 'terms-body', 'type', 'richText', 'props', JSON_OBJECT('html',
        '<p><em>Draft placeholder — replace with reviewed Terms of Service copy before publishing.</em></p><h2>Terms of Service</h2><p>These terms govern your use of TechMAC''s website and services. By using this site, you agree to them.</p><h3>Services</h3><p>TechMAC provides technology and AI consulting, automation and web engineering services as described on this site and agreed in a separate statement of work.</p><h3>Contact</h3><p>Questions about these terms can be sent to hello@technologymac.co.uk.</p>'
    ), 'children', JSON_ARRAY()))),
    'Terms of Service | TechMAC'
WHERE @site_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'terms');

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, 'privacy', 'Privacy Policy', '/privacy', 'draft',
    JSON_OBJECT('version', 1, 'blocks', JSON_ARRAY(JSON_OBJECT('id', 'privacy-body', 'type', 'richText', 'props', JSON_OBJECT('html',
        '<p><em>Draft placeholder — replace with reviewed Privacy Policy copy (UK GDPR-compliant) before publishing.</em></p><h2>Privacy Policy</h2><p>This policy explains what personal data TechMAC collects through this site (e.g. via contact and discovery-call forms), how it is used, and your rights over it.</p><h3>What we collect</h3><p>Name, email, phone and company details you submit through a form on this site.</p><h3>Your rights</h3><p>You can request access to, correction of, or deletion of your data by contacting hello@technologymac.co.uk.</p>'
    ), 'children', JSON_ARRAY()))),
    'Privacy Policy | TechMAC'
WHERE @site_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'privacy');

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, 'cookies', 'Cookie Policy', '/cookies', 'draft',
    JSON_OBJECT('version', 1, 'blocks', JSON_ARRAY(JSON_OBJECT('id', 'cookies-body', 'type', 'richText', 'props', JSON_OBJECT('html',
        '<p><em>Draft placeholder — replace with a cookie inventory matching what this site actually sets before publishing.</em></p><h2>Cookie Policy</h2><p>This site may use cookies for essential site functionality and, if enabled, analytics. This page will list each cookie, its purpose, and how to opt out once analytics/tracking is configured.</p>'
    ), 'children', JSON_ARRAY()))),
    'Cookie Policy | TechMAC'
WHERE @site_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'cookies');

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, 'accessibility', 'Accessibility Statement', '/accessibility', 'draft',
    JSON_OBJECT('version', 1, 'blocks', JSON_ARRAY(JSON_OBJECT('id', 'accessibility-body', 'type', 'richText', 'props', JSON_OBJECT('html',
        '<p><em>Draft placeholder — replace with a real accessibility statement (e.g. WCAG 2.1 AA conformance status) before publishing.</em></p><h2>Accessibility Statement</h2><p>TechMAC is committed to making this site usable for as many people as possible. If you experience any difficulty accessing content here, contact hello@technologymac.co.uk and we will address it.</p>'
    ), 'children', JSON_ARRAY()))),
    'Accessibility Statement | TechMAC'
WHERE @site_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_pages WHERE site_id = @site_id AND slug = 'accessibility');