<?php

namespace Database\Seeders;

use App\Models\Product;
use Illuminate\Database\Seeder;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ProductFromExcelSeeder extends Seeder
{
    /**
     * Mapping of filename patterns to category IDs.
     * Format: 'pattern' => category_id
     */
    protected array $categoryMap = [
        'appearal_accessories' => 21, // Apparel Accessories
        'automobiles_parts' => 32, // Car Parts & Accessories
        'beauty_health' => 25, // Beauty & Health
        'computer_and_office' => 4, // Computer & Office
        'consumer_electornics' => 2, // Consumer Electronics (note: typo in original filename)
        'consumer_electronics' => 2, // Consumer Electronics
        'elec_components' => 5, // Electronic Components & Supplies
        'home_applinces' => 8, // Home Appliances (note: typo in original filename)
        'home_appliances' => 8, // Home Appliances
        'home_garden' => 10, // Home & Garden
        'home_improvment' => 9, // Home Improvement (note: typo in original filename)
        'home_improvement' => 9, // Home Improvement
        'jewlery_accessories' => 22, // Jewelry & Accessories (note: typo in original filename)
        'jewelry_accessories' => 22, // Jewelry & Accessories
        'lights' => 12, // Lights & Lighting
        'men_clothes' => 17, // Men's Clothing
        'motorcycle' => 33, // Motorcycle Equipments & Parts
        'novelty' => 41, // Novelty & Special Use
        'office_school' => 36, // Office & School Supplies
        'phone_accessories' => 6, // Phones & Telecommunications Accessories
        'security' => 13, // Security & Protection
        'shoes' => 18, // Shoes
        'sports_entertainment' => 28, // Sports & Entertainment
        'toys' => 30, // Toys & Hobbies
    ];

    /**
     * Excel column mappings (0-indexed).
     */
    protected array $columnMap = [
        'product_id' => 0, // ProductId
        'image_url' => 1, // Image Url
        'video_url' => 2, // Video Url
        'description' => 3, // Product Desc
        'price' => 4, // Origin Price
        'discount_price' => 5, // Discount Price
        'discount' => 6, // Discount
        'currency' => 7, // Currency
        'direct_commission_rate' => 8, // Direct linking commission rate (%)
        'estimated_direct_commission' => 9, // Estimated direct linking commission
        'indirect_commission_rate' => 10, // Indirect linking commission rate (%)
        'estimated_indirect_commission' => 11, // Estimated indirect linking commission
        'sales_180day' => 12, // Sales180Day
        'positive_feedback' => 13, // Positive Feedback
        'promotion_url' => 14, // Promotion Url
        'code_name' => 15, // Code Name
        'code_start_time' => 16, // Code Start Time
        'code_end_time' => 17, // Code End Time
        'code_value' => 18, // Code Value
        'code_quantity' => 19, // Code Quantity
        'code_minimum_spend' => 20, // Code Minimum Spend
    ];

    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        $excelPath = public_path('storage/excels');
        $files = glob($excelPath.'/*.xls');

        if (empty($files)) {
            $this->command->warn('No Excel files found in '.$excelPath);

            return;
        }

        $this->command->info('Found '.count($files).' Excel files');

        $importedCount = 0;
        $skippedCount = 0;
        $errorCount = 0;

        foreach ($files as $file) {
            $filename = basename($file);
            $this->command->info("Processing: {$filename}");

            // Determine if it's Arabic or English
            $isArabic = str_contains($filename, 'arabic');
            $language = $isArabic ? 'arabic' : 'english';

            // Find matching category
            $categoryId = $this->getCategoryIdFromFilename($filename);

            if ($categoryId === null) {
                $this->command->warn('  Skipped: No matching category found');
                $skippedCount++;

                continue;
            }

            try {
                $spreadsheet = IOFactory::load($file);
                $worksheet = $spreadsheet->getActiveSheet();
                $highestRow = $worksheet->getHighestRow();

                $this->command->info("  Category ID: {$categoryId}, Language: {$language}, Rows: {$highestRow}");

                // Skip header row (start from row 2)
                for ($row = 2; $row <= $highestRow; $row++) {
                    $rowData = $this->getRowData($worksheet, $row);

                    if (empty($rowData['product_id'])) {
                        continue;
                    }

                    $this->importProduct($rowData, $categoryId, $language);
                    $importedCount++;
                }

                $this->command->info('  Imported rows from this file');

            } catch (\Exception $e) {
                $this->command->error('  Error processing file: '.$e->getMessage());
                $errorCount++;
            }
        }

        $this->command->info(PHP_EOL.'Import Summary:');
        $this->command->info("  Imported: {$importedCount} products");
        $this->command->info("  Skipped: {$skippedCount} files");
        $this->command->info("  Errors: {$errorCount} files");
    }

    /**
     * Get category ID from filename.
     */
    protected function getCategoryIdFromFilename(string $filename): ?int
    {
        foreach ($this->categoryMap as $pattern => $categoryId) {
            if (str_contains($filename, $pattern)) {
                return $categoryId;
            }
        }

        return null;
    }

    /**
     * Get row data as associative array.
     */
    protected function getRowData($worksheet, int $row): array
    {
        $data = [];
        $rowIterator = $worksheet->getRowIterator()->seek($row)->current();
        $cellIterator = $rowIterator->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false);

        $colIndex = 0;
        foreach ($cellIterator as $cell) {
            // Find which key this column belongs to
            foreach ($this->columnMap as $key => $expectedIndex) {
                if ($expectedIndex === $colIndex) {
                    $data[$key] = $this->sanitizeValue($cell->getValue());
                    break;
                }
            }
            $colIndex++;

            // Stop if we've processed all our columns of interest
            if ($colIndex > max($this->columnMap)) {
                break;
            }
        }

        return $data;
    }

    /**
     * Sanitize cell value.
     */
    protected function sanitizeValue($value): mixed
    {
        if ($value === null) {
            return null;
        }

        $value = trim((string) $value);

        if ($value === '') {
            return null;
        }

        return $value;
    }

    /**
     * Import a single product.
     */
    protected function importProduct(array $rowData, int $categoryId, string $language): void
    {
        $description = $this->truncateString($rowData['description'] ?? 'N/A', 255);

        // Extract numeric values from strings like "EGP 251.20"
        $price = $this->extractNumericPrice($rowData['price']);
        $discountPrice = $this->extractNumericPrice($rowData['discount_price']);
        $estimatedDirectCommission = $this->extractNumericPrice($rowData['estimated_direct_commission']);
        $estimatedIndirectCommission = $this->extractNumericPrice($rowData['estimated_indirect_commission']);
        $codeValue = $this->extractNumericPrice($rowData['code_value']);
        $codeMinimumSpend = $this->extractNumericPrice($rowData['code_minimum_spend']);

        // Extract percentage from strings like "79%" or "7.0"
        $discount = $this->extractPercentage($rowData['discount']);
        $directCommissionRate = $this->extractPercentage($rowData['direct_commission_rate']);
        $indirectCommissionRate = $this->extractPercentage($rowData['indirect_commission_rate']);
        $positiveFeedback = $this->extractPercentage($rowData['positive_feedback']);

        // Clean video URL (empty array check)
        $videoUrl = ! empty($rowData['video_url']) && $rowData['video_url'] !== '[]'
            ? $rowData['video_url']
            : null;

        $existing = Product::where('source', 'aliexpress')
            ->where('product_id', $rowData['product_id'])
            ->first();

        $nameAr = $language === 'arabic' ? $description : ($existing?->name_ar ?? 'N/A');
        $nameEn = $language === 'english' ? $description : ($existing?->name_en ?? 'N/A');
        $descriptionAr = $language === 'arabic' ? $description : ($existing?->description_ar ?? null);
        $descriptionEn = $language === 'english' ? $description : ($existing?->description_en ?? null);

        Product::updateOrCreate(
            [
                'source' => 'aliexpress',
                'product_id' => $rowData['product_id'],
            ],
            [
                'category_id' => $categoryId,
                'image_url' => $rowData['image_url'],
                'video_url' => $videoUrl,
                'name_ar' => $nameAr,
                'name_en' => $nameEn,
                'description_ar' => $descriptionAr,
                'description_en' => $descriptionEn,
                'price' => $price,
                'discount_price' => $discountPrice,
                'discount' => $discount,
                'currency' => $rowData['currency'] ?? 'EGP',
                'clicks' => 0,
                'direct_commission_rate' => $directCommissionRate,
                'estimated_direct_commission' => $estimatedDirectCommission,
                'indirect_commission_rate' => $indirectCommissionRate,
                'estimated_indirect_commission' => $estimatedIndirectCommission,
                'sales_180day' => (int) ($rowData['sales_180day'] ?? 0),
                'positive_feedback' => $positiveFeedback,
                'promotion_url' => $rowData['promotion_url'],
                'code_name' => $rowData['code_name'] ?: null,
                'code_start_time' => $rowData['code_start_time'] ?: null,
                'code_end_time' => $rowData['code_end_time'] ?: null,
                'code_value' => $codeValue,
                'code_quantity' => (int) ($rowData['code_quantity'] ?? 0),
                'code_minimum_spend' => $codeMinimumSpend,
            ]
        );
    }

    /**
     * Extract numeric price from string like "EGP 251.20".
     */
    protected function extractNumericPrice(?string $value): ?float
    {
        if ($value === null) {
            return null;
        }

        // Remove currency codes and spaces, keep numbers and decimal point
        $cleaned = preg_replace('/[^0-9.]/', '', $value);

        return $cleaned !== '' ? (float) $cleaned : null;
    }

    /**
     * Extract percentage from string like "79%" or "7.0".
     */
    protected function extractPercentage(?string $value): ?float
    {
        if ($value === null) {
            return null;
        }

        // Remove % sign
        $cleaned = str_replace('%', '', $value);
        $cleaned = preg_replace('/[^0-9.]/', '', $cleaned);

        return $cleaned !== '' ? (float) $cleaned : null;
    }

    /**
     * Truncate string to max length, preserving whole words.
     */
    protected function truncateString(?string $value, int $maxLength): string
    {
        if ($value === null) {
            return '';
        }

        if (mb_strlen($value) <= $maxLength) {
            return $value;
        }

        return mb_substr($value, 0, $maxLength - 3).'...';
    }
}
