<?php
namespace App\Libraries;

use App\Models\BulkTemporaryParticipantModel;
use App\Models\ParticipantModel;
use App\Models\BulkTransactionModel;
use App\Models\UserModel;
use App\Models\EventPackageModel;
use App\Models\TransactionModel;
use App\Validation\CustomRules;

use Config\Services;
use PhpOffice\PhpSpreadsheet\IOFactory;
use Ramsey\Uuid\Uuid;


class BulkRegistrationService
{
    /**
     * Membaca file Excel dan memasukkan semua baris ke tabel temporer dengan status 'Processing'.
     * Dipanggil oleh controller saat file di-upload.
     *
     * @return int Jumlah baris yang berhasil dimasukkan.
     */
    public function readAndQueueParticipants(string $filePath, int $bulkTrxId, int $defaultChurchId): int
    {
        $spreadsheet = IOFactory::load($filePath);
        $sheet = $spreadsheet->getActiveSheet();
        $highestRow = $sheet->getHighestDataRow();
        $tempModel = new BulkTemporaryParticipantModel();
        
        $participantsToInsert = [];

        for ($row = 10; $row <= $highestRow; $row++) {
            $name = trim($sheet->getCell('B' . $row)->getValue() ?? '');
            $phone = trim($sheet->getCell('C' . $row)->getValue() ?? '');
            $kta = trim($sheet->getCell('D' . $row)->getValue() ?? '');

            if (empty($name) && empty($kta)) continue;

            if (!empty($phone)) {
                $numericPhone = preg_replace('/[^\d]/', '', $phone);
                
                
                if (ctype_digit($cleanedPhone) && (!isset($phone[0]) || $phone[0] !== '+')) {
                    // Cek jika diawali 0, ganti dengan 62
                    if ($cleanedPhone[0] === '0') {
                        $cleanedPhone = '62' . substr($cleanedPhone, 1);
                    }
                    $phone = '+' . $cleanedPhone;
                } 
                           

                if (ctype_digit($numericPhone) && $phone[0] !== '+') {
                    $phone = '+' . $numericPhone;
                }
            }            

            $participantsToInsert[] = [
                'bulk_transaction_id' => $bulkTrxId,
                'name'                => $name,
                'phone_number'        => $phone ?: null,
                'kta_number'          => $kta,
                'church_id'           => $defaultChurchId,
                'process_status'      => 'Processing', // Status awal untuk cron job
                'excel_row'           => $row,
            ];
        }
        
        if (!empty($participantsToInsert)) {
            $tempModel->insertBatch($participantsToInsert);
            return count($participantsToInsert);
        }
        return 0;
    }

