SET search_path TO gm, public;

CREATE TABLE codelang (
	code varchar(100) NULL,
	country varchar(10000) NULL
);


INSERT INTO codelang (code,country) VALUES 
('af','Afrikaans')
,('af-ZA','Afrikaans (South Africa)')
,('ar','Arabic')
,('ar-AE','Arabic (U.A.E.)')
,('ar-BH','Arabic (Bahrain)')
,('ar-DZ','Arabic (Algeria)')
,('ar-EG','Arabic (Egypt)')
,('ar-IQ','Arabic (Iraq)')
,('ar-JO','Arabic (Jordan)')
,('ar-KW','Arabic (Kuwait)')
;
INSERT INTO codelang (code,country) VALUES 
('ar-LB','Arabic (Lebanon)')
,('ar-LY','Arabic (Libya)')
,('ar-MA','Arabic (Morocco)')
,('ar-OM','Arabic (Oman)')
,('ar-QA','Arabic (Qatar)')
,('ar-SA','Arabic (Saudi Arabia)')
,('ar-SY','Arabic (Syria)')
,('ar-TN','Arabic (Tunisia)')
,('ar-YE','Arabic (Yemen)')
,('az','Azeri (Latin)')
;
INSERT INTO codelang (code,country) VALUES 
('az-AZ','Azeri (Latin) (Azerbaijan)')
,('az-AZ','Azeri (Cyrillic) (Azerbaijan)')
,('be','Belarusian')
,('be-BY','Belarusian (Belarus)')
,('bg','Bulgarian')
,('bg-BG','Bulgarian (Bulgaria)')
,('bs-BA','Bosnian (Bosnia and Herzegovina)')
,('ca','Catalan')
,('ca-ES','Catalan (Spain)')
,('cs','Czech')
;
INSERT INTO codelang (code,country) VALUES 
('cs-CZ','Czech (Czech Republic)')
,('cy','Welsh')
,('cy-GB','Welsh (United Kingdom)')
,('da','Danish')
,('da-DK','Danish (Denmark)')
,('de','German')
,('de-AT','German (Austria)')
,('de-CH','German (Switzerland)')
,('de-DE','German (Germany)')
,('de-LI','German (Liechtenstein)')
;
INSERT INTO codelang (code,country) VALUES 
('de-LU','German (Luxembourg)')
,('dv','Divehi')
,('dv-MV','Divehi (Maldives)')
,('el','Greek')
,('el-GR','Greek (Greece)')
,('en','English')
,('en-AU','English (Australia)')
,('en-BZ','English (Belize)')
,('en-CA','English (Canada)')
,('en-CB','English (Caribbean)')
;
INSERT INTO codelang (code,country) VALUES 
('en-GB','English (United Kingdom)')
,('en-IE','English (Ireland)')
,('en-JM','English (Jamaica)')
,('en-NZ','English (New Zealand)')
,('en-PH','English (Republic of the Philippines)')
,('en-TT','English (Trinidad and Tobago)')
,('en-US','English (United States)')
,('en-ZA','English (South Africa)')
,('en-ZW','English (Zimbabwe)')
,('eo','Esperanto')
;
INSERT INTO codelang (code,country) VALUES 
('es','Spanish')
,('es-AR','Spanish (Argentina)')
,('es-BO','Spanish (Bolivia)')
,('es-CL','Spanish (Chile)')
,('es-CO','Spanish (Colombia)')
,('es-CR','Spanish (Costa Rica)')
,('es-DO','Spanish (Dominican Republic)')
,('es-EC','Spanish (Ecuador)')
,('es-ES','Spanish (Castilian)')
,('es-ES','Spanish (Spain)')
;
INSERT INTO codelang (code,country) VALUES 
('es-GT','Spanish (Guatemala)')
,('es-HN','Spanish (Honduras)')
,('es-MX','Spanish (Mexico)')
,('es-NI','Spanish (Nicaragua)')
,('es-PA','Spanish (Panama)')
,('es-PE','Spanish (Peru)')
,('es-PR','Spanish (Puerto Rico)')
,('es-PY','Spanish (Paraguay)')
,('es-SV','Spanish (El Salvador)')
,('es-UY','Spanish (Uruguay)')
;
INSERT INTO codelang (code,country) VALUES 
('es-VE','Spanish (Venezuela)')
,('et','Estonian')
,('et-EE','Estonian (Estonia)')
,('eu','Basque')
,('eu-ES','Basque (Spain)')
,('fa','Farsi')
,('fa-IR','Farsi (Iran)')
,('fi','Finnish')
,('fi-FI','Finnish (Finland)')
,('fo','Faroese')
;
INSERT INTO codelang (code,country) VALUES 
('fo-FO','Faroese (Faroe Islands)')
,('fr','French')
,('fr-BE','French (Belgium)')
,('fr-CA','French (Canada)')
,('fr-CH','French (Switzerland)')
,('fr-FR','French (France)')
,('fr-LU','French (Luxembourg)')
,('fr-MC','French (Principality of Monaco)')
,('gl','Galician')
,('gl-ES','Galician (Spain)')
;
INSERT INTO codelang (code,country) VALUES 
('gu','Gujarati')
,('gu-IN','Gujarati (India)')
,('he','Hebrew')
,('he-IL','Hebrew (Israel)')
,('hi','Hindi')
,('hi-IN','Hindi (India)')
,('hr','Croatian')
,('hr-BA','Croatian (Bosnia and Herzegovina)')
,('hr-HR','Croatian (Croatia)')
,('hu','Hungarian')
;
INSERT INTO codelang (code,country) VALUES 
('hu-HU','Hungarian (Hungary)')
,('hy','Armenian')
,('hy-AM','Armenian (Armenia)')
,('id','Indonesian')
,('id-ID','Indonesian (Indonesia)')
,('is','Icelandic')
,('is-IS','Icelandic (Iceland)')
,('it','Italian')
,('it-CH','Italian (Switzerland)')
,('it-IT','Italian (Italy)')
;
INSERT INTO codelang (code,country) VALUES 
('ja','Japanese')
,('ja-JP','Japanese (Japan)')
,('ka','Georgian')
,('ka-GE','Georgian (Georgia)')
,('kk','Kazakh')
,('kk-KZ','Kazakh (Kazakhstan)')
,('kn','Kannada')
,('kn-IN','Kannada (India)')
,('ko','Korean')
,('ko-KR','Korean (Korea)')
;
INSERT INTO codelang (code,country) VALUES 
('kok','Konkani')
,('kok-IN','Konkani (India)')
,('ky','Kyrgyz')
,('ky-KG','Kyrgyz (Kyrgyzstan)')
,('lt','Lithuanian')
,('lt-LT','Lithuanian (Lithuania)')
,('lv','Latvian')
,('lv-LV','Latvian (Latvia)')
,('mi','Maori')
,('mi-NZ','Maori (New Zealand)')
;
INSERT INTO codelang (code,country) VALUES 
('mk','FYRO Macedonian')
,('mk-MK','FYRO Macedonian (Former Yugoslav Republic of Macedonia)')
,('mn','Mongolian')
,('mn-MN','Mongolian (Mongolia)')
,('mr','Marathi')
,('mr-IN','Marathi (India)')
,('ms','Malay')
,('ms-BN','Malay (Brunei Darussalam)')
,('ms-MY','Malay (Malaysia)')
,('mt','Maltese')
;
INSERT INTO codelang (code,country) VALUES 
('mt-MT','Maltese (Malta)')
,('nb','Norwegian (Bokm?l)')
,('nb-NO','Norwegian (Bokm?l) (Norway)')
,('nl','Dutch')
,('nl-BE','Dutch (Belgium)')
,('nl-NL','Dutch (Netherlands)')
,('nn-NO','Norwegian (Nynorsk) (Norway)')
,('ns','Northern Sotho')
,('ns-ZA','Northern Sotho (South Africa)')
,('pa','Punjabi')
;
INSERT INTO codelang (code,country) VALUES 
('pa-IN','Punjabi (India)')
,('pl','Polish')
,('pl-PL','Polish (Poland)')
,('ps','Pashto')
,('ps-AR','Pashto (Afghanistan)')
,('pt','Portuguese')
,('pt-BR','Portuguese (Brazil)')
,('pt-PT','Portuguese (Portugal)')
,('qu','Quechua')
,('qu-BO','Quechua (Bolivia)')
;
INSERT INTO codelang (code,country) VALUES 
('qu-EC','Quechua (Ecuador)')
,('qu-PE','Quechua (Peru)')
,('ro','Romanian')
,('ro-RO','Romanian (Romania)')
,('ru','Russian')
,('ru-RU','Russian (Russia)')
,('sa','Sanskrit')
,('sa-IN','Sanskrit (India)')
,('se','Sami (Northern)')
,('se-FI','Sami (Northern) (Finland)')
;
INSERT INTO codelang (code,country) VALUES 
('se-FI','Sami (Skolt) (Finland)')
,('se-FI','Sami (Inari) (Finland)')
,('se-NO','Sami (Northern) (Norway)')
,('se-NO','Sami (Lule) (Norway)')
,('se-NO','Sami (Southern) (Norway)')
,('se-SE','Sami (Northern) (Sweden)')
,('se-SE','Sami (Lule) (Sweden)')
,('se-SE','Sami (Southern) (Sweden)')
,('sk','Slovak')
,('sk-SK','Slovak (Slovakia)')
;
INSERT INTO codelang (code,country) VALUES 
('sl','Slovenian')
,('sl-SI','Slovenian (Slovenia)')
,('sq','Albanian')
,('sq-AL','Albanian (Albania)')
,('sr-BA','Serbian (Latin) (Bosnia and Herzegovina)')
,('sr-BA','Serbian (Cyrillic) (Bosnia and Herzegovina)')
,('sr-SP','Serbian (Latin) (Serbia and Montenegro)')
,('sr-SP','Serbian (Cyrillic) (Serbia and Montenegro)')
,('sv','Swedish')
,('sv-FI','Swedish (Finland)')
;
INSERT INTO codelang (code,country) VALUES 
('sv-SE','Swedish (Sweden)')
,('sw','Swahili')
,('sw-KE','Swahili (Kenya)')
,('syr','Syriac')
,('syr-SY','Syriac (Syria)')
,('ta','Tamil')
,('ta-IN','Tamil (India)')
,('te','Telugu')
,('te-IN','Telugu (India)')
,('th','Thai')
;
INSERT INTO codelang (code,country) VALUES 
('th-TH','Thai (Thailand)')
,('tl','Tagalog')
,('tl-PH','Tagalog (Philippines)')
,('tn','Tswana')
,('tn-ZA','Tswana (South Africa)')
,('tr','Turkish')
,('tr-TR','Turkish (Turkey)')
,('tt','Tatar')
,('tt-RU','Tatar (Russia)')
,('ts','Tsonga')
;
INSERT INTO codelang (code,country) VALUES 
('uk','Ukrainian')
,('uk-UA','Ukrainian (Ukraine)')
,('ur','Urdu')
,('ur-PK','Urdu (Islamic Republic of Pakistan)')
,('uz','Uzbek (Latin)')
,('uz-UZ','Uzbek (Latin) (Uzbekistan)')
,('uz-UZ','Uzbek (Cyrillic) (Uzbekistan)')
,('vi','Vietnamese')
,('vi-VN','Vietnamese (Viet Nam)')
,('xh','Xhosa')
;
INSERT INTO codelang (code,country) VALUES 
('xh-ZA','Xhosa (South Africa)')
,('zh','Chinese')
,('zh-CN','Chinese (S)')
,('zh-HK','Chinese (Hong Kong)')
,('zh-MO','Chinese (Macau)')
,('zh-SG','Chinese (Singapore)')
,('zh-TW','Chinese (T)')
,('zu','Zulu')
,('zu-ZA','Zulu (South Africa)')
;
/***************************/
-- Manual migrate

