--  --------------------------------------------------------
--  注意：
--      以下のコマンドは、ユーザーtodouserとして接続後
--      実行すること
--  --------------------------------------------------------
\encoding UTF8;
DROP TABLE IF EXISTS todo CASCADE;
CREATE TABLE todo
(
  id              SERIAL PRIMARY KEY,
  owner_id        INTEGER,
  title           TEXT,
  importance      INTEGER,
  urgency         INTEGER,
  deadline        DATE,
  done            TEXT,
  category_code   TEXT NOT NULL,
  category_locale TEXT NOT NULL,
  groups_id       INTEGER NOT NULL
);
  
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale,
                 groups_id)
VALUES('UI設計', 1, 1, 1, '2023-10-01', 'N', '10', 'ja', 0);
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale,
                 groups_id)
VALUES('定例BBQ大会開催', 1, 1, 0, '2023-10-02', 'N', '30', 'ja', 102);
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale,
                 groups_id)
VALUES('月末締切', 2, 1, 1, '2023-10-03', 'N', '10', 'ja', 0);
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale,
                 groups_id)
VALUES('DB設計', 3, 1, 1, '2023-10-04', 'N', '10', 'ja', 0);
  
DROP TABLE IF EXISTS task;
CREATE TABLE task
(
  id           SERIAL PRIMARY KEY,
  todo_id      INTEGER,
  title        TEXT,
  deadline     DATE,
  done         TEXT
);
  
INSERT INTO task(todo_id, title, deadline, done) VALUES(2, '日時決め',NULL,'N');
INSERT INTO task(todo_id, title, deadline, done) VALUES(2, '場所検討',NULL,'N');
INSERT INTO task(todo_id, title, deadline, done) VALUES(2, '参加者募集HP作成',NULL, 'N');

DROP TABLE IF EXISTS attached_file;
CREATE TABLE attached_file(
    id             SERIAL PRIMARY KEY,
    todo_id        INTEGER,
    file_name      TEXT,
    create_time    TEXT,
    note           TEXT
);

DROP TABLE IF EXISTS account;
CREATE TABLE account
(
    id           SERIAL PRIMARY KEY,
    login_id     TEXT UNIQUE,
    name         TEXT,
    password     TEXT
);
    
INSERT INTO account(login_id, name, password) VALUES('okada', '岡田 是則', 's6rizqfk');
INSERT INTO account(login_id, name, password) VALUES('inoue', '井上 俊憲', 'g73phw5n');
INSERT INTO account(login_id, name, password) VALUES('inagaki', '稲垣 絵美', 's59mrtw3');

DROP TABLE IF EXISTS category;
CREATE TABLE category
(
    code      TEXT,
    locale    TEXT,
    name      TEXT,
    PRIMARY KEY(code, locale)
);
INSERT INTO category(code, locale, name) VALUES('10', 'ja', '仕事');
INSERT INTO category(code, locale, name) VALUES('20', 'ja', '勉強');
INSERT INTO category(code, locale, name) VALUES('30', 'ja', 'レジャー');
INSERT INTO category(code, locale, name) VALUES('10', 'en_US', 'Job');
INSERT INTO category(code, locale, name) VALUES('20', 'en_US', 'Study');
INSERT INTO category(code, locale, name) VALUES('30', 'en_US', 'Leisure');

DROP TABLE IF EXISTS groups;
CREATE TABLE groups
(
  id      SERIAL PRIMARY KEY,
  name    TEXT  NOT NULL  
);
SELECT SETVAL('groups_id_seq', 100, false);
INSERT INTO groups(id, name) VALUES(0, '');
INSERT INTO groups(name) VALUES('設計部');
INSERT INTO groups(name) VALUES('経理部');
INSERT INTO groups(name) VALUES('BBQサークル');


DROP TABLE IF EXISTS groups_account;
CREATE TABLE groups_account
(
  id            SERIAL PRIMARY KEY,
  groups_id     INTEGER  NOT NULL,
  account_id    INTEGER  NOT NULL,
  UNIQUE(groups_id, account_id)
  
);

INSERT INTO groups_account(groups_id, account_id) VALUES (100,1);
INSERT INTO groups_account(groups_id, account_id) VALUES (100,3);
INSERT INTO groups_account(groups_id, account_id) VALUES (101,2);
INSERT INTO groups_account(groups_id, account_id) VALUES (102,1);
INSERT INTO groups_account(groups_id, account_id) VALUES (102,2);
INSERT INTO groups_account(groups_id, account_id) VALUES (102,3);

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
FROM 
(
    SELECT id, owner_id, groups_id, category_code, category_locale,
           title, importance, urgency, deadline, done
      FROM todo
     WHERE groups_id = 0
  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
      FROM todo td
           JOIN groups_account ga ON ga.groups_id = td.groups_id
     WHERE td.groups_id != 0
) 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 
                    GROUP BY todo_id) tk
               ON td2.id = tk.todo_id;

