import { MigrationInterface, QueryRunner } from "typeorm";

/**
 * Truck types become back-office data instead of a hardcoded `VehicleType` enum,
 * so EDR can add a configuration without a code change.
 *
 * `vehicles.vehicle_type` is deliberately LEFT IN PLACE as a denormalised code.
 * Truck-detention billing groups trucks with raw SQL over that column
 * (`SELECT v.vehicle_type ... GROUP BY`, warehouse-fee.service.ts) and matches
 * the result against `warehouse_fee_rules.vehicle_type`. Swapping it for the FK
 * outright would silently drop detention charges, so the FK is additive and the
 * service writes the type's code through on every save.
 *
 * Raw SQL, `freight.`-qualified, IF NOT EXISTS throughout — the TypeORM builder
 * API resolves bare names against `public` and crash-loops boot.
 */
export class AddTruckTypes2840000000000 implements MigrationInterface {
  name = "AddTruckTypes2840000000000";

  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      CREATE TABLE IF NOT EXISTS freight.truck_types (
        id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
        code          varchar(32) NOT NULL,
        name          varchar(100) NOT NULL,
        capacity_tons numeric(10,3),
        has_trailer   boolean NOT NULL DEFAULT false,
        description   text,
        is_active     boolean NOT NULL DEFAULT true,
        created_at    timestamptz NOT NULL DEFAULT now(),
        updated_at    timestamptz NOT NULL DEFAULT now(),
        deleted_at    timestamptz
      )
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS ux_truck_types_code
        ON freight.truck_types (code)
    `);
    await queryRunner.query(`
      CREATE INDEX IF NOT EXISTS ix_truck_types_is_active
        ON freight.truck_types (is_active)
    `);

    // Seed one row per legacy enum value so vehicles already carrying that code
    // keep resolving, plus CASONI as the first rigid (no-trailer) configuration.
    // has_trailer is true only for the articulated configurations.
    await queryRunner.query(`
      INSERT INTO freight.truck_types (code, name, has_trailer)
      VALUES
        ('TRUCK',   'Truck',   true),
        ('TRAILER', 'Trailer', true),
        ('TANKER',  'Tanker',  true),
        ('FLATBED', 'Flatbed', true),
        ('VAN',     'Van',     false),
        ('CAR',     'Car',     false),
        ('BUS',     'Bus',     false),
        ('CASONI',  'Casoni (rigid, no trailer)', false)
      ON CONFLICT (code) DO NOTHING
    `);

    await queryRunner.query(`
      ALTER TABLE freight.vehicles
        ADD COLUMN IF NOT EXISTS truck_type_id uuid
    `);

    // Separate DO block: ADD CONSTRAINT has no IF NOT EXISTS in Postgres.
    await queryRunner.query(`
      DO $$
      BEGIN
        IF NOT EXISTS (
          SELECT 1 FROM pg_constraint WHERE conname = 'fk_vehicles_truck_type'
        ) THEN
          ALTER TABLE freight.vehicles
            ADD CONSTRAINT fk_vehicles_truck_type
            FOREIGN KEY (truck_type_id) REFERENCES freight.truck_types (id)
            ON DELETE SET NULL;
        END IF;
      END $$
    `);

    // Backfill the FK from the code already stored on each vehicle.
    await queryRunner.query(`
      UPDATE freight.vehicles v
         SET truck_type_id = t.id
        FROM freight.truck_types t
       WHERE v.truck_type_id IS NULL
         AND upper(trim(v.vehicle_type)) = t.code
    `);

    // Truck-type codes are varchar(32); the fee-rule column they are matched
    // against was varchar(20) and would truncate/reject longer codes.
    await queryRunner.query(`
      ALTER TABLE freight.warehouse_fee_rules
        ALTER COLUMN vehicle_type TYPE varchar(32)
    `);

    // A VIN identifies exactly one vehicle worldwide. Partial index so the many
    // existing rows without a VIN do not collide.
    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS ux_vehicles_vin
        ON freight.vehicles (vin)
        WHERE vin IS NOT NULL
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX IF EXISTS freight.ux_vehicles_vin`);
    await queryRunner.query(`
      ALTER TABLE freight.vehicles
        DROP CONSTRAINT IF EXISTS fk_vehicles_truck_type
    `);
    await queryRunner.query(`
      ALTER TABLE freight.vehicles
        DROP COLUMN IF EXISTS truck_type_id
    `);
    await queryRunner.query(`DROP TABLE IF EXISTS freight.truck_types`);
    // warehouse_fee_rules.vehicle_type is left widened: narrowing it back would
    // fail on any row that stored a code longer than 20 characters.
  }
}
