-- Delete tables if they exist
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 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP 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,
        created_story BOOLEAN,
        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),
        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,
        user_id INTEGER NOT NULL,
        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES USERS(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

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,
        story_id INTEGER NOT NULL,
        created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
        applied_at TIMESTAMPTZ,
        FOREIGN KEY (story_id) REFERENCES STORY(story_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

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,
        user_id INTEGER NOT NULL,
        story_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,
        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
);