-- =============================================================================
-- allbets_cms_content_seed.sql — Populate allBETs' CMS content: site nav/social,
-- the "docs" collection (Services/Sportsbook/Diversity/Bios/Workbook/Legal/the
-- 4 policy pages), and the "blog-posts" collection (Act Like A Lady blog).
--
-- allbets_site_and_catalog_seed.sql already created the dim_web_sites row and
-- the store catalog; this file fills the remaining gap found when auditing
-- production: the site row had 0 nav items / 0 collections / 0 entries, so
-- abos-site's header/footer/docs/blog pages were all silently falling back to
-- their local lib/content/*.json copies (by design — see lib/api.ts's
-- fail-soft comment — but that's a stopgap, not the intended end state).
--
-- Source of truth for the copy: abos-site/src/lib/content/{site,docs,blog}.json
-- (the same content already shipped as the local fallback), now promoted into
-- the CMS as the real source so it's editable from ContentLibrary/Navigation
-- admin tabs without a deploy.
--
-- Idempotent: every INSERT is guarded (NOT EXISTS / JSON_LENGTH check), safe
-- to re-run. allBETs' home page ("/") reads only getSiteSettings(), not a
-- CMS page — there is no dim_web_pages row needed for it, unlike LarCare.
--
-- Deploy: mysql --default-character-set=utf8mb4 -u root -p db_abos_v0.1 < db/allbets_cms_content_seed.sql
-- =============================================================================

USE db_abos_v0.1;

SET @site_id := (SELECT id FROM dim_web_sites WHERE primary_domain = 'allbetsgo.com' LIMIT 1);
SET @tenant_id := (SELECT tenant_id FROM dim_web_sites WHERE id = @site_id);
SET @business_id := (SELECT business_id FROM dim_web_sites WHERE id = @site_id);

-- ── 1. Social links ──────────────────────────────────────────────────────────
UPDATE dim_web_sites
SET social_links_json = JSON_ARRAY(
  JSON_OBJECT('platform', 'facebook', 'url', 'https://www.facebook.com/All-Bets-LLC-103885721599319'),
  JSON_OBJECT('platform', 'instagram', 'url', 'https://www.instagram.com/allbetsgo/'),
  JSON_OBJECT('platform', 'x', 'url', 'https://twitter.com/allbetscount'),
  JSON_OBJECT('platform', 'linkedin', 'url', 'https://www.linkedin.com/company/all-bets-llc/')
)
WHERE id = @site_id AND (social_links_json IS NULL OR JSON_LENGTH(social_links_json) = 0);

-- ── 2. Header nav ────────────────────────────────────────────────────────────
INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, location, label, href, sort_order)
SELECT * FROM (SELECT @tenant_id, @business_id, @site_id, 'header', v.label, v.href, v.sort_order
  FROM (
    SELECT 'Home' AS label, '/' AS href, 1 AS sort_order UNION ALL
    SELECT 'Services', '/services', 2 UNION ALL
    SELECT 'Sportsbook', '/sportsbook', 3 UNION ALL
    SELECT 'Diversity', '/diversity', 4 UNION ALL
    SELECT 'Bios', '/bios', 5 UNION ALL
    SELECT 'Act Like A Lady', '/blog', 6 UNION ALL
    SELECT 'Workbook', '/playbook', 7 UNION ALL
    SELECT 'Contact', '/contact', 8
  ) AS v
) AS t
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'header');

-- ── 3. Footer nav (3 columns: Shop, Policies, Company) ──────────────────────
INSERT INTO dim_web_navigation_items (tenant_id, business_id, site_id, location, column_title, label, href, sort_order)
SELECT * FROM (SELECT @tenant_id, @business_id, @site_id, 'footer', v.column_title, v.label, v.href, v.sort_order
  FROM (
    SELECT 'Shop' AS column_title, 'Collections' AS label, '/collections' AS href, 1 AS sort_order UNION ALL
    SELECT 'Shop', 'All goods', '/shop', 2 UNION ALL
    SELECT 'Shop', 'Portfolio', '/lookbook', 3 UNION ALL
    SELECT 'Shop', 'Lookbook', '/lookbook', 4 UNION ALL
    SELECT 'Policies', 'Store policy', '/store-policy', 5 UNION ALL
    SELECT 'Policies', 'Return policy', '/return-policy', 6 UNION ALL
    SELECT 'Policies', 'Payment policy', '/payment-policy', 7 UNION ALL
    SELECT 'Policies', 'Shipping policy', '/shipping-policy', 8 UNION ALL
    SELECT 'Company', 'Contact Us', '/contact', 9 UNION ALL
    SELECT 'Company', 'Legal', '/legal', 10 UNION ALL
    SELECT 'Company', 'Act Like A Lady', '/blog', 11 UNION ALL
    SELECT 'Company', 'Workbook + Playbook', '/playbook', 12
  ) AS v
) AS t
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_navigation_items WHERE site_id = @site_id AND location = 'footer');

-- ── 4. Collections ───────────────────────────────────────────────────────────
INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, sort_order)
SELECT @tenant_id, @business_id, @site_id, 'docs', 'Docs', 'Doc', 1
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'docs');

INSERT INTO dim_web_collections (tenant_id, business_id, site_id, collection_key, label, label_singular, sort_order)
SELECT @tenant_id, @business_id, @site_id, 'blog-posts', 'Blog Posts', 'Blog Post', 2
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'blog-posts');

SET @docs_collection_id := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'docs');
SET @blog_collection_id := (SELECT id FROM dim_web_collections WHERE site_id = @site_id AND collection_key = 'blog-posts');

-- ── 5. "docs" entries ────────────────────────────────────────────────────────

-- Services (has cards + eyebrow)
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'services', 'Our Services', 'published', 1, JSON_OBJECT(
  'excerpt', 'All Bets Professional Services — helping businesses hit by the pandemic grow and transform using technology, diversity and strategic innovation.',
  'eyebrow', 'Business Consulting',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'cards', JSON_ARRAY(
    JSON_OBJECT('title', 'Business Advisory Services', 'description', 'Create the most efficient plan for you to take your business the extra mile.'),
    JSON_OBJECT('title', 'Strategic Innovation', 'description', 'Find the best blueprint for the future of your business.'),
    JSON_OBJECT('title', 'Business Transformation Services', 'description', 'Implement a strategy to bring your business the success it deserves.')
  ),
  'bodyHtml', '<h3>01. Business Advisory</h3><p>Our Business Advisory Services will analyze every aspect of your business so we can create the most efficient plan for you to take your business the extra mile. Even if you are satisfied with the current performance of your business, there is always room for improvement.</p><h3>02. Strategic Innovation</h3><p>Our market research will reveal a host of new growth strategies for your leadership team to reimagine the future of your organization and to create an execution strategy. Companies that have a robust Strategic Innovation Plan naturally become the trend-setters in their industries.</p><h3>03. Business Transformation Services</h3><p>Business Transformation is the action of systemic evolution across the procedures, programs, technology platforms and personnel of a business to yield improvements in culture, profits and success. We will observe your procedures, create a game-plan, then collaborate to implement the strategy.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'services');

-- The INSERT above is generic consulting boilerplate that was never customized for
-- allBETs (wrong industry entirely — leftover from whatever template this seed started
-- from) and, being already-seeded, would never be touched by the WHERE NOT EXISTS guard
-- above on a re-run. Force a refresh to what allBETs actually is, using only facts already
-- established elsewhere in this same seed file (sportsbook/diversity entries below, the
-- allBETs Go store) rather than invented business claims.
UPDATE fact_web_entries SET data_json = JSON_OBJECT(
  'excerpt', 'allBETs is a picks-and-community platform for sports betting culture — content, merch, and programming built to open the table to everyone.',
  'eyebrow', 'What We Do',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'cards', JSON_ARRAY(
    JSON_OBJECT('title', 'Picks & Community Content', 'description', 'Smarter picks and bankroll discipline, written for fans of the game, not just the loudest voice in the group chat.'),
    JSON_OBJECT('title', 'allBETs Go Merch', 'description', 'Performance and streetwear pieces designed for game day, on and off the court.'),
    JSON_OBJECT('title', 'Diversity-Driven Programming', 'description', 'Content and community built to open sports betting culture to everyone at the table.')
  ),
  'bodyHtml', '<h3>01. Picks &amp; Community</h3><p>We break down picks, bankroll discipline, and the culture of sports betting for anyone who loves the game — entertainment and commentary, never wagers or betting funds.</p><h3>02. allBETs Go</h3><p>Our merch line brings the same energy to what you wear: performance and streetwear pieces built for game day, sized and styled for everyone who shows up.</p><h3>03. Diversity &amp; Access</h3><p>Sports betting culture has long skewed toward one audience. Our programming is built to open the table to everyone — women, first-timers, and lifelong fans alike.</p>'
)
WHERE collection_id = @docs_collection_id AND slug = 'services';

-- Sportsbook
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'sportsbook', 'Sportsbook', 'published', 2, JSON_OBJECT(
  'excerpt', 'How allBETs approaches sports betting content.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<h3>Picks, not wagers</h3><p>allBETs is a picks-and-community platform, not a licensed sportsbook — we don''t accept wagers or process betting funds. Content here is entertainment and community commentary around sports betting culture.</p><p>Always bet within your means, follow the laws of your jurisdiction, and treat betting as entertainment, never as income.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'sportsbook');

-- Diversity
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'diversity', 'Diversity', 'published', 3, JSON_OBJECT(
  'excerpt', 'Why allBETs exists.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<h3>Built for everyone at the table</h3><p>Sports betting culture has long skewed toward one audience. allBETs was built to open the table to everyone — women, first-timers, and lifelong fans alike — with content, community, and gear that speaks to all of us.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'diversity');

-- Bios
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'bios', 'Bios', 'published', 4, JSON_OBJECT(
  'excerpt', 'The people behind allBETs.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<h3>The team</h3><p>allBETs is built by a small team of sports fans, bettors, and builders based in Washington, D.C. Full bios coming soon.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'bios');

-- Workbook / Playbook (has downloads)
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'playbook', 'Workbook / Playbook', 'published', 5, JSON_OBJECT(
  'excerpt', 'Downloadable guides to get you started.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>Free downloadable guides for getting the most out of allBETs.</p>',
  'downloads', JSON_ARRAY(
    JSON_OBJECT('label', 'Getting Started Playbook (PDF)', 'url', '/downloads/allbets-getting-started.pdf'),
    JSON_OBJECT('label', 'Bankroll Basics Workbook (PDF)', 'url', '/downloads/allbets-bankroll-workbook.pdf')
  )
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'playbook');

-- Legal
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'legal', 'Legal', 'published', 6, JSON_OBJECT(
  'excerpt', 'Terms of Sale, Privacy, Cookies, IP, and Governing Law.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<h3>Terms of Sale</h3><p>By placing an order on allbetsgo.com you agree to pay the listed price plus applicable shipping and taxes, and to provide accurate shipping and contact information. All sales are subject to our Store, Return, Payment, and Shipping Policies.</p><h3>Privacy</h3><p>We collect the name, address, email and order history needed to sell and ship to you, and analytics that are aggregated and not tied to your identity. We do not sell customer data and we do not share it beyond our payment, shipping and email providers. You can request a copy or deletion of your data at any time by email; we respond within 30 days.</p><h3>Cookies</h3><p>We use a session cookie to remember your bag, a preference cookie to remember your region, and analytics cookies only if you accept them.</p><h3>Intellectual Property</h3><p>All Bets, allBETs, allBETs Go, and associated logos are trademarks of All Bets LLC. Site content, photography, and designs are owned by All Bets LLC or used under license and may not be reproduced without permission.</p><h3>Governing Law</h3><p>These terms are governed by the laws of the District of Columbia, United States, without regard to its conflict-of-law principles.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'legal');

-- Store Policy
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'store-policy', 'Store Policy', 'published', 7, JSON_OBJECT(
  'excerpt', 'How the allBETs Go store operates.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>allBETs Go sells direct to consumers in the United States. Prices are listed in USD and do not include applicable sales tax, which is calculated at checkout. We reserve the right to limit quantities and to refuse or cancel any order, including orders that appear fraudulent.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'store-policy');

-- Return Policy
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'return-policy', 'Return Policy', 'published', 8, JSON_OBJECT(
  'excerpt', 'Returns and exchanges on allBETs Go merch.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>We accept returns of unworn, unwashed items with tags attached within 30 days of delivery for a refund to your original payment method, or store credit for Cash on Delivery orders. Email shop@allbetsgo.com with your order number to start a return. Return shipping is the customer''s responsibility unless the item arrived damaged or incorrect.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'return-policy');

-- Payment Policy
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'payment-policy', 'Payment Policy', 'published', 9, JSON_OBJECT(
  'excerpt', 'How we handle payment and card data.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>We never see or store full card numbers. Payments are processed by our PCI-DSS Level 1 provider; we hold only the last four digits and the card brand for reconciliation. Cash on Delivery is available in the US on orders under $400, adds a $3 handling fee, and allows one delivery attempt.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'payment-policy');

-- Name the actual processor (Stripe — see abos.stripe.* config and StripeWebhookController,
-- the real, only payment integration this platform has) instead of the vague "our PCI-DSS
-- Level 1 provider," a small trust gap on a page whose whole point is reassurance.
UPDATE fact_web_entries SET data_json = JSON_OBJECT(
  'excerpt', 'How we handle payment and card data.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>We never see or store full card numbers. Payments are processed by Stripe, a PCI-DSS Level 1 provider; we hold only the last four digits and the card brand for reconciliation. Cash on Delivery is available in the US on orders under $400, adds a $3 handling fee, and allows one delivery attempt.</p>'
)
WHERE collection_id = @docs_collection_id AND slug = 'payment-policy';

-- Shipping Policy
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, data_json)
SELECT @tenant_id, @business_id, @site_id, @docs_collection_id, 'shipping-policy', 'Shipping Policy', 'published', 10, JSON_OBJECT(
  'excerpt', 'Delivery times and shipping costs.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>We currently ship within the United States. Standard shipping typically arrives in 5-7 business days; Express shipping typically arrives in 2-3 business days. Exact rates are shown at checkout and may qualify for a free-shipping threshold. Cash on Delivery orders ship Standard only.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @docs_collection_id AND slug = 'shipping-policy');

-- State the real free-shipping threshold ($120 — dim_web_sites.free_shipping_threshold_cents
-- for this site is 12000) instead of leaving the visitor to infer it from the header banner,
-- which itself used to hardcode "$120" separately (now reads the same column — see Header.tsx).
UPDATE fact_web_entries SET data_json = JSON_OBJECT(
  'excerpt', 'Delivery times and shipping costs.',
  'coverImage', '', 'author', '', 'publishedAt', '', 'tags', JSON_ARRAY(),
  'bodyHtml', '<p>We currently ship within the United States. Standard shipping typically arrives in 5-7 business days; Express shipping typically arrives in 2-3 business days. Orders of $120 or more ship free; exact rates for orders under that are shown at checkout. Cash on Delivery orders ship Standard only.</p>'
)
WHERE collection_id = @docs_collection_id AND slug = 'shipping-policy';

-- ── 6. "blog-posts" entries ──────────────────────────────────────────────────
INSERT INTO fact_web_entries (tenant_id, business_id, site_id, collection_id, slug, title, status, sort_order, published_at, data_json)
SELECT @tenant_id, @business_id, @site_id, @blog_collection_id, 'act-like-a-lady-bet-better-than-a-man',
  'Act Like A Lady, Bet Better Than A Man', 'published', 1, '2026-01-15 00:00:00', JSON_OBJECT(
  'excerpt', 'Welcome to the allBETs blog — smarter picks, better bankroll habits, no gatekeeping.',
  'coverImage', '', 'author', 'allBETs Team', 'publishedAt', '2026-01-15',
  'tags', JSON_ARRAY('culture', 'betting-101'),
  'bodyHtml', '<p>Welcome to the allBETs blog. This is where we break down picks, bankroll discipline, and the culture of sports betting — written for anyone who loves the game, not just the loudest voice in the group chat.</p>'
)
WHERE @site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM fact_web_entries WHERE site_id = @site_id AND collection_id = @blog_collection_id AND slug = 'act-like-a-lady-bet-better-than-a-man');