-- Country Data Import SQL Script
-- Generated from Lastenheft_Requirements Appendix A_Länder 1.csv


-- ===================================================
-- INSERT a property set if not exists.
-- ===================================================

INSERT INTO property_set (state)
SELECT 'VALID'
WHERE NOT EXISTS (
    SELECT 1 FROM property_set ps
    WHERE ps.state = 'VALID'
      AND ps.start_date <= GETDATE()
      AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
);



-- =============================================================================
-- 1. INSERT COUNTRY PROPERTY TYPES
-- =============================================================================

INSERT INTO country_property_type
(name, external_mapping_id, data_type, validation_rule, is_required, description, property_group, sequence_number)
VALUES
    ('Customs Union', 'UNION', 'ENUMERATION', '{ "ENUM" : ["EU", "NONE"]}', 0, 'Specifies the customs union in which the country is located. When crossing a customs union border, customs costs are added to the calculation result.', 'General', 1),
    ('Safety Stock  [working days]', 'SAFETY_STOCK', 'INT', '{"GTE": 0}', 0, 'Specifies the safety stock in working days that is maintained when sourcing from this country.', 'General', 2),
    ('Air Freight Share  [%]', 'AIR_SHARE', 'PERCENTAGE', '{"GTE": 0}', 0, 'Specifies the maximum air freight proportion that is included in the calculation when sourcing from this country. The actual air freight proportion that is used additionally depends on the overseas share of the part number and lies between 0% and this value.', 'General', 3),
    ('Wage Factor [%]', 'WAGE', 'PERCENTAGE', '{"GT": 0}', 0, 'Specifies the wage factor level for calculating handling costs in relation to the German wage factor level.', 'General', 4);

-- =============================================================================
-- 2. INSERT COUNTRIES
-- =============================================================================

