-- params
set @projectName = 'Správa kontaktů a souvisejících zápisů (IBM) - Falsum';
set @iterationName = '2. iterace';
set @projectId = (select id from ppicha.project where name = (@projectName collate utf8_unicode_ci));
set @iterationId = (select id from ppicha.iteration where superProjectId = @projectId and name = (@iterationName collate utf8_unicode_ci));	
set @daysLookahead = 3;
set @daysLookbehind = 6;
set @silenceSteepnes = 0.1;
set @cliffSteepnes = 2;


-- variables
set @endDate = (select
					max(date_format(work_item.created, '%Y-%m-%d'))
				from
					ppicha.field_change,
					ppicha.work_item_change,
					ppicha.work_unit,
					ppicha.configuration_change,
					ppicha.work_item
				where
					field_change.name = 'status'
					and (field_change.newValue = 'Closed' or newValue = 'Invalid')
					and field_change.workItemChangeId = work_item_change.id
					and work_item_change.workItemId = work_unit.id
					and work_unit.iterationId = @iterationId
					and work_item_change.id = configuration_change.changeId
					and configuration_change.configurationId = work_item.id
				);

set @startDate = (select
						min(date_format(work_item.created, '%Y-%m-%d'))
					from
						ppicha.work_unit,
						ppicha.work_item
					where
						work_unit.id = work_item.id
						and work_unit.iterationId = @iterationId
					);
set @issuesCount = (select count(id) from ppicha.work_unit where work_unit.iterationId = @iterationId);
set @duration = (select datediff(@endDate, @startDate) + 1);
set @startDateFormatted = date_format(@startDate, '%Y-%m-%d');
set @endDateFormatted = date_format(@endDate, '%Y-%m-%d');
set @dailyIdeal = (select @issuesCount / @duration);
set @silenceThreshold = @silenceSteepnes * @issuesCount;
set @cliffThreshold = @cliffSteepnes * @daysLookahead * @dailyIdeal;

select
	'closedOn',
    'lookbehind',
    'silenceThreshold',
    'lookahead',
    'cliffThreshold',
    'detected'

union

-- query
select
	-- subdate(results.closedOn, interval (@daysLookbehind - 1) day) as intervalStart,
	-- adddate(results.closedOn, interval @daysLookahead day) as intervalEnd,
	results.closedOn,
    -- results.dayIndex,
    -- @daysLookbehind,
    results.lookbehind,
    @silenceThreshold,
    results.lookahead ,
    @cliffThreshold,
    -- if(results.dayIndex >= @daysLookbehind, true, false) relevance,
    -- if(results.lookbehind <= @silenceThreshold, true, false) silence,
    -- if(results.lookahead >= @cliffThreshold, true, false) cliff,
    if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) detected
