<?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
    {
        $tablesToAdd = [
            'magasins' => 'MAG',
            'fournisseurs' => 'FOU',
            'clients' => 'CLI',
            'personnel' => 'PER',
            'cautions' => 'CAU',
            'stock' => 'STK',
            'inventaire_lignes' => 'INL',
            'audits' => 'AUD',
        ];

        foreach ($tablesToAdd as $tableName => $prefix) {
            Schema::table($tableName, function (Blueprint $table) {
                $table->string('reference')->nullable()->after('id');
            });
            $this->fillExistingReferences($tableName, $prefix);
            Schema::table($tableName, function (Blueprint $table) {
                $table->string('reference')->nullable(false)->unique()->change();
            });
        }

        // Mouvements : la référence existe - s'assurer que toutes sont uniques
        // $mouvements = DB::table('mouvements')->orderBy('id')->get();
        // $seen = [];
        // foreach ($mouvements as $index => $m) {
        //     $ref = $m->reference && ! in_array($m->reference, $seen, true)
        //         ? $m->reference
        //         : 'MVT-'.str_pad((string) ($index + 1), 6, '0', STR_PAD_LEFT);
        //     $seen[] = $ref;
        //     DB::table('mouvements')->where('id', $m->id)->update(['reference' => $ref]);
        // }
        // Schema::table('mouvements', function (Blueprint $table) {
        //     $table->string('reference')->nullable(false)->unique()->change();
        // });
    }

    protected function fillExistingReferences(string $tableName, string $prefix): void
    {
        $rows = DB::table($tableName)
            ->where(function ($q) {
                $q->whereNull('reference')->orWhere('reference', '');
            })
            ->orderBy('id')
            ->get(['id']);

        foreach ($rows as $index => $row) {
            $ref = $prefix.'-'.str_pad((string) ($index + 1), 6, '0', STR_PAD_LEFT);
            DB::table($tableName)->where('id', $row->id)->update(['reference' => $ref]);
        }
    }

    public function down(): void
    {
        $tables = [
            'magasins', 'fournisseurs', 'clients', 'personnel',
            'cautions', 'stock', 'inventaire_lignes', 'audits',
        ];

        foreach ($tables as $tableName) {
            Schema::table($tableName, function (Blueprint $table) {
                $table->dropColumn('reference');
            });
        }

        Schema::table('mouvements', function (Blueprint $table) {
            $table->dropUnique(['reference']);
            $table->string('reference')->nullable()->change();
        });
    }
};