-- Add Estonia and Luxembourg

-- Add locales, market, market locales
INSERT INTO locales (value, name, fallback_id) VALUES
    ('en_EE', 'English (Estonia)', (select id from locales where value = 'en_GB')),
	('de_LU', 'German (Luxembourg)', (select id from locales where value = 'de_DE')),
	('fr_LU', 'French (Luxembourg)', (select id from locales where value = 'fr_FR'));

INSERT INTO markets (name, vat, currency, price_adjustment) VALUES ('EE', 1.2, 'EUR', (select price_adjustment from markets where name = 'DE'));
INSERT INTO markets (name, vat, currency, price_adjustment) VALUES ('LU', 1.17, 'EUR', (select price_adjustment from markets where name = 'DE'));

INSERT INTO market_locales (market_id, locale_id, name) VALUES
    ((SELECT id FROM markets WHERE name = 'EE'), (SELECT id FROM locales WHERE value = 'en_EE'), 'Estonia (English)'),
    ((SELECT id FROM markets WHERE name = 'LU'), (SELECT id FROM locales WHERE value = 'de_LU'), 'Luxembourg (German)'),
    ((SELECT id FROM markets WHERE name = 'LU'), (SELECT id FROM locales WHERE value = 'fr_LU'), 'Luxembourg (French)');


-- Copy collections
INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
    SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'de_LU')
    FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');

INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
    SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'fr_LU')
    FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');

INSERT INTO collections_texts (collection_id, meta_title, meta_description, title, description, slug, locale_id)
    SELECT collection_id, meta_title, meta_description, title, description, slug, (SELECT id FROM locales WHERE value = 'en_EE')
    FROM collections_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');


-- Copy designer texts
INSERT INTO designer_texts (designerid, header, description, locale_id)
    SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'de_LU')
    FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');

INSERT INTO designer_texts (designerid, header, description, locale_id)
    SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'fr_LU')
    FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');

INSERT INTO designer_texts (designerid, header, description, locale_id)
    SELECT designerid, header, description, (SELECT id FROM locales WHERE value = 'en_EE')
    FROM designer_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');


-- Copy category data (page titles, meta descriptions), and category texts
INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
    SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'de_LU')
    FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');

INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
    SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'fr_LU')
    FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');

INSERT INTO categorydata (category_id, datakey_id, text, locale_id)
    SELECT category_id, datakey_id, text, (SELECT id FROM locales WHERE value = 'en_EE')
    FROM categorydata WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');

INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
    SELECT (SELECT id FROM locales WHERE value = 'de_LU'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
    FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'de_DE');

INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
    SELECT (SELECT id FROM locales WHERE value = 'fr_LU'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
    FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'fr_FR');

INSERT INTO category_texts (locale_id, category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description)
    SELECT (SELECT id FROM locales WHERE value = 'en_EE'), category_id, wallpaper_name, wallpaper_h1, wallpaper_description, canvas_name, canvas_h1, canvas_description, name, design_wallpaper_h1, design_wallpaper_description, poster_name, poster_h1, poster_description, framed_print_name, framed_print_h1, framed_print_description
    FROM category_texts WHERE locale_id = (SELECT id FROM locales WHERE value = 'en_GB');
	

-- Copy blacklisted products
-- EE: from global
-- LU: from BE or NL (benelux)
INSERT INTO product_blacklist (product_id, group_id, market_id)
    SELECT product_id, group_id, (select id from markets where name = 'EE')
    FROM product_blacklist WHERE market_id = (select id from markets where name = 'GLOBAL');
INSERT INTO product_blacklist (product_id, group_id, market_id)
    SELECT product_id, group_id, (select id from markets where name = 'LU')
    FROM product_blacklist WHERE market_id = (select id from markets where name = 'BE');

-- Add delivery methods (LU and EE: same delivery methods/prices as Germany)
-- Clear current methods for LU/EE+EUR. Add correct methods for new sites:
DELETE FROM "delivery-methods_prices" WHERE territory IN ('EE', 'LU') AND currency = 'EUR';
INSERT INTO "delivery-methods_prices" (territory, currency, option, option_value, price, method) VALUES
	('LU', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'samplesDelivery')),
	('LU', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'dhlEconomyEU')),
	('LU', 'EUR', '', '', (SELECT 7 / vat FROM markets WHERE name = 'LU'), (select id from "delivery-methods" where name = 'dhlExpress'));
INSERT INTO "delivery-methods_prices" (territory, currency, option, option_value, price, method) VALUES
	('EE', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'samplesDelivery')),
	('EE', 'EUR', '', '', 0, (select id from "delivery-methods" where name = 'dhlEconomyEU')),
	('EE', 'EUR', '', '', (SELECT 7 / vat FROM markets WHERE name = 'EE'), (select id from "delivery-methods" where name = 'dhlExpress'));
-- Delete unused delivery methods for other currencies:
-- (Note: I verified in contract_customers first that this was safe to do)
DELETE FROM "delivery-methods_prices" WHERE territory IN ('EE', 'LU') and currency != 'EUR';

-- Add invoice sequences
CREATE SEQUENCE "order-invoiceIdEE" start 1000;
CREATE SEQUENCE "order-invoiceIdLU" start 1000;


-- Copy popular links text tags to override the fallback values (because the prefix will be wrong)
-- ee from global
-- fr_lu from fr_be (french speaking benelux country)
-- de_lu from de_de
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
    SELECT textid, replace(text, '/int/', '/ee/'), (select id from locales where value = 'en_EE')
    FROM "i18n-texts" join "i18n-texts_data" using (textid)
    where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'en_EU');
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
    SELECT textid, replace(text, 'href="/', 'href="/lu-de/'), (select id from locales where value = 'de_LU')
    FROM "i18n-texts" join "i18n-texts_data" using (textid)
    where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'de_DE');
INSERT INTO "i18n-texts_data" (textid, text, locale_id)
    SELECT textid, replace(text, 'href="/be-fr/', 'href="/lu-fr/'), (select id from locales where value = 'fr_LU')
    FROM "i18n-texts" join "i18n-texts_data" using (textid)
    where name ilike 'popular-links-%' and locale_id = (select id from locales where value = 'fr_BE');