184 lines
9.3 KiB
SQL
Executable File
184 lines
9.3 KiB
SQL
Executable File
CREATE TABLE "achievements" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"key" varchar(100) NOT NULL,
|
|
"unlocked_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"metadata" jsonb DEFAULT '{}'::jsonb
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "adventure_items" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"template_id" uuid NOT NULL,
|
|
"type" varchar(20) NOT NULL,
|
|
"label" varchar(200) NOT NULL,
|
|
"config" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"schedule" jsonb
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "adventure_templates" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"name" varchar(100) NOT NULL,
|
|
"days_of_week" integer[] NOT NULL,
|
|
"is_default" boolean DEFAULT false NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "ai_suggestions" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"role" varchar(20) NOT NULL,
|
|
"content" jsonb NOT NULL,
|
|
"generated_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
"dismissed" boolean DEFAULT false NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "books" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"title" varchar(300) NOT NULL,
|
|
"author" varchar(200),
|
|
"total_pages" integer DEFAULT 300 NOT NULL,
|
|
"current_page" integer DEFAULT 0 NOT NULL,
|
|
"status" varchar(20) DEFAULT 'reading' NOT NULL,
|
|
"started_at" timestamp with time zone DEFAULT now(),
|
|
"finished_at" timestamp with time zone,
|
|
"notes" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "daily_adventure_items" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"daily_adventure_id" uuid NOT NULL,
|
|
"source_item_id" uuid,
|
|
"type" varchar(20) NOT NULL,
|
|
"label" varchar(200) NOT NULL,
|
|
"state" varchar(20) DEFAULT 'blank' NOT NULL,
|
|
"value" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"config" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"sort_order" integer DEFAULT 0 NOT NULL,
|
|
"completed_at" timestamp with time zone
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "daily_adventures" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"date" date NOT NULL,
|
|
"template_id" uuid,
|
|
"is_rest_day" boolean DEFAULT false NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "explorations" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"title" varchar(300) NOT NULL,
|
|
"description" text NOT NULL,
|
|
"category" varchar(50) NOT NULL,
|
|
"status" varchar(20) DEFAULT 'suggested' NOT NULL,
|
|
"week_of" date NOT NULL,
|
|
"completed_note" text,
|
|
"accepted_at" timestamp with time zone
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "reading_logs" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"book_id" uuid NOT NULL,
|
|
"date" date NOT NULL,
|
|
"pages_read" integer DEFAULT 0 NOT NULL,
|
|
"note" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "reflections" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"date" date NOT NULL,
|
|
"went_well" text DEFAULT '' NOT NULL,
|
|
"learned" text DEFAULT '' NOT NULL,
|
|
"improve_tomorrow" text DEFAULT '' NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "settings" (
|
|
"user_id" uuid NOT NULL,
|
|
"key" varchar(100) NOT NULL,
|
|
"value" jsonb NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "spiritual_config" (
|
|
"user_id" uuid PRIMARY KEY NOT NULL,
|
|
"prayer_labels" text[] NOT NULL,
|
|
"litany_labels" text[] NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "teacher_content" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"exploration_id" uuid,
|
|
"topic" varchar(300) NOT NULL,
|
|
"content" jsonb NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "user_progress" (
|
|
"user_id" uuid PRIMARY KEY NOT NULL,
|
|
"total_xp" integer DEFAULT 0 NOT NULL,
|
|
"level" integer DEFAULT 1 NOT NULL,
|
|
"current_chapter" varchar(100) DEFAULT 'Prologue: Awakening' NOT NULL,
|
|
"grace_days_remaining" integer DEFAULT 2 NOT NULL,
|
|
"consistency_score" integer DEFAULT 0 NOT NULL,
|
|
"discipline_score" integer DEFAULT 0 NOT NULL,
|
|
"learning_score" integer DEFAULT 0 NOT NULL,
|
|
"spiritual_score" integer DEFAULT 0 NOT NULL,
|
|
"health_score" integer DEFAULT 0 NOT NULL,
|
|
"reading_score" integer DEFAULT 0 NOT NULL,
|
|
"scores_updated_at" timestamp with time zone,
|
|
"last_visit_date" date
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "users" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"display_name" varchar(100) DEFAULT 'Traveler' NOT NULL,
|
|
"portrait_config" jsonb DEFAULT '{"skinTone":"#D4A574","hairColor":"#3D2314","clothingColor":"#3A6EA5"}'::jsonb NOT NULL,
|
|
"current_title" varchar(100),
|
|
"rest_days_used_week" integer DEFAULT 0 NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "weekly_reviews" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"week_start" date NOT NULL,
|
|
"content" jsonb DEFAULT '{}'::jsonb NOT NULL,
|
|
"mentor_letter" text,
|
|
"xp_earned" integer DEFAULT 0 NOT NULL,
|
|
"user_intention" text
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "xp_events" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" uuid NOT NULL,
|
|
"date" date NOT NULL,
|
|
"source" varchar(50) NOT NULL,
|
|
"amount" integer NOT NULL,
|
|
"metadata" jsonb
|
|
);
|
|
--> statement-breakpoint
|
|
ALTER TABLE "achievements" ADD CONSTRAINT "achievements_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "adventure_items" ADD CONSTRAINT "adventure_items_template_id_adventure_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."adventure_templates"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "adventure_templates" ADD CONSTRAINT "adventure_templates_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "ai_suggestions" ADD CONSTRAINT "ai_suggestions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "books" ADD CONSTRAINT "books_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "daily_adventure_items" ADD CONSTRAINT "daily_adventure_items_daily_adventure_id_daily_adventures_id_fk" FOREIGN KEY ("daily_adventure_id") REFERENCES "public"."daily_adventures"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "daily_adventures" ADD CONSTRAINT "daily_adventures_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "daily_adventures" ADD CONSTRAINT "daily_adventures_template_id_adventure_templates_id_fk" FOREIGN KEY ("template_id") REFERENCES "public"."adventure_templates"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "explorations" ADD CONSTRAINT "explorations_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "reading_logs" ADD CONSTRAINT "reading_logs_book_id_books_id_fk" FOREIGN KEY ("book_id") REFERENCES "public"."books"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "reflections" ADD CONSTRAINT "reflections_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "settings" ADD CONSTRAINT "settings_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "spiritual_config" ADD CONSTRAINT "spiritual_config_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "teacher_content" ADD CONSTRAINT "teacher_content_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "teacher_content" ADD CONSTRAINT "teacher_content_exploration_id_explorations_id_fk" FOREIGN KEY ("exploration_id") REFERENCES "public"."explorations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "user_progress" ADD CONSTRAINT "user_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 "weekly_reviews" ADD CONSTRAINT "weekly_reviews_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "xp_events" ADD CONSTRAINT "xp_events_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "daily_adventures_user_date_idx" ON "daily_adventures" USING btree ("user_id","date");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "reflections_user_date_idx" ON "reflections" USING btree ("user_id","date");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "settings_user_key_idx" ON "settings" USING btree ("user_id","key"); |