/* 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 < @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