<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\RosterAssign;
use App\Models\EmployeeAttendance;
use App\Models\LeaveRequest;
use App\Models\EmpCompanyDetails;
use App\Models\Sites;
use App\Models\Project;
use App\Models\Company;
use Illuminate\Support\Collection;
use App\Models\EmpTeam;
use App\Models\EmpPersonalDetails;
use Carbon\Carbon;
use App\Models\EmpDocuments;
use App\Models\RequiredDocument;
use App\Models\RequiredDocumentField;
use App\Models\User;
use App\Models\IncidentReport;
use App\Models\Swms;
use App\Models\InspectionPlan;
use App\Models\WhsReport;
use App\Models\WhsqReport;
use App\Models\IncidentSignoff;
use App\Models\SwmsSignature;
use App\Models\InspectionPlanSignature;
use App\Models\WhsSignature;
use App\Models\WhsqSignature;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;

class DashboardController extends Controller
{

    public $current_date;

    public function __construct()
    {
        $this->current_date = date('Y-m-d');
    }

    public function index(Request $request)
    {
        $userTable = $this->getUserTable();
        $workspace_id = 0;
        $customer_id = 0;
        if ($userTable === "customer") {
            $customer_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
            $authPersonalDetails = User::where('id', Auth::user()->id)->first();
        }
        if ($userTable === "emp") {
            $customer_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
            $authPersonalDetails = EmpPersonalDetails::where('emp_id', Auth::user()->id)->first();
        }

        $filter = $request->query('filter', 'ALL');
        $start_date = null;
        $end_date = date('Y-m-d');

        switch ($filter) {
            case '1M':
                $start_date = date('Y-m-d', strtotime('-1 month'));
                break;
            case '6M':
                $start_date = date('Y-m-d', strtotime('-6 months'));
                break;
            case '1Y':
                $start_date = date('Y-m-d', strtotime('-1 year'));
                break;
            case 'All':
            default:
                $start_date = null;
                break;
        }
        $data['chart_data'] = $this->generateChartData($filter, $start_date, $end_date, $customer_id, $workspace_id);

        $data['total_employees']  =   EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)
            ->where('customer_id', $customer_id)
            ->where('workspace_id', $workspace_id)
            ->where('approved', 1)
            ->where('compeleted', 1)
            ->where('status', 1)
            ->count();

        $data['total_clients']    =   Company::where('del', 0)->where('customer_id', $customer_id)->where('workspace_id', $workspace_id)->count();

        $data['total_sites']      =   Sites::where('del', 0)->where('customer_id', $customer_id)->where('workspace_id', $workspace_id)->where('active',1)->count();

        $data['sites']      =   Sites::where('del', 0)->where('customer_id', $customer_id)->where('workspace_id', $workspace_id)->where('active',1)->get();

        $data['total_projects']   =   Project::where('is_deleted', 0)->where('customer_id', $customer_id)->where('workspace_id', $workspace_id)->count();

        // Optimized: Get total roster employees count directly
        $data['total_roster_employees'] = RosterAssign::join('emp_company_details', 'roster_assigns.assign_to', 'emp_company_details.id')
            ->where('emp_company_details.del', 0)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.approved', 1)
            ->where('emp_company_details.compeleted', 1)
            ->where('emp_company_details.status', 1)
            ->where('roster_assigns.schedule_date', $this->current_date)
            ->distinct('roster_assigns.assign_to')
            ->count();

        // Optimized: Get present employees count using join
        $data['total_present'] = EmployeeAttendance::join('roster_assigns', function($join) {
                $join->on('employee_attendances.employee_id', '=', 'roster_assigns.assign_to')
                     ->where('roster_assigns.schedule_date', '=', $this->current_date);
            })
            ->join('emp_company_details', 'roster_assigns.assign_to', 'emp_company_details.id')
            ->where('employee_attendances.date', $this->current_date)
            ->where('emp_company_details.del', 0)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.approved', 1)
            ->where('emp_company_details.compeleted', 1)
            ->where('emp_company_details.status', 1)
            ->where('employee_attendances.workspace_id', $workspace_id)
            ->where('employee_attendances.customer_id', $customer_id)
            ->distinct('employee_attendances.employee_id')
            ->count();

        // Optimized: Get leaves count using join with roster
        $data['total_leaves'] = LeaveRequest::join('roster_assigns', function($join) {
                $join->on('leave_requests.employee_id', '=', 'roster_assigns.assign_to')
                     ->where('roster_assigns.schedule_date', '=', $this->current_date);
            })
            ->join('emp_company_details', 'roster_assigns.assign_to', 'emp_company_details.id')
            ->where(function ($query) {
                $query->where('leave_requests.from', '<=', $this->current_date)
                      ->where('leave_requests.to', '>=', $this->current_date);
            })
            ->where('leave_requests.status', 1)
            ->where('emp_company_details.del', 0)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.approved', 1)
            ->where('emp_company_details.compeleted', 1)
            ->where('emp_company_details.status', 1)
            ->where('leave_requests.workspace_id', $workspace_id)
            ->where('leave_requests.customer_id', $customer_id)
            ->distinct('leave_requests.employee_id')
            ->count();

        $data['total_absent'] = $data['total_roster_employees'] - $data['total_present'] - $data['total_leaves'];

        // Optimized: Only load attendance records for rostered employees
        $data['today_present'] = EmployeeAttendance::join('roster_assigns', function($join) {
                $join->on('employee_attendances.employee_id', '=', 'roster_assigns.assign_to')
                     ->where('roster_assigns.schedule_date', '=', $this->current_date);
            })
            ->where('employee_attendances.date', $this->current_date)
            ->where('employee_attendances.workspace_id', $workspace_id)
            ->where('employee_attendances.customer_id', $customer_id)
            ->select('employee_attendances.*')
            ->with('present_employee_details', 'employee_site', 'present_employee_company_details', 'breaks')
            ->get();

