Part A: Refer to PDF

CREATE TABLE user (
user_id       INT             NOT NULL AUTO_INCREMENT,
first_name    VARCHAR(255)    NOT NULL,
last_name     VARCHAR(255)    NOT NULL,
username      VARCHAR(255)    NOT NULL,
password      VARCHAR(255)    NOT NULL,
email         VARCHAR(255)    NOT NULL,
dob           VARCHAR(255)    NOT NULL,       
avatar        VARCHAR(255)    NOT NULL,
PRIMARY KEY   (user_id)
);

CREATE TABLE post (
post_id       INT             NOT NULL AUTO_INCREMENT,
user_id       INT             NOT NULL,
title         VARCHAR(255)    NOT NULL,
blog_text     TEXT            NOT NULL, 
post_image    VARCHAR(255)    NOT NULL, 
timestamp     TIMESTAMP       NOT NULL,
PRIMARY KEY   (post_id),
FOREIGN KEY   (user_id)       REFERENCES user(user_id)
);

CREATE TABLE comment (
comment_id    INT             NOT NULL AUTO_INCREMENT,
post_id       INT             NOT NULL,
user_id       INT             NOT NULL,
comment_text  TEXT            NOT NULL,
upvote        INT             NOT NULL,
downvote      INT             NOT NULL,
timestamp     TIMESTAMP       NOT NULL,
PRIMARY KEY   (comment_id),
FOREIGN KEY   (user_id)       REFERENCES user (user_id), 
FOREIGN KEY   (post_id)       REFERENCES post (post_id)
);

CREATE TABLE vote (
vote_id INT NOT NULL AUTO_INCREMENT,
comment_id INT NOT NULL,
post_id INT NOT NULL,
user_id INT NOT NULL,
upvote BIT(1) NOT NULL,
downvote BIT(1) NOT NULL,
PRIMARY KEY (vote_id),
FOREIGN KEY (user_id) REFERENCES user(user_id),
FOREIGN KEY (comment_id) REFERENCES comment(comment_id),
FOREIGN KEY (post_id) REFERENCES post(post_id)
);

B.1:

insert into user (first_name, last_name, username, password, email, dob, avatar)
values ("Jane", "Doe", "jd999", "abc12345", "jd@uregina.ca", "1990-08-15", "image/abc.jpeg");

B.2: 

insert into post (user_id, timestamp, title, blog_text, post_image)
values (1, "2022-02-04 12:30:00", "blog 1", "sir yes sir!", "images/post.jpeg");

B.3:

insert into comment (comment_id, user_id, post_id, comment_text, upvote, downvote, timestamp)
values ("1", "1", "1","hello_world2", "0","0", "1990-08-16");

B.4:

insert into vote (vote_id, comment_id, post_id, user_id, upvote, downvote)
    values ("1", "1", "1", "1", 1, 0);

C.1:

select * from user where email="jd@uregina.ca" and password="abc12345";

C.2:

SELECT p.post_id, p.title, p.blog_text, p.timestamp, COUNT(c.comment_id) AS comment_count
FROM post p
LEFT JOIN comment c ON p.post_id = c.post_id
GROUP BY p.post_id
ORDER BY p.timestamp DESC
LIMIT 5;

SELECT post_id, title, blog_text, timestamp
FROM post
ORDER BY timestamp DESC
limit 20;

C.3:

SELECT 
p.post_id,
p.title AS post_title,
p.blog_text AS post_content,
p.timestamp AS post_date,
p.post_image AS featured_image,
c.comment_id,
c.comment_text AS comment_content,
c.timestamp AS comment_date,
u.user_id AS commenter_username,
u.avatar AS commenter_avatar,
COALESCE(SUM(CASE WHEN v.vote_id = 'upvote' THEN 1 ELSE 0 END), 0) AS upvotes,
COALESCE(SUM(CASE WHEN v.vote_id = 'downvote' THEN 1 ELSE 0 END), 0) AS downvotes
FROM 
    post p
LEFT JOIN 
    comment c ON p.post_id = c.post_id
LEFT JOIN 
    user u ON c.user_id = u.user_id
LEFT JOIN 
    vote v ON c.comment_id = v.comment_id
WHERE 
    p.user_id = 1
GROUP BY 
    p.post_id, p.title, p.blog_text, p.timestamp, p.post_image, c.comment_id, c.comment_text, c.timestamp, u.username, u.avatar
ORDER BY 
    p.timestamp DESC, c.timestamp ASC;

C.4:

 SELECT 
p.post_id,
p.title AS post_title,
p.blog_text AS post_content,
p.timestamp AS post_date,
p.post_image AS featured_image,
c.comment_id,
c.comment_text AS comment_content,
c.timestamp AS comment_date,
u.username AS commenter_username,
u.avatar AS commenter_avatar,
COALESCE(SUM(CASE WHEN v.vote_id = 'upvote' THEN 1 ELSE 0 END), 0) AS upvotes,
COALESCE(SUM(CASE WHEN v.vote_id = 'downvote' THEN 1 ELSE 0 END), 0) AS downvotes,
COALESCE(SUM(CASE WHEN v.vote_id = 'upvote' THEN 1 ELSE 0 END), 0) - 
COALESCE(SUM(CASE WHEN v.vote_id = 'downvote' THEN 1 ELSE 0 END), 0) AS comment_vote_score
FROM 
    post p
LEFT JOIN 
    comment c ON p.post_id = c.post_id
LEFT JOIN 
    user u ON c.user_id = u.user_id
LEFT JOIN 
    vote v ON c.comment_id = v.comment_id
WHERE 
    p.post_id = 1
GROUP BY 
    p.post_id, p.title, p.blog_text, p.timestamp, p.post_image, 
    c.comment_id, c.comment_text, c.timestamp, u.username, u.avatar
ORDER BY 
    comment_vote_score DESC, c.timestamp ASC;

**END OF FILE**