INSERT INTO country (iso_code, name, region_code, is_deprecated) VALUES
                                                                       ('AD', N'Andorra', 'EMEA', 0),
                                                                       ('AE', N'United Arab Emirates', 'EMEA', 0),
                                                                       ('AF', N'Afghanistan', 'EMEA', 0),
                                                                       ('AG', N'Antigua and Barbuda', 'LATAM', 0),
                                                                       ('AI', N'Anguilla', 'LATAM', 0),
                                                                       ('AL', N'Albania', 'EMEA', 0),
                                                                       ('AM', N'Armenia', 'EMEA', 0),
                                                                       ('AO', N'Angola', 'EMEA', 0),
                                                                       ('AQ', N'Antarctica', 'EMEA', 0),
                                                                       ('AR', N'Argentina', 'LATAM', 0),
                                                                       ('AS', N'American Samoa', 'APAC', 0),
                                                                       ('AT', N'Austria', 'EMEA', 0),
                                                                       ('AU', N'Australia', 'APAC', 0),
                                                                       ('AW', N'Aruba', 'LATAM', 0),
                                                                       ('AX', N'Åland Islands', 'EMEA', 0),
                                                                       ('AZ', N'Azerbaijan', 'EMEA', 0),
                                                                       ('BA', N'Bosnia and Herzegovina', 'EMEA', 0),
                                                                       ('BB', N'Barbados', 'LATAM', 0),
                                                                       ('BD', N'Bangladesh', 'EMEA', 0),
                                                                       ('BE', N'Belgium', 'EMEA', 0),
                                                                       ('BF', N'Burkina Faso', 'EMEA', 0),
                                                                       ('BG', N'Bulgaria', 'EMEA', 0),
                                                                       ('BH', N'Bahrain', 'EMEA', 0),
                                                                       ('BI', N'Burundi', 'EMEA', 0),
                                                                       ('BJ', N'Benin', 'EMEA', 0),
                                                                       ('BL', N'Saint Barthélemy', 'LATAM', 0),
                                                                       ('BM', N'Bermuda', 'NAM', 0),
                                                                       ('BN', N'Brunei Darussalam', 'APAC', 0),
                                                                       ('BO', N'Bolivia', 'LATAM', 0),
                                                                       ('BQ', N'Bonaire, Sint Eustatius and Saba', 'LATAM', 0),
                                                                       ('BR', N'Brazil', 'LATAM', 0),
                                                                       ('BS', N'Bahamas', 'LATAM', 0),
                                                                       ('BT', N'Bhutan', 'APAC', 0),
                                                                       ('BV', N'Bouvet Island', 'EMEA', 0),
                                                                       ('BW', N'Botswana', 'EMEA', 0),
                                                                       ('BY', N'Belarus', 'EMEA', 0),
                                                                       ('BZ', N'Belize', 'LATAM', 0),
                                                                       ('CA', N'Canada', 'NAM', 0),
                                                                       ('CC', N'Cocos (Keeling) Islands', 'APAC', 0),
                                                                       ('CD', N'Congo, Democratic Republic', 'EMEA', 0),
                                                                       ('CF', N'Central African Republic', 'EMEA', 0),
                                                                       ('CG', N'Congo', 'EMEA', 0),
                                                                       ('CH', N'Switzerland', 'EMEA', 0),
                                                                       ('CI', N'Côte d''Ivoire', 'EMEA', 0),
                                                                       ('CK', N'Cook Islands', 'APAC', 0),
                                                                       ('CL', N'Chile', 'LATAM', 0),
                                                                       ('CM', N'Cameroon', 'EMEA', 0),
                                                                       ('CN', N'China', 'APAC', 0),
                                                                       ('CO', N'Colombia', 'LATAM', 0),
                                                                       ('CR', N'Costa Rica', 'LATAM', 0),
                                                                       ('CU', N'Cuba', 'LATAM', 0),
                                                                       ('CV', N'Cabo Verde', 'EMEA', 0),
                                                                       ('CW', N'Curaçao', 'LATAM', 0),
                                                                       ('CX', N'Christmas Island', 'APAC', 0),
                                                                       ('CY', N'Cyprus', 'EMEA', 0),
                                                                       ('CZ', N'Czech Republic', 'EMEA', 0),
                                                                       ('DE', N'Germany', 'EMEA', 0),
                                                                       ('DJ', N'Djibouti', 'EMEA', 0),
                                                                       ('DK', N'Denmark', 'EMEA', 0),
                                                                       ('DM', N'Dominica', 'LATAM', 0),
                                                                       ('DO', N'Dominican Republic', 'LATAM', 0),
                                                                       ('DZ', N'Algeria', 'EMEA', 0),
                                                                       ('EC', N'Ecuador', 'LATAM', 0),
                                                                       ('EE', N'Estonia', 'EMEA', 0),
                                                                       ('EG', N'Egypt', 'EMEA', 0),
                                                                       ('EH', N'Western Sahara', 'EMEA', 0),
                                                                       ('ER', N'Eritrea', 'EMEA', 0),
                                                                       ('ES', N'Spain', 'EMEA', 0),
                                                                       ('ET', N'Ethiopia', 'EMEA', 0),
                                                                       ('FI', N'Finland', 'EMEA', 0),
                                                                       ('FJ', N'Fiji', 'APAC', 0),
                                                                       ('FK', N'Falkland Islands', 'LATAM', 0),
                                                                       ('FM', N'Micronesia', 'APAC', 0),
                                                                       ('FO', N'Faroe Islands', 'EMEA', 0),
                                                                       ('FR', N'France', 'EMEA', 0),
                                                                       ('GA', N'Gabon', 'EMEA', 0),
                                                                       ('GB', N'United Kingdom', 'EMEA', 0),
                                                                       ('GD', N'Grenada', 'LATAM', 0),
                                                                       ('GE', N'Georgia', 'EMEA', 0),
                                                                       ('GF', N'French Guiana', 'LATAM', 0),
                                                                       ('GG', N'Guernsey', 'EMEA', 0),
                                                                       ('GH', N'Ghana', 'EMEA', 0),
                                                                       ('GI', N'Gibraltar', 'EMEA', 0),
                                                                       ('GL', N'Greenland', 'NAM', 0),
                                                                       ('GM', N'Gambia', 'EMEA', 0),
                                                                       ('GN', N'Guinea', 'EMEA', 0),
                                                                       ('GP', N'Guadeloupe', 'LATAM', 0),
                                                                       ('GQ', N'Equatorial Guinea', 'EMEA', 0),
                                                                       ('GR', N'Greece', 'EMEA', 0),
                                                                       ('GS', N'South Georgia and South Sandwich Islands', 'LATAM', 0),
                                                                       ('GT', N'Guatemala', 'LATAM', 0),
                                                                       ('GU', N'Guam', 'APAC', 0),
                                                                       ('GW', N'Guinea-Bissau', 'EMEA', 0),
                                                                       ('GY', N'Guyana', 'LATAM', 0),
                                                                       ('HK', N'Hong Kong', 'APAC', 0),
                                                                       ('HM', N'Heard Island and McDonald Islands', 'APAC', 0),
                                                                       ('HN', N'Honduras', 'LATAM', 0),
                                                                       ('HR', N'Croatia', 'EMEA', 0),
                                                                       ('HT', N'Haiti', 'LATAM', 0),
                                                                       ('HU', N'Hungary', 'EMEA', 0),
                                                                       ('ID', N'Indonesia', 'APAC', 0),
                                                                       ('IE', N'Ireland', 'EMEA', 0),
                                                                       ('IL', N'Israel', 'EMEA', 0),
                                                                       ('IM', N'Isle of Man', 'EMEA', 0),
                                                                       ('IN', N'India', 'APAC', 0),
                                                                       ('IO', N'British Indian Ocean Territory', 'APAC', 0),
                                                                       ('IQ', N'Iraq', 'EMEA', 0),
                                                                       ('IR', N'Iran', 'EMEA', 0),
                                                                       ('IS', N'Iceland', 'EMEA', 0),
                                                                       ('IT', N'Italy', 'EMEA', 0),
                                                                       ('JE', N'Jersey', 'EMEA', 0),
                                                                       ('JM', N'Jamaica', 'LATAM', 0),
                                                                       ('JO', N'Jordan', 'EMEA', 0),
                                                                       ('JP', N'Japan', 'APAC', 0),
                                                                       ('KE', N'Kenya', 'EMEA', 0),
                                                                       ('KG', N'Kyrgyzstan', 'EMEA', 0),
                                                                       ('KH', N'Cambodia', 'APAC', 0),
                                                                       ('KI', N'Kiribati', 'APAC', 0),
                                                                       ('KM', N'Comoros', 'EMEA', 0),
                                                                       ('KN', N'Saint Kitts and Nevis', 'LATAM', 0),
                                                                       ('KP', N'Korea, North', 'APAC', 0),
                                                                       ('KR', N'Korea, South', 'APAC', 0),
                                                                       ('KW', N'Kuwait', 'EMEA', 0),
                                                                       ('KY', N'Cayman Islands', 'LATAM', 0),
                                                                       ('KZ', N'Kazakhstan', 'EMEA', 0),
                                                                       ('LA', N'Laos', 'APAC', 0),
                                                                       ('LB', N'Lebanon', 'EMEA', 0),
                                                                       ('LC', N'Saint Lucia', 'LATAM', 0),
                                                                       ('LI', N'Liechtenstein', 'EMEA', 0),
                                                                       ('LK', N'Sri Lanka', 'APAC', 0),
                                                                       ('LR', N'Liberia', 'EMEA', 0),
                                                                       ('LS', N'Lesotho', 'EMEA', 0),
                                                                       ('LT', N'Lithuania', 'EMEA', 0),
                                                                       ('LU', N'Luxembourg', 'EMEA', 0),
                                                                       ('LV', N'Latvia', 'EMEA', 0),
                                                                       ('LY', N'Libya', 'EMEA', 0),
                                                                       ('MA', N'Morocco', 'EMEA', 0),
                                                                       ('MC', N'Monaco', 'EMEA', 0),
                                                                       ('MD', N'Moldova', 'EMEA', 0),
                                                                       ('ME', N'Montenegro', 'EMEA', 0),
                                                                       ('MF', N'Saint Martin', 'LATAM', 0),
                                                                       ('MG', N'Madagascar', 'EMEA', 0),
                                                                       ('MH', N'Marshall Islands', 'APAC', 0),
                                                                       ('MK', N'North Macedonia', 'EMEA', 0),
                                                                       ('ML', N'Mali', 'EMEA', 0),
                                                                       ('MM', N'Myanmar', 'APAC', 0),
                                                                       ('MN', N'Mongolia', 'APAC', 0),
                                                                       ('MO', N'Macao', 'APAC', 0),
                                                                       ('MP', N'Northern Mariana Islands', 'APAC', 0),
                                                                       ('MQ', N'Martinique', 'LATAM', 0),
                                                                       ('MR', N'Mauritania', 'EMEA', 0),
                                                                       ('MS', N'Montserrat', 'LATAM', 0),
                                                                       ('MT', N'Malta', 'EMEA', 0),
                                                                       ('MU', N'Mauritius', 'EMEA', 0),
                                                                       ('MV', N'Maldives', 'APAC', 0),
                                                                       ('MW', N'Malawi', 'EMEA', 0),
                                                                       ('MX', N'Mexico', 'LATAM', 0),
                                                                       ('MY', N'Malaysia', 'APAC', 0),
                                                                       ('MZ', N'Mozambique', 'EMEA', 0),
                                                                       ('NA', N'Namibia', 'EMEA', 0),
                                                                       ('NC', N'New Caledonia', 'APAC', 0),
                                                                       ('NE', N'Niger', 'EMEA', 0),
                                                                       ('NF', N'Norfolk Island', 'APAC', 0),
                                                                       ('NG', N'Nigeria', 'EMEA', 0),
                                                                       ('NI', N'Nicaragua', 'LATAM', 0),
                                                                       ('NL', N'Netherlands', 'EMEA', 0),
                                                                       ('NO', N'Norway', 'EMEA', 0),
                                                                       ('NP', N'Nepal', 'APAC', 0),
                                                                       ('NR', N'Nauru', 'APAC', 0),
                                                                       ('NU', N'Niue', 'APAC', 0),
                                                                       ('NZ', N'New Zealand', 'APAC', 0),
                                                                       ('OM', N'Oman', 'EMEA', 0),
                                                                       ('PA', N'Panama', 'LATAM', 0),
                                                                       ('PE', N'Peru', 'LATAM', 0),
                                                                       ('PF', N'French Polynesia', 'APAC', 0),
                                                                       ('PG', N'Papua New Guinea', 'APAC', 0),
                                                                       ('PH', N'Philippines', 'APAC', 0),
                                                                       ('PK', N'Pakistan', 'APAC', 0),
                                                                       ('PL', N'Poland', 'EMEA', 0),
                                                                       ('PM', N'Saint Pierre and Miquelon', 'NAM', 0),
                                                                       ('PN', N'Pitcairn', 'APAC', 0),
                                                                       ('PR', N'Puerto Rico', 'LATAM', 0),
                                                                       ('PS', N'Palestine', 'EMEA', 0),
                                                                       ('PT', N'Portugal', 'EMEA', 0),
                                                                       ('PW', N'Palau', 'APAC', 0),
                                                                       ('PY', N'Paraguay', 'LATAM', 0),
                                                                       ('QA', N'Qatar', 'EMEA', 0),
                                                                       ('RE', N'Réunion', 'EMEA', 0),
                                                                       ('RO', N'Romania', 'EMEA', 0),
                                                                       ('RS', N'Serbia', 'EMEA', 0),
                                                                       ('RU', N'Russian Federation', 'EMEA', 0),
                                                                       ('RW', N'Rwanda', 'EMEA', 0),
                                                                       ('SA', N'Saudi Arabia', 'EMEA', 0),
                                                                       ('SB', N'Solomon Islands', 'APAC', 0),
                                                                       ('SC', N'Seychelles', 'EMEA', 0),
                                                                       ('SD', N'Sudan', 'EMEA', 0),
                                                                       ('SE', N'Sweden', 'EMEA', 0),
                                                                       ('SG', N'Singapore', 'APAC', 0),
                                                                       ('SH', N'Saint Helena', 'EMEA', 0),
                                                                       ('SI', N'Slovenia', 'EMEA', 0),
                                                                       ('SJ', N'Svalbard and Jan Mayen', 'EMEA', 0),
                                                                       ('SK', N'Slovakia', 'EMEA', 0),
                                                                       ('SL', N'Sierra Leone', 'EMEA', 0),
                                                                       ('SM', N'San Marino', 'EMEA', 0),
                                                                       ('SN', N'Senegal', 'EMEA', 0),
                                                                       ('SO', N'Somalia', 'EMEA', 0),
                                                                       ('SR', N'Suriname', 'LATAM', 0),
                                                                       ('SS', N'South Sudan', 'EMEA', 0),
                                                                       ('ST', N'Sao Tome and Principe', 'EMEA', 0),
                                                                       ('SV', N'El Salvador', 'LATAM', 0),
                                                                       ('SX', N'Sint Maarten', 'LATAM', 0),
                                                                       ('SY', N'Syrian Arab Republic', 'EMEA', 0),
                                                                       ('SZ', N'Eswatini', 'EMEA', 0),
                                                                       ('TC', N'Turks and Caicos Islands', 'LATAM', 0),
                                                                       ('TD', N'Chad', 'EMEA', 0),
                                                                       ('TF', N'French Southern Territories', 'EMEA', 0),
                                                                       ('TG', N'Togo', 'EMEA', 0),
                                                                       ('TH', N'Thailand', 'APAC', 0),
                                                                       ('TJ', N'Tajikistan', 'EMEA', 0),
                                                                       ('TK', N'Tokelau', 'APAC', 0),
                                                                       ('TL', N'Timor-Leste', 'APAC', 0),
                                                                       ('TM', N'Turkmenistan', 'EMEA', 0),
                                                                       ('TN', N'Tunisia', 'EMEA', 0),
                                                                       ('TO', N'Tonga', 'APAC', 0),
                                                                       ('TR', N'Turkey', 'EMEA', 0),
                                                                       ('TT', N'Trinidad and Tobago', 'LATAM', 0),
                                                                       ('TV', N'Tuvalu', 'APAC', 0),
                                                                       ('TW', N'Taiwan', 'APAC', 0),
                                                                       ('TZ', N'Tanzania', 'EMEA', 0),
                                                                       ('UA', N'Ukraine', 'EMEA', 0),
                                                                       ('UG', N'Uganda', 'EMEA', 0),
                                                                       ('UM', N'United States Minor Outlying Islands', 'APAC', 0),
                                                                       ('US', N'United States', 'NAM', 0),
                                                                       ('UY', N'Uruguay', 'LATAM', 0),
                                                                       ('UZ', N'Uzbekistan', 'EMEA', 0),
                                                                       ('VA', N'Vatican City', 'EMEA', 0),
                                                                       ('VC', N'Saint Vincent and the Grenadines', 'LATAM', 0),
                                                                       ('VE', N'Venezuela', 'LATAM', 0),
                                                                       ('VG', N'Virgin Islands, British', 'LATAM', 0),
                                                                       ('VI', N'Virgin Islands, U.S.', 'LATAM', 0),
                                                                       ('VN', N'Viet Nam', 'APAC', 0),
                                                                       ('VU', N'Vanuatu', 'APAC', 0),
                                                                       ('WF', N'Wallis and Futuna', 'APAC', 0),
                                                                       ('WS', N'Samoa', 'APAC', 0),
                                                                       ('YE', N'Yemen', 'EMEA', 0),
                                                                       ('YT', N'Mayotte', 'EMEA', 0),
                                                                       ('ZA', N'South Africa', 'EMEA', 0),
                                                                       ('ZM', N'Zambia', 'EMEA', 0),
                                                                       ('ZW', N'Zimbabwe', 'EMEA', 0),
                                                                       ('XK', N'Kosovo', 'EMEA', 0);

