CREATE TABLE IF NOT EXISTS users (
    userid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
) engine=innodb;

CREATE TABLE IF NOT EXISTS poems (
    pid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    text VARCHAR(500) NOT NULL,
    likes INT NOT NULL,
    poem_userid INT UNSIGNED NOT NULL,
    FOREIGN KEY (poem_userid) 
    REFERENCES users(userid)
) engine=innodb;

CREATE TABLE IF NOT EXISTS comments (
    cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    text VARCHAR(255) NOT NULL,
    likes INT NOT NULL,
    comment_pid INT UNSIGNED NOT NULL,
    FOREIGN KEY (comment_pid) 
    REFERENCES poems(pid),
    comment_userid INT UNSIGNED NOT NULL,
    FOREIGN KEY (comment_userid) 
    REFERENCES users(userid)
) engine=innodb;
