import { MigrationInterface, QueryRunner } from "typeorm";

export class AddIndexesForPerformance1770875727560 implements MigrationInterface {
  name = "AddIndexesForPerformance1770875727560";

  public async up(queryRunner: QueryRunner): Promise<void> {
    // Index for posMasterHistory lookups
    await queryRunner.query(`
      CREATE INDEX IDX_posMasterHistory_ancestorDNA
      ON posMasterHistory(ancestorDNA, createdAt DESC)
    `);

    // Index for org tables lookups
    await queryRunner.query(`
      CREATE INDEX IDX_orgRoot_ancestorDNA_revision
      ON orgRoot(ancestorDNA, orgRevisionId)
    `);

    await queryRunner.query(`
      CREATE INDEX IDX_orgChild1_ancestorDNA_revision
      ON orgChild1(ancestorDNA, orgRevisionId)
    `);

    await queryRunner.query(`
      CREATE INDEX IDX_orgChild2_ancestorDNA_revision
      ON orgChild2(ancestorDNA, orgRevisionId)
    `);

    await queryRunner.query(`
      CREATE INDEX IDX_orgChild3_ancestorDNA_revision
      ON orgChild3(ancestorDNA, orgRevisionId)
    `);

    await queryRunner.query(`
      CREATE INDEX IDX_orgChild4_ancestorDNA_revision
      ON orgChild4(ancestorDNA, orgRevisionId)
    `);

    // Index for posMaster lookups
    await queryRunner.query(`
      CREATE INDEX IDX_posMaster_revision_org
      ON posMaster(orgRevisionId, orgRootId, orgChild1Id, orgChild2Id, orgChild3Id, orgChild4Id)
    `);

    await queryRunner.query(`
      CREATE INDEX IDX_posMaster_ancestorDNA
      ON posMaster(ancestorDNA)
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX IDX_position_posMasterId ON position`);
    await queryRunner.query(`DROP INDEX IDX_posMaster_ancestorDNA ON posMaster`);
    await queryRunner.query(`DROP INDEX IDX_posMaster_revision_org ON posMaster`);
    await queryRunner.query(`DROP INDEX IDX_orgChild4_ancestorDNA_revision ON orgChild4`);
    await queryRunner.query(`DROP INDEX IDX_orgChild3_ancestorDNA_revision ON orgChild3`);
    await queryRunner.query(`DROP INDEX IDX_orgChild2_ancestorDNA_revision ON orgChild2`);
    await queryRunner.query(`DROP INDEX IDX_orgChild1_ancestorDNA_revision ON orgChild1`);
    await queryRunner.query(`DROP INDEX IDX_orgRoot_ancestorDNA_revision ON orgRoot`);
    await queryRunner.query(`DROP INDEX IDX_posMasterHistory_ancestorDNA ON posMasterHistory`);
  }
}
