import { MigrationInterface, QueryRunner } from 'typeorm'; /** * One-shot MD collect schema for server DBs still on 178580: * * 1. Split booking_cash_collections into expense_* + cash_* * 2. Pending Collect-for-MD batches (with ROE) * 3. Office receipt collector + ROE on payment_collections * 4. WhatsApp default templates: Total line before Remarks * * Written against restored server dump `dmc_db_17_08_new` * (migrations through BookingCashCollection1785800000000). */ export class ExpenseAndMdCashCollection1785900000000 implements MigrationInterface { name = 'ExpenseAndMdCashCollection1785900000000'; public async up(qr: QueryRunner): Promise { await this.splitCashCollectionColumns(qr); await this.createCashCollectionBatches(qr); await this.addOfficeCollectorAndRoe(qr); await this.addCollectionTotalToTemplates(qr); } public async down(qr: QueryRunner): Promise { await this.removeCollectionTotalFromTemplates(qr); await this.dropOfficeCollectorAndRoe(qr); await qr.query(`DROP INDEX IF EXISTS "ix_bcc_batch_parent"`); await qr.query(`DROP TABLE IF EXISTS "booking_cash_collection_batches"`); await this.revertCashCollectionColumns(qr); } // ── 1. expense_* / cash_* split ───────────────────────────────── private async splitCashCollectionColumns(qr: QueryRunner): Promise { await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_status"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expected_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_actual_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_party_matches_type"`); // Unique indexes reference `status` in their predicate — drop before rename. await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_transport_item"`); await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_activity_item"`); await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_special_item"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "collected_by_type" TO "expense_collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "collector_name" TO "expense_collector_name"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "supplier_id" TO "expense_supplier_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "source_kind" TO "expense_source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "activity_id" TO "expense_activity_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "driver_id" TO "expense_driver_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expected_amount" TO "expense_expected_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "notes" TO "expense_notes"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "status" TO "expense_status"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "actual_amount" TO "expense_actual_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "collected_at" TO "expense_collected_at"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "difference_reason" TO "expense_difference_reason"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "collected_by" TO "expense_collected_by"`); // Cash-only MD stamps have no expense — these were NOT NULL on 178580. await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "expense_collected_by_type" DROP NOT NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "expense_status" DROP NOT NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "expense_status" DROP DEFAULT`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_collected_by_type" varchar(20) NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_collector_name" varchar(255) NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_supplier_id" uuid NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_source_kind" varchar(20) NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_activity_id" uuid NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_driver_id" uuid NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_expected_amount" numeric(12,2) NOT NULL DEFAULT 0`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_notes" text NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_status" varchar(20) NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_actual_amount" numeric(12,2) NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_collected_at" timestamptz NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ADD COLUMN "cash_collected_by" uuid NULL`); await qr.query(` ALTER TABLE "booking_cash_collections" ADD CONSTRAINT "fk_bcc_cash_supplier" FOREIGN KEY ("cash_supplier_id") REFERENCES "suppliers"("id"), ADD CONSTRAINT "fk_bcc_cash_activity" FOREIGN KEY ("cash_activity_id") REFERENCES "activities"("id"), ADD CONSTRAINT "fk_bcc_cash_driver" FOREIGN KEY ("cash_driver_id") REFERENCES "drivers"("id"), ADD CONSTRAINT "fk_bcc_cash_collected_by" FOREIGN KEY ("cash_collected_by") REFERENCES "users"("id") ON DELETE SET NULL `); await qr.query(` ALTER TABLE "booking_cash_collections" ADD CONSTRAINT "chk_bcc_expense_collected_by_type" CHECK ("expense_collected_by_type" IS NULL OR "expense_collected_by_type" IN ('OWN','SUPPLIER')), ADD CONSTRAINT "chk_bcc_expense_status" CHECK ("expense_status" IS NULL OR "expense_status" IN ('PENDING','COLLECTED','CANCELLED')), ADD CONSTRAINT "chk_bcc_expense_expected_non_neg" CHECK ("expense_expected_amount" >= 0), ADD CONSTRAINT "chk_bcc_expense_actual_non_neg" CHECK ("expense_actual_amount" IS NULL OR "expense_actual_amount" >= 0), ADD CONSTRAINT "chk_bcc_expense_source_kind" CHECK ("expense_source_kind" IS NULL OR "expense_source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL')), ADD CONSTRAINT "chk_bcc_expense_party_matches_type" CHECK ( "expense_collected_by_type" IS NULL OR ( "expense_collected_by_type" = 'OWN' AND "expense_collector_name" IS NOT NULL AND "expense_supplier_id" IS NULL AND "expense_driver_id" IS NULL AND "expense_activity_id" IS NULL AND "expense_source_kind" IS NULL ) OR ( "expense_collected_by_type" = 'SUPPLIER' AND "expense_collector_name" IS NULL AND "expense_supplier_id" IS NOT NULL AND "expense_driver_id" IS NOT NULL AND "expense_source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL') ) ), ADD CONSTRAINT "chk_bcc_cash_collected_by_type" CHECK ("cash_collected_by_type" IS NULL OR "cash_collected_by_type" IN ('OWN','SUPPLIER')), ADD CONSTRAINT "chk_bcc_cash_status" CHECK ("cash_status" IS NULL OR "cash_status" IN ('PENDING','COLLECTED')), ADD CONSTRAINT "chk_bcc_cash_expected_non_neg" CHECK ("cash_expected_amount" >= 0), ADD CONSTRAINT "chk_bcc_cash_actual_non_neg" CHECK ("cash_actual_amount" IS NULL OR "cash_actual_amount" >= 0), ADD CONSTRAINT "chk_bcc_cash_source_kind" CHECK ("cash_source_kind" IS NULL OR "cash_source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL')), ADD CONSTRAINT "chk_bcc_cash_party_matches_type" CHECK ( "cash_collected_by_type" IS NULL OR ( "cash_collected_by_type" = 'OWN' AND "cash_collector_name" IS NOT NULL AND "cash_supplier_id" IS NULL AND "cash_driver_id" IS NULL AND "cash_activity_id" IS NULL AND "cash_source_kind" IS NULL ) OR ( "cash_collected_by_type" = 'SUPPLIER' AND "cash_collector_name" IS NULL AND "cash_supplier_id" IS NOT NULL AND "cash_driver_id" IS NOT NULL AND "cash_source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL') ) ) `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_transport_item" ON "booking_cash_collections" ("tenant_id", "transport_item_id") WHERE "is_deleted" = false AND "transport_item_id" IS NOT NULL `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_activity_item" ON "booking_cash_collections" ("tenant_id", "activity_item_id") WHERE "is_deleted" = false AND "activity_item_id" IS NOT NULL `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_special_item" ON "booking_cash_collections" ("tenant_id", "special_item_id") WHERE "is_deleted" = false AND "special_item_id" IS NOT NULL `); } private async revertCashCollectionColumns(qr: QueryRunner): Promise { await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_special_item"`); await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_activity_item"`); await qr.query(`DROP INDEX IF EXISTS "ux_bcc_live_transport_item"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_party_matches_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_actual_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_expected_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_status"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_cash_collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_party_matches_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_actual_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_expected_non_neg"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_status"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "chk_bcc_expense_collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "fk_bcc_cash_collected_by"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "fk_bcc_cash_driver"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "fk_bcc_cash_activity"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP CONSTRAINT IF EXISTS "fk_bcc_cash_supplier"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_collected_by"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_collected_at"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_actual_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_status"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_notes"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_expected_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_driver_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_activity_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_supplier_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_collector_name"`); await qr.query(`ALTER TABLE "booking_cash_collections" DROP COLUMN IF EXISTS "cash_collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_collected_by" TO "collected_by"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_difference_reason" TO "difference_reason"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_collected_at" TO "collected_at"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_actual_amount" TO "actual_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_status" TO "status"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_notes" TO "notes"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_expected_amount" TO "expected_amount"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_driver_id" TO "driver_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_activity_id" TO "activity_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_source_kind" TO "source_kind"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_supplier_id" TO "supplier_id"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_collector_name" TO "collector_name"`); await qr.query(`ALTER TABLE "booking_cash_collections" RENAME COLUMN "expense_collected_by_type" TO "collected_by_type"`); await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "collected_by_type" SET NOT NULL`); await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "status" SET DEFAULT 'PENDING'`); await qr.query(`ALTER TABLE "booking_cash_collections" ALTER COLUMN "status" SET NOT NULL`); await qr.query(` ALTER TABLE "booking_cash_collections" ADD CONSTRAINT "chk_bcc_collected_by_type" CHECK ("collected_by_type" IN ('OWN','SUPPLIER')), ADD CONSTRAINT "chk_bcc_status" CHECK ("status" IN ('PENDING','COLLECTED','CANCELLED')), ADD CONSTRAINT "chk_bcc_expected_non_neg" CHECK ("expected_amount" >= 0), ADD CONSTRAINT "chk_bcc_actual_non_neg" CHECK ("actual_amount" IS NULL OR "actual_amount" >= 0), ADD CONSTRAINT "chk_bcc_source_kind" CHECK ("source_kind" IS NULL OR "source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL')), ADD CONSTRAINT "chk_bcc_party_matches_type" CHECK ( ( "collected_by_type" = 'OWN' AND "collector_name" IS NOT NULL AND "supplier_id" IS NULL AND "driver_id" IS NULL AND "activity_id" IS NULL AND "source_kind" IS NULL ) OR ( "collected_by_type" = 'SUPPLIER' AND "collector_name" IS NULL AND "supplier_id" IS NOT NULL AND "driver_id" IS NOT NULL AND "source_kind" IN ('TRANSPORT','ACTIVITY','SPECIAL') ) ) `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_transport_item" ON "booking_cash_collections" ("tenant_id", "transport_item_id") WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "transport_item_id" IS NOT NULL `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_activity_item" ON "booking_cash_collections" ("tenant_id", "activity_item_id") WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "activity_item_id" IS NOT NULL `); await qr.query(` CREATE UNIQUE INDEX "ux_bcc_live_special_item" ON "booking_cash_collections" ("tenant_id", "special_item_id") WHERE "is_deleted" = false AND "status" <> 'CANCELLED' AND "special_item_id" IS NOT NULL `); } // ── 2. Collect-for-MD batches ─────────────────────────────────── private async createCashCollectionBatches(qr: QueryRunner): Promise { await qr.query(` CREATE TABLE "booking_cash_collection_batches" ( "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), "tenant_id" uuid NOT NULL, "created_by" uuid NULL, "updated_by" uuid NULL, "created_at" timestamptz NOT NULL DEFAULT now(), "updated_at" timestamptz NOT NULL DEFAULT now(), "is_deleted" boolean NOT NULL DEFAULT false, "booking_cash_collection_id" uuid NOT NULL, "received_amount" numeric(12,2) NOT NULL, "roe" numeric(12,6) NULL, "currency_id" uuid NOT NULL, "party_type" varchar(16) NOT NULL, "customer_mobile" varchar(20) NULL, "source_id" uuid NULL, "customer_name" varchar(200) NULL, "payment_date" date NOT NULL, "mode" varchar(32) NOT NULL, "notes" text NULL, "allocations" jsonb NOT NULL, "status" varchar(20) NOT NULL DEFAULT 'PENDING', "payment_collection_id" uuid NULL, CONSTRAINT "fk_bcc_batch_parent" FOREIGN KEY ("booking_cash_collection_id") REFERENCES "booking_cash_collections"("id") ON DELETE CASCADE, CONSTRAINT "fk_bcc_batch_currency" FOREIGN KEY ("currency_id") REFERENCES "currencies"("id"), CONSTRAINT "chk_bcc_batch_status" CHECK ("status" IN ('PENDING','POSTED')), CONSTRAINT "chk_bcc_batch_party" CHECK ("party_type" IN ('CUSTOMER','SOURCE')), CONSTRAINT "chk_bcc_batch_received" CHECK ("received_amount" > 0), CONSTRAINT "chk_bcc_batch_roe_non_neg" CHECK ("roe" IS NULL OR "roe" >= 0) ) `); await qr.query(` CREATE INDEX "ix_bcc_batch_parent" ON "booking_cash_collection_batches" ("tenant_id", "booking_cash_collection_id") WHERE "is_deleted" = false `); } // ── 3. Office receipt collector + ROE ─────────────────────────── private async addOfficeCollectorAndRoe(qr: QueryRunner): Promise { await qr.query(`ALTER TABLE "payment_collections" ADD COLUMN "collected_by_type" varchar(20) NULL`); await qr.query(`ALTER TABLE "payment_collections" ADD COLUMN "collector_name" varchar(255) NULL`); await qr.query(`ALTER TABLE "payment_collections" ADD COLUMN "collector_supplier_id" uuid NULL`); await qr.query(`ALTER TABLE "payment_collections" ADD COLUMN "collector_driver_id" uuid NULL`); await qr.query(`ALTER TABLE "payment_collections" ADD COLUMN "roe" numeric(12,6) NULL`); await qr.query(` ALTER TABLE "payment_collections" ADD CONSTRAINT "chk_pc_collected_by_type" CHECK ("collected_by_type" IS NULL OR "collected_by_type" IN ('OWN','SUPPLIER')), ADD CONSTRAINT "fk_pc_collector_supplier" FOREIGN KEY ("collector_supplier_id") REFERENCES "suppliers"("id"), ADD CONSTRAINT "fk_pc_collector_driver" FOREIGN KEY ("collector_driver_id") REFERENCES "drivers"("id"), ADD CONSTRAINT "chk_pc_roe_non_neg" CHECK ("roe" IS NULL OR "roe" >= 0) `); } private async dropOfficeCollectorAndRoe(qr: QueryRunner): Promise { await qr.query(`ALTER TABLE "payment_collections" DROP CONSTRAINT IF EXISTS "chk_pc_roe_non_neg"`); await qr.query(`ALTER TABLE "payment_collections" DROP CONSTRAINT IF EXISTS "fk_pc_collector_driver"`); await qr.query(`ALTER TABLE "payment_collections" DROP CONSTRAINT IF EXISTS "fk_pc_collector_supplier"`); await qr.query(`ALTER TABLE "payment_collections" DROP CONSTRAINT IF EXISTS "chk_pc_collected_by_type"`); await qr.query(`ALTER TABLE "payment_collections" DROP COLUMN IF EXISTS "roe"`); await qr.query(`ALTER TABLE "payment_collections" DROP COLUMN IF EXISTS "collector_driver_id"`); await qr.query(`ALTER TABLE "payment_collections" DROP COLUMN IF EXISTS "collector_supplier_id"`); await qr.query(`ALTER TABLE "payment_collections" DROP COLUMN IF EXISTS "collector_name"`); await qr.query(`ALTER TABLE "payment_collections" DROP COLUMN IF EXISTS "collected_by_type"`); } // ── 4. WhatsApp Total before Remarks ──────────────────────────── private collectionTotalBlock(): string { return '{{#if collection_total}}*Total:* {{collection_total}}\n{{/if}}'; } private async addCollectionTotalToTemplates(qr: QueryRunner): Promise { const totalIf = this.collectionTotalBlock(); await qr.query( `UPDATE communication_templates SET body_template = replace( body_template, '{{#if remarks}}*Remarks:* {{remarks}}', $1 || '{{#if remarks}}*Remarks:* {{remarks}}' ) WHERE channel = 'WHATSAPP' AND context IN ('MOVEMENT_CUSTOMER', 'MOVEMENT_SUPPLIER') AND is_default = true AND is_deleted = false AND body_template LIKE '%{{#if remarks}}*Remarks:* {{remarks}}%' AND body_template NOT LIKE '%collection_total%'`, [totalIf], ); await qr.query( `UPDATE communication_templates SET body_template = replace( body_template, '*REMARKS:* {{remarks}}', $1 || '*REMARKS:* {{remarks}}' ) WHERE channel = 'WHATSAPP' AND context IN ('MOVEMENT_CUSTOMER', 'MOVEMENT_SUPPLIER') AND is_default = true AND is_deleted = false AND body_template LIKE '%*REMARKS:* {{remarks}}%' AND body_template NOT LIKE '%collection_total%'`, [totalIf], ); } private async removeCollectionTotalFromTemplates(qr: QueryRunner): Promise { await qr.query( `UPDATE communication_templates SET body_template = replace(body_template, $1, '') WHERE channel = 'WHATSAPP' AND context IN ('MOVEMENT_CUSTOMER', 'MOVEMENT_SUPPLIER') AND is_default = true AND is_deleted = false AND body_template LIKE '%collection_total%'`, [this.collectionTotalBlock()], ); } }