## first, create required tables if they don't exist
create table if not exists lastUpdated (
	id tinyint default 1 primary key,
	lastRun timestamp null,
	max_bioRecordModified datetime null);

insert ignore into lastUpdated(max_bioRecordModified)
values('2000-01-01');

create table if not exists bioRecordList (
	brKey int not null primary key);

create table if not exists removedRecords (
	brKey int not null primary key);

create table if not exists updatedRecords (
	brKey int not null primary key,
    bioRecordModifiedDate datetime);

create table if not exists cormList (
    service_uuid varchar(50) primary key,
    serviceKey int,
    european_data_only BOOL,
    key(serviceKey), index(european_data_only)
);

# then, fill in data into corm_datasource
update corm_datasource
join GBIF.service on service_uuid = serviceuuid
set corm_datasource.serviceKey = service.serviceKey;

delete from corm_datasource
where serviceKey is null;

alter table corm_datasource
	add key (serviceKey),
	add index (european_data_only);