ai_ctxe_uniform_resource_transformed_resources_cleaned

CREATE VIEW ai_ctxe_uniform_resource_transformed_resources_cleaned AS
SELECT
    ur.uniform_resource_id,
    ur.uri,
    ur.nature,
     fs.file_basename as filename,
    -- Remove frontmatter from content
    TRIM(
        CASE
            WHEN instr(urt.content, '---') = 1 THEN substr(
                urt.content,
                instr(ur.content, '---') + 3 + instr(substr(urt.content, instr(urt.content, '---') + 3), '---') + 3
            )
            ELSE urt.content
        END
    ) AS body_content,
    json_extract(urt.elaboration, '$.validation.status') AS validation_status,
    json_extract(urt.elaboration, '$.warnings') AS warnings
FROM uniform_resource ur
JOIN uniform_resource_transform urt
  ON ur.uniform_resource_id = urt.uniform_resource_id
  JOIN
  ur_ingest_session_fs_path_entry fs
  ON fs.uniform_resource_id = ur.uniform_resource_id AND fs.uniform_resource_id=urt.uniform_resource_id
WHERE ur.deleted_at IS NULL
  AND (
      json_extract(urt.elaboration, '$.validation.status') IS NULL
      OR json_extract(urt.elaboration, '$.validation.status') != 'success'
  )