<?php

namespace App\Http\Controllers;

use App\Models\Employee;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Inertia\Inertia;
use PDO;
use App\Imports\EmployeeImport;

class EmployeeController extends Controller
{
    public function index(Request $request)
    {
        set_time_limit(500);
        ini_set('memory_limit', '512M');

        $search = $request->input('search', '');
        $perPage = $request->input('perPage', 10);

        $employees = Employee::query()
            ->when($search, function ($query, $search) {
                return $query->where(function ($q) use ($search) {
                    $q->where('FullNeng', 'like', "%{$search}%")
                        ->orWhere('FullNamh', 'like', "%{$search}%")
                        ->orWhere('staff_code', 'like', "%{$search}%")
                        ->orWhere('EmpId', 'like', "%{$search}%")
                        ->orWhere('job_title', 'like', "%{$search}%")
                        ->orWhere('dept_name', 'like', "%{$search}%")
                        ->orWhere('country_name', 'like', "%{$search}%")
                        ->orWhere('college', 'like', "%{$search}%")
                        ->orWhere('Staff_Type', 'like', "%{$search}%");
                });
            })
            ->orderBy('EmpId', 'desc')
            ->paginate($perPage)
            ->through(function ($employee) {
                return [
                    'EmpId' => $employee->EmpId,
                    'Staff_Code' => trim($employee->staff_code),
                    'FullName_Eng' => $employee->FullNeng,
                    'FullName_Amh' => $employee->FullNamh,
                    'Gender' => trim($employee->sex),
                    'Country' => $employee->country_name,
                    'Job_Title' => $employee->job_title,
                    'Department' => $employee->dept_name,
                    'College' => $employee->college,
                    'HouseNo' => $employee->house_number,
                    'Woreda' => trim($employee->woreda),
                    'SubCity' => $employee->sub_city,
                    'Staff_Type' => $employee->Staff_Type,
                    'photo' => $employee->photo,
                    'sig' => $employee->sig,
                    'updated' => (bool) $employee->updated,
                ];
            });

        // Get updated count
        $updatedCount = Employee::where('updated', true)->count();
        $totalCount = Employee::count();

        return Inertia::render('dashboard', [
            'employees' => $employees,
            'search' => $search,
            'updatedCount' => $updatedCount,
            'totalCount' => $totalCount,
        ]);
    }

    private function generateEmpId()
    {
        $maxEmpId = Employee::max('EmpId');
        if (!$maxEmpId) {
            return 1;
        }
        return $maxEmpId + 1;
    }

