import { type MigrationInterface, type QueryRunner } from 'typeorm'; /** * Payment Settlement — Phase F (stakeholder regroup follow-up). * * 1. Generalise `payment_settlements` for SUPPLIER-side rows: * party_type (CUSTOMER | SUPPLIER) default CUSTOMER * supplier_id (FK → suppliers, nullable; set when * party_type = SUPPLIER) * source_item_type (e.g. 'booking_transport_item') nullable * source_item_id UUID, nullable * * Semantics: existing `booking_id` is kept on EVERY row regardless * of party — supplier-side rows still belong to a booking (the one * whose transport item ops just assigned the supplier to). That * preserves the "open booking → see all related settlements" UX * without a second join. * * `type` (COLLECTION / REFUND) is reused with party-relative * semantics: * COLLECTION = money INTO us * REFUND = money OUT of us * i.e. a SUPPLIER + REFUND row is a payout we owe the supplier. * FE relabels accordingly ("Payout" instead of "Refund" for * supplier party). * * 2. Receipts on settlement_payments: * receipt_no VARCHAR(20) e.g. RCP-2026-0001 / RFD-2026-0001 * receipt_year INT (used for the annual reset) * * Backed by a new `settlement_receipt_sequences` table — one row * per (tenant, year, prefix). Atomic SELECT FOR UPDATE in the * service guarantees uniqueness under concurrency. * * 3. Void support on settlement_payments: * is_void BOOLEAN default false * voided_at TIMESTAMPTZ * voided_by UUID * void_reason TEXT * * Admin-only endpoint (Phase F controller). Voided payments stay * in the ledger (audit) but stop counting towards the parent's * amount_settled. * * 4. Sales Head — seed PAYMENT_SETTLEMENT:view permission. Earlier * migration seeded only Admin (delete) + Ops Manager (edit). */ export class PaymentSettlementPhaseF1780000000000 implements MigrationInterface { async up(qr: QueryRunner): Promise { // ── 1. payment_settlements: party + source-item generalisation ── await qr.query(` ALTER TABLE payment_settlements ADD COLUMN IF NOT EXISTS party_type VARCHAR(20) NOT NULL DEFAULT 'CUSTOMER', ADD COLUMN IF NOT EXISTS supplier_id UUID REFERENCES suppliers(id) ON DELETE SET NULL, ADD COLUMN IF NOT EXISTS source_item_type VARCHAR(50), ADD COLUMN IF NOT EXISTS source_item_id UUID; `); await qr.query(` ALTER TABLE payment_settlements ADD CONSTRAINT chk_settlement_party_type CHECK (party_type IN ('CUSTOMER', 'SUPPLIER')); `); await qr.query(` ALTER TABLE payment_settlements ADD CONSTRAINT chk_settlement_supplier_only_when_supplier_party CHECK ( (party_type = 'SUPPLIER' AND supplier_id IS NOT NULL) OR (party_type = 'CUSTOMER' AND supplier_id IS NULL) ); `); // Idempotency guard for SUPPLIER-side rows: at most one row per // (tenant, source_item_type, source_item_id) — re-assigning the // same supplier on the same transport item must not duplicate. await qr.query(` CREATE UNIQUE INDEX IF NOT EXISTS uq_settlement_per_source_item ON payment_settlements (tenant_id, source_item_type, source_item_id) WHERE party_type = 'SUPPLIER' AND is_deleted = false; `); await qr.query(` CREATE INDEX IF NOT EXISTS idx_settlement_tenant_party_status ON payment_settlements (tenant_id, party_type, status, due_date) WHERE is_deleted = false; `); // ── 2. settlement_payments: receipt + void support ────────────── await qr.query(` ALTER TABLE settlement_payments ADD COLUMN IF NOT EXISTS receipt_no VARCHAR(20), ADD COLUMN IF NOT EXISTS receipt_year INT, ADD COLUMN IF NOT EXISTS is_void BOOLEAN NOT NULL DEFAULT FALSE, ADD COLUMN IF NOT EXISTS voided_at TIMESTAMPTZ, ADD COLUMN IF NOT EXISTS voided_by UUID, ADD COLUMN IF NOT EXISTS void_reason TEXT; `); // Receipt number is unique per (tenant, year, prefix) — partial // index because legacy rows seeded before Phase F have no receipt. await qr.query(` CREATE UNIQUE INDEX IF NOT EXISTS uq_settlement_payment_receipt ON settlement_payments (tenant_id, receipt_year, receipt_no) WHERE receipt_no IS NOT NULL AND is_deleted = false; `); // ── 3. Receipt-number sequence table ──────────────────────────── await qr.query(` CREATE TABLE IF NOT EXISTS settlement_receipt_sequences ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id UUID NOT NULL, year INT NOT NULL, prefix VARCHAR(8) NOT NULL, last_number INT NOT NULL DEFAULT 0, created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT uq_receipt_seq UNIQUE (tenant_id, year, prefix) ); `); // ── 4. RBAC: Sales Head VIEW seed ─────────────────────────────── await qr.query(` INSERT INTO role_permissions (id, tenant_id, role_id, module, access_level, is_deleted, created_at, updated_at) SELECT gen_random_uuid(), r.tenant_id, r.id, 'payment_settlement', 'view', false, NOW(), NOW() FROM roles r WHERE r.is_deleted = false AND LOWER(r.name) = 'sales head' AND NOT EXISTS ( SELECT 1 FROM role_permissions rp WHERE rp.tenant_id = r.tenant_id AND rp.role_id = r.id AND rp.module = 'payment_settlement' AND rp.is_deleted = false ); `); } async down(qr: QueryRunner): Promise { await qr.query( `DELETE FROM role_permissions WHERE module = 'payment_settlement' AND access_level = 'view' AND role_id IN (SELECT id FROM roles WHERE LOWER(name) = 'sales head');`, ); await qr.query(`DROP TABLE IF EXISTS settlement_receipt_sequences;`); await qr.query(`DROP INDEX IF EXISTS uq_settlement_payment_receipt;`); await qr.query(` ALTER TABLE settlement_payments DROP COLUMN IF EXISTS receipt_no, DROP COLUMN IF EXISTS receipt_year, DROP COLUMN IF EXISTS is_void, DROP COLUMN IF EXISTS voided_at, DROP COLUMN IF EXISTS voided_by, DROP COLUMN IF EXISTS void_reason; `); await qr.query(`DROP INDEX IF EXISTS idx_settlement_tenant_party_status;`); await qr.query(`DROP INDEX IF EXISTS uq_settlement_per_source_item;`); await qr.query( `ALTER TABLE payment_settlements DROP CONSTRAINT IF EXISTS chk_settlement_supplier_only_when_supplier_party, DROP CONSTRAINT IF EXISTS chk_settlement_party_type;`, ); await qr.query(` ALTER TABLE payment_settlements DROP COLUMN IF EXISTS party_type, DROP COLUMN IF EXISTS supplier_id, DROP COLUMN IF EXISTS source_item_type, DROP COLUMN IF EXISTS source_item_id; `); } }