initialize.sql

CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    email VARCHAR (256) NOT NULL,
    name VARCHAR (256)
);

CREATE UNIQUE INDEX IF NOT EXISTS "email_unique_index" ON "users" (lower("email"));

CREATE TABLE IF NOT EXISTS lists (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
name VARCHAR ( 50 ) NOT NULL,
user_name VARCHAR ( 20 ) NOT NULL,
description TEXT,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed BOOLEAN DEFAULT FALSE,
repeats BOOLEAN DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
list_id INTEGER REFERENCES lists(id) ON DELETE CASCADE,
name VARCHAR ( 50 ) NOT NULL,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
description TEXT,
category VARCHAR (50),
repeats BOOLEAN DEFAULT FALSE,
frequency VARCHAR (25)
);


CREATE TABLE IF NOT EXISTS task_history (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
created_on TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
completed BOOLEAN DEFAULT FALSE,
notes TEXT
);

CREATE TABLE IF NOT EXISTS symptoms (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name VARCHAR ( 200 ) UNIQUE NOT NULL,
created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
description TEXT,
category VARCHAR ( 200 )
);

CREATE TABLE IF NOT EXISTS symptoms_history (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
symptom_id INTEGER NOT NULL REFERENCES symptoms(id) ON DELETE CASCADE,
created_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
intensity INT NOT NULL,
description TEXT
);

--  to access postgres terminal "sudo -u evergreen psql -d self_scribe"