-- Users table CREATE TABLE users id INT NOT NULL AUTO_INCREMENT first

-- Users table
CREATE TABLE `users`
(
    `id`            INT NOT NULL AUTO_INCREMENT,
    `first_name`    VARCHAR(50) NOT NULL,
    `last_name`     VARCHAR(50) NOT NULL,
    `email`         VARCHAR(255) NOT NULL,
    `password`      VARCHAR(32) NOT NULL,
    `reg_date`      DATE NOT NULL,
    PRIMARY KEY(id)
);

-- Friends table
CREATE TABLE `friends`
(
    `id`            INT NOT NULL AUTO_INCREMENT,
    `friend1`       INT NOT NULL,
    `friend2`       INT NOT NULL,
    `accepted`      INT NOT NULL DEFAULT 0,
    `date`          DATE NOT NULL,
    FOREIGN KEY(`friend1`) REFERENCES users(id),
    FOREIGN KEY(`friend2`) REFERENCES users(id),
    PRIMARY KEY(id)
);

-- Posts table
CREATE TABLE `posts`
(
    `id`        INT NOT NULL AUTO_INCREMENT,
    `user_id`   INT NOT NULL,
    `title`     VARCHAR(200) NOT NULL,
    `text`      VARCHAR(5000) NOT NULL,
    `date`      DATETIME NOT NULL,
    `closed`    INT NOT NULL DEFAULT 0,
    FOREIGN KEY(`user_id`) REFERENCES users(`id`),
    PRIMARY KEY(`id`)
);

-- Comments table
CREATE TABLE `comments`
(
    `id`        INT NOT NULL AUTO_INCREMENT,
    `post_id`   INT NOT NULL,
    `user_id`   INT NOT NULL,
    `text`      VARCHAR(500) NOT NULL,
    `date`      DATETIME NOT NULL,
    FOREIGN KEY(`post_id`) REFERENCES posts(`id`),
    FOREIGN KEY(`user_id`) REFERENCES users(`id`),
    PRIMARY KEY(`id`)
);