-- =====================================================================
-- LarCare — read views
-- Source: docs/02-DATABASE-SCHEMA.md §4
-- Idempotent (CREATE OR REPLACE) — safe to re-run on every deploy.
-- =====================================================================

-- Site settings assembled with social links as JSON, so the API does one
-- query instead of two and the frontend gets the shape it already expects.
CREATE OR REPLACE VIEW vw_site_settings AS
SELECT
  s.company_name  AS companyName,
  s.tagline       AS tagline,
  s.phone         AS phone,
  s.email         AS email,
  s.address       AS address,
  s.states_served AS statesServed,
  (SELECT COALESCE(
            JSON_ARRAYAGG(JSON_OBJECT('platform', t.platform, 'url', t.url)),
            JSON_ARRAY())
     FROM (SELECT platform, url
             FROM social_links
            WHERE is_active = 1
            ORDER BY sort_order) AS t
  ) AS social
FROM site_settings s
WHERE s.id = 1;

-- Header nav, flattened parent+child. The API nests children by parent_label.
CREATE OR REPLACE VIEW vw_nav_items AS
SELECT
  n.id            AS id,
  n.parent_id     AS parentId,
  n.location      AS location,
  n.label         AS label,
  n.href          AS href,
  n.sort_order    AS sortOrder,
  p.label         AS parentLabel
FROM nav_items n
LEFT JOIN nav_items p ON p.id = n.parent_id
WHERE n.is_active = 1
  AND (p.id IS NULL OR p.is_active = 1)
ORDER BY n.location, COALESCE(n.parent_id, n.id), n.sort_order;

-- Footer columns: top-level footer items are column headings, children links.
CREATE OR REPLACE VIEW vw_footer_columns AS
SELECT
  parent.label     AS columnTitle,
  parent.sort_order AS columnOrder,
  child.label      AS linkLabel,
  child.href       AS linkHref,
  child.sort_order AS linkOrder
FROM nav_items parent
JOIN nav_items child ON child.parent_id = parent.id
WHERE parent.location = 'footer'
  AND parent.parent_id IS NULL
  AND parent.is_active = 1
  AND child.is_active = 1
ORDER BY parent.sort_order, child.sort_order;

-- Care services, published only, with highlights pre-aggregated.
CREATE OR REPLACE VIEW vw_care_services_public AS
SELECT
  cs.uuid              AS uuid,
  cs.slug              AS slug,
  cs.title             AS title,
  cs.short_description AS shortDescription,
  cs.hero_image        AS heroImage,
  cs.body_html          AS bodyHtml,
  cs.sort_order        AS `order`,
  CAST(cs.featured_on_home AS JSON) AS isFeaturedOnHome,
  cs.meta_title        AS metaTitle,
  cs.meta_description  AS metaDescription,
  COALESCE((
    SELECT JSON_ARRAYAGG(h.label)
      FROM (SELECT label
              FROM care_service_highlights
             WHERE service_id = cs.id
             ORDER BY sort_order) AS h
  ), JSON_ARRAY()) AS highlights
FROM care_services cs
WHERE cs.status = 'published'
  AND cs.deleted_at IS NULL
ORDER BY cs.sort_order;

-- Blog feed + detail. Same view serves both; the API filters by slug.
CREATE OR REPLACE VIEW vw_blog_posts_public AS
SELECT
  bp.uuid         AS uuid,
  bp.slug         AS slug,
  bp.title        AS title,
  bp.excerpt      AS excerpt,
  bp.cover_image  AS coverImage,
  bp.body_html    AS bodyHtml,
  bp.author       AS author,
  bp.published_at AS publishedAt,
  bp.meta_title   AS metaTitle,
  bp.meta_description AS metaDescription,
  COALESCE((
    SELECT JSON_ARRAYAGG(t.slug)
      FROM blog_post_tags bpt
      JOIN tags t ON t.id = bpt.tag_id
     WHERE bpt.post_id = bp.id
  ), JSON_ARRAY()) AS tags
FROM blog_posts bp
WHERE bp.status = 'published'
  AND bp.deleted_at IS NULL
  AND (bp.published_at IS NULL OR bp.published_at <= CURDATE())
ORDER BY bp.published_at DESC;

CREATE OR REPLACE VIEW vw_products_public AS
SELECT
  p.uuid         AS uuid,
  p.slug         AS slug,
  p.name         AS name,
  p.description  AS description,
  p.image        AS image,
  p.price        AS price,
  p.currency     AS currency,
  p.external_url AS externalUrl
FROM products p
WHERE p.status = 'published'
  AND p.deleted_at IS NULL
ORDER BY p.sort_order;

CREATE OR REPLACE VIEW vw_testimonials_public AS
SELECT
  t.uuid        AS uuid,
  t.author_name AS authorName,
  t.rating      AS rating,
  t.quote       AS quote
FROM testimonials t
WHERE t.status = 'published'
  AND t.deleted_at IS NULL
ORDER BY t.sort_order;

CREATE OR REPLACE VIEW vw_core_values_public AS
SELECT icon, title, description
FROM core_values
WHERE is_active = 1
ORDER BY sort_order;

CREATE OR REPLACE VIEW vw_about_stats_public AS
SELECT icon, title
FROM about_stats
WHERE is_active = 1
ORDER BY sort_order;

CREATE OR REPLACE VIEW vw_job_openings_public AS
SELECT
  j.uuid            AS uuid,
  j.slug            AS slug,
  j.title           AS title,
  j.employment_type AS type,
  j.location        AS location,
  j.summary         AS summary
FROM job_openings j
WHERE j.status = 'published'
  AND j.deleted_at IS NULL
ORDER BY j.sort_order;

-- ---------- Internal / admin views (not exposed publicly) ----------
CREATE OR REPLACE VIEW vw_contact_submissions_admin AS
SELECT
  c.uuid       AS uuid,
  c.first_name AS firstName,
  c.last_name  AS lastName,
  c.email      AS email,
  c.phone      AS phone,
  c.message    AS message,
  c.attachment_path AS attachmentPath,
  c.source_page AS sourcePage,
  c.status     AS status,
  c.created_at AS createdAt
FROM contact_submissions c
ORDER BY c.created_at DESC;

CREATE OR REPLACE VIEW vw_sms_consents_admin AS
SELECT
  s.uuid          AS uuid,
  s.first_name    AS firstName,
  s.last_name     AS lastName,
  s.email         AS email,
  s.phone         AS phone,
  s.consent_given AS consentGiven,
  s.created_at    AS createdAt,
  s.revoked_at    AS revokedAt
FROM sms_consents s
ORDER BY s.created_at DESC;