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

-- Typically when using the function name, just do it all caps

CREATE TABLE IF NOT EXISTS list (
    -- Saying this value can only be an integer and it cannot be NULL.
    id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(30),
    -- 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,
    -- One of the keywords doesn't work for dates before 1970 for some reason so DATETIME is a good choice.
    deadline DATETIME DEFAULT NULL, 
    -- The number will always be small no matter what.
    status TINYINT NOT NULL DEFAULT 0,

    -- Foreign keys
    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-15 23:59:00');

-- nrep@4Zseyp