-- =============================================================================
-- 3. INSERT COUNTRY PROPERTIES
-- =============================================================================

-- Note: Uses the currently valid property set (state = 'VALID' and within date range)
-- If no valid property set exists, these inserts will fail with NULL constraint violation
-- To create a new property set if none exists, uncomment the following:
-- INSERT INTO property_set (start_date, state) VALUES (GETDATE(), 'VALID');

-- Note: Using current valid property set
-- Customs Union Properties (only for EU countries)
INSERT INTO country_property
(country_id, country_property_type_id, property_set_id, property_value)
SELECT
    c.id,
    cpt.id,
    (SELECT ps.id FROM property_set ps
     WHERE ps.state = 'VALID'
       AND ps.start_date <= GETDATE()
       AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
     ORDER BY ps.start_date DESC
     OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY),
    CASE
        WHEN c.iso_code IN ('AT', N'BE', 'BG', 'CZ', 'DE', 'DK', 'EE', 'ES', 'FI', 'FR', 'GR', 'HR', 'HU', 'IE', 'IT', 'LT', 'LU', 'LV', 'MT', 'NL', 'PL', 'PT', 'RO', 'SE', 'SI', 'SK')
            THEN 'EU'
        ELSE 'NONE'
        END
FROM country c, country_property_type cpt
WHERE cpt.external_mapping_id = 'UNION';

