<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class UseFormattedIdsForAppointmentPreviews extends Migration
{
    public function up(): void
    {
        $db = \Config\Database::connect();

        if ($db->fieldExists('appointment_id', 'appointment_previews')) {
            $db->query('ALTER TABLE `appointment_previews` DROP INDEX `appointment_id`');
            $this->forge->dropColumn('appointment_previews', 'appointment_id');
        }

        if (! $db->fieldExists('patient_formatted_user_id', 'appointment_previews')) {
            $this->forge->addColumn('appointment_previews', [
                'patient_formatted_user_id' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 20,
                    'null'       => true,
                    'after'      => 'patient_id',
                ],
            ]);
        }

        if ($db->fieldExists('selected_doctor_id', 'appointment_previews')) {
            $db->query('ALTER TABLE `appointment_previews` DROP INDEX `selected_doctor_id`');
            $this->forge->modifyColumn('appointment_previews', [
                'selected_doctor_id' => [
                    'name'     => 'assigned_doctor_id',
                    'type'     => 'INT',
                    'unsigned' => true,
                    'null'     => true,
                ],
            ]);
        } elseif (! $db->fieldExists('assigned_doctor_id', 'appointment_previews')) {
            $this->forge->addColumn('appointment_previews', [
                'assigned_doctor_id' => [
                    'type'     => 'INT',
                    'unsigned' => true,
                    'null'     => true,
                    'after'    => 'requested_time',
                ],
            ]);
        }

        if (! $db->fieldExists('assigned_doctor_formatted_id', 'appointment_previews')) {
            $this->forge->addColumn('appointment_previews', [
                'assigned_doctor_formatted_id' => [
                    'type'       => 'VARCHAR',
                    'constraint' => 20,
                    'null'       => true,
                    'after'      => 'assigned_doctor_id',
                ],
            ]);
        }

        if ($db->fieldExists('candidate_doctor_ids', 'appointment_previews')) {
            $this->forge->dropColumn('appointment_previews', 'candidate_doctor_ids');
        }

        $this->addIndexIfMissing('assigned_doctor_id', 'assigned_doctor_id');
        $this->addIndexIfMissing('patient_formatted_user_id', 'patient_formatted_user_id');
        $this->addIndexIfMissing('assigned_doctor_formatted_id', 'assigned_doctor_formatted_id');
    }

    public function down(): void
    {
        $db = \Config\Database::connect();

        if (! $db->fieldExists('appointment_id', 'appointment_previews')) {
            $this->forge->addColumn('appointment_previews', [
                'appointment_id' => [
                    'type'     => 'INT',
                    'unsigned' => true,
                    'null'     => true,
                    'after'    => 'id',
                ],
            ]);
            $this->addIndexIfMissing('appointment_id', 'appointment_id');
        }

        if ($db->fieldExists('assigned_doctor_id', 'appointment_previews')) {
            $this->dropIndexIfExists('assigned_doctor_id');
            $this->forge->modifyColumn('appointment_previews', [
                'assigned_doctor_id' => [
                    'name'     => 'selected_doctor_id',
                    'type'     => 'INT',
                    'unsigned' => true,
                    'null'     => true,
                ],
            ]);
            $this->addIndexIfMissing('selected_doctor_id', 'selected_doctor_id');
        }

        if (! $db->fieldExists('candidate_doctor_ids', 'appointment_previews')) {
            $this->forge->addColumn('appointment_previews', [
                'candidate_doctor_ids' => [
                    'type'  => 'TEXT',
                    'null'  => true,
                    'after' => 'selected_doctor_id',
                ],
            ]);
        }

        foreach (['patient_formatted_user_id', 'assigned_doctor_formatted_id'] as $column) {
            if ($db->fieldExists($column, 'appointment_previews')) {
                $this->dropIndexIfExists($column);
                $this->forge->dropColumn('appointment_previews', $column);
            }
        }
    }

    private function addIndexIfMissing(string $indexName, string $columnName): void
    {
        $db = \Config\Database::connect();
        $escapedIndex = $db->escapeIdentifiers($indexName);
        $escapedColumn = $db->escapeIdentifiers($columnName);
        $exists = $db->query("SHOW INDEX FROM `appointment_previews` WHERE Key_name = " . $db->escape($indexName))->getRowArray();

        if (! $exists) {
            $db->query("ALTER TABLE `appointment_previews` ADD INDEX {$escapedIndex} ({$escapedColumn})");
        }
    }

    private function dropIndexIfExists(string $indexName): void
    {
        $db = \Config\Database::connect();
        $exists = $db->query("SHOW INDEX FROM `appointment_previews` WHERE Key_name = " . $db->escape($indexName))->getRowArray();

        if ($exists) {
            $escapedIndex = $db->escapeIdentifiers($indexName);
            $db->query("ALTER TABLE `appointment_previews` DROP INDEX {$escapedIndex}");
        }
    }
}