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");