<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Sites;
use App\Models\SiteDocument;
use App\Models\SiteDocumentSignature;
use App\Models\DocumentType;
use App\Models\LinkManagement;
use App\Models\Company;
use App\Models\Project;
use App\Models\EmpCompanyDetails;
use App\Models\SiteHistory;
use App\Models\EmpPersonalDetails;
use App\Models\User;
use App\Models\Role;
use App\Models\EmployeeAttendance;
use App\Models\RosterAssign;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Validator;
use Illuminate\Database\QueryException;
use Illuminate\Support\Facades\Log;


class SiteController extends Controller
{
    public function __construct()
    {
        $this->middleware('Permissions:Sites Maintain')->only(['create', 'edit']);
    }
    public function index(Request $request)
    {
        $query = Sites::where('del', 0);

        // When active=0 is passed, return ALL sites (both active and inactive) without filtering by active status
        // When active is not provided or active=1, return only active sites
        if($request->has('active') && (int)$request->active === 0){
            // Return all sites without active status filter
        }else{
            // Default: only return active sites
            $query->where('active', 1);
        }
        
        $query = $this->applyCustomerWorkspaceFilter($query);
        $query->with('supervisor')->with('Company')->with('project');
        if ($request->filled('search')) {
            $searchTerm = $request->search;
            $query->where(function ($q) use ($searchTerm) {
                $q->where('title', 'like', '%' . $searchTerm . '%')
                ->orWhere('site_state', 'like', '%' . $searchTerm . '%')
                ->orWhere('street_address', 'like', '%' . $searchTerm . '%')
                ->orWhere(function ($nameQuery) use ($searchTerm) {
                    $this->applyNameSearch($nameQuery, $searchTerm, 'supervisor');
                })
                ->orWhereHas('company', function ($subquery) use ($searchTerm) {
                    $subquery->where('name', 'like', '%' . $searchTerm . '%');
                })
                ->orWhereHas('project', function ($subquery) use ($searchTerm) {
                    $subquery->where('title', 'like', '%' . $searchTerm . '%');
                });
            });
        }
        return $this->withCount($query, 'Data get Successfully');
    }
    
 

    public function create(Request $request)
    {
        $get_employes = EmpCompanyDetails::with('EmpPersonalDetails')->where('del', 0)->where('compeleted', 1)->where('status', 1)->where('access_role', 'SPV')->get();
        $get_companies = Company::where('del', 0)->where('active', 1)->get();
        return view('Sites.create', compact('get_employes', 'get_companies'));
    }

    public function ssoList()
    {
        $userTable = $this->getUserTable();
        // Base query for employee list
        $query = EmpCompanyDetails::where('compeleted', '1')
            ->where('approved', '1')
            ->where('status', '1')
            ->where('del', '0')
            ->with([
                'empPersonalDetails' => function ($query) {
                    $query->select('emp_id', 'first_name', 'middle_name', 'last_name', 'customer_id', 'workspace_id');
                },
                'accessRole' => function ($query) {
                    $query->select('id', 'title', 'code');
                },
                'empTier' => function ($query) {
                    $query->select('id', 'title');
                },
                'accessTier' => function ($query) {
                    $query->select('id', 'title', 'tier_key');
                },
            ])
            ->select('id', 'access_role', 'tier_id');
        // Add Customer and Employee Access Checks
        if ($userTable === 'customer') {
            $query->whereHas('empPersonalDetails', function ($q) {
                $q->where('customer_id', auth()->id())
                    ->where('workspace_id', auth()->user()->current_workspace_id);
            });
        }
        if ($userTable === 'emp') {
            $query->whereHas('empPersonalDetails', function ($q) {
                $q->where('customer_id', auth()->user()->customer_id)
                    ->where('workspace_id', auth()->user()->workspace_id);
            });
        }
        // Execute query
        $empList = $query->get();
        // Filter only SSO roles
        $data = $empList->where('access_role', 'SSO')->values()->toArray();
        // Return response
        return $this->success($data, 'SSO List fetched successfully');
    }

