DELIMITER $$

DROP PROCEDURE IF EXISTS addCollections $$
CREATE PROCEDURE addCollections ()
BEGIN
	DECLARE resKey INT;
  DECLARE colKey INT;
	DECLARE instCode, collCode varchar(255);
	DECLARE done INT DEFAULT 0m;

  # get a list of resources that don't have a collection associated with
	DECLARE cur CURSOR FOR
    select distinct bioRecordResourceKey, institution_code, collection_code
    from bioRecord
    where bioRecordCollectionKey = 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN cur;

  # now loop through the result set and create new collection entries
	REPEAT
		fetch cur into resKey, instCode, collCode;
		IF NOT done THEN
       # create a collection entry
       INSERT INTO collection
       VALUES (NULL, resKey, instCode, collCode);
       # get the collection ID
       SET colKey = LAST_INSERT_ID();
       # now fill in the new id into the corresponding bioRecords
       UPDATE bioRecord
       SET bioRecordCollectionKey = colKey
       WHERE bioRecordResourceKey = resKey
         and institution_code = instCode
         and collection_code = collCode;
		end if;
	UNTIL done END REPEAT;

	CLOSE cur;
END $$

DELIMITER ;

# create a table for collections if it doesn't exist yet
create table if not exists collection(
  collectionKey int auto_increment primary key,
  resourceKey int not null,
  instCode varchar(255),
  collCode varchar(255),
  key (resourceKey), key (instCode), key (collCode)
);

# first, fill in the id for the collections we already have
update bioRecord
join collection on bioRecordResourceKey = resourceKey
               and institution_code     = instCode
               and collection_code      = collCode
set bioRecordCollectionKey = collectionKey;

# now, run the procedure that adds collection entries
CALL addCollections();
