import { MigrationInterface, QueryRunner } from 'typeorm';

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

  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 = 'attendance_requests'
         AND CONSTRAINT_NAME = ?
       LIMIT 1`,
      [constraintName],
    );

    return rows.length > 0;
  }

  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 table = await queryRunner.getTable('attendance_requests');
    if (!table) {
      throw new Error(
        'attendance_requests table is missing. Re-run AttendanceRequests1781000000000 first.',
      );
    }

    const ownerId = await this.getColumnDefinition(queryRunner, 'owner', 'id');
    const memberId = await this.getColumnDefinition(
      queryRunner,
      'members',
      'id',
    );
    const attendanceId = await this.getColumnDefinition(
      queryRunner,
      'attendances',
      'id',
    );

    await queryRunner.query(
      `ALTER TABLE \`attendance_requests\`
        ${this.buildModifySql('owner_id', ownerId, false)},
        ${this.buildModifySql('member_id', memberId, false)},
        ${this.buildModifySql('attendance_id', attendanceId, true)}`,
    );

    if (
      !(await this.foreignKeyExists(queryRunner, 'FK_attendance_requests_owner'))
    ) {
      await queryRunner.query(
        `ALTER TABLE \`attendance_requests\` ADD CONSTRAINT \`FK_attendance_requests_owner\` FOREIGN KEY (\`owner_id\`) REFERENCES \`owner\`(\`id\`) ON DELETE RESTRICT`,
      );
    }

    if (
      !(await this.foreignKeyExists(queryRunner, 'FK_attendance_requests_member'))
    ) {
      await queryRunner.query(
        `ALTER TABLE \`attendance_requests\` ADD CONSTRAINT \`FK_attendance_requests_member\` FOREIGN KEY (\`member_id\`) REFERENCES \`members\`(\`id\`) ON DELETE RESTRICT`,
      );
    }

    if (
      !(await this.foreignKeyExists(
        queryRunner,
        'FK_attendance_requests_attendance',
      ))
    ) {
      await queryRunner.query(
        `ALTER TABLE \`attendance_requests\` ADD CONSTRAINT \`FK_attendance_requests_attendance\` FOREIGN KEY (\`attendance_id\`) REFERENCES \`attendances\`(\`id\`) ON DELETE SET NULL`,
      );
    }
  }

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

    for (const fk of [
      'FK_attendance_requests_attendance',
      'FK_attendance_requests_member',
      'FK_attendance_requests_owner',
    ]) {
      if (await this.foreignKeyExists(queryRunner, fk)) {
        await queryRunner.query(
          `ALTER TABLE \`attendance_requests\` DROP FOREIGN KEY \`${fk}\``,
        );
      }
    }
  }
}
