<?php

namespace App\Services;

use App\User;
use Illuminate\Support\Facades\DB;

class UserSerialGenerator
{
    /**
     * Generate a unique user serial number
     *
     * @return string
     */
    public static function generate(): string
    {
        // Use database transaction to ensure uniqueness
        return DB::transaction(function () {
            // Get the highest existing serial number with row-level lock
            $lastUser = User::whereNotNull('serial_number')
                ->orderBy('serial_number', 'desc')
                ->lockForUpdate()
                ->first();

            if ($lastUser) {
                // Extract number from serial (e.g., "USR-2025-000005" -> 5)
                $lastSerial = $lastUser->serial_number;
                $lastNumber = (int) substr($lastSerial, -6);
                $nextNumber = $lastNumber + 1;
            } else {
                // First user with serial number
                $nextNumber = 1;
            }

            // Format: USR-YEAR-SEQUENCE (6 digits)
            $year = date('Y');
            return sprintf('USR-%s-%06d', $year, $nextNumber);
        });
    }

    /**
     * Check if a serial number is valid and unique
     *
     * @param string $serialNumber
     * @return bool
     */
    public static function isValidAndUnique(string $serialNumber): bool
    {
        // Check format: USR-YYYY-NNNNNN
        if (!preg_match('/^USR-\d{4}-\d{6}$/', $serialNumber)) {
            return false;
        }

        // Check if it already exists
        return !User::where('serial_number', $serialNumber)->exists();
    }

    /**
     * Get user by serial number
     *
     * @param string $serialNumber
     * @return User|null
     */
    public static function getUserBySerial(string $serialNumber): ?User
    {
        return User::where('serial_number', $serialNumber)->first();
    }

    /**
     * Get all certificates for a user by their serial number
     *
     * @param string $serialNumber
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public static function getCertificatesByUserSerial(string $serialNumber)
    {
        $user = self::getUserBySerial($serialNumber);
        
        if (!$user) {
            return collect();
        }

        return $user->certificates()->with([
            'quiz.webinar',
            'webinar',
            'student'
        ])->get();
    }

    /**
     * Search users by serial number (partial match)
     *
     * @param string $searchTerm
     * @return \Illuminate\Database\Eloquent\Collection
     */
    public static function searchUsersBySerial(string $searchTerm)
    {
        return User::where('serial_number', 'LIKE', '%' . $searchTerm . '%')
            ->with(['certificates' => function ($query) {
                $query->with(['quiz.webinar', 'webinar']);
            }])
            ->get();
    }
} 