-- When a job entered the queue and when it became ready to run

-- (oxygenhub#257): its pickup time is started_ms - ready_ms. queued_ms is

-- set when the job is made and when it is run again; ready_ms when a

-- runner claims it: the latest of queued_ms, its dependencies finishing

-- and its approval.

ALTER TABLE flow_jobs ADD COLUMN queued_ms BIGINT;

ALTER TABLE flow_jobs ADD COLUMN ready_ms BIGINT;

-- Earlier jobs: queued with their run, ready once the stages before

-- theirs had finished (a job runs again keeps its run's time: an estimate).

UPDATE flow_jobs SET queued_ms = (SELECT r.created_ms FROM flow_runs r WHERE r.id = flow_jobs.run_id);

UPDATE flow_jobs SET ready_ms = LEAST(started_ms, GREATEST(queued_ms, COALESCE(approved_ms, 0),

    COALESCE((SELECT MAX(x.finished_ms) FROM flow_jobs x WHERE x.run_id = flow_jobs.run_id AND x.depth < flow_jobs.depth), 0)))

  WHERE started_ms IS NOT NULL;

CREATE INDEX flow_jobs_started ON flow_jobs (started_ms);

CREATE INDEX flow_jobs_finished ON flow_jobs (finished_ms);