import { MigrationInterface, QueryRunner } from 'typeorm';

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

  private async foreignKeyExists(
    queryRunner: QueryRunner,
    constraintName: string,
  ): Promise<boolean> {
    const rows = await queryRunner.query(
      `SELECT CONSTRAINT_NAME
       FROM information_schema.TABLE_CONSTRAINTS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = '_noticemembers'
         AND CONSTRAINT_NAME = ?
       LIMIT 1`,
      [constraintName],
    );

    return rows.length > 0;
  }

  private async alignJoinColumnCollations(queryRunner: QueryRunner) {
    const [noticeIdColumn] = await queryRunner.query(
      `SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = 'notices'
         AND COLUMN_NAME = 'id'
       LIMIT 1`,
    );
    const [memberIdColumn] = await queryRunner.query(
      `SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = 'members'
         AND COLUMN_NAME = 'id'
       LIMIT 1`,
    );

    const buildModifySql = (
      columnName: 'A' | 'B',
      definition: {
        COLUMN_TYPE: string;
        CHARACTER_SET_NAME: string | null;
        COLLATION_NAME: string | null;
      },
    ) => {
      if (definition.CHARACTER_SET_NAME && definition.COLLATION_NAME) {
        return `MODIFY \`${columnName}\` ${definition.COLUMN_TYPE} CHARACTER SET ${definition.CHARACTER_SET_NAME} COLLATE ${definition.COLLATION_NAME} NOT NULL`;
      }

      return `MODIFY \`${columnName}\` ${definition.COLUMN_TYPE} NOT NULL`;
    };

    await queryRunner.query(
      `ALTER TABLE \`_noticemembers\`
        ${buildModifySql('B', noticeIdColumn)},
        ${buildModifySql('A', memberIdColumn)}`,
    );
  }

  public async up(queryRunner: QueryRunner): Promise<void> {
    const table = await queryRunner.getTable('_noticemembers');
    if (!table) {
      throw new Error(
        '_noticemembers table is missing. Re-run NoticeMembers1780960000000 first.',
      );
    }

    await this.alignJoinColumnCollations(queryRunner);

    if (!(await this.foreignKeyExists(queryRunner, 'FK_notice_members_notice'))) {
      await queryRunner.query(
        `ALTER TABLE \`_noticemembers\` ADD CONSTRAINT \`FK_notice_members_notice\` FOREIGN KEY (\`B\`) REFERENCES \`notices\`(\`id\`) ON DELETE CASCADE ON UPDATE CASCADE`,
      );
    }

    if (!(await this.foreignKeyExists(queryRunner, 'FK_notice_members_member'))) {
      await queryRunner.query(
        `ALTER TABLE \`_noticemembers\` ADD CONSTRAINT \`FK_notice_members_member\` FOREIGN KEY (\`A\`) REFERENCES \`members\`(\`id\`) ON DELETE NO ACTION ON UPDATE NO ACTION`,
      );
    }
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    const table = await queryRunner.getTable('_noticemembers');
    if (!table) {
      return;
    }

    if (await this.foreignKeyExists(queryRunner, 'FK_notice_members_member')) {
      await queryRunner.query(
        `ALTER TABLE \`_noticemembers\` DROP FOREIGN KEY \`FK_notice_members_member\``,
      );
    }

    if (await this.foreignKeyExists(queryRunner, 'FK_notice_members_notice')) {
      await queryRunner.query(
        `ALTER TABLE \`_noticemembers\` DROP FOREIGN KEY \`FK_notice_members_notice\``,
      );
    }
  }
}
