import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * `freight.companies.region` was free text until region became a closed set
 * (see ETHIOPIAN_REGIONS in @edr/types). This normalizes the rows written under
 * the old rules so they satisfy the new dropdown.
 *
 * Two classes of bad data exist, handled differently:
 *
 *  - Unambiguous spelling/case drift ("Addis ababa", "oromoia") — rewritten to
 *    the canonical spelling.
 *  - Values that are not regions at all ("Arba Minch", a city), and rows whose
 *    region contradicts their own zone/woreda — set to NULL. These are NOT
 *    guessed at: inferring "Gurage/Meskan" means Central Ethiopia would silently
 *    overwrite what the customer actually submitted. NULL surfaces the gap and
 *    the required dropdown forces a deliberate pick on next edit.
 */
export class NormalizeCompanyRegions2400000000000 implements MigrationInterface {
  name = 'NormalizeCompanyRegions2400000000000';

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Canonical spellings — case/whitespace insensitive, safe to re-run.
    await queryRunner.query(`
      UPDATE freight.companies
         SET region = v.canonical
        FROM (VALUES
          ('addis ababa',       'Addis Ababa'),
          ('addis abeba',       'Addis Ababa'),
          ('addisababa',        'Addis Ababa'),
          ('oromia',            'Oromia'),
          ('oromoia',           'Oromia'),
          ('oromiya',           'Oromia'),
          ('amhara',            'Amhara'),
          ('somali',            'Somali'),
          ('afar',              'Afar'),
          ('tigray',            'Tigray'),
          ('tigrai',            'Tigray'),
          ('sidama',            'Sidama'),
          ('harari',            'Harari'),
          ('gambela',           'Gambela'),
          ('gambella',          'Gambela'),
          ('dire dawa',         'Dire Dawa'),
          ('benishangul-gumuz', 'Benishangul-Gumuz'),
          ('benishangul gumuz', 'Benishangul-Gumuz'),
          ('central ethiopia',  'Central Ethiopia'),
          ('south ethiopia',    'South Ethiopia')
        ) AS v(variant, canonical)
       WHERE freight.companies.region IS NOT NULL
         AND lower(regexp_replace(btrim(freight.companies.region), '\\s+', ' ', 'g')) = v.variant
         AND freight.companies.region <> v.canonical
    `);

    // Anything still outside the canonical set is unresolvable — null it.
    await queryRunner.query(`
      UPDATE freight.companies
         SET region = NULL
       WHERE region IS NOT NULL
         AND region <> ''
         AND region NOT IN (
           'Addis Ababa','Afar','Amhara','Benishangul-Gumuz','Central Ethiopia',
           'Dire Dawa','Gambela','Harari','Oromia','Sidama','Somali',
           'South Ethiopia','South West Ethiopia Peoples''','Tigray'
         )
    `);

    // Normalize empty string to NULL so "unset" has one representation.
    await queryRunner.query(`
      UPDATE freight.companies SET region = NULL WHERE region = ''
    `);
  }

  public async down(): Promise<void> {
    // Irreversible by design: the original free-text values are not retained
    // anywhere, so there is nothing to restore. Rolling back the code is safe —
    // the column is still a nullable varchar(100) and accepts free text again.
  }
}
