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
  );