diff options
Diffstat (limited to 'db/migrations/0154_furry_molten_man.sql')
| -rw-r--r-- | db/migrations/0154_furry_molten_man.sql | 64 |
1 files changed, 64 insertions, 0 deletions
diff --git a/db/migrations/0154_furry_molten_man.sql b/db/migrations/0154_furry_molten_man.sql new file mode 100644 index 00000000..221eb78d --- /dev/null +++ b/db/migrations/0154_furry_molten_man.sql @@ -0,0 +1,64 @@ +CREATE TABLE "departments" ( + "id" serial PRIMARY KEY NOT NULL, + "department_code" varchar(50) NOT NULL, + "department_name" varchar(100) NOT NULL, + "description" text, + "is_active" boolean DEFAULT true NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + CONSTRAINT "departments_department_code_unique" UNIQUE("department_code") +); +--> statement-breakpoint +CREATE TABLE "evaluation_target_reviewers" ( + "id" serial PRIMARY KEY NOT NULL, + "evaluation_target_id" integer NOT NULL, + "department_code" varchar(50) NOT NULL, + "reviewer_user_id" integer NOT NULL, + "assigned_at" timestamp DEFAULT now() NOT NULL, + "assigned_by" integer NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + CONSTRAINT "unique_target_department" UNIQUE("evaluation_target_id","department_code") +); +--> statement-breakpoint +CREATE TABLE "evaluation_target_reviews" ( + "id" serial PRIMARY KEY NOT NULL, + "evaluation_target_id" integer NOT NULL, + "reviewer_user_id" integer NOT NULL, + "department_code" varchar(50) NOT NULL, + "is_approved" boolean, + "review_comment" text, + "reviewed_at" timestamp, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + CONSTRAINT "unique_target_reviewer" UNIQUE("evaluation_target_id","reviewer_user_id") +); +--> statement-breakpoint +CREATE TABLE "evaluation_targets" ( + "id" serial PRIMARY KEY NOT NULL, + "evaluation_year" integer NOT NULL, + "division" varchar(20) NOT NULL, + "vendor_id" integer NOT NULL, + "vendor_code" varchar(100) NOT NULL, + "vendor_name" varchar(255) NOT NULL, + "domestic_foreign" varchar(20) NOT NULL, + "material_type" varchar(30) NOT NULL, + "status" varchar(30) DEFAULT 'PENDING' NOT NULL, + "admin_comment" text, + "admin_user_id" integer, + "consolidated_comment" text, + "consensus_status" boolean, + "confirmed_at" timestamp, + "confirmed_by" integer, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "evaluation_target_reviewers" ADD CONSTRAINT "evaluation_target_reviewers_evaluation_target_id_evaluation_targets_id_fk" FOREIGN KEY ("evaluation_target_id") REFERENCES "public"."evaluation_targets"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_target_reviewers" ADD CONSTRAINT "evaluation_target_reviewers_reviewer_user_id_users_id_fk" FOREIGN KEY ("reviewer_user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_target_reviewers" ADD CONSTRAINT "evaluation_target_reviewers_assigned_by_users_id_fk" FOREIGN KEY ("assigned_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_target_reviews" ADD CONSTRAINT "evaluation_target_reviews_evaluation_target_id_evaluation_targets_id_fk" FOREIGN KEY ("evaluation_target_id") REFERENCES "public"."evaluation_targets"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_target_reviews" ADD CONSTRAINT "evaluation_target_reviews_reviewer_user_id_users_id_fk" FOREIGN KEY ("reviewer_user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_targets" ADD CONSTRAINT "evaluation_targets_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_targets" ADD CONSTRAINT "evaluation_targets_admin_user_id_users_id_fk" FOREIGN KEY ("admin_user_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "evaluation_targets" ADD CONSTRAINT "evaluation_targets_confirmed_by_users_id_fk" FOREIGN KEY ("confirmed_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;
\ No newline at end of file |