--
-- updated ride_info view to include fatiguePoints computation
--
drop view if exists ride_info;
create ALGORITHM=UNDEFINED
	SQL SECURITY INVOKER
view `ride_info` AS
select ride.id AS id,
	ride.userId,
	(
		select username as username
		from user
		where id = ride.userId
	) as username,
	ride.routeId,
	route.name as routeName,
	route.mapUrl as routeMapUrl,
	ride.bikeId,
	(
		select name AS name
		from bike
		where id = ride.bikeId
	) AS bikeName,
	(
		select name AS name
		from point
		where id = route.startPointId
	) AS startPoint,
	(
		select name AS name
		from point
		where id = route.endPointId
	) AS endPoint,
	ride.startTimestamp,
	(
		startTimestamp
		+ interval (time_to_sec(timeElapsed)
		+ ifnull((
			select sum(time_to_sec(timeElapsed)) AS stopage
			from stop
			where rideId = ride.id
		),0)) second
	) AS endTimestamp,
	cast((round((mileage * 100),0) / 100) as decimal) AS mileage,
	ride.timeElapsed AS rideTimeElapsed,
	cast(ifnull((
		select sum(timeElapsed) AS totalTimeElapsed
		from stop
		where rideId = ride.id
	),0) as time) AS stopTimeElapsed,
	ride.maximumSpeed,
	cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10),0) / 10) as decimal) AS averageSpeed,
	(
		select count(0) AS `cnt`
		from stop
		where rideId = ride.id
	) AS stopCount,
	round(ifnull((
		select sum(mileage
				* exp(
					(unix_timestamp(ride.startTimestamp) - unix_timestamp(date_add(startTimestamp, interval time_to_sec(timeElapsed) / 2 second)))
					* ( ln(0.5) / (0.8 * 86400) )
				)
			)
		from ride r2
		where userId = ride.userId
			and startTimestamp < ride.startTimestamp
			and startTimestamp > date_add(ride.startTimestamp, interval -3 * 4 day)
	), 0) * 100) / 100 as fatiguePoints,
	ride.notes
from ride
	left outer join route on ride.routeId = route.id;

--
-- updated constants in the fatigue points computation to be a little shorter
--
drop view if exists ride_info;
create ALGORITHM=UNDEFINED
	SQL SECURITY INVOKER
view `ride_info` AS
select ride.id AS id,
	ride.userId,
	(
		select username as username
		from user
		where id = ride.userId
	) as username,
	ride.routeId,
	route.name as routeName,
	route.mapUrl as routeMapUrl,
	ride.bikeId,
	(
		select name AS name
		from bike
		where id = ride.bikeId
	) AS bikeName,
	(
		select name AS name
		from point
		where id = route.startPointId
	) AS startPoint,
	(
		select name AS name
		from point
		where id = route.endPointId
	) AS endPoint,
	ride.startTimestamp,
	(
		startTimestamp
		+ interval (time_to_sec(timeElapsed)
		+ ifnull((
			select sum(time_to_sec(timeElapsed)) AS stopage
			from stop
			where rideId = ride.id
		),0)) second
	) AS endTimestamp,
	cast((round((mileage * 100),0) / 100) as decimal) AS mileage,
	ride.timeElapsed AS rideTimeElapsed,
	cast(ifnull((
		select sum(timeElapsed) AS totalTimeElapsed
		from stop
		where rideId = ride.id
	),0) as time) AS stopTimeElapsed,
	ride.maximumSpeed,
	cast((round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10), 0) / 10) as decimal) AS averageSpeed,
	(
		select count(0) AS `cnt`
		from stop
		where rideId = ride.id
	) AS stopCount,
	round(ifnull((
		select sum(mileage
				* exp(
					(unix_timestamp(ride.startTimestamp) - unix_timestamp(date_add(startTimestamp, interval time_to_sec(timeElapsed) / 2 second)))
					* ( ln(0.5) / (0.5 * 86400) )
				)
			)
		from ride r2
		where userId = ride.userId
			and startTimestamp < ride.startTimestamp
			and startTimestamp > date_add(ride.startTimestamp, interval -2 day)
	), 0) * 100) / 100 as fatiguePoints,
	ride.notes