from
	(
    select
		@curRow := @curRow + 1 as dayIndex,
		days.selected_date as closedOn,
		ifnull(dataPoints.lookbehind, 0) as lookbehind,
		ifnull(dataPoints.lookahead, 0) as lookahead
	from
		-- all days beteween start and end to fill in the gaps
		(
        select
			*
		from
			-- calendar
			(
			select
				adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
			from
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
				(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
			)
            v
		where
			v.selected_date between @startDateFormatted and @endDateFormatted
		)
        days
	left join
		-- actual datapoints; full outer join on lookahead and lookbehind (lookahead LEFT JOIN lookbehind UNION lookahead RIGHT JOIN lookbehind)
		(
		select
			ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint,
			ifnull(lookbehind.closedCount, 0) as lookbehind,
			ifnull(lookahead.closedCount, 0) as lookahead
		from
			-- look x days ahead (excluding current)
			(
			select
				days.selected_date as dataPoint,
				count(distinct work_unit.id) as closedCount
			from
				-- all days beteween start and end to fill in the gaps
				(
				select
					*
				from
					-- calendar
					(
					select
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
					from
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
					)
                    v
				where
					v.selected_date between @startDateFormatted and @endDateFormatted
				)
                days,
				ppicha.work_item,
				ppicha.configuration_change,
				ppicha.work_item_change,
				ppicha.work_unit,
				ppicha.field_change,
				ppicha.status,
				ppicha.project_instance,
				ppicha.project,
				ppicha.status_classification
			where
				date_format(work_item.created, '%Y-%m-%d') > days.selected_date
				and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day)
				and work_item.id = configuration_change.configurationId
				and configuration_change.changeId = work_item_change.id
				and work_item_change.workItemId = work_unit.id
				and work_unit.iterationId = @iterationId
				and work_item_change.id = field_change.workItemChangeId
				and field_change.name = 'status'
				and field_change.newValue = status.name
				and status.projectInstanceId = project_instance.id
				and project_instance.projectId = @projectId
				and status.classId = status_classification.id
				and status_classification.superClass = 'CLOSED'
			group by
				dataPoint
			)
            lookahead
		left join
			-- look y days behind (including current)
			(
			select
				days.selected_date as dataPoint,
				count(distinct work_unit.id) as closedCount
			from
				-- all days beteween start and end to fill in the gaps
				(
				select
					*
				from
					-- calendar
					(
					select
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
					from
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
					)
                    v
				where
					v.selected_date between @startDateFormatted and @endDateFormatted
				)
                days,
				ppicha.work_item,
				ppicha.configuration_change,
				ppicha.work_item_change,
				ppicha.work_unit,
				ppicha.field_change,
				ppicha.status,
				ppicha.project_instance,
				ppicha.project,
				ppicha.status_classification
			where
				date_format(work_item.created, '%Y-%m-%d') <= days.selected_date
				and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day)
				and work_item.id = configuration_change.configurationId
				and configuration_change.changeId = work_item_change.id
				and work_item_change.workItemId = work_unit.id
				and work_unit.iterationId = @iterationId
				and work_item_change.id = field_change.workItemChangeId
				and field_change.name = 'status'
				and field_change.newValue = status.name
				and status.projectInstanceId = project_instance.id
				and project_instance.projectId = @projectId
				and status.classId = status_classification.id
				and status_classification.superClass = 'CLOSED'
			group by
				dataPoint
			)
            lookbehind
			on lookahead.dataPoint = lookbehind.dataPoint
		
		union
		
		select
			ifnull(lookbehind.dataPoint, lookahead.dataPoint) as dataPoint,
			ifnull(lookbehind.closedCount, 0) as lookbehind,
			ifnull(lookahead.closedCount, 0) as lookahead
		from
			-- look x days ahead (excluding current)
			(
			select
				days.selected_date as dataPoint,
				count(distinct work_unit.id) as closedCount
			from
				-- all days beteween start and end to fill in the gaps
				(
				select
					*
				from
					-- calendar
					(
					select
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
					from
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
					)
					v
				where
					v.selected_date between @startDateFormatted and @endDateFormatted
				)
				days,
				ppicha.work_item,
				ppicha.configuration_change,
				ppicha.work_item_change,
				ppicha.work_unit,
				ppicha.field_change,
				ppicha.status,
				ppicha.project_instance,
				ppicha.project,
				ppicha.status_classification
			where
				date_format(work_item.created, '%Y-%m-%d') > days.selected_date
				and date_format(work_item.created, '%Y-%m-%d') <= adddate(days.selected_date, interval @daysLookahead day)
				and work_item.id = configuration_change.configurationId
				and configuration_change.changeId = work_item_change.id
				and work_item_change.workItemId = work_unit.id
				and work_unit.iterationId = @iterationId
				and work_item_change.id = field_change.workItemChangeId
				and field_change.name = 'status'
				and field_change.newValue = status.name
				and status.projectInstanceId = project_instance.id
				and project_instance.projectId = @projectId
				and status.classId = status_classification.id
				and status_classification.superClass = 'CLOSED'
			group by
				dataPoint
			)
			lookahead
		right join
			(
            -- look y days behind (including current)
			select
				days.selected_date as dataPoint,
				count(distinct work_unit.id) as closedCount
			from
				-- all days beteween start and end to fill in the gaps
				(
				select
					*
				from
					-- calendar
					(
					select
						adddate(@startDateFormatted, t3.i * 1000 + t2.i * 100 + t1.i * 10 + t0.i) selected_date
					from
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
						(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
					)
					v
				where
					v.selected_date between @startDateFormatted and @endDateFormatted
				) 
				days,
				ppicha.work_item,
				ppicha.configuration_change,
				ppicha.work_item_change,
				ppicha.work_unit,
				ppicha.field_change,
				ppicha.status,
				ppicha.project_instance,
				ppicha.project,
				ppicha.status_classification
			where
				date_format(work_item.created, '%Y-%m-%d') <= days.selected_date
				and date_format(work_item.created, '%Y-%m-%d') > subdate(days.selected_date, interval @daysLookbehind day)
				and work_item.id = configuration_change.configurationId
				and configuration_change.changeId = work_item_change.id
				and work_item_change.workItemId = work_unit.id
				and work_unit.iterationId = @iterationId
				and work_item_change.id = field_change.workItemChangeId
				and field_change.name = 'status'
				and field_change.newValue = status.name
				and status.projectInstanceId = project_instance.id
				and project_instance.projectId = @projectId
				and status.classId = status_classification.id
				and status_classification.superClass = 'CLOSED'
			group by
				dataPoint
			)
			lookbehind
			on lookahead.dataPoint = lookbehind.dataPoint
		)
		dataPoints
		on days.selected_date = dataPoints.dataPoint
	-- row index
    join
		(select @curRow := 0) r
	order by
		days.selected_date
	)
    results
-- actual detection
-- where
-- 	if(results.dayIndex >= @daysLookbehind and results.lookbehind <= @silenceThreshold and results.lookahead >= @cliffThreshold, true, false) = true