CREATE TABLE country_code (
	code varchar(100) NULL,
	country varchar(10000) NULL
);

insert into country_code(code, country)
select distinct
	   t.code,  	   
	   coalesce(
				   	case when length(t.country_name2) = 1 then null else t.country_name2 end, 
				   	case when length(t.contry_name1) = 1 then null else t.contry_name1 end, 
				   	t.country
	   			) as country
from 
(
	select trim(c.code) as code,
			substring(trim(c.country) from  '\((.+)\)') as contry_name1, 
			substring(
					  substring(trim(c.country) from  '\((.+)\)')
				from '\((.*)$') as country_name2,
			trim(c.country) as country		  			
	from codelang as c
) t; 

commit; 


delete from location_country_languages as lcl 
where lcl.country_id in 
(
	select 
	lc.id  
	from 
	(
		select	
			lpad((row_number() over (order by t.country asc))::text, 3, '0') as code,		
			jsonb_build_object('en-GB',  t.country) as "name"
		from 
		(
			select 
			distinct c.country
			from country_code c
		) t 
	) d 
	join location_country lc on  lc.code =  d.code and d."name"=lc."name"
)
;
commit;


delete from location_country as lcl 
where lcl.id in 
(
	select 
	lc.id  
	from 
	(
		select	
			lpad((row_number() over (order by t.country asc))::text, 3, '0') as code,		
			jsonb_build_object('en-GB',  t.country) as "name"
		from 
		(
			select 
			distinct c.country
			from country_code c
		) t 
	) d 
	join location_country lc on  lc.code =  d.code and d."name"=lc."name"
)
;

commit;


delete from translation_language tl 
where tl.id in 
(
	SELECT tl.id 
	FROM 
	(
		select 
		distinct c.country, c.code 
		 from country_code c
	) t 
	JOIN translation_language tl ON tl.locale = t.code and tl.title = t.country
); 

commit; 

drop table country_code;
drop table codelang;

commit;