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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
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;
|
