# Stored procedure for merging bioRecords
DELIMITER $$

DROP PROCEDURE IF EXISTS mergeBioRecords $$
CREATE PROCEDURE mergeBioRecords ()
BEGIN
    declare cnt int default 1;
    while (cnt > 0) do
        # get a chunk and merge it into bioRecord table
        replace low_priority into bioRecord
        select * from tmpBioRecords
        order by bioRecordKey
        limit 100000;
        # remove records from to do list
        delete from tmpBioRecords
        order by bioRecordKey
        limit 100000;
        # count rows
        select count(*) into cnt from tmpBioRecords;
    end while;
END $$

DELIMITER ;

# First, create temporary tables that contain all data that have to be merged
# and save taxonCounts for taxon entries that will be replaced
create table tmpBioRecords like syn_delta.bioRecord;
alter table tmpBioRecords disable keys;

insert into tmpBioRecords
select bioRecord.*
from mergeList
join syn_delta.bioRecord on brKey = bioRecordKey;

create temporary table tmpTaxa
select distinct bioRecordTaxonKey as taxonKey
from tmpBioRecords;
alter table tmpTaxa add key(taxonKey);

create temporary table tmpCollections
select distinct bioRecordCollectionKey as collectionKey
from tmpBioRecords;
alter table tmpCollections add key(collectionKey);

create temporary table tmpCountries
select distinct bioRecordCountryCode as countryCode
from tmpBioRecords;
alter table tmpCountries add key(countryCode);

create temporary table tmpTaxon
select taxon.*
from tmpTaxa
join syn_delta.taxon on taxon.taxonKey = tmpTaxa.taxonKey;
alter table tmpTaxon add key(taxonKey);

# 1. First, remove deleted bioRecords
delete low_priority from bioRecord
using syn_delta.removedRecords
join bioRecord on bioRecordKey = brKey;

# 2. Now, merge updated data into cache
update tmpTaxon
join taxon on taxon.taxonKey = tmpTaxon.taxonKey
set tmpTaxon.unitCount = taxon.unitCount;

replace low_priority into imageRecord
select imageRecord.*
from mergeList
join syn_delta.imageRecord on imageRecordBioRecordKey = brKey;

replace low_priority into collection
select collection.*
from tmpCollections
join syn_delta.collection on collection.collectionKey = tmpCollections.collectionKey;

replace low_priority into isoCountry
select isoCountry.*
from tmpCountries
join syn_delta.isoCountry on IsoCountryCode = countryCode;

replace low_priority into vernacularName
select vernacularName.*
from tmpTaxa
join syn_delta.vernacularName on vernacularTaxonKey = taxonKey;

replace low_priority into higherTaxon
select higherTaxon.*
from tmpTaxa
join syn_delta.higherTaxon on higherTaxon.TaxonKey = tmpTaxa.taxonKey;

replace low_priority into taxon
select *
from tmpTaxon;

# now main table
call mergeBioRecords();

# last: table resource
create temporary table tmpResources
select distinct bioRecordResourceKey as resourceKey
from bioRecord;
alter table tmpResources add key(resourceKey);

replace low_priority into resource
select resource.*
from tmpResources
join syn_delta.resource on resource.resourceKey = tmpResources.resourceKey;

# finally, clean up
drop table tmpBioRecords;
