66 lines
4.8 KiB
SQL
66 lines
4.8 KiB
SQL
CREATE TABLE "commission_checkout" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"request" jsonb NOT NULL,
|
|
"amount_baht" integer NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "commission_message" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"ticket_id" uuid NOT NULL,
|
|
"author_id" text NOT NULL,
|
|
"text" text,
|
|
"image_object_key" text,
|
|
"image_mime_type" varchar(32),
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "commission_payment" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"checkout_id" uuid NOT NULL,
|
|
"ticket_id" uuid NOT NULL,
|
|
"reference_id" text NOT NULL,
|
|
"trans_ref" text NOT NULL,
|
|
"amount_baht" integer NOT NULL,
|
|
"transferred_at" timestamp with time zone NOT NULL,
|
|
"slip_object_key" text NOT NULL,
|
|
"slip_mime_type" varchar(32) NOT NULL,
|
|
"provider_data" jsonb NOT NULL,
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "commission_reaction" (
|
|
"message_id" uuid NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"emoji" varchar(16) NOT NULL,
|
|
CONSTRAINT "commission_reaction_message_id_user_id_emoji_pk" PRIMARY KEY("message_id","user_id","emoji")
|
|
);
|
|
--> statement-breakpoint
|
|
CREATE TABLE "commission_ticket" (
|
|
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
|
|
"checkout_id" uuid NOT NULL,
|
|
"user_id" text NOT NULL,
|
|
"status" varchar(16) DEFAULT 'open' 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 "auth"."user" ALTER COLUMN "role" SET DEFAULT 'user';--> statement-breakpoint
|
|
ALTER TABLE "commission_checkout" ADD CONSTRAINT "commission_checkout_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "auth"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_message" ADD CONSTRAINT "commission_message_ticket_id_commission_ticket_id_fk" FOREIGN KEY ("ticket_id") REFERENCES "public"."commission_ticket"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_message" ADD CONSTRAINT "commission_message_author_id_user_id_fk" FOREIGN KEY ("author_id") REFERENCES "auth"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_payment" ADD CONSTRAINT "commission_payment_checkout_id_commission_checkout_id_fk" FOREIGN KEY ("checkout_id") REFERENCES "public"."commission_checkout"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_payment" ADD CONSTRAINT "commission_payment_ticket_id_commission_ticket_id_fk" FOREIGN KEY ("ticket_id") REFERENCES "public"."commission_ticket"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_reaction" ADD CONSTRAINT "commission_reaction_message_id_commission_message_id_fk" FOREIGN KEY ("message_id") REFERENCES "public"."commission_message"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_reaction" ADD CONSTRAINT "commission_reaction_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "auth"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_ticket" ADD CONSTRAINT "commission_ticket_checkout_id_commission_checkout_id_fk" FOREIGN KEY ("checkout_id") REFERENCES "public"."commission_checkout"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
ALTER TABLE "commission_ticket" ADD CONSTRAINT "commission_ticket_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "auth"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
|
|
CREATE INDEX "commission_checkout_user_idx" ON "commission_checkout" USING btree ("user_id","created_at");--> statement-breakpoint
|
|
CREATE INDEX "commission_message_ticket_time_idx" ON "commission_message" USING btree ("ticket_id","created_at");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "commission_payment_checkout_unique" ON "commission_payment" USING btree ("checkout_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "commission_payment_reference_unique" ON "commission_payment" USING btree ("reference_id");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "commission_payment_trans_ref_unique" ON "commission_payment" USING btree ("trans_ref");--> statement-breakpoint
|
|
CREATE UNIQUE INDEX "commission_ticket_checkout_unique" ON "commission_ticket" USING btree ("checkout_id");--> statement-breakpoint
|
|
CREATE INDEX "commission_ticket_user_idx" ON "commission_ticket" USING btree ("user_id","created_at");--> statement-breakpoint
|
|
CREATE INDEX "commission_ticket_status_idx" ON "commission_ticket" USING btree ("status","updated_at"); |