<?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::table('products', function (Blueprint $table) {
            if (!Schema::hasColumn('products', 'sku')) {
                $table->string('sku')->nullable()->after('product_code');
            }
            if (!Schema::hasColumn('products', 'short_name_en')) {
                $table->string('short_name_en')->nullable()->after('name_ar');
                $table->string('short_name_ar')->nullable()->after('short_name_en');
            }
            if (!Schema::hasColumn('products', 'subcategory_id')) {
                $table->foreignId('subcategory_id')->nullable()->after('category_id')->constrained('categories')->onDelete('set null');
            }
            if (!Schema::hasColumn('products', 'product_type')) {
                $table->enum('product_type', ['stock', 'service', 'non_stock'])->default('stock')->after('brand_id');
            }
            if (!Schema::hasColumn('products', 'base_unit_id')) {
                $table->foreignId('base_unit_id')->nullable()->after('product_type')->constrained('units')->onDelete('set null');
                $table->foreignId('purchase_unit_id')->nullable()->after('base_unit_id')->constrained('units')->onDelete('set null');
                $table->foreignId('sales_unit_id')->nullable()->after('purchase_unit_id')->constrained('units')->onDelete('set null');
            }
            if (!Schema::hasColumn('products', 'pcs_per_carton')) {
                $table->integer('pcs_per_carton')->default(1)->after('sales_unit_id');
                $table->string('pack_size')->nullable()->after('pcs_per_carton');
                $table->decimal('weight', 10, 3)->default(0.000)->after('pack_size');
                $table->decimal('volume', 10, 3)->default(0.000)->after('weight');
                $table->text('packing_desc_en')->nullable()->after('volume');
                $table->text('packing_desc_ar')->nullable()->after('packing_desc_en');
            }
            if (!Schema::hasColumn('products', 'vat_applicable')) {
                $table->boolean('vat_applicable')->default(true)->after('packing_desc_ar');
                $table->string('vat_category')->default('Standard')->after('vat_rate');
            }
            if (!Schema::hasColumn('products', 'avg_cost')) {
                $table->decimal('avg_cost', 18, 4)->default(0.0000)->after('cost_price');
                $table->decimal('last_purchase_cost', 18, 4)->default(0.0000)->after('avg_cost');
                $table->decimal('wholesale_price', 18, 2)->default(0.00)->after('sale_price');
                $table->decimal('retail_price', 18, 2)->default(0.00)->after('min_sale_price');
                $table->enum('price_mode', ['Exclusive VAT', 'Inclusive VAT'])->default('Exclusive VAT')->after('retail_price');
            }
            if (!Schema::hasColumn('products', 'track_stock')) {
                $table->boolean('track_stock')->default(true)->after('price_mode');
                $table->boolean('allow_negative_stock')->default(false)->after('track_stock');
                $table->decimal('min_stock', 18, 4)->default(0.0000)->after('allow_negative_stock');
                $table->decimal('max_stock', 18, 4)->default(0.0000)->after('min_stock');
                $table->decimal('reorder_level', 18, 4)->default(0.0000)->after('max_stock');
                $table->decimal('reorder_qty', 18, 4)->default(0.0000)->after('reorder_level');
                $table->boolean('track_batch')->default(false)->after('reorder_qty');
                $table->boolean('track_expiry')->default(false)->after('track_batch');
                $table->boolean('track_serial')->default(false)->after('track_expiry');
                $table->foreignId('default_warehouse_id')->nullable()->after('track_serial')->constrained('warehouses')->onDelete('set null');
                $table->string('rack_number')->nullable()->after('default_warehouse_id');
                $table->string('shelf_number')->nullable()->after('rack_number');
                $table->string('image_path')->nullable()->after('shelf_number');
                $table->text('notes')->nullable()->after('image_path');
                $table->foreignId('created_by')->nullable()->after('status')->constrained('users')->onDelete('set null');
            }
        });

        Schema::create('product_price_histories', function (Blueprint $table) {
            $table->id();
            $table->foreignId('product_id')->constrained('products')->onDelete('cascade');
            $table->string('price_type'); // purchase, selling, wholesale, min_selling
            $table->decimal('old_price', 18, 4)->default(0.0000);
            $table->decimal('new_price', 18, 4)->default(0.0000);
            $table->foreignId('changed_by')->nullable()->constrained('users')->onDelete('set null');
            $table->string('reference')->nullable();
            $table->timestamps();
        });

        Schema::create('customer_product_prices', function (Blueprint $table) {
            $table->id();
            $table->foreignId('customer_id')->constrained('customers')->onDelete('cascade');
            $table->foreignId('product_id')->constrained('products')->onDelete('cascade');
            $table->decimal('special_price', 18, 2)->default(0.00);
            $table->enum('vat_mode', ['Exclusive VAT', 'Inclusive VAT'])->default('Exclusive VAT');
            $table->date('start_date')->nullable();
            $table->date('end_date')->nullable();
            $table->enum('status', ['active', 'inactive'])->default('active');
            $table->timestamps();

            $table->unique(['customer_id', 'product_id']);
        });
    }

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