# 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
        limit 100000;
        # remove records from to do list
        delete from tmpBioRecords
        limit 100000;
        # count rows
        select count(*) into cnt from tmpBioRecords;
    end while;
END $$

DELIMITER ;


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


# 2. Then, create a temporary table that contains all data that have to be merged, merge main table
/*create temporary table tmpBioRecords like bioRecord;
alter table tmpBioRecords disable keys;

insert into tmpBioRecords
select bioRecord.*
from syn_delta.bioRecord
straight_join mergeList  on brKey = bioRecordKey;
              			          
call mergeBioRecords();*/

/* SHORTCUT version for new (i.e. empty) cache */
insert into bioRecord
select bioRecord.*
from syn_delta.bioRecord
straight_join mergeList  on brKey = bioRecordKey;


# 3. Now, create temporary tables that contain lists of all dependent objects that need to be merged
create temporary table tmpTaxa
select distinct bioRecordTaxonKey from bioRecord;
alter table tmpTaxa add key (bioRecordTaxonKey);

create temporary table tmpResources
select distinct bioRecordResourceKey as resKey from bioRecord;
alter table tmpResources add key(resKey);

create temporary table tmpCountries
select distinct bioRecordCountryCode as couCode from bioRecord;
alter table tmpCountries add key(couCode);


# 4. Merge taxon tables, but preserve unitCounts
create temporary table tmpTaxon
select taxon.*
from syn_delta.taxon
straight_join tmpTaxa on taxonKey = bioRecordTaxonKey;

alter table tmpTaxon
  add key (taxonKey);

update tmpTaxon
join taxon on taxon.taxonKey = tmpTaxon.taxonKey
set tmpTaxon.unitCount = taxon.unitCount;

replace low_priority into taxon
select * from tmpTaxon;

replace low_priority into higherTaxon
select higherTaxon.*
from syn_delta.higherTaxon
straight_join tmpTaxon on higherTaxon.TaxonKey = tmpTaxon.taxonKey;

replace low_priority into vernacularName
select vernacularName.*
from syn_delta.vernacularName
straight_join tmpTaxon on vernacularTaxonKey = taxonKey;


## 5. Finally, merge all other dependant tables
replace low_priority into resource
select resource.*
from syn_delta.resource
straight_join tmpResources on resourceKey = resKey;

replace low_priority into isoCountry
select isoCountry.*
from syn_delta.isoCountry
straight_join tmpCountries on isoCountryCode = couCode;

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