<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        // ── 1. Lookup table ─────────────────────────────────────────────────────
        Schema::create('refund_methods', function (Blueprint $table) {
            $table->tinyIncrements('id');
            $table->string('code', 30)->unique();
            $table->string('name', 100);
            $table->unsignedTinyInteger('sort_order')->default(0);
        });

        DB::table('refund_methods')->insert([
            ['id' => 1, 'code' => 'cash',        'name' => 'Cash',        'sort_order' => 1],
            ['id' => 2, 'code' => 'credit_note', 'name' => 'Credit Note', 'sort_order' => 2],
        ]);

        // ── 2. Add FK column (idempotent: production never had it) ───────────────
        Schema::table('returns', function (Blueprint $table) {
            $table->unsignedTinyInteger('refund_method_id')->nullable()->after('total');
        });

        // ── 3. Add refund_amount if not already present ──────────────────────────
        //    Dev servers that ran the old enum migration already have this column.
        if (! Schema::hasColumn('returns', 'refund_amount')) {
            Schema::table('returns', function (Blueprint $table) {
                $table->decimal('refund_amount', 14, 2)->default(0)->after('refund_method_id');
            });
        }

        // ── 4. Migrate data from the old enum column (dev only) ──────────────────
        if (Schema::hasColumn('returns', 'refund_method')) {
            DB::table('returns')->where('refund_method', 'cash')->update(['refund_method_id' => 1]);
            DB::table('returns')->where('refund_method', 'credit_note')->update(['refund_method_id' => 2]);
            Schema::table('returns', function (Blueprint $table) {
                $table->dropColumn('refund_method');
            });
        }

        // ── 5. Backfill legacy returns (pre-feature, all had CNs auto-issued) ────
        //    Use a subquery form so this runs on both MySQL and SQLite (tests).
        DB::statement('
            UPDATE `returns`
            SET `refund_method_id` = 2,
                `refund_amount`    = (
                    SELECT `amount` FROM `credit_notes`
                    WHERE `credit_notes`.`return_id` = `returns`.`id`
                    LIMIT 1
                )
            WHERE `refund_method_id` IS NULL
              AND EXISTS (
                SELECT 1 FROM `credit_notes`
                WHERE `credit_notes`.`return_id` = `returns`.`id`
              )
        ');

        // ── 6. FK constraint ─────────────────────────────────────────────────────
        Schema::table('returns', function (Blueprint $table) {
            $table->foreign('refund_method_id')
                ->references('id')->on('refund_methods')
                ->cascadeOnUpdate()
                ->restrictOnDelete();
        });
    }

    public function down(): void
    {
        Schema::table('returns', function (Blueprint $table) {
            $table->dropForeign(['refund_method_id']);
            $table->dropColumn(['refund_method_id', 'refund_amount']);
        });

        Schema::dropIfExists('refund_methods');
    }
};
