--issue_status_changes, a view which shows all issue status changes
CREATE OR REPLACE VIEW issue_status_changes AS
  SELECT *
  FROM dblink('host=localhost dbname=redmine user=root password=123456',
  '
  SELECT
    i.id AS issue_id,
    s1.name AS old_status,
    s2.name AS new_status,
    j.created_on AS created_on
  FROM journal_details AS jd
  INNER JOIN journals AS j ON j.id = jd.journal_id AND jd.property = ''attr'' AND jd.prop_key = ''status_id''
  INNER JOIN issues AS i ON i.id = j.journalized_id AND ''Issue'' = j.journalized_type
  INNER JOIN issue_statuses AS s1 ON s1.id = jd.old_value::integer
  INNER JOIN issue_statuses AS s2 ON s2.id = jd.value::integer
  ORDER BY j.created_on ASC
  ')
  AS t1(issue_id INTEGER, old_status VARCHAR, new_status VARCHAR, created_on TIMESTAMP);
---------------------------------------------------------------


--issue_statuses, a view which shows all issues and it's status names
CREATE OR REPLACE VIEW issue_statuses AS
  SELECT *
  FROM dblink('host=localhost dbname=redmine user=root password=123456',
  '
  SELECT i.id, s.name
  FROM issues AS i
  INNER JOIN issue_statuses AS s ON s.id = i.status_id
  ')
  AS t1(id INTEGER, "name" VARCHAR);
---------------------------------------------------------------


--data_flow_vw, the timer view
CREATE OR REPLACE VIEW data_flow_vw AS
SELECT
  CASE
  WHEN NOT EXISTS (SELECT issue_id FROM issue_status_changes WHERE issue_id = a.ticket) --no any issue changes
    THEN
      array_to_string(
        array(
          SELECT name
          FROM issue_statuses
          WHERE id = a.ticket
          LIMIT 1
        ), ', '
      )
  WHEN EXISTS (SELECT issue_id FROM issue_status_changes WHERE issue_id = a.ticket AND created_on >= a.start_time AND created_on <= a.end_time)
    THEN
      array_to_string(
        array(
          (
            SELECT old_status AS status
            FROM issue_status_changes
            WHERE issue_id = a.ticket
              AND created_on >= a.start_time
              AND created_on <= a.end_time
            ORDER BY created_on ASC
            LIMIT 1
          )
          UNION ALL
          (
            SELECT new_status AS status
            FROM issue_status_changes
            WHERE issue_id = a.ticket
              AND created_on >= a.start_time
              AND created_on <= a.end_time
            ORDER BY created_on ASC
          )
        ), ', '
      )
  WHEN EXISTS (SELECT issue_id FROM issue_status_changes WHERE issue_id = a.ticket AND created_on < a.start_time ORDER BY created_on DESC LIMIT 1)
    THEN
      array_to_string(
        array(
          SELECT new_status
          FROM issue_status_changes
          WHERE issue_id = a.ticket AND created_on < a.start_time
          ORDER BY created_on DESC
          LIMIT 1
        ), ', '
      )
  ELSE
    array_to_string(
      array(
        SELECT old_status
        FROM issue_status_changes
        WHERE issue_id = a.ticket AND created_on > a.end_time
        ORDER BY created_on ASC
        LIMIT 1
      ), ', '
    )
  END AS status,
  a.*
FROM
(
  SELECT
    wi.id,
    wi.wbegin AS start_int,
    wi.wend AS end_int,
    (di.daydate::TIMESTAMP + wi.wbegin * '1 second'::INTERVAL) AS start_time,
    (di.daydate::TIMESTAMP + wi.wend * '1 second'::INTERVAL) AS end_time,
    'Timer' AS type,
    array_to_string(
      array(
        SELECT a.name
        FROM t4u_projects AS a
        WHERE pj.parentkey like '%' || a.id || '%'
        ORDER BY a.id ASC
      ), ', '
    ) || ', ' || t.name AS info,
    t.name AS info_2,
    pn.surname AS developer,
    (wi.wend - wi.wbegin) AS duration,
    '' AS time_elapsed,
    pj.name AS project,
    substring(t.name FROM E'#(\\d+)')::INTEGER AS ticket,
    '' AS min_guess,
    '' AS best_guess,
    '' AS max_guess,
    '' AS calculated_estimate,
    wi.wcomment AS developer_comment,
    '' AS approver_comment,
    '' AS time_pass_requested,
    '' AS time_estimate_approved,
    '' AS time_estimate_approved_by,
    '' AS time_estimate_approved_at
  FROM t4u_workitems AS wi
  INNER JOIN t4u_dayinfos AS di ON wi.dayinfo_id = di.id
  INNER JOIN t4u_persons AS pn ON pn.id = di.person_id
  INNER JOIN t4u_tasks AS t ON t.id = wi.task_id
  INNER JOIN t4u_projects AS pj ON pj.id = t.project_id
) AS a