CREATE TABLE IF NOT EXISTS "comment" (
    "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
    "post_id" uuid NOT NULL,
    "user_id" uuid NOT NULL,
    "parent_id" uuid,
    "content" text NOT NULL,
    "likes_count" integer DEFAULT 0 NOT NULL,
    "created_at" timestamp DEFAULT now() NOT NULL,
    "updated_at" timestamp DEFAULT now() NOT NULL,
    CONSTRAINT "comments_likes_count_check" CHECK ("likes_count" >= 0),
    CONSTRAINT "comments_content_check" CHECK ((content IS NULL) OR (length(TRIM(BOTH FROM content)) > 0))
    );
--> statement-breakpoint

CREATE TABLE IF NOT EXISTS "comment_reactions" (
   "comment_id" uuid NOT NULL,
   "user_id" uuid NOT NULL,
   CONSTRAINT "comment_reactions_pkey" PRIMARY KEY("comment_id","user_id")
);
--> statement-breakpoint

DROP TABLE IF EXISTS "comments" CASCADE;
--> statement-breakpoint

ALTER TABLE "comment" DROP CONSTRAINT IF EXISTS "comment_post_id_posts_id_fk";
--> statement-breakpoint
ALTER TABLE "comment" DROP CONSTRAINT IF EXISTS "comment_user_id_users_id_fk";
--> statement-breakpoint
ALTER TABLE "comment_reactions" DROP CONSTRAINT IF EXISTS "comment_reactions_comment_id_comment_id_fk";
--> statement-breakpoint
ALTER TABLE "comment_reactions" DROP CONSTRAINT IF EXISTS "comment_reactions_user_id_users_id_fk";
--> statement-breakpoint

ALTER TABLE "comment"
    ADD CONSTRAINT "comment_post_id_posts_id_fk"
        FOREIGN KEY ("post_id") REFERENCES "public"."posts"("id")
            ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint

ALTER TABLE "comment"
    ADD CONSTRAINT "comment_user_id_users_id_fk"
        FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
            ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint

ALTER TABLE "comment_reactions"
    ADD CONSTRAINT "comment_reactions_comment_id_comment_id_fk"
        FOREIGN KEY ("comment_id") REFERENCES "public"."comment"("id")
            ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint

ALTER TABLE "comment_reactions"
    ADD CONSTRAINT "comment_reactions_user_id_users_id_fk"
        FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
            ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint

CREATE INDEX IF NOT EXISTS "comments_post_id_idx" ON "comment" USING btree ("post_id");
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comments_user_id_idx" ON "comment" USING btree ("user_id");
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comments_parent_id_idx" ON "comment" USING btree ("parent_id");
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comments_created_at_idx" ON "comment" USING btree ("created_at" DESC NULLS LAST);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comments_likes_count_idx" ON "comment" USING btree ("likes_count");
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comment_reactions_comment_idx" ON "comment_reactions" USING btree ("comment_id");
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "comment_reactions_user_idx" ON "comment_reactions" USING btree ("user_id");
