CREATE TYPE community_post_type AS ENUM ('USER_UPDATE', 'MILESTONE', 'ADMIN_STORY', 'RESOURCE_SHARE');

CREATE TABLE IF NOT EXISTS "community_profiles" (
	"id" serial PRIMARY KEY NOT NULL,
	"user_id" integer,
	"organization_id" integer,
	"bio" text,
	"location" text,
	"interests" text[],
	"badges" text[],
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "community_profiles_user_id_unique" UNIQUE("user_id"),
	CONSTRAINT "community_profiles_organization_id_unique" UNIQUE("organization_id")
);

CREATE TABLE IF NOT EXISTS "community_posts" (
	"id" serial PRIMARY KEY NOT NULL,
	"post_type" "community_post_type" DEFAULT 'USER_UPDATE' NOT NULL,
	"author_id" integer,
	"organization_id" integer,
	"title" text,
	"slug" text,
	"excerpt" text,
	"content_html" text NOT NULL,
	"cover_image_url" text,
	"show_to_individuals" boolean DEFAULT true NOT NULL,
	"show_to_investors" boolean DEFAULT true NOT NULL,
	"show_to_organizations" boolean DEFAULT true NOT NULL,
	"is_published" boolean DEFAULT true NOT NULL,
	"published_at" timestamp DEFAULT now(),
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "community_posts_slug_unique" UNIQUE("slug")
);

CREATE TABLE IF NOT EXISTS "community_comments" (
	"id" serial PRIMARY KEY NOT NULL,
	"post_id" integer NOT NULL,
	"author_id" integer NOT NULL,
	"parent_id" integer,
	"content_html" text NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);

CREATE TABLE IF NOT EXISTS "community_reactions" (
	"id" serial PRIMARY KEY NOT NULL,
	"post_id" integer,
	"comment_id" integer,
	"user_id" integer NOT NULL,
	"reaction_type" text DEFAULT 'LIKE' NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);

DO $$ BEGIN
 ALTER TABLE "community_profiles" ADD CONSTRAINT "community_profiles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_profiles" ADD CONSTRAINT "community_profiles_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_posts" ADD CONSTRAINT "community_posts_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_posts" ADD CONSTRAINT "community_posts_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_comments" ADD CONSTRAINT "community_comments_post_id_community_posts_id_fk" FOREIGN KEY ("post_id") REFERENCES "public"."community_posts"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_comments" ADD CONSTRAINT "community_comments_author_id_users_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_reactions" ADD CONSTRAINT "community_reactions_post_id_community_posts_id_fk" FOREIGN KEY ("post_id") REFERENCES "public"."community_posts"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_reactions" ADD CONSTRAINT "community_reactions_comment_id_community_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."community_comments"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

DO $$ BEGIN
 ALTER TABLE "community_reactions" ADD CONSTRAINT "community_reactions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

CREATE INDEX IF NOT EXISTS "community_profiles_user_idx" ON "community_profiles" USING btree ("user_id");
CREATE INDEX IF NOT EXISTS "community_profiles_org_idx" ON "community_profiles" USING btree ("organization_id");
CREATE INDEX IF NOT EXISTS "community_posts_type_published_idx" ON "community_posts" USING btree ("post_type","is_published","published_at");
CREATE INDEX IF NOT EXISTS "community_comments_post_idx" ON "community_comments" USING btree ("post_id","created_at");
CREATE UNIQUE INDEX IF NOT EXISTS "community_reactions_post_user_uidx" ON "community_reactions" USING btree ("post_id","user_id");
CREATE UNIQUE INDEX IF NOT EXISTS "community_reactions_comment_user_uidx" ON "community_reactions" USING btree ("comment_id","user_id");