delete from ccmanager where pid=701;

delete from centers where pid=701;
delete from centers_groups where pid=701;

delete from categs where pid=701;
delete from categs_groups where pid=701;

update ain_corona a set a.countryterritoryCode = (select b.Three_Letter_Country_Code 
															from ain_corona_countries b
															where b.Two_Letter_Country_Code = a.geoid)
															where a.countryterritoryCode is null;

update ain_corona set dateRep = concat(year,'-',lpad(month,2,"0"),'-',lpad(day,2,"0"));															

insert into centers_groups (pid,code_center_group,desc_center_group)
select distinct 701,continent_code,continent_name
from ain_corona_countries
order by 2;

insert ignore into centers (pid,code_center_group,code_center,desc_center,comm_center)
select distinct 701,continent_code,Three_Letter_Country_Code,replace(replace(Country_Name,'&','and'),'\'',' '),Two_Letter_Country_Code
from ain_corona_countries
where Three_Letter_Country_Code is not null
order by 2;

update centers a set a.desc_center = (select distinct replace(b.countriesAndTerritories,'_',' ') 
										from ain_corona b
										where b.countryterritoryCode = a.code_center)
						where a.pid = 701
						and a.code_center in (select countryterritoryCode from ain_corona where countriesAndTerritories is not null);

update centers set desc_center = replace(desc_center,'(',' ') where pid=701;
update centers set desc_center = replace(desc_center,')',' ') where pid=701;

insert into categs_groups (pid,code_categ_group,desc_categ_group)
select 701,'1000','Covid-19 Data Groups';

INSERT INTO categs (pid, code_categ, desc_categ, um, comm_categ, code_categ_group) VALUES
(701,	'1001',	'Covid-19 Data',	'--',	'',	'1000');


/* POPULATION mid 1 */
insert ignore into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select countryterritoryCode,1001,'2019-12-31',ifnull(max(popdata2018),0),ifnull(max(popdata2018),0),1,701
from ain_corona
where countryterritoryCode in (Select code_center from centers where pid = 701)
group by countryterritoryCode;

/* CASES mid 2 */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select countryterritoryCode,1001,daterep,ifnull(cases,0),ifnull(cases,0),2,701
from ain_corona
where countryterritoryCode in (Select code_center from centers where pid = 701);

/* LOSSES mid 3 */
insert into ccmanager (code_center,code_categ,rab_date,rab_qty,rab_number,mid,pid)
select countryterritoryCode,1001,daterep,ifnull(deaths,0),ifnull(deaths,0),3,701
from ain_corona
where countryterritoryCode in (Select code_center from centers where pid = 701);

update centers set desc_center = code_center where code_center = 'USA';

/* INSERT DATA INTO ZCOVID19 */

truncate table zcovid19;

insert ignore into zcovid19 (code_center_group,code_center)
select distinct c.code_center_group,a.code_center
from ccmanager a, centers c
where a.pid=701
and a.code_center = c.code_center
and a.pid = c.pid
order by a.code_center desc;

update zcovid19 a set a.desc_center_group = (select b.desc_center_group
									from centers_groups b
									where b.pid = 701
									and b.code_center_group = a.code_center_group);

update zcovid19 a set a.desc_center = (select b.desc_center
									from centers b
									where b.pid = 701
									and b.code_center = a.code_center);

update zcovid19 a set a.population = (select sum(b.rab_number)
									from ccmanager b
									where b.pid = 701
									and b.code_center = a.code_center
									and b.mid = 1);

update zcovid19 a set a.cases = (select sum(b.rab_number)
									from ccmanager b
									where b.pid = 701
									and b.code_center = a.code_center
									and b.mid = 2);
									
update zcovid19 a set a.losses = (select sum(b.rab_number)
									from ccmanager b
									where b.pid = 701
									and b.code_center = a.code_center
									and b.mid = 3);
			
update zcovid19 set per_million = (cases / population)*1000000, lper_million = (losses / population)*1000000;		

delete from zcovid19 where population < 1; 									