ai_ctxe_uniform_resource_with_frontmatter

CREATE VIEW ai_ctxe_uniform_resource_with_frontmatter AS

SELECT 
    ur.uniform_resource_id,
    ur.uri,
   
    
    -- Extract filename from file_path_rel
    CASE 
        WHEN urf.file_path_rel LIKE '%/%' THEN 
            substr(urf.file_path_rel, length(rtrim(urf.file_path_rel, replace(urf.file_path_rel, '/', ''))) + 1)
        ELSE 
            urf.file_path_rel
    END AS filename,
    
    ur.created_at,
    ur.created_by,
    ur.content,
    ur.frontmatter,
    
    -- Extract title and summary from frontmatter JSON
    json_extract(ur.frontmatter, '$.title') AS title,
    json_extract(ur.frontmatter, '$.summary') AS summary,

    -- content with frontmatter stripped
    TRIM(
    CASE
      WHEN instr(ur.content, '---') = 1
        THEN substr(
          ur.content,
          instr(ur.content, '---') + 3 + instr(substr(ur.content, instr(ur.content, '---') + 3), '---') + 3
        )
      ELSE ur.content
    END
  ) AS body_text,

    -- Additional useful fields from uniform_resource_file
    urf.nature,
    urf.source_path,
    urf.file_path_rel,
    urf.size_bytes

FROM uniform_resource ur
LEFT JOIN uniform_resource_file urf 
    ON ur.uniform_resource_id = urf.uniform_resource_id
WHERE deleted_at IS NULL
        AND frontmatter IS NOT NULL AND LENGTH(TRIM(frontmatter)) > 0