CREATE TABLE "account" (
	"id" text PRIMARY KEY NOT NULL,
	"account_id" text NOT NULL,
	"provider_id" text NOT NULL,
	"user_id" text NOT NULL,
	"access_token" text,
	"refresh_token" text,
	"id_token" text,
	"access_token_expires_at" timestamp with time zone,
	"refresh_token_expires_at" timestamp with time zone,
	"scope" text,
	"password" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "block" (
	"id" uuid PRIMARY KEY NOT NULL,
	"page_id" uuid NOT NULL,
	"type" varchar(64) NOT NULL,
	"schema_version" integer DEFAULT 1 NOT NULL,
	"config" jsonb NOT NULL,
	"responsive_layout" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "character" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" varchar(120) NOT NULL,
	"name_th" text NOT NULL,
	"name_en" text,
	"description" text,
	"portrait_media_id" uuid,
	"element" varchar(48),
	"role" varchar(80),
	"rarity" integer,
	"sort_order" integer DEFAULT 0 NOT NULL,
	"visible" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "data_source_version" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"data_source_id" uuid NOT NULL,
	"version" integer NOT NULL,
	"rows" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"evaluation" jsonb,
	"created_by_id" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "data_source" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"fields" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"current_version" integer DEFAULT 1 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "media" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"object_key" text NOT NULL,
	"file_name" text NOT NULL,
	"mime_type" varchar(64) NOT NULL,
	"byte_size" bigint NOT NULL,
	"checksum" varchar(128),
	"width" integer,
	"height" integer,
	"status" varchar(24) DEFAULT 'pending' NOT NULL,
	"current_reference_count" integer DEFAULT 0 NOT NULL,
	"revision_reference_count" integer DEFAULT 0 NOT NULL,
	"created_by_id" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "media_reference" (
	"media_id" uuid NOT NULL,
	"owner_type" varchar(24) NOT NULL,
	"owner_id" uuid NOT NULL,
	"reference_kind" varchar(24) NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "media_reference_media_id_owner_type_owner_id_reference_kind_pk" PRIMARY KEY("media_id","owner_type","owner_id","reference_kind")
);
--> statement-breakpoint
CREATE TABLE "outbox_event" (
	"id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "outbox_event_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775807 START WITH 1 CACHE 1),
	"topic" varchar(160) NOT NULL,
	"aggregate_id" uuid NOT NULL,
	"event_type" varchar(64) NOT NULL,
	"payload" jsonb NOT NULL,
	"status" varchar(24) DEFAULT 'pending' NOT NULL,
	"attempts" integer DEFAULT 0 NOT NULL,
	"available_at" timestamp with time zone DEFAULT now() NOT NULL,
	"processed_at" timestamp with time zone,
	"last_error" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "page_data_source" (
	"page_id" uuid NOT NULL,
	"data_source_id" uuid NOT NULL,
	"pinned_version" integer,
	CONSTRAINT "page_data_source_page_id_data_source_id_pk" PRIMARY KEY("page_id","data_source_id")
);
--> statement-breakpoint
CREATE TABLE "page" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"character_id" uuid NOT NULL,
	"slug" varchar(120) NOT NULL,
	"title" text NOT NULL,
	"nav_order" integer DEFAULT 0 NOT NULL,
	"visible" boolean DEFAULT false NOT NULL,
	"public_note" text,
	"version" integer DEFAULT 1 NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "public_snapshot" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"page_id" uuid NOT NULL,
	"page_version" integer NOT NULL,
	"version_vector" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"vector_hash" varchar(64) NOT NULL,
	"dto" jsonb NOT NULL,
	"visible" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "revision" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"page_id" uuid NOT NULL,
	"page_version" integer NOT NULL,
	"kind" varchar(24) NOT NULL,
	"label" text,
	"author_id" text,
	"source_revision_id" uuid,
	"checkpoint_hour" timestamp with time zone,
	"snapshot" jsonb NOT NULL,
	"data_source_versions" jsonb DEFAULT '{}'::jsonb NOT NULL,
	"media_ids" jsonb DEFAULT '[]'::jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"expires_at" timestamp with time zone
);
--> statement-breakpoint
CREATE TABLE "session" (
	"id" text PRIMARY KEY NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"token" text NOT NULL,
	"ip_address" text,
	"user_agent" text,
	"user_id" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "slug_alias" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"kind" varchar(24) NOT NULL,
	"old_character_slug" varchar(120) NOT NULL,
	"old_page_slug" varchar(120),
	"target_character_id" uuid NOT NULL,
	"target_page_id" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "template" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"slug" varchar(120) NOT NULL,
	"name" text NOT NULL,
	"description" text,
	"category" varchar(64) NOT NULL,
	"structure" jsonb NOT NULL,
	"built_in" boolean DEFAULT false NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "user" (
	"id" text PRIMARY KEY NOT NULL,
	"name" text NOT NULL,
	"email" text NOT NULL,
	"email_verified" boolean DEFAULT false NOT NULL,
	"image" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "verification" (
	"id" text PRIMARY KEY NOT NULL,
	"identifier" text NOT NULL,
	"value" text NOT NULL,
	"expires_at" timestamp with time zone NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "account" ADD CONSTRAINT "account_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "block" ADD CONSTRAINT "block_page_id_page_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."page"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "character" ADD CONSTRAINT "character_portrait_media_id_media_id_fk" FOREIGN KEY ("portrait_media_id") REFERENCES "public"."media"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "data_source_version" ADD CONSTRAINT "data_source_version_data_source_id_data_source_id_fk" FOREIGN KEY ("data_source_id") REFERENCES "public"."data_source"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "data_source_version" ADD CONSTRAINT "data_source_version_created_by_id_user_id_fk" FOREIGN KEY ("created_by_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "media" ADD CONSTRAINT "media_created_by_id_user_id_fk" FOREIGN KEY ("created_by_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "media_reference" ADD CONSTRAINT "media_reference_media_id_media_id_fk" FOREIGN KEY ("media_id") REFERENCES "public"."media"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "page_data_source" ADD CONSTRAINT "page_data_source_page_id_page_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."page"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "page_data_source" ADD CONSTRAINT "page_data_source_data_source_id_data_source_id_fk" FOREIGN KEY ("data_source_id") REFERENCES "public"."data_source"("id") ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "page" ADD CONSTRAINT "page_character_id_character_id_fk" FOREIGN KEY ("character_id") REFERENCES "public"."character"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "public_snapshot" ADD CONSTRAINT "public_snapshot_page_id_page_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."page"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "revision" ADD CONSTRAINT "revision_page_id_page_id_fk" FOREIGN KEY ("page_id") REFERENCES "public"."page"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "revision" ADD CONSTRAINT "revision_author_id_user_id_fk" FOREIGN KEY ("author_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "session" ADD CONSTRAINT "session_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "slug_alias" ADD CONSTRAINT "slug_alias_target_character_id_character_id_fk" FOREIGN KEY ("target_character_id") REFERENCES "public"."character"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "slug_alias" ADD CONSTRAINT "slug_alias_target_page_id_page_id_fk" FOREIGN KEY ("target_page_id") REFERENCES "public"."page"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "account_provider_unique" ON "account" USING btree ("provider_id","account_id");--> statement-breakpoint
CREATE INDEX "account_user_id_idx" ON "account" USING btree ("user_id");--> statement-breakpoint
CREATE INDEX "block_page_order_idx" ON "block" USING btree ("page_id","sort_order");--> statement-breakpoint
CREATE UNIQUE INDEX "character_slug_unique" ON "character" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "character_directory_idx" ON "character" USING btree ("visible","sort_order");--> statement-breakpoint
CREATE UNIQUE INDEX "data_source_version_unique" ON "data_source_version" USING btree ("data_source_id","version");--> statement-breakpoint
CREATE INDEX "data_source_name_idx" ON "data_source" USING btree ("name");--> statement-breakpoint
CREATE UNIQUE INDEX "media_object_key_unique" ON "media" USING btree ("object_key");--> statement-breakpoint
CREATE INDEX "media_visibility_idx" ON "media" USING btree ("status","current_reference_count");--> statement-breakpoint
CREATE INDEX "media_reference_owner_idx" ON "media_reference" USING btree ("owner_type","owner_id");--> statement-breakpoint
CREATE INDEX "outbox_pending_idx" ON "outbox_event" USING btree ("status","available_at","id");--> statement-breakpoint
CREATE INDEX "page_data_source_dependency_idx" ON "page_data_source" USING btree ("data_source_id");--> statement-breakpoint
CREATE UNIQUE INDEX "page_character_slug_unique" ON "page" USING btree ("character_id","slug");--> statement-breakpoint
CREATE INDEX "page_navigation_idx" ON "page" USING btree ("character_id","visible","nav_order");--> statement-breakpoint
CREATE UNIQUE INDEX "public_snapshot_version_unique" ON "public_snapshot" USING btree ("page_id","page_version","vector_hash");--> statement-breakpoint
CREATE INDEX "public_snapshot_lookup_idx" ON "public_snapshot" USING btree ("page_id","visible","created_at");--> statement-breakpoint
CREATE INDEX "revision_page_created_idx" ON "revision" USING btree ("page_id","created_at");--> statement-breakpoint
CREATE UNIQUE INDEX "revision_checkpoint_hour_unique" ON "revision" USING btree ("page_id","checkpoint_hour") WHERE "revision"."kind" = 'checkpoint';--> statement-breakpoint
CREATE UNIQUE INDEX "session_token_unique" ON "session" USING btree ("token");--> statement-breakpoint
CREATE INDEX "session_user_id_idx" ON "session" USING btree ("user_id");--> statement-breakpoint
CREATE UNIQUE INDEX "slug_alias_path_unique" ON "slug_alias" USING btree ("old_character_slug","old_page_slug");--> statement-breakpoint
CREATE UNIQUE INDEX "template_slug_unique" ON "template" USING btree ("slug");--> statement-breakpoint
CREATE UNIQUE INDEX "user_email_unique" ON "user" USING btree ("email");--> statement-breakpoint
CREATE INDEX "verification_identifier_idx" ON "verification" USING btree ("identifier");