import { type MigrationInterface, type QueryRunner } from 'typeorm';

/**
 * Vehicle-type cleanup — mirrors scripts/cleanup-vehicle-types.sql so
 * the fix runs once on deploy instead of relying on someone running the
 * SQL manually. Idempotent: re-running is a no-op against an already-
 * cleaned tenant.
 *
 * What it does (per tenant, scoped via the WHERE on each statement —
 * no JOIN to a tenants table needed because vehicle_types is already
 * tenant-scoped):
 *
 *   1. Reset operational_cap=1 on Sedan / SUV / Limousine to NULL.
 *      Cap=1 is nonsensical for passenger cabs; came from a wrong
 *      seed. NULL → the FE falls back to seating_capacity (which is
 *      sane on those rows).
 *
 *   2. Soft-delete the single-letter "O" vehicle type — clearly a
 *      placeholder typo that survived. Only delete if it has no live
 *      booking_transport_items referencing it.
 *
 *   3. Soft-delete exact-name duplicates with ZERO bookings AND ZERO
 *      destination mappings. Keep the most-configured original.
 *
 * NOT touched: name renames, vehicle_type_id FKs on historical rows,
 * destination_vehicle_types — those are stable and shouldn't move.
 */
export class VehicleTypeJunkCleanup1781000000000 implements MigrationInterface {
  public async up(qr: QueryRunner): Promise<void> {
    // 1. Drop bad operational_cap=1 values on passenger cabs.
    await qr.query(`
      UPDATE vehicle_types
         SET operational_cap = NULL,
             updated_at = NOW()
       WHERE is_deleted = false
         AND operational_cap = 1
         AND name IN ('Sedan', 'SUV', 'Limousine');
    `);

    // 2. Soft-delete junk single-letter "O" type when unused.
    await qr.query(`
      UPDATE vehicle_types vt
         SET is_deleted = true,
             updated_at = NOW()
       WHERE vt.is_deleted = false
         AND vt.name = 'O'
         AND NOT EXISTS (
           SELECT 1 FROM booking_transport_items bti
            WHERE bti.vehicle_type_id = vt.id AND bti.is_deleted = false
         );
    `);

    // 3. Soft-delete exact-name duplicates with zero usage and zero
    //    destination mappings. Keeps the one with the most context.
    await qr.query(`
      WITH dups AS (
        SELECT vt.id,
               vt.name,
               vt.created_at,
               (SELECT COUNT(*) FROM booking_transport_items bti
                  WHERE bti.vehicle_type_id = vt.id AND bti.is_deleted = false)  AS used,
               (SELECT COUNT(*) FROM destination_vehicle_types dvt
                  WHERE dvt.vehicle_type_id = vt.id)                             AS mapped,
               ROW_NUMBER() OVER (
                 PARTITION BY vt.tenant_id, vt.name
                 ORDER BY (
                   CASE WHEN vt.seating_capacity IS NOT NULL THEN 1 ELSE 2 END
                 ),
                 vt.created_at ASC
               ) AS rn
          FROM vehicle_types vt
         WHERE vt.is_deleted = false
      )
      UPDATE vehicle_types vt
         SET is_deleted = true,
             updated_at = NOW()
        FROM dups
       WHERE vt.id = dups.id
         AND dups.rn > 1
         AND dups.used = 0
         AND dups.mapped = 0;
    `);
  }

  public async down(): Promise<void> {
    // Irreversible — we soft-delete junk rows. Bringing them back would
    // surface the very rows the FE was meant to hide; instead leave the
    // forward migration as the single source of truth.
  }
}