<?php

namespace App\Http\Controllers;

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

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

        $search = $request->input('search', '');
        $perPage = max(1, min((int) $request->input('perPage', 10), 100));

        $employees = Employee::query()
            ->when($search, function ($query, $search) {
                $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}%");
                });
            })
            ->orderBy('EmpId', 'desc')
            ->paginate($perPage);

        $ids = collect($employees->items())->pluck('EmpId')->all();

        $metadata = empty($ids)
            ? collect()
            : EmployeeMetadata::whereIn('EmpId', $ids)
                ->get()
                ->keyBy('EmpId');

        $employees->getCollection()->transform(
            function ($employee) use ($metadata) {
                $meta = $metadata->get($employee->EmpId);

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

        $updatedCount = EmployeeMetadata::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');

        return $maxEmpId ? $maxEmpId + 1 : 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',
        ]);

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

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

        $empId = $this->generateEmpId();
        $validated['EmpId'] = $empId;

        // Create the employee record in SQL Server.
        $employee = Employee::create($validated);

        // Store application metadata separately in SQLite.
        EmployeeMetadata::updateOrCreate(
            ['EmpId' => $empId],
            [
                'Staff_Type' => $staffType,
                'updated' => true,
            ]
        );

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

            $photoBinary = base64_decode($photo, true);

            if ($photoBinary === false) {
                throw new \InvalidArgumentException('Invalid photo data.');
            }

            $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->bindValue(':id', $empId, PDO::PARAM_INT);
            $stmt->execute();
        }

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

            $sigBinary = base64_decode($sig, true);

            if ($sigBinary === false) {
                throw new \InvalidArgumentException('Invalid signature data.');
            }

            $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',
        ]);

        $hasStaffType = array_key_exists('Staff_Type', $validated);
        $staffType = $validated['Staff_Type'] ?? null;
        $photo = $validated['photo'] ?? null;
        $sig = $validated['sig'] ?? null;

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

        // Update only real columns in SQL Server.
        if (!empty($validated)) {
            $employee->update($validated);
        }

        // Update SQLite metadata without sending these fields to SQL Server.
        $metadataValues = ['updated' => true];

        if ($hasStaffType) {
            $metadataValues['Staff_Type'] = $staffType;
        }

        EmployeeMetadata::updateOrCreate(
            ['EmpId' => (int) $id],
            $metadataValues
        );

        if ($request->exists('photo')) {
            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, true);

                if ($photoBinary === false) {
                    throw new \InvalidArgumentException('Invalid photo data.');
                }

                $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->bindValue(':id', (int) $id, PDO::PARAM_INT);
                $stmt->execute();
            }
        }

        if ($request->exists('sig')) {
            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, true);

                if ($sigBinary === false) {
                    throw new \InvalidArgumentException('Invalid signature data.');
                }

                $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);

        // Preserve existing behavior: delete the SQL Server employee record.
        $employee->delete();

        // Remove associated SQLite metadata too.
        EmployeeMetadata::where('EmpId', (int) $id)->delete();

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

    public function show($id)
    {
        $employee = Employee::findOrFail($id);
        $meta = EmployeeMetadata::find((int) $employee->EmpId);

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

    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 (\Throwable $e) {
            report($e);

            return redirect()->back()->with(
                'error',
                'Import failed. Please check the application log.'
            );
        }
    }
}