-- Safety Stock Properties
INSERT INTO country_property
(country_id, country_property_type_id, property_set_id, property_value)
SELECT
    c.id,
    cpt.id,
    (SELECT ps.id FROM property_set ps
     WHERE ps.state = 'VALID'
       AND ps.start_date <= GETDATE()
       AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
     ORDER BY ps.start_date DESC
     OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY),
    CASE c.iso_code
            WHEN 'AD' THEN N'15'
            WHEN 'AE' THEN N'20'
            WHEN 'AF' THEN N'30'
            WHEN 'AG' THEN N'55'
            WHEN 'AI' THEN N'55'
            WHEN 'AL' THEN N'15'
            WHEN 'AM' THEN N'15'
            WHEN 'AO' THEN N'15'
            WHEN 'AQ' THEN N'55'
            WHEN 'AR' THEN N'55'
            WHEN 'AS' THEN N'55'
            WHEN 'AT' THEN N'10'
            WHEN 'AU' THEN N'55'
            WHEN 'AW' THEN N'55'
            WHEN 'AZ' THEN N'15'
            WHEN 'BA' THEN N'15'
            WHEN 'BB' THEN N'55'
            WHEN 'BD' THEN N'55'
            WHEN 'BE' THEN N'10'
            WHEN 'BF' THEN N'30'
            WHEN 'BG' THEN N'10'
            WHEN 'BH' THEN N'20'
            WHEN 'BI' THEN N'30'
            WHEN 'BJ' THEN N'30'
            WHEN 'BL' THEN N'30'
            WHEN 'BM' THEN N'55'
            WHEN 'BN' THEN N'55'
            WHEN 'BO' THEN N'55'
            WHEN 'BQ' THEN N'55'
            WHEN 'BR' THEN N'55'
            WHEN 'BS' THEN N'55'
            WHEN 'BT' THEN N'55'
            WHEN 'BV' THEN N'30'
            WHEN 'BW' THEN N'15'
            WHEN 'BY' THEN N'55'
            WHEN 'BZ' THEN N'55'
            WHEN 'CA' THEN N'55'
            WHEN 'CC' THEN N'55'
            WHEN 'CD' THEN N'30'
            WHEN 'CF' THEN N'30'
            WHEN 'CG' THEN N'30'
            WHEN 'CH' THEN N'10'
            WHEN 'CI' THEN N'30'
            WHEN 'CK' THEN N'30'
            WHEN 'CL' THEN N'55'
            WHEN 'CM' THEN N'30'
            WHEN 'CN' THEN N'55'
            WHEN 'CO' THEN N'55'
            WHEN 'CR' THEN N'55'
            WHEN 'CU' THEN N'55'
            WHEN 'CV' THEN N'30'
            WHEN 'CW' THEN N'30'
            WHEN 'CX' THEN N'55'
            WHEN 'CY' THEN N'10'
            WHEN 'CZ' THEN N'10'
            WHEN 'DE' THEN N'10'
            WHEN 'DJ' THEN N'30'
            WHEN 'DK' THEN N'10'
            WHEN 'DM' THEN N'55'
            WHEN 'DO' THEN N'55'
            WHEN 'DZ' THEN N'10'
            WHEN 'EC' THEN N'55'
            WHEN 'EE' THEN N'10'
            WHEN 'EG' THEN N'30'
            WHEN 'EH' THEN N'30'
            WHEN 'ER' THEN N'30'
            WHEN 'ES' THEN N'10'
            WHEN 'ET' THEN N'30'
            WHEN 'FI' THEN N'10'
            WHEN 'FJ' THEN N'55'
            WHEN 'FK' THEN N'55'
            WHEN 'FM' THEN N'55'
            WHEN 'FO' THEN N'30'
            WHEN 'FR' THEN N'10'
            WHEN 'GA' THEN N'30'
            WHEN 'GB' THEN N'30'
            WHEN 'GD' THEN N'55'
            WHEN 'GE' THEN N'10'
            WHEN 'GF' THEN N'30'
            WHEN 'GG' THEN N'30'
            WHEN 'GH' THEN N'30'
            WHEN 'GI' THEN N'10'
            WHEN 'GL' THEN N'30'
            WHEN 'GM' THEN N'30'
            WHEN 'GN' THEN N'30'
            WHEN 'GP' THEN N'30'
            WHEN 'GQ' THEN N'30'
            WHEN 'GR' THEN N'10'
            WHEN 'GS' THEN N'55'
            WHEN 'GT' THEN N'55'
            WHEN 'GU' THEN N'55'
            WHEN 'GW' THEN N'30'
            WHEN 'GY' THEN N'55'
            WHEN 'HK' THEN N'55'
            WHEN 'HM' THEN N'30'
            WHEN 'HN' THEN N'55'
            WHEN 'HR' THEN N'10'
            WHEN 'HT' THEN N'55'
            WHEN 'HU' THEN N'10'
            WHEN 'ID' THEN N'55'
            WHEN 'IE' THEN N'10'
            WHEN 'IL' THEN N'30'
            WHEN 'IM' THEN N'30'
            WHEN 'IN' THEN N'55'
            WHEN 'IO' THEN N'55'
            WHEN 'IQ' THEN N'30'
            WHEN 'IR' THEN N'30'
            WHEN 'IS' THEN N'20'
            WHEN 'IT' THEN N'10'
            WHEN 'JE' THEN N'30'
            WHEN 'JM' THEN N'55'
            WHEN 'JO' THEN N'30'
            WHEN 'JP' THEN N'55'
            WHEN 'KE' THEN N'30'
            WHEN 'KG' THEN N'30'
            WHEN 'KH' THEN N'55'
            WHEN 'KI' THEN N'55'
            WHEN 'KM' THEN N'30'
            WHEN 'KN' THEN N'55'
            WHEN 'KP' THEN N'55'
            WHEN 'KR' THEN N'55'
            WHEN 'KW' THEN N'30'
            WHEN 'KY' THEN N'55'
            WHEN 'KZ' THEN N'30'
            WHEN 'LA' THEN N'55'
            WHEN 'LB' THEN N'30'
            WHEN 'LC' THEN N'55'
            WHEN 'LI' THEN N'10'
            WHEN 'LK' THEN N'55'
            WHEN 'LR' THEN N'30'
            WHEN 'LS' THEN N'30'
            WHEN 'LT' THEN N'10'
            WHEN 'LU' THEN N'10'
            WHEN 'LV' THEN N'10'
            WHEN 'LY' THEN N'30'
            WHEN 'MA' THEN N'20'
            WHEN 'MC' THEN N'30'
            WHEN 'MD' THEN N'30'
            WHEN 'ME' THEN N'30'
            WHEN 'MF' THEN N'30'
            WHEN 'MG' THEN N'30'
            WHEN 'MH' THEN N'55'
            WHEN 'MK' THEN N'30'
            WHEN 'ML' THEN N'30'
            WHEN 'MM' THEN N'55'
            WHEN 'MN' THEN N'55'
            WHEN 'MO' THEN N'55'
            WHEN 'MP' THEN N'55'
            WHEN 'MQ' THEN N'30'
            WHEN 'MR' THEN N'30'
            WHEN 'MS' THEN N'55'
            WHEN 'MT' THEN N'10'
            WHEN 'MU' THEN N'30'
            WHEN 'MV' THEN N'55'
            WHEN 'MW' THEN N'30'
            WHEN 'MX' THEN N'55'
            WHEN 'MY' THEN N'55'
            WHEN 'MZ' THEN N'30'
            WHEN 'NA' THEN N'30'
            WHEN 'NC' THEN N'30'
            WHEN 'NE' THEN N'30'
            WHEN 'NF' THEN N'55'
            WHEN 'NG' THEN N'30'
            WHEN 'NI' THEN N'55'
            WHEN 'NL' THEN N'10'
            WHEN 'NO' THEN N'10'
            WHEN 'NP' THEN N'55'
            WHEN 'NR' THEN N'55'
            WHEN 'NU' THEN N'55'
            WHEN 'NZ' THEN N'55'
            WHEN 'OM' THEN N'30'
            WHEN 'PA' THEN N'55'
            WHEN 'PE' THEN N'55'
            WHEN 'PF' THEN N'30'
            WHEN 'PG' THEN N'55'
            WHEN 'PH' THEN N'55'
            WHEN 'PK' THEN N'55'
            WHEN 'PL' THEN N'10'
            WHEN 'PM' THEN N'30'
            WHEN 'PN' THEN N'55'
            WHEN 'PR' THEN N'55'
            WHEN 'PS' THEN N'30'
            WHEN 'PT' THEN N'10'
            WHEN 'PW' THEN N'55'
            WHEN 'PY' THEN N'55'
            WHEN 'QA' THEN N'30'
            WHEN 'RE' THEN N'30'
            WHEN 'RO' THEN N'10'
            WHEN 'RS' THEN N'10'
            WHEN 'RU' THEN N'30'
            WHEN 'RW' THEN N'30'
            WHEN 'SA' THEN N'30'
            WHEN 'SB' THEN N'55'
            WHEN 'SC' THEN N'30'
            WHEN 'SD' THEN N'30'
            WHEN 'SE' THEN N'10'
            WHEN 'SG' THEN N'55'
            WHEN 'SH' THEN N'30'
            WHEN 'SI' THEN N'10'
            WHEN 'SJ' THEN N'55'
            WHEN 'SK' THEN N'10'
            WHEN 'SL' THEN N'30'
            WHEN 'SM' THEN N'30'
            WHEN 'SN' THEN N'30'
            WHEN 'SO' THEN N'30'
            WHEN 'SR' THEN N'55'
            WHEN 'SS' THEN N'30'
            WHEN 'ST' THEN N'30'
            WHEN 'SV' THEN N'55'
            WHEN 'SX' THEN N'30'
            WHEN 'SY' THEN N'30'
            WHEN 'SZ' THEN N'30'
            WHEN 'TC' THEN N'55'
            WHEN 'TD' THEN N'30'
            WHEN 'TF' THEN N'30'
            WHEN 'TG' THEN N'30'
            WHEN 'TH' THEN N'55'
            WHEN 'TJ' THEN N'30'
            WHEN 'TK' THEN N'55'
            WHEN 'TL' THEN N'55'
            WHEN 'TM' THEN N'30'
            WHEN 'TN' THEN N'30'
            WHEN 'TO' THEN N'55'
            WHEN 'TR' THEN N'15'
            WHEN 'TT' THEN N'55'
            WHEN 'TV' THEN N'55'
            WHEN 'TW' THEN N'55'
            WHEN 'TZ' THEN N'30'
            WHEN 'UA' THEN N'55'
            WHEN 'UG' THEN N'30'
            WHEN 'UM' THEN N'55'
            WHEN 'US' THEN N'55'
            WHEN 'UY' THEN N'55'
            WHEN 'UZ' THEN N'30'
            WHEN 'VA' THEN N'30'
            WHEN 'VC' THEN N'55'
            WHEN 'VE' THEN N'55'
            WHEN 'VG' THEN N'55'
            WHEN 'VI' THEN N'55'
            WHEN 'VN' THEN N'55'
            WHEN 'VU' THEN N'55'
            WHEN 'WF' THEN N'30'
            WHEN 'WS' THEN N'55'
            WHEN 'YE' THEN N'30'
            WHEN 'YT' THEN N'30'
            WHEN 'ZA' THEN N'30'
            WHEN 'ZM' THEN N'30'
            WHEN 'ZW' THEN N'30'
            WHEN 'XK' THEN N'55'
        END
