<?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) {
            // Add new columns
            $table->string('slug')->unique()->nullable()->after('name');
            $table->text('description')->nullable()->after('slug');
            $table->text('short_description')->nullable()->after('description');
            $table->text('specifications')->nullable()->after('short_description');
            
            // Additional pricing fields
            $table->decimal('wholesale_price', 12, 2)->nullable()->after('sale_price');
            $table->decimal('discount_price', 12, 2)->nullable()->after('wholesale_price');
            $table->integer('discount_percent')->nullable()->after('discount_price');
            $table->decimal('old_price', 12, 2)->nullable()->after('discount_percent');
            
            // Enhanced inventory management
            $table->integer('minimum_stock')->default(10)->after('reorder_point');
            $table->integer('maximum_stock')->nullable()->after('minimum_stock');
            $table->integer('reserved_stock')->default(0)->after('maximum_stock');
            $table->integer('committed_stock')->default(0)->after('reserved_stock');
            $table->integer('available_stock')->virtualAs('stock - reserved_stock')->after('committed_stock');
            $table->string('stock_status')->default('in_stock')->after('available_stock'); // in_stock, low_stock, out_of_stock
            $table->boolean('track_inventory')->default(true)->after('stock_status');
            $table->boolean('allow_backorders')->default(false)->after('track_inventory');
            $table->boolean('manage_serial_numbers')->default(false)->after('allow_backorders');
            $table->boolean('manage_batch_numbers')->default(false)->after('manage_serial_numbers');
            
            // Product attributes
            $table->string('weight')->nullable()->after('manage_batch_numbers');
            $table->string('dimensions')->nullable()->after('weight');
            $table->string('color')->nullable()->after('dimensions');
            $table->string('size')->nullable()->after('color');
            $table->string('material')->nullable()->after('size');
            
            // Images and media
            $table->string('primary_image')->nullable()->after('material');
            $table->json('gallery_images')->nullable()->after('primary_image');
            $table->string('video_url')->nullable()->after('gallery_images');
            
            // SEO and marketing
            $table->string('meta_title')->nullable()->after('video_url');
            $table->text('meta_description')->nullable()->after('meta_title');
            $table->json('meta_keywords')->nullable()->after('meta_description');
            
            // Product status and visibility
            $table->string('status')->default('active')->index()->after('meta_keywords'); // active, inactive, discontinued, draft
            $table->boolean('is_featured')->default(false)->after('status');
            $table->boolean('is_new')->default(false)->after('is_featured');
            $table->boolean('is_bestseller')->default(false)->after('is_new');
            $table->boolean('is_on_sale')->default(false)->after('is_bestseller');
            $table->boolean('is_digital')->default(false)->after('is_on_sale');
            $table->boolean('is_service')->default(false)->after('is_digital');
            
            // Warranty and support
            $table->integer('warranty_months')->nullable()->after('is_service');
            $table->text('warranty_terms')->nullable()->after('warranty_months');
            $table->string('support_email')->nullable()->after('warranty_terms');
            $table->string('support_phone')->nullable()->after('support_email');
            
            // Shipping
            $table->decimal('shipping_weight', 8, 2)->nullable()->after('support_phone');
            $table->string('shipping_class')->nullable()->after('shipping_weight');
            $table->boolean('free_shipping')->default(false)->after('shipping_class');
            
            // Sales and analytics
            $table->integer('total_sold')->default(0)->after('free_shipping');
            $table->decimal('total_revenue', 12, 2)->default(0)->after('total_sold');
            $table->integer('view_count')->default(0)->after('total_revenue');
            $table->integer('wishlist_count')->default(0)->after('view_count');
            
            // Supplier information
            $table->string('supplier_sku')->nullable()->after('wishlist_count');
            $table->string('supplier_name')->nullable()->after('supplier_sku');
            $table->decimal('supplier_cost', 12, 2)->nullable()->after('supplier_name');
            $table->integer('lead_time_days')->nullable()->after('supplier_cost');
            
            // Barcode and identification
            $table->string('upc')->nullable()->after('lead_time_days');
            $table->string('ean')->nullable()->after('upc');
            $table->string('isbn')->nullable()->after('ean');
            $table->string('mpn')->nullable()->after('isbn');
            
            // Additional timestamps
            $table->timestamp('price_last_updated_at')->nullable()->after('mpn');
            $table->timestamp('stock_last_updated_at')->nullable()->after('price_last_updated_at');
            $table->timestamp('last_sold_at')->nullable()->after('stock_last_updated_at');
            
            // Indexes for performance
            $table->index(['status', 'is_featured']);
            $table->index(['category_id', 'status']);
            $table->index(['brand_id', 'status']);
            $table->index(['is_on_sale', 'status']);
            $table->index(['stock_status', 'track_inventory']);
        });
    }

    public function down(): void
    {
        Schema::table('products', function (Blueprint $table) {
            // Remove all added columns
            $table->dropColumn([
                'slug',
                'description',
                'short_description',
                'specifications',
                'wholesale_price',
                'discount_price',
                'discount_percent',
                'old_price',
                'minimum_stock',
                'maximum_stock',
                'reserved_stock',
                'committed_stock',
                'available_stock',
                'stock_status',
                'track_inventory',
                'allow_backorders',
                'manage_serial_numbers',
                'manage_batch_numbers',
                'weight',
                'dimensions',
                'color',
                'size',
                'material',
                'primary_image',
                'gallery_images',
                'video_url',
                'meta_title',
                'meta_description',
                'meta_keywords',
                'status',
                'is_featured',
                'is_new',
                'is_bestseller',
                'is_on_sale',
                'is_digital',
                'is_service',
                'warranty_months',
                'warranty_terms',
                'support_email',
                'support_phone',
                'shipping_weight',
                'shipping_class',
                'free_shipping',
                'total_sold',
                'total_revenue',
                'view_count',
                'wishlist_count',
                'supplier_sku',
                'supplier_name',
                'supplier_cost',
                'lead_time_days',
                'upc',
                'ean',
                'isbn',
                'mpn',
                'price_last_updated_at',
                'stock_last_updated_at',
                'last_sold_at',
            ]);
            
            // Remove indexes
            $table->dropIndex(['status', 'is_featured']);
            $table->dropIndex(['category_id', 'status']);
            $table->dropIndex(['brand_id', 'status']);
            $table->dropIndex(['is_on_sale', 'status']);
            $table->dropIndex(['stock_status', 'track_inventory']);
        });
    }
};