Archetype: sql_flag_populate.mustache

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_populate.org. Edit the org source. }}
{{! Template to generate SQL with enhanced GPL v3 license and flag SVG images data }}
{{{sql_license}}}
/*
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_flag_populate.mustache
 * To modify, update the template and regenerate.
 */

-- Script to populate DQ SVG images into the database
--

DO $$
declare
    v_dataset_id uuid;
begin
    -- Get the dataset ID using (name, subject_area_name, domain_name)
    select id into v_dataset_id
    from ores_dq_datasets_tbl
    where name = 'Solvaris Country Flag Images'
      and subject_area_name = 'Country Flags'
      and domain_name = 'Reference Data'
      and valid_to = ores_utility_infinity_timestamp_fn();

    if v_dataset_id is null then
        raise exception 'Dataset not found: name="Solvaris Country Flag Images", subject_area="Country Flags", domain="Reference Data"';
    end if;

    -- Clear existing images for this dataset (idempotency)
    delete from ores_dq_images_artefact_tbl
    where dataset_id = v_dataset_id;

    raise notice 'Populating images for dataset: %', 'Solvaris Country Flag Images';

    -- Insert images
{{#country_currency}}
    insert into ores_dq_images_artefact_tbl (
        dataset_id, tenant_id, image_id, version, key, description, svg_data
    ) values (
        v_dataset_id, ores_utility_system_tenant_id_fn(), gen_random_uuid(), 0, '{{country_code_lower}}', 'Flag of {{country_code_lower}}', $svg${{{generated_svg}}}$svg$
    );
{{/country_currency}}

end $$;

-- =============================================================================
-- Summary
-- =============================================================================

\echo ''
\echo '--- Summary ---'

select 'Data Quality Total Images' as entity, count(*) as count
from ores_dq_images_artefact_tbl where dataset_id in (
    select id from ores_dq_datasets_tbl
    where name = 'Solvaris Country Flag Images'
      and subject_area_name = 'Country Flags'
      and domain_name = 'Reference Data'
  )
order by entity;