    /**
     * Metode utama yang dipanggil oleh cron job (Spark Command).
     * Mengambil semua peserta berstatus 'Processing' dan memvalidasinya.
     */
    public function validatePendingParticipants(): array
    {
        helper('general');
        $tempModel = new BulkTemporaryParticipantModel();
        
        $pendingParticipants = $tempModel->where('process_status', 'Processing')->findAll();

        if (empty($pendingParticipants)) {
            return ['processed_count' => 0, 'valid_count' => 0, 'invalid_count' => 0];
        }

        // Kelompokkan peserta berdasarkan bulk_transaction_id
        $participantsByBulkId = [];
        foreach ($pendingParticipants as $p) {
            $participantsByBulkId[$p->bulk_transaction_id][] = $p;
        }

        $existingKtas = $this->getAllExistingKtas();
        $totalProcessed = 0; $totalValid = 0; $totalInvalid = 0;

        foreach ($participantsByBulkId as $bulkId => $participants) {
            $bulkHeader = (new BulkTransactionModel())->find($bulkId);
            if (!$bulkHeader) continue;
            
            $defaultUser = (new UserModel())->find($bulkHeader->user_id);
            if (!$defaultUser) continue;

            $ktasToValidate = [];
            foreach ($participants as $p) {
                $kta = strip_leading_zeros($p->kta_number);
                if (!empty($kta)) {
                    $ktasToValidate[$kta] = true; 
                }
            }
            $ktasToValidate = array_keys($ktasToValidate);

            $apiDataMap = [];
            if (!empty($ktasToValidate)) {
                $apiDataMap = $this->validateKtasWithBulkApi($ktasToValidate);
            }

            $batchErrors = [];
            $batchValidCount = 0;
            foreach ($participants as $p) {
                $errors = [];
                $updateData = [];
                $kta = strip_leading_zeros($p->kta_number);

                // Validasi Pra-API
                if (empty($kta)) {
                    $errors[] = 'KTA Number is empty.';
                } elseif (isset($existingKtas[$bulkHeader->event_id]) && in_array($kta, $existingKtas[$bulkHeader->event_id])) {
                    $errors[] = "KTA Number ({$kta}) is already registered for this event.";
                }

                //if (empty($p->name) || !preg_match('/^[a-zA-Z\s\.\']+$/', $p->name)) { $errors[] = 'Name is required and must be letters and spaces only.'; }
                //if (!empty($p->phone_number) && !preg_match('/^\+[1-9]\d{1,14}$/', $p->phone_number)) { $errors[] = 'Phone format is invalid.'; }

                // Validasi Hasil API
                if (empty($errors)) {
                    if (!isset($apiDataMap[$kta])) {
                        $errors[] = 'KTA not found in API.';
                    } else {
                        $apiResult = $apiDataMap[$kta];
                        if (empty($apiResult['tgl_lahir'])) $errors[] = 'Date of Birth not found in Membership.';
                        if (empty($apiResult['jenis'])) $errors[] = 'Gender not found in Membership.';
                        
                        if (empty($errors)) {
                            $updateData['birth_date'] = date('Y-m-d', strtotime($apiResult['tgl_lahir']));
                            $gender = strtolower($apiResult['jenis'] ?? '');
                            $updateData['gender'] = (in_array($gender, ['pria', 'laki-laki']))
                                ? 'Male'
                                : 'Female';
                            
                            $emailFromApi = $this->cleanEmail($apiResult['email'] ?? null);
                            $updateData['email'] = $emailFromApi ?: ($defaultUser ? $defaultUser->email : null);
                            
                            $phoneFromExcel = $this->cleanPhoneNumber($p->phone_number);
                            $phoneFromApi = $this->cleanPhoneNumber($apiResult['hp'] ?? null);
                            log_message('error', 'Excel: ' .$p->name . $phoneFromExcel);
                            log_message('error', 'Cleaned: ' .$p->name . $phoneFromApi);
                            $updateData['phone_number'] = $phoneFromExcel ?: $phoneFromApi ?: ($defaultUser ? $defaultUser->phone_number : null);
                        }
                    }
                }
                
                // Update status
                if (empty($errors)) {
                    $updateData['process_status'] = 'Valid';
                    $batchValidCount++;
                } else {
                    $updateData['process_status'] = 'Invalid';
                    $errorString = "Row " . ($p->excel_row) . ": " . implode(', ', $errors);
                    $updateData['process_result'] = $errorString;
                    $batchErrors[] = $errorString;
                }
                $tempModel->update($p->id, $updateData);
            }
            
            (new BulkTransactionModel())->update($bulkId, [
                'total_quantity' => $batchValidCount,
                'process_status' => 'Validated',
                'process_result' => json_encode($batchErrors),
            ]);

            $totalProcessed += count($participants);
            $totalValid += $batchValidCount;
            $totalInvalid += count($batchErrors);
        }

        return ['processed_count' => $totalProcessed, 'valid_count' => $totalValid, 'invalid_count' => $totalInvalid];
    }

    private function validateKtasWithBulkApi(array $ktaNumbers): array
    {
        try {
            $client = Services::curlrequest(['timeout' => 30, 'verify' => false]);
            $response = $client->request('POST', 'https://appsapi.gbika.net/get_personal_bulk', [
                'json' => [
                    'username' => getenv('api.gbika.username'),
                    'password' => getenv('api.gbika.password'),
                    'nokta'    => $ktaNumbers,
                ]
            ]);
            
            $apiData = json_decode($response->getBody(), true);

            // Jika respons tidak valid atau tidak ada data, kembalikan array kosong
            if (empty($apiData['data']) || !is_array($apiData['data'])) {
                return [];
            }
            
            // Susun ulang array agar key-nya adalah nomor KTA
            $resultMap = [];
            foreach ($apiData['data'] as $item) {
                if (isset($item['nokta'])) {
                    $cleanedKta = strip_leading_zeros($item['nokta']);
                    $resultMap[$cleanedKta] = $item;
                }
            }
            return $resultMap;

        } catch (\Exception $e) {
            log_message('error', 'Bulk API call failed: ' . $e->getMessage());
            return []; // Kembalikan array kosong jika API gagal total
        }
    }

