DROP TABLE IF EXISTS assignees;
DROP TABLE IF EXISTS lists;
DROP TABLE IF EXISTS items;

-- joins tables
DROP TABLE IF EXISTS items_assignees;


-- table creation
CREATE TABLE lists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    title TEXT NOT NULL
);

CREATE TABLE items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    list_id INTEGER NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    content TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0,
    FOREIGN KEY (list_id) REFERENCES lists (id)
);

CREATE TABLE assignees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

-- joins table
CREATE TABLE items_assignees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_id INTEGER,
    assignee_id INTEGER,
    FOREIGN KEY (item_id) REFERENCES items(id),
    FOREIGN KEY (assignee_id)REFERENCES assignees(id)
)