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, 0 insertions, 85 deletions
diff --git a/db/migrations_backup/0096_real_inhumans.sql b/db/migrations_backup/0096_real_inhumans.sql deleted file mode 100644 index d97dc83e..00000000 --- a/db/migrations_backup/0096_real_inhumans.sql +++ /dev/null @@ -1,85 +0,0 @@ -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 |