USE db_abos_v0.1;

-- =============================================================================================
-- seed_abos_page_collections.sql — backs /abos with real CMS collections instead of hardcoded
-- frontend arrays, matching how every other repeatable section on this site (solutions,
-- services, faq, problem-points, ...) is a dim_web_collections + fact_web_entries pair fetched
-- via getEntries(collectionKey). Three new collections for TechMAC's site (site_id, business_id
-- resolved below):
--   abos-modules — the 9 real modules from AppModule.java, with a plain-language description.
--   abos-tiers   — the 3 illustrative pricing tiers (Starter/Growth/Enterprise), since ABOS has
--                  no live plan/checkout API (see seed_abos_inquiry_form.sql's comment).
--   abos-faq     — the FAQ entries shown on the page.
--
-- Safe to re-run: WHERE NOT EXISTS seed guards on both collections and entries.
-- =============================================================================================

SET @tenant_id := 1;
SET @techmac_business_id := (SELECT id FROM businesses WHERE tenant_id = @tenant_id AND name = 'TechMAC' LIMIT 1);
SET @techmac_site_id := (SELECT id FROM dim_web_sites WHERE tenant_id = @tenant_id AND name = 'TechMAC' LIMIT 1);

-- ── Collections ──────────────────────────────────────────────────────────────────────────────
INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, has_seo_fields, is_system, sort_order, is_deleted)
SELECT @tenant_id, @techmac_business_id, @techmac_site_id, 'abos-modules', 'ABOS Modules', 'Module', 'collection', 0, 1, 20, 0
WHERE @techmac_site_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-modules'
);
INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, has_seo_fields, is_system, sort_order, is_deleted)
SELECT @tenant_id, @techmac_business_id, @techmac_site_id, 'abos-tiers', 'ABOS Pricing Tiers', 'Tier', 'collection', 0, 1, 21, 0
WHERE @techmac_site_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-tiers'
);
INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, kind, has_seo_fields, is_system, sort_order, is_deleted)
SELECT @tenant_id, @techmac_business_id, @techmac_site_id, 'abos-faq', 'ABOS FAQ', 'Question', 'collection', 0, 1, 22, 0
WHERE @techmac_site_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-faq'
);

SET @modules_collection_id := (SELECT id FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-modules');
SET @tiers_collection_id := (SELECT id FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-tiers');
SET @faq_collection_id := (SELECT id FROM dim_web_collections WHERE site_id = @techmac_site_id AND collection_key = 'abos-faq');

-- ── Collection fields (admin-form schema) ────────────────────────────────────────────────────
INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, is_deleted)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @modules_collection_id AS collection_id, 'description' AS field_key, 'Description' AS label, 'textarea' AS field_type, 1 AS is_required, 1 AS sort_order, 0 AS is_deleted
) seed
WHERE @modules_collection_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @modules_collection_id);

INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, is_deleted)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @tiers_collection_id AS collection_id, 'tagline' AS field_key, 'Tagline' AS label, 'text' AS field_type, 1 AS is_required, 1 AS sort_order, 0 AS is_deleted UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @tiers_collection_id, 'priceFrom', 'Price From', 'text', 1, 2, 0 UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @tiers_collection_id, 'modules', 'Modules included (one per line)', 'textarea', 1, 3, 0 UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @tiers_collection_id, 'featured', 'Featured ("Most chosen" badge)', 'checkbox', 0, 4, 0
) seed
WHERE @tiers_collection_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @tiers_collection_id);

INSERT INTO dim_web_collection_fields (tenant_id, business_id, site_id, collection_id, field_key, label, field_type, is_required, sort_order, is_deleted)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @faq_collection_id AS collection_id, 'answer' AS field_key, 'Answer' AS label, 'textarea' AS field_type, 1 AS is_required, 1 AS sort_order, 0 AS is_deleted
) seed
WHERE @faq_collection_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM dim_web_collection_fields WHERE collection_id = @faq_collection_id);

