CREATE VIEW ai_ctxe_uniform_resource_frontmatter_view AS
SELECT
ur.uniform_resource_id,
ur.uri,
-- Extracting only important keys from the frontmatter column
json_extract(frontmatter, '$.id') AS frontmatter_id,
json_extract(frontmatter, '$.title') AS title,
json_extract(frontmatter, '$.summary') AS frontmatter_summary,
json_extract(frontmatter, '$.merge-group') AS frontmatter_merge_group,
json_extract(frontmatter, '$.artifact-nature') AS frontmatter_artifact_nature,
json_extract(frontmatter, '$.lifecycle') AS frontmatter_lifecycle,
json_extract(frontmatter, '$.visibility') AS frontmatter_visibility,
json_extract(frontmatter, '$.audience') AS frontmatter_audience,
json_extract(frontmatter, '$.function') AS frontmatter_function,
json_extract(frontmatter, '$.product.name') AS frontmatter_product_name,
-- Extracting features dynamically (up to the first 5 features)
trim(
json_extract(frontmatter, '$.product.features[0]') || ',' ||
json_extract(frontmatter, '$.product.features[1]') || ',' ||
json_extract(frontmatter, '$.product.features[2]') || ',' ||
json_extract(frontmatter, '$.product.features[3]') || ',' ||
json_extract(frontmatter, '$.product.features[4]')
) AS frontmatter_product_features,
json_extract(frontmatter, '$.provenance.source-uri') AS frontmatter_provenance_source_uri,
json_extract(frontmatter, '$.provenance.dependencies') AS frontmatter_provenance_dependencies,
-- Extracting reviewers dynamically (up to the first 5 reviewers)
trim(
json_extract(frontmatter, '$.provenance.reviewers[0]') || ',' ||
json_extract(frontmatter, '$.provenance.reviewers[1]')
) AS frontmatter_reviewers,
json_extract(urt.elaboration, '$.validation.status') AS validation_status,
json_extract(urt.elaboration, '$.warnings[0]') AS elaboration_warning
FROM uniform_resource ur
LEFT JOIN uniform_resource_transform urt
ON ur.uniform_resource_id = urt.uniform_resource_id