-- Loads the international systems prepared by tools/prepare_*.py (data/prepared/*.csv) into the database you are connected to,
-- where the extension is installed:
--
--    make prepare
--    psql -d yourdb -f tools/load_systems.sql
--
-- Run it again after a new version of a standard is prepared: an unchanged list changes nothing, a changed one becomes the next revision.
\set ON_ERROR_STOP on
CREATE TEMP TABLE stg (code text, level text, parent text, label text);

\copy stg FROM 'data/prepared/isic_rev4.csv' (FORMAT csv, HEADER)
SELECT define_code_system('isic_rev4',
   '[{"name":"section", "width":1,"charset":"[A-Z]","link":"none",    "expected":21},
     {"name":"division","width":2,"charset":"[0-9]","link":"grouping","expected":88},
     {"name":"group",   "width":3,"charset":"[0-9]","link":"prefix",  "expected":238},
     {"name":"class",   "width":4,"charset":"[0-9]","link":"prefix",  "expected":419}]',
   'SELECT * FROM stg', 'International Standard Industrial Classification of All Economic Activities, Rev.4', 'United Nations Statistics Division') AS isic_rev4_revision;

TRUNCATE stg;
\copy stg FROM 'data/prepared/isco_08.csv' (FORMAT csv, HEADER)
SELECT define_code_system('isco_08',
   '[{"name":"major",    "width":1,"charset":"[0-9]","link":"none",  "expected":10},
     {"name":"sub_major","width":2,"charset":"[0-9]","link":"prefix","expected":43},
     {"name":"minor",    "width":3,"charset":"[0-9]","link":"prefix","expected":130},
     {"name":"unit",     "width":4,"charset":"[0-9]","link":"prefix","expected":436}]',
   'SELECT * FROM stg', 'International Standard Classification of Occupations, ISCO-08', 'International Labour Organization') AS isco_08_revision;

TRUNCATE stg;
\copy stg FROM 'data/prepared/nace_rev2.csv' (FORMAT csv, HEADER)
-- NACE Rev.2 is derived from ISIC Rev.4: the same sections and divisions, its own groups and classes
SELECT define_code_system('nace_rev2',
   '[{"name":"section", "width":1,"charset":"[A-Z]","link":"none",    "expected":21},
     {"name":"division","width":2,"charset":"[0-9]","link":"grouping","expected":88},
     {"name":"group",   "width":3,"charset":"[0-9]","link":"prefix",  "expected":272},
     {"name":"class",   "width":4,"charset":"[0-9]","link":"prefix",  "expected":615}]',
   'SELECT * FROM stg', 'Statistical classification of economic activities in the European Community, Rev.2', 'Eurostat',
   refines => 'isic_rev4', shared_levels => 2) AS nace_rev2_revision;

SELECT name, current_revision, ncodes, levels FROM code_systems;