import { MigrationInterface, QueryRunner } from 'typeorm';

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

    if (!allCategory.length) {
      throw new Error(
        'All category not found. Ensure AllCategorySeeder migration has run first.',
      );
    }

    const allCategoryId = allCategory[0].category_id;

    await queryRunner.query(`
      INSERT INTO campaign_categories (campaign_id, category_id)
      SELECT c.id, '${allCategoryId}'
      FROM campaigns c
      WHERE c.deleted_at IS NULL AND c.status ='running'
        AND NOT EXISTS (
          SELECT 1
          FROM campaign_categories cc
          WHERE cc.campaign_id = c.id
            AND cc.category_id = '${allCategoryId}'
            AND cc.deleted_at IS 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.insertHomeCountryOrders(
      queryRunner,
      allCategoryId,
      rowSelect,
      orderByExpr,
    );
    await this.insertInternationalListingOrders(
      queryRunner,
      allCategoryId,
      rowSelect,
      orderByExpr,
    );
    await this.insertInternationalOnlyOrders(
      queryRunner,
      allCategoryId,
      rowSelect,
      orderByExpr,
    );
  }

  private async insertHomeCountryOrders(
    queryRunner: QueryRunner,
    allCategoryId: 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
        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.status ='running'
          AND cc.category_id = '${allCategoryId}'
          AND c.is_only_international_influencer = false
          AND c.is_open_to_international_influencer = false
          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
          )
      ),
      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,
    allCategoryId: 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 c.status ='running'
          AND co.deleted_at IS NULL
          AND cc.category_id = '${allCategoryId}'
          AND c.is_only_international_influencer = false
          AND c.is_open_to_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
    `);
  }

  private async insertInternationalOnlyOrders(
    queryRunner: QueryRunner,
    allCategoryId: 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 c.status ='running'
          AND co.deleted_at IS NULL
          AND cc.category_id = '${allCategoryId}'
          AND c.is_only_international_influencer = true
          AND (
            c.campaign_country IS NULL
            OR co.two_digit_code IS DISTINCT FROM c.campaign_country
          )
          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 cc.id
        FROM campaign_categories cc
        INNER JOIN category_translations ct
          ON ct.category_id = cc.category_id
         AND ct.slug = 'all'
         AND ct.language_code = 'en'
         AND ct.deleted_at IS NULL
        WHERE cc.deleted_at IS NULL
      )
    `);

    await queryRunner.query(`
      DELETE FROM campaign_categories
      WHERE category_id IN (
        SELECT category_id
        FROM category_translations
        WHERE slug = 'all'
          AND language_code = 'en'
          AND deleted_at IS NULL
      )
    `);
  }
}