import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Locomotive names must be unique so staff can identify a unit by name alone
 * (the card view leads with `name`, falling back to `code`). Uniqueness is:
 *
 * - case/whitespace-insensitive — "MTL1", "mtl1" and " MTL1 " are one name;
 * - scoped to live rows — a decommissioned (soft-deleted) locomotive must not
 *   hold its name hostage, matching how the fleet reuses yard codes;
 * - skipped for blank names — `name` stays optional, and NULL/'' rows are
 *   excluded rather than colliding with each other.
 *
 * A partial expression index gives all three; a plain UNIQUE column cannot.
 */
export class UniqueLocomotiveName2430000000000 implements MigrationInterface {
  name = 'UniqueLocomotiveName2430000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Pre-existing duplicates would abort CREATE UNIQUE INDEX. Suffix every
    // copy after the oldest (…-2, …-3) so the index can build; the oldest row
    // keeps the original name. Deterministic on created_at, then id.
    await queryRunner.query(`
      WITH ranked AS (
        SELECT
          id,
          name,
          row_number() OVER (
            PARTITION BY lower(btrim(name))
            ORDER BY created_at, id
          ) AS rn
        FROM "freight"."locomotives"
        WHERE deleted_at IS NULL
          AND name IS NOT NULL
          AND btrim(name) <> ''
      )
      UPDATE "freight"."locomotives" AS l
      SET name = btrim(ranked.name) || '-' || ranked.rn
      FROM ranked
      WHERE l.id = ranked.id
        AND ranked.rn > 1
    `);

    await queryRunner.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "UQ_locomotives_name_active"
        ON "freight"."locomotives" (lower(btrim("name")))
        WHERE "deleted_at" IS NULL
          AND "name" IS NOT NULL
          AND btrim("name") <> ''
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `DROP INDEX IF EXISTS "freight"."UQ_locomotives_name_active"`,
    );
    // The de-duplicating renames are not reversed: the original names are no
    // longer recoverable, and restoring them would re-introduce the conflict.
  }
}
