diff options
Diffstat (limited to 'db/migrations/0097_sharp_tana_nile.sql')
| -rw-r--r-- | db/migrations/0097_sharp_tana_nile.sql | 116 |
1 files changed, 116 insertions, 0 deletions
diff --git a/db/migrations/0097_sharp_tana_nile.sql b/db/migrations/0097_sharp_tana_nile.sql new file mode 100644 index 00000000..e56195c7 --- /dev/null +++ b/db/migrations/0097_sharp_tana_nile.sql @@ -0,0 +1,116 @@ +CREATE TABLE "tag_class_attributes" ( + "id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tag_class_attributes_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1), + "tag_class_id" integer NOT NULL, + "att_id" varchar(50) NOT NULL, + "def_val" text, + "uom_id" varchar(50), + "seq" integer DEFAULT 0, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + CONSTRAINT "uniq_att_id_in_tag_class" UNIQUE("tag_class_id","att_id") +); +--> statement-breakpoint +CREATE TABLE "tech_sales_attachments" ( + "id" serial PRIMARY KEY NOT NULL, + "attachment_type" varchar(50) NOT NULL, + "tech_sales_rfq_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 +); +--> statement-breakpoint +CREATE TABLE "tech_sales_rfq_comment_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 "tech_sales_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, + "is_read" 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 "tech_sales_rfqs" ( + "id" serial PRIMARY KEY NOT NULL, + "rfq_code" varchar(50), + "item_id" integer NOT NULL, + "bidding_project_id" integer, + "material_code" varchar(255), + "due_date" date NOT NULL, + "rfq_send_date" date, + "status" varchar(30) DEFAULT 'RFQ Created' NOT NULL, + "pic_code" varchar(50), + "remark" text, + "sent_by" integer, + "created_by" integer NOT NULL, + "updated_by" integer NOT NULL, + "created_at" timestamp DEFAULT now() NOT NULL, + "updated_at" timestamp DEFAULT now() NOT NULL, + "cancel_reason" text, + "project_snapshot" jsonb, + "series_snapshot" jsonb, + CONSTRAINT "tech_sales_rfqs_rfq_code_unique" UNIQUE("rfq_code") +); +--> statement-breakpoint +CREATE TABLE "tech_sales_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_price" numeric, + "currency" varchar(10), + "valid_until" date, + "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 +ALTER TABLE "tag_class_attributes" ADD CONSTRAINT "tag_class_attributes_tag_class_id_tag_classes_id_fk" FOREIGN KEY ("tag_class_id") REFERENCES "public"."tag_classes"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_attachments" ADD CONSTRAINT "tech_sales_attachments_tech_sales_rfq_id_tech_sales_rfqs_id_fk" FOREIGN KEY ("tech_sales_rfq_id") REFERENCES "public"."tech_sales_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_attachments" ADD CONSTRAINT "tech_sales_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 "tech_sales_rfq_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_attachments_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_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_attachments_comment_id_tech_sales_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."tech_sales_rfq_comments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_attachments_quotation_id_tech_sales_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."tech_sales_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfq_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_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 "tech_sales_rfq_comment_attachments" ADD CONSTRAINT "tech_sales_rfq_comment_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 "tech_sales_rfq_comments" ADD CONSTRAINT "tech_sales_rfq_comments_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_comments" ADD CONSTRAINT "tech_sales_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 "tech_sales_rfq_comments" ADD CONSTRAINT "tech_sales_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 "tech_sales_rfq_comments" ADD CONSTRAINT "tech_sales_rfq_comments_parent_comment_id_tech_sales_rfq_comments_id_fk" FOREIGN KEY ("parent_comment_id") REFERENCES "public"."tech_sales_rfq_comments"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD CONSTRAINT "tech_sales_rfqs_item_id_items_id_fk" FOREIGN KEY ("item_id") REFERENCES "public"."items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD CONSTRAINT "tech_sales_rfqs_bidding_project_id_bidding_projects_id_fk" FOREIGN KEY ("bidding_project_id") REFERENCES "public"."bidding_projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD CONSTRAINT "tech_sales_rfqs_sent_by_users_id_fk" FOREIGN KEY ("sent_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_rfqs" ADD CONSTRAINT "tech_sales_rfqs_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 "tech_sales_rfqs" ADD CONSTRAINT "tech_sales_rfqs_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint +ALTER TABLE "tech_sales_vendor_quotations" ADD CONSTRAINT "tech_sales_vendor_quotations_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_vendor_quotations" ADD CONSTRAINT "tech_sales_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 +CREATE INDEX "tag_class_attributes_seq_idx" ON "tag_class_attributes" USING btree ("seq");
\ No newline at end of file |