<?php

namespace App\Services;

use App\Repositories\RationCardRepository;
use App\ResponseHandler\Response;
use App\Validations\Validation;
use Illuminate\Http\Response as LaravelResponse;
use App\Models\RationCard;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use DateTime;
use DateTimeZone;
use Symfony\Component\HttpFoundation\Response as HttpFoundationResponse;
use Symfony\Component\HttpFoundation\StreamedResponse;

class RationCardService
{
    protected $rationCardRepository;
    protected $response;
    protected $validation;

    public function __construct(
        RationCardRepository $rationCardRepository,
        Response $response,
        Validation $validation
    ) {
        $this->rationCardRepository = $rationCardRepository;
        $this->response = $response;
        $this->validation = $validation;
    }


    public function createRationCard(array $data)
    {
        // Validate the ration card data
        $validationResponse = $this->validation->validationForRationCard($data);
        $responseData = $validationResponse->getData();

        if ($responseData->status !== 200) {
            return $this->response->respondWithError($responseData->message, $responseData->status);
        }

        // Check if the ration card number already exists
        $existingCard = $this->rationCardRepository->getByRationCardNumber($data['ration_card_number']);
        if ($existingCard) {
            return $this->response->respondWithError('Ration card number already exists', 409); // 409 Conflict
        }

        // Check if any of the voter IDs already exist
        if (!empty($data['voter_info_list'])) {
            foreach ($data['voter_info_list'] as $voter) {
                $existingVoter = $this->rationCardRepository->getByVoterId($voter['voter_id_number']);
                if ($existingVoter) {
                    return $this->response->respondWithError('Voter ID already exists: ' . $voter['voter_id_number'], 409); // 409 Conflict
                }
            }
        }

        // Create the Ration Card if both the ration card number and voter IDs do not exist
        $rationCard = $this->rationCardRepository->create([
            'ration_card_number' => $data['ration_card_number'],
            'address' => $data['address'],
            'other_state' => $data['other_state'] ?? false,
        ]);

        // If voter info exists, save voters
        if (!empty($data['voter_info_list'])) {
            foreach ($data['voter_info_list'] as $voter) {
                $rationCard->voters()->create([
                    'voter_id_number' => $voter['voter_id_number'],
                    'name' => $voter['name'],
                    'gender' => $voter['gender'],
                    'year_of_birth' => $voter['year_of_birth'],
                    'is_head' => $voter['is_head'],
                    'booth_id' => $voter['booth_id'] ?? null, // ✅ Added booth ID
                    'booth_number' => $voter['booth_number'],
                    'mobile_number' => $voter['mobile_number'],
                    'aadhar_number' => $voter['aadhar_number'], // ✅ Added new column
                    'street_id' => $voter['street_id'] ?? null, // ✅ Added street ID
                    'street_name' => $voter['street_name'] ?? null, // ✅ Added street name
                ]);
            }
        }

        return $rationCard
            ? $this->response->respondWithData('Ration Card created successfully', $rationCard, new LaravelResponse)
            : $this->response->respondWithError('Error creating Ration Card.');
    }

    /**
     * Create a ration card without voters
     * Useful when you want to create the card first and add voters separately
     */
    public function createRationCardOnly(array $data)
    {
        // Validate basic ration card data (without voter validation)
        if (empty($data['ration_card_number'])) {
            return $this->response->respondWithError('Ration card number is required', 422);
        }

        if (empty($data['address'])) {
            return $this->response->respondWithError('Address is required', 422);
        }

        // Check if the ration card number already exists (including soft-deleted)
        $existingCard = RationCard::where('ration_card_number', $data['ration_card_number'])->first();
        
        if ($existingCard) {
            if ($existingCard->is_deleted) {
                // Permanently delete the soft-deleted ration card and its voters
                $existingCard->voters()->forceDelete(); // Hard delete voters
                $existingCard->forceDelete(); // Hard delete ration card
            } else {
                return $this->response->respondWithError('Ration card number already exists', 409); // 409 Conflict
            }
        }

        // Create the Ration Card without voters
        $rationCard = $this->rationCardRepository->create([
            'ration_card_number' => $data['ration_card_number'],
            'address' => $data['address'],
            'other_state' => $data['other_state'] ?? false,
        ]);

        return $rationCard
            ? $this->response->respondWithData('Ration Card created successfully (without voters)', $rationCard, new LaravelResponse)
            : $this->response->respondWithError('Error creating Ration Card.');
    }

