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
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
|
CREATE TABLE "procurement_attachments" (
"id" serial PRIMARY KEY NOT NULL,
"attachment_type" varchar(50) NOT NULL,
"procurement_rfqs_id" integer,
"procurement_rfq_details_id" integer,
"file_name" varchar(255) NOT NULL,
"original_file_name" varchar(255) NOT NULL,
"file_path" varchar(512) NOT NULL,
"file_size" integer,
"file_type" varchar(100),
"description" varchar(500),
"created_by" integer NOT NULL,
"created_at" timestamp DEFAULT now() NOT NULL,
CONSTRAINT "attachment_type_check" CHECK ("procurement_attachments"."procurement_rfqs_id" IS NOT NULL OR "procurement_attachments"."procurement_rfq_details_id" IS NOT NULL)
);
--> statement-breakpoint
CREATE TABLE "procurement_quotation_items" (
"id" serial PRIMARY KEY NOT NULL,
"quotation_id" integer NOT NULL,
"pr_item_id" integer NOT NULL,
"material_code" varchar(50),
"material_description" varchar(255),
"quantity" numeric NOT NULL,
"uom" varchar(20),
"unit_price" numeric NOT NULL,
"total_price" numeric NOT NULL,
"currency" varchar(10) DEFAULT 'USD',
"vendor_material_code" varchar(50),
"vendor_material_description" varchar(255),
"delivery_date" date,
"lead_time_in_days" integer,
"tax_rate" numeric,
"tax_amount" numeric,
"discount_rate" numeric,
"discount_amount" numeric,
"remark" text,
"is_alternative" boolean DEFAULT false,
"is_recommended" boolean DEFAULT false,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_attachments" (
"id" serial PRIMARY KEY NOT NULL,
"rfq_id" integer NOT NULL,
"comment_id" integer,
"quotation_id" integer,
"file_name" varchar(255) NOT NULL,
"file_size" integer NOT NULL,
"file_type" varchar(100),
"file_path" varchar(500) NOT NULL,
"is_vendor_upload" boolean DEFAULT false,
"uploaded_by" integer,
"vendor_id" integer,
"uploaded_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_rfq_comments" (
"id" serial PRIMARY KEY NOT NULL,
"rfq_id" integer NOT NULL,
"vendor_id" integer,
"user_id" integer,
"content" text NOT NULL,
"is_vendor_comment" boolean DEFAULT false,
"parent_comment_id" integer,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "procurement_vendor_quotations" (
"id" serial PRIMARY KEY NOT NULL,
"rfq_id" integer NOT NULL,
"vendor_id" integer NOT NULL,
"quotation_code" varchar(50),
"quotation_version" integer DEFAULT 1,
"total_items_count" integer DEFAULT 0,
"sub_total" numeric DEFAULT '0',
"tax_total" numeric DEFAULT '0',
"discount_total" numeric DEFAULT '0',
"total_price" numeric DEFAULT '0',
"currency" varchar(10) DEFAULT 'USD',
"valid_until" date,
"estimated_delivery_date" date,
"payment_terms_code" varchar(50),
"incoterms_code" varchar(20),
"incoterms_detail" varchar(255),
"status" varchar(30) DEFAULT 'Draft' NOT NULL,
"remark" text,
"rejection_reason" text,
"submitted_at" timestamp,
"accepted_at" timestamp,
"created_by" integer,
"updated_by" integer,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
DROP VIEW "public"."procurement_rfq_details_view";--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfq_details_id_procurement_rfq_details_id_fk" FOREIGN KEY ("procurement_rfq_details_id") REFERENCES "public"."procurement_rfq_details"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_pr_item_id_pr_items_id_fk" FOREIGN KEY ("pr_item_id") REFERENCES "public"."pr_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_parent_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("parent_comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_payment_terms_code_payment_terms_code_fk" FOREIGN KEY ("payment_terms_code") REFERENCES "public"."payment_terms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."procurement_rfq_details_view" AS (select "rfq_details"."id" as "detail_id", "rfqs"."id" as "rfq_id", "rfqs"."rfq_code" as "rfq_code", (
SELECT COUNT(*)
FROM procurement_rfq_comments
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
) as "comment_count", (
SELECT created_at
FROM procurement_rfq_comments
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
ORDER BY created_at DESC LIMIT 1
) as "last_comment_date", (
SELECT created_at
FROM procurement_rfq_comments
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id" AND is_vendor_comment = true
ORDER BY created_at DESC LIMIT 1
) as "last_vendor_comment_date", (
SELECT COUNT(*)
FROM procurement_rfq_attachments
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
) as "attachment_count", (
SELECT COUNT(*) > 0
FROM procurement_vendor_quotations
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
) as "has_quotation", (
SELECT status
FROM procurement_vendor_quotations
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
ORDER BY created_at DESC LIMIT 1
) as "quotation_status", (
SELECT total_price
FROM procurement_vendor_quotations
WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
ORDER BY created_at DESC LIMIT 1
) as "quotation_total_price" from "procurement_rfq_details" "rfq_details" left join "procurement_rfqs" "rfqs" on "rfq_details"."procurement_rfqs_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "items" on "rfqs"."item_id" = "items"."id" left join "vendors" on "rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "rfq_details"."updated_by" = "updated_by_user"."id");
|
