<?php

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

return new class extends Migration {
    public function up(): void
    {
        Schema::create('supplier_ledgers', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            $table->id();

            // FK columns (types reflect your dump)
            $table->unsignedBigInteger('supplier_id');   // NOT NULL
            $table->unsignedBigInteger('purchase_id');   // NOT NULL
            $table->string('invoice_no', 191)->nullable();

            // Columns
            $table->string('date', 191);                 // matches varchar(191)
            $table->enum('balance_type', ['Debit', 'Credit']);
            $table->unsignedBigInteger('account_id')->nullable(); // NULL in dump
            $table->decimal('amount', 15, 2);

            $table->unsignedBigInteger('created_by');    // NOT NULL
            $table->unsignedBigInteger('updated_by')->nullable();

            $table->timestamps();

            // (Optional) explicit indexes (foreignId would auto-index, but this mirrors your UI)
            $table->index('supplier_id', 'supplier_ledgers_supplier_id_index');
            $table->index('purchase_id', 'supplier_ledgers_purchase_id_index');
            $table->index('account_id',  'supplier_ledgers_account_id_index');
            $table->index('created_by',  'supplier_ledgers_created_by_index');
            $table->index('updated_by',  'supplier_ledgers_updated_by_index');
        });

        // Add foreign keys only if parent tables exist (prevents errno:150 during partial runs)
        Schema::table('supplier_ledgers', function (Blueprint $table) {
            if (Schema::hasTable('suppliers')) {
                $table->foreign('supplier_id', 'supplier_ledgers_supplier_id_foreign')
                      ->references('id')->on('suppliers')
                      ->restrictOnDelete()->cascadeOnUpdate();
            }

            // Prefer core "purchases" table; fall back to "rm_purchases" if that’s what your module uses
            if (Schema::hasTable('purchases')) {
                $table->foreign('purchase_id', 'supplier_ledgers_purchase_id_foreign')
                      ->references('id')->on('purchases')
                      ->restrictOnDelete()->cascadeOnUpdate();
            } elseif (Schema::hasTable('rm_purchases')) {
                $table->foreign('purchase_id', 'supplier_ledgers_purchase_id_foreign')
                      ->references('id')->on('rm_purchases')
                      ->restrictOnDelete()->cascadeOnUpdate();
            }

            // account_id is nullable; only add FK if you actually have an "accounts" table
            if (Schema::hasTable('accounts')) {
                $table->foreign('account_id', 'supplier_ledgers_account_id_foreign')
                      ->references('id')->on('accounts')
                      ->nullOnDelete()->cascadeOnUpdate();
            }

            // created_by / updated_by -> users
            if (Schema::hasTable('users')) {
                $table->foreign('created_by', 'supplier_ledgers_created_by_foreign')
                      ->references('id')->on('users')
                      ->restrictOnDelete()->cascadeOnUpdate();

                $table->foreign('updated_by', 'supplier_ledgers_updated_by_foreign')
                      ->references('id')->on('users')
                      ->nullOnDelete()->cascadeOnUpdate();
            }
        });
    }

    public function down(): void
    {
        // Drop FKs first (ignore errors if they weren't created)
        Schema::table('supplier_ledgers', function (Blueprint $table) {
            foreach ([
                'supplier_ledgers_supplier_id_foreign',
                'supplier_ledgers_purchase_id_foreign',
                'supplier_ledgers_account_id_foreign',
                'supplier_ledgers_created_by_foreign',
                'supplier_ledgers_updated_by_foreign',
            ] as $fk) {
                try { $table->dropForeign($fk); } catch (\Throwable $e) {}
            }
        });

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

