create table activity (
	id int unsigned not null auto_increment primary key,
	name varchar(255) not null,
	isValid tinyint unsigned not null default 1
);
create table tour (
	id int unsigned not null auto_increment primary key,
	name varchar(255) not null,
	isComplete tinyint unsigned not null default 0
);
create table link_tour_activity (
	tourId int unsigned not null,
	actvityId int unsigned not null,
	primary key (tourId, actvityId),
	key k_actvityId (actvityId),
	constraint link_tour_activity_tourId_segment_id foreign key (tourId) references tour (id) on delete cascade,
	constraint link_tour_activity_actvityId_segment_tag_id foreign key (actvityId) references activity (id) on delete cascade 
);
create table import (
	id int unsigned not null auto_increment primary key,
	timestamp datetime not null,
	sourceFilename varchar(255) not null,
	isComplete tinyint unsigned not null default 0
);
create table segment (
	id int unsigned not null auto_increment primary key,
	importId int unsigned not null,
		key k_importId (importId),
		constraint segment_importId_import_id foreign key (importId) references import (id),
	activityId int unsigned not null,
		key k_activityId (activityId),
		constraint segment_activityId_activity_id foreign key (activityId) references activity (id),
	userId int unsigned not null,
		key k_userId (userId),
		foreign key (userId) references user (id),
	isValid tinyint unsigned not null default 1,
	startTimestamp datetime not null,
	endTimestamp datetime not null,
	startLongitude float(9, 6) not null,
	startLatitude float(8, 6) not null,
	endLongitude float(9, 6) not null,
	endLatitude float (8, 6) not null,
	distance float(6, 2) not null,
	maximumSpeed float(4, 2) not null,
	averageCadence tinyint unsigned null,
	averageHeartRate smallint unsigned null,
	maximumHeartRate smallint unsigned null
);
create table sample (
	id int unsigned not null auto_increment primary key,
	segmentId int unsigned not null,
		key k_segmentId (segmentId),
		foreign key (segmentId) references segment (id),
	isValid tinyint unsigned not null default 1,
	timestamp datetime not null,
	longitude float(9, 6) not null,
	latitude float(8, 6) not null,
	distance float(6, 2) not null,
	elevation float(6, 1) null,
	heartRate smallint unsigned null,
	cadence tinyint unsigned null
);

--
-- changed import status management
--
alter table import
	drop isComplete,
	add status enum('in progress','complete','error') not null default 'in progress';

--
-- added importId to activity
--
alter table activity
	add importId int unsigned not null after id,
		add key k_importId (importId),
		add constraint activity_importId_import_id foreign key (importId) references import (id);

--
-- moved user constraint from segment to import
--
alter table segment
	drop foreign key segment_ibfk_1,
	drop key k_userId,
	drop userId;
alter table import
	add userId int unsigned not null after id,
		add key k_userId (userId),
		add constraint import_userId_user_id foreign key (userId) references user (id);

--
-- flipped samples to be attached to an activity, instead of a segment
-- so a segment will just pull out a subset of it's activity's samples
-- based on timestamp
--
alter table sample
	drop foreign key sample_ibfk_1,
	drop key k_segmentId,
	drop segmentId,
	add activityId int unsigned not null after id,
		add key k_activityId (activityId),
		add constraint sample_activityId_activity_id foreign key (activityId) references activity (id);

--
-- added calories field to segment, even though it's a crapshoot and I
-- don't really care about the data.
--
alter table sample
	add caloriesBurned int unsigned after distance;
alter table sample
	drop caloriesBurned;
alter table segment
	add caloriesBurned int unsigned after distance;

--
-- added userId to activity so that custom-created activities (those not
-- created by an import) can still be tied to a user
--
alter table activity
	modify importId int unsigned null;
alter table activity
	add userId int unsigned not null after id,
		add key k_userId (userId),
		add constraint activity_userId_user_id foreign key (userId) references user (id);

--
-- renamed sample.distance to sample.elapsedDistance
--
alter table sample
	change distance elapsedDistance float(6, 2) not null;

--
-- changed nullability and scale of segment/sample columns to be more
-- flexible
--
alter table segment
	modify startLongitude double null,
	modify startLatitude double null,
	modify endLongitude double null,
	modify endLatitude double null,
	modify distance double null;
alter table sample
	modify longitude double null,
	modify latitude double null,
	modify elapsedDistance double null;

--
-- changed samples to be segment associatd, instead of activity
-- associated, even though an activity association is still needed
-- for importing
--
alter table sample
	drop foreign key sample_activityId_activity_id,
	drop key k_activityId,
	change activityId import_activityId int unsigned not null,
		add key k_import_activityId (import_activityId),
		add constraint sample_import_activityId_activity_id foreign key (import_activityId) references activity (id),
	add segmentId int unsigned null after import_activityId,
		add key k_segmentId (segmentId),
		add constraint sample_segmentId_segment_id foreign key (segmentId) references segment (id);

