<?php

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

class AddNotificationsHomeworkIndexesAndMpesaGateway extends Migration
{
    public function up()
    {
        if (!Schema::hasTable('notifications')) {
            Schema::create('notifications', function (Blueprint $table) {
                $table->uuid('id')->primary();
                $table->string('type');
                $table->morphs('notifiable');
                $table->text('data');
                $table->timestamp('read_at')->nullable();
                $table->timestamps();
            });
        }

        $this->addHomeworkIndexes();
        $this->addMpesaGatewaySettings();
        $this->createMpesaTransactionsTable();
        $this->seedMpesaGatewayRows();
    }

    public function down()
    {
        Schema::dropIfExists('mpesa_transactions');

        if (Schema::hasTable('sm_payment_gateway_settings')) {
            Schema::table('sm_payment_gateway_settings', function (Blueprint $table) {
                foreach (['mpesa_passkey', 'mpesa_consumer_key', 'mpesa_consumer_secret', 'mpesa_shortcode'] as $column) {
                    if (Schema::hasColumn('sm_payment_gateway_settings', $column)) {
                        $table->dropColumn($column);
                    }
                }
            });
        }

        $this->dropIndex('sm_homeworks', 'sm_homeworks_teacher_school_academic_index');
        $this->dropIndex('sm_homeworks', 'sm_homeworks_assignment_lookup_index');
        $this->dropIndex('sm_homework_students', 'sm_homework_students_lookup_index');
        $this->dropIndex('sm_homework_students', 'sm_homework_students_student_homework_unique', true);
        $this->dropIndex('sm_upload_homework_contents', 'sm_upload_homework_contents_lookup_index');
        $this->dropIndex('sm_upload_homework_contents', 'sm_upload_homework_contents_student_homework_unique', true);

        Schema::dropIfExists('notifications');
    }

    private function addHomeworkIndexes()
    {
        $this->addIndex('sm_homeworks', ['created_by', 'school_id', 'academic_id'], 'sm_homeworks_teacher_school_academic_index');
        $this->addIndex('sm_homeworks', ['class_id', 'section_id', 'subject_id', 'school_id', 'academic_id'], 'sm_homeworks_assignment_lookup_index');
        $this->addIndex('sm_homework_students', ['student_id', 'homework_id', 'school_id', 'academic_id'], 'sm_homework_students_lookup_index');
        $this->addUniqueWhenClean(
            'sm_homework_students',
            ['student_id', 'homework_id', 'school_id', 'academic_id'],
            'sm_homework_students_student_homework_unique'
        );
        $this->addIndex('sm_upload_homework_contents', ['student_id', 'homework_id', 'school_id', 'academic_id'], 'sm_upload_homework_contents_lookup_index');
        $this->addUniqueWhenClean(
            'sm_upload_homework_contents',
            ['student_id', 'homework_id', 'school_id', 'academic_id'],
            'sm_upload_homework_contents_student_homework_unique'
        );
    }

    private function addMpesaGatewaySettings()
    {
        if (!Schema::hasTable('sm_payment_gateway_settings')) {
            return;
        }

        Schema::table('sm_payment_gateway_settings', function (Blueprint $table) {
            if (!Schema::hasColumn('sm_payment_gateway_settings', 'mpesa_passkey')) {
                $table->text('mpesa_passkey')->nullable()->after('gateway_private_key');
            }
            if (!Schema::hasColumn('sm_payment_gateway_settings', 'mpesa_consumer_key')) {
                $table->text('mpesa_consumer_key')->nullable()->after('mpesa_passkey');
            }
            if (!Schema::hasColumn('sm_payment_gateway_settings', 'mpesa_consumer_secret')) {
                $table->text('mpesa_consumer_secret')->nullable()->after('mpesa_consumer_key');
            }
            if (!Schema::hasColumn('sm_payment_gateway_settings', 'mpesa_shortcode')) {
                $table->string('mpesa_shortcode', 30)->nullable()->after('mpesa_consumer_secret');
            }
        });
    }

