import { MigrationInterface, QueryRunner } from 'typeorm';

const MIN_APPLICATION = 50;

export class HotCategoryCampaignSeeder1783130000000 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
    `);

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

    const hotCategoryId = hotCategory[0].category_id;

    await queryRunner.query(`
      INSERT INTO campaign_categories (campaign_id, category_id)
      SELECT c.id, '${hotCategoryId}'
      FROM campaigns c
      WHERE c.deleted_at IS NULL
        AND c.applied >= ${MIN_APPLICATION}
        AND NOT EXISTS (
          SELECT 1
          FROM campaign_categories cc
          WHERE cc.campaign_id = c.id
            AND cc.category_id = '${hotCategoryId}'
            AND cc.deleted_at IS NULL
        )
    `);

    await queryRunner.query(`
      WITH rows_to_insert AS (
        SELECT
          co.id AS country_id,
          cc.id AS campaign_category_id,
          c.created_at AS campaign_created_at,
          c.id AS campaign_id
        FROM campaign_categories cc
        INNER JOIN campaigns c ON c.id = cc.campaign_id AND c.deleted_at IS NULL
        INNER JOIN countries co ON co.two_digit_code = c.campaign_country AND co.deleted_at IS NULL
        WHERE cc.deleted_at IS NULL
          AND cc.category_id = '${hotCategoryId}'
          AND c.campaign_country IS NOT NULL
          AND NOT EXISTS (
            SELECT 1
            FROM campaign_category_country_order cco
            WHERE cco.campaign_category_id = cc.id
              AND cco.country_id = co.id
              AND cco.deleted_at IS NULL
          )
      ),
      numbered AS (
        SELECT
          country_id,
          campaign_category_id,
          ROW_NUMBER() OVER (
            PARTITION BY country_id
            ORDER BY campaign_created_at ASC, campaign_id ASC
          ) AS order_by
        FROM rows_to_insert
      )
      INSERT INTO campaign_category_country_order (country_id, campaign_category_id, order_by)
      SELECT country_id, campaign_category_id, order_by
      FROM numbered
      ON CONFLICT (country_id, campaign_category_id) DO NOTHING
    `);

    await queryRunner.query(`
      UPDATE campaign_category_country_order cco
      SET
        order_by = ranked.new_order_by,
        updated_at = CURRENT_TIMESTAMP
      FROM (
        SELECT
          cco2.id,
          ROW_NUMBER() OVER (
            PARTITION BY cco2.country_id, cc.category_id
            ORDER BY c.created_at ASC, c.id ASC
          ) AS new_order_by
        FROM campaign_category_country_order cco2
        INNER JOIN campaign_categories cc
          ON cc.id = cco2.campaign_category_id
         AND cc.deleted_at IS NULL
        INNER JOIN campaigns c
          ON c.id = cc.campaign_id
         AND c.deleted_at IS NULL
        WHERE cco2.deleted_at IS NULL
          AND cc.category_id = '${hotCategoryId}'
      ) ranked
      WHERE cco.id = ranked.id
        AND cco.order_by IS DISTINCT FROM ranked.new_order_by
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DELETE FROM campaign_category_country_order
      WHERE campaign_category_id IN (
        SELECT cc.id
        FROM campaign_categories cc
        INNER JOIN campaigns c ON c.id = cc.campaign_id AND c.deleted_at IS NULL
        INNER JOIN category_translations ct
          ON ct.category_id = cc.category_id
         AND ct.slug = 'hot'
         AND ct.language_code = 'en'
         AND ct.deleted_at IS NULL
        WHERE cc.deleted_at IS NULL
          AND c.applied >= ${MIN_APPLICATION}
      )
    `);

    await queryRunner.query(`
      DELETE FROM campaign_categories
      WHERE id IN (
        SELECT cc.id
        FROM campaign_categories cc
        INNER JOIN campaigns c ON c.id = cc.campaign_id AND c.deleted_at IS NULL
        INNER JOIN category_translations ct
          ON ct.category_id = cc.category_id
         AND ct.slug = 'hot'
         AND ct.language_code = 'en'
         AND ct.deleted_at IS NULL
        WHERE cc.deleted_at IS NULL
          AND c.applied >= ${MIN_APPLICATION}
      )
    `);
  }
}