RelationalDesign: schema_creation.4.sql

-- Delete tables if they exist
DROP TABLE IF EXISTS NEED_APPROVAL CASCADE;
DROP TABLE IF EXISTS READING_LIST_ITEMS CASCADE;
DROP TABLE IF EXISTS HAS_GENRE CASCADE;
DROP TABLE IF EXISTS COLLABORATION CASCADE;
DROP TABLE IF EXISTS COMMENT CASCADE;
DROP TABLE IF EXISTS LIKES CASCADE;
DROP TABLE IF EXISTS AI_SUGGESTION CASCADE;
DROP TABLE IF EXISTS NOTIFICATION CASCADE;
DROP TABLE IF EXISTS READING_LIST CASCADE;
DROP TABLE IF EXISTS CHAPTER CASCADE;
DROP TABLE IF EXISTS STORY CASCADE;
DROP TABLE IF EXISTS WRITER CASCADE;
DROP TABLE IF EXISTS REGULAR_USER CASCADE;
DROP TABLE IF EXISTS ADMINS CASCADE;
DROP TABLE IF EXISTS GENRE CASCADE;
DROP TABLE IF EXISTS USERS CASCADE;

-- Tables

CREATE TABLE USERS(
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    user_name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    user_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    user_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);

CREATE TABLE ADMINS(
    user_id INTEGER PRIMARY KEY,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE REGULAR_USER(
    user_id INTEGER PRIMARY KEY,
    joined_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE WRITER(
    user_id INTEGER PRIMARY KEY,
    bio TEXT,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE STORY(
    story_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL DEFAULT '',
    mature_content BOOLEAN NOT NULL,
    short_description VARCHAR(500) NOT NULL,
    image VARCHAR(2048),
    story_content TEXT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'draft',
    user_id INTEGER NOT NULL,
    story_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    story_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
    FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE CHAPTER(
    chapter_id SERIAL PRIMARY KEY,
    chapter_number INTEGER NOT NULL,
    chapter_name VARCHAR(100) NOT NULL,
    title VARCHAR(200) NOT NULL,
    chapter_content TEXT NOT NULL,
    word_count INTEGER CHECK (word_count >= 0),
    rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
    published_at TIMESTAMPTZ NOT NULL,
    view_count INTEGER DEFAULT 0 CHECK (view_count >= 0),
    story_id INTEGER NOT NULL,
    chapter_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    chapter_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_chapter_number UNIQUE(story_id, chapter_number),
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE GENRE(
    genre_id SERIAL PRIMARY KEY,
    genre_name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE READING_LIST(
    list_id SERIAL PRIMARY KEY,
    list_name VARCHAR(100) NOT NULL,
    list_content TEXT,
    is_public BOOLEAN NOT NULL,
    user_id INTEGER NOT NULL,
    list_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    list_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE NOTIFICATION(
    notification_id SERIAL PRIMARY KEY,
    notification_content TEXT NOT NULL,
    content_type VARCHAR(50) NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    link VARCHAR(500),
    user_id INTEGER NOT NULL,
    story_id INTEGER NOT NULL,
    notification_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE AI_SUGGESTION(
    suggestion_id SERIAL PRIMARY KEY,
    original_text TEXT NOT NULL,
    suggested_text TEXT NOT NULL,
    suggestion_type VARCHAR(50) NOT NULL,
    accepted BOOLEAN NOT NULL DEFAULT FALSE,
    suggestion_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    applied_at TIMESTAMPTZ,
    story_id INTEGER NOT NULL,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
       ON DELETE CASCADE
       ON UPDATE CASCADE
);

CREATE TABLE LIKES(
    user_id INTEGER,
    story_id INTEGER,
    liked_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT like_pk PRIMARY KEY(user_id, story_id),
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE COMMENT(
    comment_id SERIAL PRIMARY KEY,
    comment_content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    story_id INTEGER NOT NULL,
    comment_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    comment_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE COLLABORATION(
    user_id INTEGER,
    story_id INTEGER,
    role VARCHAR(50) NOT NULL,
    permission_level INTEGER,
    collab_created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT collaboration_pk PRIMARY KEY(user_id, story_id),
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE HAS_GENRE(
    story_id INTEGER,
    genre_id INTEGER,
    CONSTRAINT has_genre_pk PRIMARY KEY(story_id, genre_id),
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (genre_id) REFERENCES GENRE(genre_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE READING_LIST_ITEMS(
    list_id INTEGER,
    story_id INTEGER,
    added_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT reading_list_items_pk PRIMARY KEY(list_id, story_id),
    FOREIGN KEY (list_id) REFERENCES READING_LIST(list_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE NEED_APPROVAL(
    suggestion_id INTEGER,
    story_id INTEGER,
    chapter_id INTEGER,
    CONSTRAINT need_approval_pk PRIMARY KEY(suggestion_id, story_id, chapter_id),
    FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (chapter_id) REFERENCES CHAPTER(chapter_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);