    /**
     * Update ration card data only (without affecting voters)
     * Useful when you want to update only ration card basic information
     * 
     * @param int $id
     * @param array $data
     * @return \Illuminate\Http\JsonResponse
     */
    public function updateRationCardOnly($id, array $data)
    {
        // Find the existing Ration Card
        $rationCard = $this->rationCardRepository->getById($id);
        if (!$rationCard) {
            return $this->response->respondWithError('Ration Card not found', 404);
        }

        // Validate basic ration card data (without voter validation)
        if (empty($data['ration_card_number'])) {
            return $this->response->respondWithError('Ration card number is required', 422);
        }

        if (empty($data['address'])) {
            return $this->response->respondWithError('Address is required', 422);
        }

        // Check if the ration card number already exists (for different ration card)
        if ($data['ration_card_number'] !== $rationCard->ration_card_number) {
            $existingCard = $this->rationCardRepository->getByRationCardNumber($data['ration_card_number']);
            if ($existingCard && $existingCard->id !== $id) {
                return $this->response->respondWithError('Ration card number already exists', 409);
            }
        }

        // Update only the Ration Card basic fields (without touching voters)
        $updated = $rationCard->update([
            'ration_card_number' => $data['ration_card_number'],
            'address' => $data['address'],
            'other_state' => $data['other_state'] ?? $rationCard->other_state,
        ]);

        if ($updated) {
            // Refresh the model to get updated data
            $rationCard->refresh();
            return $this->response->respondWithData('Ration Card updated successfully (voters unchanged)', $rationCard, new LaravelResponse);
        } else {
            return $this->response->respondWithError('Error updating Ration Card.');
        }
    }

    // Update Ration Card and Voter Info
    public function updateRationCard($id, array $data)
    {
        // Find the existing Ration Card
        $rationCard = $this->rationCardRepository->getById($id);
        if (!$rationCard) {
            return $this->response->respondWithError('Ration Card not found', 404);
        }

        // Validate the ration card data
        $validationResponse = $this->validation->validationForRationCard($data);
        $responseData = $validationResponse->getData();

        if ($responseData->status !== 200) {
            return $this->response->respondWithError($responseData->message, $responseData->status);
        }

        // Update the Ration Card fields (Address, Ration Card Number, etc.)
        $rationCard->update([
            'ration_card_number' => $data['ration_card_number'] ?? $rationCard->ration_card_number,
            'address' => $data['address'] ?? $rationCard->address,
            'other_state' => $data['other_state'] ?? false,
        ]);

        // --- Handle Voter Info List ---
        $existingVoterIds = $rationCard->voters()->pluck('id')->toArray();
        $incomingVoterIds = collect($data['voter_info_list'] ?? [])
            ->pluck('id')
            ->filter() // only existing voters with ID
            ->toArray();

        // Soft delete removed voters
        $toDelete = array_diff($existingVoterIds, $incomingVoterIds);
        if (!empty($toDelete)) {
            \App\Models\Voter::whereIn('id', $toDelete)->update(['is_deleted' => true]);
        }

        // Update or create voters
        if (!empty($data['voter_info_list'])) {
            foreach ($data['voter_info_list'] as $voter) {
                if (!empty($voter['id'])) {
                    // Existing voter → update
                    $existingVoter = \App\Models\Voter::find($voter['id']);
                    if ($existingVoter) {
                        $existingVoter->update([
                            'name' => $voter['name'],
                            'gender' => $voter['gender'],
                            'year_of_birth' => $voter['year_of_birth'],
                            'is_head' => $voter['is_head'],
                            'booth_id' => $voter['booth_id'] ?? null, // ✅ Added booth ID
                            'booth_number' => $voter['booth_number'],
                            'mobile_number' => $voter['mobile_number'],
                            'aadhar_number' => $voter['aadhar_number'],
                            'street_id' => $voter['street_id'] ?? null, // ✅ Added street ID
                            'street_name' => $voter['street_name'] ?? null, // ✅ Added street name
                            'is_deleted' => false, // reactivate if previously deleted
                        ]);
                    }
                } else {
                    // New voter → create
                    $rationCard->voters()->create([
                        'voter_id_number' => $voter['voter_id_number'],
                        'name' => $voter['name'],
                        'gender' => $voter['gender'],
                        'year_of_birth' => $voter['year_of_birth'],
                        'is_head' => $voter['is_head'],
                        'booth_id' => $voter['booth_id'] ?? null, // ✅ Added booth ID
                        'booth_number' => $voter['booth_number'],
                        'mobile_number' => $voter['mobile_number'],
                        'aadhar_number' => $voter['aadhar_number'],
                        'street_id' => $voter['street_id'] ?? null, // ✅ Added street ID
                        'street_name' => $voter['street_name'] ?? null, // ✅ Added street name
                    ]);
                }
            }
        }

        return $this->response->respondWithData('Ration Card updated successfully', $rationCard, new LaravelResponse);
    }

   

