<?php

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

return new class extends Migration {
    public function up(): void
    {
        // Add column + FK if missing
        if (! Schema::hasColumn('deposits', 'job_id')) {
            Schema::table('deposits', function (Blueprint $table) {
                $table->unsignedBigInteger('job_id')->nullable()->after('id');
                // Indexes are safe to add even if nullable
                $table->index(['job_id'], 'deposits_job_id_index');

                // Add FK if jobs table exists
                if (Schema::hasTable('jobs')) {
                    $table->foreign('job_id')
                        ->references('id')->on('jobs')
                        ->cascadeOnDelete();
                }
            });
        }

        /**
         * Optional backfill:
         * If your deposits have a payment_id that points to payments.job_id,
         * we can backfill job_id from that.
         */
        if (
            Schema::hasColumn('deposits', 'payment_id') &&
            Schema::hasTable('payments') &&
            Schema::hasColumn('payments', 'job_id')
        ) {
            // Portable PHP backfill (works on MySQL without fancy UPDATE…JOIN permissions)
            $pairs = DB::table('deposits as d')
                ->leftJoin('payments as p', 'p.id', '=', 'd.payment_id')
                ->whereNull('d.job_id')
                ->whereNotNull('p.job_id')
                ->select('d.id as deposit_id', 'p.job_id')
                ->get();

            foreach ($pairs as $row) {
                DB::table('deposits')
                    ->where('id', $row->deposit_id)
                    ->update(['job_id' => $row->job_id]);
            }
        }
    }

    public function down(): void
    {
        if (Schema::hasColumn('deposits', 'job_id')) {
            Schema::table('deposits', function (Blueprint $table) {
                // Drop FK/index if they exist; wrap in try-catch to be lenient
                try { $table->dropForeign(['job_id']); } catch (\Throwable $e) {}
                try { $table->dropIndex('deposits_job_id_index'); } catch (\Throwable $e) {}
                $table->dropColumn('job_id');
            });
        }
    }
};
