import { MigrationInterface, QueryRunner } from 'typeorm';

export class CampaignCategoryCountryOrderSeeder1781510669026 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    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
    `);

    const internationalCategoryId = internationalCategory[0]?.category_id ?? null;

    const orderByColumn = await queryRunner.query(`
      SELECT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'public'
          AND table_name = 'campaign_categories'
          AND column_name = 'order_by'
      ) AS exists
    `);

    const hasLegacyOrderBy = Boolean(orderByColumn[0]?.exists);
    const rowSelect = hasLegacyOrderBy
      ? 'cc.order_by AS legacy_order, cc.created_at'
      : 'cc.created_at AS legacy_order, cc.created_at';
    const orderByExpr = hasLegacyOrderBy
      ? 'r.legacy_order ASC, r.created_at ASC'
      : 'r.created_at ASC';

    await this.insertCountrySpecificOrders(
      queryRunner,
      internationalCategoryId,
      rowSelect,
      orderByExpr,
    );

    if (internationalCategoryId) {
      await this.insertInternationalListingOrders(
        queryRunner,
        internationalCategoryId,
        rowSelect,
        orderByExpr,
      );
    }

    await this.insertInternationalOnlyOrders(queryRunner, rowSelect, orderByExpr);
  }

  private async insertCountrySpecificOrders(
    queryRunner: QueryRunner,
    internationalCategoryId: string | null,
    rowSelect: string,
    orderByExpr: string,
  ): Promise<void> {
    const internationalFilter = internationalCategoryId
      ? `AND NOT (
          c.is_open_to_international_influencer = true
          AND cc.category_id = '${internationalCategoryId}'
        )`
      : '';

    await queryRunner.query(`
      WITH rows_to_insert AS (
        SELECT
          co.id AS country_id,
          cc.id AS campaign_category_id,
          cc.category_id,
          ${rowSelect}
        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 c.is_only_international_influencer = false
          ${internationalFilter}
          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
          )
      ),
      existing_max AS (
        SELECT
          cco.country_id,
          cc2.category_id,
          COALESCE(MAX(cco.order_by), 0) AS max_order
        FROM campaign_category_country_order cco
        INNER JOIN campaign_categories cc2 ON cc2.id = cco.campaign_category_id
        WHERE cco.deleted_at IS NULL
        GROUP BY cco.country_id, cc2.category_id
      ),
      numbered AS (
        SELECT
          r.country_id,
          r.campaign_category_id,
          COALESCE(em.max_order, 0) + ROW_NUMBER() OVER (
            PARTITION BY r.country_id, r.category_id
            ORDER BY ${orderByExpr}
          ) AS order_by
        FROM rows_to_insert r
        LEFT JOIN existing_max em
          ON em.country_id = r.country_id
         AND em.category_id = r.category_id
      )
      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
    `);
  }

  private async insertInternationalListingOrders(
    queryRunner: QueryRunner,
    internationalCategoryId: string,
    rowSelect: string,
    orderByExpr: string,
  ): Promise<void> {
    await queryRunner.query(`
      WITH rows_to_insert AS (
        SELECT
          co.id AS country_id,
          cc.id AS campaign_category_id,
          cc.category_id,
          ${rowSelect}
        FROM campaign_categories cc
        INNER JOIN campaigns c ON c.id = cc.campaign_id AND c.deleted_at IS NULL
        CROSS JOIN countries co
        WHERE cc.deleted_at IS NULL
          AND co.deleted_at IS NULL
          AND c.is_only_international_influencer = false
          AND c.is_open_to_international_influencer = true
          AND cc.category_id = '${internationalCategoryId}'
          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
          )
      ),
      existing_max AS (
        SELECT
          cco.country_id,
          cc2.category_id,
          COALESCE(MAX(cco.order_by), 0) AS max_order
        FROM campaign_category_country_order cco
        INNER JOIN campaign_categories cc2 ON cc2.id = cco.campaign_category_id
        WHERE cco.deleted_at IS NULL
        GROUP BY cco.country_id, cc2.category_id
      ),
      numbered AS (
        SELECT
          r.country_id,
          r.campaign_category_id,
          COALESCE(em.max_order, 0) + ROW_NUMBER() OVER (
            PARTITION BY r.country_id, r.category_id
            ORDER BY ${orderByExpr}
          ) AS order_by
        FROM rows_to_insert r
        LEFT JOIN existing_max em
          ON em.country_id = r.country_id
         AND em.category_id = r.category_id
      )
      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
    `);
  }

  private async insertInternationalOnlyOrders(
    queryRunner: QueryRunner,
    rowSelect: string,
    orderByExpr: string,
  ): Promise<void> {
    await queryRunner.query(`
      WITH rows_to_insert AS (
        SELECT
          co.id AS country_id,
          cc.id AS campaign_category_id,
          cc.category_id,
          ${rowSelect}
        FROM campaign_categories cc
        INNER JOIN campaigns c ON c.id = cc.campaign_id AND c.deleted_at IS NULL
        CROSS JOIN countries co
        WHERE cc.deleted_at IS NULL
          AND co.deleted_at IS NULL
          AND c.is_only_international_influencer = true
          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
          )
      ),
      existing_max AS (
        SELECT
          cco.country_id,
          cc2.category_id,
          COALESCE(MAX(cco.order_by), 0) AS max_order
        FROM campaign_category_country_order cco
        INNER JOIN campaign_categories cc2 ON cc2.id = cco.campaign_category_id
        WHERE cco.deleted_at IS NULL
        GROUP BY cco.country_id, cc2.category_id
      ),
      numbered AS (
        SELECT
          r.country_id,
          r.campaign_category_id,
          COALESCE(em.max_order, 0) + ROW_NUMBER() OVER (
            PARTITION BY r.country_id, r.category_id
            ORDER BY ${orderByExpr}
          ) AS order_by
        FROM rows_to_insert r
        LEFT JOIN existing_max em
          ON em.country_id = r.country_id
         AND em.category_id = r.category_id
      )
      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
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      DELETE FROM campaign_category_country_order
      WHERE campaign_category_id IN (
        SELECT id FROM campaign_categories WHERE deleted_at IS NULL
      )
    `);
  }
}