    // In RationCardService.php
    public function getRationCardById($id)
    {
        $rationCard = $this->rationCardRepository->getByIdWithVoters($id);

        if (!$rationCard) {
            return null;
        }

        // Convert to array
        $data = $rationCard->toArray();

        // Rename 'voters' key → 'voter_info_list'
        if (isset($data['voters'])) {
            $data['voter_info_list'] = $data['voters'];
            unset($data['voters']);
        }

        return $data;
    }
    // In RationCardService.php
    public function getPaginatedRationCards($perPage = 10)
    {
        return $this->rationCardRepository->getPaginatedRationCards($perPage);
    }

 public function getRationCardCount()
    {
        return $this->rationCardRepository->getRationCardCount();
    }

    
    /**
     * Download all ration cards as Excel - Optimized for large datasets
     * Uses chunked processing and streaming to handle 40k+ records efficiently
     */
    public function downloadAsExcel($filters = [])
    {
        // Configuration for large dataset handling
        $chunkSize = 1000; // Process records in chunks of 1000
        $maxMemory = '256M'; // Set memory limit
        $maxExecutionTime = 600; // 10 minutes max execution time
        
        // Increase limits for large export
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $excelFileName = 'rationcards-' . $dateTime . '.xlsx';

        // Extract filter parameters  
        $boothNumber = is_array($filters) ? ($filters['boothNumber'] ?? null) : $filters;

        // Use StreamedResponse for memory efficiency
        return new StreamedResponse(function () use ($chunkSize, $excelFileName, $boothNumber) {
            // Create Excel file
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('RationCards Export');

            // Set headers
            $headers = [
                'Serial No.',
                'RationCard Number', 
                'RationCard Address',
                'Head Name',
                'Head Phone Number',
                'Other State'
            ];

            // Write headers
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimension($col)->setAutoSize(true);
                $col++;
            }

            $currentRow = 2;
            $serialNumber = 1;
            
            // Process records in chunks to avoid memory issues
            $this->rationCardRepository->getModel()
                ->where('is_deleted', false)
                ->with(['voters' => function ($query) use ($boothNumber) {
                    $query->where('is_head', true)->where('is_deleted', false);
                    if ($boothNumber !== null) {
                        $query->where('booth_number', $boothNumber);
                    }
                }])
                ->chunk($chunkSize, function ($records) use ($sheet, &$currentRow, &$serialNumber) {
                    foreach ($records as $record) {
                        // Get head voter info
                        $headVoter = $record->voters->first();
                        $headName = $headVoter ? $headVoter->name : 'N/A';
                        $headPhone = $headVoter ? $headVoter->mobile_number : 'N/A';

                        $rowData = [
                            $serialNumber,
                            $record->ration_card_number,
                            $record->address,
                            $headName,
                            $headPhone,
                            $record->other_state ? 'Yes' : 'No'
                        ];

                        // Write row data
                        $col = 'A';
                        foreach ($rowData as $value) {
                            $sheet->setCellValue($col . $currentRow, $value);
                            $sheet->getStyle($col . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                            $col++;
                        }

                        $currentRow++;
                        $serialNumber++;
                        
                        // Clear memory every 100 records
                        if ($serialNumber % 100 === 0) {
                            $sheet->garbageCollect();
                        }
                    }
                });

            // Create temporary file for streaming
            $tempFile = tempnam(sys_get_temp_dir(), 'rationcards_export_');
            
            $writer = new Xlsx($spreadsheet);
            $writer->save($tempFile);
            
            // Stream the file
            $handle = fopen($tempFile, 'r');
            while (!feof($handle)) {
                echo fread($handle, 8192); // Read in 8KB chunks
                flush(); // Ensure data is sent immediately
            }
            fclose($handle);
            
            // Clean up temporary file
            unlink($tempFile);
            
            // Clean up spreadsheet from memory
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            
        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $excelFileName . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0'
        ]);
    }

