import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * EDR last-mile is multi-truck: a booking can be served by as many trucks as it
 * has containers (bulk hauls until the tonnage is drawn down). Arrival/delivery
 * were stamped once per `last_mile` record, so every truck shared one timestamp.
 * These per-vehicle columns give each EDR truck its own arrival, leaving and
 * weighed load — the same granularity self-haul trucks already have.
 *
 * Weights are TONNES (matching bookings.cargo_total_weight_vgm and the exit
 * weighing UI). Named `*_tons` deliberately: the older
 * customer_truck_assignments.gross_weight_kg is named kg but stores tonnes.
 * All nullable — legacy rows predate per-truck tracking.
 */
export class AddLastMileTruckArrivalDeparture2260000000000 implements MigrationInterface {
  name = 'AddLastMileTruckArrivalDeparture2260000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE freight.last_mile_vehicle_assignments
        ADD COLUMN IF NOT EXISTS arrived_at        timestamptz NULL,
        ADD COLUMN IF NOT EXISTS departed_at       timestamptz NULL,
        ADD COLUMN IF NOT EXISTS gross_weight_tons numeric(14, 3) NULL,
        ADD COLUMN IF NOT EXISTS net_weight_tons   numeric(14, 3) NULL
    `);

    // A truck carries 1x40ft OR 2x20ft, so an EDR truck needs MORE than the one
    // container the legacy scalar `container_number` can hold. Mirrors the
    // self-haul customer_truck_containers child table. The scalar stays in place
    // (synced to the first container) for backward compatibility.
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.last_mile_vehicle_containers (
        id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        assignment_id    uuid NOT NULL REFERENCES freight.last_mile_vehicle_assignments(id) ON DELETE CASCADE,
        last_mile_id     uuid NOT NULL,
        container_number varchar(32) NOT NULL,
        created_at       timestamptz NOT NULL DEFAULT now(),
        updated_at       timestamptz NOT NULL DEFAULT now(),
        deleted_at       timestamptz NULL
      )
    `);
    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS "IDX_last_mile_vehicle_containers_assignment"
        ON freight.last_mile_vehicle_containers (assignment_id)
    `);
    // A container rides exactly one truck per delivery.
    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "UQ_last_mile_vehicle_container"
        ON freight.last_mile_vehicle_containers (last_mile_id, container_number)
        WHERE deleted_at IS NULL
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE IF EXISTS freight.last_mile_vehicle_containers`);
    await queryRunner.query(`
      ALTER TABLE freight.last_mile_vehicle_assignments
        DROP COLUMN IF EXISTS arrived_at,
        DROP COLUMN IF EXISTS departed_at,
        DROP COLUMN IF EXISTS gross_weight_tons,
        DROP COLUMN IF EXISTS net_weight_tons
    `);
  }
}
