<?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
    {
        Schema::table('t_item_requests', function (Blueprint $table) {
            $table->string('spb_number', 50)->nullable()->after('id');
            $table->index('spb_number');
        });

        // Update existing records with generated SPB numbers
        $itemRequests = DB::table('t_item_requests')
            ->where('delete_flag', false)
            ->whereNull('spb_number')
            ->get();

        foreach ($itemRequests as $itemRequest) {
            // Generate SPB number for existing records
            try {
                $spbNumber = App\Models\NumberingFormat::generateNumber($itemRequest->store_id, 'SPB');
                
                DB::table('t_item_requests')
                    ->where('id', $itemRequest->id)
                    ->update(['spb_number' => $spbNumber]);
                    
            } catch (Exception $e) {
                // If no format exists, create a simple number
                $count = DB::table('t_item_requests')
                    ->where('store_id', $itemRequest->store_id)
                    ->where('id', '<=', $itemRequest->id)
                    ->count();
                    
                $storeCode = DB::table('m_stores')
                    ->where('id', $itemRequest->store_id)
                    ->value('code') ?? 'STR';
                    
                $spbNumber = "SPB-{$storeCode}-" . str_pad($count, 4, '0', STR_PAD_LEFT);
                
                DB::table('t_item_requests')
                    ->where('id', $itemRequest->id)
                    ->update(['spb_number' => $spbNumber]);
            }
        }
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('t_item_requests', function (Blueprint $table) {
            $table->dropIndex(['spb_number']);
            $table->dropColumn('spb_number');
        });
    }
};