    /**
     * Download ration cards by booth number with voter counts
     * New API endpoint for booth-specific ration card export with gender counts
     */
    public function downloadRationCardsAsExcelByBooth($boothNumber)
    {
        // Configuration for large dataset handling
        $chunkSize = 1000;
        $maxMemory = '256M';
        $maxExecutionTime = 600;
        
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $excelFileName = 'rationcards-booth-' . $boothNumber . '-' . $dateTime . '.xlsx';

        return new StreamedResponse(function () use ($chunkSize, $boothNumber) {
            // Create Excel file
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('RationCards Booth ' . $boothNumber);

            // Set headers
            $headers = [
                'Serial No.',
                'Ration ID',
                'Ration Number', 
                'Address',
                'Head Name',
                'Head Phone Number',
                'Booth Number',
                'Male Voters Count',
                'Female Voters Count',
                'Total Voters Count'
            ];

            // Write headers
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimension($col)->setAutoSize(true);
                $col++;
            }

            $currentRow = 2;
            $serialNumber = 1;
            
            // Get ration cards that have voters with the specified booth number
            \App\Models\RationCard::where('is_deleted', false)
                ->whereHas('voters', function($query) use ($boothNumber) {
                    $query->where('booth_number', $boothNumber)
                          ->where('is_deleted', false);
                })
                ->with(['voters' => function ($query) use ($boothNumber) {
                    $query->where('booth_number', $boothNumber)
                          ->where('is_deleted', false);
                }])
                ->chunk($chunkSize, function ($records) use ($sheet, &$currentRow, &$serialNumber, $boothNumber) {
                    foreach ($records as $record) {
                        // Get all voters for this booth
                        $allVoters = $record->voters;
                        
                        // Get head voter info
                        $headVoter = $allVoters->where('is_head', true)->first();
                        if (!$headVoter) {
                            $headVoter = $allVoters->first();
                        }
                        $headName = $headVoter ? $headVoter->name : 'N/A';
                        $headPhone = $headVoter ? $headVoter->mobile_number : 'N/A';

                        // Count male and female voters (case-insensitive)
                        $maleCount = $allVoters->filter(function($voter) {
                            return $voter->gender && (
                                strtolower($voter->gender) === 'male' || 
                                strtolower($voter->gender) === 'm' ||
                                $voter->gender === 'ஆண்'
                            );
                        })->count();
                        
                        $femaleCount = $allVoters->filter(function($voter) {
                            return $voter->gender && (
                                strtolower($voter->gender) === 'female' || 
                                strtolower($voter->gender) === 'f' ||
                                $voter->gender === 'பெண்'
                            );
                        })->count();
                        
                        $totalCount = $allVoters->count();

                        $rowData = [
                            $serialNumber,
                            $record->id,
                            $record->ration_card_number,
                            $record->address,
                            $headName,
                            $headPhone,
                            $boothNumber,
                            $maleCount,
                            $femaleCount,
                            $totalCount
                        ];

                        // Write row data
                        $col = 'A';
                        foreach ($rowData as $value) {
                            $sheet->setCellValue($col . $currentRow, $value);
                            $sheet->getStyle($col . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                            $col++;
                        }

                        $currentRow++;
                        $serialNumber++;
                        
                        // Clear memory every 100 records
                        if ($serialNumber % 100 === 0) {
                            $sheet->garbageCollect();
                        }
                    }
                });

            // Create temporary file for streaming
            $tempFile = tempnam(sys_get_temp_dir(), 'rationcards_booth_export_');
            
            $writer = new Xlsx($spreadsheet);
            $writer->save($tempFile);
            
            // Stream the file
            $handle = fopen($tempFile, 'r');
            while (!feof($handle)) {
                echo fread($handle, 8192);
                flush();
            }
            fclose($handle);
            
            // Clean up temporary file
            unlink($tempFile);
            
            // Clean up spreadsheet from memory
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            
        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $excelFileName . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0'
        ]);
    }

