CREATE TYPE "public"."community_post_type" AS ENUM('USER_UPDATE', 'MILESTONE', 'ADMIN_STORY', 'RESOURCE_SHARE');--> statement-breakpoint
CREATE TYPE "public"."kyc_status_enum" AS ENUM('PENDING', 'APPROVED', 'REJECTED');--> statement-breakpoint
ALTER TYPE "public"."wallet_transaction_type_enum" ADD VALUE 'ASCA_OFFLINE_WITHDRAWAL';--> statement-breakpoint
CREATE TABLE "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
);
--> statement-breakpoint
CREATE TABLE "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")
);
--> statement-breakpoint
CREATE TABLE "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")
);
--> statement-breakpoint
CREATE TABLE "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
);
--> statement-breakpoint
CREATE TABLE "membership_id_settings" (
	"id" integer PRIMARY KEY NOT NULL,
	"prefix" text DEFAULT 'WWN' NOT NULL,
	"digits" integer DEFAULT 7 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "organization_membership_id_settings" (
	"id" serial PRIMARY KEY NOT NULL,
	"organization_id" integer NOT NULL,
	"prefix" text DEFAULT 'WWN' NOT NULL,
	"digits" integer DEFAULT 7 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "organization_membership_id_settings_organization_id_unique" UNIQUE("organization_id")
);
--> statement-breakpoint
ALTER TABLE "ascas" ADD COLUMN "terms_html" text;--> statement-breakpoint
ALTER TABLE "ascas" ADD COLUMN "organization_id" integer;--> statement-breakpoint
ALTER TABLE "ascas" ADD COLUMN "funding_start_at" timestamp;--> statement-breakpoint
ALTER TABLE "ascas" ADD COLUMN "funding_end_at" timestamp;--> statement-breakpoint
ALTER TABLE "investment_opportunities" ADD COLUMN "terms_html" text;--> statement-breakpoint
ALTER TABLE "investment_opportunities" ADD COLUMN "organization_id" integer;--> statement-breakpoint
ALTER TABLE "lending_templates" ADD COLUMN "terms_html" text;--> statement-breakpoint
ALTER TABLE "savings_circles" ADD COLUMN "terms_html" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "contact_address" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "contact_street" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "contact_postal_code" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "contact_country_iso2" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employment_employed" boolean;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employment_status" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employer_name" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employer_address" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "employment_position" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_name" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_phone" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_wwn_id" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_address" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_street" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_city" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "referrer_postal_code" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "consent_accepted" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "consent_accepted_at" timestamp;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_first_name" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_last_name" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_other_names" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_gender" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_residential_address" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_street" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_city" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_postal_code" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_email" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_phone" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_relationship" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "next_of_kin_occupation" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_id_front_url" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_id_back_url" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_selfie_url" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_status" "kyc_status_enum";--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_submitted_at" timestamp;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_reviewed_at" timestamp;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_reviewed_by_user_id" integer;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "kyc_rejection_reason" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "membership_registration_id" text;--> statement-breakpoint
ALTER TABLE "users" ADD COLUMN "deleted_at" timestamp;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
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;--> statement-breakpoint
ALTER TABLE "organization_membership_id_settings" ADD CONSTRAINT "organization_membership_id_settings_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "community_comments_post_idx" ON "community_comments" USING btree ("post_id","created_at");--> statement-breakpoint
CREATE INDEX "community_posts_type_published_idx" ON "community_posts" USING btree ("post_type","is_published","published_at");--> statement-breakpoint
CREATE INDEX "community_profiles_user_idx" ON "community_profiles" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "community_profiles_org_idx" ON "community_profiles" USING btree ("organization_id");--> statement-breakpoint
CREATE UNIQUE INDEX "community_reactions_post_user_uidx" ON "community_reactions" USING btree ("post_id","user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "community_reactions_comment_user_uidx" ON "community_reactions" USING btree ("comment_id","user_id");--> statement-breakpoint
CREATE INDEX "org_membership_id_settings_org_updated_idx" ON "organization_membership_id_settings" USING btree ("organization_id","updated_at");--> statement-breakpoint
ALTER TABLE "ascas" ADD CONSTRAINT "ascas_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "investment_opportunities" ADD CONSTRAINT "investment_opportunities_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "ascas_org_status_idx" ON "ascas" USING btree ("organization_id","status");--> statement-breakpoint
CREATE UNIQUE INDEX "users_membership_registration_id_uidx" ON "users" USING btree ("membership_registration_id");