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

-- Tables: 
CREATE TABLE IF NOT EXISTS list (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(30) NOT NULL,
	-- If using VARCHAR for key, don't go over 191

	PRIMARY KEY (id)
);

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)
);


-- Inserts: 
/*INSERT INTO list VALUES(1, 'My List'), (2, 'Another List');*/
INSERT INTO list(name) VALUES('My List'), ('Another List');

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');


-- Views:
CREATE OR REPLACE VIEW detailed_list AS
SELECT list.name, task.description, task.deadline, task.status
FROM list INNER JOIN task ON task.list_id = list.id;
