{{{#!sql
-- 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
);