[Module Metadata]
AUTHOR=Sarah Edwards/mac4n6.com/@iamevltwin
MODULE_NOTES=Health Indoor Time of Day

[Database Metadata]
DATABASE=healthdb_secure.sqlite
PLATFORM=IOS
VERSIONS=10,11,12,13

[Query Metadata]
QUERY_NAME=health_workout_timeofday
ACTIVITY=Health Workout Time of Day
KEY_TIMESTAMP=START DATE

[SQL Query 11,12,13]
QUERY=
	SELECT
	    DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH') AS "START DATE",
	    DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH') AS "END DATE",
		CASE METADATA_VALUES.NUMERICAL_VALUE 
			WHEN 0.0 THEN "NIGHT" 
	        WHEN 1.0 THEN "DAY" 
		END "TIME OF DAY",
	    CASE WORKOUTS.ACTIVITY_TYPE 
	        WHEN 63 THEN "HIGH INTENSITY INTERVAL TRAINING (HIIT)" 
	        WHEN 37 THEN "INDOOR / OUTDOOR RUN" 
	        WHEN 3000 THEN "OTHER" 
	        WHEN 52 THEN "INDOOR / OUTDOOR WALK" 
	        WHEN 20 THEN "FUNCTIONAL TRAINING" 
	        WHEN 13 THEN "INDOOR CYCLE" 
	        WHEN 16 THEN "ELLIPTICAL" 
	        WHEN 35 THEN "ROWER" 
			ELSE "UNKNOWN" || "-" || WORKOUTS.ACTIVITY_TYPE 
	    END "WORKOUT TYPE", 
		WORKOUTS.DURATION / 60.00 AS "DURATION (IN MINUTES)", 
		WORKOUTS.TOTAL_ENERGY_BURNED AS "CALORIES BURNED", 
		WORKOUTS.TOTAL_DISTANCE AS "DISTANCE IN KILOMETERS",
		WORKOUTS.TOTAL_DISTANCE*0.621371 AS "DISTANCE IN MILES",  
		WORKOUTS.TOTAL_BASAL_ENERGY_BURNED AS "TOTAL BASEL ENERGY BURNED", 
		    CASE WORKOUTS.GOAL_TYPE 
		        WHEN 2 THEN "MINUTES" 
		        WHEN 0 THEN "OPEN" 
		    END "GOAL TYPE",
		WORKOUTS.GOAL AS "GOAL", 
		WORKOUTS.TOTAL_FLIGHTS_CLIMBED AS "FLIGHTS CLIMBED", 
		WORKOUTS.TOTAL_W_STEPS AS "STEPS" 
	FROM
	    SAMPLES 
	    LEFT OUTER JOIN
	        METADATA_VALUES 
	        ON METADATA_VALUES.OBJECT_ID = SAMPLES.DATA_ID 
	    LEFT OUTER JOIN
	        METADATA_KEYS 
	        ON METADATA_KEYS.ROWID = METADATA_VALUES.KEY_ID 
	    LEFT OUTER JOIN
	        WORKOUTS 
	        ON WORKOUTS.DATA_ID = SAMPLES.DATA_ID 
	WHERE
	    WORKOUTS.ACTIVITY_TYPE NOT NULL AND KEY IS "_HKPrivateWorkoutWasInDaytime"

[SQL Query 10]
QUERY=
	SELECT
	    DATETIME(SAMPLES.START_DATE + 978307200, 'UNIXEPOCH') AS "START DATE",
	    DATETIME(SAMPLES.END_DATE + 978307200, 'UNIXEPOCH') AS "END DATE",
		CASE METADATA_VALUES.NUMERICAL_VALUE 
			WHEN 0.0 THEN "NIGHT" 
	        WHEN 1.0 THEN "DAY" 
		END "TIME OF DAY",
	    CASE WORKOUTS.ACTIVITY_TYPE 
	        WHEN 63 THEN "HIGH INTENSITY INTERVAL TRAINING (HIIT)" 
	        WHEN 37 THEN "INDOOR / OUTDOOR RUN" 
	        WHEN 3000 THEN "OTHER" 
	        WHEN 52 THEN "INDOOR / OUTDOOR WALK" 
	        WHEN 20 THEN "FUNCTIONAL TRAINING" 
	        WHEN 13 THEN "INDOOR CYCLE" 
	        WHEN 16 THEN "ELLIPTICAL" 
	        WHEN 35 THEN "ROWER" 
			ELSE "UNKNOWN" || "-" || WORKOUTS.ACTIVITY_TYPE 
	    END "WORKOUT TYPE", 
		WORKOUTS.DURATION / 60.00 AS "DURATION (IN MINUTES)", 
		WORKOUTS.TOTAL_ENERGY_BURNED AS "CALORIES BURNED", 
		WORKOUTS.TOTAL_DISTANCE AS "DISTANCE IN KILOMETERS", 
		WORKOUTS.TOTAL_BASAL_ENERGY_BURNED AS "TOTAL BASEL ENERGY BURNED", 
		    CASE WORKOUTS.GOAL_TYPE 
		        WHEN 2 THEN "MINUTES" 
		        WHEN 0 THEN "OPEN" 
		    END "GOAL TYPE",
		WORKOUTS.GOAL AS "GOAL"
	FROM
	    SAMPLES 
	    LEFT OUTER JOIN
	        METADATA_VALUES 
	        ON METADATA_VALUES.OBJECT_ID = SAMPLES.DATA_ID 
	    LEFT OUTER JOIN
	        METADATA_KEYS 
	        ON METADATA_KEYS.ROWID = METADATA_VALUES.KEY_ID 
	    LEFT OUTER JOIN
	        WORKOUTS 
	        ON WORKOUTS.DATA_ID = SAMPLES.DATA_ID 
	WHERE
	    WORKOUTS.ACTIVITY_TYPE NOT NULL AND KEY IS "_HKPrivateWorkoutWasInDaytime"