<?php

namespace App\Services;

use App\Models\Product;
use Illuminate\Http\Request;

class SimpleExcelImporter
{
    public function import(Request $request)
    {
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv|max:10240',
            'category_id' => 'required|exists:categories,id',
            'source' => 'required|in:aliexpress,amazon,noon',
        ]);

        $file = $request->file('file');
        $categoryId = $request->category_id;
        $source = $request->source;

        // Read the file
        $data = $this->readExcelFile($file);

        if (empty($data)) {
            throw new \Exception('No data found in file. Please make sure your file has headers and data rows.');
        }

        \Log::info('Import data', ['count' => count($data), 'first_row' => $data[0] ?? []]);

        // Import products
        $imported = 0;
        $errors = [];
        foreach ($data as $index => $row) {
            try {
                // Skip rows without origin_price
                if (empty($row['origin_price']) || $row['origin_price'] <= 0) {
                    $errors[] = 'Row '.($index + 2).': Missing or invalid origin_price';

                    continue;
                }

                Product::create([
                    'category_id' => $categoryId,
                    'source' => $source,
                    'name_ar' => $row['product_desc'] ?? 'No description',
                    'name_en' => $row['product_desc'] ?? 'No description',
                    'description_ar' => $row['product_desc'] ?? null,
                    'description_en' => $row['product_desc'] ?? null,
                    'price' => (float) ($row['origin_price'] ?? 0),
                    'discount_price' => ! empty($row['discount_price']) && $row['discount_price'] > 0 ? (float) $row['discount_price'] : null,
                    'currency' => $row['currency'] ?? 'EGP',
                    'clicks' => 0,
                    'direct_commission_rate' => (float) ($row['direct_linking_commission_rate_'] ?? 0),
                    'estimated_direct_commission' => (float) ($row['estimated_direct_linking_commission'] ?? 0),
                    'indirect_commission_rate' => (float) ($row['indirect_linking_commission_rate_'] ?? 0),
                    'estimated_indirect_commission' => (float) ($row['estimated_indirect_linking_commission'] ?? 0),
                    'sales_180day' => (int) ($row['sales180day'] ?? 0),
                    'positive_feedback' => (float) ($row['positive_feedback'] ?? 0),
                    'promotion_url' => $row['promotion_url'] ?? null,
                    'code_name' => $row['code_name'] ?? null,
                    'code_start_time' => $this->parseDate($row['code_start_time'] ?? null),
                    'code_end_time' => $this->parseDate($row['code_end_time'] ?? null),
                    'code_value' => ! empty($row['code_value']) && $row['code_value'] > 0 ? (float) $row['code_value'] : null,
                    'code_quantity' => ! empty($row['code_quantity']) && $row['code_quantity'] > 0 ? (int) $row['code_quantity'] : null,
                    'code_minimum_spend' => ! empty($row['code_minimum_spend']) && $row['code_minimum_spend'] > 0 ? (float) $row['code_minimum_spend'] : null,
                ]);
                $imported++;
            } catch (\Exception $e) {
                $errors[] = 'Row '.($index + 2).': '.$e->getMessage();

                continue;
            }
        }

        if (! empty($errors)) {
            \Log::error('Import errors', $errors);
        }

        return $imported;
    }

    private function readExcelFile($file)
    {
        $extension = $file->getClientOriginalExtension();
        $data = [];

        if ($extension === 'csv') {
            $data = $this->readCsv($file);
        } else {
            // For xlsx/xls, we'll use a simple approach
            // Since we don't have the Excel package, we'll try to convert to CSV or use a fallback
            // For now, let's try using PHP's built-in functions
            $data = $this->readExcelAsCsv($file);
        }

        return $data;
    }

    private function readCsv($file)
    {
        $data = [];
        $filePath = $file->getRealPath();

        \Log::info('Reading CSV file', ['path' => $filePath, 'exists' => file_exists($filePath)]);

        $handle = fopen($filePath, 'r');

        if ($handle === false) {
            \Log::error('Could not open file for reading');

            return $data;
        }

        // Get headers
        $headers = fgetcsv($handle, 0, ',');
        if ($headers === false) {
            fclose($handle);
            \Log::error('Could not read CSV headers');

            return $data;
        }

        \Log::info('CSV headers read', ['headers' => $headers]);

        // Clean headers (remove spaces, convert to lowercase, replace spaces with underscores)
        $headers = array_map(function ($header) {
            $cleaned = strtolower(trim(str_replace([' ', '/', '%', '(', ')'], '_', $header)));

            // Remove trailing underscores
            return rtrim($cleaned, '_');
        }, $headers);

        \Log::info('Cleaned headers', ['headers' => $headers]);

        // Read data
        $rowCount = 0;
        while (($row = fgetcsv($handle, 0, ',')) !== false) {
            if (count($row) === count($headers)) {
                $combined = array_combine($headers, $row);
                if (! empty($combined)) {
                    $data[] = $combined;
                    $rowCount++;
                }
            } else {
                \Log::warning('Row count mismatch', ['row' => $rowCount, 'expected' => count($headers), 'got' => count($row)]);
            }
        }

        fclose($handle);

        \Log::info('CSV reading complete', ['total_rows' => $rowCount]);

        return $data;
    }

    private function readExcelAsCsv($file)
    {
        // Fallback: Try to read as CSV (some Excel files can be read this way)
        $data = [];
        $filePath = $file->getRealPath();
        $handle = fopen($filePath, 'r');

        if ($handle === false) {
            \Log::error('Could not open Excel file for reading');

            return $data;
        }

        // Try to detect delimiter
        $firstLine = fgets($handle);
        rewind($handle);

        $delimiters = [',', "\t", ';', '|'];
        $delimiter = ',';
        $maxCount = 0;

        foreach ($delimiters as $delim) {
            $count = substr_count($firstLine, $delim);
            if ($count > $maxCount) {
                $maxCount = $count;
                $delimiter = $delim;
            }
        }

        \Log::info('Detected delimiter', ['delimiter' => $delimiter === "\t" ? 'TAB' : $delimiter]);

        // Get headers
        $headers = fgetcsv($handle, 0, $delimiter);
        if ($headers === false) {
            fclose($handle);
            \Log::error('Could not read Excel headers');

            return $data;
        }

        \Log::info('Excel headers read', ['headers' => $headers]);

        // Clean headers
        $headers = array_map(function ($header) {
            $cleaned = strtolower(trim(str_replace([' ', '/', '%', '(', ')'], '_', $header)));

            return rtrim($cleaned, '_');
        }, $headers);

        // Read data
        $rowCount = 0;
        while (($row = fgetcsv($handle, 0, $delimiter)) !== false) {
            if (count($row) === count($headers)) {
                $combined = array_combine($headers, $row);
                if (! empty($combined)) {
                    $data[] = $combined;
                    $rowCount++;
                }
            }
        }

        fclose($handle);

        \Log::info('Excel reading complete', ['total_rows' => $rowCount]);

        return $data;
    }

    private function parseDate($date)
    {
        if (empty($date)) {
            return null;
        }

        try {
            return \Carbon\Carbon::parse($date)->format('Y-m-d H:i:s');
        } catch (\Exception $e) {
            return null;
        }
    }
}
