ALTER TABLE .`sequenceaccession` 
ADD COLUMN `boldnumber` VARCHAR(45) NULL ;
ALTER TABLE .`sha1responses` 
ADD COLUMN `datasetdir` VARCHAR(255) NULL ;
ALTER TABLE `preparation` 
ADD COLUMN `preparationMaterials` VARCHAR(455) NULL ;
ALTER TABLE `preparation` 
CHANGE COLUMN `preparationMethod` `preparationType` VARCHAR(255) NULL DEFAULT NULL ;
ALTER TABLE `loan` 
ADD COLUMN `blockedUntilDate` DATE ;
ALTER TABLE `loan` 
ADD COLUMN `disposition_clean` VARCHAR(45);
--changes november 2015
ALTER TABLE `multimedia` 
ADD COLUMN `relatedResource` VARCHAR(355) NULL DEFAULT NULL;
--march 2016
ALTER TABLE `measurementorfacts` 
CHANGE COLUMN `parameter` `parameter` VARCHAR(455) NULL DEFAULT NULL ;

-- changes for revision 327
create table namedarea(
	namedareaid int AUTO_INCREMENT primary key,
	fk_occurrenceid int,
	sequence tinyint,
	class varchar(255),
	name varchar(255),
	constraint fk_namedareas1 FOREIGN KEY (fk_occurrenceid) REFERENCES rawoccurrence (occurrenceid) ON DELETE CASCADE ON UPDATE CASCADE
);
-- changes for revision 330 (guid moved from rawoccurrence/occurrence to tripleidstore)
alter table tripleidstore
add column guid varchar(255) after tripleidstoreid,
add index idxTripleidstoreGUID(guid); 

update tripleidstore
join rawoccurrence on fk_tripleidstoreid=tripleidstoreid
set tripleidstore.guid = rawoccurrence.guid
where rawoccurrence.guid is not null

alter table rawoccurrence drop column guid;
alter table occurrence drop column guid;

-- changes for revision 355 (ipen, hardiness and establishmentMeans added to harvesting)
alter table rawoccurrence
	add column ipen varchar(255),
	add column hardiness char(1);

alter table occurrence
	add column ipen varchar(255),
	add column hardiness char(1);

-- changes for revision 360 (comment, context, creator and created date for multimedia added to harvesting)
alter table multimedia
	add column comment varchar(512),
	add column context varchar(512),
	add column creator varchar(255),
	add createdDate varchar(50);
	
-- changes for revision 370 (harvesting of biotope/habitat added)
ALTER TABLE rawoccurrence
	ADD COLUMN biotope TEXT NULL AFTER locality;
	
-- changes in rev372 (column comment in multimedia was too small)
ALTER TABLE multimedia
	CHANGE COLUMN comment comment VARCHAR(4096) NULL DEFAULT NULL;

-- update of old higher taxon ranks/names for rev373 (whith lowercase letters)
update rawhigher
set higherrank=CONCAT(UCASE(LEFT(higherrank, 1)), LCASE(SUBSTRING(higherrank, 2))),
	highertaxon=CONCAT(UCASE(LEFT(highertaxon, 1)), LCASE(SUBSTRING(highertaxon, 2)))

-- changes in rev374 (harvesting of abcd:acquiredFrom, see tic.bgbm.org/redmine/issues/551)
ALTER TABLE rawoccurrence
	ADD COLUMN acquiredFrom VARCHAR(255) NULL AFTER hardiness;

