-- 02_insert_pays_ref_nomenclature_items.sql
-- Insertion de pays dans public.ref_nomenclature_items
-- Colonnes autorisées: (nomenclature_fk, nomenclature_item, nomenclature_item_order, nomenclature_item_code)
-- Idempotent via WHERE NOT EXISTS

BEGIN;

INSERT INTO public.ref_nomenclature_items
  (nomenclature_fk, nomenclature_item, nomenclature_item_order, nomenclature_item_code)
SELECT v.nomenclature_fk, v.nomenclature_item, v.nomenclature_item_order, v.nomenclature_item_code
FROM (
  VALUES
    ('PAYS','Algeria',0,'DZ'),
    ('PAYS','Afghanistan',1,'AF'),
    ('PAYS','Albania',2,'AL'),
    ('PAYS','Andorra',4,'AD'),
    ('PAYS','Angola',5,'AO'),
    ('PAYS','Antigua and Barbuda',6,'AG'),
    ('PAYS','Argentina',7,'AR'),
    ('PAYS','Armenia',8,'AM'),
    ('PAYS','Australia',9,'AU'),
    ('PAYS','Austria',10,'AT'),
    ('PAYS','Azerbaijan',11,'AZ'),
    ('PAYS','Bahamas',12,'BS'),
    ('PAYS','Bahrain',13,'BH'),
    ('PAYS','Bangladesh',14,'BD'),
    ('PAYS','Barbados',15,'BB'),
    ('PAYS','Belarus',16,'BY'),
    ('PAYS','Belgium',17,'BE'),
    ('PAYS','Belize',18,'BZ'),
    ('PAYS','Benin',19,'BJ'),
    ('PAYS','Bhutan',20,'BT'),
    ('PAYS','Bolivia',21,'BO'),
    ('PAYS','Bosnia and Herzegovina',22,'BA'),
    ('PAYS','Botswana',23,'BW'),
    ('PAYS','Brazil',24,'BR'),
    ('PAYS','Brunei',25,'BN'),
    ('PAYS','Bulgaria',26,'BG'),
    ('PAYS','Burkina Faso',27,'BF'),
    ('PAYS','Burundi',28,'BI'),
    ('PAYS','Cabo Verde',29,'CV'),
    ('PAYS','Cambodia',30,'KH'),
    ('PAYS','Cameroon',31,'CM'),
    ('PAYS','Canada',32,'CA'),
    ('PAYS','Central African Republic',33,'CF'),
    ('PAYS','Chad',34,'TD'),
    ('PAYS','Chile',35,'CL'),
    ('PAYS','China',36,'CN'),
    ('PAYS','Colombia',37,'CO'),
    ('PAYS','Comoros',38,'KM'),
    ('PAYS','Congo (Congo-Brazzaville)',39,'CG'),
    ('PAYS','Congo (DRC)',40,'CD'),
    ('PAYS','Costa Rica',41,'CR'),
    ('PAYS','Croatia',42,'HR'),
    ('PAYS','Cuba',43,'CU'),
    ('PAYS','Cyprus',44,'CY'),
    ('PAYS','Czechia (Czech Republic)',45,'CZ'),
    ('PAYS','Denmark',47,'DK'),
    ('PAYS','Djibouti',48,'DJ'),
    ('PAYS','Dominica',49,'DM'),
    ('PAYS','Dominican Republic',50,'DO'),
    ('PAYS','Ecuador',51,'EC'),
    ('PAYS','Egypt',52,'EG'),
    ('PAYS','El Salvador',53,'SV'),
    ('PAYS','Equatorial Guinea',54,'GQ'),
    ('PAYS','Eritrea',55,'ER'),
    ('PAYS','Estonia',56,'EE'),
    ('PAYS','Eswatini (formerly Swaziland)',57,'SZ'),
    ('PAYS','Ethiopia',58,'ET'),
    ('PAYS','Fiji',59,'FJ'),
    ('PAYS','Finland',60,'FI'),
    ('PAYS','France',61,'FR'),
    ('PAYS','Gabon',62,'GA'),
    ('PAYS','Gambia',63,'GM'),
    ('PAYS','Georgia',64,'GE'),
    ('PAYS','Germany',65,'DE'),
    ('PAYS','Ghana',66,'GH'),
    ('PAYS','Greece',67,'GR'),
    ('PAYS','Grenada',68,'GD'),
    ('PAYS','Guatemala',69,'GT'),
    ('PAYS','Guinea',70,'GN'),
    ('PAYS','Guinea-Bissau',71,'GW'),
    ('PAYS','Guyana',72,'GY'),
    ('PAYS','Haiti',73,'HT'),
    ('PAYS','Honduras',74,'HN'),
    ('PAYS','Hungary',75,'HU'),
    ('PAYS','Iceland',76,'IS'),
    ('PAYS','India',77,'IN'),
    ('PAYS','Indonesia',78,'ID'),
    ('PAYS','Iran',79,'IR'),
    ('PAYS','Iraq',80,'IQ'),
    ('PAYS','Ireland',81,'IE'),
    ('PAYS','Israel',82,'IL'),
    ('PAYS','Italy',83,'IT'),
    ('PAYS','Jamaica',84,'JM'),
    ('PAYS','Japan',85,'JP'),
    ('PAYS','Jordan',86,'JO'),
    ('PAYS','Kazakhstan',87,'KZ'),
    ('PAYS','Kenya',88,'KE'),
    ('PAYS','Kiribati',89,'KI'),
    ('PAYS','Kuwait',90,'KW'),
    ('PAYS','Kyrgyzstan',91,'KG'),
    ('PAYS','Laos',92,'LA'),
    ('PAYS','Latvia',93,'LV'),
    ('PAYS','Lebanon',94,'LB'),
    ('PAYS','Lesotho',95,'LS'),
    ('PAYS','Liberia',96,'LR'),
    ('PAYS','Libya',97,'LY'),
    ('PAYS','Liechtenstein',98,'LI'),
    ('PAYS','Lithuania',99,'LT'),
    ('PAYS','Luxembourg',100,'LU'),
    ('PAYS','Madagascar',101,'MG'),
    ('PAYS','Malawi',102,'MW'),
    ('PAYS','Malaysia',103,'MY'),
    ('PAYS','Maldives',104,'MV'),
    ('PAYS','Mali',105,'ML'),
    ('PAYS','Malta',106,'MT'),
    ('PAYS','Marshall Islands',107,'MH'),
    ('PAYS','Mauritania',108,'MR'),
    ('PAYS','Mauritius',109,'MU'),
    ('PAYS','Mexico',110,'MX'),
    ('PAYS','Micronesia',111,'FM'),
    ('PAYS','Moldova',112,'MD'),
    ('PAYS','Monaco',113,'MC'),
    ('PAYS','Mongolia',114,'MN'),
    ('PAYS','Montenegro',115,'ME'),
    ('PAYS','Morocco',116,'MA'),
    ('PAYS','Mozambique',117,'MZ'),
    ('PAYS','Myanmar (formerly Burma)',118,'MM'),
    ('PAYS','Namibia',119,'NA'),
    ('PAYS','Nauru',120,'NR'),
    ('PAYS','Nicaragua',121,'NI'),
    ('PAYS','Niger',122,'NE'),
    ('PAYS','Nigeria',123,'NG'),
    ('PAYS','North Korea',124,'KP'),
    ('PAYS','Norway',126,'NO'),
    ('PAYS','Oman',127,'OM'),
    ('PAYS','Pakistan',128,'PK'),
    ('PAYS','Palau',129,'PW'),
    ('PAYS','Palestine State',130,'PS'),
    ('PAYS','Panama',131,'PA'),
    ('PAYS','Papua New Guinea',132,'PG'),
    ('PAYS','Paraguay',133,'PY'),
    ('PAYS','Peru',134,'PE'),
    ('PAYS','Philippines',135,'PH'),
    ('PAYS','Poland',136,'PL'),
    ('PAYS','Portugal',137,'PT'),
    ('PAYS','Qatar',138,'QA'),
    ('PAYS','Romania',139,'RO'),
    ('PAYS','Russia',140,'RU'),
    ('PAYS','Rwanda',141,'RW'),
    ('PAYS','Saint Kitts and Nevis',142,'KN'),
    ('PAYS','Saint Lucia',143,'LC'),
    ('PAYS','Saint Vincent and the Grenadines',144,'VC'),
    ('PAYS','Samoa',145,'WS'),
    ('PAYS','San Marino',146,'SM'),
    ('PAYS','Sao Tome and Principe',147,'ST'),
    ('PAYS','Saudi Arabia',148,'SA'),
    ('PAYS','Senegal',149,'SN'),
    ('PAYS','Serbia',150,'RS'),
    ('PAYS','Seychelles',151,'SC'),
    ('PAYS','Sierra Leone',152,'SL'),
    ('PAYS','Singapore',153,'SG'),
    ('PAYS','Slovakia',154,'SK'),
    ('PAYS','Slovenia',155,'SI'),
    ('PAYS','Solomon Islands',156,'SB'),
    ('PAYS','Somalia',157,'SO'),
    ('PAYS','South Africa',158,'ZA'),
    ('PAYS','South Korea',159,'KR'),
    ('PAYS','South Sudan',160,'SS'),
    ('PAYS','Spain',161,'ES'),
    ('PAYS','Sri Lanka',162,'LK'),
    ('PAYS','Sudan',163,'SD'),
    ('PAYS','Suriname',164,'SR'),
    ('PAYS','Sweden',165,'SE'),
    ('PAYS','Switzerland',166,'CH'),
    ('PAYS','Syria',167,'SY'),
    ('PAYS','Taiwan',168,'TW'),
    ('PAYS','Tajikistan',169,'TJ'),
    ('PAYS','Tanzania',170,'TZ'),
    ('PAYS','Thailand',171,'TH'),
    ('PAYS','Timor-Leste',172,'TL'),
    ('PAYS','Togo',173,'TG'),
    ('PAYS','Tonga',174,'TO'),
    ('PAYS','Trinidad and Tobago',175,'TT'),
    ('PAYS','Tunisia',176,'TN'),
    ('PAYS','Turkey',177,'TR'),
    ('PAYS','Turkmenistan',178,'TM'),
    ('PAYS','Tuvalu',179,'TV'),
    ('PAYS','Uganda',180,'UG'),
    ('PAYS','Ukraine',181,'UA'),
    ('PAYS','United Arab Emirates',182,'AE'),
    ('PAYS','United Kingdom',183,'GB'),
    ('PAYS','United States',184,'US'),
    ('PAYS','Uruguay',185,'UY'),
    ('PAYS','Uzbekistan',186,'UZ'),
    ('PAYS','Vanuatu',187,'VU'),
    ('PAYS','Venezuela',188,'VE'),
    ('PAYS','Vietnam',189,'VN'),
    ('PAYS','Yemen',190,'YE'),
    ('PAYS','Zambia',191,'ZM'),
    ('PAYS','Zimbabwe',192,'ZW')
) AS v(nomenclature_fk, nomenclature_item, nomenclature_item_order, nomenclature_item_code)
WHERE NOT EXISTS (
  SELECT 1
  FROM public.ref_nomenclature_items r
  WHERE r.nomenclature_fk = v.nomenclature_fk
    AND r.nomenclature_item_code = v.nomenclature_item_code
);

COMMIT;