<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Services\MagentoCategoryMigrationService;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class TestProductImageMigration extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'test:product-images {sku=saunaheater2403}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Test migrating images for a specific product by SKU';

    protected $migrationService;

    /**
     * Create a new command instance.
     */
    public function __construct(MagentoCategoryMigrationService $migrationService)
    {
        parent::__construct();
        $this->migrationService = $migrationService;
    }

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $sku = $this->argument('sku');
        
        $this->info("Testing image migration for product SKU: {$sku}");
        $this->newLine();

        // Get connection details from the service using reflection
        $reflection = new \ReflectionClass($this->migrationService);
        $m1ConnectionProp = $reflection->getProperty('magento1Connection');
        $m2ConnectionProp = $reflection->getProperty('magento2Connection');
        $m1PrefixProp = $reflection->getProperty('magento1Prefix');
        $m2PrefixProp = $reflection->getProperty('magento2Prefix');
        
        $m1ConnectionProp->setAccessible(true);
        $m2ConnectionProp->setAccessible(true);
        $m1PrefixProp->setAccessible(true);
        $m2PrefixProp->setAccessible(true);
        
        $m1Connection = $m1ConnectionProp->getValue($this->migrationService);
        $m2Connection = $m2ConnectionProp->getValue($this->migrationService);
        $m1Prefix = $m1PrefixProp->getValue($this->migrationService);
        $m2Prefix = $m2PrefixProp->getValue($this->migrationService);

        // Step 1: Find product in M1
        $this->info("Step 1: Finding product in Magento 1...");
        $m1Product = $this->findM1ProductBySku($sku, $m1Connection, $m1Prefix);
        
        if (!$m1Product) {
            $this->error("Product with SKU '{$sku}' not found in Magento 1");
            return 1;
        }
        
        $m1ProductId = $m1Product->entity_id;
        $this->info("✓ Found M1 product: ID {$m1ProductId}, SKU: {$sku}");
        $this->newLine();

        // Step 2: Check images in M1
        $this->info("Step 2: Checking images in Magento 1...");
        $m1Images = $this->getM1Images($m1ProductId, $m1Connection, $m1Prefix);
        $this->info("✓ Found " . count($m1Images) . " images in M1:");
        foreach ($m1Images as $idx => $image) {
            $this->line("  " . ($idx + 1) . ". {$image['file']} (value_id: {$image['value_id']})");
        }
        $this->newLine();

        // Step 3: Find product in M2
        $this->info("Step 3: Finding product in Magento 2...");
        $m2Product = $this->findM2ProductBySku($sku, $m2Connection, $m2Prefix);
        
        if (!$m2Product) {
            $this->error("Product with SKU '{$sku}' not found in Magento 2. Please migrate the product first.");
            return 1;
        }
        
        $m2ProductId = $m2Product->entity_id;
        $this->info("✓ Found M2 product: ID {$m2ProductId}, SKU: {$sku}");
        $this->newLine();

        // Step 4: Check images in M2 before migration
        $this->info("Step 4: Checking images in Magento 2 BEFORE migration...");
        $m2ImagesBefore = $this->getM2Images($m2ProductId, $m2Connection, $m2Prefix);
        $this->info("✓ Found " . count($m2ImagesBefore) . " images in M2 before migration");
        if (count($m2ImagesBefore) > 0) {
            foreach ($m2ImagesBefore as $idx => $image) {
                $this->line("  " . ($idx + 1) . ". {$image['file']} (value_id: {$image['value_id']})");
            }
        }
        $this->newLine();

        // Step 5: Migrate images
        $this->info("Step 5: Migrating images...");
        try {
            $m1EntityTypeId = DB::connection($m1Connection)
                ->table($m1Prefix . 'eav_entity_type')
                ->where('entity_type_code', 'catalog_product')
                ->value('entity_type_id');

            // Use reflection to call the protected method
            $reflection = new \ReflectionClass($this->migrationService);
            $method = $reflection->getMethod('migrateProductMediaGallery');
            $method->setAccessible(true);
            
            $method->invoke($this->migrationService, $m1ProductId, $m2ProductId, $m1EntityTypeId);
            
            $this->info("✓ Migration method executed");
        } catch (\Exception $e) {
            $this->error("✗ Error during migration: " . $e->getMessage());
            $this->error("Stack trace: " . $e->getTraceAsString());
            return 1;
        }
        $this->newLine();

        // Step 6: Check images in M2 after migration
        $this->info("Step 6: Checking images in Magento 2 AFTER migration...");
        $m2ImagesAfter = $this->getM2Images($m2ProductId, $m2Connection, $m2Prefix);
        $this->info("✓ Found " . count($m2ImagesAfter) . " images in M2 after migration");
        
        if (count($m2ImagesAfter) > 0) {
            foreach ($m2ImagesAfter as $idx => $image) {
                $this->line("  " . ($idx + 1) . ". {$image['file']} (value_id: {$image['value_id']})");
            }
        } else {
            $this->error("✗ No images found in M2 after migration!");
        }
        $this->newLine();

        // Step 7: Compare results
        $this->info("Step 7: Comparison Results:");
        $this->line("  M1 images: " . count($m1Images));
        $this->line("  M2 images before: " . count($m2ImagesBefore));
        $this->line("  M2 images after: " . count($m2ImagesAfter));
        $this->line("  Expected: " . count($m1Images));
        $this->line("  Actual: " . count($m2ImagesAfter));
        
        if (count($m2ImagesAfter) >= count($m1Images)) {
            $this->info("✓ SUCCESS: All images migrated!");
        } else {
            $this->error("✗ FAILURE: Missing " . (count($m1Images) - count($m2ImagesAfter)) . " images");
            
            // Show which images are missing
            $m1Files = array_column($m1Images, 'file');
            $m2Files = array_column($m2ImagesAfter, 'file');
            $missing = array_diff($m1Files, $m2Files);
            
            if (!empty($missing)) {
                $this->error("Missing images:");
                foreach ($missing as $file) {
                    $this->line("  - {$file}");
                }
            }
        }

        return 0;
    }

    protected function findM1ProductBySku($sku, $connection, $prefix)
    {
        // Try entity table first
        try {
            $product = DB::connection($connection)
                ->table($prefix . 'catalog_product_entity')
                ->where('sku', $sku)
                ->first();
            
            if ($product) {
                return $product;
            }
        } catch (\Exception $e) {
            // SKU might be in EAV table
        }

        // Try EAV table
        $entityTypeId = DB::connection($connection)
            ->table($prefix . 'eav_entity_type')
            ->where('entity_type_code', 'catalog_product')
            ->value('entity_type_id');

        if (!$entityTypeId) {
            return null;
        }

        $skuAttributeId = DB::connection($connection)
            ->table($prefix . 'eav_attribute')
            ->where('entity_type_id', $entityTypeId)
            ->where('attribute_code', 'sku')
            ->value('attribute_id');

        if (!$skuAttributeId) {
            return null;
        }

        $skuRow = DB::connection($connection)
            ->table($prefix . 'catalog_product_entity_varchar')
            ->where('attribute_id', $skuAttributeId)
            ->where('value', $sku)
            ->where('store_id', 0)
            ->first();

        if (!$skuRow) {
            return null;
        }

        return DB::connection($connection)
            ->table($prefix . 'catalog_product_entity')
            ->where('entity_id', $skuRow->entity_id)
            ->first();
    }

    protected function findM2ProductBySku($sku, $connection, $prefix)
    {
        // Try entity table first
        try {
            $product = DB::connection($connection)
                ->table($prefix . 'catalog_product_entity')
                ->where('sku', $sku)
                ->first();
            
            if ($product) {
                return $product;
            }
        } catch (\Exception $e) {
            // SKU might be in EAV table
        }

        // Try EAV table
        $entityTypeId = DB::connection($connection)
            ->table($prefix . 'eav_entity_type')
            ->where('entity_type_code', 'catalog_product')
            ->value('entity_type_id');

        if (!$entityTypeId) {
            return null;
        }

        $skuAttributeId = DB::connection($connection)
            ->table($prefix . 'eav_attribute')
            ->where('entity_type_id', $entityTypeId)
            ->where('attribute_code', 'sku')
            ->value('attribute_id');

        if (!$skuAttributeId) {
            return null;
        }

        $skuRow = DB::connection($connection)
            ->table($prefix . 'catalog_product_entity_varchar')
            ->where('attribute_id', $skuAttributeId)
            ->where('value', $sku)
            ->where('store_id', 0)
            ->first();

        if (!$skuRow) {
            return null;
        }

        return DB::connection($connection)
            ->table($prefix . 'catalog_product_entity')
            ->where('entity_id', $skuRow->entity_id)
            ->first();
    }

    protected function getM1Images($productId, $connection, $prefix)
    {
        $images = [];

        // Check media gallery table
        try {
            $galleryImages = DB::connection($connection)
                ->table($prefix . 'catalog_product_entity_media_gallery')
                ->where('entity_id', $productId)
                ->get();

            foreach ($galleryImages as $img) {
                $images[] = [
                    'value_id' => $img->value_id,
                    'file' => $img->value,
                    'attribute_id' => $img->attribute_id ?? null,
                ];
            }
        } catch (\Exception $e) {
            // Table might not exist
        }

        // Also check individual image attributes
        $entityTypeId = DB::connection($connection)
            ->table($prefix . 'eav_entity_type')
            ->where('entity_type_code', 'catalog_product')
            ->value('entity_type_id');

        if ($entityTypeId) {
            $imageAttributes = ['image', 'small_image', 'thumbnail'];
            foreach ($imageAttributes as $attrCode) {
                $attrId = DB::connection($connection)
                    ->table($prefix . 'eav_attribute')
                    ->where('entity_type_id', $entityTypeId)
                    ->where('attribute_code', $attrCode)
                    ->value('attribute_id');

                if ($attrId) {
                    $imageValue = DB::connection($connection)
                        ->table($prefix . 'catalog_product_entity_varchar')
                        ->where('entity_id', $productId)
                        ->where('attribute_id', $attrId)
                        ->where('store_id', 0)
                        ->whereNotNull('value')
                        ->where('value', '!=', '')
                        ->where('value', '!=', 'no_selection')
                        ->value('value');

                    if ($imageValue && !in_array($imageValue, array_column($images, 'file'))) {
                        $images[] = [
                            'value_id' => null,
                            'file' => $imageValue,
                            'attribute_id' => $attrId,
                        ];
                    }
                }
            }
        }

        return $images;
    }

    protected function getM2Images($productId, $connection, $prefix)
    {
        $images = [];

        // Get media gallery attribute ID
        $entityTypeId = DB::connection($connection)
            ->table($prefix . 'eav_entity_type')
            ->where('entity_type_code', 'catalog_product')
            ->value('entity_type_id');

        if (!$entityTypeId) {
            return $images;
        }

        $mediaGalleryAttrId = DB::connection($connection)
            ->table($prefix . 'eav_attribute')
            ->where('entity_type_id', $entityTypeId)
            ->where('attribute_code', 'media_gallery')
            ->value('attribute_id');

        if (!$mediaGalleryAttrId) {
            return $images;
        }

        // Get images linked to this product via value_to_entity table
        $valueIds = DB::connection($connection)
            ->table($prefix . 'catalog_product_entity_media_gallery_value_to_entity')
            ->where('entity_id', $productId)
            ->pluck('value_id')
            ->toArray();

        if (empty($valueIds)) {
            return $images;
        }

        $galleryImages = DB::connection($connection)
            ->table($prefix . 'catalog_product_entity_media_gallery')
            ->whereIn('value_id', $valueIds)
            ->where('attribute_id', $mediaGalleryAttrId)
            ->get();

        foreach ($galleryImages as $img) {
            $images[] = [
                'value_id' => $img->value_id,
                'file' => $img->value,
            ];
        }

        return $images;
    }
}