Archetype: sql_currency_populate.mustache

{{! GENERATED FILE — tangled from projects/ores.codegen/library/templates/sql_populate.org. Edit the org source. }}
{{{sql_license}}}
/**
 * AUTO-GENERATED FILE - DO NOT EDIT MANUALLY
 * Template: sql_currency_populate.mustache
 * To modify, update the template and regenerate.
 *
 * Data Quality Solvaris Currencies Artefact Population Script
 *
 * Populates the dq_currencies_artefact_tbl with Solvaris currency data.
 * Source: country_currency.json model
 *
 * This script is idempotent.
 */


DO $$
declare
    v_dataset_id uuid;
    v_flags_dataset_id uuid;
    v_placeholder_image_id uuid;
    v_count integer := 0;
begin
    -- Get the currencies dataset ID from the datasets model
    select id into v_dataset_id
    from ores_dq_datasets_tbl
    where name = '{{currencies_dataset.name}}'
      and subject_area_name = 'Currencies'
      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 for currencies';
    end if;

    -- Get the flags dataset ID (for linking images)
    select id into v_flags_dataset_id
    from ores_dq_datasets_tbl
    where name = '{{flags_dataset.name}}'
      and subject_area_name = 'Country Flags'
      and domain_name = 'Reference Data'
      and valid_to = ores_utility_infinity_timestamp_fn();

    if v_flags_dataset_id is null then
        raise exception 'Dataset not found for flag images';
    end if;

    -- Get the placeholder image (xx.svg = "no flag available")
    select image_id into v_placeholder_image_id
    from ores_dq_images_artefact_tbl
    where dataset_id = v_flags_dataset_id
      and key = 'xx';

    if v_placeholder_image_id is null then
        raise warning 'Placeholder image (xx) not found - currencies without flags will have NULL image_id';
    end if;

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

    raise notice 'Populating currencies for dataset: %', (select name from ores_dq_datasets_tbl where id = v_dataset_id);

    -- Insert Solvaris currencies with flag image links
    insert into ores_dq_currencies_artefact_tbl (
        dataset_id, tenant_id, iso_code, version, name, numeric_code, symbol, fraction_symbol,
        fractions_per_unit, rounding_type, rounding_precision, format, asset_class, market_tier, image_id
    )
    select
        v_dataset_id,
        ores_utility_system_tenant_id_fn(),
        c.iso_code,
        0,
        c.name,
        c.numeric_code::text,
        c.symbol,
        c.fraction_symbol,
        c.fractions_per_unit,
        c.rounding_type,
        c.rounding_precision,
        c.format,
        c.asset_class,
        c.market_tier,
        coalesce(i.image_id, v_placeholder_image_id)
    from (values
{{#country_currency}}
        ('{{currency_code}}', '{{{currency}}}', {{currency_number}}, '{{{currency_symbol}}}', '{{fraction_symbol}}', {{fractions_per_unit}}, '{{rounding_type}}', {{rounding_precision}}, '{{format}}', '{{asset_class}}', '{{market_tier}}', '{{country_code_lower}}'){{^last}},{{/last}}
{{/country_currency}}
    ) as c(iso_code, name, numeric_code, symbol, fraction_symbol, fractions_per_unit, rounding_type, rounding_precision, format, asset_class, market_tier, flag_key)
    left join ores_dq_images_artefact_tbl i
        on i.dataset_id = v_flags_dataset_id
        and i.key = c.flag_key;

    get diagnostics v_count = row_count;

    raise notice 'Successfully populated % currencies for dataset: %', v_count, (select name from ores_dq_datasets_tbl where id = v_dataset_id);
end $$;

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

\echo ''
\echo '--- DQ Solvaris Currencies Summary ---'

with currencies as (
    select c.asset_class
    from ores_dq_currencies_artefact_tbl c
    join ores_dq_datasets_tbl d on c.dataset_id = d.id
    where d.subject_area_name = 'Currencies'
      and d.domain_name = 'Reference Data'
      and d.valid_to = ores_utility_infinity_timestamp_fn()
)
select 'Total Solvaris Currencies' as metric, count(*) as count from currencies
union all
select 'Fiat Currencies', count(*) from currencies where asset_class = 'fiat'
union all
select 'Synthetic Currencies', count(*) from currencies where asset_class = 'synthetic';