-- changes in rev376 (harvesting of abcd:AccessionStatus, see http://tic.bgbm.org/redmine/issues/568)
ALTER TABLE rawoccurrence
	ADD COLUMN accessionStatus VARCHAR(255) NULL AFTER acquiredFrom;

-- changes in rev389, (deprecation of Netherland Antilles, see http://tic.bgbm.org/redmine/issues/613)
INSERT IGNORE INTO `country` (`countryid`, `country`, `iso`) VALUES
	(267, 'Bonaire, Sint Eustatius and Saba', 'BQ'),
	(268, 'Sint Maarten', 'SX'),
	(269, 'Curaçao', 'CW');
UPDATE country SET country='Netherlands Antilles (no longer exists)' WHERE iso='AN';

INSERT IGNORE INTO `countryToOceanOrContinent` (`fk_countryid`, `fk_oceanOrContinentid`) VALUES
	(267, 15),
	(268, 15),
	(269, 15);

-- changes in rev390 (harvesting of abcd:DonorCategory, see http://tic.bgbm.org/redmine/issues/619)
ALTER TABLE rawoccurrence
	ADD COLUMN donorCategory CHAR(1) NULL AFTER accessionStatus;

-- restrict length of accessionStatus to char(1), since it's just a single character
ALTER TABLE rawoccurrence
	MODIFY accessionStatus CHAR(1) NULL;
	
-- rev 399: New column in basket
ALTER TABLE user_shopping_cart
      ADD COLUMN tripleidstoreid INT NOT NULL DEFAULT NULL AFTER institution_code;

-- Swasiland renamed to Eswatini
UPDATE country SET country='Eswatini' WHERE iso='SZ'

-- changes for revision 408 (harvesting of biotope/habitat added to occurrence table)
ALTER TABLE occurrence
	ADD COLUMN biotope TEXT NULL AFTER locality;

UPDATE occurrence o
JOIN rawoccurrence r ON r.occurrenceid=o.occurrenceid
SET o.biotope=r.biotope
WHERE r.biotope IS NOT NULL

--changes for revision 428 (cleaning country/continent tables)
--change African subregions to Africa and remove African subregions
DELETE FROM countryToOceanOrContinent WHERE fk_countryid IN (32, 83, 86, 96, 102, 113, 115, 183, 193, 198, 211, 265) AND fk_oceanOrContinentid = 19;
DELETE FROM countryToOceanOrContinent WHERE fk_countryid IN (184, 248) AND fk_oceanOrContinentid = 20;
DELETE FROM countryToOceanOrContinent WHERE fk_countryid IN (27, 151, 207, 249);
DELETE FROM country WHERE countryid IN (27, 151, 207, 249);
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 12 WHERE fk_oceanOrContinentid IN (17, 18, 19, 20, 164);
DELETE FROM oceanOrContinent WHERE oceanOrContinentid IN (17, 18, 19, 20, 164);

--add column 'category' to oceanOrContinent
ALTER TABLE `oceanOrContinent`
	ADD COLUMN `category` VARCHAR(45) NULL AFTER `type`,
	ADD INDEX `category` (`category`);
UPDATE oceanOrContinent SET category = 'continent' WHERE oceanOrContinentid IN (7, 10, 11, 12, 14, 15, 16, 176);	
UPDATE oceanOrContinent SET category = 'other' WHERE oceanOrContinentid IN (8, 9, 13);
UPDATE oceanOrContinent SET category = 'ocean' WHERE oceanOrContinentid IN (1, 2, 3, 4, 5, 6);
UPDATE oceanOrContinent SET category = 'sea' WHERE category IS NULL;

--update continent - country relationships
DELETE FROM countryToOceanOrContinent WHERE fk_countryid = 55 AND fk_oceanOrContinentid = 12; #Gibraltar
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 11 WHERE fk_countryid IN (246, 259, 48, 194, 220, 164, 28); #Armenia, Azerbaijan, Georgia, Kazakhstan, Turkey, Eurasia, Russia
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 14 WHERE fk_countryid = 128 AND fk_oceanOrContinentid = 15; #Aruba
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 15 WHERE fk_countryid = 172 AND fk_oceanOrContinentid = 14; #Cuba
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 10 WHERE fk_countryid = 167 AND fk_oceanOrContinentid = 9; #Cyprus
UPDATE countryToOceanOrContinent SET fk_oceanOrContinentid = 7 WHERE fk_countryid = 92; #World
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (17, 176); #Bouvet Island
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (47, 10); #French Guyana
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (48, 10); #Georgia
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (43, 10); #Guadaloupe
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (65, 176); #Heard Island and McDonald Islands
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (261, 16); #Indonesia
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (194, 10); #Kazakhstan
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (117, 10); #Martinique
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (74, 14); #Panama
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (32, 10); #Réunion
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (40, 176); #South Georgia and the South Sandwich Islands
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (220, 10); #Turkey
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (164, 10); #Eurasia
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (230, 12); #French Southern Territories
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (230, 176); #French Southern Territories
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (138, 15); #French Southern Territories
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (28, 10); #Eurasia
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (90, 14); #America
INSERT INTO countryToOceanOrContinent (fk_countryid, fk_oceanOrContinentid) VALUES (90, 15); #America

-- remove duplicates and countries, that no longer exist
DELETE FROM countryToOceanOrContinent WHERE fk_countryid IN (26, 236, 266, 267, 268, 269);
DELETE FROM country WHERE countryid IN (26, 236, 258, 266, 267, 268, 269);


-- Changes to user_shopping_cart (https://git.bgbm.org/ggbn/bhit/-/issues/106)
ALTER TABLE `user_shopping_cart`
	ADD COLUMN `guid` VARCHAR(455) NULL AFTER `user_id`,
	ADD INDEX `guid` (`guid`);
ALTER TABLE `user_shopping_cart`
	ALTER `unit_id` DROP DEFAULT;
ALTER TABLE `user_shopping_cart`
	CHANGE COLUMN `unit_id` `unit_id` VARCHAR(50) NULL COLLATE 'utf8mb3_bin' AFTER `guid`;
ALTER TABLE `user_shopping_cart`
	CHANGE COLUMN `unit_id` `unit_id` VARCHAR(455) NULL DEFAULT NULL COLLATE 'utf8mb3_bin' AFTER `guid`; /* wasn't long enough */
