schema.sql

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    hash_password TEXT NOT NULL,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    date_of_birth TEXT NOT NULL,
    university TEXT NOT NULL,
    course TEXT NOT NULL,
    period INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS users_connections(
    follower_id INTEGER NOT NULL,
    followed_id INTEGER NOT NULL,
    PRIMARY KEY (follower_id, followed_id),
    FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (followed_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS community(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    moderator_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    followers INTEGER,
    description TEXT NOT NULL,
    FOREIGN KEY (moderator_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS posts(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL,
    community_id INTEGER,
    post_text TEXT,
    upvotes INTEGER,
    num_comments INTEGER,
    upload_date TEXT NOT NULL,
    original_post_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES users(id),
    FOREIGN KEY (community_id) REFERENCES community(id),
    FOREIGN KEY (original_post_id) REFERENCES posts(id)
);

CREATE TABLE IF NOT EXISTS post_upvotes (
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    PRIMARY KEY (user_id, post_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id)
);

CREATE TABLE IF NOT EXISTS community_members(
    community_id INTEGER NOT NULL,
    member_id INTEGER NOT NULL,
    PRIMARY KEY (community_id, member_id),
    FOREIGN KEY (community_id) REFERENCES community(id) ON DELETE CASCADE,
    FOREIGN KEY (member_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS medias(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_id INTEGER NOT NULL,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    url TEXT NOT NULL,
    type TEXT NOT NULL,
    format TEXT NOT NULL,
    byte_size INTEGER NOT NULL,
    upload_date TEXT NOT NULL,
    video_duration INTEGER,
    video_thumbnail TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);