    /**
     * Memanggil API GBIKA untuk validasi KTA dan mengambil data.
     */
    private function validateKtaWithApi(string $ktaNumber): array
    {
        try {
            $client = Services::curlrequest(['timeout' => 10, 'verify' => false]);
            $response = $client->request('POST', getenv('api.gbika.url'), [
                'form_params' => [
                    'username' => getenv('api.gbika.username'),
                    'password' => getenv('api.gbika.password'),
                    'nokta'    => $ktaNumber,
                ]
            ]);
            $apiData = json_decode($response->getBody(), true);

            if (empty($apiData['data'])) {
                return ['error' => 'KTA not found in API.'];
            }
            return $apiData['data'];
        } catch (\Exception $e) {
            log_message('error', 'API call failed for KTA ' . $ktaNumber . ': ' . $e->getMessage());
            return ['error' => 'API call failed.'];
        }
    }

    public function processValidatedParticipants(int $bulkTrxId): array
    {
        $db = \Config\Database::connect();
        $db->transStart();

        try {
            $bulkTrxModel = new \App\Models\BulkTransactionModel();
            $tempPartModel = new \App\Models\BulkTemporaryParticipantModel();
            
            // 1. Kunci dan ambil data header upload massal
            $sql = "SELECT * FROM bulk_transactions WHERE id = ? FOR UPDATE";
            $bulkHeader = $db->query($sql, [$bulkTrxId])->getRow();

            if (!$bulkHeader || $bulkHeader->process_status !== 'Validated') {
                $db->transRollback();
                return ['success' => false, 'message' => 'This bulk transaction is not ready to be processed or has been processed.'];
            }

            // 2. Ambil semua peserta yang valid
            $validParticipants = $tempPartModel->where('bulk_transaction_id', $bulkTrxId)
                                               ->where('process_status', 'Valid')
                                               ->findAll();

            if (empty($validParticipants)) {
                $db->transRollback();
                return ['success' => false, 'message' => 'No valid participants found to process.'];
            }

            $customRules = new CustomRules();
            foreach ($validParticipants as $tempP) {
                $ktaError = null;
                $dataForValidation = ['event_id' => $bulkHeader->event_id];
                
                if (!$customRules->is_unique_participant_kta($tempP->kta_number, 'event_id', $dataForValidation, $ktaError)) {
                    // Jika ada KTA yang sudah terdaftar, batalkan seluruh proses
                    $db->transRollback();
                    // Update status header menjadi 'Failed'
                    $bulkTrxModel->update($bulkTrxId, [
                        'process_status' => 'Failed',
                        'process_result' => json_encode(["Processing failed: {$ktaError} Please re-upload."]),
                    ]);
                    return ['success' => false, 'message' => "Processing failed: {$ktaError} Please re-upload the file after correction."];
                }
            }
            
            // 3. Panggil service untuk generate invoice number
            $transactionService = new TransactionService();
            $invoiceNumber =  $transactionService->generateInvoiceNumber($bulkHeader->event_id) ?: $bulkHeader->invoice_number;

            // 4. Ambil harga paket dan hitung total
            $package = (new \App\Models\EventPackageModel())->find($bulkHeader->event_package_id);
            if (!$package) {
                 $db->transRollback();
                 return ['success' => false, 'message' => 'Associated event package not found.'];
            }
            $totalQuantity = count($validParticipants);
            $subtotal = $totalQuantity * $package->price;

            // 5. Buat transaksi utama dengan status 'pending' terlebih dahulu
            $transactionModel = new \App\Models\TransactionModel();
            $transactionId = $transactionModel->insert([
                'uuid'           => \Ramsey\Uuid\Uuid::uuid4()->toString(),
                'event_id'       => $bulkHeader->event_id,
                'user_id'        => $bulkHeader->user_id,
                'invoice_number' => $invoiceNumber,
                'transaction_date' => $bulkHeader->transaction_date,
                'payment_type'   => $bulkHeader->payment_type,
                'payment_status' => 'pending', // Set ke 'pending' agar markAsPaid berfungsi
                'total_quantity' => $totalQuantity,
                'subtotal'       => $subtotal,
                'total_discount' => 0,
                'handling_fee'   => 0,
                'total_amount'   => $subtotal,
                'promotion_id'   => null,
            ]);

            if (!$transactionId) {
                $db->transRollback();
                log_message('error', '[BulkService] Failed to insert main transaction record. Errors: ' . json_encode($transactionModel->errors()));
                return ['success' => false, 'message' => 'Failed to create the main transaction record.'];
            }

            // 6. Pindahkan data peserta dari temporer ke tabel 'participants'
            $participantModel = new \App\Models\ParticipantModel();
            $participantsToInsert = [];
            foreach ($validParticipants as $tempP) {
                $participantsToInsert[] = [
                    'transaction_id'   => $transactionId,
                    'user_id'          => $bulkHeader->user_id,
                    'event_package_id' => $bulkHeader->event_package_id,
                    'church_id'        => $tempP->church_id,
                    'name'             => $tempP->name,
                    'kta_number'       => $tempP->kta_number,
                    'birth_date'       => $tempP->birth_date,
                    'phone_number'     => $tempP->phone_number,
                    'email'            => $tempP->email,
                    'gender'           => $tempP->gender,
                ];
            }
            $participantModel->insertBatch($participantsToInsert);

            // 7. Update status header upload massal menjadi 'Completed'
            $bulkTrxModel->update($bulkTrxId, ['process_status' => 'Completed']);

            $db->transComplete();
            
            if ($db->transStatus() === false) {
                return ['success' => false, 'message' => 'A database error occurred during processing.'];
            }
            
            // 8. SETELAH semua data aman di database, panggil 'markAsPaid'.
            // Ini akan mengubah status ke 'paid', membuat QR, dan mengirim email.
            $transactionService->markAsPaid($transactionId);

            return ['success' => true, 'message' => lang('Admin/BulkTransactions.processSuccess', [$totalQuantity])];

        } catch (\Exception $e) {
            if (isset($db) && $db->transStatus() !== false) $db->transRollback();
            log_message('error', '[BulkRegistrationService] Exception on processValidatedParticipants. ID: ' . $bulkTrxId . '. Error: ' . $e->getMessage());
            return ['success' => false, 'message' => 'An unexpected error occurred. Please check logs.'];
        }
    }

