diff options
Diffstat (limited to 'db/migrations_backup/0096_real_inhumans.sql')
| -rw-r--r-- | db/migrations_backup/0096_real_inhumans.sql | 85 |
1 files changed, 85 insertions, 0 deletions
diff --git a/db/migrations_backup/0096_real_inhumans.sql b/db/migrations_backup/0096_real_inhumans.sql new file mode 100644 index 00000000..d97dc83e --- /dev/null +++ b/db/migrations_backup/0096_real_inhumans.sql @@ -0,0 +1,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");
\ No newline at end of file |