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
|
CREATE TABLE "poa" (
"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "poa_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
"contract_no" varchar(100) NOT NULL,
"original_contract_no" varchar(100) NOT NULL,
"project_id" integer NOT NULL,
"vendor_id" integer NOT NULL,
"original_contract_name" varchar(255) NOT NULL,
"original_status" varchar(50) NOT NULL,
"delivery_terms" text,
"delivery_date" date,
"delivery_location" varchar(255),
"currency" varchar(10),
"total_amount" numeric(12, 2),
"discount" numeric(12, 2),
"tax" numeric(12, 2),
"shipping_fee" numeric(12, 2),
"net_total" numeric(12, 2),
"change_reason" text,
"approval_status" varchar(50) DEFAULT 'PENDING',
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_investigation_attachments" (
"id" serial PRIMARY KEY NOT NULL,
"investigation_id" integer NOT NULL,
"file_name" varchar(255) NOT NULL,
"file_path" varchar(1024) NOT NULL,
"attachment_type" varchar(50) DEFAULT 'REPORT',
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "vendor_investigations" (
"id" serial PRIMARY KEY NOT NULL,
"vendor_id" integer NOT NULL,
"status" varchar(50) DEFAULT 'PLANNED' NOT NULL,
"scheduled_start_at" timestamp,
"scheduled_end_at" timestamp,
"completed_at" timestamp,
"investigation_notes" text,
"created_at" timestamp DEFAULT now() NOT NULL,
"updated_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_original_contract_no_contracts_contract_no_fk" FOREIGN KEY ("original_contract_no") REFERENCES "public"."contracts"("contract_no") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "poa" ADD CONSTRAINT "poa_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "vendor_investigation_attachments" ADD CONSTRAINT "vendor_investigation_attachments_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 "vendor_investigations" ADD CONSTRAINT "vendor_investigations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE VIEW "public"."poa_detail_view" AS (select "poa"."id", "poa"."contract_no", "contracts"."project_id", "contracts"."vendor_id", "poa"."change_reason", "poa"."approval_status", "contracts"."contract_name" as "original_contract_name", "contracts"."status" as "original_status", "contracts"."start_date" as "original_start_date", "contracts"."end_date" as "original_end_date", "poa"."delivery_terms", "poa"."delivery_date", "poa"."delivery_location", "poa"."currency", "poa"."total_amount", "poa"."discount", "poa"."tax", "poa"."shipping_fee", "poa"."net_total", "poa"."created_at", "poa"."updated_at", EXISTS (
SELECT 1
FROM "contract_envelopes"
WHERE "contract_envelopes"."contract_id" = "poa"."id"
) as "has_signature" from "poa" left join "contracts" on "poa"."contract_no" = "contracts"."contract_no");--> statement-breakpoint
CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."status", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."completed_at", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendors"."id", "vendors"."vendor_name", "vendors"."vendor_code", "vendors"."tax_id", "vendors"."status", "vendors"."country", "vendors"."email", "vendors"."phone", "vendors"."website", "vendors"."created_at", "vendors"."updated_at", (
SELECT COALESCE(
json_agg(json_build_object(
'id', c.id,
'contactName', c.contact_name,
'contactEmail', c.contact_email,
'contactPhone', c.contact_phone,
'contactPosition', c.contact_position,
'isPrimary', c.is_primary,
'createdAt', c.created_at,
'updatedAt', c.updated_at
)),
'[]'::json
)
FROM vendor_contacts c
WHERE c.vendor_id = "vendors"."id"
) as "contacts", (
SELECT COALESCE(
json_agg(json_build_object(
'id', pi.id,
'vendorId', pi.vendor_id,
'itemCode', pi.item_code,
'createdAt', pi.created_at,
'updatedAt', pi.updated_at
)),
'[]'::json
)
FROM vendor_possible_items pi
WHERE pi.vendor_id = "vendors"."id"
) as "possibleItems" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id");
|
