with all_sprints as (
select
id,
name,
coalesce(activated_date, start_date) + (sprint_starting_grace_period())::INTERVAL as start_date,
coalesce(complete_date, finish_date) as finish_date,
state,
row_number () over (order by start_date desc) as row_number
from sprint
where state != ''FUTURE'' and start_date is not null and start_date < now() and (finish_date is not null or complete_date is not null)
order by start_date desc
),

sprints as (
select * from all_sprints where row_number <= depth_for_avg_velocity_sprint() * 1 + 6 order by start_date
),

scope as (
select * from ticket
where parent_task is null and (done_date is null or done_date > (select min(start_date) from sprints))
),

-- Generate ticket snaphsots
snapshots as (
select * from ticket_snapshots(ARRAY(select key from scope))
),

snapshot_on_sprint_start as (
select distinct on (h.id, s.id) h.id as issue_id, s.id as sprint, s.start_date, h.snapshot_created
from snapshots h, sprints s
where h.snapshot_created < s.start_date
order by h.id, s.id, h.snapshot_created desc
),

snapshot_on_sprint_finish as (
select distinct on (h.id, s.id) h.id as issue_id, s.id as sprint, s.finish_date, h.snapshot_created
from snapshots h, sprints s
where h.snapshot_created < s.finish_date
order by h.id, s.id, h.snapshot_created desc
),

-- Generate ready by date
ready_on_sprint_start as(
select sum(sn.story_points) as story_points, sprint, array_agg(issue_id) as tickets from snapshots sn
inner join snapshot_on_sprint_start
on sn.id = snapshot_on_sprint_start.issue_id
and sn.snapshot_created = snapshot_on_sprint_start.snapshot_created
where is_ready(sn) and sn.story_points IS NOT NULL
group by sprint
),

completed_on_sprint_finish as (
select sum(sn.story_points) as story_points, sprint from snapshots sn
inner join snapshot_on_sprint_finish
on sn.id = snapshot_on_sprint_finish.issue_id
and sn.snapshot_created = snapshot_on_sprint_finish.snapshot_created
where sn.done_date < snapshot_on_sprint_finish.finish_date and sprint = any(sn.sprints)
group by sprint
),

average_velocity as (
select s.id as sprint, state, finish_date, start_date,
AVG(sum(coalesce(story_points,0))::numeric) OVER (ORDER BY s.finish_date asc ROWS BETWEEN (select depth_for_avg_velocity_sprint()-1) PRECEDING AND CURRENT ROW) AS average_velocity
from sprints s left join completed_on_sprint_finish on completed_on_sprint_finish.sprint = s.id
group by s.id, s.finish_date, state, start_date
),

backlog_health as (
select sprint,
case when s.state = ''CLOSED'' then lag(s.average_velocity) OVER (ORDER BY s.start_date asc)
when s.state = ''ACTIVE'' then (select average_velocity from average_velocity where state = ''CLOSED'' order by finish_date ASC limit 1) end
as avg_velocity
from average_velocity s)

select s.name as "Sprint",
(case
when avg_velocity = 0 then 0
when story_points is null then 0
else round((r.story_points/avg_velocity)::numeric,1)
end) as "Backlog Health"
from sprints s
left join ready_on_sprint_start r on s.id = r.sprint
inner join backlog_health v on v.sprint = s.id
where row_number <= 6
order by start_date