1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
CREATE TABLE "reg_eval_criteria" (
"id" serial PRIMARY KEY NOT NULL,
"category" varchar(32) DEFAULT 'processScore' NOT NULL,
"item" varchar(32) DEFAULT 'quality' NOT NULL,
"classification" varchar(255) NOT NULL,
"range" varchar(255),
"remarks" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "reg_eval_criteria_details" (
"id" serial PRIMARY KEY NOT NULL,
"criteria_id" integer NOT NULL,
"detail" text NOT NULL,
"order_index" integer DEFAULT 0 NOT NULL,
"score_equip_ship" numeric(5, 2),
"score_equip_marine" numeric(5, 2),
"score_bulk_ship" numeric(5, 2),
"score_bulk_marine" numeric(5, 2),
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "reg_eval_criteria_details" ADD CONSTRAINT "reg_eval_criteria_details_criteria_id_reg_eval_criteria_id_fk" FOREIGN KEY ("criteria_id") REFERENCES "public"."reg_eval_criteria"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."reg_eval_criteria_view" AS (select "reg_eval_criteria"."id", "reg_eval_criteria"."category", "reg_eval_criteria"."item", "reg_eval_criteria"."classification", "reg_eval_criteria"."range", "reg_eval_criteria_details"."id", "reg_eval_criteria_details"."detail", "reg_eval_criteria_details"."order_index", "reg_eval_criteria_details"."score_equip_ship", "reg_eval_criteria_details"."score_equip_marine", "reg_eval_criteria_details"."score_bulk_ship", "reg_eval_criteria_details"."score_bulk_marine" from "reg_eval_criteria" left join "reg_eval_criteria_details" on "reg_eval_criteria"."id" = "reg_eval_criteria_details"."criteria_id" order by "reg_eval_criteria"."id", "reg_eval_criteria_details"."order_index");
|