        $employeeCountBySiteName = $data['today_present']->groupBy(function ($attendance) {
            return $attendance->employee_site->title; // Group by site name
        })->map(function ($site) {
            return [
                'employee_count' => $site->count(),  // Count of employees
                'check_in_count' => $site->whereNotNull('check_in')->count(),  // Count of check-ins
                'check_out_count' => $site->whereNotNull('check_out')->count()  // Count of check-outs
            ];
        });
        $data['sites_employee'] = $employeeCountBySiteName->toArray();

        // Optimized: Only load leave records for rostered employees  
        $data['today_leaves'] = LeaveRequest::join('roster_assigns', function($join) {
                $join->on('leave_requests.employee_id', '=', 'roster_assigns.assign_to')
                     ->where('roster_assigns.schedule_date', '=', $this->current_date);
            })
            ->where(function ($query) {
                $query->where('leave_requests.from', '<=', $this->current_date)
                      ->where('leave_requests.to', '>=', $this->current_date);
            })
            ->where('leave_requests.workspace_id', $workspace_id)
            ->where('leave_requests.customer_id', $customer_id)
            ->where('leave_requests.status', 1)
            ->select('leave_requests.*')
            ->with('leave_employee_details', 'leave_employee_company_details')
            ->get();



        $emp_teams = EmpTeam::with(['emp_teams_member' => function ($query) {
            $query->whereHas('roster_assigns');
        }])->where('del', 0)
            ->where('workspace_id', $workspace_id)
            ->where('customer_id', $customer_id)
            ->get();

        $teams_response = [];

        foreach ($emp_teams as $team) {
            $total_employees_with_roster = $team->emp_teams_member->count();
            $total_employees_with_attendance = 0;
            $total_employees_without_attendance = 0;

            foreach ($team->emp_teams_member as $team_member) {
                if ($team_member->roster_assigns && $team_member->roster_assigns->team_employee_attendance) {
                    $total_employees_with_attendance++;
                } else {
                    $total_employees_without_attendance++;
                }
            }

            $teams_response[] = [
                'team_id' => $team->id,
                'team_title' => $team->title,
                'total_employees_with_roster' => $total_employees_with_roster,
                'total_employees_with_attendance' => $total_employees_with_attendance,
                'total_employees_without_attendance' => $total_employees_without_attendance,
            ];
        }
        $subcontractors=\App\Models\LinkManagement::where('customer_id', $customer_id)
        ->where('workspace_id', $workspace_id)
        ->where('status', 1)
        ->get();
        $data['subcontractors'] = $subcontractors;
        $data['total_subcontractors'] = $subcontractors->count();

        $assets = \App\Models\Asset::where('customer_id', $customer_id)
        ->where('workspace_id', $workspace_id)
        ->where('status', 1)
        ->orderBy('purchase_price', 'desc')
        ->take(10)
        ->with('employee_details:emp_id,first_name,middle_name,last_name')
        ->get();
        $data['total_assets'] = $assets->count();
        $data['assets'] = $assets;

        $data['teams'] = $teams_response;

        // Add current day information
        $data['current_date'] = $this->current_date;
        $data['current_day'] = date('l', strtotime($this->current_date)); // Full day name (e.g., Monday)
        $data['formatted_date'] = date('F j, Y', strtotime($this->current_date)); // Human readable format