     /**
     * Download all ration cards as Excel - Optimized for large datasets
     * Uses chunked processing and streaming to handle 40k+ records efficiently
     */
    public function downloadVotersAsExcel($filters = [])
    {
        // Configuration for large dataset handling
        $chunkSize = 1000; // Process records in chunks of 1000
        $maxMemory = '256M'; // Set memory limit
        $maxExecutionTime = 600; // 10 minutes max execution time
        
        // Increase limits for large export
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $excelFileName = 'rationcards-' . $dateTime . '.xlsx';

        // Extract filter parameters
        $boothNumber = $filters['boothNumber'] ?? null;
        $gender = $filters['gender'] ?? null;
        $ageFrom = $filters['age_from'] ?? null;
        $ageUntil = $filters['age_until'] ?? null;

        // Use StreamedResponse for memory efficiency
        return new StreamedResponse(function () use ($chunkSize, $excelFileName, $boothNumber, $gender, $ageFrom, $ageUntil) {
            // Create Excel file
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('RationCards Export');

            // Set headers
            $headers = [
                'Serial No.',
                'Voter Id Number', 
                'Name',
                'Head',
                'Mobile Number',
                'Aadhar Number',
                'Gender',
                'Year Of Birth',
                'Age',
                'Other State',
                'Booth Number',
                'Street ID',
                'Street Name',
                'Address',
                'Ration Card Number'
            ];

            // Write headers
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimension($col)->setAutoSize(true);
                $col++;
            }

            $currentRow = 2;
            $serialNumber = 1;
            
            // Process voters directly so both mapped and unmapped voters are exported
            \App\Models\Voter::where('is_deleted', false)
                ->with(['rationCard' => function ($query) {
                    $query->where('is_deleted', false);
                }])
                ->where(function($query) use ($boothNumber, $gender, $ageFrom, $ageUntil) {
                    // Filter by booth number if provided
                    if ($boothNumber !== null) {
                        $query->where('booth_number', $boothNumber);
                    }

                    // Filter by gender if provided
                    if ($gender !== null) {
                        $query->where('gender', $gender);
                    }

                    // Filter by age range if both age_from and age_until are provided
                    if ($ageFrom !== null && $ageUntil !== null) {
                        $currentYear = date('Y');
                        $birthYearFrom = $currentYear - $ageUntil; // For older people (higher age)
                        $birthYearUntil = $currentYear - $ageFrom; // For younger people (lower age)
                        $query->whereBetween('year_of_birth', [$birthYearFrom, $birthYearUntil]);
                    }
                })
                ->chunk($chunkSize, function ($voters) use ($sheet, &$currentRow, &$serialNumber) {
                    foreach ($voters as $voter) {
                        $rationCard = $voter->rationCard;

                        $rowData = [
                            $serialNumber,
                            $voter->voter_id_number,
                            $voter->name,
                            $voter->is_head ? 'Yes' : 'No',
                            $voter->mobile_number,
                            $voter->aadhar_number,
                            $voter->gender,
                            $voter->year_of_birth,
                            (date('Y') - $voter->year_of_birth) ?? 'N/A',
                            $rationCard ? ($rationCard->other_state ? 'Yes' : 'No') : 'N/A',
                            $voter->booth_number,
                            $voter->street_id ?? 'N/A',
                            $voter->street_name ?? 'N/A',
                            $rationCard ? $rationCard->address : 'N/A',
                            $rationCard ? $rationCard->ration_card_number : 'N/A',
                        ];

                        // Write row data
                        $col = 'A';
                        foreach ($rowData as $value) {
                            $sheet->setCellValue($col . $currentRow, $value);
                            $sheet->getStyle($col . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                            $col++;
                        }

                        $currentRow++;
                        $serialNumber++;

                        // Clear memory every 100 records
                        if ($serialNumber % 100 === 0) {
                            $sheet->garbageCollect();
                        }
                    }
                });

            // Create temporary file for streaming
            $tempFile = tempnam(sys_get_temp_dir(), 'rationcards_export_');
            
            $writer = new Xlsx($spreadsheet);
            $writer->save($tempFile);
            
            // Stream the file
            $handle = fopen($tempFile, 'r');
            while (!feof($handle)) {
                echo fread($handle, 8192); // Read in 8KB chunks
                flush(); // Ensure data is sent immediately
            }
            fclose($handle);
            
            // Clean up temporary file
            unlink($tempFile);
            
            // Clean up spreadsheet from memory
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            
        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $excelFileName . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0'
        ]);
    }

