schema.sql

CREATE TABLE IF NOT EXISTS users(
    user_id SERIAL PRIMARY KEY,
	password TEXT NOT NULL,
	username TEXT UNIQUE NOT NULL
	);

CREATE TABLE IF NOT EXISTS posts(
	post_id SERIAL PRIMARY KEY,
	title TEXT NOT NULL,
	content TEXT NOT NULL,
	user_id SERIAL REFERENCES users(user_id) ON DELETE SET NULL, 
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);

CREATE TABLE IF NOT EXISTS comments(
	comment_id SERIAL PRIMARY KEY,
	post_id SERIAL REFERENCES posts(post_id) ON DELETE CASCADE,
	user_id SERIAL REFERENCES users(user_id) ON DELETE SET NULL,
	content TEXT NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);

CREATE TABLE IF NOT EXISTS votes(
    like_id SERIAL PRIMARY KEY,
    user_id SERIAL REFERENCES users(user_id) ON DELETE SET NULL,
    post_id SERIAL REFERENCES posts(post_id) ON DELETE CASCADE,
    vote_code BOOLEAN NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	edited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (post_id, user_id)
    );

CREATE TABLE IF NOT EXISTS profiles(
	profile_id SERIAL PRIMARY KEY,
	user_id SERIAL REFERENCES users(user_id) ON DELETE SET NULL,
	description TEXT NOT NULL DEFAULT 'unknown',
	country TEXT DEFAULT 'unknown',
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);