from ride
	left outer join route on ride.routeId = route.id;



--
-- updated mileage and average speed computations to cast to decimal at
-- the right place so they don't end up rounding to the nearest mile
--
drop view if exists ride_info;
create ALGORITHM=UNDEFINED
	SQL SECURITY INVOKER
view `ride_info` AS
select ride.id AS id,
	ride.userId,
	(
		select username as username
		from user
		where id = ride.userId
	) as username,
	ride.routeId,
	route.name as routeName,
	route.mapUrl as routeMapUrl,
	ride.bikeId,
	(
		select name AS name
		from bike
		where id = ride.bikeId
	) AS bikeName,
	(
		select name AS name
		from point
		where id = route.startPointId
	) AS startPoint,
	(
		select name AS name
		from point
		where id = route.endPointId
	) AS endPoint,
	ride.startTimestamp,
	(
		startTimestamp
		+ interval (time_to_sec(timeElapsed)
		+ ifnull((
			select sum(time_to_sec(timeElapsed)) AS stopage
			from stop
			where rideId = ride.id
		),0)) second
	) AS endTimestamp,
	cast(round(mileage * 100, 0) as decimal) / 100 AS mileage,
	ride.timeElapsed AS rideTimeElapsed,
	cast(ifnull((
		select sum(timeElapsed) AS totalTimeElapsed
		from stop
		where rideId = ride.id
	),0) as time) AS stopTimeElapsed,
	ride.maximumSpeed,
	cast(round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10), 0) as decimal) / 10 AS averageSpeed,
	(
		select count(0) AS `cnt`
		from stop
		where rideId = ride.id
	) AS stopCount,
	cast(round(ifnull((
		select sum(mileage
				* exp(
					(unix_timestamp(ride.startTimestamp) - unix_timestamp(date_add(startTimestamp, interval time_to_sec(timeElapsed) / 2 second)))
					* ( ln(0.5) / (0.5 * 86400) )
				)
			)
		from ride r2
		where userId = ride.userId
			and startTimestamp < ride.startTimestamp
			and startTimestamp > date_add(ride.startTimestamp, interval -2 day)
	), 0) * 100) as decimal) / 100 as fatiguePoints,
	ride.notes
from ride
	left outer join route on ride.routeId = route.id;


--
-- removed fatiguePoints column
--
drop view if exists ride_info;
create ALGORITHM=UNDEFINED
	SQL SECURITY INVOKER
view `ride_info` AS
select ride.id AS id,
	ride.userId,
	(
		select username as username
		from user
		where id = ride.userId
	) as username,
	ride.routeId,
	route.name as routeName,
	route.mapUrl as routeMapUrl,
	ride.bikeId,
	(
		select name AS name
		from bike
		where id = ride.bikeId
	) AS bikeName,
	(
		select name AS name
		from point
		where id = route.startPointId
	) AS startPoint,
	(
		select name AS name
		from point
		where id = route.endPointId
	) AS endPoint,
	ride.startTimestamp,
	(
		startTimestamp
		+ interval (time_to_sec(timeElapsed)
		+ ifnull((
			select sum(time_to_sec(timeElapsed)) AS stopage
			from stop
			where rideId = ride.id
		),0)) second
	) AS endTimestamp,
	cast(round(mileage * 100, 0) as decimal) / 100 AS mileage,
	ride.timeElapsed AS rideTimeElapsed,
	cast(ifnull((
		select sum(timeElapsed) AS totalTimeElapsed
		from stop
		where rideId = ride.id
	),0) as time) AS stopTimeElapsed,
	ride.maximumSpeed,
	cast(round((((mileage / time_to_sec(timeElapsed)) * 3600) * 10), 0) as decimal) / 10 AS averageSpeed,
	(
		select count(0) AS `cnt`
		from stop
		where rideId = ride.id
	) AS stopCount,
	ride.notes
from ride
	left outer join route on ride.routeId = route.id;