FROM country c, country_property_type cpt
WHERE cpt.external_mapping_id = 'SAFETY_STOCK';

-- Air Freight Share Properties (0.03 for countries with safety stock 55, otherwise 0%)
INSERT INTO country_property
(country_id, country_property_type_id, property_set_id, property_value)
SELECT
    c.id,
    cpt.id,
    (SELECT ps.id FROM property_set ps
     WHERE ps.state = 'VALID'
       AND ps.start_date <= GETDATE()
       AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
     ORDER BY ps.start_date DESC
     OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY),
    CASE
        WHEN cp_safety.property_value = '55' THEN N'0.03'
        ELSE '0'
        END
FROM country c
         CROSS JOIN country_property_type cpt
         LEFT JOIN country_property cp_safety
                   ON cp_safety.country_id = c.id
                       AND cp_safety.country_property_type_id = (
                           SELECT id FROM country_property_type
                           WHERE external_mapping_id = 'SAFETY_STOCK'
                       )
                       AND cp_safety.property_set_id = (
                           SELECT ps.id FROM property_set ps
                           WHERE ps.state = 'VALID'
                             AND ps.start_date <= GETDATE()
                             AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
                           ORDER BY ps.start_date DESC
                           OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY)
WHERE cpt.external_mapping_id = 'AIR_SHARE';

