<?php

namespace App\Exports;

use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Events\AfterSheet;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;

class PendingClientExport extends DefaultValueBinder implements FromCollection, WithHeadings, ShouldAutoSize, WithEvents, WithCustomValueBinder
{
    public function headings(): array
    {
        $dataArray = [
            'ID',
            'Username',
            'password',
            'code',
            'Status',
            'Expire',
            'B Cycle',
            'Package',
            'Package Rate',
            'OTC',
            'POP',
            'Name',
            'Contact',
            'Father Name',
            'Mother Name',
            'NID',
            'Area',
            'Block',
            'Road Name',
            'Road No',
            'Building Name',
            'Building No',
            'Flat',
            'Box',
            'OLT/PON',
            'Latitude',
            'Longitude',
            'Cable Type',
            'C Date',
            'J Date',
            'Remarks',
            'Balance',
            'Email',
            'Mac',
            'Created By'
        ];

        if (checkSettings('package_rate_in_client_search_excel') != 'enable') {
            $dataArray = array_diff($dataArray, ['Package Rate']);
        }
        return $dataArray;
    }
    protected $data;

    function __construct($data)
    {
        $this->data = $data;
    }

    /**
     * Bind value to a cell - ensures proper UTF-8 encoding for Bengali text
     */
    public function bindValue(Cell $cell, $value)
    {
        // Ensure proper UTF-8 encoding before binding
        if (is_string($value)) {
            $value = $this->ensureUtf8($value);
        }
        return parent::bindValue($cell, $value);
    }

    /**
     * Fix encoding issues and ensure proper UTF-8 for Excel export
     * Handles UTF-8 bytes incorrectly interpreted as ISO-8859-1
     */
    private function ensureUtf8($value)
    {
        if (is_null($value)) {
            return '';
        }

        if (!is_string($value)) {
            return $value;
        }

        // Check for garbled UTF-8 pattern (like à¦"à¦¸à¦¿)
        // This pattern indicates UTF-8 bytes were read as ISO-8859-1
        // The bytes are already corrupted, so we need to fix them
        if (preg_match('/à[¦-ÿ]/', $value)) {
            // The string contains UTF-8 bytes that were interpreted as ISO-8859-1
            // We need to reinterpret the bytes as UTF-8
            // Method: Get raw bytes and treat them as UTF-8

            // Use iconv if available (most reliable for this conversion)
            if (function_exists('iconv')) {
                // Convert: treat the garbled string as ISO-8859-1, output as UTF-8
                // This reinterprets the bytes correctly
                $iconvFixed = @iconv('ISO-8859-1', 'UTF-8//IGNORE', $value);
                if ($iconvFixed !== false && mb_check_encoding($iconvFixed, 'UTF-8') && preg_match('/[\x{0980}-\x{09FF}]/u', $iconvFixed)) {
                    return $iconvFixed;
                }
            }

            // Alternative method: Try direct mb_convert_encoding
            // This treats the garbled string as ISO-8859-1 and converts to UTF-8
            $directFixed = mb_convert_encoding($value, 'UTF-8', 'ISO-8859-1');
            if (mb_check_encoding($directFixed, 'UTF-8') && preg_match('/[\x{0980}-\x{09FF}]/u', $directFixed)) {
                return $directFixed;
            }

            // Try Windows-1252 as source encoding
            $winFixed = mb_convert_encoding($value, 'UTF-8', 'Windows-1252');
            if (mb_check_encoding($winFixed, 'UTF-8') && preg_match('/[\x{0980}-\x{09FF}]/u', $winFixed)) {
                return $winFixed;
            }
        }

        // Check if already valid UTF-8 with proper Bangla characters
        if (mb_check_encoding($value, 'UTF-8')) {
            // If it contains Bangla characters, it's good
            if (preg_match('/[\x{0980}-\x{09FF}]/u', $value)) {
                return $value;
            }
        }

        // Try to detect encoding
        $detected = mb_detect_encoding($value, ['UTF-8', 'ISO-8859-1', 'Windows-1252', 'ASCII'], true);
        if ($detected && $detected !== 'UTF-8') {
            $converted = mb_convert_encoding($value, 'UTF-8', $detected);
            if (mb_check_encoding($converted, 'UTF-8')) {
                return $converted;
            }
        }

        // If it's already valid UTF-8, return as is
        if (mb_check_encoding($value, 'UTF-8')) {
            return $value;
        }

        // Last resort: return as is (might be binary or other encoding)
        return $value;
    }

