tem/index.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='tem/index.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
              

              -- Intro text
SELECT
    'text' AS component,
    'Opsfolio Threat Exposure Management (TEM) transforms static penetration test reports into real-time, actionable dashboards and workflows. Powered by Opsfolio EAA, it streamlines vulnerability reporting, automates remediation tracking, and delivers compliance-ready evidence to keep your organization secure and audit-ready.' AS contents;

-- Navigation list
WITH navigation_cte AS (
    SELECT COALESCE(title, caption) AS title, description
    FROM sqlpage_aide_navigation
    WHERE namespace = 'prime' AND path = 'tem'||'/index.sql'
)
SELECT 'list' AS component, title, description
FROM navigation_cte;

SELECT caption AS title, sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' || COALESCE(url, path) AS link, description
FROM sqlpage_aide_navigation
WHERE namespace = 'prime' AND parent_path = 'tem'||'/index.sql'
ORDER BY sibling_order;

--- Page Title for Tasks Section
SELECT 'title' AS component, 'Tasks Overview' AS contents;

--- Small description above the table
SELECT 'text' AS component,
'This table lists all tasks detected in the system, including their status and title. Click on a Task ID to view detailed content.' AS contents;

--- Tasks Table
SELECT 'table' AS component, TRUE AS sort, TRUE AS search, 'Title' AS markdown;

SET total_rows = (SELECT COUNT(*) FROM tem_task_summary WHERE uri LIKE '%task%');
SET limit = COALESCE($limit, 50);
SET offset = COALESCE($offset, 0);
SET total_pages = ($total_rows + $limit - 1) / $limit;
SET current_page = ($offset / $limit) + 1;
SELECT
'[' || title || '](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/task_detail.sql?task_id=' || uniform_resource_id || ')' AS "Title",
task_id AS "Task ID",
status AS "Status",
priority,
strftime('%m-%d-%Y', created_date) AS "created date",
strftime('%m-%d-%Y', updated_date) AS "updated date"
FROM tem_task_summary;

SELECT 'text' AS component,
    (SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || ')' ELSE '' END)
    || ' '
    || '(Page ' || $current_page || ' of ' || $total_pages || ") "
    || (SELECT CASE WHEN CAST($current_page AS INTEGER) < CAST($total_pages AS INTEGER) THEN '[Next](?limit=' || $limit || '&offset=' || ($offset + $limit) || ')' ELSE '' END)
    AS contents_md
;
        ;