import { MigrationInterface, QueryRunner } from 'typeorm';

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

  private async getColumnDefinition(
    queryRunner: QueryRunner,
    tableName: string,
    columnName: string,
  ) {
    const [column] = 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],
    );

    return column;
  }

  private buildModifySql(
    columnName: string,
    definition: {
      COLUMN_TYPE: string;
      CHARACTER_SET_NAME: string | null;
      COLLATION_NAME: string | null;
    },
    nullable: boolean,
  ) {
    const nullSql = nullable ? 'NULL' : 'NOT 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} ${nullSql}`;
    }

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

  public async up(queryRunner: QueryRunner): Promise<void> {
    const columnExists = await queryRunner.query(
      `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'access_role_id' LIMIT 1`,
    );

    if (!columnExists?.length) {
      await queryRunner.query(
        `ALTER TABLE \`members\` ADD \`access_role_id\` varchar(36) NULL`,
      );
    }

    const accessRoleId = await this.getColumnDefinition(
      queryRunner,
      'access_roles',
      'id',
    );
    if (accessRoleId) {
      await queryRunner.query(
        `ALTER TABLE \`members\` ${this.buildModifySql('access_role_id', accessRoleId, true)}`,
      );
    }

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

    if (!fkExists?.length) {
      await queryRunner.query(
        `ALTER TABLE \`members\` ADD CONSTRAINT \`FK_members_access_role\` FOREIGN KEY (\`access_role_id\`) REFERENCES \`access_roles\`(\`id\`) ON DELETE SET NULL ON UPDATE NO ACTION`,
      );
    }

    await queryRunner.query(`
      UPDATE \`members\` m
      INNER JOIN \`access_roles\` ar
        ON ar.owner_id = m.owner_id AND ar.slug = m.role
      SET m.access_role_id = ar.id
      WHERE m.access_role_id IS NULL
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    const fkExists = await queryRunner.query(
      `SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND CONSTRAINT_NAME = 'FK_members_access_role' LIMIT 1`,
    );

    if (fkExists?.length) {
      await queryRunner.query(
        `ALTER TABLE \`members\` DROP FOREIGN KEY \`FK_members_access_role\``,
      );
    }

    const columnExists = await queryRunner.query(
      `SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'members' AND COLUMN_NAME = 'access_role_id' LIMIT 1`,
    );

    if (columnExists?.length) {
      await queryRunner.query(
        `ALTER TABLE \`members\` DROP COLUMN \`access_role_id\``,
      );
    }
  }
}
