diff options
Diffstat (limited to 'db/migrations/0135_lazy_devos.sql')
| -rw-r--r-- | db/migrations/0135_lazy_devos.sql | 190 |
1 files changed, 190 insertions, 0 deletions
diff --git a/db/migrations/0135_lazy_devos.sql b/db/migrations/0135_lazy_devos.sql new file mode 100644 index 00000000..5aeecf28 --- /dev/null +++ b/db/migrations/0135_lazy_devos.sql @@ -0,0 +1,190 @@ +CREATE TABLE "tech_sales_rfq_items" ( + "id" serial PRIMARY KEY NOT NULL, + "rfq_id" integer NOT NULL, + "item_shipbuilding_id" integer, + "item_offshore_top_id" integer, + "item_offshore_hull_id" integer, + "item_type" varchar(20) NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tech_vendor_attachments" ( + "id" serial PRIMARY KEY NOT NULL, + "vendor_id" integer, + "file_name" varchar(255) NOT NULL, + "file_path" varchar(1024) NOT NULL, + "attachment_type" varchar(50) DEFAULT 'GENERAL', + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tech_vendor_candidates" ( + "id" serial PRIMARY KEY NOT NULL, + "company_name" varchar(255) NOT NULL, + "contact_email" varchar(255), + "contact_phone" varchar(50), + "tax_id" varchar(100), + "address" text, + "country" varchar(100), + "source" varchar(100), + "status" varchar(30) DEFAULT 'COLLECTED' NOT NULL, + "remark" text, + "items" text NOT NULL, + "vendor_id" integer, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tech_vendor_contacts" ( + "id" serial PRIMARY KEY NOT NULL, + "vendor_id" integer NOT NULL, + "contact_name" varchar(255) NOT NULL, + "contact_position" varchar(100), + "contact_email" varchar(255) NOT NULL, + "contact_phone" varchar(50), + "is_primary" boolean DEFAULT false NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tech_vendor_possible_items" ( + "id" serial PRIMARY KEY NOT NULL, + "vendor_id" integer NOT NULL, + "item_code" varchar(100) NOT NULL, + "item_name" varchar(255) NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +CREATE TABLE "tech_vendors" ( + "id" serial PRIMARY KEY NOT NULL, + "vendor_name" varchar(255) NOT NULL, + "vendor_code" varchar(100), + "tax_id" varchar(100), + "country" varchar(100), + "country_eng" varchar(100), + "country_fab" varchar(100), + "agent_name" varchar(255), + "agent_phone" varchar(50), + "agent_email" varchar(255), + "address" varchar(255), + "phone" varchar(50), + "email" varchar(255), + "website" varchar(255), + "tech_vendor_type" text NOT NULL, + "status" varchar(30) DEFAULT 'ACTIVE' NOT NULL, + "representative_name" varchar(255), + "representative_email" varchar(255), + "representative_phone" varchar(50), + "representative_birth" varchar(20), + "items" text, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL +); +--> statement-breakpoint +ALTER TABLE "item_offshore_hull" DROP CONSTRAINT "item_offshore_hull_item_code_items_item_code_fk"; +--> statement-breakpoint +ALTER TABLE "item_offshore_top" DROP CONSTRAINT "item_offshore_top_item_code_items_item_code_fk"; +--> statement-breakpoint +ALTER TABLE "item_shipbuilding" DROP CONSTRAINT "item_shipbuilding_item_code_items_item_code_fk"; +--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comment_attachments" DROP CONSTRAINT "tech_sales_rfq_comment_attachments_vendor_id_vendors_id_fk"; +--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comments" DROP CONSTRAINT "tech_sales_rfq_comments_vendor_id_vendors_id_fk"; +--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" DROP CONSTRAINT "tech_sales_rfqs_item_shipbuilding_id_item_shipbuilding_id_fk"; +--> statement-breakpoint +ALTER TABLE "tech_sales_vendor_quotations" DROP CONSTRAINT "tech_sales_vendor_quotations_vendor_id_vendors_id_fk"; +--> statement-breakpoint +ALTER TABLE "item_offshore_hull" ALTER COLUMN "item_code" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "item_offshore_hull" ALTER COLUMN "work_type" SET DATA TYPE varchar(20);--> statement-breakpoint +ALTER TABLE "item_offshore_hull" ALTER COLUMN "work_type" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "item_offshore_top" ALTER COLUMN "item_code" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "item_offshore_top" ALTER COLUMN "work_type" SET DATA TYPE varchar(20);--> statement-breakpoint +ALTER TABLE "item_offshore_top" ALTER COLUMN "work_type" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "item_shipbuilding" ALTER COLUMN "item_code" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "item_shipbuilding" ALTER COLUMN "work_type" SET DATA TYPE varchar(20);--> statement-breakpoint +ALTER TABLE "item_shipbuilding" ALTER COLUMN "work_type" DROP NOT NULL;--> statement-breakpoint +ALTER TABLE "users" ADD COLUMN "tech_company_id" integer;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD COLUMN "description" text;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD COLUMN "rfq_type" varchar(20) DEFAULT 'SHIP' NOT NULL;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_items" ADD CONSTRAINT "tech_sales_rfq_items_rfq_id_tech_sales_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."tech_sales_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_items" ADD CONSTRAINT "tech_sales_rfq_items_item_shipbuilding_id_item_shipbuilding_id_fk" FOREIGN KEY ("item_shipbuilding_id") REFERENCES "public"."item_shipbuilding"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_items" ADD CONSTRAINT "tech_sales_rfq_items_item_offshore_top_id_item_offshore_top_id_fk" FOREIGN KEY ("item_offshore_top_id") REFERENCES "public"."item_offshore_top"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_items" ADD CONSTRAINT "tech_sales_rfq_items_item_offshore_hull_id_item_offshore_hull_id_fk" FOREIGN KEY ("item_offshore_hull_id") REFERENCES "public"."item_offshore_hull"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_vendor_attachments" ADD CONSTRAINT "tech_vendor_attachments_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_vendor_candidates" ADD CONSTRAINT "tech_vendor_candidates_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_vendor_contacts" ADD CONSTRAINT "tech_vendor_contacts_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_vendor_possible_items" ADD CONSTRAINT "tech_vendor_possible_items_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_vendor_possible_items" ADD CONSTRAINT "tech_vendor_possible_items_item_code_items_item_code_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("item_code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "users" ADD CONSTRAINT "users_tech_company_id_tech_vendors_id_fk" FOREIGN KEY ("tech_company_id") REFERENCES "public"."tech_vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_attachments_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comments" ADD CONSTRAINT "tech_sales_rfq_comments_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_vendor_quotations" ADD CONSTRAINT "tech_sales_vendor_quotations_vendor_id_tech_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."tech_vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" DROP COLUMN "item_shipbuilding_id";--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" DROP COLUMN "project_snapshot";--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" DROP COLUMN "series_snapshot";--> statement-breakpoint +CREATE VIEW "public"."tech_vendor_candidates_with_vendor_info" AS (select "tech_vendor_candidates"."id", "tech_vendor_candidates"."company_name", "tech_vendor_candidates"."contact_email", "tech_vendor_candidates"."contact_phone", "tech_vendor_candidates"."tax_id", "tech_vendor_candidates"."address", "tech_vendor_candidates"."country", "tech_vendor_candidates"."source", "tech_vendor_candidates"."status", "tech_vendor_candidates"."items", "tech_vendor_candidates"."remark", "tech_vendor_candidates"."created_at", "tech_vendor_candidates"."updated_at", "tech_vendors"."vendor_name", "tech_vendors"."vendor_code", "tech_vendors"."created_at" as "vendor_created_at" from "tech_vendor_candidates" left join "tech_vendors" on "tech_vendor_candidates"."vendor_id" = "tech_vendors"."id");--> statement-breakpoint +CREATE VIEW "public"."tech_vendor_detail_view" AS (select "id", "vendor_name", "vendor_code", "tax_id", "address", "country", "country_eng", "country_fab", "agent_name", "agent_phone", "agent_email", "phone", "email", "website", "status", "tech_vendor_type", "representative_name", "representative_email", "representative_phone", "representative_birth", "created_at", "updated_at", + (SELECT COALESCE( + json_agg( + json_build_object( + 'id', c.id, + 'contactName', c.contact_name, + 'contactPosition', c.contact_position, + 'contactEmail', c.contact_email, + 'contactPhone', c.contact_phone, + 'isPrimary', c.is_primary + ) + ), + '[]'::json + ) + FROM vendor_contacts c + WHERE c.vendor_id = tech_vendors.id) + as "contacts", + (SELECT COALESCE( + json_agg( + json_build_object( + 'id', a.id, + 'fileName', a.file_name, + 'filePath', a.file_path, + 'attachmentType', a.attachment_type, + 'createdAt', a.created_at + ) + ORDER BY a.attachment_type, a.created_at DESC + ), + '[]'::json + ) + FROM tech_vendor_attachments a + WHERE a.vendor_id = tech_vendors.id) + as "attachments", + (SELECT COUNT(*) + FROM tech_vendor_attachments a + WHERE a.vendor_id = tech_vendors.id) + as "attachment_count", + (SELECT COUNT(*) + FROM vendor_contacts c + WHERE c.vendor_id = tech_vendors.id) + as "contact_count", + (SELECT COALESCE( + json_agg( + json_build_object( + 'itemCode', i.item_code, + 'itemName', it.item_name + ) + ), + '[]'::json + ) + FROM tech_vendor_possible_items i + LEFT JOIN items it ON i.item_code = it.item_code + WHERE i.vendor_id = tech_vendors.id) + as "possible_items", + (SELECT COUNT(*) + FROM tech_vendor_possible_items i + WHERE i.vendor_id = tech_vendors.id) + as "item_count" from "tech_vendors");--> statement-breakpoint +CREATE VIEW "public"."tech_vendor_items_view" AS (select "tech_vendor_possible_items"."id", "tech_vendor_possible_items"."vendor_id", "items"."item_code", "items"."item_name", "tech_vendor_possible_items"."created_at", "tech_vendor_possible_items"."updated_at" from "tech_vendor_possible_items" left join "items" on "tech_vendor_possible_items"."item_code" = "items"."item_code");--> statement-breakpoint +DROP TYPE "public"."offshore_hull_work_type";--> statement-breakpoint +DROP TYPE "public"."offshore_top_work_type";--> statement-breakpoint +DROP TYPE "public"."work_type";
\ No newline at end of file |