<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::table('products', function (Blueprint $table) {
            // 1. Name (pastikan ada)
            if (!Schema::hasColumn('products', 'name')) {
                $table->string('name')->after('id');
            }
            
            // 2. Slug
            if (!Schema::hasColumn('products', 'slug')) {
                $table->string('slug')->nullable()->after('name');
            }
            
            // 3. Image
            if (!Schema::hasColumn('products', 'image')) {
                $table->string('image')->nullable()->after('slug');
            }
            
            // 4. Brand
            if (!Schema::hasColumn('products', 'brand')) {
                $table->string('brand')->nullable()->after('name');
            }
            
            // 5. Category
            if (!Schema::hasColumn('products', 'category')) {
                $table->string('category')->nullable()->after('brand');
            }
            
            // 6. Description
            if (!Schema::hasColumn('products', 'description')) {
                $table->text('description')->nullable()->after('image');
            }
            
            // 7. Product Description (dari API)
            if (!Schema::hasColumn('products', 'product_description')) {
                $table->text('product_description')->nullable()->after('description');
            }
            
            // 8. API Code (pastikan ada)
            if (!Schema::hasColumn('products', 'api_code')) {
                $table->string('api_code')->nullable()->after('category_id');
            }
            
            // 9. Buyer SKU Code
            if (!Schema::hasColumn('products', 'buyer_sku_code')) {
                $table->string('buyer_sku_code')->nullable()->after('api_code');
            }
            
            // 10. Seller Name
            if (!Schema::hasColumn('products', 'seller_name')) {
                $table->string('seller_name')->nullable()->after('category');
            }
            
            // 11. Seller Product Status
            if (!Schema::hasColumn('products', 'seller_product_status')) {
            $table->boolean('seller_product_status')->default(true); // hapus ->after('is_active')
            }
            
            // 12. Unlimited Stock
            if (!Schema::hasColumn('products', 'unlimited_stock')) {
                $table->boolean('unlimited_stock')->default(false)->after('seller_product_status');
            }
            
            // 13. Stock
            if (!Schema::hasColumn('products', 'stock')) {
                $table->integer('stock')->default(0)->after('unlimited_stock');
            }
            
            // 14. Multi
            if (!Schema::hasColumn('products', 'multi')) {
                $table->boolean('multi')->default(false)->after('stock');
            }
            
            // 15. Start Cut Off
            if (!Schema::hasColumn('products', 'start_cut_off')) {
                $table->string('start_cut_off')->nullable()->after('multi');
            }
            
            // 16. End Cut Off
            if (!Schema::hasColumn('products', 'end_cut_off')) {
                $table->string('end_cut_off')->nullable()->after('start_cut_off');
            }
            
            // 17. Sort Order (kalau belum ada)
            if (!Schema::hasColumn('products', 'sort_order')) {
                $table->integer('sort_order')->default(0); // hapus ->after('is_active')
            }
            
            // 18. Last Synced At
            if (!Schema::hasColumn('products', 'last_synced_at')) {
                $table->timestamp('last_synced_at')->nullable()->after('updated_at');
            }
        });
        
        // Tambah indexes SETELAH semua kolom ada
        $this->addIndexIfNotExists('products', 'buyer_sku_code');
        $this->addIndexIfNotExists('products', 'brand');
        $this->addIndexIfNotExists('products', 'category');
        $this->addIndexIfNotExists('products', 'slug');
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            // Drop indexes
            $this->dropIndexIfExists('products', 'products_buyer_sku_code_index');
            $this->dropIndexIfExists('products', 'products_brand_index');
            $this->dropIndexIfExists('products', 'products_category_index');
            $this->dropIndexIfExists('products', 'products_slug_index');
            
            // Drop columns yang ditambahin
            $columns = [
                'slug',
                'image',
                'buyer_sku_code',
                'brand',
                'category',
                'seller_name',
                'description',
                'product_description',
                'seller_product_status',
                'unlimited_stock',
                'stock',
                'multi',
                'start_cut_off',
                'end_cut_off',
                'last_synced_at'
            ];
            
            foreach ($columns as $column) {
                if (Schema::hasColumn('products', $column)) {
                    $table->dropColumn($column);
                }
            }
        });
    }
    
    /**
     * Add index if not exists
     */
    private function addIndexIfNotExists($table, $column)
    {
        $indexName = "{$table}_{$column}_index";
        
        if (!$this->indexExists($table, $indexName)) {
            Schema::table($table, function (Blueprint $table) use ($column) {
                $table->index($column);
            });
        }
    }
    
    /**
     * Drop index if exists
     */
    private function dropIndexIfExists($table, $indexName)
    {
        if ($this->indexExists($table, $indexName)) {
            Schema::table($table, function (Blueprint $table) use ($indexName) {
                $table->dropIndex($indexName);
            });
        }
    }
    
    /**
     * Check if index exists
     */
    private function indexExists($table, $indexName)
    {
        try {
            $indexes = DB::select("SHOW INDEXES FROM {$table} WHERE Key_name = ?", [$indexName]);
            return !empty($indexes);
        } catch (\Exception $e) {
            return false;
        }
    }
};
