<?php

namespace App\Services;

use App\Models\Company;
use App\Models\CompanyEmployee;
use App\Models\EmployeeRegistration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Validator;
use Maatwebsite\Excel\Facades\Excel;

class BulkEmployeeRegistrationUploadService
{
    public function __construct(
        private readonly CompanyEmployeeService $companyEmployeeService,
        private readonly NinEncryptionService $ninEncryptionService
    ) {}

    /**
     * Process bulk CSV upload for employee registrations.
     * Returns array with successful imports, failed rows, and errors.
     */
    public function processCsvUpload(string $csvPath, bool $sendInvitations = false, ?string $originalFilename = null): array
    {
        $results = [
            'successful' => [],
            'failed' => [],
            'total_rows' => 0,
            'successful_count' => 0,
            'failed_count' => 0,
        ];

        try {
            $csvData = $this->parseCsvFile($csvPath, $originalFilename);
            $results['total_rows'] = count($csvData);

            foreach ($csvData as $index => $row) {
                $rowNumber = $index + 2; // +2 because index starts at 0 and we skip header

                try {
                    // Validate row data
                    $validation = $this->validateCsvRow($row, $rowNumber);
                    if (!$validation['valid']) {
                        $results['failed'][] = [
                            'row' => $rowNumber,
                            'data' => $row,
                            'errors' => $validation['errors'],
                        ];
                        $results['failed_count']++;
                        continue;
                    }

                    // Check for duplicate email
                    $duplicate = $this->checkDuplicateEmail($row['email_address']);
                    if ($duplicate) {
                        $results['failed'][] = [
                            'row' => $rowNumber,
                            'data' => $row,
                            'errors' => ['Email address already exists in the system.'],
                        ];
                        $results['failed_count']++;
                        continue;
                    }

                    // Create Employee registration (and optionally employee account)
                    $result = $this->createEmployeeFromCsv($row, $sendInvitations);

                    $results['successful'][] = [
                        'row' => $rowNumber,
                        'employee_name' => $row['first_name'] . ' ' . $row['last_name'],
                        'email' => $row['email_address'],
                        'submission_id' => $result['submission']->id,
                        'employee_id' => $result['employee']?->id,
                    ];
                    $results['successful_count']++;
                } catch (\Exception $e) {
                    Log::error('Error processing CSV row: ' . $e->getMessage(), [
                        'row' => $rowNumber,
                        'data' => $row,
                        'exception' => $e,
                    ]);

                    $results['failed'][] = [
                        'row' => $rowNumber,
                        'data' => $row,
                        'errors' => [$e->getMessage()],
                    ];
                    $results['failed_count']++;
                }
            }

            return $results;
        } catch (\Exception $e) {
            Log::error('Error processing bulk CSV upload: ' . $e->getMessage(), [
                'exception' => $e,
            ]);
            throw $e;
        }
    }

    /**
     * Parse CSV or Excel file and return array of data.
     */
    private function parseCsvFile(string $filePath, ?string $originalFilename = null): array
    {
        // Get file extension from original filename if provided, otherwise from file path
        if ($originalFilename) {
            $extension = strtolower(pathinfo($originalFilename, PATHINFO_EXTENSION));
        } else {
            $extension = strtolower(pathinfo($filePath, PATHINFO_EXTENSION));
        }

        Log::info('Parsing uploaded file', [
            'file_path' => $filePath,
            'original_filename' => $originalFilename,
            'extension' => $extension,
        ]);

        // Handle Excel files (.xlsx, .xls)
        if (in_array($extension, ['xlsx', 'xls'])) {
            Log::info('Detected Excel file, using Excel parser');
            return $this->parseExcelFile($filePath);
        }

        // Handle CSV files (.csv, .txt)
        Log::info('Detected CSV file, using CSV parser');
        return $this->parseCsvFileOnly($filePath);
    }

    /**
     * Parse CSV file only and return array of data.
     */
    private function parseCsvFileOnly(string $filePath): array
    {
        $csvData = [];
        $header = null;

        if (($handle = fopen($filePath, 'r')) !== false) {
            while (($row = fgetcsv($handle)) !== false) {
                if (!$header) {
                    $header = $row;
                } else {
                    $csvData[] = array_combine($header, $row);
                }
            }
            fclose($handle);
        }

        return $csvData;
    }

