CREATE TYPE "public"."course_level" AS ENUM('Foundation', 'Intermediate', 'Advanced');--> statement-breakpoint
CREATE TYPE "public"."course_status" AS ENUM('draft', 'published', 'archived');--> statement-breakpoint
CREATE TYPE "public"."lesson_type" AS ENUM('video', 'reading', 'quiz');--> statement-breakpoint
CREATE TYPE "public"."product_status" AS ENUM('draft', 'published', 'archived');--> statement-breakpoint
CREATE TYPE "public"."stock_status" AS ENUM('in_stock', 'out_of_stock', 'preorder', 'coming_soon');--> statement-breakpoint
CREATE TYPE "public"."study_phase" AS ENUM('Planned', 'Recruiting', 'Active', 'Completed');--> statement-breakpoint
CREATE TYPE "public"."study_status" AS ENUM('draft', 'published', 'archived');--> statement-breakpoint
CREATE TABLE "course_modules" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"course_id" uuid NOT NULL,
	"title" text NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "courses" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"level" "course_level" DEFAULT 'Foundation' NOT NULL,
	"topic" text DEFAULT 'General' NOT NULL,
	"short_description" text DEFAULT '' NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"prerequisites" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"instructor_name" text,
	"instructor_title" text,
	"instructor_bio" text,
	"cover_photo" text,
	"cover_photo_alt" text,
	"status" "course_status" DEFAULT 'draft' NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"published_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "enrollments" (
	"user_id" uuid NOT NULL,
	"course_id" uuid NOT NULL,
	"enrolled_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "enrollments_user_id_course_id_pk" PRIMARY KEY("user_id","course_id")
);
--> statement-breakpoint
CREATE TABLE "lesson_progress" (
	"user_id" uuid NOT NULL,
	"lesson_id" uuid NOT NULL,
	"completed_at" timestamp DEFAULT now() NOT NULL,
	CONSTRAINT "lesson_progress_user_id_lesson_id_pk" PRIMARY KEY("user_id","lesson_id")
);
--> statement-breakpoint
CREATE TABLE "lessons" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"module_id" uuid NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"type" "lesson_type" DEFAULT 'reading' NOT NULL,
	"duration_min" integer DEFAULT 0 NOT NULL,
	"content" text DEFAULT '' NOT NULL,
	"transcript" text,
	"key_points" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_documents" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid NOT NULL,
	"name" text NOT NULL,
	"type" text NOT NULL,
	"position" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_investigators" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid NOT NULL,
	"name" text NOT NULL,
	"institution" text NOT NULL,
	"role" text NOT NULL,
	"position" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_sites" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"study_id" uuid NOT NULL,
	"city" text NOT NULL,
	"country" text NOT NULL,
	"pi_name" text NOT NULL,
	"enrolment" integer DEFAULT 0 NOT NULL,
	"position" integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE "research_studies" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"phase" "study_phase" DEFAULT 'Planned' NOT NULL,
	"nct_id" text,
	"sponsor" text DEFAULT 'OrthoGlobe Research Collaborative' NOT NULL,
	"principal_investigator" text DEFAULT '' NOT NULL,
	"condition_keyword" text DEFAULT '' NOT NULL,
	"target_enrolment" integer DEFAULT 0 NOT NULL,
	"current_enrolment" integer DEFAULT 0 NOT NULL,
	"countries" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"background" text DEFAULT '' NOT NULL,
	"design" text DEFAULT '' NOT NULL,
	"intervention" text DEFAULT '' NOT NULL,
	"control" text DEFAULT '' NOT NULL,
	"inclusion" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"exclusion" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"primary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"secondary_outcomes" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"status" "study_status" DEFAULT 'draft' NOT NULL,
	"published_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "store_categories" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"name" text NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"position" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "store_products" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"short_description" text DEFAULT '' NOT NULL,
	"description" text DEFAULT '' NOT NULL,
	"category_id" uuid,
	"price_minor" integer DEFAULT 0 NOT NULL,
	"currency" text DEFAULT 'GBP' NOT NULL,
	"sku" text,
	"stock_status" "stock_status" DEFAULT 'coming_soon' NOT NULL,
	"cover_image" text,
	"cover_image_alt" text,
	"member_only" boolean DEFAULT false NOT NULL,
	"status" "product_status" DEFAULT 'draft' NOT NULL,
	"published_at" timestamp,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "course_modules" ADD CONSTRAINT "course_modules_course_id_courses_id_fk" FOREIGN KEY ("course_id") REFERENCES "public"."courses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "enrollments" ADD CONSTRAINT "enrollments_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "enrollments" ADD CONSTRAINT "enrollments_course_id_courses_id_fk" FOREIGN KEY ("course_id") REFERENCES "public"."courses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lesson_progress" ADD CONSTRAINT "lesson_progress_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lesson_progress" ADD CONSTRAINT "lesson_progress_lesson_id_lessons_id_fk" FOREIGN KEY ("lesson_id") REFERENCES "public"."lessons"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "lessons" ADD CONSTRAINT "lessons_module_id_course_modules_id_fk" FOREIGN KEY ("module_id") REFERENCES "public"."course_modules"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "research_documents" ADD CONSTRAINT "research_documents_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_investigators" ADD CONSTRAINT "research_investigators_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_sites" ADD CONSTRAINT "research_sites_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 "store_products" ADD CONSTRAINT "store_products_category_id_store_categories_id_fk" FOREIGN KEY ("category_id") REFERENCES "public"."store_categories"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "course_modules_course_idx" ON "course_modules" USING btree ("course_id");--> statement-breakpoint
CREATE UNIQUE INDEX "courses_slug_idx" ON "courses" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "courses_status_idx" ON "courses" USING btree ("status");--> statement-breakpoint
CREATE UNIQUE INDEX "lessons_module_slug_idx" ON "lessons" USING btree ("module_id","slug");--> statement-breakpoint
CREATE INDEX "research_documents_study_idx" ON "research_documents" USING btree ("study_id");--> statement-breakpoint
CREATE INDEX "research_investigators_study_idx" ON "research_investigators" USING btree ("study_id");--> statement-breakpoint
CREATE INDEX "research_sites_study_idx" ON "research_sites" USING btree ("study_id");--> statement-breakpoint
CREATE UNIQUE INDEX "research_studies_slug_idx" ON "research_studies" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "research_studies_status_idx" ON "research_studies" USING btree ("status");--> statement-breakpoint
CREATE UNIQUE INDEX "store_categories_slug_idx" ON "store_categories" USING btree ("slug");--> statement-breakpoint
CREATE UNIQUE INDEX "store_products_slug_idx" ON "store_products" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "store_products_status_idx" ON "store_products" USING btree ("status");