    public function collection()
    {
        $items = [];
        foreach ($this->data as $client) {

            if ($client->pop->subreseller == 'yes') {
                $speed = 'Sub Pack:' . $this->ensureUtf8($client->subpack->name ?? '') . "\n";
                if (auth()->user()->can('show-mother-package')) {
                    $speed .= 'Main Pack:' . $this->ensureUtf8($client->packages->package_name ?? '');
                }
                $rate = $client->subpack->rate ?? 0;
            } else {
                $speed =  'Pack:' . $this->ensureUtf8($client->packages->package_name ?? '');
                $rate = $client->packages->package_rate ?? 0;
            }

            $client_info  = $this->ensureUtf8($client->clientsinfo->flat_no ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->building_name ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->building_no ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->road_name ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->block_sector ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->box ?? '') . " ";
            $client_info .= $this->ensureUtf8($client->clientsinfo->olt_pon ?? '') . " ";

            if (isset($client->customerAccount->dueAmount)) {
                $balance = $client->pop->bill_generate == "yes" ? "'" . (string)($client->customerAccount->dueAmount) . "'" : "'0";
            } else {
                $balance = "'0";
            }

            if(checkSettings('show_client_password_in_exports') == 'enable'){
                $password = $client->password;
            } else {
                $password = '********';
            }

            $row = [
                $client->id,
                $this->ensureUtf8($client->userid),
                $password,
                $this->ensureUtf8($client->customer_code ?? ''),
                $this->ensureUtf8($client->clients_status),
                $client->expire_date ? date('Y-m-d', strtotime($client->expire_date)) : '',
                $this->ensureUtf8($client->billing_cycle),
                $speed,
                $rate,
                $this->ensureUtf8($client->clientsinfo->otc ?? ''),
                $this->ensureUtf8($client->pop->popname ?? ''),
                $this->ensureUtf8($client->clientsinfo->clients_name ?? ''),
                $this->ensureUtf8($client->clientsinfo->contact_no ?? ''),
                $this->ensureUtf8($client->clientsinfo->father_name ?? ''),
                $this->ensureUtf8($client->clientsinfo->mother_name ?? ''),
                $this->ensureUtf8($client->clientsinfo->national_id ?? ''),
                $this->ensureUtf8($client->clientsinfo->area ?? ''),
                $this->ensureUtf8($client->clientsinfo->block_sector ?? ''),
                $this->ensureUtf8($client->clientsinfo->road_name ?? ''),
                $this->ensureUtf8($client->clientsinfo->road_no ?? ''),
                $this->ensureUtf8($client->clientsinfo->building_name ?? ''),
                $this->ensureUtf8($client->clientsinfo->building_no ?? ''),
                $this->ensureUtf8($client->clientsinfo->flat_no ?? ''),
                $this->ensureUtf8($client->clientsinfo->box ?? ''),
                $this->ensureUtf8($client->clientsinfo->olt_pon ?? ''),
                $this->ensureUtf8($client->clientsinfo->client_latitude ?? ''),
                $this->ensureUtf8($client->clientsinfo->client_longitude ?? ''),
                $this->ensureUtf8($client->clientsinfo->cable_type ?? ''),
                $client->created_at ? date('Y-m-d', strtotime($client->created_at)) : '',
                $client->clientsinfo->joinDate ? date('Y-m-d', strtotime($client->clientsinfo->joinDate)) : '',
                $this->ensureUtf8($client->clientsinfo->remarks ?? ''),
                $balance,
                $this->ensureUtf8($client->clientsinfo->email ?? ''),
                $this->ensureUtf8($client->mac ?? ''),
                $this->ensureUtf8($client->adminUser->name ?? '')
            ];

            if (checkSettings('package_rate_in_client_search_excel') != 'enable') {
                // Remove package rate column (index 8)
                unset($row[8]);
                $row = array_values($row);
            }

            $items[] = $row;
        }

        return collect($items);
    }

    public function registerEvents(): array
    {
        return [
            AfterSheet::class => function (AfterSheet $event) {
                // Get the worksheet delegate
                $sheet = $event->sheet->getDelegate();

                // Get the highest row and column
                $highestRow = $sheet->getHighestRow();
                $highestColumn = $sheet->getHighestColumn();

                // Apply font to all cells in the sheet
                if ($highestRow > 0 && $highestColumn) {
                    // Try different variations of Noto Sans Bengali font name
                    // The exact font name may vary depending on how it's installed
                    $fontName = 'Noto Sans Bengali'; // Try this first
                    // Alternative names if the above doesn't work:
                    // 'NotoSansBengali', 'Noto Sans Bengali Regular', 'NotoSansBengali-Regular'

                    $cellRange = 'A1:' . $highestColumn . $highestRow;
                    $style = $event->sheet->getStyle($cellRange);

                    // Apply font - Excel will use the font if installed, otherwise fallback to default
                    $style->applyFromArray([
                        'font' => [
                            'name' => $fontName,
                            'size' => 11,
                        ],
                    ]);

                    // Also set directly using the font object
                    $style->getFont()->setName($fontName)->setSize(11);
                }
            },
        ];
    }
}

