CREATE SCHEMA IF NOT EXISTS "public";

-- Create "categories" table
CREATE TABLE IF NOT EXISTS "public"."categories" (
  "category_id" integer NOT NULL, 
  "category_name" character varying(50) NOT NULL, 
  PRIMARY KEY ("category_id"), 
  CONSTRAINT "categories_category_name_key" UNIQUE ("category_name")
);
-- Create "users" table
CREATE TABLE IF NOT EXISTS "public"."users" (
  "user_id" bigint NOT NULL, 
  "username" character varying(50) NOT NULL, 
  "email" character varying(100) NOT NULL, 
  "password" character varying(255) NOT NULL, 
  "created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  "is_admin" boolean NOT NULL, 
  "display_name" character varying(32) NULL, 
  PRIMARY KEY ("user_id"), 
  CONSTRAINT "users_email_key" UNIQUE ("email"), 
  CONSTRAINT "users_username_key" UNIQUE ("username")
);
-- Create "blogs" table
CREATE TABLE IF NOT EXISTS "public"."blogs" (
  "blog_id" bigint NOT NULL, 
  "user_id" bigint NOT NULL, 
  "title" character varying(255) NULL, 
  "description" text NULL, 
  "category_id" integer NULL, 
  "created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("blog_id"), 
  CONSTRAINT "blogs_category_id_fkey" FOREIGN KEY ("category_id") REFERENCES "public"."categories" ("category_id") ON UPDATE NO ACTION ON DELETE NO ACTION, 
  CONSTRAINT "blogs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "posts" table
CREATE TABLE IF NOT EXISTS "public"."posts" (
  "post_id" bigint NOT NULL, 
  "blog_id" bigint NULL, 
  "user_id" bigint NOT NULL, 
  "title" character varying(255) NULL, 
  "content" text NULL, 
  "created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  "updated_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("post_id"), 
  CONSTRAINT "posts_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "public"."blogs" ("blog_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "posts_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "bookmarks" table
CREATE TABLE IF NOT EXISTS "public"."bookmarks" (
  "user_id" bigint NOT NULL, 
  "post_id" bigint NOT NULL, 
  "bookmarked_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("user_id", "post_id"), 
  CONSTRAINT "bookmarks_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "public"."posts" ("post_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "bookmarks_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "comments" table
CREATE TABLE IF NOT EXISTS "public"."comments" (
  "comment_id" bigint NOT NULL, 
  "post_id" bigint NOT NULL, 
  "user_id" bigint NOT NULL, 
  "content" text NOT NULL, 
  "created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("comment_id"), 
  CONSTRAINT "comments_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "public"."posts" ("post_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "comments_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "favorites" table
CREATE TABLE IF NOT EXISTS "public"."favorites" (
  "user_id" bigint NOT NULL, 
  "blog_id" bigint NOT NULL, 
  "favorited_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("user_id", "blog_id"), 
  CONSTRAINT "favorites_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "public"."blogs" ("blog_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "favorites_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "likes" table
CREATE TABLE IF NOT EXISTS "public"."likes" (
  "like_id" bigint NOT NULL, 
  "user_id" bigint NULL, 
  "comment_id" bigint NULL, 
  "created_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY ("like_id"), 
  CONSTRAINT "likes_comment_id_fkey" FOREIGN KEY ("comment_id") REFERENCES "public"."comments" ("comment_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "likes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "tags" table
CREATE TABLE IF NOT EXISTS "public"."tags" (
  "tag_id" integer NOT NULL, 
  "tag_name" character varying(50) NOT NULL, 
  PRIMARY KEY ("tag_id"), 
  CONSTRAINT "tags_tag_name_key" UNIQUE ("tag_name")
);
-- Create "post_tags" table
CREATE TABLE IF NOT EXISTS "public"."post_tags" (
  "post_id" bigint NOT NULL, 
  "tag_id" integer NOT NULL, 
  PRIMARY KEY ("post_id", "tag_id"), 
  CONSTRAINT "post_tags_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "public"."posts" ("post_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "post_tags_tag_id_fkey" FOREIGN KEY ("tag_id") REFERENCES "public"."tags" ("tag_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create "post_votes" table
CREATE TABLE IF NOT EXISTS "public"."post_votes" (
  "post_id" bigint NOT NULL, 
  "user_id" bigint NOT NULL, 
  "vote" boolean NOT NULL, 
  PRIMARY KEY ("post_id", "user_id"), 
  CONSTRAINT "post_votes_post_id_fkey" FOREIGN KEY ("post_id") REFERENCES "public"."posts" ("post_id") ON UPDATE NO ACTION ON DELETE CASCADE, 
  CONSTRAINT "post_votes_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE NO ACTION
);
-- Create "profiles" table
CREATE TABLE IF NOT EXISTS "public"."profiles" (
  "user_id" bigint NOT NULL, 
  "bio" text NULL, 
  "avatar_url" character varying(255) NULL, 
  "website_url" character varying(100) NULL, 
  PRIMARY KEY ("user_id"), 
  CONSTRAINT "profiles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "public"."users" ("user_id") ON UPDATE NO ACTION ON DELETE CASCADE
);
-- Create index "profiles_user_id_idx" to table: "profiles"
CREATE UNIQUE INDEX IF NOT EXISTS "profiles_user_id_idx" ON "public"."profiles" ("user_id");