-- ── Entries: abos-modules (the 9 real modules from AppModule.java) ──────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_deleted, data_json)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @modules_collection_id AS collection_id, 'crm' AS slug, 'CRM' AS title, 'published' AS status, 1 AS sort_order, 0 AS is_deleted,
        JSON_OBJECT('description', 'Contacts, clients, opportunities and leads through to invoices and commissions, plus campaign and quote-form tracking.') AS data_json UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'finance', 'Finance', 'published', 2, 0,
        JSON_OBJECT('description', 'Transactions, ledger and VAT handling, kept in sync with everything CRM and Procurement post.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'hr', 'HR', 'published', 3, 0,
        JSON_OBJECT('description', 'The staff side of the business: people, roles and the records HR actually needs day to day.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'projects', 'Projects', 'published', 4, 0,
        JSON_OBJECT('description', 'Plan and track delivery work without a separate tool bolted on the side.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'procurement-inventory', 'Procurement & Inventory', 'published', 5, 0,
        JSON_OBJECT('description', 'Materials, products and physical assets, plus the purchase orders that move them.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'real-estate', 'Real Estate', 'published', 6, 0,
        JSON_OBJECT('description', 'Track property and physical assets for businesses that manage premises as part of the operation.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'legal', 'Legal', 'published', 7, 0,
        JSON_OBJECT('description', 'Liabilities and the obligations attached to them, visible alongside the rest of the business.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'communication', 'Communication', 'published', 8, 0,
        JSON_OBJECT('description', 'Internal messaging and notifications wired into the same records everyone else is working from.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @modules_collection_id, 'web-cms', 'Web & CMS', 'published', 9, 0,
        JSON_OBJECT('description', 'The public site and store module. This page, and the rest of technologymac.co.uk, runs on it.')
) seed
WHERE @modules_collection_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM fact_web_entries WHERE collection_id = @modules_collection_id
);

-- ── Entries: abos-tiers (illustrative pricing) ───────────────────────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_deleted, data_json)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @tiers_collection_id AS collection_id, 'starter' AS slug, 'ABOS Starter' AS title, 'published' AS status, 1 AS sort_order, 0 AS is_deleted,
        JSON_OBJECT('tagline', 'For a business ready to leave spreadsheets behind.', 'priceFrom', '£1,599', 'modules', 'CRM\nFinance\nWeb & CMS', 'featured', CAST(0 AS JSON)) AS data_json UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @tiers_collection_id, 'growth', 'ABOS Growth', 'published', 2, 0,
        JSON_OBJECT('tagline', 'For a business coordinating delivery, not just sales.', 'priceFrom', '£2,499', 'modules', 'Everything in Starter\nProjects\nProcurement & Inventory\nHR', 'featured', CAST(1 AS JSON)) UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @tiers_collection_id, 'enterprise', 'ABOS Enterprise', 'published', 3, 0,
        JSON_OBJECT('tagline', 'For a business that needs every module talking to every other one.', 'priceFrom', 'Custom', 'modules', 'Everything in Growth\nReal Estate\nLegal\nCommunication\nDedicated integration work', 'featured', CAST(0 AS JSON))
) seed
WHERE @tiers_collection_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM fact_web_entries WHERE collection_id = @tiers_collection_id
);

-- ── Entries: abos-faq ─────────────────────────────────────────────────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, is_deleted, data_json)
SELECT * FROM (
    SELECT @tenant_id AS tenant_id, @techmac_business_id AS business_id, @techmac_site_id AS site_id, @faq_collection_id AS collection_id, 'is-this-a-template-or-a-real-system' AS slug, 'Is this a template, or a real system?' AS title, 'published' AS status, 1 AS sort_order, 0 AS is_deleted,
        JSON_OBJECT('answer', 'Real. ABOS runs TechMAC''s own CRM, finance records and this website''s CMS today, not a separate demo environment.') AS data_json UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @faq_collection_id, 'do-we-have-to-take-every-module', 'Do we have to take every module?', 'published', 2, 0,
        JSON_OBJECT('answer', 'No. Most businesses start with the CRM, Finance and Web modules on the Starter tier and add Projects, Procurement or HR once they need them, which is exactly what the Growth tier covers.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @faq_collection_id, 'why-from-pricing', 'Why is pricing shown as "from" instead of a fixed number?', 'published', 3, 0,
        JSON_OBJECT('answer', 'Because ABOS is scoped to your module mix, user count and any data migration involved, the same way any real ERP engagement is priced. "From" is the honest floor, not a teaser rate.') UNION ALL
    SELECT @tenant_id, @techmac_business_id, @techmac_site_id, @faq_collection_id, 'how-long-does-onboarding-take', 'How long does onboarding take?', 'published', 4, 0,
        JSON_OBJECT('answer', 'It depends on how many modules and how much existing data needs migrating, but a TechMAC architect will give you a real timeline after the discovery call, not before.')
) seed
WHERE @faq_collection_id IS NOT NULL AND NOT EXISTS (
    SELECT 1 FROM fact_web_entries WHERE collection_id = @faq_collection_id
);