1 files changed, 36 insertions, 0 deletions
diff --git a/db/migrations/0128_sad_vermin.sql b/db/migrations/0128_sad_vermin.sql
new file mode 100644
index 00000000..86c27be9
--- /dev/null
+++ b/db/migrations/0128_sad_vermin.sql
@@ -0,0 +1,36 @@
+DROP INDEX "latest_revision_idx";--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" ADD COLUMN "current_revision" varchar(10) DEFAULT 'Rev.0' NOT NULL;--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" ADD COLUMN "latest_revision_id" integer;--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" ADD COLUMN "updated_at" timestamp DEFAULT now() NOT NULL;--> statement-breakpoint
+CREATE UNIQUE INDEX "attachment_revision_idx" ON "b_rfq_attachment_revisions" USING btree ("attachment_id","revision_no");--> statement-breakpoint
+CREATE UNIQUE INDEX "latest_revision_idx" ON "b_rfq_attachment_revisions" USING btree ("attachment_id","is_latest") WHERE "b_rfq_attachment_revisions"."is_latest" = $1;--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_name";--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" DROP COLUMN "original_file_name";--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_path";--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_size";--> statement-breakpoint
+ALTER TABLE "b_rfq_attachments" DROP COLUMN "file_type";--> statement-breakpoint
+CREATE VIEW "public"."attachments_with_latest_revision" AS (
+    SELECT 
+      a.id as attachment_id,
+      a.attachment_type,
+      a.serial_no,
+      a.rfq_id,
+      a.description,
+      a.current_revision,
+      
+      r.id as revision_id,
+      r.file_name,
+      r.original_file_name,
+      r.file_path,
+      r.file_size,
+      r.file_type,
+      r.revision_comment,
+      
+      a.created_by,
+      u.name as created_by_name,
+      a.created_at,
+      a.updated_at
+    FROM b_rfq_attachments a
+    LEFT JOIN b_rfq_attachment_revisions r ON a.latest_revision_id = r.id
+    LEFT JOIN users u ON a.created_by = u.id
+  );
+\ No newline at end of file