import { type MigrationInterface, type QueryRunner } from 'typeorm'; /** * Tax Bifurcation — Step 2 of the stakeholder-locked tax-bifurcation * rollout (spec finalised 2026-06-12). * * Per-line snapshot model: every "money cell" (selling price, floor, * supplier cost, customer instalment) carries a JSONB snapshot of the * tax slab + per-component breakdown frozen at write time. New rows * computed via the pure helper shipped in Step 1 * (common/services/tax-bifurcation.helper.ts). * * What this migration does: * * PART A — TRANSACTIONAL WIPE * Per stakeholder direction (2026-06-12) — no backfill of the * bifurcation onto existing rows. Wipe queries, quotes, bookings, * their child line tables, settlements, payments, notifications * and audit_logs as a single transaction. Mirrors the * scripts/wipe-transactional.sql shape so masters stay intact. * * PART B — MASTER tax_slab_id (effective-slab resolver foundation) * destinations.tax_slab_id default for the destination * transport_services.tax_slab_id per-service override * activities.tax_slab_id per-service override * suppliers.tax_slab_id supplier-side override (cost flow) * suppliers.reverse_charge_applicable foundation for the Phase-2 * reverse-charge accountant flow; * column added now to avoid a * second migration later * * PART C — PRICING MATRIX BIFURCATION * transport_pricing + activity_pricing * For each cell in (default + 4 seasons) × (sell + floor), add: * *_base_price (numeric(12,2) nullable) * *_tax_amount (numeric(12,2) nullable) * The existing inclusive *_price columns are kept as-is — services * recompute base + tax on save from the destination/service slab * and write both. Reads switch to the new columns gradually. * * PART D — LINE-TABLE tax_snapshot JSONB * query_quote_hotels / transports / activities / specials * booking_hotel_items / transport_items / activity_items / special_items * Each gets `tax_snapshot` JSONB — frozen at line save. * * PART E — SUPPLIER-COST bifurcation * booking_transport_items.cost_tax_snapshot JSONB * booking_activity_items.cost_tax_snapshot JSONB * Captures the supplier-side breakdown of actual_supplier_cost. * * PART F — SETTLEMENT INSTALMENT bifurcation * settlement_payments.tax_snapshot JSONB — per-instalment customer * payment bifurcation. Accountant report (parked) reads from these. * * No indexes added on the new JSONB columns — these are snapshots * read with the row, never filtered by. Add a GIN index later if a * report query needs it. * * Reversibility: * - The schema additions in down() drop every column we add (clean * reversal). * - The wipe in up() is intentionally irreversible — we can't put * back data we deleted. If a redo is needed, restore from a * pre-deploy dump. */ export class TaxBifurcationSchema1781100000000 implements MigrationInterface { public async up(qr: QueryRunner): Promise { // ─── PART A ── Wipe transactional ─────────────────────────────────── // REMOVED 2026-06-15 (client request). The original migration also // deleted every row from queries / query_quotes / bookings / line // items / settlements / audit_logs because the bifurcation snapshot // wasn't safely populatable for pre-existing rows. That blanket wipe // surprised operators who weren't expecting a destructive deploy. // // Result of the removal: pre-bifurcation rows survive the migration // with NULL tax_snapshot / *_base_price / *_tax_amount columns. // Reads on those NULL snapshots fall through `bifurcateInclusive` // to "exempt" (base = total, tax = 0), which is the same display // any non-Dubai destination produces. Quote/booking edit saves // resolve and persist a fresh snapshot, hydrating the row on first // touch — no orphaning, no broken totals. // // scripts/wipe-transactional.sql still exists for operators who // explicitly opt into a fresh-data deploy (e.g. dev resets). It is // NOT invoked from any automated path. // // ─── PART B ── Master tax_slab_id ─────────────────────────────────── await qr.query(` ALTER TABLE destinations ADD COLUMN IF NOT EXISTS tax_slab_id uuid REFERENCES tax_slabs(id); `); await qr.query(` ALTER TABLE transport_services ADD COLUMN IF NOT EXISTS tax_slab_id uuid REFERENCES tax_slabs(id); `); await qr.query(` ALTER TABLE activities ADD COLUMN IF NOT EXISTS tax_slab_id uuid REFERENCES tax_slabs(id); `); await qr.query(` ALTER TABLE suppliers ADD COLUMN IF NOT EXISTS tax_slab_id uuid REFERENCES tax_slabs(id), ADD COLUMN IF NOT EXISTS reverse_charge_applicable boolean NOT NULL DEFAULT false; `); // ─── PART C ── Pricing matrix bifurcation ─────────────────────────── // Five tiers (default + 4 seasons) × two matrices (sell, floor) = 10 // cells per pricing row; each cell adds a base + tax_amount column. const tiers = ['default', 'season_1', 'season_2', 'season_3', 'season_4']; for (const tier of tiers) { const sellBase = `${tier}_base_price`; const sellTax = `${tier}_tax_amount`; const floorBase = `floor_${tier}_base_price`; const floorTax = `floor_${tier}_tax_amount`; await qr.query(` ALTER TABLE transport_pricing ADD COLUMN IF NOT EXISTS ${sellBase} numeric(12,2), ADD COLUMN IF NOT EXISTS ${sellTax} numeric(12,2), ADD COLUMN IF NOT EXISTS ${floorBase} numeric(12,2), ADD COLUMN IF NOT EXISTS ${floorTax} numeric(12,2); `); await qr.query(` ALTER TABLE activity_pricing ADD COLUMN IF NOT EXISTS ${sellBase} numeric(12,2), ADD COLUMN IF NOT EXISTS ${sellTax} numeric(12,2), ADD COLUMN IF NOT EXISTS ${floorBase} numeric(12,2), ADD COLUMN IF NOT EXISTS ${floorTax} numeric(12,2); `); } // ─── PART D ── Line-table tax_snapshot ────────────────────────────── const lineTables = [ 'query_quote_hotels', 'query_quote_transports', 'query_quote_activities', 'query_quote_specials', 'booking_hotel_items', 'booking_transport_items', 'booking_activity_items', 'booking_special_items', ]; for (const t of lineTables) { await qr.query(`ALTER TABLE ${t} ADD COLUMN IF NOT EXISTS tax_snapshot jsonb;`); } // ─── PART E ── Supplier-cost bifurcation ──────────────────────────── await qr.query(` ALTER TABLE booking_transport_items ADD COLUMN IF NOT EXISTS cost_tax_snapshot jsonb; `); await qr.query(` ALTER TABLE booking_activity_items ADD COLUMN IF NOT EXISTS cost_tax_snapshot jsonb; `); // ─── PART F ── Settlement instalment bifurcation ──────────────────── await qr.query(` ALTER TABLE settlement_payments ADD COLUMN IF NOT EXISTS tax_snapshot jsonb; `); } public async down(qr: QueryRunner): Promise { // Schema reversal only — the wipe in up() is irreversible. await qr.query(`ALTER TABLE settlement_payments DROP COLUMN IF EXISTS tax_snapshot;`); await qr.query(` ALTER TABLE booking_activity_items DROP COLUMN IF EXISTS cost_tax_snapshot; `); await qr.query(` ALTER TABLE booking_transport_items DROP COLUMN IF EXISTS cost_tax_snapshot; `); const lineTables = [ 'query_quote_hotels', 'query_quote_transports', 'query_quote_activities', 'query_quote_specials', 'booking_hotel_items', 'booking_transport_items', 'booking_activity_items', 'booking_special_items', ]; for (const t of lineTables) { await qr.query(`ALTER TABLE ${t} DROP COLUMN IF EXISTS tax_snapshot;`); } const tiers = ['default', 'season_1', 'season_2', 'season_3', 'season_4']; for (const tier of tiers) { await qr.query(` ALTER TABLE activity_pricing DROP COLUMN IF EXISTS ${tier}_base_price, DROP COLUMN IF EXISTS ${tier}_tax_amount, DROP COLUMN IF EXISTS floor_${tier}_base_price, DROP COLUMN IF EXISTS floor_${tier}_tax_amount; `); await qr.query(` ALTER TABLE transport_pricing DROP COLUMN IF EXISTS ${tier}_base_price, DROP COLUMN IF EXISTS ${tier}_tax_amount, DROP COLUMN IF EXISTS floor_${tier}_base_price, DROP COLUMN IF EXISTS floor_${tier}_tax_amount; `); } await qr.query(` ALTER TABLE suppliers DROP COLUMN IF EXISTS reverse_charge_applicable, DROP COLUMN IF EXISTS tax_slab_id; `); await qr.query(`ALTER TABLE activities DROP COLUMN IF EXISTS tax_slab_id;`); await qr.query(`ALTER TABLE transport_services DROP COLUMN IF EXISTS tax_slab_id;`); await qr.query(`ALTER TABLE destinations DROP COLUMN IF EXISTS tax_slab_id;`); } }