import { MigrationInterface, QueryRunner } from 'typeorm';

type ColumnDefinition = {
  COLUMN_TYPE: string;
  CHARACTER_SET_NAME: string | null;
  COLLATION_NAME: string | null;
};

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

  private buildColumnSql(
    columnName: 'A' | 'B',
    definition: ColumnDefinition,
  ): string {
    const { COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME } = definition;

    if (CHARACTER_SET_NAME && COLLATION_NAME) {
      return `\`${columnName}\` ${COLUMN_TYPE} CHARACTER SET ${CHARACTER_SET_NAME} COLLATE ${COLLATION_NAME} NOT NULL`;
    }

    return `\`${columnName}\` ${COLUMN_TYPE} NOT NULL`;
  }

  private async getColumnDefinition(
    queryRunner: QueryRunner,
    tableName: string,
    columnName: string,
  ): Promise<ColumnDefinition> {
    const [definition] = await queryRunner.query(
      `SELECT COLUMN_TYPE, CHARACTER_SET_NAME, COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = ?
         AND COLUMN_NAME = ?
       LIMIT 1`,
      [tableName, columnName],
    );

    if (!definition) {
      throw new Error(
        `Could not resolve column definition for ${tableName}.${columnName}`,
      );
    }

    return definition;
  }

  public async up(queryRunner: QueryRunner): Promise<void> {
    const existingTable = await queryRunner.getTable('_noticemembers');

    if (!existingTable) {
      const noticeIdColumn = await this.getColumnDefinition(
        queryRunner,
        'notices',
        'id',
      );
      const memberIdColumn = await this.getColumnDefinition(
        queryRunner,
        'members',
        'id',
      );

      const columnB = this.buildColumnSql('B', noticeIdColumn);
      const columnA = this.buildColumnSql('A', memberIdColumn);

      await queryRunner.query(
        `CREATE TABLE \`_noticemembers\` (${columnB}, ${columnA}, INDEX \`IDX_notice_members_B\` (\`B\`), INDEX \`IDX_notice_members_A\` (\`A\`), PRIMARY KEY (\`B\`, \`A\`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci`,
      );
    }

    const fkNoticeExists = await queryRunner.query(
      `SELECT CONSTRAINT_NAME
       FROM information_schema.TABLE_CONSTRAINTS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = '_noticemembers'
         AND CONSTRAINT_NAME = 'FK_notice_members_notice'
       LIMIT 1`,
    );

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

    const fkMemberExists = await queryRunner.query(
      `SELECT CONSTRAINT_NAME
       FROM information_schema.TABLE_CONSTRAINTS
       WHERE TABLE_SCHEMA = DATABASE()
         AND TABLE_NAME = '_noticemembers'
         AND CONSTRAINT_NAME = 'FK_notice_members_member'
       LIMIT 1`,
    );

    if (!fkMemberExists?.length) {
      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;
    }

    await queryRunner.query(
      `ALTER TABLE \`_noticemembers\` DROP FOREIGN KEY \`FK_notice_members_member\``,
    );
    await queryRunner.query(
      `ALTER TABLE \`_noticemembers\` DROP FOREIGN KEY \`FK_notice_members_notice\``,
    );
    await queryRunner.query(
      `DROP INDEX \`IDX_notice_members_A\` ON \`_noticemembers\``,
    );
    await queryRunner.query(
      `DROP INDEX \`IDX_notice_members_B\` ON \`_noticemembers\``,
    );
    await queryRunner.query(`DROP TABLE \`_noticemembers\``);
  }
}
