<?php

namespace App\Services;

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\RowIterator;
use Illuminate\Support\Facades\Log;

class ExcelImportService
{
    /**
     * Get total number of rows in the Excel file
     */
    public function getTotalRows(string $filePath): int
    {
        try {
            $reader = IOFactory::createReaderForFile($filePath);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            
            return $worksheet->getHighestRow() - 1; // Subtract header row
        } catch (\Exception $e) {
            Log::error('Error counting rows in Excel file: ' . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Get header row and map to database columns
     */
    public function getHeaders(string $filePath): array
    {
        try {
            $reader = IOFactory::createReaderForFile($filePath);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            
            $headers = [];
            $row = $worksheet->getRowIterator(1, 1)->current();
            
            foreach ($row->getCellIterator() as $cell) {
                $value = $cell->getValue();
                if ($value !== null && $value !== '') {
                    $headers[] = trim($value);
                }
            }
            
            return $this->mapHeaders($headers);
        } catch (\Exception $e) {
            Log::error('Error reading headers from Excel file: ' . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Map Arabic Excel headers to English database columns
     */
    public function mapHeaders(array $excelHeaders): array
    {
        $columnMapping = config('excel_columns.column_mapping', []);
        $mappedHeaders = [];
        
        foreach ($excelHeaders as $index => $header) {
            $header = trim($header);
            if (isset($columnMapping[$header])) {
                $mappedHeaders[$index] = $columnMapping[$header];
            } else {
                // If header not found in mapping, use original (sanitized)
                $mappedHeaders[$index] = $this->sanitizeColumnName($header);
            }
        }
        
        return $mappedHeaders;
    }

    /**
     * Process a chunk of rows from the Excel file
     */
    public function processChunk(string $filePath, int $startRow, int $chunkSize, array $headerMapping): array
    {
        $data = [];
        
        try {
            $reader = IOFactory::createReaderForFile($filePath);
            $reader->setReadDataOnly(true);
            $spreadsheet = $reader->load($filePath);
            $worksheet = $spreadsheet->getActiveSheet();
            
            $endRow = min($startRow + $chunkSize - 1, $worksheet->getHighestRow());
            
            for ($row = $startRow; $row <= $endRow; $row++) {
                $rowData = $this->readRow($worksheet, $row, $headerMapping);
                
                // Skip completely empty rows
                if ($this->isRowEmpty($rowData)) {
                    continue;
                }
                
                $data[] = $rowData;
            }
        } catch (\Exception $e) {
            Log::error("Error processing chunk starting at row {$startRow}: " . $e->getMessage());
            throw $e;
        }
        
        return $data;
    }

    /**
     * Read a single row from the worksheet
     */
    protected function readRow($worksheet, int $rowNumber, array $headerMapping): array
    {
        $rowData = [];
        $highestColumn = $worksheet->getHighestColumn();
        $columnIndex = 0;
        
        foreach (range('A', $highestColumn) as $column) {
            $cell = $worksheet->getCell($column . $rowNumber);
            $value = $cell->getValue();
            
            // Get the mapped column name
            $columnName = $headerMapping[$columnIndex] ?? null;
            
            if ($columnName) {
                // Handle date columns
                if (in_array($columnName, config('excel_columns.date_columns', []))) {
                    $rowData[$columnName] = $this->convertExcelDate($value);
                } else {
                    $rowData[$columnName] = $this->cleanValue($value);
                }
            }
            
            $columnIndex++;
        }
        
        return $rowData;
    }

    /**
     * Convert Excel date serial to MySQL DATE format
     */
    public function convertExcelDate($excelDate): ?string
    {
        if (empty($excelDate)) {
            return null;
        }
        
        // If it's already a date string, try to parse it
        if (is_string($excelDate)) {
            $timestamp = strtotime($excelDate);
            if ($timestamp !== false) {
                return date('Y-m-d', $timestamp);
            }
        }
        
        // If it's a numeric Excel date serial
        if (is_numeric($excelDate)) {
            try {
                // Excel epoch is January 1, 1900
                $unixTimestamp = ($excelDate - 25569) * 86400;
                return date('Y-m-d', $unixTimestamp);
            } catch (\Exception $e) {
                Log::warning("Could not convert Excel date: {$excelDate}");
                return null;
            }
        }
        
        return null;
    }

    /**
     * Clean and sanitize cell value
     */
    protected function cleanValue($value)
    {
        if ($value === null) {
            return null;
        }
        
        // Convert to string and trim
        $value = trim((string) $value);
        
        // Return null for empty strings
        if ($value === '') {
            return null;
        }
        
        return $value;
    }

    /**
     * Check if a row is completely empty
     */
    protected function isRowEmpty(array $rowData): bool
    {
        foreach ($rowData as $value) {
            if ($value !== null && $value !== '') {
                return false;
            }
        }
        
        return true;
    }

    /**
     * Sanitize column name for database
     */
    protected function sanitizeColumnName(string $name): string
    {
        // Remove special characters, replace spaces with underscores
        $name = preg_replace('/[^a-zA-Z0-9_]/', '_', $name);
        $name = strtolower($name);
        return $name;
    }
}