    public function store(Request $request)
    {
        $validated = $request->validate([
            'staff_code' => 'nullable|string|max:50',
            'FullNeng' => 'nullable|string|max:255',
            'FullNamh' => 'nullable|string|max:255',
            'sex' => 'nullable|string|max:10',
            'country_name' => 'nullable|string|max:100',
            'job_title' => 'nullable|string|max:255',
            'dept_name' => 'nullable|string|max:255',
            'college' => 'nullable|string|max:255',
            'house_number' => 'nullable|string|max:50',
            'woreda' => 'nullable|string|max:50',
            'sub_city' => 'nullable|string|max:100',
            'Staff_Type' => 'nullable|string|max:255',
            'photo' => 'nullable|string',
            'sig' => 'nullable|string',
        ]);

        $photo = $validated['photo'] ?? null;
        $sig = $validated['sig'] ?? null;

        unset($validated['photo']);
        unset($validated['sig']);
        $empId = $this->generateEmpId();

        $validated['EmpId'] = $empId;
        $validated['updated'] = true;

        $employee = Employee::create($validated);

        if ($photo) {
            if (strpos($photo, 'data:image') === 0) {
                $photo = preg_replace('/^data:image\/\w+;base64,/', '', $photo);
            }
            $photoBinary = base64_decode($photo);

            $pdo = DB::connection('sqlsrv')->getPdo();
            $stmt = $pdo->prepare("UPDATE [HR_eID] SET [photo] = :photo WHERE [EmpId] = :id");
            $stmt->bindParam(':photo', $photoBinary, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
            $stmt->bindParam(':id', $empId);
            $stmt->execute();
        }

        if ($sig) {
            if (strpos($sig, 'data:image') === 0) {
                $sig = preg_replace('/^data:image\/\w+;base64,/', '', $sig);
            }
            $sigBinary = base64_decode($sig);
            $hexSig = bin2hex($sigBinary);
            DB::connection('sqlsrv')->update(
                "UPDATE [HR_eID] SET [sig] = CONVERT(VARBINARY(MAX), ?, 2) WHERE [EmpId] = ?",
                [$hexSig, $empId]
            );
        }

        return redirect()->back()->with('success', 'Employee created successfully');
    }

    public function update(Request $request, $id)
    {
        $employee = Employee::findOrFail($id);

        $validated = $request->validate([
            'staff_code' => 'nullable|string|max:50',
            'FullNeng' => 'nullable|string|max:255',
            'FullNamh' => 'nullable|string|max:255',
            'sex' => 'nullable|string|max:10',
            'country_name' => 'nullable|string|max:100',
            'job_title' => 'nullable|string|max:255',
            'dept_name' => 'nullable|string|max:255',
            'college' => 'nullable|string|max:255',
            'house_number' => 'nullable|string|max:50',
            'woreda' => 'nullable|string|max:50',
            'sub_city' => 'nullable|string|max:100',
            'Staff_Type' => 'nullable|string|max:255',
            'photo' => 'nullable|string',
            'sig' => 'nullable|string',
        ]);

        $photo = $validated['photo'] ?? null;
        $sig = $validated['sig'] ?? null;

        unset($validated['photo']);
        unset($validated['sig']);

        $validated['updated'] = true;

        $employee->update($validated);

        if ($photo !== null) {
            if (empty($photo)) {
                DB::connection('sqlsrv')->update(
                    "UPDATE [HR_eID] SET [photo] = NULL WHERE [EmpId] = ?",
                    [$id]
                );
            } else {
                if (strpos($photo, 'data:image') === 0) {
                    $photo = preg_replace('/^data:image\/\w+;base64,/', '', $photo);
                }
                $photoBinary = base64_decode($photo);

                $pdo = DB::connection('sqlsrv')->getPdo();
                $stmt = $pdo->prepare("UPDATE [HR_eID] SET [photo] = :photo WHERE [EmpId] = :id");
                $stmt->bindParam(':photo', $photoBinary, PDO::PARAM_LOB, 0, PDO::SQLSRV_ENCODING_BINARY);
                $stmt->bindParam(':id', $id);
                $stmt->execute();
            }
        }

        if ($sig !== null) {
            if (empty($sig)) {
                DB::connection('sqlsrv')->update(
                    "UPDATE [HR_eID] SET [sig] = NULL WHERE [EmpId] = ?",
                    [$id]
                );
            } else {
                if (strpos($sig, 'data:image') === 0) {
                    $sig = preg_replace('/^data:image\/\w+;base64,/', '', $sig);
                }
                $sigBinary = base64_decode($sig);
                $hexSig = bin2hex($sigBinary);
                DB::connection('sqlsrv')->update(
                    "UPDATE [HR_eID] SET [sig] = CONVERT(VARBINARY(MAX), ?, 2) WHERE [EmpId] = ?",
                    [$hexSig, $id]
                );
            }
        }

        return redirect()->back()->with('success', 'Employee updated successfully');
    }

    public function destroy($id)
    {
        $employee = Employee::findOrFail($id);
        $employee->delete();

        return redirect()->back()->with('success', 'Employee deleted successfully');
    }

    public function show($id)
    {
        $employee = Employee::findOrFail($id);

        return response()->json([
            'EmpId' => $employee->EmpId,
            'Staff_Code' => trim($employee->staff_code),
            'FullName_Eng' => $employee->FullNeng,
            'FullName_Amh' => $employee->FullNamh,
            'Gender' => trim($employee->sex),
            'Country' => $employee->country_name,
            'Job_Title' => $employee->job_title,
            'Department' => $employee->dept_name,
            'College' => $employee->college,
            'HouseNo' => $employee->house_number,
            'Woreda' => trim($employee->woreda),
            'SubCity' => $employee->sub_city,
            'Staff_Type' => $employee->Staff_Type,
            'photo' => $employee->photo,
            'sig' => $employee->sig,
            'updated' => (bool) $employee->updated,
        ]);
    }

    public function import(Request $request)
    {
        set_time_limit(600);
        ini_set('memory_limit', '1024M');
        $request->validate([
            'file' => 'required|mimes:xlsx,xls,csv|max:10240',
        ]);

        try {
            $import = new EmployeeImport();
            $results = $import->import($request->file('file')->getPathname());

            $message = "Import completed!\n";
            $message .= "✓ Successfully updated: {$results['success']} employees\n";

            if ($results['failed'] > 0) {
                $message .= "✗ Failed: {$results['failed']} employees\n";
            }

            if (!empty($results['not_found'])) {
                $message .= "⚠️ Not found: " . count($results['not_found']) . " employees\n";
                foreach (array_slice($results['not_found'], 0, 5) as $item) {
                    $message .= "  - EmpId: {$item['EmpId']} ({$item['name']}) - Row: {$item['row']}\n";
                }
                if (count($results['not_found']) > 5) {
                    $message .= "  ... and " . (count($results['not_found']) - 5) . " more\n";
                }
            }

            if (!empty($results['errors'])) {
                $message .= "⚠️ Errors: " . count($results['errors']) . "\n";
                foreach (array_slice($results['errors'], 0, 3) as $item) {
                    $message .= "  - Row: {$item['row']} - {$item['error']}\n";
                }
                if (count($results['errors']) > 3) {
                    $message .= "  ... and " . (count($results['errors']) - 3) . " more errors\n";
                }
            }

            if (!empty($results['skipped'])) {
                $message .= "⏭️ Skipped: " . count($results['skipped']) . " rows\n";
            }

            return redirect()->back()->with('import_results', [
                'message' => $message,
                'results' => $results
            ]);
        } catch (\Exception $e) {
            return redirect()->back()->with('error', 'Import failed: ' . $e->getMessage());
        }
    }
}