tem/tenant/openssl.sql

              SELECT 'dynamic' AS component, sqlpage.run_sql('shell/shell.sql') AS properties;
              -- not including breadcrumbs from sqlpage_aide_navigation
              -- 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 = 'tem/tenant/openssl.sql/index.sql') as contents;
    ;

  --- Breadcrumb setup
  SELECT 'breadcrumb' AS component;
      SELECT 'Home' AS title,
          sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' AS link;
      SELECT 'Threat Exposure Management' AS title,
          sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/index.sql' AS link;
      SELECT 'Attack Surface Mapping By Tenant' AS title,
          sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/attack_surface_mapping_tenant.sql' AS link;
      SELECT tenant_name AS title,
          sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/tenant/attack_surface_mapping_inner.sql?tenant_id=' || $tenant_id AS link
      FROM tem_tenant WHERE tenant_id = $tenant_id;
      SELECT 'Findings' AS title,
          sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/tem/tenant/finding.sql?tenant_id=' || $tenant_id AS link;
      SELECT 'SSL/TLS Certificate Metadata' AS title,
          '#' AS link;

  --- Page title
  SELECT 'title' AS component,
      'SSL/TLS Certificate Metadata' AS contents;

  --- Page description
  SELECT 'text' AS component,
      'This page displays structured SSL/TLS certificate details extracted from OpenSSL output. 
      Each row represents a certificate discovered within the tenant’s infrastructure, showing subject details, issuer information, and validity periods. 
      These insights help assess certificate ownership, identify expired or weakly issued certificates, and strengthen the tenant’s security posture.' AS contents;

  --- Table setup
  SELECT 'table' AS component,
      TRUE AS sort,
      TRUE AS search;

  SET total_rows = (SELECT COUNT(*) FROM tem_openssl WHERE tenant_id = $tenant_id);
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
    CASE WHEN common_name IS NULL OR trim(common_name) = '' THEN '-' ELSE common_name END AS "Common Name",
    CASE WHEN subject_organization IS NULL OR trim(subject_organization) = '' THEN '-' ELSE subject_organization END AS "Subject Organization",
    CASE WHEN issuer_common_name IS NULL OR trim(issuer_common_name) = '' THEN '-' ELSE issuer_common_name END AS "Issuer CN",
    CASE WHEN issuer_organization IS NULL OR trim(issuer_organization) = '' THEN '-' ELSE issuer_organization END AS "Issuer Organization",
    CASE WHEN issuer_country IS NULL OR trim(issuer_country) = '' THEN '-' ELSE issuer_country END AS "Issuer Country",

    -- Issued Date
    CASE
        WHEN issued_date IS NULL OR trim(issued_date) = '' THEN '-'
        ELSE printf(
                '%s %s %s',
                trim(substr(replace(replace(issued_date, '  ', ' '), '  ', ' '), 1, 3)),
                trim(substr(replace(replace(issued_date, '  ', ' '), '  ', ' '), 5, 2)),
                trim(substr(replace(replace(issued_date, '  ', ' '), '  ', ' '), -9, 4))
            )
    END AS "Issued Date",

    -- Expires Date
    CASE
        WHEN expires_date IS NULL OR trim(expires_date) = '' THEN '-'
        ELSE printf(
                '%s %s %s',
                trim(substr(replace(replace(expires_date, '  ', ' '), '  ', ' '), 1, 3)),
                trim(substr(replace(replace(expires_date, '  ', ' '), '  ', ' '), 5, 2)),
                trim(substr(replace(replace(expires_date, '  ', ' '), '  ', ' '), -9, 4))
            )
    END AS "Expires Date"

FROM tem_openssl
WHERE tenant_id = $tenant_id;

  SELECT 'text' AS component,
    (SELECT CASE WHEN CAST($current_page AS INTEGER) > 1 THEN '[Previous](?limit=' || $limit || '&offset=' || ($offset - $limit) || COALESCE('&tenant_id=' || replace($tenant_id, ' ', '%20'), '') || ')' 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) || COALESCE('&tenant_id=' || replace($tenant_id, ' ', '%20'), '') || ')' ELSE '' END)
    AS contents_md
;
        ;