qualityfolio/related_requirement_detail.sql

              SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
              SELECT 'breadcrumb' as component;
WITH RECURSIVE breadcrumbs AS (
    SELECT
        COALESCE(abbreviated_caption, caption) AS title,
        COALESCE(url, path) AS link,
        parent_path, 0 AS level,
        namespace
    FROM sqlpage_aide_navigation
    WHERE namespace = 'prime' AND path='qualityfolio/related_requirement_detail.sql'
    UNION ALL
    SELECT
        COALESCE(nav.abbreviated_caption, nav.caption) AS title,
        COALESCE(nav.url, nav.path) AS link,
        nav.parent_path, b.level + 1, nav.namespace
    FROM sqlpage_aide_navigation nav
    INNER JOIN breadcrumbs b ON nav.namespace = b.namespace AND nav.path = b.parent_path
)
SELECT title ,      
sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/'||link as link        
FROM breadcrumbs ORDER BY level DESC;
              -- not including page title from sqlpage_aide_navigation
              

                SELECT 'title' AS component, (SELECT COALESCE(title, caption)
    FROM sqlpage_aide_navigation
   WHERE namespace = 'prime' AND path = 'qualityfolio/related_requirement_detail.sql/index.sql') as contents;
    ;

  --- Breadcrumbs
  SELECT 'breadcrumb' AS component;
  SELECT 'Home' AS title, sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' AS link;
  SELECT 'QualityFolio' AS title, sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/index.sql' AS link;
  SELECT (SELECT COALESCE(title, requirement_id) FROM requirement WHERE requirement_id = $requirement) AS title, '#' AS link;

  --- Card Header with Requirement Title
  -- SELECT 'card' AS component,
  --   1 AS columns;

    SELECT 'title'AS component,
   (SELECT page_title FROM page_guide WHERE page_identifier = 'qualityfolio/related_requirement_detail.sql' AND page_order = 1) as contents;

    SELECT 'text' as component,
      (SELECT page_content FROM page_guide WHERE page_identifier = 'qualityfolio/related_requirement_detail.sql' AND page_order = 1) as contents;

  -- Render requirement header and metadata as a styled HTML block (no table)
SELECT 'html' AS component,
    COALESCE(
      '<section class="requirement-hero" style="background:linear-gradient(90deg,#ffffff,#f7f9fc);padding:22px;border-radius:10px;margin-bottom:18px;border:1px solid #eceff3;">' ||
      '<div style="display:flex;gap:20px;align-items:flex-start">' ||
      '<div style="flex:1">' ||
      '<h1 style="margin:0 0 8px 0;font-size:24px;font-weight:700;color:#1f2937">' || COALESCE(title, requirement_id) || '</h1>' ||
      '<div style="color:#6b7280;font-size:13px">' ||
      COALESCE(created_by, '') ||
      CASE WHEN created_by IS NOT NULL AND created_at IS NOT NULL THEN ' • ' ELSE '' END ||
      CASE WHEN created_at IS NOT NULL THEN strftime('%d-%m-%Y', created_at) ELSE '' END ||
      CASE WHEN version IS NOT NULL THEN ' • v' || version ELSE '' END ||
      CASE WHEN type IS NOT NULL THEN ' • ' || type ELSE '' END ||
      '</div></br>' ||
      '<p style="margin:0 0 12px 0;color:#374151;line-height:1.5">' || COALESCE(description, 'No description') || '</p>' ||
      '</div>' ||
      '<div style="min-width:160px;text-align:right">' ||
      '<a href="' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/qualityfolio/test_case_related_requirements.sql?requirement=' || REPLACE(REPLACE(requirement_id, ' ', '%20'), '&', '%26') || '" style="display:inline-block;background:#eef2ff;color:#3730a3;padding:8px 12px;border-radius:6px;text-decoration:none;font-weight:600">View related test cases</a>' ||
      '</div>' ||
      '</div>' ||
      '</section>',
      '<div class="requirement-detail"><p>No requirement found</p></div>'
    ) AS html
  FROM requirement WHERE requirement_id = $requirement;
  
  -- Return content with YAML frontmatter removed (strip leading '--- ... ---' block)
  SELECT 'text' AS component,
    TRIM(
      CASE
        WHEN instr(content, '---') = 0 THEN content
        ELSE
          -- find the position after the second '---'
          CASE
            WHEN instr(substr(content, instr(content, '---') + 3), '---') = 0 THEN
              -- only one '---' found; return remainder after first occurrence
              ltrim(substr(content, instr(content, '---') + 3))
            ELSE
              ltrim(
                substr(
                  content,
                  instr(substr(content, instr(content, '---') + 3), '---') + instr(content, '---') + 5
                )
              )
          END
      END
    ) AS contents_md
  FROM requirement
  WHERE requirement_id = $requirement;