diff options
Diffstat (limited to 'db/migrations/0255_mixed_cobalt_man.sql')
| -rw-r--r-- | db/migrations/0255_mixed_cobalt_man.sql | 81 |
1 files changed, 81 insertions, 0 deletions
diff --git a/db/migrations/0255_mixed_cobalt_man.sql b/db/migrations/0255_mixed_cobalt_man.sql new file mode 100644 index 00000000..14141d57 --- /dev/null +++ b/db/migrations/0255_mixed_cobalt_man.sql @@ -0,0 +1,81 @@ +CREATE TABLE "pq_lists" ( + "id" serial PRIMARY KEY NOT NULL, + "name" varchar(255) NOT NULL, + "type" varchar(20) NOT NULL, + "project_id" integer, + "is_deleted" boolean DEFAULT false NOT NULL, + "valid_to" timestamp, + "created_by" integer, + "updated_by" integer, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "site_visit_request_attachments" ( + "id" serial PRIMARY KEY NOT NULL, + "site_visit_request_id" integer NOT NULL, + "vendor_site_visit_info_id" integer, + "file_name" varchar(255) NOT NULL, + "original_file_name" varchar(255), + "file_path" varchar(1024) NOT NULL, + "file_size" integer, + "mime_type" varchar(100), + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "site_visit_requests" ( + "id" serial PRIMARY KEY NOT NULL, + "investigation_id" integer NOT NULL, + "requester_id" integer, + "inspection_duration" numeric(4, 1), + "requested_start_date" timestamp, + "requested_end_date" timestamp, + "shi_attendees" jsonb DEFAULT '{}'::jsonb NOT NULL, + "vendor_requests" jsonb DEFAULT '{}'::jsonb NOT NULL, + "additional_requests" text, + "status" varchar(20) DEFAULT 'REQUESTED' NOT NULL, + "sent_at" timestamp, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "vendor_site_visit_info" ( + "id" serial PRIMARY KEY NOT NULL, + "site_visit_request_id" integer NOT NULL, + "factory_name" varchar(255) NOT NULL, + "factory_location" varchar(255) NOT NULL, + "factory_address" text NOT NULL, + "factory_pic_name" varchar(255) NOT NULL, + "factory_pic_phone" varchar(50) NOT NULL, + "factory_pic_email" varchar(255) NOT NULL, + "factory_directions" text, + "access_procedure" text, + "has_attachments" boolean DEFAULT false NOT NULL, + "other_info" text, + "submitted_at" timestamp DEFAULT now() NOT NULL, + "submitted_by" integer NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "pq_criterias" ADD COLUMN "sub_group_name" varchar(255);--> statement-breakpoint +ALTER TABLE "pq_criterias" ADD COLUMN "pq_list_id" integer NOT NULL;--> statement-breakpoint +ALTER TABLE "pq_criterias" ADD COLUMN "input_format" varchar(50) DEFAULT 'TEXT' NOT NULL;--> statement-breakpoint +ALTER TABLE "vendor_criteria_attachments" ADD COLUMN "original_file_name" varchar(255);--> statement-breakpoint +ALTER TABLE "vendor_investigation_attachments" ADD COLUMN "original_file_name" varchar(255);--> statement-breakpoint +ALTER TABLE "vendor_pq_submissions" ADD COLUMN "due_date" timestamp;--> statement-breakpoint +ALTER TABLE "vendor_pq_submissions" ADD COLUMN "agreements" jsonb DEFAULT '{}'::jsonb NOT NULL;--> statement-breakpoint +ALTER TABLE "vendor_pq_submissions" ADD COLUMN "pq_items" varchar(1000);--> statement-breakpoint +ALTER TABLE "vendor_pq_criteria_answers" ADD COLUMN "shi_comment" text;--> statement-breakpoint +ALTER TABLE "vendor_pq_criteria_answers" ADD COLUMN "vendor_reply" text;--> statement-breakpoint +ALTER TABLE "pq_lists" ADD CONSTRAINT "pq_lists_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "pq_lists" ADD CONSTRAINT "pq_lists_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "pq_lists" ADD CONSTRAINT "pq_lists_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "site_visit_request_attachments" ADD CONSTRAINT "site_visit_request_attachments_site_visit_request_id_site_visit_requests_id_fk" FOREIGN KEY ("site_visit_request_id") REFERENCES "public"."site_visit_requests"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "site_visit_request_attachments" ADD CONSTRAINT "site_visit_request_attachments_vendor_site_visit_info_id_vendor_site_visit_info_id_fk" FOREIGN KEY ("vendor_site_visit_info_id") REFERENCES "public"."vendor_site_visit_info"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "site_visit_requests" ADD CONSTRAINT "site_visit_requests_investigation_id_vendor_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."vendor_investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "site_visit_requests" ADD CONSTRAINT "site_visit_requests_requester_id_users_id_fk" FOREIGN KEY ("requester_id") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "vendor_site_visit_info" ADD CONSTRAINT "vendor_site_visit_info_site_visit_request_id_site_visit_requests_id_fk" FOREIGN KEY ("site_visit_request_id") REFERENCES "public"."site_visit_requests"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "vendor_site_visit_info" ADD CONSTRAINT "vendor_site_visit_info_submitted_by_users_id_fk" FOREIGN KEY ("submitted_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "pq_criterias" ADD CONSTRAINT "pq_criterias_pq_list_id_pq_lists_id_fk" FOREIGN KEY ("pq_list_id") REFERENCES "public"."pq_lists"("id") ON DELETE cascade ON UPDATE cascade;
\ No newline at end of file |