1DROP VIEW thread_view;
2
3CREATE VIEW thread_view
4  AS
5    SELECT thread.id, thread.original_poster, thread.coordinator, thread.closed_by,
6      thread.private, thread.lock, thread.type,
7      thread.create_date, thread.last_activity_date, thread.last_modification_date, thread.close_date,
8      thread.title, thread.content, thread.content_html,
9      thread.task_count, thread.task_count_closed, thread.task_sum_cost,
10      label.id AS label_id,
11      label.name AS label_name,
12      (SELECT MAX(priority) FROM task_view WHERE task_view.thread_id = thread.id) AS priority,
13      CASE WHEN thread.state = 'proposal' THEN 0
14      WHEN thread.state = 'opened' AND thread.task_count = 0 THEN 1
15      WHEN thread.state = 'opened' THEN 2
16      WHEN thread.state = 'closed' THEN 3
17      WHEN thread.state = 'rejected' THEN 4 END AS sort,
18      CASE WHEN thread.state = 'opened' AND thread.task_count > 0 AND thread.task_count = thread.task_count_closed THEN 'done'
19      ELSE thread.state END AS state,
20      (SELECT COUNT(*) FROM thread_comment WHERE type = 'cause' AND thread_id=thread.id) AS cause_count,
21      (SELECT COUNT(*) FROM thread_comment WHERE type = 'risk' AND thread_id=thread.id) AS risk_count,
22      (SELECT COUNT(*) FROM thread_comment WHERE type = 'opportunity' AND thread_id=thread.id) AS opportunity_count
23    FROM thread
24      LEFT JOIN thread_label ON thread.id = thread_label.thread_id
25      LEFT JOIN label ON label.id = thread_label.label_id;
26