Archetype: sql_non_iso_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_non_iso_currency_populate.mustache
 * To modify, update the template and regenerate.
 *
 * FpML Non-ISO Currencies Artefact Population Script
 *
 * Populates the dq_currencies_artefact_tbl with FpML non-ISO currency data.
 * Dataset: {{dataset.code}}
 * Source version: {{dataset.source_version}}
 *
 * These currencies extend ISO 4217 for derivatives trading:
 * - Offshore currencies: CNH (Hong Kong), CNT (Taiwan)
 * - Crown Dependencies: GGP (Guernsey), IMP (Isle of Man), JEP (Jersey)
 * - Pacific islands: KID (Kiribati), TVD (Tuvalu)
 * - Historical: MCF (Monaco), SML (San Marino), VAL (Vatican)
 *
 * Note: Currency metadata (symbol, fractions, etc.) is enriched from
 * hardcoded values in fpml_parser.py since FPML only provides code/description.
 *
 * 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 FpML Non-ISO Currencies dataset ID
    select id into v_dataset_id
    from ores_dq_datasets_tbl
    where code = '{{dataset.code}}'
      and valid_to = ores_utility_infinity_timestamp_fn();

    if v_dataset_id is null then
        raise exception 'Dataset not found: {{dataset.code}}. Run dataset population first.';
    end if;

    -- Get the flags dataset ID (for linking images)
    select id into v_flags_dataset_id
    from ores_dq_datasets_tbl
    where code = 'assets.country_flags'
      and valid_to = ores_utility_infinity_timestamp_fn();

    if v_flags_dataset_id is null then
        raise exception 'Dataset not found: assets.country_flags';
    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: {{dataset.name}}';

    -- Insert FpML non-ISO 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,
        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
{{#items}}
        ('{{code}}', '{{{name}}}', '{{numeric_code}}', '{{{symbol}}}', '{{fraction_symbol}}', {{fractions_per_unit}}, '{{rounding_type}}', {{rounding_precision}}, '{{format}}', '{{asset_class}}', '{{market_tier}}', '{{flag_key}}'){{^last}},{{/last}}
{{/items}}
    ) 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 % non-ISO currencies', v_count;
end $$;

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

\echo ''
\echo '--- FpML Non-ISO Currencies Summary ---'

select 'Total FpML Non-ISO Currencies' as metric, count(*) as count
from ores_dq_currencies_artefact_tbl c
join ores_dq_datasets_tbl d on c.dataset_id = d.id
where d.code = '{{dataset.code}}'
  and d.valid_to = ores_utility_infinity_timestamp_fn()
union all
select 'Fiat (asset_class=fiat)', count(*)
from ores_dq_currencies_artefact_tbl c
join ores_dq_datasets_tbl d on c.dataset_id = d.id
where d.code = '{{dataset.code}}'
  and d.valid_to = ores_utility_infinity_timestamp_fn()
  and c.asset_class = 'fiat'
union all
select 'Historical (market_tier=historical)', count(*)
from ores_dq_currencies_artefact_tbl c
join ores_dq_datasets_tbl d on c.dataset_id = d.id
where d.code = '{{dataset.code}}'
  and d.valid_to = ores_utility_infinity_timestamp_fn()
  and c.market_tier = 'historical';