-- ============================================================================
-- Production DB drift check + fix for the batch/window flow.
--
-- WHY: BookingBatchService.reserve() calls billing.syncPayableDueDate, which
-- queries freight.invoices.payments (a jsonb ledger added by migration
-- 1828000000000-ExtendInvoicesForPartialPayment). If that column is MISSING on
-- production (snapshot/restore drift — the migration can read as "applied" in
-- freight.migrations while the DDL never took effect), every reserve() throws
-- `column Invoice.payments does not exist`, the batch fill loop aborts mid-pass,
-- and you see exactly:
--   * only ONE booking gets a pay window (the loop dies after the first reserve
--     whose invoice sync throws), and
--   * reservations never expire cleanly (the settle path hits the same query).
--
-- Run STEP 1 first (read-only). If it shows the columns are MISSING, run STEP 2
-- (idempotent, additive — safe to run even if partially applied).
-- ============================================================================

-- ---------------------------------------------------------------------------
-- STEP 1 — CHECK (read-only). Expect all 6 rows present; if any are missing,
-- production has the drift and STEP 2 is required.
-- ---------------------------------------------------------------------------
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'freight'
  AND table_name = 'invoices'
  AND column_name IN (
    'payments', 'subtotal_amount', 'tax_amount',
    'paid_amount', 'balance_amount', 'paid_at'
  )
ORDER BY column_name;
-- Also confirm the enum has the partial-payment statuses:
SELECT unnest(enum_range(NULL::freight.invoices_status_enum))::text AS status;
--   Expect ISSUED and PARTIALLY_PAID to be present.


-- ---------------------------------------------------------------------------
-- STEP 2 — FIX (idempotent). Only run if STEP 1 showed missing columns.
-- Mirrors migration 1828000000000 up(); all ADD COLUMN IF NOT EXISTS, so
-- re-running is safe. Wrapped so the enum additions (which cannot run inside a
-- transaction block with immediate use) are applied first, then the columns.
-- ---------------------------------------------------------------------------

-- Enum values (no-op if they already exist).
ALTER TYPE freight.invoices_status_enum ADD VALUE IF NOT EXISTS 'ISSUED' BEFORE 'PENDING';
ALTER TYPE freight.invoices_status_enum ADD VALUE IF NOT EXISTS 'PARTIALLY_PAID' BEFORE 'PAID';

-- Money-tracking + payments ledger columns.
ALTER TABLE freight.invoices
  ADD COLUMN IF NOT EXISTS subtotal_amount numeric(14, 2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS tax_amount      numeric(14, 2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS paid_amount     numeric(14, 2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS balance_amount  numeric(14, 2) NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS paid_at         timestamptz,
  ADD COLUMN IF NOT EXISTS payments        jsonb NOT NULL DEFAULT '[]';

-- Backfill derived money fields for existing rows (only rows not already set).
UPDATE freight.invoices
   SET subtotal_amount = total_amount,
       balance_amount  = total_amount
 WHERE subtotal_amount = 0 AND balance_amount = 0;

UPDATE freight.invoices
   SET paid_amount    = total_amount,
       balance_amount = 0,
       paid_at        = COALESCE(paid_at, updated_at)
 WHERE status = 'PAID' AND paid_amount = 0;

-- ---------------------------------------------------------------------------
-- STEP 3 — RE-CHECK. Re-run STEP 1; all 6 columns + both enum values should
-- now be present. After this, deploy the freight_feature/usermanagement branch
-- and the batch will reserve ALL fitting bookings + expire non-payers + top up.
-- ---------------------------------------------------------------------------


-- ============================================================================
-- STEP 4 — BROADER DRIFT AUDIT (read-only). The same snapshot drift that hid
-- invoices.payments can hide OTHER columns the batch flow selects. reserve()
-- and settleReserved() load the FULL Booking entity, so ANY missing booking
-- column throws mid-loop (e.g. we already hit
-- `column Booking.consolidation_resume_status does not exist`). This lists every
-- booking column the entity expects that is MISSING from production — expect
-- ZERO rows. Any row = a drifted migration whose DDL must be re-applied.
-- ============================================================================
WITH expected(col) AS (
  SELECT unnest(ARRAY[
    'reference','customer_id','company_id','company_profile_id','is_government',
    'government_institution','train_id','status','contract_id','contract_route_id',
    'booking_type','contract_kind','created_by_role','created_by_user_id',
    'scheduled_date','estimated_shipment_date','expires_at','total_amount',
    'adjusted_total_amount','adjusted_by_staff_id','adjusted_at','adjustment_reason',
    'contract_validity_days','contract_valid_from','contract_valid_until',
    'payment_status','contract_type','service_type_id','customs_clearing_enabled',
    'customs_clearing_agent','equipment_return','origin_yard_id','destination_yard_id',
    'trade_direction','freight_type','cargo_type_id','cargo_free_text','shipping_line_id',
    'cargo_total_weight_vgm','is_hazardous','is_reefer','bulk_hazardous_quantity',
    'bulk_reefer_quantity','payment_currency','pnr_code','fully_executed_at',
    'pricing_breakdown','locked_at','priority_score','consolidation_partner_id',
    'consolidation_resume_status','wagons_required','scheduling_status',
    'hold_started_at','hold_expires_at','scheduled_at','train_schedule_id',
    'loaded_at','arrived_at','payment_deadline','selected_for_batch_at',
    'gl_station_yard_id','clearance_current_phase','duty_required',
    'vessel_departure_date','ro_amendment_requested_at','ro_hold_reason',
    'pre_clearance_finalized_at','gl_assigned_staff_id','gl_assigned_at'
  ])
)
SELECT e.col AS missing_booking_column
FROM expected e
LEFT JOIN information_schema.columns c
  ON c.table_schema = 'freight' AND c.table_name = 'bookings' AND c.column_name = e.col
WHERE c.column_name IS NULL
ORDER BY e.col;
-- If any rows come back, tell me which columns — I'll give you the exact
-- migration(s) to re-apply (each is ADD COLUMN IF NOT EXISTS, idempotent).