<?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
    {
        // 1) Create table (structure + indexes)
        Schema::create('rm_product_stocks', function (Blueprint $table) {
            $table->engine = 'InnoDB';

            $table->id(); // BIGINT UNSIGNED AI

            // From your dump
            $table->unsignedBigInteger('product_id');           // NOT NULL
            $table->unsignedBigInteger('unit_id')->nullable();  // NULL
            $table->unsignedBigInteger('company_id')->nullable();
            $table->decimal('opening_quantity',        10, 2)->default(0.00);
            $table->decimal('purchased_quantity',      10, 2)->default(0.00);
            $table->decimal('sold_quantity',           10, 2)->default(0.00);
            $table->decimal('order_sold_quantity',     10, 2)->default(0.00);
            $table->decimal('return_quantity',         10, 2)->default(0.00);
            $table->decimal('purchase_return_quantity',10, 2)->default(0.00);
            $table->decimal('production_quantity',     10, 2)->default(0.00);
            $table->decimal('issue_quantity',          10, 2)->default(0.00);
            $table->decimal('exchange_in',             16, 2)->default(0.00);
            $table->decimal('exchange_out',            16, 2)->default(0.00);
            $table->decimal('adjustment_in',           10, 2)->default(0.00);
            $table->decimal('adjustment_out',          10, 2)->default(0.00);
            $table->decimal('transfer_in',             10, 2)->default(0.00);
            $table->decimal('transfer_out',            10, 2)->default(0.00);
            $table->decimal('sale_damage_quantity',    10, 2)->default(0.00);
            $table->decimal('purchase_damage_quantity',10, 2)->default(0.00);
            $table->decimal('available_quantity',      10, 2)->default(0.00);
            $table->string('variant', 191)->nullable();

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

            // timestamps nullable (as per dump)
            $table->timestamp('created_at')->nullable();
            $table->timestamp('updated_at')->nullable();

            $table->unsignedBigInteger('warehouse_id')->nullable();

            // Helpful indexes to match phpMyAdmin view
            $table->index('product_id',  'rm_product_stocks_product_id_index');
            $table->index('company_id',  'rm_product_stocks_company_id_index');
            $table->index('created_by',  'rm_product_stocks_created_by_index');
            $table->index('updated_by',  'rm_product_stocks_updated_by_index');
        });

        // 2) Add foreign keys only if parent tables exist
        Schema::table('rm_product_stocks', function (Blueprint $table) {
            // product_id -> prefer rm_products, fallback to products
            if (Schema::hasTable('rm_products')) {
                $table->foreign('product_id', 'rm_product_stocks_product_id_foreign')
                      ->references('id')->on('rm_products')
                      ->cascadeOnUpdate()
                      ->restrictOnDelete(); // product_id NOT NULL
            } elseif (Schema::hasTable('products')) {
                $table->foreign('product_id', 'rm_product_stocks_product_id_foreign')
                      ->references('id')->on('products')
                      ->cascadeOnUpdate()
                      ->restrictOnDelete();
            }

            // unit_id -> item_units (nullable)
            if (Schema::hasTable('item_units')) {
                $table->foreign('unit_id', 'rm_product_stocks_unit_id_foreign')
                      ->references('id')->on('item_units')
                      ->cascadeOnUpdate()
                      ->nullOnDelete();
            }

            // company_id -> companies (nullable)
            if (Schema::hasTable('companies')) {
                $table->foreign('company_id', 'rm_product_stocks_company_id_foreign')
                      ->references('id')->on('companies')
                      ->cascadeOnUpdate()
                      ->nullOnDelete();
            }

            // warehouse_id -> warehouses (nullable)
            if (Schema::hasTable('warehouses')) {
                $table->foreign('warehouse_id', 'rm_product_stocks_warehouse_id_foreign')
                      ->references('id')->on('warehouses')
                      ->cascadeOnUpdate()
                      ->nullOnDelete();
            }

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

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

    public function down(): void
    {
        Schema::dropIfExists('rm_product_stocks');
    }
};
