<?php

namespace App\Jobs;

use App\Models\Booth;
use App\Models\Street;
use App\Models\Voter;
use App\Services\OcrService;
use App\Services\VoterBoxParser;
use Illuminate\Bus\Queueable;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Log;
use thiagoalessio\TesseractOCR\TesseractOCR;

class ProcessVoterImageBatch implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    protected string $directory;
    protected int $startIndex;
    protected ?int $maxImages;
    protected array $options;
    protected array $results;

    public function __construct(string $directory, int $startIndex = 0, ?int $maxImages = null, array $options = [])
    {
        $this->directory = $directory;
        $this->startIndex = $startIndex;
        $this->maxImages = $maxImages;
        $this->options = $options;
    }

    public function handle(): array
    {
        set_time_limit(3600);
        ini_set('max_execution_time', '3600');
        
        Log::info('Starting voter batch import', ['directory' => $this->directory, 'start_index' => $this->startIndex, 'max_images' => $this->maxImages]);
        
        $this->results = [
            'booth' => null,
            'voters_files' => [],
            'total_imported' => 0,
            'total_updated' => 0,
            'total_deleted' => 0,
            'total_skipped' => 0,
            'skipped_voters' => [],
            'warning_data' => [],
            'skipped_excel_path' => null,
            'warning_excel_path' => null
        ];

        if (!is_dir($this->directory)) {
            Log::error('Directory not found for batch import', ['directory' => $this->directory]);
            return $this->results;
        }

        $images = collect(File::files($this->directory))
            ->filter(fn($f) => in_array(strtolower($f->getExtension()), ['jpg','jpeg','png']))
            ->sortBy(fn($f) => $f->getFilename())
            ->values();

        if ($images->isEmpty()) {
            Log::warning('No images found for batch import', ['directory' => $this->directory]);
            return $this->results;
        }
        
        $boothInfoFile = $images->first(fn($file) => str_contains(strtolower($file->getFilename()), 'booth_info'));
        
        Log::info('Booth info file search', [
            'found' => $boothInfoFile ? true : false, 
            'filename' => $boothInfoFile ? $boothInfoFile->getFilename() : 'none',
            'path' => $boothInfoFile ? $boothInfoFile->getRealPath() : 'none'
        ]);
        
        $extractedBoothNumber = null;
        $extractedBoothAddress = null;
        $extractedStreetNames = [];
        if ($boothInfoFile) {
            [$extractedBoothNumber, $extractedBoothAddress, $extractedStreetNames] = $this->extractBoothDetails($boothInfoFile->getRealPath());
            Log::info('Booth extraction result', [
                'booth_number' => $extractedBoothNumber,
                'booth_address' => $extractedBoothAddress,
                'street_count' => count($extractedStreetNames)
            ]);
        } else {
            Log::warning('No booth_info file found in directory', ['directory' => $this->directory]);
        }
        
        if ($extractedBoothNumber) {
            // Ensure booth_address is set on create to satisfy DB constraints
            $booth = Booth::firstOrCreate(
                ['booth_number' => $extractedBoothNumber],
                ['booth_address' => $extractedBoothAddress ?? '']
            );
            // Update booth address if we extracted one and it's different
            if ($extractedBoothAddress && $booth->booth_address !== $extractedBoothAddress) {
                $booth->booth_address = $extractedBoothAddress;
                $booth->save();
            }
            // If streets were extracted from booth info, upsert them and set booth->streets with their IDs
            $streetNames = [];
            $streetIds = [];
            if (!empty($extractedStreetNames)) {
                foreach ($extractedStreetNames as $sname) {
                    if (!$sname) continue;
                    $street = Street::firstOrCreate(['street_name' => $sname]);
                    $streetIds[] = $street->id;
                }
                $booth->streets = $streetIds;
                $booth->save();
                $streetNames = Street::whereIn('id', $streetIds)->pluck('street_name')->toArray();
            } else {
                // fallback to resolve any pre-existing booth streets
                $existingIds = is_array($booth->streets) ? $booth->streets : [];
                if (!empty($existingIds)) {
                    $streetNames = Street::whereIn('id', $existingIds)->pluck('street_name')->toArray();
                }
            }
            $this->results['booth'] = [
                'booth_number' => $booth->booth_number,
                'booth_address' => $booth->booth_address,
                'streets' => $streetNames,
            ];
        }

        $voterPages = $images->filter(fn($file) => str_contains(strtolower($file->getFilename()), 'voter'))->values();
        
        if ($voterPages->isEmpty()) {
            Log::warning('No voter pages found');
            return $this->results;
        }
        
        // Process all voter pages from startIndex; respect maxImages if provided
        $processSlice = $voterPages->slice($this->startIndex);
        if ($this->maxImages !== null && $this->maxImages > 0) {
            $processSlice = $processSlice->take($this->maxImages);
        }

        // Use extracted booth number, or fall back to directory name (last part of path)
        $boothNumber = $extractedBoothNumber;
        if (!$boothNumber) {
            // Extract booth number from directory path (e.g., "/path/to/Constituency/16 - ORLEAMPETH/11" -> "11")
            $pathParts = explode('/', rtrim($this->directory, '/'));
            $boothNumber = end($pathParts);
            Log::info('Using booth number from directory path', ['booth_number' => $boothNumber, 'directory' => $this->directory]);
        }
        
        // Ensure booth exists in database (create if needed)
        if ($boothNumber) {
            $booth = Booth::firstOrCreate(
                ['booth_number' => $boothNumber],
                ['booth_address' => $extractedBoothAddress ?? '']
            );
            // Update booth address if we extracted one and it's different
            if ($extractedBoothAddress && $booth->booth_address !== $extractedBoothAddress) {
                $booth->booth_address = $extractedBoothAddress;
                $booth->save();
            }
            $boothId = $booth->id;
            Log::info('Booth ensured in database', ['booth_id' => $boothId, 'booth_number' => $boothNumber]);
        } else {
            $boothId = null;
        }
        
        // Update results with booth info even if extraction failed
        if ($boothId && !isset($this->results['booth'])) {
            $booth = Booth::find($boothId);
            if ($booth) {
                $streetNames = [];
                $existingIds = is_array($booth->streets) ? $booth->streets : [];
                if (!empty($existingIds)) {
                    $streetNames = Street::whereIn('id', $existingIds)->pluck('street_name')->toArray();
                }
                $this->results['booth'] = [
                    'booth_number' => $booth->booth_number,
                    'booth_address' => $booth->booth_address,
                    'streets' => $streetNames,
                ];
            }
        }

        // Optional purge of existing voters for this booth to avoid legacy incorrect serials
        if (($this->options['reset_booth_voters'] ?? false) && $boothId) {
            \App\Models\Voter::where('booth_id', $boothId)->delete();
            Log::info('Purged existing voters for booth prior to re-import', ['booth_id' => $boothId]);
        }

        $useCrops = $this->options['use_crops'] ?? false;

        $pageIndex = 0;
        foreach ($processSlice as $imageFile) {
            $imagePath = $imageFile->getRealPath();
            Log::info('Processing voter page', ['image' => $imagePath]);

            $ocrService = new OcrService();
            $parser = new VoterBoxParser();

            // Extract section street from page header: "Section No and Name : 1-..."
            $sectionStreetName = null;
            $sectionStreetId = null;
            try {
                $pageText = $ocrService->extractText($imagePath);
                if (preg_match('/Section\s+No\s+and\s+Name\s*:?\s*(.+)/i', $pageText, $sm)) {
                    $raw = trim($sm[1]);
                    // Remove leading index like "1-"
                    $headerStreet = trim(preg_replace('/^\d+\s*-\s*/', '', $raw));
                    if ($headerStreet) {
                        // Reduce to base street (before first comma) for matching
                        $base = trim(preg_split('/\s*,\s*/', $headerStreet)[0]);
                        [$sectionStreetId, $sectionStreetName] = $this->resolveStreetByBaseName($base);
                        if (!$sectionStreetId && $base) {
                            // Street not found - create it automatically
                            Log::info('Street not found in streets table, creating new street', [
                                'raw' => $headerStreet, 
                                'base' => $base
                            ]);
                            
                            try {
                                $newStreet = Street::create([
                                    'street_name' => $base,
                                    'is_deleted' => false
                                ]);
                                $sectionStreetId = $newStreet->id;
                                $sectionStreetName = $newStreet->street_name;
                                
                                Log::info('Created new street', [
                                    'street_id' => $sectionStreetId,
                                    'street_name' => $sectionStreetName
                                ]);
                            } catch (\Exception $e) {
                                Log::error('Failed to create street', [
                                    'street_name' => $base,
                                    'error' => $e->getMessage()
                                ]);
                                // Keep the name even if creation failed
                                $sectionStreetName = $base;
                            }
                        }
                    }
                } else {
                    Log::warning('Section No and Name header not found on page', ['image' => basename($imagePath)]);
                }
            } catch (\Throwable $e) {
                Log::error('Failed extracting section street from page', ['error' => $e->getMessage()]);
            }
            
            if ($useCrops) {
                $cropOptions = $this->options['crop_options'] ?? [];
                $crops = $ocrService->cropGridImages(
                    $imagePath,
                    $cropOptions['cols'] ?? 3,
                    $cropOptions['rows'] ?? 10,
                    $cropOptions
                );
                
                $ocrResults = $ocrService->ocrCrops($crops);
                $pageVoters = [];
                $deletedRecords = [];

                foreach ($ocrResults as $result) {
                    Log::info("Processing crop for row: {$result->row}, col: {$result->col}", ['path' => $result->path]);
                    Log::debug("OCR Text for r:{$result->row},c:{$result->col}", ['text' => $result->text]);
                    
                    // Positional voter ID selection: use structured OCR to find ID in top-right band
                    $positionalVoterId = null;
                    // Positional serial selection: find numeric-only token in top-left boxed area
                    $positionalSerial = null;
                    $serialCandidates = [];
                    try {
                        $words = $ocrService->extractStructuredData($result->path);
                        if (!empty($words)) {
                            $cropW = $result->w; $cropH = $result->h;
                            // Stricter positional constraints to avoid neighbor bleed
                            $rightBandX = (int)round($cropW * 0.55); // rightmost 45%
                            $topBandY = (int)round($cropH * 0.02);   // very top edge
                            $topBandH = (int)round($cropH * 0.15);   // narrower top band (15%)
                            $leftBandX = (int)round($cropW * 0.02);   // minimal left margin
                            $leftBandW = (int)round($cropW * 0.25);   // narrow band for serial box only (left 25%)
                            // Detect photo box region using 'Photo' and 'Available' tokens
                            $photoLeft = null; $photoTop = null; $photoRight = null; $photoBottom = null;
                            foreach ($words as $w) {
                                if (preg_match('/^(Photo|Available)$/i', $w['text'])) {
                                    // Coordinates from extractStructuredData on crop are already crop-relative
                                    $relX = $w['left']; $relY = $w['top'];
                                    if ($photoLeft === null) {
                                        $photoLeft = $relX; $photoTop = $relY; $photoRight = $relX + ($w['width'] ?? 0); $photoBottom = $relY + ($w['height'] ?? 0);
                                    } else {
                                        $photoLeft = min($photoLeft, $relX);
                                        $photoTop = min($photoTop, $relY);
                                        $photoRight = max($photoRight, $relX + ($w['width'] ?? 0));
                                        $photoBottom = max($photoBottom, $relY + ($w['height'] ?? 0));
                                    }
                                }
                            }
                            $candidates = [];
                            foreach ($words as $w) {
                                $text = $w['text'];
                                $originalText = $text;
                                
                                // Normalize OCR confusion in voter IDs: convert digits to similar letters in first 3 positions
                                // Common OCR errors: 1->I, 0->O, 5->S, 8->B
                                // Pattern matches: 2-3 alphanumeric chars + 7-8 digits (e.g., XG10780700, XDQ0109009)
                                if (preg_match('/^[A-Z0-9]{2,3}\d{7,8}$/', $text)) {
                                    // Extract first 3 chars and normalize them
                                    $prefix = substr($text, 0, 3);
                                    $suffix = substr($text, 3);
                                    // Replace digit-like characters with letters in prefix
                                    $normalizedPrefix = str_replace(['1', '0', '5', '8'], ['I', 'O', 'S', 'B'], $prefix);
                                    $normalizedId = $normalizedPrefix . $suffix;
                                    $text = $normalizedId;
                                    
                                    if ($originalText !== $text) {
                                        Log::debug('Voter ID normalized', [
                                            'original' => $originalText,
                                            'normalized' => $text,
                                            'prefix_original' => $prefix,
                                            'prefix_normalized' => $normalizedPrefix
                                        ]);
                                    }
                                }
                                
                                // Accept voter IDs with 3 letters followed by 7 digits (e.g., XDQ0109009, XGI0780700)
                                if (preg_match('/^[A-Z]{3}\d{7}$/', $text)) {
                                    // Coordinates are already crop-relative when extractStructuredData is called on crop
                                    $relX = $w['left'];
                                    $relY = $w['top'];
                                    if ($relX < 0 || $relY < 0) continue;
                                    // basic size sanity for token height to reduce noise
                                    $h = $w['height'] ?? 0;
                                    if ($h < 10 || $h > ($cropH * 0.5)) continue;
                                    $inTopRightBand = ($relX >= $rightBandX && $relY >= $topBandY && $relY <= ($topBandY + $topBandH));
                                    // If photo box detected, allow ID to be near or right of photo box (with 20px tolerance)
                                    $anchoredToPhoto = true;
                                    if ($photoLeft !== null) {
                                        $anchoredToPhoto = ($relX >= ($photoLeft - 20));
                                    }
                                    
                                    if ($text === 'XGI0780700') {
                                        Log::debug('XGI0780700 position check', [
                                            'relX' => $relX,
                                            'relY' => $relY,
                                            'rightBandX' => $rightBandX,
                                            'topBandY' => $topBandY,
                                            'topBandH' => $topBandH,
                                            'inTopRightBand' => $inTopRightBand,
                                            'photoLeft' => $photoLeft,
                                            'anchoredToPhoto' => $anchoredToPhoto,
                                            'passes' => ($inTopRightBand && $anchoredToPhoto)
                                        ]);
                                    }
                                    
                                    if ($inTopRightBand && $anchoredToPhoto) {
                                        // Distance to top-right corner for tie-breaking
                                        $dx = ($cropW - $relX);
                                        $dy = (0 - $relY);
                                        $dist = sqrt(($dx*$dx) + ($dy*$dy));
                                        $candidates[] = ['id' => $text, 'dist' => $dist, 'relX' => $relX, 'relY' => $relY];
                                    }
                                }
                                // Serial candidates: numeric-only tokens - use relaxed and strict bands
                                // Accept plain digits or tokens with optional leading '#' or 'S' (for deleted profiles)
                                if (preg_match('/^[#S]?\s*\d{1,4}$/', $w['text'])) {
                                    // Coordinates are already crop-relative
                                    $relX = $w['left'];
                                    $relY = $w['top'];
                                    if ($relX < 0 || $relY < 0) continue;
                                    $h = $w['height'] ?? 0;
                                    if ($h < 6 || $h > ($cropH * 0.6)) continue;
                                    
                                    // Try strict band first (very top-left corner)
                                    $inStrictBand = ($relX >= $leftBandX && $relX <= ($leftBandX + $leftBandW) && $relY >= $topBandY && $relY <= ($topBandY + $topBandH));
                                    // Also try relaxed band (slightly wider) as fallback
                                    $relaxedLeftW = (int)round($cropW * 0.35);
                                    $relaxedTopH = (int)round($cropH * 0.20);
                                    $inRelaxedBand = ($relX >= $leftBandX && $relX <= ($leftBandX + $relaxedLeftW) && $relY >= $topBandY && $relY <= ($topBandY + $relaxedTopH));
                                    
                                    if ($inStrictBand || $inRelaxedBand) {
                                        // Prefer the one closest to top-left corner
                                        $dx = ($relX - 0);
                                        $dy = ($relY - 0);
                                        $dist = sqrt(($dx*$dx) + ($dy*$dy));
                                        // Normalize by stripping any leading '#' or 'S'
                                        $normalized = preg_replace('/^[#S]\s*/', '', $w['text']);
                                        $priority = $inStrictBand ? 1 : 2; // prefer strict matches
                                        $serialCandidates[] = ['serial' => $normalized, 'dist' => $dist, 'priority' => $priority, 'relX' => $relX, 'relY' => $relY];
                                    }
                                }
                            }
                            if (!empty($candidates)) {
                                usort($candidates, fn($a,$b) => $a['dist'] <=> $b['dist']);
                                $positionalVoterId = $candidates[0]['id'];
                            }
                            if (!empty($serialCandidates ?? [])) {
                                // Sort by priority first, then distance
                                usort($serialCandidates, function($a, $b) {
                                    if ($a['priority'] !== $b['priority']) return $a['priority'] <=> $b['priority'];
                                    return $a['dist'] <=> $b['dist'];
                                });
                                $positionalSerial = intval($serialCandidates[0]['serial']);
                            }
                        }
                    } catch (\Throwable $e) {
                        Log::warning('Structured OCR failed for crop; falling back to text ID heuristic', ['error' => $e->getMessage()]);
                    }

                    $voterData = $parser->parseVoterBox($result->text);
                    
                    // Check if this voter profile is marked as DELETED
                    $isDeleted = $this->isVoterDeleted($result->text);
                    
                    if ($isDeleted) {
                        // Extract basic info for deleted record tracking
                        $deletedRecord = [
                            'serial_number' => $voterData['serial_number'] ?? null,
                            'voter_id_number' => $voterData['voter_id_number'] ?? null,
                            'name' => $voterData['name'] ?? null,
                            'row' => $result->row,
                            'col' => $result->col,
                            'position' => "Row {$result->row}, Col {$result->col}"
                        ];
                        $deletedRecords[] = $deletedRecord;
                        
                        Log::info('Deleted voter profile detected', [
                            'page' => basename($imagePath),
                            'serial' => $deletedRecord['serial_number'],
                            'name' => $deletedRecord['name'],
                            'voter_id' => $deletedRecord['voter_id_number'],
                            'position' => $deletedRecord['position']
                        ]);
                        
                        // Skip processing this voter - don't add to pageVoters
                        if (!(bool)($this->options['keep_crops'] ?? false)) {
                            @unlink($result->path);
                        }
                        continue; // Skip to next crop
                    }
                    
                    // Multi-strategy serial extraction with fallback chain:
                    // 1. Positional structured OCR (most accurate when it works)
                    // 2. Text-based heuristic from first lines
                    // 3. Keep null if both fail (don't auto-generate)
                    
                    $extractedSerial = null;
                    $extractionMethod = null;
                    
                    // Strategy 1: Use positional structured OCR result
                    if ($positionalSerial !== null) {
                        $extractedSerial = $positionalSerial;
                        $extractionMethod = 'positional';
                    }
                    // Strategy 2: Fall back to text heuristic if positional failed
                    elseif (empty($voterData['serial_number'])) {
                        [$serialNumber, $rawSerialLine] = $this->extractSerialNumberFromText($result->text);
                        if ($serialNumber !== null) {
                            $extractedSerial = intval($serialNumber);
                            $extractionMethod = 'text_heuristic';
                            $voterData['raw_serial_line'] = $rawSerialLine;
                        }
                    }
                    
                    // Log extraction details for debugging
                    Log::info('Serial extraction', [
                        'page' => $pageIndex,
                        'row' => $result->row,
                        'col' => $result->col,
                        'positional_candidates' => count($serialCandidates ?? []),
                        'positional_serial' => $positionalSerial,
                        'extracted_serial' => $extractedSerial,
                        'method' => $extractionMethod,
                        'voter_id' => $voterData['voter_id_number'] ?? 'unknown',
                        'name' => $voterData['name'] ?? 'unknown'
                    ]);
                    
                    // Assign the extracted serial
                    if ($extractedSerial !== null) {
                        $voterData['serial_number'] = $extractedSerial;
                    }
                    
                    // Overwrite voter_id_number with positional pick when available
                    if ($positionalVoterId) {
                        $voterData['voter_id_number'] = $positionalVoterId;
                    }

                    if ($voterData && !empty($voterData['name']) && !empty($voterData['voter_id_number'])) {
                        // Do not discard based on raw serial line content; keep all valid voter boxes
                        // Attach section street info to each voter on this page
                        $voterData['street_id'] = $sectionStreetId;
                        $voterData['street_name'] = $sectionStreetName;
                        $cols = $cropOptions['cols'] ?? 3;
                        $rows = $cropOptions['rows'] ?? 10;
                        $gridSerial = (($result->row - 1) * $cols) + $result->col; // 1..(cols*rows)
                        $globalOffsetBase = $pageIndex * ($cols * $rows);
                        $fallbackSerial = $globalOffsetBase + $gridSerial; // continuous across pages
                        // Finalize serial_number: prefer positional top-left pick, then text heuristic; avoid auto-generation
                        // Leave null if not confidently parsed.
                        if (!empty($voterData['serial_number'])) {
                            // keep what we parsed (positional or text)
                        } else {
                            // no serial parsed; warn and leave null
                            Log::warning('Missing serial number', [
                                'page' => $pageIndex,
                                'row' => $result->row,
                                'col' => $result->col,
                                'expected_fallback' => $fallbackSerial,
                                'voter_id' => $voterData['voter_id_number'],
                                'name' => $voterData['name'],
                                'first_text_lines' => implode(' | ', array_slice(explode("\n", $result->text), 0, 3))
                            ]);
                            $voterData['serial_number'] = null;
                        }
                        
                        // Check for duplicate serials in current page batch (only if serial exists)
                        if (!empty($voterData['serial_number'])) {
                            $existingDuplicate = array_filter($pageVoters, function($v) use ($voterData) {
                                return isset($v['serial_number']) && $v['serial_number'] === $voterData['serial_number'];
                            });
                            if (!empty($existingDuplicate)) {
                                $duplicate = reset($existingDuplicate);
                                Log::warning('Duplicate serial number detected in page batch', [
                                    'serial' => $voterData['serial_number'],
                                    'page' => $pageIndex,
                                    'existing_voter' => ['id' => $duplicate['voter_id_number'], 'name' => $duplicate['name']],
                                    'new_voter' => ['id' => $voterData['voter_id_number'], 'name' => $voterData['name']],
                                    'row' => $result->row,
                                    'col' => $result->col
                                ]);
                                // Don't nullify - let database handle uniqueness; just log warning
                            }
                        }
                        
                        Log::debug('Serial assignment', [
                            'page_index' => $pageIndex,
                            'row' => $result->row,
                            'col' => $result->col,
                            'parsed_serial' => $voterData['serial_number'],
                            'fallback_serial' => $fallbackSerial,
                            'final_serial' => $voterData['serial_number'],
                            'voter_id' => $voterData['voter_id_number'],
                            'name' => $voterData['name']
                        ]);
                        // Check for null/empty fields and add to warning_data (for reporting only)
                        $nullFields = [];
                        $fieldsToCheck = ['serial_number', 'house_number', 'age', 'relation_name'];
                        foreach ($fieldsToCheck as $field) {
                            if (empty($voterData[$field])) {
                                $nullFields[] = $field;
                            }
                        }
                        
                        // Log warnings but don't prevent import
                        if (!empty($nullFields)) {
                            $this->results['warning_data'][] = [
                                'page' => basename($imagePath),
                                'row' => $result->row,
                                'col' => $result->col,
                                'position' => "Row {$result->row}, Col {$result->col}",
                                'voter_data' => [
                                    'serial_number' => $voterData['serial_number'] ?? null,
                                    'voter_id_number' => $voterData['voter_id_number'],
                                    'name' => $voterData['name'],
                                    'relation_type' => $voterData['relation_type'] ?? null,
                                    'relation_name' => $voterData['relation_name'] ?? null,
                                    'house_number' => $voterData['house_number'] ?? null,
                                    'age' => $voterData['age'] ?? null,
                                    'gender' => $voterData['gender'] ?? null,
                                ],
                                'null_fields' => $nullFields,
                                'reason' => 'Warning: ' . implode(', ', $nullFields) . ' is/are null or empty (imported with warnings)'
                            ];
                            Log::debug('Voter imported with incomplete data', [
                                'voter_id' => $voterData['voter_id_number'],
                                'null_fields' => $nullFields
                            ]);
                        }
                        
                        $pageVoters[] = $voterData;
                    } else {
                        // Determine specific reason for skipping
                        $skipReason = [];
                        if (empty($voterData['name'])) {
                            $skipReason[] = 'name is missing';
                        }
                        if (empty($voterData['voter_id_number'])) {
                            $skipReason[] = 'voter_id_number is missing';
                        }
                        
                        $skippedVoterInfo = [
                            'page' => basename($imagePath),
                            'row' => $result->row,
                            'col' => $result->col,
                            'position' => "Row {$result->row}, Col {$result->col}",
                            'voter_data' => [
                                'serial_number' => $voterData['serial_number'] ?? null,
                                'voter_id_number' => $voterData['voter_id_number'] ?? null,
                                'name' => $voterData['name'] ?? null,
                                'relation_type' => $voterData['relation_type'] ?? null,
                                'relation_name' => $voterData['relation_name'] ?? null,
                                'house_number' => $voterData['house_number'] ?? null,
                                'age' => $voterData['age'] ?? null,
                                'gender' => $voterData['gender'] ?? null,
                            ],
                            'reason' => 'Skipped: ' . implode(' and ', $skipReason)
                        ];
                        
                        $this->results['skipped_voters'][] = $skippedVoterInfo;
                        
                        Log::warning("Skipping crop due to missing name or voter ID.", [
                            'row' => $result->row, 
                            'col' => $result->col,
                            'parsed_data' => $voterData,
                            'reason' => implode(' and ', $skipReason)
                        ]);
                        $this->results['total_skipped']++;
                    }
                    // Clean up temporary crop file unless keep_crops is enabled
                    if (!(bool)($this->options['keep_crops'] ?? false)) {
                        @unlink($result->path);
                    }
                }
                $this->persistVoters($pageVoters, $boothId, $boothNumber);
                
                // Process deleted voters - remove from database if they exist
                $this->handleDeletedVoters($deletedRecords, $boothId);
                
                // Append per-page file summary including deleted records
                $this->results['voters_files'][] = [
                    'file' => basename($imagePath),
                    'Imported Count' => count($pageVoters),
                    'Deleted Record' => $deletedRecords,
                ];

            } else {
                // Fallback to full page OCR
                $text = $ocrService->extractText($imagePath);
                $voters = $this->parseVotersFromText($text);
                $this->persistVoters($voters, $boothId, $boothNumber);
                $this->results['voters_files'][] = [
                    'file' => basename($imagePath),
                    'Imported Count' => count($voters),
                    'Deleted Record' => [],
                ];
            }
            $pageIndex++;
        }
        
        // Generate Excel reports for skipped voters and warnings
        $this->generateExcelReports();
        
        Log::info('Finished voter batch import', $this->results);
        return $this->results;
    }

    /**
     * Persist voters to database - handles both CREATE and UPDATE operations
     * 
     * @param array $voters Array of voter data to persist
     * @param int|null $boothId Booth ID
     * @param string|null $boothNumber Booth number
     * @return void
     */
    protected function persistVoters(array $voters, ?int $boothId, ?string $boothNumber): void
    {
        if (!$boothId) {
            Log::warning('Cannot persist voters without a booth ID.');
            $this->results['total_skipped'] += count($voters);
            return;
        }

        foreach ($voters as $voterData) {
            try {
                // Calculate year_of_birth: currentYear - age, with fallback to current year if age missing
                $age = isset($voterData['age']) ? intval($voterData['age']) : 0;
                $currentYear = intval(date('Y'));
                if ($age > 0 && $age < 120) {
                    $yearOfBirth = $currentYear - $age;
                } else {
                    // Fallback: if age is missing or invalid, set year_of_birth to current year to satisfy DB constraint
                    $yearOfBirth = $currentYear;
                    $age = 0;
                }
                
                // Find existing voter by voter_id_number and booth_id
                $existingVoter = Voter::where('booth_id', $boothId)
                    ->where('voter_id_number', $voterData['voter_id_number'])
                    ->first();
                
                $updateData = [
                    'serial_number' => $voterData['serial_number'],
                    'booth_number' => $boothNumber,
                    'street_id' => $voterData['street_id'] ?? null,
                    'street_name' => $voterData['street_name'] ?? null,
                    'name' => $voterData['name'],
                    'relation_name' => $voterData['relation_name'],
                    'relation_type' => $voterData['relation_type'],
                    'house_number' => $voterData['house_number'],
                    'age' => $voterData['age'],
                    'year_of_birth' => $yearOfBirth,
                    'gender' => $voterData['gender'],
                    'is_deleted' => false, // Ensure is_deleted is false for active voters
                ];
                
                if ($existingVoter) {
                    // UPDATE operation
                    $existingVoter->update($updateData);
                    $this->results['total_updated']++;
                    
                    Log::info('Updated voter', [
                        'voter_id' => $voterData['voter_id_number'],
                        'serial_number' => $voterData['serial_number'],
                        'name' => $voterData['name']
                    ]);
                } else {
                    // CREATE operation
                    $updateData['booth_id'] = $boothId;
                    $updateData['voter_id_number'] = $voterData['voter_id_number'];
                    Voter::create($updateData);
                    $this->results['total_imported']++;
                    
                    Log::info('Created new voter', [
                        'voter_id' => $voterData['voter_id_number'],
                        'serial_number' => $voterData['serial_number'],
                        'name' => $voterData['name']
                    ]);
                }

            } catch (\Exception $e) {
                $this->results['warning_data'][] = [
                    'voter_data' => [
                        'serial_number' => $voterData['serial_number'] ?? null,
                        'voter_id_number' => $voterData['voter_id_number'] ?? null,
                        'name' => $voterData['name'] ?? null,
                        'relation_type' => $voterData['relation_type'] ?? null,
                        'relation_name' => $voterData['relation_name'] ?? null,
                        'house_number' => $voterData['house_number'] ?? null,
                        'age' => $voterData['age'] ?? null,
                        'gender' => $voterData['gender'] ?? null,
                    ],
                    'reason' => 'Database error: ' . $e->getMessage(),
                    'error_type' => 'persist_failed'
                ];
                
                Log::error('Failed to persist voter', [
                    'serial_number' => $voterData['serial_number'] ?? 'N/A',
                    'error' => $e->getMessage()
                ]);
                $this->results['total_skipped']++;
            }
        }
    }
    
    /**
     * Handle deleted voters - DELETE operation
     * If a voter is marked as deleted in the image and exists in database, remove them
     * This method is separate to allow for future enhancements like soft delete, archival, etc.
     * 
     * @param array $deletedRecords Array of deleted voter records from OCR
     * @param int|null $boothId Booth ID
     * @return void
     */
    protected function handleDeletedVoters(array $deletedRecords, ?int $boothId): void
    {
        if (!$boothId || empty($deletedRecords)) {
            return;
        }
        
        foreach ($deletedRecords as $deletedVoter) {
            try {
                $voterId = $deletedVoter['voter_id_number'] ?? null;
                
                if (!$voterId) {
                    Log::warning('Deleted voter has no voter_id_number, skipping delete operation', [
                        'serial_number' => $deletedVoter['serial_number'] ?? 'N/A',
                        'name' => $deletedVoter['name'] ?? 'N/A'
                    ]);
                    continue;
                }
                
                // Find voter in database by voter_id_number and booth_id
                $existingVoter = Voter::where('booth_id', $boothId)
                    ->where('voter_id_number', $voterId)
                    ->first();
                
                if ($existingVoter) {
                    // DELETE operation - permanently remove from database
                    $voterInfo = [
                        'id' => $existingVoter->id,
                        'voter_id' => $existingVoter->voter_id_number,
                        'serial_number' => $existingVoter->serial_number,
                        'name' => $existingVoter->name,
                    ];
                    
                    $existingVoter->delete();
                    $this->results['total_deleted']++;
                    
                    Log::info('Deleted voter from database', $voterInfo);
                    
                    // Future enhancement: Could add soft delete, move to archive table, etc.
                    // Example: $existingVoter->update(['is_deleted' => true, 'deleted_at' => now()]);
                } else {
                    Log::debug('Deleted voter not found in database, no action needed', [
                        'voter_id' => $voterId,
                        'serial_number' => $deletedVoter['serial_number'] ?? 'N/A',
                        'name' => $deletedVoter['name'] ?? 'N/A'
                    ]);
                }
                
            } catch (\Exception $e) {
                Log::error('Failed to delete voter', [
                    'voter_id' => $deletedVoter['voter_id_number'] ?? 'N/A',
                    'serial_number' => $deletedVoter['serial_number'] ?? 'N/A',
                    'error' => $e->getMessage()
                ]);
                
                // Don't add to warning_data as these are expected deleted records
            }
        }
    }

    protected function extractBoothDetails(string $imagePath): array
    {
        try {
            $text = (new TesseractOCR($imagePath))->lang('eng')->psm(4)->run();
            Log::debug('Booth info OCR text', ['text' => $text]);
            
            // Try a few different patterns to find the booth number.
            $patterns = [
                '/Part\s+No\.\s*:\s*(\d+)/i',
                '/Part\s+Number\s*:\s*(\d+)/i',
                '/No\.\s+and\s+Name\s+of\s+Polling\s+Station\s*:\s*(\d+)/i',
                '/pareno\.?\s*:\s*(\d+)/i',  // Match "pareno.:11" format
                '/part\s*no\.?\s*:\s*(\d+)/i'  // Relaxed part number match
            ];

            foreach ($patterns as $pattern) {
                if (preg_match($pattern, $text, $matches)) {
                    Log::info('Found booth number using pattern.', ['pattern' => $pattern, 'booth_number' => $matches[1]]);
                    $boothNumber = $matches[1];
                    // Extract Address of Polling Station (located below the label, possibly multi-line)
                    $address = null;
                    // Pattern: "Address of Polling Station :" followed by content on next lines until section "4. NUMBER OF ELECTORS"
                    if (preg_match('/Address\s+of\s+Polling\s+Station\s*:\s*(.+?)(?=\n\s*4\.\s*NUMBER\s+OF\s+ELECTORS|\Z)/is', $text, $am)) {
                        $rawAddress = trim($am[1]);
                        // Split into lines and clean
                        $addressLines = preg_split('/\n+/', $rawAddress);
                        $addressLines = array_map('trim', $addressLines);
                        // Filter out empty lines, section headers, and "Stations in this part" line
                        $addressLines = array_filter($addressLines, function($line) {
                            return !empty($line) && 
                                   !preg_match('/^(Starting|Ending|Serial No|Male|Female|Third Gender|Total|Net Electors)$/i', $line) &&
                                   !preg_match('/^\d+\s*\.\s*[A-Z]+/i', $line) &&
                                   !preg_match('/Stations\s+in\s+this\s+part/i', $line);
                        });
                        // Join with space to create full address
                        $address = implode(' ', $addressLines);
                    }
                    if ($address) {
                        // Further cleanup
                        $address = preg_replace('/\s+Total\s+Pages.*$/i', '', $address);
                        $address = preg_replace('/\s+Signature.*$/i', '', $address);
                        $address = trim($address);
                        Log::info('Extracted booth address.', ['address' => $address]);
                    } else {
                        Log::warning('Could not extract booth address from OCR text.');
                    }
                    // Extract streets under "No. and name of sections in the part"
                    $streetNames = $this->extractStreetNamesFromBoothText($text);
                    if (!empty($streetNames)) {
                        Log::info('Extracted street names from booth info.', ['count' => count($streetNames)]);
                    }
                    return [$boothNumber, $address, $streetNames];
                }
            }

            Log::warning('Could not find booth number in OCR text.', ['image' => $imagePath]);
            return [null, null, []];

        } catch (\Exception $e) {
            Log::error('Failed to extract booth number', ['error' => $e->getMessage()]);
            return [null, null, []];
        }
    }

    /**
     * Extract the voter serial number from OCR text of a single voter box.
     * Returns [serialNumber|null, rawLine|null].
     * Heuristics:
     * - Prefer a standalone 1-4 digit token appearing in the first few lines
     * - Ignore numbers when the line also contains Age or House Number labels
     * - If none found, return nulls
     */
    protected function extractSerialNumberFromText(string $text): array
    {
        $clean = preg_replace('/\r/', '', $text);
        $lines = preg_split('/\n+/', trim($clean));
        // Expand search to first 8 lines to catch edge cases where serial is lower
        $lines = array_slice($lines, 0, 8);
        foreach ($lines as $idx => $line) {
            $raw = trim($line);
            if ($raw === '') continue;
            $lower = strtolower($raw);
            // Skip lines that clearly belong to other fields
            if (str_contains($lower, 'age as on') || str_contains($lower, 'house number') || 
                str_contains($lower, 'gender') || str_contains($lower, 'part no')) {
                continue;
            }
            // Remove common box artifacts and normalize
            $candidate = preg_replace('/[\[\]\(\)\{\}|_]+/', ' ', $raw);
            $candidate = preg_replace('/\s+/', ' ', trim($candidate));
            
            // Pattern 1: Standalone digits with optional # or S prefix (S indicates deleted profile)
            if (preg_match('/^[#S]?\s*(\d{1,4})$/', $candidate, $m)) {
                return [$m[1], $raw];
            }
            // Pattern 2: Digits after colon, hash, or S
            if (preg_match('/^[:#S]\s*(\d{1,4})$/', $candidate, $m)) {
                return [$m[1], $raw];
            }
            // Pattern 3: Digits with surrounding whitespace and optional S prefix
            if (preg_match('/^\s*[#S]?\s*(\d{1,4})\s*$/', $candidate, $m)) {
                return [$m[1], $raw];
            }
            // Pattern 4: Serial number label followed by number
            if (preg_match('/(?:serial|sl|s\.?n\.?|no\.?)\s*[:#]?\s*(\d{1,4})/i', $candidate, $m)) {
                return [$m[1], $raw];
            }
            // Pattern 5: Standalone 1-4 digit number on its own line (must be in first 3 lines)
            if ($idx < 3 && preg_match('/^\D*?(\d{1,4})\D*?$/', $candidate, $m)) {
                // Verify it's not age (typically 18-100)
                $num = intval($m[1]);
                if ($num > 0 && $num <= 300) { // serial numbers typically under 300 per page
                    return [$m[1], $raw];
                }
            }
        }
        // No confident parse from header lines
        return [null, null];
    }

    /**
     * Extract street names from the booth info OCR text.
     * Looks for the block starting with "No. and name of sections in the part"
     * and parses lines like "1-XYZ STREET, ..." -> returns unique street name tokens before the comma.
     */
    protected function extractStreetNamesFromBoothText(string $text): array
    {
        $streets = [];
        // Normalize whitespace
        $clean = preg_replace('/\r/', '', $text);
        $lines = preg_split('/\n+/', $clean);
        $capture = false;
        foreach ($lines as $line) {
            $l = trim($line);
            if ($l === '') continue;
            // Start capture when header line is encountered
            if (preg_match('/No\.\s*\.?.*name\s+of\s+sections\s+in\s+the\s+part/i', $l)) {
                $capture = true; 
                continue;
            }
            if ($capture) {
                // Stop capture when we reach the next section header or an empty separator
                if (preg_match('/^\d+\s*\.|^3\./', $l) || preg_match('/^Main\s+Town|^Post\s+Office|^Police\s+Station|^District|^Pin\s+code/i', $l)) {
                    // ignore numeric headers or right column labels
                    // continue scanning
                }
                // Match lines starting with index like "1-", "2-" etc.
                if (preg_match('/^\d+\s*-\s*(.+)$/', $l, $m)) {
                    $full = trim($m[1]);
                    // The street name is typically before the first comma
                    $name = trim(preg_split('/\s*,\s*/', $full)[0]);
                    // Normalize case and spacing
                    $name = preg_replace('/\s+/', ' ', $name);
                    if ($name && !in_array($name, $streets, true)) {
                        $streets[] = $name;
                    }
                } else {
                    // If we encounter a new major section header, stop
                    if (preg_match('/^\d+\.\s*Details|^3\.\s*Polling\s+station\s+details|^4\.\s*NUMBER\s+OF\s+ELECTORS/i', $l)) {
                        break;
                    }
                }
            }
        }
        return $streets;
    }

    protected function parseVotersFromText(string $text): array
    {
        // This is a fallback and currently not the focus.
        // A proper implementation would parse the full text block.
        Log::warning('Full page parsing is not fully implemented. Only cropped imports are reliable.');
        return [];
    }

    protected function currentBoothNumber(): ?string
    {
        // In a real app, this might come from the session or a user setting.
        // For this job, we rely on the extracted number.
        return $this->results['booth']['booth_number'] ?? null;
    }

    protected function currentBoothId(?string $boothNumber): ?int
    {
        if (!$boothNumber) return null;
        $booth = Booth::where('booth_number', $boothNumber)->first();
        return $booth ? $booth->id : null;
    }

    /**
     * Attempt to resolve a street by base name (token before first comma).
     * Performs case-insensitive and normalized comparisons.
     * Returns [id|null, canonicalName|null].
     */
    protected function resolveStreetByBaseName(string $baseName): array
    {
        $candidate = $this->normalizeStreet($baseName);
        // Load small list of streets and try normalized match
        $all = Street::all(['id','street_name']);
        foreach ($all as $s) {
            if ($this->normalizeStreet($s->street_name) === $candidate) {
                return [$s->id, $s->street_name];
            }
        }
        // Try a loose contains match (handles small OCR variations)
        foreach ($all as $s) {
            $normDb = $this->normalizeStreet($s->street_name);
            if (str_contains($candidate, $normDb) || str_contains($normDb, $candidate)) {
                return [$s->id, $s->street_name];
            }
        }
        return [null, null];
    }

    protected function normalizeStreet(string $name): string
    {
        $name = strtoupper($name);
        // Replace multiple spaces, drop punctuation
        $name = preg_replace('/[^A-Z0-9\s]/', '', $name);
        $name = preg_replace('/\s+/', ' ', trim($name));
        return $name;
    }

    /**
     * Check if a voter profile is marked as DELETED.
     * Detection criteria (OR condition - any one match indicates deleted):
     * 1. Serial number starts with standalone 'S' character (primary detection)
     * 2. OCR text contains "DELETED" or partial matches (secondary, less reliable due to 45° angle)
     * 
     * @param string $text OCR text from the voter card crop
     * @return bool True if voter is deleted, false otherwise
     */
    protected function isVoterDeleted(string $text): bool
    {
        // Normalize text for detection
        $normalizedText = strtoupper(trim($text));
        
        // Split by newlines and check first few lines
        $lines = preg_split('/\n+/', $normalizedText);
        $lines = array_map('trim', array_filter($lines));
        
        // PRIMARY DETECTION: 'S' prefix pattern (most reliable)
        // Pattern 1: Check if first line is standalone 'S'
        if (isset($lines[0]) && $lines[0] === 'S') {
            // Check if second line contains serial number (1-4 digits)
            if (isset($lines[1]) && preg_match('/^\d{1,4}$/', $lines[1])) {
                return true;
            }
        }
        
        // Pattern 2: 'S' followed by number on same line (with space)
        foreach (array_slice($lines, 0, 5) as $line) {
            if (preg_match('/^S\s+\d{1,4}$/', $line)) {
                return true;
            }
        }
        
        // Pattern 3: Check if any of first 3 lines is just 'S'
        foreach (array_slice($lines, 0, 3) as $lineNum => $line) {
            if ($line === 'S') {
                // Look for serial number in next 2 lines
                for ($i = $lineNum + 1; $i < min($lineNum + 3, count($lines)); $i++) {
                    if (isset($lines[$i]) && preg_match('/^\d{1,4}$/', $lines[$i])) {
                        return true;
                    }
                }
            }
        }
        
        // SECONDARY DETECTION: "DELETED" text (less reliable, 45° angle)
        // Look for full or partial "DELETED" text
        $deletedPatterns = ['DELETED', 'DELET', 'LETED'];
        foreach ($deletedPatterns as $pattern) {
            if (str_contains($normalizedText, $pattern)) {
                return true;
            }
        }
        
        return false;
    }
    
    /**
     * Generate Excel reports for skipped voters and warnings
     * Saves files in the booth directory
     * 
     * @return void
     */
    protected function generateExcelReports(): void
    {
        try {
            $boothNumber = $this->results['booth']['booth_number'] ?? 'unknown';
            $timestamp = date('Y-m-d_His');
            
            // Generate Skipped Voters Excel if there are skipped records
            if (!empty($this->results['skipped_voters'])) {
                $skippedFile = $this->directory . "/skipped_voters_{$boothNumber}_{$timestamp}.xlsx";
                $this->createSkippedVotersExcel($skippedFile, $this->results['skipped_voters']);
                $this->results['skipped_excel_path'] = $skippedFile;
                Log::info('Generated skipped voters Excel', ['file' => $skippedFile, 'count' => count($this->results['skipped_voters'])]);
            }
            
            // Generate Warnings Excel if there are warnings
            if (!empty($this->results['warning_data'])) {
                $warningsFile = $this->directory . "/warnings_{$boothNumber}_{$timestamp}.xlsx";
                $this->createWarningsExcel($warningsFile, $this->results['warning_data']);
                $this->results['warning_excel_path'] = $warningsFile;
                Log::info('Generated warnings Excel', ['file' => $warningsFile, 'count' => count($this->results['warning_data'])]);
            }
            
        } catch (\Exception $e) {
            Log::error('Failed to generate Excel reports', [
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
        }
    }
    
    /**
     * Create Excel file for skipped voters
     * 
     * @param string $filePath Path to save the Excel file
     * @param array $skippedVoters Array of skipped voter records
     * @return void
     */
    protected function createSkippedVotersExcel(string $filePath, array $skippedVoters): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Skipped Voters');
        
        // Set column headers
        $headers = [
            'A1' => 'Page',
            'B1' => 'Position',
            'C1' => 'Row',
            'D1' => 'Col',
            'E1' => 'Serial Number',
            'F1' => 'Voter ID',
            'G1' => 'Name',
            'H1' => 'Age',
            'I1' => 'Gender',
            'J1' => 'House Number',
            'K1' => 'Relation Type',
            'L1' => 'Relation Name',
            'M1' => 'Reason'
        ];
        
        // Apply header styling
        foreach ($headers as $cell => $value) {
            $sheet->setCellValue($cell, $value);
        }
        
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => '4472C4']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
        ];
        $sheet->getStyle('A1:M1')->applyFromArray($headerStyle);
        
        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(25);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(8);
        $sheet->getColumnDimension('D')->setWidth(8);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(25);
        $sheet->getColumnDimension('H')->setWidth(8);
        $sheet->getColumnDimension('I')->setWidth(10);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getColumnDimension('L')->setWidth(25);
        $sheet->getColumnDimension('M')->setWidth(40);
        
        // Add data rows
        $row = 2;
        foreach ($skippedVoters as $voter) {
            $voterData = $voter['voter_data'] ?? [];
            
            $sheet->setCellValue("A{$row}", $voter['page'] ?? '');
            $sheet->setCellValue("B{$row}", $voter['position'] ?? '');
            $sheet->setCellValue("C{$row}", $voter['row'] ?? '');
            $sheet->setCellValue("D{$row}", $voter['col'] ?? '');
            $sheet->setCellValue("E{$row}", $voterData['serial_number'] ?? '');
            $sheet->setCellValue("F{$row}", $voterData['voter_id_number'] ?? '');
            $sheet->setCellValue("G{$row}", $voterData['name'] ?? '');
            $sheet->setCellValue("H{$row}", $voterData['age'] ?? '');
            $sheet->setCellValue("I{$row}", $voterData['gender'] ?? '');
            $sheet->setCellValue("J{$row}", $voterData['house_number'] ?? '');
            $sheet->setCellValue("K{$row}", $voterData['relation_type'] ?? '');
            $sheet->setCellValue("L{$row}", $voterData['relation_name'] ?? '');
            $sheet->setCellValue("M{$row}", $voter['reason'] ?? '');
            
            // Apply borders to data rows
            $sheet->getStyle("A{$row}:M{$row}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D0D0D0']]]
            ]);
            
            $row++;
        }
        
        // Freeze header row
        $sheet->freezePane('A2');
        
        // Save file
        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);
    }
    
    /**
     * Create Excel file for warnings
     * 
     * @param string $filePath Path to save the Excel file
     * @param array $warnings Array of warning records
     * @return void
     */
    protected function createWarningsExcel(string $filePath, array $warnings): void
    {
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('Warnings');
        
        // Set column headers
        $headers = [
            'A1' => 'Page',
            'B1' => 'Position',
            'C1' => 'Row',
            'D1' => 'Col',
            'E1' => 'Serial Number',
            'F1' => 'Voter ID',
            'G1' => 'Name',
            'H1' => 'Age',
            'I1' => 'Gender',
            'J1' => 'House Number',
            'K1' => 'Relation Type',
            'L1' => 'Relation Name',
            'M1' => 'Null Fields',
            'N1' => 'Reason'
        ];
        
        // Apply header styling
        foreach ($headers as $cell => $value) {
            $sheet->setCellValue($cell, $value);
        }
        
        $headerStyle = [
            'font' => ['bold' => true, 'color' => ['rgb' => 'FFFFFF']],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E67E22']],
            'alignment' => ['horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
        ];
        $sheet->getStyle('A1:N1')->applyFromArray($headerStyle);
        
        // Set column widths
        $sheet->getColumnDimension('A')->setWidth(25);
        $sheet->getColumnDimension('B')->setWidth(15);
        $sheet->getColumnDimension('C')->setWidth(8);
        $sheet->getColumnDimension('D')->setWidth(8);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('G')->setWidth(25);
        $sheet->getColumnDimension('H')->setWidth(8);
        $sheet->getColumnDimension('I')->setWidth(10);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getColumnDimension('L')->setWidth(25);
        $sheet->getColumnDimension('M')->setWidth(25);
        $sheet->getColumnDimension('N')->setWidth(40);
        
        // Add data rows
        $row = 2;
        foreach ($warnings as $warning) {
            $voterData = $warning['voter_data'] ?? [];
            $nullFields = $warning['null_fields'] ?? [];
            
            $sheet->setCellValue("A{$row}", $warning['page'] ?? '');
            $sheet->setCellValue("B{$row}", $warning['position'] ?? '');
            $sheet->setCellValue("C{$row}", $warning['row'] ?? '');
            $sheet->setCellValue("D{$row}", $warning['col'] ?? '');
            $sheet->setCellValue("E{$row}", $voterData['serial_number'] ?? '');
            $sheet->setCellValue("F{$row}", $voterData['voter_id_number'] ?? '');
            $sheet->setCellValue("G{$row}", $voterData['name'] ?? '');
            $sheet->setCellValue("H{$row}", $voterData['age'] ?? '');
            $sheet->setCellValue("I{$row}", $voterData['gender'] ?? '');
            $sheet->setCellValue("J{$row}", $voterData['house_number'] ?? '');
            $sheet->setCellValue("K{$row}", $voterData['relation_type'] ?? '');
            $sheet->setCellValue("L{$row}", $voterData['relation_name'] ?? '');
            $sheet->setCellValue("M{$row}", is_array($nullFields) ? implode(', ', $nullFields) : '');
            $sheet->setCellValue("N{$row}", $warning['reason'] ?? '');
            
            // Apply borders to data rows
            $sheet->getStyle("A{$row}:N{$row}")->applyFromArray([
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN, 'color' => ['rgb' => 'D0D0D0']]]
            ]);
            
            $row++;
        }
        
        // Freeze header row
        $sheet->freezePane('A2');
        
        // Save file
        $writer = new Xlsx($spreadsheet);
        $writer->save($filePath);
    }
}
