CREATE TYPE "public"."research_application_status" AS ENUM('pending', 'accepted', 'declined', 'withdrawn');--> statement-breakpoint
CREATE TYPE "public"."research_proposal_status" AS ENUM('pending', 'accepted', 'declined');--> statement-breakpoint
CREATE TYPE "public"."research_publication_type" AS ENUM('journal', 'preprint', 'conference', 'poster', 'dataset', 'report');--> statement-breakpoint
CREATE TABLE "research_applications" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid,
	"name" text NOT NULL,
	"email" text NOT NULL,
	"institution" text DEFAULT '' NOT NULL,
	"country" text DEFAULT '' NOT NULL,
	"professional_role" text DEFAULT '' NOT NULL,
	"orcid_url" text,
	"linkedin_url" text,
	"role_preference" text DEFAULT '' NOT NULL,
	"experience" text DEFAULT '' NOT NULL,
	"motivation" text DEFAULT '' NOT NULL,
	"status" "research_application_status" DEFAULT 'pending' NOT NULL,
	"reviewed_by" uuid,
	"reviewed_at" timestamp,
	"review_notes" text,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_proposals" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"title" text NOT NULL,
	"proposer_name" text NOT NULL,
	"email" text NOT NULL,
	"institution" text DEFAULT '' NOT NULL,
	"country" text DEFAULT '' NOT NULL,
	"orcid_url" text,
	"specialty" text DEFAULT '' NOT NULL,
	"background" text DEFAULT '' NOT NULL,
	"hypothesis" text DEFAULT '' NOT NULL,
	"design" text DEFAULT '' NOT NULL,
	"resources_needed" text DEFAULT '' NOT NULL,
	"funding_status" text DEFAULT '' NOT NULL,
	"status" "research_proposal_status" DEFAULT 'pending' NOT NULL,
	"reviewed_by" uuid,
	"reviewed_at" timestamp,
	"review_notes" text,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_publications" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid NOT NULL,
	"title" text NOT NULL,
	"authors" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"journal" text DEFAULT '' NOT NULL,
	"year" integer,
	"doi" text,
	"pmid" text,
	"url" text,
	"abstract" text DEFAULT '' NOT NULL,
	"type" "research_publication_type" DEFAULT 'journal' NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_updates" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid NOT NULL,
	"title" text NOT NULL,
	"body" text DEFAULT '' NOT NULL,
	"posted_at" timestamp DEFAULT now() NOT NULL,
	"posted_by_name" text DEFAULT '' NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "featured" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "specialty" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "themes" jsonb DEFAULT '[]'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "timeline_milestones" jsonb DEFAULT '[]'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "ethics_ref" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "research_studies" ADD COLUMN "protocol_version" text DEFAULT '' NOT NULL;--> statement-breakpoint
ALTER TABLE "research_applications" ADD CONSTRAINT "research_applications_study_id_research_studies_id_fk" FOREIGN KEY ("study_id") REFERENCES "public"."research_studies"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "research_applications" ADD CONSTRAINT "research_applications_reviewed_by_users_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "research_proposals" ADD CONSTRAINT "research_proposals_reviewed_by_users_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "research_publications" ADD CONSTRAINT "research_publications_study_id_research_studies_id_fk" FOREIGN KEY ("study_id") REFERENCES "public"."research_studies"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "research_updates" ADD CONSTRAINT "research_updates_study_id_research_studies_id_fk" FOREIGN KEY ("study_id") REFERENCES "public"."research_studies"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "research_applications_study_idx" ON "research_applications" USING btree ("study_id");--> statement-breakpoint
CREATE INDEX "research_applications_status_idx" ON "research_applications" USING btree ("status");--> statement-breakpoint
CREATE INDEX "research_applications_created_idx" ON "research_applications" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "research_proposals_status_idx" ON "research_proposals" USING btree ("status");--> statement-breakpoint
CREATE INDEX "research_proposals_created_idx" ON "research_proposals" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "research_publications_study_idx" ON "research_publications" USING btree ("study_id");--> statement-breakpoint
CREATE INDEX "research_publications_year_idx" ON "research_publications" USING btree ("year");--> statement-breakpoint
CREATE INDEX "research_updates_study_idx" ON "research_updates" USING btree ("study_id");--> statement-breakpoint
CREATE INDEX "research_updates_posted_idx" ON "research_updates" USING btree ("posted_at");--> statement-breakpoint
CREATE INDEX "research_studies_specialty_idx" ON "research_studies" USING btree ("specialty");--> statement-breakpoint
CREATE INDEX "research_studies_featured_idx" ON "research_studies" USING btree ("featured");