import { MigrationInterface, QueryRunner } from 'typeorm';

export class CountryRecommendedCategoriesSeeder1780998267340 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    const hotCategory = await queryRunner.query(`
      SELECT category_id
      FROM category_translations
      WHERE slug = 'hot'
        AND language_code = 'en'
        AND deleted_at IS NULL
      LIMIT 1
    `);

    const internationalCategory = await queryRunner.query(`
      SELECT category_id
      FROM category_translations
      WHERE slug = 'international'
        AND language_code = 'en'
        AND deleted_at IS NULL
      LIMIT 1
    `);

    if (!hotCategory.length || !internationalCategory.length) {
      throw new Error(
        'Hot or International category not found. Ensure category seed migration has run first.',
      );
    }

    const hotCategoryId = hotCategory[0].category_id;
    const internationalCategoryId = internationalCategory[0].category_id;

    await queryRunner.query(`
      INSERT INTO country_recommended_categories (country_id, category_id, order_by)
      SELECT c.id, '${hotCategoryId}', 1
      FROM countries c
      WHERE c.deleted_at IS NULL
      ON CONFLICT (country_id, category_id) DO NOTHING
    `);

    await queryRunner.query(`
      INSERT INTO country_recommended_categories (country_id, category_id, order_by)
      SELECT c.id, '${internationalCategoryId}', 2
      FROM countries c
      WHERE c.deleted_at IS NULL
      ON CONFLICT (country_id, category_id) DO NOTHING
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DELETE FROM country_recommended_categories
      WHERE category_id IN (
        SELECT category_id
        FROM category_translations
        WHERE slug IN ('hot', 'international')
          AND language_code = 'en'
          AND deleted_at IS NULL
      )
    `);
  }
}