    public function store(Request $request)
    {
       
        $validator = Validator::make($request->all(), [
            'title' => 'required',
            'state' => 'required',
            'external_id' => 'nullable',
            'supervisor' => 'nullable',
            'company_client' => 'nullable',
            'description' => 'nullable',
            'address' => 'required',
            'latitude' => 'required',
            'longitude' => 'required',
            'area_radius' => 'required',
            'project' => 'nullable',
            'forman_id' => 'nullable',
            'sso_id'=>'nullable'
        ]);
        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        }
        try {
            $userTable = $this->getUserTable();
            $auth_id = 0;
            $workspace_id = 0;
            $created_by = 0;
            if ($userTable === "customer") {
                $auth_id = Auth::user()->id;
                $workspace_id = Auth::user()->current_workspace_id;
            }
            if ($userTable === "emp") {
                $auth_id = auth()->user()->customer_id;
                $workspace_id = auth()->user()->workspace_id;
                $created_by = auth()->user()->id;
            }
            $validatedData = $validator->validated();
            $Data = Sites::create([
                'title' => $validatedData['title'],
                'site_state' => $validatedData['state'],
                'external_id' => $validatedData['external_id'],
                'supervisor_id' => $validatedData['supervisor'],
                'company_client' => $validatedData['company_client'],
                'forman_id' => $validatedData['forman_id'],
                'sso_id' => $validatedData['sso_id'],   
                'description' => $validatedData['description'],
                'street_address' => $validatedData['address'],
                'latitude' => $validatedData['latitude'],
                'longitude' => $validatedData['longitude'],
                'area_radius' => $validatedData['area_radius'],
                'project_id' => $validatedData['project'],
                'customer_id' => $auth_id,
                'workspace_id' => $workspace_id,
                'created_by' => $created_by
            ]);
            return $this->success($Data, 'Site Created Successfully');
        } catch (QueryException $exception) {
            $errorMessage = $exception->getMessage();
            preg_match("/'([^']+)'/", $errorMessage, $matches);
            $errorField = count($matches) > 1 ? $matches[1] : 'unknown';
            return $this->message('The ' . $errorField . ' field is required.', 500);
        }
    }


    public function siteEdit($id)
    {
        $site = Sites::with(['emppersonaldetails', 'company','siteSafetyOfficer'])->where('id', $id)->where('del', '0')->first();
        if (!$site) {
            return $this->message('Site not Found.', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($site->workspace_id != auth()->user()->current_workspace_id || $site->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Site', 403);
        }

        if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Site', 403);
        }
        return $this->success($site, 'Site data retrieved successfully');
    }

    public function siteUpdate(Request $request, $id)
    {

        $validator = Validator::make($request->all(), [
            'title' => 'required',
            'state' => 'required',
            'external_id' => 'nullable',
            'supervisor' => 'nullable',
            'company_client' => 'nullable',
            'description' => 'nullable',
            'address' => 'required',
            'latitude' => 'required',
            'longitude' => 'required',
            'area_radius' => 'required',
            'project' => 'nullable',
            'forman_id' => 'nullable',
            'sso_id'=>'nullable'
        ]);

        if ($validator->fails()) {
            return $this->handleValidationFailure($validator);
        } else {

            $validatedData = $validator->validated();
            $site = Sites::where('id', $id)->where('del', '0')->first();
            if (!$site) {
                return $this->message('Site not found or already deleted', 404);
            }
            $updateData = [
                'title' => $validatedData['title'],
                'site_state' => $validatedData['state'],
                'external_id' => $validatedData['external_id'],
                'supervisor_id' => $validatedData['supervisor'],
                'company_client' => $validatedData['company_client'],
                'description' => $validatedData['description'],
                'street_address' => $validatedData['address'],
                'latitude' => $validatedData['latitude'],
                'longitude' => $validatedData['longitude'],
                'area_radius' => $validatedData['area_radius'],
                'forman_id' => $validatedData['forman_id'],
                'sso_id' => $validatedData['sso_id'],
                // 'project_id' => $validatedData['project']
            ];
            $userTable = $this->getUserTable();
            if (
                $userTable == "customer" && ($site->workspace_id != auth()->user()->current_workspace_id || $site->customer_id != auth()->user()->id)
            ) {
                return $this->message('You do not have access to this Site', 403);
            }
            if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('You do not have access to this Site', 403);
            }
            $site->update($updateData);
            $getuserdetail = DB::table('users')->where('id', auth()->user()->id)->first();
            $description = "<a href='" . url('/user-profile/' . Auth::id()) . "' style='text-transform: capitalize;' role='button' class='primary text-decoration-none'>" . e($getuserdetail->name ?? '') . "</a> changed the site detail: '" . e($site->title) . "'.";
            $history = [
                'site_id' => $site->id,
                'description' => $description,
                'updated_by' => auth()->user()->id,
            ];
            SiteHistory::create($history);
            return $this->success($updateData, 'Site Updated Successfully');
        }
    }

    public function updateStatus($id)
    {
        $site = Sites::where('id', $id)->where('del', '0')->first();
        if (!$site) {
            return $this->message('Site not found', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($site->workspace_id != auth()->user()->current_workspace_id || $site->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Site', 403);
        }

        if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Site', 403);
        }
        // Toggle the active status
        $site->active = $site->active == 1 ? 0 : 1;
        $site->save();
        return $this->success($site, 'Status Updated Successfully');
    }

    public function destroy($id)
    {
        $site = Sites::where('id', $id)->where('del', '0')->first();
        if (!$site) {
            return $this->message('Site not found or already deleted', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($site->workspace_id != auth()->user()->current_workspace_id || $site->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Site', 403);
        }

        if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Site', 403);
        }
        Sites::where('id', $id)->update(['del' => '1']);
        $getuserdetail = DB::table('users')->where('id', auth()->user()->id)->first();
        if (!$getuserdetail) {
            return $this->message('User details not found');
        }
        $description = "<a href='" . url('/') . "/user-profile/" . Auth::user()->id . "' style='text-transform: capitalize;' role='button' class='primary text-decoration-none'> {$getuserdetail->name} </a>  Deleted that site: '{$site->title}'.";

        $history = [
            'site_id' => $site->id,
            'description' => $description,
            'updated_by' => auth()->user()->id,
        ];
        SiteHistory::create($history);
        return $this->message('User details not found', 200);
    }


    public function view($id)
    {
        $site = Sites::with('siteSafetyOfficer','supervisor','forman')->where('id', $id)->where('del', '0')->first();
        if (!$site) {
            return $this->message('Site not found or deleted', 404);
        }
        $userTable = $this->getUserTable();
        if (
            $userTable == "customer" && ($site->workspace_id != auth()->user()->current_workspace_id || $site->customer_id != auth()->user()->id)
        ) {
            return $this->message('You do not have access to this Site', 403);
        }

        if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('You do not have access to this Site', 403);
        }
        // $site = Sites::where('id', $id)->first();
        $sitehistories = SiteHistory::where('site_id', $id)->get();
        $company = Company::where('id', $site->company_client)->first();
        $project = Project::where('id', $site->project_id)->first();

        if ($site->created_by == 0) {
            $created_by = User::where('id', $site->customer_id)->select('name as name')->first();
        }else {
            $created_by = EmpPersonalDetails::where('emp_id', $site->created_by)->first();
        }
        
        $empsuper = EmpPersonalDetails::where('emp_id', $site->supervisor_id)->first();

        $site['company'] = $company;
        $site['project'] = $project;
        $site['created_by'] = $created_by;
        $site['sitehistories'] = $sitehistories;
        
        // Load site documents with roles
        $siteDocuments = SiteDocument::where('site_id', $id)
            ->orderBy('created_at', 'desc')
            ->get();
        
            // Load role details, document type, and subcontractor for each document
        foreach ($siteDocuments as $doc) {
            if ($doc->role_ids && is_array($doc->role_ids)) {
                $doc->roles = Role::whereIn('id', $doc->role_ids)->get();
            } else {
                $doc->roles = collect([]);
            }
            
            // Load document type relationship
            if ($doc->document_type) {
                $doc->documentTypeDetail = DocumentType::find($doc->document_type);
            }

            // Load subcontractor relationship
            if ($doc->subcontractors) {
                $doc->subcontractorDetail = LinkManagement::with(['roleId', 'tierId'])->find($doc->subcontractors);
            }
        }
        
        $site['documents'] = $siteDocuments;

        return response()->json([
            'data' => $site
        ]);
        // return view('Sites.view', compact('site','company','emppersonal','empsuper','sitehistories'));
    }

    /**
     * Create/Store a site document
     */
    public function siteDocument(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'title' => 'required|string|max:255',
                'site_id' => 'required|integer|exists:sites,id',
                'document_type' => 'required|integer|exists:document_types,id',
                'roles' => 'nullable|array', // Array of role IDs
                'roles.*' => 'integer|exists:roles,id',
                'subcontractors' => 'nullable|array',
                'subcontractors.*' => 'integer|exists:link_management,id',
                'sign_requires' => 'nullable|integer|in:0,1',
                'signature_timing' => 'nullable|integer|in:0,1'
            ]);

            if ($validator->fails()) {
                return $this->message($validator->errors(), 422);
            }

            $userTable = $this->getUserTable();
            $site = Sites::find($request->site_id);
            
            if (!$site) {
                return $this->message('Site not found', 404);
            }

            // Check access control
            if ($userTable == "customer" && ($site->customer_id != auth()->id() || $site->workspace_id != auth()->user()->current_workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }
            if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }

            // Handle file upload
            $document = null;
            if ($request->hasFile('file')) {
                $document = $this->handleFileImageUpload($request, 'SiteDocuments');
            }

            // Prepare data for mass assignment
            $documentData = [
                'title' => $request->title,
                'site_id' => $request->site_id,
                'document_type' => $request->document_type ?? null,
                'uploaded_by' => auth()->user()->id,
                'uploaded_by_type' => $userTable, // Set 'customer' or 'emp'
                'sign_requires' => $request->sign_requires ?? null,
                'signature_timing' => $request->signature_timing ?? null,
                'file' => $document['path'] ?? null,
                'role_ids' => $request->has('roles') && is_array($request->roles) ? $request->roles : null,
                'subcontractors' => $request->has('subcontractors') && is_array($request->subcontractors) ? $request->subcontractors : null,
            ];

            // Create site document using mass assignment (this will properly apply JSON casts)
            $siteDocument = SiteDocument::create($documentData);

            // Get uploaded by user info
            if ($userTable === 'emp') {
                $added_by = EmpPersonalDetails::where('emp_id', Auth::user()->id)
                    ->select('first_name', 'middle_name', 'last_name')
                    ->first();
                $fullName = trim(($added_by->first_name ?? '') . " " . ($added_by->middle_name ?? '') . " " . ($added_by->last_name ?? ''));
            } else {
                $added_by = User::where('id', Auth::user()->id)->select('name')->first();
                $fullName = $added_by->name ?? '';
            }

            // Create site history entry
            $description = "<a href='" . url('/user-profile/' . Auth::user()->id) . "' style='text-transform: capitalize;' role='button' class='primary text-decoration-none'> {$fullName} </a> uploaded a new document: '{$siteDocument->title}' to site: {$site->title}.";
            $history = [
                'site_id' => $siteDocument->site_id,
                'description' => $description,
                'updated_by' => auth()->user()->id,
            ];
            SiteHistory::create($history);

            // Load role details, document type, and subcontractor for the document
            if ($siteDocument->role_ids && is_array($siteDocument->role_ids)) {
                $siteDocument->roles = Role::whereIn('id', $siteDocument->role_ids)->get();
            } else {
                $siteDocument->roles = collect([]);
            }
            
            // Load document type relationship
            if ($siteDocument->document_type) {
                $siteDocument->documentTypeDetail = DocumentType::find($siteDocument->document_type);
            }

            // Load subcontractor relationships
            if ($siteDocument->subcontractors && is_array($siteDocument->subcontractors)) {
                $siteDocument->subcontractorsDetail = LinkManagement::with(['roleId', 'tierId'])->whereIn('id', $siteDocument->subcontractors)->get();
            } else {
                $siteDocument->subcontractorsDetail = collect([]);
            }

            return $this->success($siteDocument, 'Site document saved successfully', 200);
        } catch (\Exception $e) {
            return $this->message('Error saving site document: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Get all documents for a site
     */
    public function siteDocuments(Request $request, $site_id)
    {
        try {
            $site = Sites::find($site_id);
            
            if (!$site) {
                return $this->message('Site not found', 404);
            }

            $userTable = $this->getUserTable();
            
            // Check access control
            if ($userTable == "customer" && ($site->customer_id != auth()->id() || $site->workspace_id != auth()->user()->current_workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }
            if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }

            // Get documents with uploaded by info and roles
            $documents = SiteDocument::where('site_id', $site_id)
                ->orderBy('created_at', 'desc')
                ->get();

            // Add uploaded_by user information and roles
            foreach ($documents as $doc) {
                // Get user info based on uploaded_by_type
                if ($doc->uploaded_by_type == 'customer') {
                    $user = User::find($doc->uploaded_by);
                    if ($user) {
                        $doc->uploaded_by_name = $user->name;
                    }
                } else {
                    $emp = EmpPersonalDetails::where('emp_id', $doc->uploaded_by)->first();
                    if ($emp) {
                        $doc->uploaded_by_name = trim(($emp->first_name ?? '') . " " . ($emp->middle_name ?? '') . " " . ($emp->last_name ?? ''));
                    }
                }

                // Load roles from role_ids JSON array
                if ($doc->role_ids && is_array($doc->role_ids)) {
                    $doc->roles = Role::whereIn('id', $doc->role_ids)->get();
                } else {
                    $doc->roles = collect([]);
                }
                
                // Load document type relationship
                if ($doc->document_type) {
                    $doc->documentTypeDetail = DocumentType::find($doc->document_type);
                }

                // Load subcontractor relationships
                if ($doc->subcontractors && is_array($doc->subcontractors)) {
                    $doc->subcontractorsDetail = LinkManagement::with(['roleId', 'tierId'])->whereIn('id', $doc->subcontractors)->get();
                } else {
                    $doc->subcontractorsDetail = collect([]);
                }
            }

            return $this->success($documents, 'Site documents retrieved successfully', 200);
        } catch (\Exception $e) {
            return $this->message('Error retrieving site documents: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Update a site document
     */
    public function updateSiteDocument(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'document_id' => 'required|exists:site_documents,id',
                'title' => 'required|string|max:255',
                'document_type' => 'required|integer|exists:document_types,id',
                'roles' => 'nullable|array',
                'roles.*' => 'integer|exists:roles,id',
                'subcontractors' => 'nullable|array',
                'subcontractors.*' => 'integer|exists:link_management,id'
            ]);

            if ($validator->fails()) {
                return $this->message($validator->errors(), 422);
            }

            $siteDocument = SiteDocument::find($request->document_id);
            
            if (!$siteDocument) {
                return $this->message('Site document not found', 404);
            }

            $site = Sites::find($siteDocument->site_id);
            
            if (!$site) {
                return $this->message('Site not found for the document', 404);
            }

            $userTable = $this->getUserTable();
            
            // Check access control
            if ($userTable == "customer" && ($site->customer_id != auth()->id() || $site->workspace_id != auth()->user()->current_workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }
            if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }

            $oldTitle = $siteDocument->title;

            // Update document fields
            $siteDocument->title = $request->title;
            if ($request->has('document_type')) {
                $siteDocument->document_type = $request->document_type;
            }
            if ($request->has('sign_requires')) {
                $siteDocument->sign_requires = $request->sign_requires;
            }
            if ($request->has('signature_timing')) {
                $siteDocument->signature_timing = $request->signature_timing;
            }

            // Handle file upload if provided
            if ($request->hasFile('file')) {
                // Delete old file if exists
                if ($siteDocument->file && file_exists(public_path($siteDocument->file))) {
                    unlink(public_path($siteDocument->file));
                }
                $document = $this->handleFileImageUpload($request, 'SiteDocuments');
                $siteDocument->file = $document['path'] ?? null;
            }

            // Update role IDs as JSON array if provided
            if ($request->has('roles') && is_array($request->roles)) {
                $siteDocument->role_ids = $request->roles;
            }

            // Update subcontractor IDs as JSON array if provided
            if ($request->has('subcontractors') && is_array($request->subcontractors)) {
                $siteDocument->subcontractors = $request->subcontractors;
            }

            $siteDocument->save();

            // Get user info for history
            if ($userTable === 'emp') {
                $added_by = EmpPersonalDetails::where('emp_id', Auth::user()->id)
                    ->select('first_name', 'middle_name', 'last_name')
                    ->first();
                $fullName = trim(($added_by->first_name ?? '') . " " . ($added_by->middle_name ?? '') . " " . ($added_by->last_name ?? ''));
            } else {
                $added_by = User::where('id', Auth::user()->id)->select('name')->first();
                $fullName = $added_by->name ?? '';
            }

            // Create site history entry
            $description = "<a href='" . url('/user-profile/' . Auth::user()->id) . "' style='text-transform: capitalize;' role='button' class='primary text-decoration-none'> {$fullName} </a> updated the document title from: '{$oldTitle}' to: '{$request->title}'.";
            $history = [
                'site_id' => $site->id,
                'description' => $description,
                'updated_by' => auth()->user()->id,
            ];
            SiteHistory::create($history);

            // Load role details, document type, and subcontractor for the document
            if ($siteDocument->role_ids && is_array($siteDocument->role_ids)) {
                $siteDocument->roles = Role::whereIn('id', $siteDocument->role_ids)->get();
            } else {
                $siteDocument->roles = collect([]);
            }
            
            // Load document type relationship
            if ($siteDocument->document_type) {
                $siteDocument->documentTypeDetail = DocumentType::find($siteDocument->document_type);
            }

            // Load subcontractor relationships
            if ($siteDocument->subcontractors && is_array($siteDocument->subcontractors)) {
                $siteDocument->subcontractorsDetail = LinkManagement::with(['roleId', 'tierId'])->whereIn('id', $siteDocument->subcontractors)->get();
            } else {
                $siteDocument->subcontractorsDetail = collect([]);
            }

            return $this->success($siteDocument, 'Site document updated successfully', 200);
        } catch (\Exception $e) {
            return $this->message('Error updating site document: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Delete a site document
     */
    public function deleteSiteDocument(Request $request)
    {
        try {
            $siteDocument = SiteDocument::find($request->id);
            
            if (!$siteDocument) {
                return $this->message('Document not found', 404);
            }

            $site = Sites::find($siteDocument->site_id);
            
            if (!$site) {
                return $this->message('Site not found for the document', 404);
            }

            $userTable = $this->getUserTable();
            
            // Check access control
            if ($userTable == "customer" && ($site->customer_id != auth()->id() || $site->workspace_id != auth()->user()->current_workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }
            if ($userTable == "emp" && ($site->customer_id != auth()->user()->customer_id || $site->workspace_id != auth()->user()->workspace_id)) {
                return $this->message('Unauthorized access to this site', 403);
            }

            // Get user info for history
            if ($userTable === 'emp') {
                $added_by = EmpPersonalDetails::where('emp_id', Auth::user()->id)
                    ->select('first_name', 'middle_name', 'last_name')
                    ->first();
                $fullName = trim(($added_by->first_name ?? '') . " " . ($added_by->middle_name ?? '') . " " . ($added_by->last_name ?? ''));
            } else {
                $added_by = User::where('id', Auth::user()->id)->select('name')->first();
                $fullName = $added_by->name ?? '';
            }

            // Create site history entry
            $description = "<a href='" . url('/user-profile/' . Auth::user()->id) . "' style='text-transform: capitalize;' role='button' class='primary text-decoration-none'> {$fullName} </a> deleted the document: '{$siteDocument->title}' from site: {$site->title}.";
            $history = [
                'site_id' => $site->id,
                'description' => $description,
                'updated_by' => auth()->user()->id,
            ];
            SiteHistory::create($history);

            // Delete the file if it exists
            if ($siteDocument->file && file_exists(public_path($siteDocument->file))) {
                unlink(public_path($siteDocument->file));
            }

            // Delete the document (roles are stored in JSON, no need to detach)
            $siteDocument->delete();

            return $this->message('Document deleted successfully', 200);
        } catch (\Exception $e) {
            return $this->message('Error deleting site document: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Get all document types for dropdown
     */
    public function getDocumentTypes()
    {
        try {
            $documentTypes = DocumentType::where('del', '0')
                ->orderBy('title', 'asc')
                ->select('id', 'title')
                ->get();

            return $this->success($documentTypes, 'Document types retrieved successfully', 200);
        } catch (\Exception $e) {
            return $this->message('Error retrieving document types: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Helper method to get employee's role ID
     * Uses withoutGlobalScopes to avoid filtering issues
     */
    private function getEmployeeRoleId($employeeId, $customerId, $workspaceId)
    {
        $employee = EmpCompanyDetails::withoutGlobalScopes()
            ->where('id', $employeeId)
            ->where('customer_id', $customerId)
            ->where('workspace_id', $workspaceId)
            ->first();
        
        if (!$employee || !$employee->access_role) {
            return null;
        }

        $role = Role::where('code', $employee->access_role)
            ->where('del', '0')
            ->first();

        return $role ? $role->id : null;
    }

    /**
     * Helper method to check if document applies to employee based on role_ids
     * Returns true if document should be shown to employee
     */
    private function documentAppliesToEmployee($document, $employeeRoleId)
    {
        // Document must have role_ids defined
        if (!$document->role_ids || !is_array($document->role_ids) || count($document->role_ids) == 0) {
            return false;
        }

        // Employee must have a role and it must be in document's role_ids
        if (!$employeeRoleId || !in_array($employeeRoleId, $document->role_ids)) {
            return false;
        }

        return true;
    }

    /**
     * Helper method to check if employee has signed a document
     */
    private function hasEmployeeSignedDocument($siteDocumentId, $employeeId, $siteId)
    {
        return SiteDocumentSignature::where('site_document_id', $siteDocumentId)
            ->where('employee_id', $employeeId)
            ->where('site_id', $siteId)
            ->exists();
    }

    /**
     * Helper method to build document response data
     */
    private function buildDocumentResponseData($document, $includeSignedStatus = false, $employeeId = null)
    {
        $documentData = [
            'id' => $document->id,
            'title' => $document->title,
            'file' => $document->file,
            'document_type' => $document->document_type,
            'site_id' => $document->site_id,
            'sign_requires' => $document->sign_requires,
        ];

        // Load document type detail
        if ($document->document_type) {
            $documentData['documentTypeDetail'] = DocumentType::find($document->document_type);
        }

        // Add signed status if requested
        if ($includeSignedStatus && $employeeId) {
            $signature = SiteDocumentSignature::where('site_document_id', $document->id)
                ->where('employee_id', $employeeId)
                ->where('site_id', $document->site_id)
                ->first();
            
            if ($signature) {
                $documentData['is_signed_required'] = 0; // Already signed
                $documentData['signed_at'] = $signature->signed_at;
            } else {
                $documentData['is_signed_required'] = 1; // Required to sign
            }
        }

        return $documentData;
    }

    /**
     * Helper method to check if employee needs to sign documents before check-in
     * Returns documents that require signature at check-in (signature_timing = 0)
     */
    public function getRequiredDocumentsForCheckIn($siteId, $employeeId, $customerId, $workspaceId)
    {
        $employeeRoleId = $this->getEmployeeRoleId($employeeId, $customerId, $workspaceId);

        // Get documents that require signature at check-in
        $documents = SiteDocument::where('site_id', $siteId)
            ->where('sign_requires', 1)
            ->where('signature_timing', 0)
            ->get();

        $requiredDocuments = [];

        foreach ($documents as $document) {
            // Check if document applies to this employee based on role
            if (!$this->documentAppliesToEmployee($document, $employeeRoleId)) {
                continue;
            }

            // Only return documents that haven't been signed yet
            if (!$this->hasEmployeeSignedDocument($document->id, $employeeId, $siteId)) {
                $documentData = $this->buildDocumentResponseData($document);
                $documentData['is_signed_required'] = 1; // Not signed yet - signature required
                $requiredDocuments[] = $documentData;
            }
        }

        return $requiredDocuments;
    }

    /**
     * API: Check if documents need signing before check-in
     * Called during check-in process to interrupt if signatures required
     */
    public function checkDocumentsBeforeCheckIn(Request $request)
    {
        try {
            $validator = Validator::make($request->all(), [
                'site_id' => 'required|integer|exists:sites,id',
                'employee_id' => 'required|integer|exists:emp_company_details,id',
            ]);

            if ($validator->fails()) {
                return $this->message($validator->errors(), 422);
            }

            $userTable = $this->getUserTable();
            $customerId = $userTable == "customer" ? auth()->id() : auth()->user()->customer_id;
            $workspaceId = $userTable == "customer" ? auth()->user()->current_workspace_id : auth()->user()->workspace_id;

            // Get and validate site
            $site = Sites::find($request->site_id);
            if (!$site) {
                return $this->message('Site not found', 404);
            }

            // Check site access control
            if ($site->customer_id != $customerId || $site->workspace_id != $workspaceId) {
                return $this->message('Unauthorized access to this site', 403);
            }

            // Get and validate employee
            $employee = EmpCompanyDetails::withoutGlobalScopes()->find($request->employee_id);
            if (!$employee) {
                return $this->message('Employee not found', 404);
            }

            // Check employee access control
            if ($employee->customer_id != $customerId || $employee->workspace_id != $workspaceId) {
                return $this->message('Unauthorized access to this employee', 403);
            }

            // Get required documents for check-in
            $requiredDocuments = $this->getRequiredDocumentsForCheckIn(
                $request->site_id,
                $request->employee_id,
                $customerId,
                $workspaceId
            );

            $response = [
                'requires_signature' => count($requiredDocuments) > 0,
                'documents' => $requiredDocuments,
                'message' => count($requiredDocuments) > 0 
                    ? 'Please sign the required documents before checking in.' 
                    : 'No documents require signature before check-in.'
            ];

            return $this->success($response, 'Documents check completed', 200);
        } catch (\Exception $e) {
            return $this->message('Error checking documents: ' . $e->getMessage(), 500);
        }
    }

    /**
     * API: Submit document signature
     */
    public function submitDocumentSignature(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'site_document_id' => 'required|integer|exists:site_documents,id',
            'employee_id' => 'required|integer|exists:emp_company_details,id',
            'site_id' => 'required|integer|exists:sites,id',
            'signature' => 'required', // Can be file or base64 string
        ]);

        if ($validator->fails()) {
            return $this->message($validator->errors(), 422);
        }

        $userTable = $this->getUserTable();
        $validatedData = $validator->validated();
        $employeeId = $validatedData['employee_id'];
        $siteId = $validatedData['site_id'];
        $siteDocumentId = $validatedData['site_document_id'];
        
        // Get customer and workspace IDs
        $customerId = $userTable == "customer" ? auth()->id() : auth()->user()->customer_id;
        $workspaceId = $userTable == "customer" ? auth()->user()->current_workspace_id : auth()->user()->workspace_id;

        // Get employee (without global scopes for consistency)
        $employee = EmpCompanyDetails::withoutGlobalScopes()->find($employeeId);
        if (!$employee) {
            return $this->message('Employee not found', 404);
        }

        // Validate employee access control
        if ($userTable == "customer" && ($employee->customer_id != $customerId || $employee->workspace_id != $workspaceId)) {
            return $this->message('Unauthorized access to this employee', 403);
        }
        if ($userTable == "emp" && ($employee->customer_id != $customerId || $employee->workspace_id != $workspaceId)) {
            return $this->message('Unauthorized access to this employee', 403);
        }

        // Get site and validate access
        $site = Sites::find($siteId);
        if (!$site) {
            return $this->message('Site not found', 404);
        }

        if ($site->customer_id != $customerId || $site->workspace_id != $workspaceId) {
            return $this->message('Unauthorized access to this site', 403);
        }

        // Get site document
        $siteDocument = SiteDocument::find($siteDocumentId);
        if (!$siteDocument) {
            return $this->message('Site document not found', 404);
        }

        if ($siteDocument->site_id != $siteId) {
            return $this->message('Document does not belong to this site', 422);
        }

        // Validate that employee's role matches document's role_ids
        if (!$this->documentAppliesToEmployee($siteDocument, $this->getEmployeeRoleId($employeeId, $customerId, $workspaceId))) {
            return $this->message('This document is not applicable to your role', 403);
        }

        // Handle signature - can be file upload or base64 string
        $signatureData = null;
        if ($request->hasFile('signature')) {
            $uploadResult = $this->handleFileImageUpload($request, 'SiteDocumentSignatures');
            $signatureData = $uploadResult['path'] ?? null;
            if (!$signatureData) {
                return $this->message('Signature file upload failed', 422);
            }
        } else {
            $signatureData = $validatedData['signature'];
        }

        // Check if already signed
        if ($this->hasEmployeeSignedDocument($siteDocumentId, $employeeId, $siteId)) {
            return $this->message('Document already signed by this employee', 422);
        }

        // Create signature record
        $signature = SiteDocumentSignature::create([
            'site_document_id' => $siteDocumentId,
            'employee_id' => $employeeId,
            'site_id' => $siteId,
            'customer_id' => $customerId,
            'workspace_id' => $workspaceId,
            'signature' => $signatureData,
            'signed_at' => now(),
        ]);

        // Send notification to customer about the signature
        try {
            // Get employee personal details for notification message
            $employeePersonal = EmpPersonalDetails::where('emp_id', $employeeId)->first();
            
            $employeeName = 'Employee';
            if ($employeePersonal) {
                $employeeName = trim(
                    ($employeePersonal->first_name ?? '') . ' ' . 
                    ($employeePersonal->middle_name ?? '') . ' ' . 
                    ($employeePersonal->last_name ?? '')
                );
                // Fallback to email if name is empty
                if (empty(trim($employeeName))) {
                    $employeeName = $employee->employee_email ?? 'Employee';
                }
            }
            
            $notificationTitle = "Site Document Signed";
            $notificationMessage = "Employee {$employeeName} has signed the document \"{$siteDocument->title}\" at " . now()->format('d-m-Y H:i:s');
            
            $this->save_notifications(
                $notificationTitle,
                $notificationMessage,
                config('constants.employee_types.employee'),
                $employeeId, // sender_id (employee who signed)
                config('constants.employee_types.customer'),
                $customerId, // receiver_id (customer who should be notified)
                config('constants.notification_types.signature_added'),
                $customerId,
                $workspaceId
            );
        } catch (\Exception $e) {
            // Log error but don't fail the signature submission
            Log::error('SiteController:submitDocumentSignature: Error sending signature notification: ' . $e->getMessage(), ['employeeId' => $employeeId, 'siteDocumentId' => $siteDocumentId, 'siteId' => $siteId, 'customerId' => $customerId, 'workspaceId' => $workspaceId, 'error' => $e->getMessage()]);
        }

        // Load relationships
        $signature->load(['siteDocument', 'empPersonalDetails', 'site']);

        return $this->message('Saved Successfully', 200);
    }

    /**
     * API: Get documents that can be signed later (after check-in)
     */
    public function getDocumentsToSignLater(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'employee_id' => 'required|integer|exists:emp_company_details,id',
            'site_id' => 'required|integer|exists:sites,id', // Optional: filter by site
        ]);
        
        if ($validator->fails()) {
            return $this->message($validator->errors(), 422);
        }
        
        $userTable = $this->getUserTable();
        
        // Get employee without global scopes to avoid filtering issues
        $employee = EmpCompanyDetails::withoutGlobalScopes()->find($request->employee_id);
        
        if (!$employee) {
            return $this->message('Employee not found', 404);
        }
        
        // Check access control
        if ($userTable == "customer" && ($employee->customer_id != auth()->id() || $employee->workspace_id != auth()->user()->current_workspace_id)) {
            return $this->message('Unauthorized access to this employee', 403);
        }
        if ($userTable == "emp" && ($employee->customer_id != auth()->user()->customer_id || $employee->workspace_id != auth()->user()->workspace_id)) {
            return $this->message('Unauthorized access to this employee', 403);
        }
        
        $customerId = $userTable == "customer" ? auth()->id() : auth()->user()->customer_id;
        $workspaceId = $userTable == "customer" ? auth()->user()->current_workspace_id : auth()->user()->workspace_id;
        $employeeRoleId = $this->getEmployeeRoleId($request->employee_id, $customerId, $workspaceId);

        // Get all site IDs that belong to this customer/workspace
        $siteIds = Sites::where('customer_id', $customerId)
            ->where('workspace_id', $workspaceId)
            ->pluck('id')
            ->toArray();
        
        if (empty($siteIds)) {
            return $this->success([], 'No documents found', 200);
        }

        // Get documents that require signature and can be signed later
        // Filter by customer_id and workspace_id through sites relationship
        $documents = SiteDocument::whereIn('site_id', $siteIds)
            ->where('sign_requires', 1)
            ->where('signature_timing', 1)
            ->get();

        $documentsToSign = [];

        foreach ($documents as $document) {
            // Check if document applies to this employee based on role
            if (!$this->documentAppliesToEmployee($document, $employeeRoleId)) {
                continue;
            }

            // Build document data with signed status
            $documentData = $this->buildDocumentResponseData(
                $document,
                true, // Include signed status
                $request->employee_id
            );

            $documentsToSign[] = $documentData;
        }

        return $this->success($documentsToSign, 'Documents retrieved successfully', 200);
    }
    public function viewSiteDocumentSignatures($id)
    {
        try {
            $validator = Validator::make(['id' => $id], [
                'id' => 'required|integer|exists:site_documents,id',
            ]);

            if ($validator->fails()) {
                return $this->message($validator->errors()->first(), 422);
            }

            $userTable = $this->getUserTable();
            $customerId = $userTable == "customer" ? auth()->id() : auth()->user()->customer_id;
            $workspaceId = $userTable == "customer" ? auth()->user()->current_workspace_id : auth()->user()->workspace_id;

            // Get site document with relationships
            $siteDocument = SiteDocument::with(['site', 'documentType'])
                ->where('id', $id)
                ->first();

            if (!$siteDocument) {
                return $this->message('Site document not found', 404);
            }

            // Validate site access (site document belongs to a site, which has customer/workspace)
            $site = Sites::find($siteDocument->site_id);
            if (!$site) {
                return $this->message('Site not found', 404);
            }

            if ($site->customer_id != $customerId || $site->workspace_id != $workspaceId) {
                return $this->message('Unauthorized access to this document', 403);
            }

            // Convert site document to array to get formatted dates from BaseModel
            $documentData = $siteDocument->toArray();

            // Enrich uploaded_by information
            if ($siteDocument->uploaded_by) {
                if ($siteDocument->uploaded_by_type == 'customer') {
                    $user = User::find($siteDocument->uploaded_by);
                    if ($user) {
                        $documentData['uploaded_by_detail'] = [
                            'id' => $user->id,
                            'name' => $user->name,
                            'type' => 'customer'
                        ];
                    } else {
                        $documentData['uploaded_by_detail'] = null;
                    }
                } else {
                    // Employee
                    $emp = EmpPersonalDetails::where('emp_id', $siteDocument->uploaded_by)->first();
                    if ($emp) {
                        $empName = $emp->first_name . ' ' . $emp->middle_name . ' ' . $emp->last_name;
                        $documentData['uploaded_by_detail'] = [
                            'id' => $siteDocument->uploaded_by,
                            'name' => $empName,
                            'type' => 'emp'
                        ];
                    } else {
                        $documentData['uploaded_by_detail'] = null;
                    }
                }
            } else {
                $documentData['uploaded_by_detail'] = null;
            }

            // Replace role_ids with role details (titles)
            if ($siteDocument->role_ids && is_array($siteDocument->role_ids) && count($siteDocument->role_ids) > 0) {
                $roles = Role::whereIn('id', $siteDocument->role_ids)
                    ->where('del', '0')
                    ->select('id', 'title', 'code')
                    ->get();
                
                $documentData['roles'] = $roles->map(function ($role) {
                    return [
                        'id' => $role->id,
                        'title' => $role->title,
                        'code' => $role->code
                    ];
                })->toArray();
            } else {
                $documentData['roles'] = [];
            }
            // Remove role_ids from response as we now have roles with details
            unset($documentData['role_ids']);

            // Replace subcontractors IDs with subcontractor details (titles)
            // Handle both array and single value cases
            $subcontractorIds = $siteDocument->subcontractors;
            if (!is_array($subcontractorIds) && $subcontractorIds) {
                // If it's a single value, convert to array
                $subcontractorIds = [$subcontractorIds];
            }
            
            if ($subcontractorIds && is_array($subcontractorIds) && count($subcontractorIds) > 0) {
                $subcontractors = LinkManagement::whereIn('id', $subcontractorIds)
                    ->where('customer_id', $customerId)
                    ->where('workspace_id', $workspaceId)
                    ->select('id', 'title', 'name', 'company_name')
                    ->get();
                
                $documentData['subcontractors'] = $subcontractors->map(function ($subcontractor) {
                    return [
                        'id' => $subcontractor->id,
                        'title' => $subcontractor->title ?? $subcontractor->name ?? $subcontractor->company_name,
                        'name' => $subcontractor->name,
                        'company_name' => $subcontractor->company_name
                    ];
                })->toArray();
            } else {
                $documentData['subcontractors'] = [];
            }

            // Get all signatures for this document with employee details (optimized with joins)
            $signaturesData = DB::table('site_document_signatures')
                ->leftJoin('emp_personal_details', 'site_document_signatures.employee_id', '=', 'emp_personal_details.emp_id')
                ->leftJoin('emp_company_details', 'site_document_signatures.employee_id', '=', 'emp_company_details.id')
                ->leftJoin('roles', function ($join) {
                    $join->on('emp_company_details.access_role', '=', 'roles.code')
                        ->where('roles.del', '0');
                })
                ->where('site_document_signatures.site_document_id', $id)
                ->where('site_document_signatures.customer_id', $customerId)
                ->where('site_document_signatures.workspace_id', $workspaceId)
                ->select(
                    'site_document_signatures.*',
                    'emp_personal_details.first_name as employee_first_name',
                    'emp_personal_details.middle_name as employee_middle_name',
                    'emp_personal_details.last_name as employee_last_name',
                    'emp_personal_details.image as image',
                    'emp_company_details.access_role as role_code',
                    'roles.title as role_title'
                )
                ->get();

            // Get user date-time format from BaseModel (includes both date and time)
            $siteDocumentSignatureModel = new SiteDocumentSignature();
            $userDateTimeFormat = $siteDocumentSignatureModel->getUserDateTimeFormat();

            // Format signature data
            $signatureCollection = $signaturesData->map(function ($signature) use ($userDateTimeFormat) {
                $signatureData = (array) $signature;
                
                // Format created_at according to system settings (with date and time)
                if (isset($signatureData['created_at']) && $signatureData['created_at']) {
                    try {
                        $carbonDate = \Carbon\Carbon::parse($signatureData['created_at']);
                        $signatureData['created_at'] = $carbonDate->format($userDateTimeFormat);
                        $signatureData['updated_at'] = $carbonDate->format($userDateTimeFormat);
                    } catch (\Exception $e) {
                        Log::warning("Failed to format created_at for signature: " . $e->getMessage());
                        // Keep original value if parsing fails
                    }
                }
                
                // Build full employee name
                $signatureData['employee_name'] = trim(
                    ($signature->employee_first_name ?? '') . ' ' .
                    ($signature->employee_middle_name ?? '') . ' ' .
                    ($signature->employee_last_name ?? '')
                );

                // Ensure null values are handled
                $signatureData['employee_first_name'] = $signature->employee_first_name ?? null;
                $signatureData['employee_middle_name'] = $signature->employee_middle_name ?? null;
                $signatureData['employee_last_name'] = $signature->employee_last_name ?? null;
                $signatureData['role_code'] = $signature->role_code ?? null;
                $signatureData['role_title'] = $signature->role_title ?? null;

                return $signatureData;
            });

            // Calculate widget counts for required signs
            $widgetCounts = $this->calculateDocumentSignatureWidgetCounts(
                $siteDocument,
                $siteDocument->site_id,
                $customerId,
                $workspaceId
            );

            // Get list of employees who haven't signed yet
            $notSignedEmployees = $this->getNotSignedEmployees(
                $siteDocument,
                $siteDocument->site_id,
                $customerId,
                $workspaceId
            );

            // Prepare response data
            $responseData = [
                'site_document' => $documentData,
                'signatures' => $signatureCollection->values()->toArray(),
                'widget_counts' => $widgetCounts,
                'not_signed_employees' => $notSignedEmployees,
            ];

            return $this->success($responseData, 'Site document signatures retrieved successfully', 200);
        } catch (\Exception $e) {
            return $this->message('An error occurred while retrieving signature data: ' . $e->getMessage(), 500);
        }
    }

    /**
     * Calculate widget counts for document signature requirements
     * Returns: total_required_signs, signed_signs, pending_signs
     */
    private function calculateDocumentSignatureWidgetCounts($siteDocument, $siteId, $customerId, $workspaceId)
    {
        try {
            // If document doesn't require signatures, return zeros
            if (!$siteDocument->sign_requires || $siteDocument->sign_requires != 1) {
                return [
                    'total_required_signs' => 0,
                    'signed_signs' => 0,
                    'pending_signs' => 0
                ];
            }

            // Get role_ids from document
            $roleIds = $siteDocument->role_ids;
            if (!is_array($roleIds) || count($roleIds) == 0) {
                // No roles specified, so no required signs
                return [
                    'total_required_signs' => 0,
                    'signed_signs' => 0,
                    'pending_signs' => 0
                ];
            }

            // Get role codes from role_ids (roles are now global)
            $roles = Role::whereIn('id', $roleIds)
                ->where('del', '0')
                ->pluck('code')
                ->toArray();

            if (empty($roles)) {
                return [
                    'total_required_signs' => 0,
                    'signed_signs' => 0,
                    'pending_signs' => 0
                ];
            }

            // Get employees with matching roles who have roster assignments for this site
            $requiredEmployeeIds = DB::table('emp_company_details')
                ->join('roster_assigns', function ($join) use ($siteId, $customerId, $workspaceId) {
                    $join->on('emp_company_details.id', '=', 'roster_assigns.assign_to')
                        ->where('roster_assigns.site_id', $siteId)
                        ->where('roster_assigns.customer_id', $customerId)
                        ->where('roster_assigns.workspace_id', $workspaceId);
                })
                ->whereIn('emp_company_details.access_role', $roles)
                ->where('emp_company_details.customer_id', $customerId)
                ->where('emp_company_details.workspace_id', $workspaceId)
                ->where('emp_company_details.del', '0')
                ->distinct()
                ->pluck('emp_company_details.id')
                ->toArray();

            $totalRequiredSigns = count($requiredEmployeeIds);

            // Count signed signatures for this document and site (distinct employees)
            $signedSigns = DB::table('site_document_signatures')
                ->where('site_document_id', $siteDocument->id)
                ->where('site_id', $siteId)
                ->where('customer_id', $customerId)
                ->where('workspace_id', $workspaceId)
                ->select(DB::raw('COUNT(DISTINCT employee_id) as count'))
                ->first()
                ->count ?? 0;

            $pendingSigns = max(0, $totalRequiredSigns - $signedSigns);

            return [
                'total_required_signs' => $totalRequiredSigns,
                'signed_signs' => $signedSigns,
                'pending_signs' => $pendingSigns
            ];
        } catch (\Exception $e) {
            Log::error('SiteController:calculateDocumentSignatureWidgetCounts: Error calculating widget counts: ' . $e->getMessage());
            // Return zeros on error to prevent breaking the API
            return [
                'total_required_signs' => 0,
                'signed_signs' => 0,
                'pending_signs' => 0
            ];
        }
    }
    private function getNotSignedEmployees($siteDocument, $siteId, $customerId, $workspaceId)
    {
        try {
            // If document doesn't require signatures, return empty array
            if (!$siteDocument->sign_requires || $siteDocument->sign_requires != 1) {
                return [];
            }

            // Get role_ids from document
            $roleIds = $siteDocument->role_ids;
            if (!is_array($roleIds) || count($roleIds) == 0) {
                return [];
            }

            // Get role codes from role_ids (roles are now global)
            $roles = Role::whereIn('id', $roleIds)
                ->where('del', '0')
                ->pluck('code')
                ->toArray();

            if (empty($roles)) {
                return [];
            }

            // Get all employees with matching roles who have roster assignments for this site
            $requiredEmployeeIds = DB::table('emp_company_details')
                ->join('roster_assigns', function ($join) use ($siteId, $customerId, $workspaceId) {
                    $join->on('emp_company_details.id', '=', 'roster_assigns.assign_to')
                        ->where('roster_assigns.site_id', $siteId)
                        ->where('roster_assigns.customer_id', $customerId)
                        ->where('roster_assigns.workspace_id', $workspaceId);
                })
                ->whereIn('emp_company_details.access_role', $roles)
                ->where('emp_company_details.customer_id', $customerId)
                ->where('emp_company_details.workspace_id', $workspaceId)
                ->where('emp_company_details.del', '0')
                ->distinct()
                ->pluck('emp_company_details.id')
                ->toArray();

            if (empty($requiredEmployeeIds)) {
                return [];
            }

            // Get employees who have already signed this document
            $signedEmployeeIds = SiteDocumentSignature::where('site_document_id', $siteDocument->id)
                ->where('site_id', $siteId)
                ->where('customer_id', $customerId)
                ->where('workspace_id', $workspaceId)
                ->distinct()
                ->pluck('employee_id')
                ->toArray();

            // Find employees who haven't signed yet
            $notSignedEmployeeIds = array_diff($requiredEmployeeIds, $signedEmployeeIds);

            if (empty($notSignedEmployeeIds)) {
                return [];
            }

            // Get detailed information for employees who haven't signed
            $notSignedEmployees = DB::table('emp_company_details')
                ->leftJoin('emp_personal_details', 'emp_company_details.id', '=', 'emp_personal_details.emp_id')
                ->leftJoin('roles', function ($join) {
                    $join->on('emp_company_details.access_role', '=', 'roles.code')
                        ->where('roles.del', '0');
                })
                ->whereIn('emp_company_details.id', $notSignedEmployeeIds)
                ->where('emp_company_details.customer_id', $customerId)
                ->where('emp_company_details.workspace_id', $workspaceId)
                ->where('emp_company_details.del', '0')
                ->select(
                    'emp_company_details.id as employee_id',
                    'emp_company_details.employee_email',
                    'emp_personal_details.first_name',
                    'emp_personal_details.middle_name',
                    'emp_personal_details.last_name',
                    'emp_personal_details.image',
                    'emp_company_details.access_role as role_code',
                    'roles.title as role_title',
                    'roles.id as role_id'
                )
                ->get();

            // Format the response
            return $notSignedEmployees->map(function ($employee) {
                $fullName = trim(
                    ($employee->first_name ?? '') . ' ' .
                    ($employee->middle_name ?? '') . ' ' .
                    ($employee->last_name ?? '')
                );

                // Fallback to email if name is empty
                if (empty(trim($fullName))) {
                    $fullName = $employee->employee_email ?? 'Unknown Employee';
                }

                return [
                    'employee_id' => $employee->employee_id,
                    'employee_name' => $fullName,
                    'first_name' => $employee->first_name ?? null,
                    'middle_name' => $employee->middle_name ?? null,
                    'last_name' => $employee->last_name ?? null,
                    'employee_email' => $employee->employee_email,
                    'image' => $employee->image ?? null,
                    'role' => [
                        'id' => $employee->role_id ?? null,
                        'code' => $employee->role_code ?? null,
                        'title' => $employee->role_title ?? null,
                    ]
                ];
            })->values()->toArray();

        } catch (\Exception $e) {
            Log::error('SiteController:getNotSignedEmployees: Error getting not signed employees: ' . $e->getMessage());
            // Return empty array on error to prevent breaking the API
            return [];
        }
    }
}