    /**
     * Parse Excel file and return array of data.
     */
    private function parseExcelFile(string $filePath): array
    {
        try {
            $data = Excel::toArray([], $filePath);

            // Excel::toArray returns a multi-dimensional array with sheets
            // We'll use the first sheet
            if (empty($data) || empty($data[0])) {
                Log::warning('Excel file is empty or has no sheets');
                return [];
            }

            $rows = $data[0];

            if (empty($rows)) {
                Log::warning('Excel sheet has no rows');
                return [];
            }

            // First row is the header
            $header = array_shift($rows);

            Log::info('Excel parsing - raw header', [
                'header' => $header,
                'header_count' => count($header),
            ]);

            // Clean header: remove empty columns
            $header = array_filter($header, function($value) {
                return !is_null($value) && trim($value) !== '';
            });

            // Reindex header to have sequential keys
            $header = array_values($header);
            $headerCount = count($header);

            Log::info('Excel parsing - cleaned header', [
                'header' => $header,
                'header_count' => $headerCount,
            ]);

            // Combine header with each row
            $result = [];
            $rowIndex = 0;

            foreach ($rows as $row) {
                $rowIndex++;

                // Skip completely empty rows
                if (empty(array_filter($row, function($value) {
                    return !is_null($value) && trim($value) !== '';
                }))) {
                    continue;
                }

                // Only take the first N columns matching header count
                $rowData = [];
                for ($i = 0; $i < $headerCount; $i++) {
                    $rowData[] = isset($row[$i]) ? $row[$i] : null;
                }

                Log::info('Excel parsing - row data', [
                    'row_index' => $rowIndex,
                    'row_count' => count($rowData),
                    'header_count' => $headerCount,
                    'row' => $rowData,
                ]);

                if (count($header) !== count($rowData)) {
                    Log::error('Excel parsing - count mismatch', [
                        'row_index' => $rowIndex,
                        'header_count' => count($header),
                        'row_count' => count($rowData),
                        'header' => $header,
                        'row' => $rowData,
                    ]);
                    throw new \Exception("Row {$rowIndex} has " . count($rowData) . " columns but header has " . count($header) . " columns");
                }

                $result[] = array_combine($header, $rowData);
            }

            Log::info('Excel parsing completed', [
                'total_rows' => count($result),
            ]);

            return $result;
        } catch (\Exception $e) {
            Log::error('Error parsing Excel file: ' . $e->getMessage(), [
                'exception' => $e,
                'trace' => $e->getTraceAsString(),
            ]);
            throw $e;
        }
    }

    /**
     * Validate a single CSV row.
     */
    private function validateCsvRow(array $row, int $rowNumber): array
    {
        $validator = Validator::make($row, [
            'company_id' => [
                'required',
                'integer',
                function ($attribute, $value, $fail) {
                    $company = Company::find($value);
                    if (!$company || $company->status !== 'active') {
                        $fail('The selected company is not active or does not exist.');
                    }
                },
            ],
            'title' => ['nullable', 'string', 'max:20'],
            'first_name' => ['required', 'string', 'max:100'],
            'last_name' => ['required', 'string', 'max:100'],
            'middle_name' => ['nullable', 'string', 'max:100'],
            'gender' => ['nullable', 'string', 'in:Male,Female,Other'],
            'date_of_birth' => ['required', 'date', 'before:today'],
            'nationality' => ['required', 'string', 'max:100'],
            'phone_number' => ['required', 'string', 'max:20'],
            'foreign_number' => ['nullable', 'string', 'max:20'],
            'email_address' => [
                'required',
                'string',
                'email',
                'max:255',
            ],
            'country' => ['required', 'string', 'max:100'],
            'state' => ['required', 'string', 'max:100'],
            'city' => ['required', 'string', 'max:100'],
            'chn' => ['nullable', 'string', 'max:50'],
            'house_address' => ['required', 'string'],
            'previous_address' => ['nullable', 'string'],
            'bvn' => ['required', 'string', 'regex:/^[0-9]{11}$/'],
            'bank_name' => ['nullable', 'string', 'max:100'],
            'bank_account_number' => ['nullable', 'string', 'max:20'],
            'tin_or_pin' => ['nullable', 'string', 'max:50'],
            'identity_card_type' => ['required', 'string', 'max:100'],
            'identity_card_number' => [
                'required',
                'string',
                'max:50',
                function ($attribute, $value, $fail) use ($row) {
                    // Validate NIN is 11 digits when identity_card_type is "National Identity Number"
                    if (isset($row['identity_card_type']) && $row['identity_card_type'] === 'National Identity Number') {
                        if (!preg_match('/^[0-9]{11}$/', $value)) {
                            $fail('National Identity Number must be exactly 11 digits.');
                        }
                    }
                },
            ],
        ]);

        if ($validator->fails()) {
            return [
                'valid' => false,
                'errors' => $validator->errors()->all(),
            ];
        }

        return ['valid' => true, 'errors' => []];
    }

