<?php

namespace App\Services;

use App\Models\CompanyEmployee;
use App\Models\PortalUser;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Validation\ValidationException;

class CompanyEmployeeService
{
    public function __construct(
        private readonly PortalUserService $portalUserService
    ) {}

    /**
     * Create a new company employee and their portal user account.
     */
    public function createEmployee(array $data, int $companyId): CompanyEmployee
    {
        try {
            // First: Create employee and portal user in a transaction
            $result = DB::transaction(function () use ($data, $companyId) {
                // Create the company employee record with only basic info
                // Status is 'inactive' until they set their password
                $employee = CompanyEmployee::create([
                    'company_id' => $companyId,
                    'first_name' => $data['first_name'],
                    'last_name' => $data['last_name'],
                    'email' => $data['email'],
                    'employment_status' => 'inactive',
                ]);

                // Create portal user for the employee
                $portalUser = PortalUser::create([
                    'name' => $employee->full_name,
                    'email' => $employee->email,
                    'role' => 'employee',
                    'company_id' => $companyId,
                    'employee_id' => $employee->id,
                    'password' => null,
                ]);

                // Link portal user to employee
                $employee->update(['portal_user_id' => $portalUser->id]);

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

            // Second: Send invitation email AFTER transaction commits successfully
            // This prevents sending emails for failed/rolled-back transactions
            try {
                $this->portalUserService->sendInvitation($result['portalUser']);
            } catch (\Exception $e) {
                // Log email failure but don't rollback since transaction already committed
                Log::error('Failed to send invitation email after employee creation: ' . $e->getMessage(), [
                    'employee_id' => $result['employee']->id,
                    'portal_user_id' => $result['portalUser']->id,
                    'exception' => $e,
                ]);
                // Email can be resent later via resend-invitation endpoint
            }

            return $result['employee']->fresh();
        } catch (QueryException $e) {
            // Check for duplicate entry error
            if ($e->getCode() === '23000' && str_contains($e->getMessage(), 'Duplicate entry')) {
                throw ValidationException::withMessages([
                    'email' => ['An employee with this email already exists.'],
                ]);
            }
            Log::error('QueryException in createEmployee: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        } catch (\Exception $e) {
            Log::error('Exception in createEmployee: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        }
    }

    /**
     * Create a new company employee and their portal user account WITHOUT sending invitation.
     * Used for bulk uploads and employee registrations that don't require email invites.
     */
    public function createEmployeeWithoutInvite(array $data, int $companyId): CompanyEmployee
    {
        try {
            return DB::transaction(function () use ($data, $companyId) {
                // Create the company employee record with only basic info
                // Status is 'inactive' since no password is set
                $employee = CompanyEmployee::create([
                    'company_id' => $companyId,
                    'first_name' => $data['first_name'],
                    'last_name' => $data['last_name'],
                    'email' => $data['email'],
                    'date_of_birth' => $data['date_of_birth'] ?? null,
                    'phone' => $data['phone'] ?? null,
                    'employment_status' => 'inactive',
                ]);

                // Create portal user for the employee (without password)
                $portalUser = PortalUser::create([
                    'name' => $employee->full_name,
                    'email' => $employee->email,
                    'role' => 'employee',
                    'company_id' => $companyId,
                    'employee_id' => $employee->id,
                    'password' => null,
                ]);

                // Link portal user to employee
                $employee->update(['portal_user_id' => $portalUser->id]);

                Log::info('Employee created without invitation', [
                    'employee_id' => $employee->id,
                    'portal_user_id' => $portalUser->id,
                ]);

                return $employee->fresh();
            });
        } catch (QueryException $e) {
            // Check for duplicate entry error
            if ($e->getCode() === '23000' && str_contains($e->getMessage(), 'Duplicate entry')) {
                throw ValidationException::withMessages([
                    'email' => ['An employee with this email already exists.'],
                ]);
            }
            Log::error('QueryException in createEmployeeWithoutInvite: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        } catch (\Exception $e) {
            Log::error('Exception in createEmployeeWithoutInvite: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        }
    }

    /**
     * Create a company employee record only — no PortalUser, no invitation email.
     * Used for the new tokenised submission flow where employees receive a submission
     * link instead of a portal account invitation.
     */
    public function createEmployeeRecord(array $data, int $companyId): CompanyEmployee
    {
        try {
            return DB::transaction(function () use ($data, $companyId) {
                $employee = CompanyEmployee::create([
                    'company_id' => $companyId,
                    'first_name' => $data['first_name'],
                    'last_name' => $data['last_name'],
                    'email' => $data['email'],
                    'date_of_birth' => $data['date_of_birth'] ?? null,
                    'phone' => $data['phone'] ?? null,
                    'employment_status' => 'inactive',
                ]);

                Log::info('Employee record created (no portal user)', [
                    'employee_id' => $employee->id,
                ]);

                return $employee->fresh();
            });
        } catch (QueryException $e) {
            if ($e->getCode() === '23000' && str_contains($e->getMessage(), 'Duplicate entry')) {
                throw ValidationException::withMessages([
                    'email' => ['An employee with this email already exists.'],
                ]);
            }
            Log::error('QueryException in createEmployeeRecord: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        } catch (\Exception $e) {
            Log::error('Exception in createEmployeeRecord: ' . $e->getMessage(), ['exception' => $e]);
            throw $e;
        }
    }

    /**
     * Get employees for a specific company (paginated, with search and filters).
     */
    public function getEmployeesByCompany(int $companyId, int $perPage = 15, ?array $filters = [])
    {
        $query = CompanyEmployee::where('company_id', $companyId)
            ->with('portalUser');

        // Apply search and filters
        $this->applySearchAndFilters($query, $filters);

        return $query->orderBy('created_at', 'desc')
            ->paginate($perPage);
    }

    /**
     * Get all employees (for system admins, paginated, with search and filters).
     */
    public function getAllEmployees(int $perPage = 15, ?array $filters = [])
    {
        $query = CompanyEmployee::with(['portalUser', 'company']);

        // Apply search and filters
        $this->applySearchAndFilters($query, $filters);

        return $query->orderBy('created_at', 'desc')
            ->paginate($perPage);
    }

    /**
     * Apply search and filter conditions to the query.
     */
    private function applySearchAndFilters($query, ?array $filters = []): void
    {
        if (empty($filters)) {
            return;
        }

        // Text search across name, email, and employee_number
        if (!empty($filters['search'])) {
            $search = $filters['search'];
            $query->where(function ($q) use ($search) {
                $q->where('first_name', 'LIKE', "%{$search}%")
                    ->orWhere('last_name', 'LIKE', "%{$search}%")
                    ->orWhere('email', 'LIKE', "%{$search}%")
                    ->orWhere('employee_number', 'LIKE', "%{$search}%");

                // Database-agnostic full name search
                $driver = \DB::connection()->getDriverName();
                if ($driver === 'sqlite') {
                    // SQLite uses || for concatenation
                    $q->orWhereRaw("(first_name || ' ' || last_name) LIKE ?", ["%{$search}%"]);
                } else {
                    // MySQL/PostgreSQL use CONCAT
                    $q->orWhereRaw("CONCAT(first_name, ' ', last_name) LIKE ?", ["%{$search}%"]);
                }
            });
        }

        // Filter by employment status
        if (!empty($filters['employment_status'])) {
            $query->where('employment_status', $filters['employment_status']);
        }

        // Filter by NIN verification status
        if (isset($filters['nin_verified'])) {
            $ninVerified = filter_var($filters['nin_verified'], FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
            if ($ninVerified !== null) {
                $query->where('is_nin_verified', $ninVerified);
            }
        }
    }
}
