--  --------------------------------------------------------
--  注意：
--      以下のコマンドは、ユーザーtodouserとして接続後
--      実行すること
--  --------------------------------------------------------
\encoding UTF8;
DROP TABLE IF EXISTS todo;
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
);
  
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale)
VALUES('todo-1', 1, 0, 0, '2023-10-01', 'N', '10', 'ja');
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale)
VALUES('todo-2', 1, 0, 1, '2023-10-02', 'Y', '10', 'ja');
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale)
VALUES('todo-3', 1, 1, 0, '2023-10-03', 'N', '10', 'ja');
INSERT INTO todo(title, owner_id, importance, urgency, deadline, done, category_code, category_locale)
VALUES('todo-4', 1, 1, 1, '2023-10-04', 'Y', '10', 'ja');
  
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(1, 'task1-1','2023-09-30','N');
INSERT INTO task(todo_id,title,deadline,done) VALUES(2, 'task2-1',NULL,'N');
INSERT INTO task(todo_id,title,deadline,done) VALUES(2, 'task2-2',NULL,'Y');
INSERT INTO task(todo_id,title,deadline,done) VALUES(3, 'task3-1','2023-10-02','N');
INSERT INTO task(todo_id,title,deadline,done) VALUES(3, 'task3-2',NULL,'N');
INSERT INTO task(todo_id,title,deadline,done) VALUES(3, 'task3-3',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');