-- Wage Factor Properties (only for countries with defined values)
-- Wage Factor Properties (only for countries with defined values)
INSERT INTO country_property
(country_id, country_property_type_id, property_set_id, property_value)
SELECT
    c.id,
    cpt.id,
    (SELECT ps.id FROM property_set ps
     WHERE ps.state = 'VALID'
       AND ps.start_date <= GETDATE()
       AND (ps.end_date IS NULL OR ps.end_date > GETDATE())
     ORDER BY ps.start_date DESC
     OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY),
    CASE c.iso_code
        WHEN 'AT' THEN N'0.99'
        WHEN 'BE' THEN N'1.14'
        WHEN 'BG' THEN N'0.23'
        WHEN 'CZ' THEN N'0.44'
        WHEN 'DE' THEN N'1.00'
        WHEN 'DK' THEN N'1.16'
        WHEN 'EE' THEN N'0.60'
        WHEN 'ES' THEN N'0.90'
        WHEN 'FI' THEN N'1.02'
        WHEN 'FR' THEN N'1.05'
        WHEN 'GR' THEN N'0.35'
        WHEN 'HR' THEN N'0.31'
        WHEN 'HU' THEN N'0.35'
        WHEN 'IE' THEN N'0.97'
        WHEN 'IT' THEN N'0.72'
        WHEN 'LT' THEN N'0.36'
        WHEN 'LU' THEN N'1.31'
        WHEN 'LV' THEN N'0.33'
        WHEN 'MT' THEN N'0.41'
        WHEN 'NL' THEN N'1.05'
        WHEN 'PL' THEN N'0.27'
        WHEN 'PT' THEN N'0.41'
        WHEN 'RO' THEN N'0.27'
        WHEN 'SE' THEN N'0.94'
        WHEN 'SI' THEN N'0.62'
        WHEN 'SK' THEN N'0.42'
        ELSE '1'
        END
FROM country c, country_property_type cpt
WHERE cpt.external_mapping_id = 'WAGE';

-- =============================================================================
-- VERIFICATION QUERIES (Optional - for testing)
-- =============================================================================

-- Verify country count
-- SELECT COUNT(*) as total_countries FROM country;

-- Verify property types
-- SELECT * FROM country_property_type;

-- Verify EU countries with all properties
-- SELECT
--     c.iso_code,
--     c.region_code,
--     MAX(CASE WHEN cpt.name = 'Customs Union' THEN cp.property_value END) as customs_union,
--     MAX(CASE WHEN cpt.name = 'Safety Stock' THEN cp.property_value END) as safety_stock,
--     MAX(CASE WHEN cpt.name = 'Air Freight Share' THEN cp.property_value END) as air_freight,
--     MAX(CASE WHEN cpt.name = 'Wage Factor' THEN cp.property_value END) as wage_factor
-- FROM country c
-- JOIN country_property cp ON c.id = cp.country_id
-- JOIN country_property_type cpt ON cp.country_property_type_id = cpt.id
-- WHERE c.iso_code IN ('DE', 'FR', 'AT', 'BE', 'NL')
-- GROUP BY c.id, c.iso_code, c.region_code
-- ORDER BY c.iso_code;