-- create(drop and create if exists) stored procedure for importing data into image_source table

DROP procedure if exists import_image_source;
delimiter //
create procedure import_image_source (source_name varchar(80), url varchar(255), contact_email varchar(80), contributor_name varchar(80))
begin
insert into image_source (source_name, url, contact_email, contributor_name) values (source_name, url, contact_email, contributor_name);
select last_insert_id();
end //


-- create(drop and create if exists) stored procedure for importing data into image_source_version table

DROP procedure if exists import_image_source_version;
delimiter //
create procedure import_image_source_version (image_source_id int(11), source_version varchar(45), contribution_date date, publication_id varchar(45))
begin
insert into image_source_version (image_source_id, source_version, contribution_date, publication_id) values (image_source_id, source_version, contribution_date, publication_id);
select last_insert_id();
end //

-- create(drop and create if exists) stored procedure for importing data into curator table

DROP procedure if exists import_curator;
delimiter //
create procedure import_curator (firstname varchar(80), lastname varchar(80), primary_email varchar(80), affiliation varchar(80))
begin
insert into curator (firstname, lastname, primary_email, affiliation) values (firstname, lastname, primary_email, affiliation);
select last_insert_id();
end //

-- create(drop and create if exists) stored procedure for importing data into taxon table

DROP procedure if exists import_taxon;
delimiter //
create procedure import_taxon (species_id varchar(45), species_name varchar(80), genus varchar(80))
begin
insert into taxon (species_id, species_name, genus) values (species_id, species_name, genus);
select last_insert_id();
end //

-- create(drop and create if exists) stored procedure for importing data into annotated_image table

DROP procedure if exists import_annotated_image;
delimiter //
create procedure import_annotated_image (curator_id int(11), image_source_version_id int(11), taxon_id int(11), image_path varchar(255), collection_location varchar(255), collection_date date, ip_comment varchar(255), doi varchar(45), source_db_name varchar(45), source_db_id varchar(30), comments varchar(255))
begin
insert into annotated_image (curator_id, image_source_version_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments) values (curator_id, image_source_version_id, taxon_id, image_path, collection_location, collection_date, ip_comment, doi, source_db_name, source_db_id, comments);
select last_insert_id();
end //

-- create(drop and create if exists) stored procedure for importing data into annotated_term table

DROP procedure if exists import_annotated_term;
delimiter //
create procedure import_annotated_term (keyword varchar(80), ontology_term_id varchar(80))
begin
insert into annotated_term (keyword, ontology_term_id) values (keyword, ontology_term_id);
select last_insert_id();
end //

-- create(drop and create if exists) stored procedure for importing data into annotated_term_image table

DROP procedure if exists import_annotated_term_image;
delimiter //
create procedure import_annotated_term_image (annotated_term_id int(11), annotated_image_id int(11))
begin
insert into annotated_term_image (annotated_term_id, annotated_image_id) values (annotated_term_id, annotated_image_id);
select last_insert_id();
end //