# First, count the taxa and update the counts in table "taxon"
create temporary table tmpTaxa
select bioRecordTaxonKey as tKey, count(*) as uCount
from bioRecord
group by bioRecordTaxonKey;

alter table tmpTaxa
  modify uCount int,
  add key (tKey);

update low_priority taxon
left join tmpTaxa on tKey = taxonKey
set unitCount = IfNull(uCount, 0);

# then, create all the suggest tables with prefix "tmp"
create table tmpTaxon
select taxonName as Name, sum(unitCount) as unitCount
from taxon
where unitCount > 0
group by taxonName;

create table tmpGenus
select genus as Name, sum(unitCount) as unitCount
from taxon
where unitCount > 0
group by genus;

create table tmpFamily
select family as Name, sum(unitCount) as unitCount
from taxon
where unitCount > 0
group by family;

create table tmpHigherTaxon
select higherTaxonName as Name, sum(unitCount) as unitCount
from higherTaxon
join taxon using (taxonKey)
where unitCount > 0
group by higherTaxonName;

create table tmpVernacular
select vernacularName as Name, sum(unitCount) as unitCount
from vernacularName
join taxon on vernacularTaxonKey = taxonKey
where unitCount > 0
group by vernacularName;

# for country, first create a temp table for countryCodes (faster)
create temporary table tmpCountryCodes
select bioRecordCountryCode, count(*) as unitCount
from bioRecord
group by bioRecordCountryCode;

create table tmpCountry
select countryName as Name, sum(unitCount) as unitCount
from isoCountry
join tmpCountryCodes on bioRecordCountryCode = isoCountryCode
group by countryName;

create table tmpInstCode
select institution_code as Name, count(*) as unitCount from bioRecord group by institution_code;

create table tmpCollCode
select collection_code as Name, count(*) as unitCount from bioRecord group by collection_code;

# remove trailing spaces
update tmpInstCode set Name = trim(Name);
update tmpCollCode set Name = trim(Name);

# remove invalid entries
delete from tmpFamily 			 where Name is null or Name = '';
delete from tmpGenus 				where Name is null or Name = '';
delete from tmpHigherTaxon 	where Name is null or Name = '';
delete from tmpInstCode 	   where Name is null or Name = '';
delete from tmpCollCode 	   where Name is null or Name = '';

# last, drop old tables and rename tmp tables, creating index

alter table tmpInstCode    rename to suggestInstCode, 	 modify unitCount int, add index (Name);
alter table tmpCollCode    rename to suggestCollCode,  	modify unitCount int, add index (Name);
alter table tmpTaxon       rename to suggestTaxon,       modify unitCount int, add index (Name);
alter table tmpGenus       rename to suggestGenus,       modify unitCount int, add index (Name);
alter table tmpFamily      rename to suggestFamily,      modify unitCount int, add index (Name);
alter table tmpHigherTaxon rename to suggestHigherTaxon, modify unitCount int, add index (Name);
alter table tmpVernacular  rename to suggestVernacular,  modify unitCount int, add index (Name);
alter table tmpCountry     rename to suggestCountry,     modify unitCount int, add index (Name);