CREATE TABLE users(
    id serial PRIMARY KEY,
    first_name text,
    last_name text,
    email_address text NOT NULL,
    screen_name text NOT NULL
);

CREATE TABLE posts(
    id serial PRIMARY KEY,
    author_id int REFERENCES users ON DELETE RESTRICT,
    title text,
    content text,
    published timestamp DEFAULT now()
);

CREATE TABLE tags (
    id serial PRIMARY KEY,
    tag text NOT NULL
);

CREATE TABLE post_tags (
    post_id int REFERENCES posts(id) ON DELETE CASCADE,
    tag_id int REFERENCES tags(id) ON DELETE RESTRICT,
    PRIMARY KEY (post_id, tag_id)
);

CREATE TABLE comments (
    id serial PRIMARY KEY,
    comment_text text,
    author_id integer REFERENCES users ON DELETE CASCADE NOT NULL,
    post_id integer REFERENCES posts ON DELETE CASCADE NOT NULL,
    referring_comment_id integer,
    FOREIGN KEY (referring_comment_id) REFERENCES comments(id) ON DELETE SET NULL
);


INSERT INTO users
    (email_address, screen_name) VALUES
    ('firstemail@email.com', 'JOdawg'),
    ('secondeamil@email.com', 'CDfox'),
    ('thirdemail@email.com', 'RCpanda');

INSERT INTO tags (tag) VALUES
    ('current events'), ('something important'), ('boring');

INSERT INTO posts (title, content, author_id) VALUES
    ('What do you want to know?', 'blah blah blah', 1),
    ('Random Title', 'Boring content', 1);

INSERT INTO post_tags (post_id, tag_id) VALUES
    (1, 2),
    (2, 3),
    (2, 1),
    (1, 1);

INSERT INTO comments (author_id, post_id) VALUES
    (2, 1),
    (1, 2);

INSERT INTO comments (author_id, post_id, referring_comment_id) VALUES
(3, 1, 1),
(2, 1, 2);