P2: schema_creation.sql

-- Delete tables if they exist
DROP TABLE IF EXISTS NEED_APPROVAL CASCADE;
DROP TABLE IF EXISTS NOTIFY CASCADE;
DROP TABLE IF EXISTS ROLES CASCADE;
DROP TABLE IF EXISTS PERMISSION_LEVEL CASCADE;
DROP TABLE IF EXISTS SUGGESTION_TYPE CASCADE;
DROP TABLE IF EXISTS CONTENT_TYPE 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 STATUS 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,
    name VARCHAR(100) NOT NULL,
    surname VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    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,
    FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

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

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

CREATE TABLE STORY(
    story_id SERIAL PRIMARY KEY,
    mature_content BOOLEAN NOT NULL,
    short_description VARCHAR(500) NOT NULL,
    image VARCHAR(2048),
    content TEXT NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES WRITER(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE STATUS(
    story_id INTEGER,
    status VARCHAR(50) NOT NULL,
    CONSTRAINT status_pk PRIMARY KEY(story_id, status),
    CONSTRAINT status_values CHECK (status IN ('draft', 'published', 'archived')),
    FOREIGN KEY (story_id) REFERENCES STORY(story_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,
    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,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    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,
    name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE READING_LIST(
    list_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    content TEXT,
    is_public BOOLEAN NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    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,
    content TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE CONTENT_TYPE(
    notification_id INTEGER,
    content_type VARCHAR(50) NOT NULL,
    CONSTRAINT content_type_pk PRIMARY KEY(notification_id, content_type),
    FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_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,
    accepted BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    applied_at TIMESTAMPTZ
);

CREATE TABLE SUGGESTION_TYPE(
    suggestion_id INTEGER,
    suggestion_type VARCHAR(50) NOT NULL,
    CONSTRAINT suggestion_type_pk PRIMARY KEY(suggestion_id, suggestion_type),
    FOREIGN KEY (suggestion_id) REFERENCES AI_SUGGESTION(suggestion_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE LIKES(
    user_id INTEGER,
    story_id INTEGER,
    created_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,
    content TEXT NOT NULL,
    story_id INTEGER NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE COLLABORATION(
    user_id INTEGER,
    story_id INTEGER,
    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 ROLES(
    user_id INTEGER,
    story_id INTEGER,
    roles VARCHAR(50) NOT NULL,
    CONSTRAINT role_pk PRIMARY KEY(user_id, story_id, roles),
    FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE PERMISSION_LEVEL(
    user_id INTEGER,
    story_id INTEGER,
    permission_level INTEGER NOT NULL,
    CONSTRAINT permission_level_pk PRIMARY KEY(user_id, story_id, permission_level),
    FOREIGN KEY (user_id, story_id) REFERENCES COLLABORATION(user_id, 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 NOTIFY(
    user_id INTEGER,
    story_id INTEGER,
    notification_id INTEGER,
    CONSTRAINT notify_pk PRIMARY KEY(user_id, story_id, notification_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,
    FOREIGN KEY (notification_id) REFERENCES NOTIFICATION(notification_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
);