    private function createMpesaTransactionsTable()
    {
        if (Schema::hasTable('mpesa_transactions')) {
            return;
        }

        Schema::create('mpesa_transactions', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('status', 20)->default('pending')->index();
            $table->decimal('amount', 12, 2)->default(0);
            $table->string('phone_number', 30)->nullable()->index();
            $table->string('mpesa_receipt', 80)->nullable()->unique();
            $table->string('merchant_request_id')->nullable()->index();
            $table->string('checkout_request_id')->nullable()->index();
            $table->string('account_reference')->nullable();
            $table->string('transaction_type')->nullable();
            $table->unsignedInteger('user_id')->nullable()->index();
            $table->unsignedInteger('student_id')->nullable()->index();
            $table->unsignedInteger('school_id')->nullable()->default(1)->index();
            $table->unsignedInteger('academic_id')->nullable()->index();
            $table->unsignedInteger('gateway_setting_id')->nullable();
            $table->string('payable_type')->nullable();
            $table->string('payable_id')->nullable();
            $table->longText('payload')->nullable();
            $table->longText('response')->nullable();
            $table->longText('callback_payload')->nullable();
            $table->text('failure_reason')->nullable();
            $table->timestamp('completed_at')->nullable();
            $table->timestamps();
        });
    }

    private function seedMpesaGatewayRows()
    {
        if (!Schema::hasTable('sm_payment_gateway_settings') || !Schema::hasTable('sm_payment_methhods')) {
            return;
        }

        $schoolIds = [1];
        if (Schema::hasTable('sm_schools')) {
            $ids = DB::table('sm_schools')->pluck('id')->toArray();
            if (count($ids)) {
                $schoolIds = $ids;
            }
        }

        foreach ($schoolIds as $schoolId) {
            $gateway = DB::table('sm_payment_gateway_settings')
                ->where('gateway_name', 'Mpesa')
                ->where('school_id', $schoolId)
                ->first();

            if (!$gateway) {
                $gatewayId = DB::table('sm_payment_gateway_settings')->insertGetId([
                    'gateway_name' => 'Mpesa',
                    'gateway_mode' => 'sandbox',
                    'active_status' => 0,
                    'school_id' => $schoolId,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            } else {
                $gatewayId = $gateway->id;
            }

            $method = DB::table('sm_payment_methhods')
                ->where('method', 'Mpesa')
                ->where('school_id', $schoolId)
                ->first();

            if (!$method) {
                DB::table('sm_payment_methhods')->insert([
                    'method' => 'Mpesa',
                    'type' => 'System',
                    'active_status' => 0,
                    'gateway_id' => $gatewayId,
                    'school_id' => $schoolId,
                    'created_at' => now(),
                    'updated_at' => now(),
                ]);
            }
        }
    }

    private function addIndex($tableName, array $columns, $indexName)
    {
        if (!Schema::hasTable($tableName) || $this->indexExists($tableName, $indexName)) {
            return;
        }

        try {
            Schema::table($tableName, function (Blueprint $table) use ($columns, $indexName) {
                $table->index($columns, $indexName);
            });
        } catch (\Throwable $e) {
            Log::warning('Unable to add index.', [
                'table' => $tableName,
                'index' => $indexName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function addUniqueWhenClean($tableName, array $columns, $indexName)
    {
        if (!Schema::hasTable($tableName) || $this->indexExists($tableName, $indexName)) {
            return;
        }

        if ($this->hasDuplicateRows($tableName, $columns)) {
            Log::warning('Skipped unique homework constraint because duplicate rows exist.', [
                'table' => $tableName,
                'index' => $indexName,
            ]);
            return;
        }

        try {
            Schema::table($tableName, function (Blueprint $table) use ($columns, $indexName) {
                $table->unique($columns, $indexName);
            });
        } catch (\Throwable $e) {
            Log::warning('Unable to add unique index.', [
                'table' => $tableName,
                'index' => $indexName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function hasDuplicateRows($tableName, array $columns)
    {
        try {
            $columnSql = implode(', ', array_map(function ($column) {
                return "`{$column}`";
            }, $columns));

            $rows = DB::select("
                SELECT {$columnSql}, COUNT(*) AS aggregate_count
                FROM `{$tableName}`
                GROUP BY {$columnSql}
                HAVING aggregate_count > 1
                LIMIT 1
            ");

            return count($rows) > 0;
        } catch (\Throwable $e) {
            return true;
        }
    }

    private function dropIndex($tableName, $indexName, $unique = false)
    {
        if (!Schema::hasTable($tableName) || !$this->indexExists($tableName, $indexName)) {
            return;
        }

        try {
            Schema::table($tableName, function (Blueprint $table) use ($indexName, $unique) {
                if ($unique) {
                    $table->dropUnique($indexName);
                } else {
                    $table->dropIndex($indexName);
                }
            });
        } catch (\Throwable $e) {
            Log::warning('Unable to drop index.', [
                'table' => $tableName,
                'index' => $indexName,
                'error' => $e->getMessage(),
            ]);
        }
    }

    private function indexExists($tableName, $indexName)
    {
        try {
            $indexes = DB::select("SHOW INDEX FROM `{$tableName}` WHERE Key_name = ?", [$indexName]);
            return count($indexes) > 0;
        } catch (\Throwable $e) {
            return false;
        }
    }
}
