ai_ctxe_uniform_resource_risk_view

CREATE VIEW ai_ctxe_uniform_resource_risk_view AS
SELECT
    -- Count of resources with null or empty frontmatter
    (
        SELECT COUNT(*)
        FROM uniform_resource
        WHERE deleted_at IS NULL
          AND (frontmatter IS NULL OR LENGTH(TRIM(frontmatter)) = 0)
    ) AS count_empty_frontmatter,

    -- Count of grouped resources where order is null or count > 1
    (
        SELECT COUNT(*)
        FROM (
            SELECT 
                json_extract(frontmatter, '$.merge-group') AS mg,
                json_extract(frontmatter, '$.order') AS ord,
                COUNT(*) AS ct
            FROM uniform_resource
            WHERE deleted_at IS NULL
              AND frontmatter IS NOT NULL
              AND json_extract(frontmatter, '$.merge-group') IS NOT NULL
            GROUP BY mg, ord
            HAVING ord IS NULL OR ct > 1
        ) AS grouped_resources
    ) AS count_grouped_resources,

    -- Count of files over 1MB linked to non-deleted resources
    (
        SELECT COUNT(*)
        FROM uniform_resource_file urf
        JOIN uniform_resource ur ON urf.uniform_resource_id = ur.uniform_resource_id
        WHERE ur.deleted_at IS NULL
          AND urf.size_bytes > 1048576
    ) AS count_large_files