    private function getAllExistingKtas(): array
    {
        $participants = (new ParticipantModel())
            ->select('participants.kta_number, transactions.event_id')
            ->join('transactions', 'transactions.id = participants.transaction_id')
            ->whereIn('transactions.payment_status', ['pending', 'paid'])
            ->where('participants.kta_number IS NOT NULL AND participants.kta_number !=', '')
            ->findAll();

        $ktasByEvent = [];
        foreach ($participants as $p) {
            $ktasByEvent[$p->event_id][] = strip_leading_zeros($p->kta_number);
        }
        return $ktasByEvent;
    }
    
    private function cleanEmail(?string $email): ?string
    {
        if (empty($email)) {
            return null;
        }
        // Bersihkan spasi dan validasi format
        $cleanedEmail = trim($email);
        return filter_var($cleanedEmail, FILTER_VALIDATE_EMAIL) ? $cleanedEmail : null;
    }
    
    private function cleanPhoneNumber(?string $phone): ?string
    {
        if (empty($phone)) {
            return null;
        }
        
        // 1. Hapus semua karakter yang bukan angka
        $cleaned = preg_replace('/[^\d]/', '', $phone);
        
        // 2. Jika nomor diawali dengan 0, ganti dengan 62 (kode negara Indonesia)
        
        if (substr($cleaned, 0, 1) === '0') {
            $cleaned = '62' . substr($cleaned, 1);
        }
        
        
        // 3. Jika nomor sudah bersih dan tidak diawali '+', tambahkan.
        if (ctype_digit($cleaned)) {
            $cleaned = '+' . $cleaned;
        }

        // 4. Lakukan validasi akhir terhadap format internasional
        if (preg_match('/^\+62\d{8,15}$/', $cleaned)) {
            return $cleaned;
        }

        // Jika setelah semua usaha formatnya masih salah, kembalikan null
        return null;
    }
}