/**
 * SQL commands for creating a task manager application
 *
 * Tables:
 * (1) list: id (PK), name
 * (2) task: id (PK), list_id (FK), description, deadline, status
 */

CREATE TABLE IF NOT EXISTS list (
    id INTEGER NOT NULL AUTO_INCREMENT,  -- TABLE CONSTRAINT IS NOT NULL
    name VARCHAR(30) NOT NULL,                       -- IF USING VARCHAR FOR KEY, DON'T GO OVER 191

    PRIMARY KEY (id)
);

INSERT INTO list(name) VALUES('My List'), ('Another list');

CREATE TABLE IF NOT EXISTS task (
    id INTEGER NOT NULL AUTO_INCREMENT,
    list_id INTEGER NOT NULL,
    description VARCHAR(255) NOT NULL,
    deadline DATETIME DEFAULT NULL,
    status TINYINT NOT NULL 0,

    PRIMARY KEY (id),
    FOREIGN KEY (list_id) REFERENCES list(id)
);

INSERT INTO task(list_id, description, deadline) VALUES
(1, 'Walk the dog', '2020-03-12 10:00:00');
(2, 'Finish HW', '2020-03-16 23:59:00');
