schema.sql

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS recipes CASCADE;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS messages;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT UNIQUE,
    password TEXT,
	is_admin BOOLEAN DEFAULT FALSE
);

CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    title TEXT,
	cooking_time INTEGER,
	recipe TEXT,
	likes INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	public BOOLEAN DEFAULT TRUE,
	user_id INTEGER REFERENCES users ON DELETE CASCADE
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
	content TEXT,
	likes INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	user_id INTEGER REFERENCES users ON DELETE CASCADE,
	recipe_id INTEGER REFERENCES recipes ON DELETE CASCADE
);

CREATE TABLE profiles (
    id SERIAL PRIMARY KEY,
	bio TEXT,
	total_likes INTEGER,
	user_id INTEGER REFERENCES users ON DELETE CASCADE
);

CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
	content TEXT,
	sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	sent_from_id INTEGER REFERENCES users ON DELETE CASCADE,
	sent_to_id INTEGER REFERENCES users ON DELETE CASCADE,
	been_read BOOLEAN DEFAULT FALSE
);