sq/missing-meta-information.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
      'breadcrumb' AS component;
    SELECT
      'Home' AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/' as link;
    SELECT
      'Website Resources' AS title,
      sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/sq' as link;

    SELECT
    'title' AS component,
    'Social Media and SEO Metadata Analysis for: '||$hostname::TEXT ||'' as contents,
    3 AS level;

    SELECT
    'title' AS component,
    ''||name||': '||description||'' as contents,
    5 AS level
    FROM site_quality_control;

    SELECT
      'card' AS component,
      '' AS title,
      2 AS columns;

    SELECT
      'Open Graph Missing Properties Overview' AS title,
      GROUP_CONCAT(
        '**' || property_name || ' missing URLs:** ' ||
        ' [ ' || missing_count || ' ](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/sq/missing-meta-information/details.sql?hostname=' || $hostname || '&property=' || property_name ||') ' || '  

',
        ''
      ) AS description_md,
      'tag' AS icon,
      'green' AS color
    FROM (
      SELECT
        property_name,
        COUNT(*) AS missing_count
      FROM uniform_resource_uri_missing_open_graph
      WHERE uri LIKE '%' || $hostname::TEXT || '%'
      GROUP BY property_name
    );

    SELECT
      'HTML Meta Missing Properties Overview' AS title,
      GROUP_CONCAT(
        '**' || property_name || ' missing URLs:** ' ||
        ' [ ' || missing_count || ' ](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/sq/missing-meta-information/details.sql?hostname=' || $hostname || '&property=' || property_name ||') ' || '  

',
        ''
      ) AS description_md,
      'tag' AS icon,
      'orange' AS color
    FROM (
      SELECT
        property_name,
        COUNT(*) AS missing_count
      FROM uniform_resource_uri_missing_html_meta_data
      WHERE uri LIKE '%' || $hostname::TEXT || '%'
      GROUP BY property_name
    );

    SELECT
      'Twitter Card Missing Properties Overview' AS title,
      GROUP_CONCAT(
        '**' || property_name || ' missing URLs:** ' ||
        ' [ ' || missing_count || ' ](' || sqlpage.environment_variable('SQLPAGE_SITE_PREFIX') || '/sq/missing-meta-information/details.sql?hostname=' || $hostname || '&property=' || property_name ||') ' || '  

',
        ''
      ) AS description_md,
      'tag' AS icon,
      'blue' AS color
    FROM (
      SELECT
        property_name,
        COUNT(*) AS missing_count
      FROM uniform_resource_uri_missing_twitter_card_cached
      WHERE uri LIKE '%' || $hostname::TEXT || '%'
      GROUP BY property_name
    );