DROP VIEW IF EXISTS v_todolist;
CREATE VIEW v_todolist AS
SELECT td2.id, td2.owner_id, c.NAME AS cname, g.NAME AS gname, td2.title,
       td2.importance, td2.urgency, COALESCE(tk.cnt, 0) AS task, 
       td2.deadline, td2.done,
       td2.completed_tasks
FROM 
(
    SELECT id, owner_id, groups_id, category_code, category_locale,
           title, importance, urgency, deadline, done, 
           completed_tasks
      FROM todo
     WHERE groups_id = 0
       AND is_deleted = 'N'	-- 追加
  UNION
    SELECT td.id, ga.account_id AS owner_id, td.groups_id, td.category_code, td.category_locale,
           td.title, td.importance, td.urgency, td.deadline, td.done,
           td.completed_tasks
      FROM todo td
           JOIN groups_account ga ON ga.groups_id = td.groups_id
     WHERE td.groups_id != 0
       AND is_deleted = 'N'	-- 追加
) td2
  JOIN category c ON td2.category_code = c.code AND td2.category_locale = c.locale
  JOIN groups g   ON td2.groups_id = g.id
  LEFT OUTER JOIN (SELECT todo_id, COUNT(*) AS cnt 
                     FROM task 
                    WHERE is_deleted = 'N'  -- 追加(Taskにis_deletedを追加してから)
                    GROUP BY todo_id) tk
               ON td2.id = tk.todo_id;