    /**
     * Check if an email already exists.
     */
    private function checkDuplicateEmail(string $email): bool
    {
        return DB::table('companies')->where('email', $email)->exists()
            || DB::table('portal_users')->where('email', $email)->exists()
            || DB::table('company_employees')->where('email', $email)->exists();
    }

    /**
     * Create employee registration (and optionally employee account) from CSV row.
     */
    private function createEmployeeFromCsv(array $row, bool $sendInvitation): array
    {
        return DB::transaction(function () use ($row, $sendInvitation) {
            // Create Employee registration record
            $submission = EmployeeRegistration::create([
                'company_id' => $row['company_id'],
                'submission_type' => 'bulk_upload',
                'status' => EmployeeRegistration::STATUS_APPROVED, // Auto-approved for bulk uploads
                // BIO-DATA INFORMATION
                'title' => $row['title'] ?? null,
                'first_name' => $row['first_name'],
                'last_name' => $row['last_name'],
                'middle_name' => $row['middle_name'] ?? null,
                'gender' => $row['gender'] ?? null,
                'date_of_birth' => $row['date_of_birth'],
                'nationality' => $row['nationality'],
                'phone_number' => $row['phone_number'],
                'confirm_phone_number' => $row['phone_number'], // Same as phone_number for bulk upload
                'foreign_number' => $row['foreign_number'] ?? null,
                'email_address' => $row['email_address'],
                'confirm_email_address' => $row['email_address'], // Same as email_address for bulk upload
                'country' => $row['country'],
                'state' => $row['state'],
                'city' => $row['city'],
                'chn' => $row['chn'] ?? null,
                'house_address' => $row['house_address'],
                'previous_address' => $row['previous_address'] ?? null,
                // BANK INFORMATION - Encrypt plain BVN from CSV
                ...$this->encryptBvnForStorage($row['bvn']),
                'bank_name' => $row['bank_name'] ?? null,
                'bank_account_number' => $row['bank_account_number'] ?? null,
                'tin_or_pin' => $row['tin_or_pin'] ?? null,
                'bank_details_consent' => true,
                // IDENTITY INFORMATION
                'identity_card_type' => $row['identity_card_type'],
                // Conditionally encrypt NIN
                ...$this->prepareNinForStorage($row['identity_card_type'], $row['identity_card_number']),
                // Consent
                'consent_given' => true,
                'approved_at' => now(),
            ]);

            // Always create employee account without sending invitation
            // Bulk uploads are auto-approved and accounts are created immediately
            $employee = $this->companyEmployeeService->createEmployeeWithoutInvite([
                'first_name' => $row['first_name'],
                'last_name' => $row['last_name'],
                'email' => $row['email_address'],
                'date_of_birth' => $row['date_of_birth'],
                'phone' => $row['phone_number'],
            ], $row['company_id']);

            // Link submission to employee
            $submission->update([
                'company_employee_id' => $employee->id,
                'account_status' => EmployeeRegistration::ACCOUNT_STATUS_PENDING,
            ]);

            return [
                'submission' => $submission,
                'employee' => $employee,
            ];
        });
    }

    /**
     * Encrypt plain BVN for storage.
     *
     * @param string $plainBvn
     * @return array Returns encrypted BVN fields for database storage
     */
    private function encryptBvnForStorage(string $plainBvn): array
    {
        $encrypted = $this->ninEncryptionService->encrypt($plainBvn);

        return [
            'bvn_encrypted' => $encrypted['iv'] . ':' . $encrypted['ciphertext'] . ':' . $encrypted['tag'],
        ];
    }

    /**
     * Prepare NIN for storage (conditional encryption).
     *
     * @param string $identityCardType
     * @param string $identityCardNumber
     * @return array Returns fields for database storage
     */
    private function prepareNinForStorage(string $identityCardType, string $identityCardNumber): array
    {
        if ($identityCardType === 'National Identity Number') {
            $encrypted = $this->ninEncryptionService->encrypt($identityCardNumber);

            return [
                'identity_card_number' => null,
                'nin_number_encrypted' => $encrypted['iv'] . ':' . $encrypted['ciphertext'] . ':' . $encrypted['tag'],
            ];
        } else {
            return [
                'identity_card_number' => $identityCardNumber,
                'nin_number_encrypted' => null,
            ];
        }
    }
}
