/* Set starting parameters */
SET
	@CurrentYearStart = "2024-04-01";
SET
	@CurrentYearEnd = "2025-03-31";
SET
	@ExtractStart = "2024-12-01";
SET
	@ExtractEnd = "2024-12-31";
SET
	@JobID = 352;
SELECT
	TIME_TO_SEC(TOILBalance) INTO @Balance
FROM
	jobs
WHERE
	ID = @JobID;
	/* Retrieve just the extract range */
SELECT
	DAYNAME(DATE) AS DAY,
	DATE,
	StartingBalance,
	Worked,
	Shifts,
	`Leave`,
	LeaveNotes,
	Expected,
	Claimed,
	Difference,
	EndingBalance
FROM
	(

		/* Finalise some calculations, convert calculated decimal values to TIME values and give calculated columns meaningful names for the whole year  */
		SELECT
			`Date`,
			CONCAT(
				FLOOR(@Balance / 3600),
				":",
				LPAD(
					FLOOR(@Balance % 3600 / 60),
					2,
					"0"
				),
				":00"
			) AS StartingBalance,
			CAST(
				SEC_TO_TIME(
					IF(WorkedSeconds, WorkedSeconds, 0)
				) AS TIME
			) AS Worked,
			Shifts,
			CAST(
				SEC_TO_TIME(ExpectedSeconds * LeaveAllowance) AS TIME
			) AS `Leave`,
			LeaveNotes,
			CAST(
				SEC_TO_TIME(ExpectedSeconds) AS TIME
			) AS Expected,
			CAST(
				SEC_TO_TIME(
					(
						IF(
							ExpectedSeconds, ExpectedSeconds,
							0
						)* IF(
							LeaveAllowance, LeaveAllowance, 0
						)
					)+ IF(WorkedSeconds, WorkedSeconds, 0)
				) AS TIME
			) AS Claimed,
			CAST(
				SEC_TO_TIME(
					(
						ExpectedSeconds * IF(
							LeaveAllowance, LeaveAllowance, 0
						) + IF(WorkedSeconds, WorkedSeconds, 0)
					) - ExpectedSeconds
				) AS TIME
			) AS Difference,
			@Balance := @Balance +(
				(
					ExpectedSeconds * IF(
						LeaveAllowance, LeaveAllowance, 0
					) + IF(WorkedSeconds, WorkedSeconds, 0)
				) - ExpectedSeconds
			),
			CONCAT(
				FLOOR(@Balance / 3600),
				":",
				LPAD(
					FLOOR(@Balance % 3600 / 60),
					2,
					"0"
				),
				":00"
			) AS EndingBalance
		FROM
			(

				/* Retrieve the raw data for the entire year  */
				SELECT
					Dates.Date,
					SUM(
						IF(
							`leave`.ID,
							IF(
								Dates.Date = `From`,
								`First`,
								IF(Dates.Date = `To`, `Last`, 1)
							),
							NULL
						)
					) AS LeaveAllowance,
					WorkedSeconds,
					Shifts,
					GROUP_CONCAT(`Type` SEPARATOR " and ") AS LeaveNotes,
					TIME_TO_SEC(
						JSON_UNQUOTE(
							JSON_EXTRACT(
								WorkingPattern,
								CONCAT(
									"$.",
									DAYNAME(Dates.Date)
								)
							)
						)
					) AS ExpectedSeconds
				FROM
					(

						/* CTE to generate a list of dates for whole year */
						WITH
							RECURSIVE Date_Ranges AS (
								SELECT
									@CurrentYearStart AS DATE
								UNION ALL
								SELECT
									DATE + INTERVAL 1 DAY
								FROM
									Date_Ranges
								WHERE
									DATE & lt; @CurrentYearEnd
							)
						SELECT
							*
						FROM
							Date_Ranges
					) AS Dates
					LEFT JOIN
					/* Foreach day retrieve whether there is any leave recorded  */
					`leave` ON Dates.Date BETWEEN `From`
					AND `To`
					AND `leave`.JobID = @JobID
					LEFT JOIN
					/* For each day, calculate the total worked hours in seconds. Also, store the shift data in a JSON object  */
					(
						SELECT
							`checkins`.Date,
							SUM(
								TIME_TO_SEC(`End`)- TIME_TO_SEC(`Start`)
							) AS WorkedSeconds,
							JSON_ARRAYAGG(
								JSON_OBJECT(
									"ID", checkins.ID, "Start", checkins.`Start`,
									"End", checkins.`End`
								)
							) AS Shifts
						FROM
							`checkins`
						GROUP BY
							`checkins`.Date
					) AS `checkins` ON Dates.Date = `checkins`.Date
					INNER JOIN
					/* for each day, retrieve the applicable working pattern into a JSON object. as an inner join is used only dates that have a pattern defined will be retrieved */
					(
						SELECT
							`StartDate`,
							`EndDate`,
							JSON_OBJECT(
								"Monday", Monday, "Tuesday", Tuesday,
								"Wednesday", Wednesday, "Thursday",
								Thursday, "Friday", Friday, "Saturday",
								Saturday, "Sunday", Sunday
							) AS WorkingPattern
						FROM
							`pattern`
						WHERE
							JobID = @JobID
					) AS `pattern` ON Dates.Date BETWEEN `pattern`.StartDate
					AND `pattern`.EndDate
				GROUP BY
					Dates.Date
			) AS CurrentYearCalculated
	) AS EXTRACT
HAVING
	Extract.Date BETWEEN @ExtractStart
	AND @ExtractEnd