-- EVEROP: Cambodia provinces + delivery zones/methods only (no products). Safe to run twice.
-- Fees are PLACEHOLDERS: change them later in Admin > Delivery methods.
USE fashion_shop;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM shipping_methods WHERE zone_id IN (SELECT id FROM shipping_zones WHERE name IN ('Phnom Penh','Provinces'));
DELETE FROM shipping_zone_provinces WHERE zone_id IN (SELECT id FROM shipping_zones WHERE name IN ('Phnom Penh','Provinces'));
DELETE FROM shipping_zones WHERE name IN ('Phnom Penh','Provinces');
SET FOREIGN_KEY_CHECKS = 1;

INSERT IGNORE INTO countries (iso2, name, phone_code) VALUES ('KH', 'Cambodia', '+855');
SET @kh = (SELECT id FROM countries WHERE iso2 = 'KH');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Phnom Penh', 'ភ្នំពេញ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Phnom Penh');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Banteay Meanchey', 'បន្ទាយមានជ័យ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Banteay Meanchey');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Battambang', 'បាត់ដំបង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Battambang');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kampong Cham', 'កំពង់ចាម' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kampong Cham');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kampong Chhnang', 'កំពង់ឆ្នាំង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kampong Chhnang');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kampong Speu', 'កំពង់ស្ពឺ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kampong Speu');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kampong Thom', 'កំពង់ធំ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kampong Thom');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kampot', 'កំពត' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kampot');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kandal', 'កណ្តាល' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kandal');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kep', 'កែប' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kep');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Koh Kong', 'កោះកុង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Koh Kong');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Kratie', 'ក្រចេះ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Kratie');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Mondulkiri', 'មណ្ឌលគិរី' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Mondulkiri');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Oddar Meanchey', 'ឧត្តរមានជ័យ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Oddar Meanchey');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Pailin', 'ប៉ៃលិន' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Pailin');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Preah Sihanouk', 'ព្រះសីហនុ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Preah Sihanouk');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Preah Vihear', 'ព្រះវិហារ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Preah Vihear');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Prey Veng', 'ព្រៃវែង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Prey Veng');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Pursat', 'ពោធិ៍សាត់' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Pursat');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Ratanakiri', 'រតនគិរី' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Ratanakiri');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Siem Reap', 'សៀមរាប' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Siem Reap');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Stung Treng', 'ស្ទឹងត្រែង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Stung Treng');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Svay Rieng', 'ស្វាយរៀង' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Svay Rieng');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Takeo', 'តាកែវ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Takeo');
INSERT INTO provinces (country_id, name, name_km) SELECT @kh, 'Tboung Khmum', 'ត្បូងឃ្មុំ' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM provinces WHERE country_id=@kh AND name='Tboung Khmum');
INSERT INTO shipping_zones (name) VALUES ('Phnom Penh'), ('Provinces');
INSERT INTO shipping_zone_provinces (zone_id, province_id) SELECT (SELECT id FROM shipping_zones WHERE name='Phnom Penh'), id FROM provinces WHERE country_id=@kh AND name='Phnom Penh';
INSERT INTO shipping_zone_provinces (zone_id, province_id) SELECT (SELECT id FROM shipping_zones WHERE name='Provinces'), id FROM provinces WHERE country_id=@kh AND name<>'Phnom Penh';
INSERT INTO shipping_methods (zone_id, name, base_fee, free_over_amount, est_days_min, est_days_max, cod_available) VALUES ((SELECT id FROM shipping_zones WHERE name='Phnom Penh'), 'Standard delivery', 1.50, 120, 1, 2, 1);
INSERT INTO shipping_methods (zone_id, name, base_fee, free_over_amount, est_days_min, est_days_max, cod_available) VALUES ((SELECT id FROM shipping_zones WHERE name='Provinces'), 'Standard delivery', 3.00, 120, 3, 5, 1);

INSERT INTO settings (`key`, `value`, `group`) SELECT 'free_shipping_over','120','shipping' FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM settings WHERE `key`='free_shipping_over');