    /**
     * Download unmapped voters (voters without ration_card_id) as Excel
     * Filters by booth number and other criteria
     */
    public function downloadUnmappedVotersAsExcel($filters = [])
    {
        // Configuration for large dataset handling
        $chunkSize = 1000; // Process records in chunks of 1000
        $maxMemory = '256M'; // Set memory limit
        $maxExecutionTime = 600; // 10 minutes max execution time
        
        // Increase limits for large export
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $excelFileName = 'unmapped-voters-' . $dateTime . '.xlsx';

        // Extract filter parameters
        $boothNumber = $filters['boothNumber'] ?? null;
        $gender = $filters['gender'] ?? null;
        $ageFrom = $filters['age_from'] ?? null;
        $ageUntil = $filters['age_until'] ?? null;

        // Use StreamedResponse for memory efficiency
        return new StreamedResponse(function () use ($chunkSize, $excelFileName, $boothNumber, $gender, $ageFrom, $ageUntil) {
            // Create Excel file
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Unmapped Voters');

            // Set headers
            $headers = [
                'Serial No.',
                'Voter Id Number', 
                'Name',
                'Head',
                'Mobile Number',
                'Aadhar Number',
                'Gender',
                'Year Of Birth',
                'Age',
                'Booth Number',
                'Street ID',
                'Street Name'
            ];

            // Write headers
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimension($col)->setAutoSize(true);
                $col++;
            }

            $currentRow = 2;
            $serialNumber = 1;
            
            // Process records in chunks to avoid memory issues
            // Query voters where ration_card_id is NULL
            \App\Models\Voter::where('is_deleted', false)
                ->whereNull('ration_card_id')
                ->where(function($query) use ($boothNumber, $gender, $ageFrom, $ageUntil) {
                    // Filter by booth number if provided
                    if ($boothNumber !== null) {
                        $query->where('booth_number', $boothNumber);
                    }
                    
                    // Filter by gender if provided
                    if ($gender !== null) {
                        $query->where('gender', $gender);
                    }
                    
                    // Filter by age range if both age_from and age_until are provided
                    if ($ageFrom !== null && $ageUntil !== null) {
                        $currentYear = date('Y');
                        $birthYearFrom = $currentYear - $ageUntil; // For older people (higher age)
                        $birthYearUntil = $currentYear - $ageFrom; // For younger people (lower age)
                        $query->whereBetween('year_of_birth', [$birthYearFrom, $birthYearUntil]);
                    }
                })
                ->chunk($chunkSize, function ($voters) use ($sheet, &$currentRow, &$serialNumber) {
                    foreach ($voters as $voter) {
                        $rowData = [
                            $serialNumber,
                            $voter->voter_id_number,
                            $voter->name,
                            $voter->is_head ? 'Yes' : 'No',
                            $voter->mobile_number,
                            $voter->aadhar_number,
                            $voter->gender,
                            $voter->year_of_birth,
                            (date('Y') - $voter->year_of_birth) ?? 'N/A',
                            $voter->booth_number,
                            $voter->street_id ?? 'N/A',
                            $voter->street_name ?? 'N/A'
                        ];

                        // Write row data
                        $col = 'A';
                        foreach ($rowData as $value) {
                            $sheet->setCellValue($col . $currentRow, $value);
                            $sheet->getStyle($col . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                            $col++;
                        }

                        $currentRow++;
                        $serialNumber++;
                        
                        // Clear memory every 100 records
                        if ($serialNumber % 100 === 0) {
                            $sheet->garbageCollect();
                        }
                    }
                });

            // Create temporary file for streaming
            $tempFile = tempnam(sys_get_temp_dir(), 'unmapped_voters_export_');
            
            $writer = new Xlsx($spreadsheet);
            $writer->save($tempFile);
            
            // Stream the file
            $handle = fopen($tempFile, 'r');
            while (!feof($handle)) {
                echo fread($handle, 8192); // Read in 8KB chunks
                flush(); // Ensure data is sent immediately
            }
            fclose($handle);
            
            // Clean up temporary file
            unlink($tempFile);
            
            // Clean up spreadsheet from memory
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);
            
        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $excelFileName . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0'
        ]);
    }

    /**
     * Download total unmapped voters as Excel (all booths)
     */
    public function downloadTotalUnmappedVotersAsExcel()
    {
        return $this->downloadUnmappedVotersAsExcel([]);
    }

    /**
     * Download mapped voters (voters with ration_card_id) as Excel
     * Filters by booth number and other criteria
     */
    public function downloadMappedVotersAsExcel($filters = [])
    {
        // Configuration for large dataset handling
        $chunkSize = 1000; // Process records in chunks of 1000
        $maxMemory = '256M'; // Set memory limit
        $maxExecutionTime = 600; // 10 minutes max execution time

        // Increase limits for large export
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $excelFileName = 'mapped-voters-' . $dateTime . '.xlsx';

        // Extract filter parameters
        $boothNumber = $filters['boothNumber'] ?? null;
        $gender = $filters['gender'] ?? null;
        $ageFrom = $filters['age_from'] ?? null;
        $ageUntil = $filters['age_until'] ?? null;

        // Use StreamedResponse for memory efficiency
        return new StreamedResponse(function () use ($chunkSize, $excelFileName, $boothNumber, $gender, $ageFrom, $ageUntil) {
            // Create Excel file
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Mapped Voters');

            // Set headers
            $headers = [
                'Serial No.',
                'Voter Id Number',
                'Name',
                'Head',
                'Mobile Number',
                'Aadhar Number',
                'Gender',
                'Year Of Birth',
                'Age',
                'Booth Number',
                'Street ID',
                'Street Name',
                'Ration Card Number',
                'Ration Card Address'
            ];

            // Write headers
            $col = 'A';
            foreach ($headers as $header) {
                $sheet->setCellValue($col . '1', $header);
                $sheet->getStyle($col . '1')->getFont()->setBold(true);
                $sheet->getStyle($col . '1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                $sheet->getColumnDimension($col)->setAutoSize(true);
                $col++;
            }

            $currentRow = 2;
            $serialNumber = 1;

            // Process records in chunks to avoid memory issues
            // Query voters where ration_card_id is NOT NULL
            \App\Models\Voter::where('is_deleted', false)
                ->whereNotNull('ration_card_id')
                ->with(['rationCard' => function ($query) {
                    $query->where('is_deleted', false);
                }])
                ->where(function($query) use ($boothNumber, $gender, $ageFrom, $ageUntil) {
                    // Filter by booth number if provided
                    if ($boothNumber !== null) {
                        $query->where('booth_number', $boothNumber);
                    }

                    // Filter by gender if provided
                    if ($gender !== null) {
                        $query->where('gender', $gender);
                    }

                    // Filter by age range if both age_from and age_until are provided
                    if ($ageFrom !== null && $ageUntil !== null) {
                        $currentYear = date('Y');
                        $birthYearFrom = $currentYear - $ageUntil; // For older people (higher age)
                        $birthYearUntil = $currentYear - $ageFrom; // For younger people (lower age)
                        $query->whereBetween('year_of_birth', [$birthYearFrom, $birthYearUntil]);
                    }
                })
                ->chunk($chunkSize, function ($voters) use ($sheet, &$currentRow, &$serialNumber) {
                    foreach ($voters as $voter) {
                        $rationCard = $voter->rationCard;

                        $rowData = [
                            $serialNumber,
                            $voter->voter_id_number,
                            $voter->name,
                            $voter->is_head ? 'Yes' : 'No',
                            $voter->mobile_number,
                            $voter->aadhar_number,
                            $voter->gender,
                            $voter->year_of_birth,
                            (date('Y') - $voter->year_of_birth) ?? 'N/A',
                            $voter->booth_number,
                            $voter->street_id ?? 'N/A',
                            $voter->street_name ?? 'N/A',
                            $rationCard ? $rationCard->ration_card_number : 'N/A',
                            $rationCard ? $rationCard->address : 'N/A'
                        ];

                        // Write row data
                        $col = 'A';
                        foreach ($rowData as $value) {
                            $sheet->setCellValue($col . $currentRow, $value);
                            $sheet->getStyle($col . $currentRow)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
                            $col++;
                        }

                        $currentRow++;
                        $serialNumber++;

                        // Clear memory every 100 records
                        if ($serialNumber % 100 === 0) {
                            $sheet->garbageCollect();
                        }
                    }
                });

            // Create temporary file for streaming
            $tempFile = tempnam(sys_get_temp_dir(), 'mapped_voters_export_');

            $writer = new Xlsx($spreadsheet);
            $writer->save($tempFile);

            // Stream the file
            $handle = fopen($tempFile, 'r');
            while (!feof($handle)) {
                echo fread($handle, 8192); // Read in 8KB chunks
                flush(); // Ensure data is sent immediately
            }
            fclose($handle);

            // Clean up temporary file
            unlink($tempFile);

            // Clean up spreadsheet from memory
            $spreadsheet->disconnectWorksheets();
            unset($spreadsheet);

        }, 200, [
            'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            'Content-Disposition' => 'attachment; filename="' . $excelFileName . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate',
            'Pragma' => 'no-cache',
            'Expires' => '0'
        ]);
    }

    /**
     * Alternative CSV export for very large datasets (faster than Excel)
     * Recommended for 50k+ records
     */
    public function downloadAsCSV()
    {
        $chunkSize = 2000; // Larger chunks for CSV
        $maxMemory = '128M';
        $maxExecutionTime = 300; // 5 minutes
        
        ini_set('memory_limit', $maxMemory);
        ini_set('max_execution_time', $maxExecutionTime);

        $date = new DateTime('now', new DateTimeZone('Asia/Kolkata'));
        $dateTime = $date->format('Y-m-d_H-i-s');
        $filename = 'rationcards-' . $dateTime . '.csv';

        return new StreamedResponse(function () use ($chunkSize) {
            $handle = fopen('php://output', 'w');
            
            // Write CSV headers
            fputcsv($handle, [
                'Serial No.',
                'RationCard Number',
                'RationCard Address', 
                'Head Name',
                'Head Phone Number',
                'Other State'
            ]);

            $serialNumber = 1;
            
            // Process in chunks
            $this->rationCardRepository->getModel()
                ->where('is_deleted', false)
                ->with(['voters' => function ($query) {
                    $query->where('is_head', true)->where('is_deleted', false);
                }])
                ->chunk($chunkSize, function ($records) use ($handle, &$serialNumber) {
                    foreach ($records as $record) {
                        $headVoter = $record->voters->first();
                        $headName = $headVoter ? $headVoter->name : 'N/A';
                        $headPhone = $headVoter ? $headVoter->mobile_number : 'N/A';

                        fputcsv($handle, [
                            $serialNumber,
                            $record->ration_card_number,
                            $record->address,
                            $headName,
                            $headPhone,
                            $record->other_state ?? 'N/A'
                        ]);
                        
                        $serialNumber++;
                        
                        // Flush output every 500 records
                        if ($serialNumber % 500 === 0) {
                            flush();
                        }
                    }
                });

            fclose($handle);
        }, 200, [
            'Content-Type' => 'text/csv',
            'Content-Disposition' => 'attachment; filename="' . $filename . '"',
            'Cache-Control' => 'no-store, no-cache, must-revalidate'
        ]);
    }

    /**
     * Get export progress/status (for large exports with progress tracking)
     */
    public function getExportProgress($exportId)
    {
        // This could be implemented with Redis/Cache to track progress
        $progress = cache()->get("export_progress_{$exportId}", 0);
        
        return response()->json([
            'export_id' => $exportId,
            'progress' => $progress,
            'status' => $progress >= 100 ? 'completed' : 'processing'
        ]);
    }


    public function getByRationCardNumber($number)
    {
        return $this->rationCardRepository->getByRationCardNumber($number);
    }

  // Call the method from the repository
    public function getByVoterId($voterId)
    {
        return $this->rationCardRepository->getByVoterId($voterId);
    }
      public function getByAnyValue($value)
    {
        return $this->rationCardRepository->getByAnyValue($value);
    }

    /**
     * Search by multiple criteria: ration card number, voter ID, phone number, or Aadhaar number
     * Returns standardized response with voter/ration card details
     */
    public function searchByMultipleCriteria($searchValue)
    {
        // First, try to find a voter by voter_id_number, mobile_number, or aadhar_number
        $voter = \App\Models\Voter::where('is_deleted', false)
            ->where(function($query) use ($searchValue) {
                $query->where('voter_id_number', $searchValue)
                      ->orWhere('mobile_number', $searchValue)
                      ->orWhere('aadhar_number', $searchValue);
            })
            ->first();

        // If voter found, use voter's details
        if ($voter) {
            // Get the ration card if ration_card_id exists
            $rationCard = null;
            if ($voter->ration_card_id) {
                $rationCard = \App\Models\RationCard::where('id', $voter->ration_card_id)
                    ->where('is_deleted', false)
                    ->first();
            }

            $booth = \App\Models\Booth::where('booth_number', $voter->booth_number)
                ->where('is_deleted', false)
                ->first();

            return [
                'rationCardNumber' => $rationCard ? $rationCard->ration_card_number : null,
                'voterId' => $voter->voter_id_number,
                'voterName' => $voter->name,
                'boothId' => $booth ? $booth->id : null,
                'boothNumber' => $voter->booth_number,
                'voterAddress' => $rationCard ? $rationCard->address : null,
            ];
        }

        // If not found as voter, try to find by ration card number
        $rationCard = \App\Models\RationCard::where('ration_card_number', $searchValue)
            ->where('is_deleted', false)
            ->first();

        if ($rationCard) {
            // Get the head of family (is_head = true)
            $headVoter = \App\Models\Voter::where('ration_card_id', $rationCard->id)
                ->where('is_deleted', false)
                ->where('is_head', true)
                ->first();

            // If no head voter, get first voter
            if (!$headVoter) {
                $headVoter = \App\Models\Voter::where('ration_card_id', $rationCard->id)
                    ->where('is_deleted', false)
                    ->orderBy('id')
                    ->first();
            }

            if ($headVoter) {
                $booth = \App\Models\Booth::where('booth_number', $headVoter->booth_number)
                    ->where('is_deleted', false)
                    ->first();

                return [
                    'rationCardNumber' => $rationCard->ration_card_number,
                    'voterId' => $headVoter->voter_id_number,
                    'voterName' => $rationCard->head_of_family_name ?? $headVoter->name,
                    'boothId' => $booth ? $booth->id : null,
                    'boothNumber' => $headVoter->booth_number,
                    'voterAddress' => $rationCard->address,
                ];
            }

            // If ration card exists but no voters, return ration card details only
            return [
                'rationCardNumber' => $rationCard->ration_card_number,
                'voterId' => null,
                'voterName' => $rationCard->head_of_family_name,
                'boothId' => null,
                'boothNumber' => null,
                'voterAddress' => $rationCard->address,
            ];
        }

        // Not found in any table
        return null;
    }
    

  public function deleteRationCard($rationCardNumber)
{
    // Fetch the ration card by number
    $rationCard = $this->rationCardRepository->getByRationCardNumber($rationCardNumber);
    if (!$rationCard) {
        return false; // Return false if ration card not found
    }

    // Hard delete only the ration card record
    $rationCard->delete();
    
    return true; // Return true on successful hard delete
}


}