        return response()->json([
            'message' => 'Dashboard data Fetched Successfully.',
            'data' => $data
        ]);
    }
    private function generateChartData($filter, $start_date, $end_date, $customer_id, $workspace_id)
    {
        $chartData = [];

        if ($start_date === null && $filter === 'All') {
            $earliest_client = Company::where('del', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->min('created_at');

            $earliest_site = Sites::where('del', 0)
                ->where('active',1)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->min('created_at');

            $earliest_project = Project::where('is_deleted', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->min('created_at');

            $earliest_dates = array_filter([$earliest_client, $earliest_site, $earliest_project]);
            $earliest_date = !empty($earliest_dates) ? min($earliest_dates) : date('Y-m-d', strtotime('-1 year'));

            $start_date = date('Y-m-d', strtotime($earliest_date));
        }

        if ($filter == '1M') {
            $period = new \DatePeriod(
                new \DateTime($start_date),
                new \DateInterval('P1D'),
                new \DateTime($end_date . ' +1 day')
            );
            $format = 'd M';
            $query_format = 'Y-m-d';
            $interval = 'day';
        } elseif ($filter == '6M' || $filter == '1Y') {
            $period = new \DatePeriod(
                new \DateTime($start_date),
                new \DateInterval('P1M'),
                new \DateTime($end_date . ' +1 day')
            );
            $format = 'M';
            $query_format = 'Y-m';
            $interval = 'month';
        } else { // All
            $start_datetime = new \DateTime($start_date);
            $end_datetime = new \DateTime($end_date);
            $diff_months = $start_datetime->diff($end_datetime)->m + ($start_datetime->diff($end_datetime)->y * 12);

            if ($diff_months > 24) {
                $period = new \DatePeriod(
                    new \DateTime(date('Y-01-01', strtotime($start_date))),
                    new \DateInterval('P1Y'),
                    new \DateTime(date('Y-12-31', strtotime($end_date . ' +1 year')))
                );
                $format = 'Y';
                $query_format = 'Y';
                $interval = 'year';
            } else {
                $period = new \DatePeriod(
                    new \DateTime($start_date),
                    new \DateInterval('P1M'),
                    new \DateTime($end_date . ' +1 month')
                );
                $format = 'M Y';
                $query_format = 'Y-m';
                $interval = 'month';
            }
        }

        foreach ($period as $key => $date) {
            $date_formatted = $date->format($format);
            $date_query = $date->format($query_format);

            $period_start_date = clone $date;
            $period_end_date = clone $date;

            if ($interval == 'day') {
                $period_end_date->modify('+1 day');
            } elseif ($interval == 'month') {
                $period_end_date->modify('last day of this month');
                $period_start_date->modify('first day of this month');
            } elseif ($interval == 'year') {
                $period_start_date->modify('first day of january this year');
                $period_end_date->modify('last day of december this year');
            }

            $period_start_str = $period_start_date->format('Y-m-d');
            $period_end_str = $period_end_date->format('Y-m-d');

            $clients_count = Company::where('del', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->whereDate('created_at', '>=', $period_start_str)
                ->whereDate('created_at', '<=', $period_end_str)
                ->count();

            $sites_count = Sites::where('del', 0)
                ->where('active',1)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->whereDate('created_at', '>=', $period_start_str)
                ->whereDate('created_at', '<=', $period_end_str)
                ->count();

            $projects_count = Project::where('is_deleted', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->whereDate('created_at', '>=', $period_start_str)
                ->whereDate('created_at', '<=', $period_end_str)
                ->count();

            $chartData[] = [
                'name' => $date_formatted,
                'Clients' => $clients_count,
                'Sites' => $sites_count,
                'Projects' => $projects_count
            ];
        }

        if ($filter == '1M') {
            $chartData = array_slice($chartData, -30);
        } elseif ($filter == '6M') {
            $chartData = array_slice($chartData, -6);
        } elseif ($filter == '1Y') {
            $chartData = array_slice($chartData, -12);
        } else { // All
            if (count($chartData) > 12) {
                $step = ceil(count($chartData) / 12);
                $temp = [];
                for ($i = 0; $i < count($chartData); $i += $step) {
                    if (isset($chartData[$i])) {
                        $temp[] = $chartData[$i];
                    }
                }
                $chartData = $temp;
            }
        }

        if (count($chartData) < 3) {
            $months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
            $current_month = (int)date('m') - 1;

            while (count($chartData) < 3) {
                $chartData[] = [
                    'name' => $months[$current_month % 12],
                    'Clients' => 0,
                    'Sites' => 0,
                    'Projects' => 0
                ];
                $current_month--;
            }

            $chartData = array_reverse($chartData);
        }

        if (count($chartData) > 0) {
            $total_clients = Company::where('del', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->count();

            $total_sites = Sites::where('del', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('active',1)
                ->count();

            $total_projects = Project::where('is_deleted', 0)
                ->where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->count();

            $chartData[count($chartData) - 1]['Clients'] = $total_clients;
            $chartData[count($chartData) - 1]['Sites'] = $total_sites;
            $chartData[count($chartData) - 1]['Projects'] = $total_projects;
        }

        return $chartData;
    }

    /**
     * Get documents expiring within 30 days
     * 
     * REVIEW NOTES:
     * 1. Uses hardcoded 'Expiry Date' field name (case-sensitive) - should be case-insensitive
     * 2. Uses 30 days threshold instead of 15 days
     * 3. Uses 'join' for file_doc which may exclude documents without file fields - consider 'leftJoin'
     * 4. whereDate may fail if expiry_doc.value is not a valid date format - should validate date format
     * 5. Doesn't verify if required document has expiry_date field before processing
     * 
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function expiringDocuments(Request $request)
    {
        $customer_id = auth()->user()->id;
        $workspace_id = auth()->user()->current_workspace_id;

        $today = Carbon::today();
        $near_expiry_date = $today->addDays(30); // Documents expiring in the next 30 days

        // Base query for fetching documents
        $query = DB::table('emp_documents as expiry_doc')
            ->join('emp_personal_details', 'emp_personal_details.emp_id', '=', 'expiry_doc.emp_id')
            ->join('emp_company_details', 'emp_personal_details.emp_id', '=', 'emp_company_details.id')
            ->join('emp_teams_members', 'emp_personal_details.emp_id', '=', 'emp_teams_members.emp_id')
            ->join('emp_teams', 'emp_teams_members.team_id', '=', 'emp_teams.id')
            ->join('required_documents', 'required_documents.id', '=', 'expiry_doc.required_document')
            ->join('required_document_fields as expiry_field', 'expiry_field.id', '=', 'expiry_doc.required_document_field')
            ->leftJoin('emp_documents as file_doc', function ($join) {
                $join->on('expiry_doc.emp_id', '=', 'file_doc.emp_id')
                    ->on('expiry_doc.required_document', '=', 'file_doc.required_document');
            })
            ->join('required_document_fields as file_field', 'file_field.id', '=', 'file_doc.required_document_field')
            ->where('expiry_field.field_name', 'Expiry Date')  // Ensure we're filtering for expiry date field
            ->where('file_field.field_type', 'file')  // Ensure we're filtering for file fields
            ->whereDate('expiry_doc.value', '<=', $near_expiry_date)  // Filter based on expiry date
            ->where('expiry_doc.del', '0')  // Filter non-deleted expiry records
            ->where('file_doc.del', '0')    // Filter non-deleted file records
            ->where('emp_company_details.del', '0')
            ->where('emp_company_details.compeleted', '1')
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->select(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                DB::raw("GROUP_CONCAT(emp_teams.title SEPARATOR ', ') as team_titles"),  // Combine multiple team titles
                'required_documents.id as document_id',
                'required_documents.title as document_title',
                'expiry_doc.value as expiry_date',  // Expiry date
                'file_doc.value as document_path',   // File path from the file_doc
                DB::raw("DATEDIFF(expiry_doc.value, CURDATE()) as remaining_days")  // Calculate remaining days
            )
            ->groupBy(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                'required_documents.id',
                'required_documents.title',
                'expiry_doc.value',
                'file_doc.value'
            );

        $count = DB::table(DB::raw("({$query->toSql()}) as sub"))->mergeBindings($query)->count();

        // Apply pagination
        $start = $request->input('from', 0);
        $limit = 10;  // Set the limit for pagination
        $query->offset($start)->limit($limit);

        // Get the paginated results
        $query_result = $query->get();

        $data =  [
            'expiring_documents' => $query_result,
        ];

        return response()->json([
            'message' => 'Get employees with expiring documents successfully.',
            'data' => $data,
            'count' => $count,
        ], 200);
    }

    /**
     * Get documents expiring within 15 days
     * Only includes documents that have an expiry_date field
     * 
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function expiringDocumentsWithin15Days(Request $request)
    {
        $userTable = $this->getUserTable();
        $customer_id = 0;
        $workspace_id = 0;
        
        if ($userTable === "customer") {
            $customer_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
        } elseif ($userTable === "emp") {
            $customer_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
        } else {
            return response()->json([
                'message' => 'Unauthorized access.',
                'data' => [],
                'count' => 0,
            ], 401);
        }

        $today = Carbon::today();
        $expiry_threshold = $today->copy()->addDays(15); // Documents expiring within 15 days

        // First, get all required documents that have an expiry_date field
        // Using case-insensitive comparison for field_name
        $documentsWithExpiryField = DB::table('required_document_fields as rdf')
            ->join('required_documents as rd', 'rd.id', '=', 'rdf.doc_id')
            ->where('rdf.del', '0')
            ->where('rdf.status', '1') // Only active fields
            ->where(function($query) {
                $query->whereRaw('LOWER(rdf.field_name) = ?', ['expiry_date'])
                      ->orWhereRaw('LOWER(rdf.field_name) = ?', ['expiry date']);
            })
            ->where('rd.del', '0')
            ->where('rd.customer_id', $customer_id)
            ->where('rd.workspace_id', $workspace_id)
            ->pluck('rdf.doc_id')
            ->unique();

        if ($documentsWithExpiryField->isEmpty()) {
            return response()->json([
                'message' => 'No documents with expiry_date field found.',
                'data' => ['expiring_documents' => []],
                'count' => 0,
            ], 200);
        }

        // Get expiry_date field IDs for these documents
        $expiryFieldIds = RequiredDocumentField::where('del', '0')
            ->where('status', '1')
            ->whereIn('doc_id', $documentsWithExpiryField)
            ->where(function($query) {
                $query->whereRaw('LOWER(field_name) = ?', ['expiry_date'])
                      ->orWhereRaw('LOWER(field_name) = ?', ['expiry date']);
            })
            ->pluck('id');

        // Base query for fetching documents with expiry dates within 15 days OR already expired
        $query = DB::table('emp_documents as expiry_doc')
            ->join('emp_personal_details', 'emp_personal_details.emp_id', '=', 'expiry_doc.emp_id')
            ->join('emp_company_details', 'emp_personal_details.emp_id', '=', 'emp_company_details.id')
            ->leftJoin('emp_teams_members', 'emp_personal_details.emp_id', '=', 'emp_teams_members.emp_id')
            ->leftJoin('emp_teams', 'emp_teams_members.team_id', '=', 'emp_teams.id')
            ->join('required_documents', 'required_documents.id', '=', 'expiry_doc.required_document')
            ->join('required_document_fields as expiry_field', 'expiry_field.id', '=', 'expiry_doc.required_document_field')
            ->whereIn('expiry_doc.required_document_field', $expiryFieldIds) // Only documents with expiry_date field
            ->where(function($query) {
                $query->whereRaw('LOWER(expiry_field.field_name) = ?', ['expiry_date'])
                      ->orWhereRaw('LOWER(expiry_field.field_name) = ?', ['expiry date']);
            })
            ->where('expiry_field.status', '1') // Only active fields
            ->where('expiry_field.del', '0')
            ->where('expiry_doc.del', '0')
            ->where('emp_company_details.del', '0')
            ->where('emp_company_details.compeleted', '1')
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->whereIn('expiry_doc.required_document', $documentsWithExpiryField) // Only documents that have expiry_date field
            ->whereNotNull('expiry_doc.value')
            ->where('expiry_doc.value', '!=', '')
            ->whereRaw('STR_TO_DATE(expiry_doc.value, "%Y-%m-%d") IS NOT NULL') // Ensure value is a valid date
            ->where(function($query) use ($expiry_threshold) {
                // Include documents that are either:
                // 1. Already expired (expiry_date < today)
                // 2. Expiring within 15 days (expiry_date <= today + 15 days)
                $query->whereRaw('STR_TO_DATE(expiry_doc.value, "%Y-%m-%d") < CURDATE()') // Already expired
                      ->orWhereRaw('STR_TO_DATE(expiry_doc.value, "%Y-%m-%d") <= ?', [$expiry_threshold->format('Y-m-d')]); // Within 15 days
            });

        // Apply search filter if search parameter is provided
        if ($request->filled('search')) {
            $searchTerm = $request->input('search');
            $query->where(function($q) use ($searchTerm) {
                $q->where('emp_personal_details.first_name', 'LIKE', "%{$searchTerm}%")
                  ->orWhere('emp_personal_details.middle_name', 'LIKE', "%{$searchTerm}%")
                  ->orWhere('emp_personal_details.last_name', 'LIKE', "%{$searchTerm}%")
                  ->orWhere('emp_teams.title', 'LIKE', "%{$searchTerm}%")
                  ->orWhere('required_documents.title', 'LIKE', "%{$searchTerm}%");
            });
        }

        $query->select(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                DB::raw("GROUP_CONCAT(DISTINCT emp_teams.title SEPARATOR ', ') as team_titles"),
                'required_documents.id as document_id',
                'required_documents.title as document_title',
                'expiry_doc.value as expiry_date',
                DB::raw("DATEDIFF(STR_TO_DATE(expiry_doc.value, '%Y-%m-%d'), CURDATE()) as remaining_days"),
                DB::raw("CASE WHEN STR_TO_DATE(expiry_doc.value, '%Y-%m-%d') < CURDATE() THEN 1 ELSE 0 END as is_expired")
            )
            ->groupBy(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                'required_documents.id',
                'required_documents.title',
                'expiry_doc.value'
            )
            ->orderBy('is_expired', 'desc') // Show expired documents first
            ->orderBy('remaining_days', 'asc'); // Then order by days remaining (most urgent first)

        // Get count before pagination
        $count = DB::table(DB::raw("({$query->toSql()}) as sub"))
            ->mergeBindings($query)
            ->count();

        // Apply pagination
        $limit = (int)$request->input('per_page', 10);
        $page = (int)$request->input('page', 1);
        
        // Ensure page is at least 1
        $page = max(1, $page);
        
        // Calculate offset from page number (page is 1-based, offset is 0-based)
        $offset = ($page - 1) * $limit;
        
        // Calculate pagination metadata
        $currentPage = $page;
        $totalPages = $count > 0 ? (int)ceil($count / $limit) : 1;
        $from = $count > 0 ? (($page - 1) * $limit) + 1 : 0;
        $to = min($page * $limit, $count);
        
        $query->offset($offset)->limit($limit);

        // Get the paginated results
        $query_result = $query->get();

        // Get file field IDs for file and image types
        $fileFieldIds = RequiredDocumentField::where('del', '0')
            ->where('status', '1')
            ->whereIn('doc_id', $documentsWithExpiryField)
            ->whereIn('field_type', ['file', 'image'])
            ->pluck('id');

        // Process results to add doc_link array
        $processed_results = $query_result->map(function ($item) use ($fileFieldIds) {
            // Get all file/image documents for this employee and document
            $fileDocuments = DB::table('emp_documents as file_doc')
                ->join('required_document_fields as file_field', 'file_field.id', '=', 'file_doc.required_document_field')
                ->where('file_doc.emp_id', $item->emp_id)
                ->where('file_doc.required_document', $item->document_id)
                ->whereIn('file_doc.required_document_field', $fileFieldIds)
                ->where('file_doc.del', '0')
                ->where('file_field.del', '0')
                ->where('file_field.status', '1')
                ->whereNotNull('file_doc.value')
                ->where('file_doc.value', '!=', '')
                ->select('file_doc.value as file_path')
                ->get();

            // Convert to array and add full URL if needed
            $doc_links = $fileDocuments->map(function ($fileDoc) {
                // If the path doesn't start with http, prepend the base URL
                $filePath = $fileDoc->file_path;
                if (!empty($filePath) && !filter_var($filePath, FILTER_VALIDATE_URL)) {
                    // Assuming files are stored in public directory
                    $filePath = url($filePath);
                }
                return $filePath;
            })->toArray();

            // Add doc_link array to the item
            $item->doc_link = $doc_links;
            
            return $item;
        });

        $data = [
            'expiring_documents' => $processed_results,
        ];

        return response()->json([
            'message' => 'Get employees with documents expired or expiring within 15 days successfully.',
            'data' => $data,
            'count' => $count,
            'pagination' => [
                'total' => $count,
                'per_page' => $limit,
                'current_page' => $currentPage,
                'last_page' => $totalPages,
                'from' => $from,
                'to' => $to,
            ],
        ], 200);
    }

    public function missingDocuments(Request $request)
    {
        $customer_id = auth()->user()->id;
        $workspace_id = auth()->user()->current_workspace_id;

        $limit = 10;
        $start = $request->input('from', 0);

        // Subquery to get the uploaded documents for each employee
        $uploadedDocuments = DB::table('emp_documents')
            ->select('emp_id', DB::raw('GROUP_CONCAT(required_document) as uploaded_docs'))
            ->groupBy('emp_id');

        $query = DB::table('emp_personal_details')
            ->join('emp_teams_members', 'emp_personal_details.emp_id', '=', 'emp_teams_members.emp_id')
            ->join('emp_teams', 'emp_teams_members.team_id', '=', 'emp_teams.id')
            ->join('emp_company_details', 'emp_personal_details.emp_id', '=', 'emp_company_details.id') // Join employee company details
            ->crossJoin('required_documents')
            ->join('required_document_fields', 'required_documents.id', '=', 'required_document_fields.doc_id')
            ->leftJoinSub($uploadedDocuments, 'uploaded_docs', function ($join) {
                $join->on('emp_personal_details.emp_id', '=', 'uploaded_docs.emp_id');
            })
            ->whereRaw('FIND_IN_SET(required_documents.id, IFNULL(uploaded_docs.uploaded_docs, "")) = 0')
            ->where('emp_personal_details.del', '0')
            ->where('required_documents.del', '0')
            ->where('required_document_fields.field_required', 1)
            ->where(function ($query) {
                $query->where('required_documents.for_who', '2')
                    ->orWhere(function ($query) {
                        $query->whereColumn('required_documents.for_who', 'emp_company_details.user_type');
                    });
            })
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->select(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                DB::raw("GROUP_CONCAT(DISTINCT emp_teams.title SEPARATOR ', ') as team_titles"),
                'required_documents.title as document_title',
                'emp_company_details.user_type'
            )
            ->groupBy(
                'emp_personal_details.emp_id',
                'emp_personal_details.first_name',
                'emp_personal_details.middle_name',
                'emp_personal_details.last_name',
                'required_documents.title',
                'emp_company_details.user_type',
            );

        $count = DB::table(DB::raw("({$query->toSql()}) as sub"))
            ->mergeBindings($query)
            ->count();

        $results = $query->offset($start)->limit($limit)->get();

        return response()->json([
            'message' => 'Get employees with missing documents successfully.',
            'data' => ['missing_documents' => $results],
            'count' => $count,
        ], 200);
    }

    public function absentEmployees(Request $request)
    {

        $userTable = $this->getUserTable();
        $workspace_id = 0;
        $customer_id = 0;
        if ($userTable === "customer") {
            $customer_id = Auth::user()->id;
            $workspace_id = Auth::user()->current_workspace_id;
            $authPersonalDetails = User::where('id', Auth::user()->id)->first();
        }
        if ($userTable === "emp") {
            $customer_id = auth()->user()->customer_id;
            $workspace_id = auth()->user()->workspace_id;
            $authPersonalDetails = EmpPersonalDetails::where('emp_id', Auth::user()->id)->first();
        }

        $employees_ids = RosterAssign::join('emp_company_details', 'roster_assigns.assign_to', 'emp_company_details.id')
            ->where('emp_company_details.del', 0)
            ->where('emp_company_details.status', 1)
            ->where('roster_assigns.schedule_date', $this->current_date)
            ->where('roster_assigns.customer_id', $customer_id)
            ->where('roster_assigns.workspace_id', $workspace_id)
            ->groupBy('roster_assigns.assign_to')
            ->pluck('roster_assigns.assign_to')
            ->toArray();
        $present_employee_ids = EmployeeAttendance::where('date', $this->current_date)
            ->whereIn('employee_id', $employees_ids)
            ->where('customer_id', $customer_id)
            ->where('workspace_id', $workspace_id)
            ->groupBy('employee_id')
            ->pluck('employee_id')
            ->toArray();

    
        $leave_employee_ids = LeaveRequest::where('from', "<=", $this->current_date)
            ->where('to', ">=", $this->current_date)
            ->where('status', 1)
            ->where('customer_id', $customer_id)
            ->where('workspace_id', $workspace_id)
            ->pluck('employee_id')
            ->toArray();
    

        $absent_employee_id = array_values(array_diff($employees_ids, array_merge($present_employee_ids, $leave_employee_ids)));

        $absent_employees = EmpCompanyDetails::withoutGlobalScope(\App\Scopes\NotDeletedScope::class)
            ->join('emp_personal_details', 'emp_company_details.id', 'emp_personal_details.emp_id')
            ->join('roles', 'emp_company_details.access_role', 'roles.code')
            ->whereIn('emp_company_details.id', $absent_employee_id)
            ->where('emp_company_details.customer_id', $customer_id)
            ->where('emp_company_details.workspace_id', $workspace_id)
            ->where('emp_company_details.del', '!=', 1)
            ->where(function ($query) {
                $query->where('emp_company_details.invited', 1)
                    ->orWhere(function ($q) {
                        $q->where('emp_company_details.invited', 0)
                            ->where('emp_company_details.approved', 1)
                            ->where('emp_company_details.compeleted', 1)
                            ->where('emp_company_details.status', 1);
                    });
            })
            ->select(
                'emp_company_details.id as emp_company_id',
                'emp_company_details.employee_email',
                'emp_personal_details.*',
                'roles.title',
                'emp_company_details.access_role'
            )
            ->distinct('emp_company_details.id');

        $count = $absent_employees->count();
        $start = $request->input('from', 0);
        $limit = 10;  // Set the limit for pagination
        $absent_employees->offset($start)->limit($limit);

        $query_result = $absent_employees->get();


        $data =  [
            'absent_employees' => $query_result->values(),
        ];

        return response()->json([
            'message' => 'Get absent employees successfully.',
            'data' => $data,
            'count' => $count,
        ], 200);
    }
    public function presentEmployees(Request $request)
    {
        // dd("Heloo buddy");
        $customer_id = auth()->user()->id;
        $workspace_id = auth()->user()->current_workspace_id;

        $present_employees = EmployeeAttendance::where('date', $this->current_date)
            ->where('customer_id', $customer_id)
            ->where('workspace_id', $workspace_id)
            ->with('present_employee_details', 'employee_site', 'present_employee_company_details');

        $count = $present_employees->count();
        $start = $request->input('from', 0);
        $limit = 10;  // Set the limit for pagination
        $present_employees->offset($start)->limit($limit);

        // Get the paginated results
        $query_result = $present_employees->get();

        foreach ($query_result as &$present) {
            $present['formatted_date'] = formatDate($present['date']);
            $present['formatted_check_in'] = formatTime($present['check_in']);
            $present['formatted_check_out'] = formatTime($present['check_out']);
        }

        $data =  [
            'today_present' => $query_result,
        ];

        return response()->json([
            'message' => 'Get employees with expiring documents successfully.',
            'data' => $data,
            'count' => $count,
        ], 200);
    }

    /**
     * Get pending document approvals list
     * Lists all reports (incident, WHS, inspection plan, SWMS, WHSQ) that are sent for approval
     * Shows signature status, required signatures, and signed count
     */
    public function getPendingDocumentApprovals(Request $request)
    {
        try {
            $userTable = $this->getUserTable();
            $workspace_id = 0;
            $customer_id = 0;
            
            if ($userTable === "customer") {
                $customer_id = Auth::user()->id;
                $workspace_id = Auth::user()->current_workspace_id;
            } elseif ($userTable === "emp") {
                $customer_id = auth()->user()->customer_id;
                $workspace_id = auth()->user()->workspace_id;
            } else {
                return response()->json([
                    'message' => 'Unauthorized access.',
                    'data' => []
                ], 403);
            }

            $pendingApprovals = [];

            // 1. Incident Reports
            $incidentReports = IncidentReport::where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('del', '0')
                ->where('approval_status', 1) // Sent for approval
                ->get();

            foreach ($incidentReports as $report) {
                // Get all signoffs (pre-assigned signers)
                $signoffs = IncidentSignoff::where('incident_report_id', $report->id)->get();
                $requiredSignatures = $signoffs->count();
                
                // Check if authorized_by is already in signoffs, if not add 1 to required
                $authorizedInSignoffs = $signoffs->contains('emp_id', $report->authorised_by);
                if ($report->authorised_by && !$authorizedInSignoffs) {
                    $requiredSignatures += 1;
                }

                // Count signed signatures from signoffs table
                $signedCount = $signoffs->whereNotNull('signature')->count();

                // Get submitted by using send_by and sender_type
                $submittedBy = $this->getSubmittedByName($report);

                // Format status
                $status = $this->formatApprovalStatus($signedCount, $requiredSignatures);

                $pendingApprovals[] = [
                    'id' => $report->id,
                    'document' => $report->title ?? 'Incident Report #' . $report->id,
                    'submitted_by' => $submittedBy,
                    'status' => $status,
                    'status_label' => $this->getStatusLabel($signedCount, $requiredSignatures),
                    'signed_count' => $signedCount,
                    'required_signatures' => $requiredSignatures,
                    'report_type' => 'incident_report',
                    'report_type_label' => 'Incident Report'
                ];
            }

            // 2. SWMS Reports
            $swmsReports = Swms::where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('del', '0')
                ->where('approval_status', 1)
                ->get();

            foreach ($swmsReports as $report) {
                // Get all signoffs
                $signoffs = SwmsSignature::where('swms_id', $report->id)->get();
                $requiredSignatures = $signoffs->count();
                
                // Get authorized persons (site_manager_or_forman can be comma-separated)
                $authorizedPersons = [];
                if ($report->site_manager_or_forman) {
                    $authorizedPersons = array_filter(array_map('trim', explode(',', $report->site_manager_or_forman)));
                    $authorizedPersons = array_filter($authorizedPersons, function($id) {
                        return !empty($id) && $id != '0';
                    });
                    
                    // Check which authorized persons are not in signoffs yet
                    foreach ($authorizedPersons as $authId) {
                        $authInSignoffs = $signoffs->contains('emp_id', $authId);
                        if (!$authInSignoffs) {
                            $requiredSignatures += 1;
                        }
                    }
                }

                // Count signed signatures
                $signedCount = $signoffs->whereNotNull('signatures')->count();

                // Get submitted by using send_by and sender_type
                $submittedBy = $this->getSubmittedByName($report);

                $status = $this->formatApprovalStatus($signedCount, $requiredSignatures);

                $pendingApprovals[] = [
                    'id' => $report->id,
                    'document' => $report->title ?? 'SWMS Report #' . $report->id,
                    'submitted_by' => $submittedBy,
                    'status' => $status,
                    'status_label' => $this->getStatusLabel($signedCount, $requiredSignatures),
                    'signed_count' => $signedCount,
                    'required_signatures' => $requiredSignatures,
                    'report_type' => 'swms_report',
                    'report_type_label' => 'SWMS Report'
                ];
            }

            // 3. Inspection Plans
            $inspectionPlans = InspectionPlan::where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('del', '0')
                ->where('approval_status', 1)
                ->get();

            foreach ($inspectionPlans as $report) {
                // Get signoffs (excluding checklist-specific signatures, only quick entry ones)
                $signoffs = InspectionPlanSignature::where('inspection_plan_id', $report->id)
                    ->whereNull('itpc_id') // Quick entry signatures only
                    ->get();
                $requiredSignatures = $signoffs->count();
                
                // Check if authorized_by is already in signoffs, if not add 1 to required
                $authorizedInSignoffs = $signoffs->contains('employee_id', $report->authorised_by);
                if ($report->authorised_by && !$authorizedInSignoffs) {
                    $requiredSignatures += 1;
                }

                // Count signed signatures
                $signedCount = $signoffs->whereNotNull('signature')->count();

                // Get submitted by using send_by and sender_type
                $submittedBy = $this->getSubmittedByName($report);

                $status = $this->formatApprovalStatus($signedCount, $requiredSignatures);

                $pendingApprovals[] = [
                    'id' => $report->id,
                    'document' => $report->title ?? 'Inspection Plan #' . $report->id,
                    'submitted_by' => $submittedBy,
                    'status' => $status,
                    'status_label' => $this->getStatusLabel($signedCount, $requiredSignatures),
                    'signed_count' => $signedCount,
                    'required_signatures' => $requiredSignatures,
                    'report_type' => 'inspection_report',
                    'report_type_label' => 'Inspection Plan'
                ];
            }

            // 4. WHS Reports
            $whsReports = WhsReport::where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('approval_status', 1)
                ->get();

            foreach ($whsReports as $report) {
                // Get all signoffs
                $signoffs = WhsSignature::where('whs_id', $report->id)->get();
                $requiredSignatures = $signoffs->count();
                
                // Check if authorized_by is already in signoffs, if not add 1 to required
                $authorizedInSignoffs = $signoffs->contains('employee_id', $report->authorised_by);
                if ($report->authorised_by && !$authorizedInSignoffs) {
                    $requiredSignatures += 1;
                }

                // Count signed signatures
                $signedCount = $signoffs->whereNotNull('signatures')->count();

                // Get submitted by using send_by and sender_type
                $submittedBy = $this->getSubmittedByName($report);

                $status = $this->formatApprovalStatus($signedCount, $requiredSignatures);

                $pendingApprovals[] = [
                    'id' => $report->id,
                    'document' => $report->title ?? $report->document_title ?? 'WHS Report #' . $report->id,
                    'submitted_by' => $submittedBy,
                    'status' => $status,
                    'status_label' => $this->getStatusLabel($signedCount, $requiredSignatures),
                    'signed_count' => $signedCount,
                    'required_signatures' => $requiredSignatures,
                    'report_type' => 'whs_report',
                    'report_type_label' => 'WHS Report'
                ];
            }

            // 5. WHSQ Reports
            $whsqReports = WhsqReport::where('customer_id', $customer_id)
                ->where('workspace_id', $workspace_id)
                ->where('approval_status', 1)
                ->get();

            foreach ($whsqReports as $report) {
                // Get all signoffs
                $signoffs = WhsqSignature::where('whsq_report_id', $report->id)->get();
                $requiredSignatures = $signoffs->count();
                
                // Check if authorized_by is already in signoffs, if not add 1 to required
                $authorizedInSignoffs = $signoffs->contains('employee_id', $report->authorised_by);
                if ($report->authorised_by && !$authorizedInSignoffs) {
                    $requiredSignatures += 1;
                }

                // Count signed signatures
                $signedCount = $signoffs->whereNotNull('signature')->count();

                // Get submitted by using send_by and sender_type
                $submittedBy = $this->getSubmittedByName($report);

                $status = $this->formatApprovalStatus($signedCount, $requiredSignatures);

                $pendingApprovals[] = [
                    'id' => $report->id,
                    'document' => $report->document_title ?? 'WHSQ Report #' . $report->id,
                    'submitted_by' => $submittedBy,
                    'status' => $status,
                    'status_label' => $this->getStatusLabel($signedCount, $requiredSignatures),
                    'signed_count' => $signedCount,
                    'required_signatures' => $requiredSignatures,
                    'report_type' => 'whsqe_report',
                    'report_type_label' => 'WHSQ Report'
                ];
            }

            // Sort by most recent first (you can adjust sorting as needed)
            usort($pendingApprovals, function($a, $b) {
                return $b['id'] <=> $a['id'];
            });

            // Convert array to collection for pagination
            $pendingApprovalsCollection = collect($pendingApprovals);

            // Get pagination parameters from request
            $page = (int) $request->get('page', 1);
            $perPage = (int) $request->get('per_page', 10);

            // Calculate pagination metadata
            $total = $pendingApprovalsCollection->count();
            $currentPageItems = $pendingApprovalsCollection->forPage($page, $perPage)->values();

            // Create paginator instance
            $paginator = new LengthAwarePaginator(
                $currentPageItems,
                $total,
                $perPage,
                $page,
                [
                    'path' => LengthAwarePaginator::resolveCurrentPath(),
                    'pageName' => 'page',
                ]
            );

            return response()->json([
                'message' => 'Pending document approvals fetched successfully.',
                'data' => $currentPageItems->toArray(),
                'count' => $currentPageItems->count(),
                'total_count' => $total,
                'pagination' => [
                    'total' => $total,
                    'per_page' => $perPage,
                    'current_page' => $page,
                    'last_page' => $paginator->lastPage(),
                    'from' => $total > 0 ? (($page - 1) * $perPage + 1) : null,
                    'to' => $total > 0 ? min($page * $perPage, $total) : null,
                ]
            ], 200);

        } catch (\Exception $e) {
            return response()->json([
                'message' => 'Error fetching pending approvals: ' . $e->getMessage(),
                'data' => []
            ], 500);
        }
    }

    /**
     * Format approval status text
     */
    private function formatApprovalStatus($signedCount, $requiredSignatures)
    {
        if ($requiredSignatures == 0) {
            return 'No signatures required';
        }

        if ($signedCount == 0) {
            return 'Awaiting Signature';
        }

        if ($signedCount >= $requiredSignatures) {
            return 'All Signed';
        }

        return $signedCount . ' of ' . $requiredSignatures . ' Signed';
    }

    /**
     * Get status label for UI display
     */
    private function getStatusLabel($signedCount, $requiredSignatures)
    {
        if ($requiredSignatures == 0) {
            return 'no-signature';
        }

        if ($signedCount == 0) {
            return 'awaiting-signature';
        }

        if ($signedCount >= $requiredSignatures) {
            return 'all-signed';
        }

        return 'partial-signed';
    }

    /**
     * Get submitted by name using send_by and sender_type
     * Falls back to other fields if send_by is not available
     */
    private function getSubmittedByName($report)
    {
        // First, try to use send_by and sender_type (set when report is sent for approval)
        if (isset($report->send_by) && isset($report->sender_type) && $report->send_by) {
            if ($report->sender_type === 'customer') {
                // Get customer name from User table
                $user = User::find($report->send_by);
                if ($user) {
                    return $user->name ?? 'Unknown';
                }
            } elseif ($report->sender_type === 'emp') {
                // Get employee name from EmpPersonalDetails
                $emp = EmpPersonalDetails::where('emp_id', $report->send_by)->first();
                if ($emp) {
                    return trim(($emp->first_name ?? '') . ' ' . ($emp->middle_name ?? '') . ' ' . ($emp->last_name ?? ''));
                }
            }
        }

        return 'Unknown';
    }
}
