import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddIconFileIdToApps1772529826116 implements MigrationInterface {
  name = 'AddIconFileIdToApps1772529826116';

  public async up(queryRunner: QueryRunner): Promise<void> {
    const hasColumn = await queryRunner.query(`
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = 'public' AND table_name = 'apps' AND column_name = 'icon_file_id'
    `);
    if (!hasColumn?.length) {
      await queryRunner.query(`
        ALTER TABLE "apps"
        ADD COLUMN "icon_file_id" character varying
      `);
    }

    // Backfill: copy icon_file_id from the latest build of each app
    await queryRunner.query(`
      UPDATE "apps" a
      SET "icon_file_id" = sub."icon_file_id"
      FROM (
        SELECT DISTINCT ON (b."app_id") b."app_id", b."icon_file_id"
        FROM "builds" b
        WHERE b."icon_file_id" IS NOT NULL
        ORDER BY b."app_id", b."created_at" DESC
      ) sub
      WHERE a."id" = sub."app_id" AND a."icon_file_id" IS NULL
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      ALTER TABLE "apps" DROP COLUMN IF EXISTS "icon_file_id"
    `);
  }
}