<?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::create('products', function (Blueprint $table) {
            $table->id();

            // Dual Unit System
            $table->foreignId('purchase_unit_id')->nullable()->constrained('units')->cascadeOnDelete(); // Unit when buying (e.g., boxes, bottles)
            $table->foreignId('sale_unit_id')->nullable()->constrained('units')->cascadeOnDelete(); // Unit when selling (e.g., capsules, tablets)
            $table->decimal('conversion_factor', 10, 4)->default(1); // How many sale units in 1 purchase unit (e.g., 1 box = 24 capsules)

            $table->foreignId('category_id')->nullable()->constrained('categories')->cascadeOnDelete();

            // Basic Info
            $table->string('name');
            $table->string('slug')->unique();
            $table->string('generic_name')->nullable();
            $table->string('brand')->nullable();

            $table->integer('current_stock')->default(0); // Cached total from stock_entries (in sale units)
            $table->decimal('selling_price', 10, 2)->nullable(); // Price per sale unit
            $table->decimal('purchase_price', 10, 2)->nullable(); // Price per purchase unit
            $table->integer('reorder_level')->default(0); // In sale units

            $table->boolean('status')->default(false);

            $table->timestamps();
            $table->softDeletes();
            $table->index('status');
            $table->index(['category_id', 'status'], 'products_category_status_index');
            $table->index(['purchase_unit_id', 'sale_unit_id'], 'products_units_index');
            $table->index(['current_stock', 'reorder_level'], 'products_stock_reorder_index');
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('products');
    }
};
