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

CREATE TABLE IF NOT EXISTS list (
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
    -- if using varchar for primary key (or any type of key) dont go over 191
);

--INSERT INTO list VALUES(1, "My list");
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 DEFAULT 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-13 23:59:59');
