<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

/**
 * SIFA P3-T1 — patient master identity extensions.
 *
 * Additive, nullable identity columns on the existing clc_patients table
 * (spec §7 medical_core > patient > identity + administrative). Every column
 * is guarded with Schema::hasColumn so the migration is idempotent and never
 * conflicts with a partially-migrated install. Existing free-text fields
 * (allergies / chronic_conditions / family_history) are left untouched and
 * continue to display as the legacy baseline; the new structured satellite
 * tables live alongside them.
 *
 * `merged_into_id` supports the patient-merge feature (P3-T1): the losing
 * record is marked inactive and points at the surviving master.
 */
class AddPatientMasterIdentityToClcPatients extends Migration
{
    public function up()
    {
        Schema::table('clc_patients', function (Blueprint $table) {
            if (!Schema::hasColumn('clc_patients', 'national_id')) {
                $table->string('national_id', 50)->nullable()->after('bloodgroup');
            }
            if (!Schema::hasColumn('clc_patients', 'passport_number')) {
                $table->string('passport_number', 50)->nullable()->after('national_id');
            }
            if (!Schema::hasColumn('clc_patients', 'marital_status')) {
                $table->string('marital_status', 20)->nullable()->after('passport_number');
            }
            if (!Schema::hasColumn('clc_patients', 'nationality')) {
                $table->string('nationality', 100)->nullable()->after('marital_status');
            }
            if (!Schema::hasColumn('clc_patients', 'primary_language')) {
                $table->string('primary_language', 50)->nullable()->after('nationality');
            }
            if (!Schema::hasColumn('clc_patients', 'preferred_name')) {
                $table->string('preferred_name', 100)->nullable()->after('primary_language');
            }
            if (!Schema::hasColumn('clc_patients', 'gender_identity')) {
                $table->string('gender_identity', 50)->nullable()->after('preferred_name');
            }
            if (!Schema::hasColumn('clc_patients', 'registered_location_id')) {
                $table->unsignedInteger('registered_location_id')->nullable()->after('gender_identity');
            }
            if (!Schema::hasColumn('clc_patients', 'merged_into_id')) {
                // Points at the surviving master patient after a merge.
                $table->unsignedInteger('merged_into_id')->nullable()->after('registered_location_id');
            }
        });

        // Indexes for search + merge lookups. Added separately so the column
        // guards above stay simple and the index adds are individually safe.
        $this->addIndexIfMissing('clc_patients', 'clc_patients_national_id_index', ['national_id']);
        $this->addIndexIfMissing('clc_patients', 'clc_patients_merged_into_id_index', ['merged_into_id']);

        // FK to business_locations for the registered branch. Soft (nullOnDelete)
        // so removing a location never cascades a patient delete.
        if (Schema::hasColumn('clc_patients', 'registered_location_id')
            && Schema::hasTable('business_locations')
            && !$this->foreignKeyExists('clc_patients', 'clc_patients_registered_location_id_foreign')) {
            try {
                Schema::table('clc_patients', function (Blueprint $table) {
                    $table->foreign('registered_location_id')
                          ->references('id')->on('business_locations')
                          ->nullOnDelete();
                });
            } catch (\Throwable $e) {
                \Log::warning('clc_patients.registered_location_id FK skipped: ' . $e->getMessage());
            }
        }
    }

    public function down()
    {
        // Drop FK first (guarded), then columns. Kept lenient so a partial
        // rollback never hard-fails.
        if ($this->foreignKeyExists('clc_patients', 'clc_patients_registered_location_id_foreign')) {
            try {
                Schema::table('clc_patients', function (Blueprint $table) {
                    $table->dropForeign('clc_patients_registered_location_id_foreign');
                });
            } catch (\Throwable $e) {
                // ignore
            }
        }

        foreach ([
            'national_id', 'passport_number', 'marital_status', 'nationality',
            'primary_language', 'preferred_name', 'gender_identity',
            'registered_location_id', 'merged_into_id',
        ] as $col) {
            if (Schema::hasColumn('clc_patients', $col)) {
                Schema::table('clc_patients', function (Blueprint $table) use ($col) {
                    $table->dropColumn($col);
                });
            }
        }
    }

    private function addIndexIfMissing(string $table, string $indexName, array $columns): void
    {
        try {
            $exists = collect(\DB::select("SHOW INDEX FROM `{$table}`"))
                ->pluck('Key_name')->contains($indexName);
            if (!$exists) {
                Schema::table($table, function (Blueprint $t) use ($columns, $indexName) {
                    $t->index($columns, $indexName);
                });
            }
        } catch (\Throwable $e) {
            \Log::warning("Index {$indexName} skipped: " . $e->getMessage());
        }
    }

    private function foreignKeyExists(string $table, string $fkName): bool
    {
        try {
            $db = \DB::getDatabaseName();
            $row = \DB::selectOne(
                'SELECT COUNT(*) AS c FROM information_schema.TABLE_CONSTRAINTS
                 WHERE CONSTRAINT_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = ?
                 AND CONSTRAINT_TYPE = "FOREIGN KEY"',
                [$db, $table, $fkName]
            );
            return $row && (int) $row->c > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }
}
