DELIMITER $$

DROP PROCEDURE IF EXISTS addCollections $$
CREATE PROCEDURE addCollections ()
BEGIN
	DECLARE resKey INT;
  DECLARE colKey INT DEFAULT -1;
	DECLARE resName varchar(255);
	DECLARE done INT DEFAULT 0;

  # get a list of resources that don't have a collection associated with
	DECLARE res CURSOR FOR
		select distinct resourceKey, resourceName
		from bioRecord
		join resource on bioRecordResourceKey = resourceKey
    where protocol = 'BioCASe'
		  and (bioRecordCollectionKey = 0 or bioRecordCollectionKey is null);
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN res;

  # now loop through the result set and create new collection entries
	REPEAT
		fetch res into resKey, resName;
		IF NOT done THEN
       # create a collection entry
       INSERT INTO collection
       VALUES (colKey, NULL, resName);
       # memorize it in the corInsertedCollections table
       insert into corInsertedCollections
       values(colKey, resKey);
       # now fill in the new id into the corresponding bioRecords
       UPDATE bioRecord
       SET bioRecordCollectionKey = colKey
       WHERE bioRecordResourceKey = resKey
         and (bioRecordCollectionKey = 0 or bioRecordCollectionKey is null);
       # last not least: increment colKey
       SET colKey = colKey - 1;
		end if;
	UNTIL done END REPEAT;

	CLOSE res;

END $$

DELIMITER ;

# create a table for memorizing inserted collections if it doesn't exist yet
create table if not exists corInsertedCollections(
  colKey int primary key,
  resKey int not null,
  key(resKey)
);

# first, check if there are new records with no collection, but with a resource we
# already created a collection entry for. If so, fill in the collectionKey.
update bioRecord
join corInsertedCollections on bioRecordResourceKey = resKey
set bioRecordCollectionKey = colKey
where bioRecordCollectionKey = 0 or bioRecordCollectionKey is null;

# now, run the procedure
CALL addCollections();
