<?php

use Illuminate\Database\Migrations\Migration;

class MakeAptTypeReasonBusinessNullable extends Migration
{
    public function up()
    {
        // vc_appointment_types
        try {
            \DB::statement("ALTER TABLE `vc_appointment_types` DROP FOREIGN KEY " .
                $this->getFkName('vc_appointment_types', 'business_id'));
        } catch (\Throwable $e) {}

        \DB::statement("ALTER TABLE `vc_appointment_types`
            MODIFY `business_id` INT UNSIGNED NULL DEFAULT NULL");

        // vc_appointment_reasons
        try {
            \DB::statement("ALTER TABLE `vc_appointment_reasons` DROP FOREIGN KEY " .
                $this->getFkName('vc_appointment_reasons', 'business_id'));
        } catch (\Throwable $e) {}

        \DB::statement("ALTER TABLE `vc_appointment_reasons`
            MODIFY `business_id` INT UNSIGNED NULL DEFAULT NULL");
    }

    public function down()
    {
        \DB::statement("ALTER TABLE `vc_appointment_types`
            MODIFY `business_id` INT UNSIGNED NOT NULL");
        \DB::statement("ALTER TABLE `vc_appointment_reasons`
            MODIFY `business_id` INT UNSIGNED NOT NULL");
    }

    private function getFkName(string $table, string $column): string
    {
        $rows = \DB::select("
            SELECT CONSTRAINT_NAME
            FROM information_schema.KEY_COLUMN_USAGE
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = ?
              AND COLUMN_NAME = ?
              AND REFERENCED_TABLE_NAME IS NOT NULL
        ", [$table, $column]);

        return $rows ? $rows[0]->CONSTRAINT_NAME : "{$table}_{$column}_foreign";
    }
}
