<?php

namespace App\Http\Controllers\Api\Hrms\Report;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Symfony\Component\HttpFoundation\StreamedResponse;

use App\Models\Hrms\JobOpening;
use App\Models\Hrms\Applicant;
use App\Models\Hrms\Performance;
use App\Models\Hrms\Designation;
use App\Models\Hrms\Department;
use App\Models\Hrms\Attendance;
use App\Models\Hrms\Employee;
use App\Models\Hrms\ShiftMaster;
use App\Models\Hrms\AssingShift;
use App\Models\Hrms\LeaveMaster;
use App\Models\Hrms\Holiday;
use App\Models\Hrms\Training;
use App\Models\Hrms\Trainer;
use App\Models\Hrms\Project;
use App\Models\Hrms\Client;
use App\Models\Hrms\Leave;
use App\Models\User;
use Validator;
use Auth;
use Carbon\Carbon;

class OrganisationReportController extends Controller
{

    
    public function organisationLeaveReportStatus(Request $request)
    {
        $auth = Auth::user();
    
        try {
            // Fetch leave types for the organisation
            $leaveMaster = LeaveMaster::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->get();
    
            $myLeaveReportStatus = [];
            $overviewReportTotal = [];
    
            if ($request['fromDate']!='' && isset($request['toDate'])) {
                $fromDate = $request->fromDate;
                $toDate = $request->toDate . ' 23:59:59';
            }else{
                $fromDate = date('Y-m-01'); //month startdate
                $toDate = date('Y-m-t') . ' 23:59:59'; //month enddate
            }
    
            // echo $fromDate;die;
            foreach ($leaveMaster as $leaveType) {
                $leave = Leave::where('leave_type_id', $leaveType->id)
                    ->where('is_disabled', '0')
                    ->where('organisation_id', $auth['active_organisation'])
                    ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate);
    
                // Calculate counts based on status
                $approvedLeavesCount = (clone $leave)->where('status', 'Approved')->count();
                $pendingLeavesCount = (clone $leave)->where('status', 'Pending')->count();
                $declinedLeavesCount = (clone $leave)->where('status', 'Declined')->count();
    
                $totalCount = $approvedLeavesCount + $pendingLeavesCount + $declinedLeavesCount;
    
                $myLeaveReportStatus[] = [
                    'leave_type_id' => $leaveType->id,
                    'leave_type' => $leaveType->leave_type,
                    'available_days' => $leaveType->available_days,
                    'approved_leaves_count' => $approvedLeavesCount,
                    'pending_leaves_count' => $pendingLeavesCount,
                    'declined_leaves_count' => $declinedLeavesCount,
                    'total_leaves_count' => $totalCount,
                ];
            }
    
            $leaveOverview = Leave::where('is_disabled', '0')
                    ->where('organisation_id', $auth['active_organisation'])
                    ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate);

            $approved_leaves_countOver = (clone $leaveOverview)->where('status','Approved')->count();
            $pending_leaves_countOver = (clone $leaveOverview)->where('status','Pending')->count();
            $declined_leaves_countOver = (clone $leaveOverview)->where('status','Declined')->count();
            $totalCountOver = (clone $leaveOverview)->count();
            
            $overviewReportTotal = [
                'totalCountOver' => $totalCountOver,
                'approved_leaves_countOver' => $approved_leaves_countOver,
                'pending_leaves_countOver' => $pending_leaves_countOver,
                'declined_leaves_countOver' => $declined_leaves_countOver,
            ];

            $response = [
                'success' => true,
                'my_leave_report_status' => $myLeaveReportStatus,
                'overview_report_total' => $overviewReportTotal,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 404);
        }
    }

    public function organisationLeaveReportSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            $leaveMaster = LeaveMaster::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->get();
    
            $myLeaveReportSummary = [];
    
            if ($request['fromDate']!='' && isset($request['toDate'])) {
                $fromDate = $request->fromDate;
                $toDate = $request->toDate . ' 23:59:59';
            }else{
                $fromDate = date('Y-m-01'); //month startdate
                $toDate = date('Y-m-t') . ' 23:59:59';//month enddate
            }
    
            // echo $fromDate;die;
            foreach ($leaveMaster as $leaveType) {
                $leave = Leave::where('leave_type_id', $leaveType->id)
                    ->where('is_disabled', '0')
                    ->where('organisation_id', $auth['active_organisation'])
                    ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate);
    
                // Calculate counts based on status
                $approvedLeavesCount = (clone $leave)->where('status', 'Approved')->count();
                $pendingLeavesCount = (clone $leave)->where('status', 'Pending')->count();
                $declinedLeavesCount = (clone $leave)->where('status', 'Declined')->count();
    
                $bookedDays = (clone $leave)->selectRaw("IFNULL(SUM(CAST(REGEXP_SUBSTR(days, '[0-9]+') AS UNSIGNED)), 0) as total_days")
                ->value('total_days');
                $totalCount = (clone $leave)->count();
                $remainig_count = $leaveType->available_days - $bookedDays;
    
                $myLeaveReportSummary[] = [
                    'leave_type_id' => $leaveType->id,
                    'leave_type' => $leaveType->leave_type,
                    'available_days' => $leaveType->available_days,
                    'booked_days' => $bookedDays,
                    'remainig_days' => $remainig_count,
                    'total_leaves_count' => $totalCount,
                ];
            }
    
            $response = [
                'success' => true,
                'my_leave_report_summary' => $myLeaveReportSummary
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 404);
        }
    }
    
    
    public function organisationLeaveReportYear(Request $request)
    {
        $auth = Auth::user();
    
        try {
            $fromDate = $request->filled('fromDate') ? $request->input('fromDate') : date('Y-01-01'); // Year start date
            $toDate = $request->filled('toDate') ? $request->input('toDate') : date('Y-12-31') . ' 23:59:59';     // Year end date
    
            // Query data for the selected date range
            $leaveData = Leave::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate)
                ->selectRaw("MONTH(updated_at) as month, 
                             COUNT(CASE WHEN status = 'Approved' THEN 1 END) as approved_count,
                             COUNT(CASE WHEN status = 'Pending' THEN 1 END) as pending_count,
                             COUNT(CASE WHEN status = 'Declined' THEN 1 END) as declined_count,
                             COUNT(*) as total_count")
                ->groupByRaw("MONTH(updated_at)")
                ->orderByRaw("MONTH(updated_at)")
                ->get();
    
            // Initialize array for all months with default values
            $myLeaveReportYear = [];
            $totalApproved = $totalPending = $totalDeclined = $totalCount = 0;
    
            // Iterate over all months of the current year
            for ($month = 1; $month <= 12; $month++) {
                $monthName = date('F', mktime(0, 0, 0, $month, 1)); // Get full month name
    
                // Check if data exists for the current month
                $data = $leaveData->firstWhere('month', $month);
    
                // Add data to the array or default to zero
                $myLeaveReportYear[] = [
                    'month_name' => $monthName,
                    'total_leaves_count' => $data->total_count ?? 0,
                    'approved_leaves_count' => $data->approved_count ?? 0,
                    'pending_leaves_count' => $data->pending_count ?? 0,
                    'declined_leaves_count' => $data->declined_count ?? 0,
                ];
    
                // Accumulate totals
                $totalApproved += $data->approved_count ?? 0;
                $totalPending += $data->pending_count ?? 0;
                $totalDeclined += $data->declined_count ?? 0;
                $totalCount += $data->total_count ?? 0;
            }
    
            // Add overall totals to the response
            $response = [
                'success' => true,
                'my_leave_report_year' => $myLeaveReportYear,
                'year_totals' => [
                    'total_leaves_count' => $totalCount,
                    'approved_leaves_count' => $totalApproved,
                    'pending_leaves_count' => $totalPending,
                    'declined_leaves_count' => $totalDeclined,
                ],
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 404);
        }
    }
    
    public function organisationReportDepartmentSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
        
            // Get total number of employees in the organisation
            $totalEmp = Employee::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->count();
    
            // Get all departments with their employee count
            $departments = Department::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->withCount([
                    'employees' => function ($query) use ($auth) {
                        $query->where('is_disabled', '0')
                            ->where('organisation_id', $auth['active_organisation']);
                    }
                ])
                ->get();
    
            $departmentReportSummary = [];
    
            foreach ($departments as $department) {
                $departmentReportSummary[] = [
                    'department_id' => $department->id,
                    'department_name' => $department->department_name,
                    'employee_count' => $department->employees_count,
                ];
            }
    
            $response = [
                'success' => true,
                'total_emp' => $totalEmp,
                'department_report_summary' => $departmentReportSummary,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 404);
        }
    }

    public function organisationReportDesignationSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {

            // Get total number of employees in the organisation
            $totalEmp = Employee::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->count();
    
            // Get all Designation with their employee count
            $designations = Designation::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation'])
                ->withCount([
                    'employees' => function ($query) use ($auth) {
                        $query->where('is_disabled', '0')
                            ->where('organisation_id', $auth['active_organisation']);
                    }
                ])
                ->get();
    
            $designationReportSummary = [];
    
            foreach ($designations as $designation) {
                $designationReportSummary[] = [
                    'designation_id' => $designation->id,
                    'designation_name' => $designation->designation_name,
                    'employee_count' => $designation->employees_count,
                ];
            }
    
            $response = [
                'success' => true,
                'total_emp' => $totalEmp,
                'designation_report_summary' => $designationReportSummary,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 404);
        }
    }
    
    public function organisationReportGenderSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            // Base query for employees
            $employeeQuery = Employee::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation']);
    
            // Fetch total employees
            $totalEmp = (clone $employeeQuery)->count();
    
            // Fetch counts by gender
            $totalMale = (clone $employeeQuery)->where('gender', 'Male')->count();
            $totalFemale = (clone $employeeQuery)->where('gender', 'Female')->count();
            $totalOther = (clone $employeeQuery)->where('gender', 'Other')->count();
    
            // Build response
            $response = [
                'success' => true,
                'total_emp' => $totalEmp,
                'total_male' => $totalMale,
                'total_female' => $totalFemale,
                'total_other' => $totalOther,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 500);
        }
    }

    public function organisationReportAgeGroupSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            // Base query for employees
            $employeeQuery = Employee::where('is_disabled', '0')
                ->where('organisation_id', $auth['active_organisation']);
    
            // Fetch total employees
            $totalEmp = (clone $employeeQuery)->count();
    
            // Define age groups
            $ageGroups = [
                '15-20' => [15, 20],
                '21-25' => [21, 25],
                '26-30' => [26, 30],
                '31-35' => [31, 35],
                '36-40' => [36, 40],
                '41-50' => [41, 50],
                '51-60' => [51, 60],
                '61-70' => [61, 70],
                '71-80' => [71, 80],
                '81-90' => [81, 90],
                '91-100' => [91, 100],
            ];
    
            // Calculate counts for each age group
            $ageGroupCounts = [];
            foreach ($ageGroups as $group => $range) {
                $ageGroupCounts[$group] = (clone $employeeQuery)
                    ->whereBetween('age', $range)
                    ->count();
            }
    
            // Build response
            $response = [
                'success' => true,
                'total_emp' => $totalEmp,
                'age_group_summary' => $ageGroupCounts,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 500);
        }
    }
    
    public function organisationReportemployeeStatsSummary(Request $request) 
    {
        $user = Auth::user();
    
        if ($user != null) {
            // Get the current date
            $todayDate = date('Y-m-d') . ' 23:59:59';
    
            $currentYearStart = date('Y-01-01');
            $currentYearEnd = date('Y-12-31') . ' 23:59:59'; 
    
            // Get the first and last date of the last month
            $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
            $lastMonthEnd = date('Y-m-t', strtotime('last day of last month')) . ' 23:59:59'; 
    
            $currentMonthStart = date('Y-m-01'); //month startdate
            $currentMonthEnd = date('Y-m-t') . ' 23:59:59'; //month enddate

            // Query to get all employees
            $employeeQuery = Employee::where('is_disabled', '0')
                ->where('organisation_id', $user['active_organisation']);
    
            // Active employees
            $total_active_employee = (clone $employeeQuery)->where('employee_status', 'Active')->count();
            $total_inactive_employee = (clone $employeeQuery)->where('employee_status', 'Inactive')->count();
            $total_terminated_employee = (clone $employeeQuery)->where('employee_status', 'Terminated')->count();
            $total_noticeperiod_employee = (clone $employeeQuery)->where('employee_status', 'Notice Period')->count();
            $total_resigned_employee = (clone $employeeQuery)->where('employee_status', 'Resigned')->count();

            // Overall total employees
            $total_employee = (clone $employeeQuery)->count();

            // Last month's total employees
            $last_month_total_employee = (clone $employeeQuery)
                ->whereDate('created_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_employee = $last_month_total_employee > 0
                ? round((($total_employee - $last_month_total_employee) / $last_month_total_employee) * 100, 1)
                : 0;

            // New employees currentMonthStart to  currentMonthEnd
            $this_month_new_employee = (clone $employeeQuery)
                ->where('created_at', '>=', $currentMonthStart)->where('created_at', '<=', $currentMonthEnd)
                ->count();
    
            // New employees (last month) lastMonthStart to  lastMonthEnd
            $last_month_new_employee = (clone $employeeQuery)
                ->where('created_at', '>=', $lastMonthStart)->where('created_at', '<=', $lastMonthEnd)
                ->count();
            $percent_new_employee = $last_month_new_employee > 0
                ? round((($this_month_new_employee - $last_month_new_employee) / $last_month_new_employee) * 100, 1)
                : 0;
    

            // New Resigned employees currentMonthStart to  currentMonthEnd
            $this_month_resigned_employee = (clone $employeeQuery)
                ->where('employee_status', 'Resigned')
                ->where('updated_at', '>=', $currentMonthStart)->where('updated_at', '<=', $currentMonthEnd)
                ->count();
    
            // New Resigned employees (last month) lastMonthStart to  lastMonthEnd
            $last_month_resigned_employee = (clone $employeeQuery)
                ->where('employee_status', 'Resigned')
                ->where('updated_at', '>=', $lastMonthStart)->where('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_resigned_employee = $last_month_resigned_employee > 0
                ? round((($this_month_resigned_employee - $last_month_resigned_employee) / $last_month_resigned_employee) * 100, 1)
                : 0;
                

            // Employees on leave currentMonthStart to  currentMonthEnd
            $this_month_on_leave = Leave::where('organisation_id', $user['active_organisation'])
                ->where('updated_at', '>=', $currentMonthStart)->where('updated_at', '<=', $currentMonthEnd)
                ->distinct('user_id')
                ->count('user_id');
    
            // Employees on leave (last month)
            $last_month_on_leave = Leave::where('organisation_id', $user['active_organisation'])
                ->where('updated_at', '>=', $lastMonthStart)->where('updated_at', '<=', $lastMonthEnd)
                ->distinct('user_id')
                ->count('user_id');
            $percent_on_leave = $last_month_on_leave > 0
                ? round((($this_month_on_leave - $last_month_on_leave) / $last_month_on_leave) * 100, 1)
                : 0;
    

            // Prepare response
            $response = [
                'total_employee' => $total_employee, // Overall total
                'last_month_total_employee' => $last_month_total_employee, // From last month total
                'percent_total_employee' => $percent_total_employee . '%',
    
                'this_month_new_employee' => $this_month_new_employee, // this_month_new_employee
                'last_month_new_employee' => $last_month_new_employee, // From last month new employees
                'percent_new_employee' => $percent_new_employee . '%',
    
                'this_month_resigned_employee' => $this_month_resigned_employee, // this_month_resigned_employee
                'last_month_resigned_employee' => $last_month_resigned_employee, // From last month resigned employees
                'percent_resigned_employee' => $percent_resigned_employee . '%',
    
                'this_month_on_leave' => $this_month_on_leave, // this_month_on_leave
                'last_month_on_leave' => $last_month_on_leave, // From last month employees on leave
                'percent_on_leave' => $percent_on_leave . '%',

                'total_active_employee'=>$total_active_employee,
                'total_inactive_employee'=>$total_inactive_employee,
                'total_terminated_employee'=>$total_terminated_employee,
                'total_noticeperiod_employee'=>$total_noticeperiod_employee,
                'total_resigned_employee'=>$total_resigned_employee,
                'success' => true,
            ];
        } else {
            $response = [
                'message' => 'Invalid User',
            ];
        }
    
        return response()->json($response);
    }
    
    public function organisationReportPerformance(Request $request) 
    {
        $auth = Auth::user();
    
        try {
            // Determine the date range
            if (!empty($request->fromDate) && isset($request->toDate)) {
                $fromDate = $request->fromDate;
                $toDate = $request->toDate . ' 23:59:59';
            } else {
                $fromDate = date('Y-01-01'); // Start of the current year
                $toDate = date('Y-12-31') . ' 23:59:59';  // End of the current year
            }
        
            $query = Performance::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation)
                ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate);
        
            if (!empty($request->user_id)) {
                $query->where('user_id', $request->user_id);
            }
        
            // Fetch the performance data
            $performance = $query->get();
        
            // Prepare response
            $response = [
                'result' => $performance,
                'success' => true,
            ];
        
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 500);
        }
    }
    
    public function organisationReportProjectStatsSummary(Request $request) 
    {
        $auth = Auth::user();
    
        try {
            $projectStats=[];
            // Get the first and last date of the last month
            $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
            $lastMonthEnd = date('Y-m-t', strtotime('last day of last month')) . ' 23:59:59';

            $projectQuery = Project::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);
            
            $completed = (clone $projectQuery)->where('status', 'Completed')->count();
            // Last month's total project
            $last_month_total_project = (clone $projectQuery)
                ->whereDate('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_completed = $last_month_total_project > 0
                ? round((($completed - $last_month_total_project) / $last_month_total_project) * 100, 1)
                : 0;

            $ongoing =  (clone $projectQuery)->where('status', 'Ongoing')->count();
            // Last month's total ongoing
            $last_month_total_ongoing = (clone $projectQuery)
                ->whereDate('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_ongoing = $last_month_total_ongoing > 0
                ? round((($ongoing - $last_month_total_ongoing) / $last_month_total_ongoing) * 100, 1)
                : 0;

            $cancelled = (clone $projectQuery)->where('status', 'Cancelled')->count();
            // Last month's total cancelled
            $last_month_total_cancelled = (clone $projectQuery)
                ->whereDate('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_cancelled = $last_month_total_cancelled > 0
                ? round((($cancelled - $last_month_total_cancelled) / $last_month_total_cancelled) * 100, 1)
                : 0;

            $onHold = (clone $projectQuery)->where('status', 'On Hold')->count();
            // Last month's total onHold
            $last_month_total_onHold = (clone $projectQuery)
                ->whereDate('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_onHold = $last_month_total_onHold > 0
                ? round((($onHold - $last_month_total_onHold) / $last_month_total_onHold) * 100, 1)
                : 0;

            $notStarted = (clone $projectQuery)->where('status', 'Not Started')->count();
            // Last month's total notStarted
            $last_month_total_notStarted = (clone $projectQuery)
                ->whereDate('updated_at', '<=', $lastMonthEnd)
                ->count();
            $percent_total_notStarted = $last_month_total_notStarted > 0
                ? round((($notStarted - $last_month_total_notStarted) / $last_month_total_notStarted) * 100, 1)
                : 0;

            $projectStats=[
                'total_completed' => $completed,
                'last_month_total_project' => $percent_total_completed . '%',
                'total_ongoing' => $ongoing,
                'last_month_total_ongoing' => $percent_total_ongoing . '%',
                'total_cancelled' => $cancelled,
                'last_month_total_cancelled' => $percent_total_cancelled . '%',
                'total_onHold' => $onHold,
                'last_month_total_onHold' => $percent_total_onHold . '%',
                'total_notStarted' => $notStarted,
                'last_month_total_notStarted' => $percent_total_notStarted . '%',
            ];
            // Prepare response
            $response = [
                'project_stats' => $projectStats,
                'success' => true,
            ];
        
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 500);
        }
    }
    
    public function organisationReportClientProjectSummary(Request $request) {
        $auth = Auth::user();
    
        try {
            // Get the first and last date of the last month
            $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
            $lastMonthEnd = date('Y-m-t', strtotime('last day of last month')) . ' 23:59:59'; 
            
            $todayDate = date('Y-m-d') . ' 23:59:59'; 


            $clientProjectSummaries = [];
            $clientStats = [];
    
            $clients = Client::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation)
                ->get();
    
            foreach ($clients as $client) {
                $projectQuery = Project::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation)
                ->where('client_id', $request->client_id ?? $client->id);

                $totalProjects = $projectQuery->count();
                $onHold = (clone $projectQuery)->where('status', 'On Hold')->count();
                $cancelled = (clone $projectQuery)->where('status', 'Cancelled')->count();
                $ongoing = (clone $projectQuery)->where('status', 'Ongoing')->count();
                $completed = (clone $projectQuery)->where('status', 'Completed')->count();
                $notStarted = (clone $projectQuery)->where('status', 'Not Started')->count();
    
                $clientProjectSummaries[] = [
                    'client_id' => $client->id,
                    'client_name' => $client->full_name,
                    'total_project_count' => $totalProjects,
                    'total_completed' => $completed,
                    'total_ongoing' => $ongoing,
                    'total_cancelled' => $cancelled,
                    'total_onHold' => $onHold,
                    'total_notStarted' => $notStarted,
                ];
            }
    
            $clientQuery = Client::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);

            $total_client = (clone $clientQuery)->count(); //total
            $total_active = (clone $clientQuery)->where('status', '0')->count(); //Active
            $total_inactive = (clone $clientQuery)->where('status', '1')->count(); //InActive
            $total_new_client = (clone $clientQuery)->where('created_at', '>=', $lastMonthStart)
            ->where('created_at', '<=', $todayDate)->count(); //new client
                
            $clientStats = [
                'total_client' => $total_client,
                'total_active' => $total_active,
                'total_inactive' => $total_inactive,
                'total_new_client' => $total_new_client,
            ];

            $response = [
                'client_project_summary' => $clientProjectSummaries,
                'client_stats' => $clientStats,
                'success' => true,
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred: ' . $e->getMessage()
            ], 500);
        }
    }
    
    public function organisationReportTrainerStatsSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            // Get the first and last date of the last month
            $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
            $lastMonthEnd = date('Y-m-t', strtotime('last day of last month')) . ' 23:59:59'; 
            
            $todayDate = date('Y-m-d') . ' 23:59:59'; 
           
            $trainerSummary = [];
            $trainerStats = [];
    
            $trainers = Trainer::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation)
                ->get();
    
            foreach ($trainers as $trainer) {
                $trainingQuery = Training::where('is_disabled', '0')
                    ->where('organisation_id', $auth->active_organisation)
                    ->where('trainer_id', $request->trainer_id ?? $trainer->id);
    
                $totalTrainings = $trainingQuery->count();
                $inprogress = (clone $trainingQuery)->where('status', '0')->count(); // In progress
                $pending = (clone $trainingQuery)->where('status', '1')->count(); // Pending
                $completed = (clone $trainingQuery)->where('status', '2')->count(); // Completed
    
                $trainerSummary[] = [
                    'trainer_name' => $trainer->trainer_name,
                    'total_training_count' => $totalTrainings,
                    'total_inprogress' => $inprogress,
                    'total_pending' => $pending,
                    'total_completed' => $completed,
                ];
            }
    
            $trainerQuery = Trainer::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);

            $total_trainer = (clone $trainerQuery)->count(); //total
            $total_active = (clone $trainerQuery)->where('status', '0')->count(); //Active
            $total_inactive = (clone $trainerQuery)->where('status', '1')->count(); //InActive
            $total_new_trainer = (clone $trainerQuery)->whereDate('created_at', '>=', $lastMonthStart)
            ->whereDate('created_at', '<=', $todayDate)->count(); //new Trainer
                
            $trainerStats = [
                'total_trainer' => $total_trainer,
                'total_active' => $total_active,
                'total_inactive' => $total_inactive,
                'total_new_trainer' => $total_new_trainer,
            ];
            
            $response = [
                'trainer_summary' => $trainerSummary,
                'trainer_stats' => $trainerStats,
                'success' => true,
                'message' => 'success',
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred: ' . $e->getMessage()
            ], 500);
        }
    }

    public function organisationReportTrainingStatsSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            $trainingStats = [];
            // Get the first and last date of the last month
            $lastMonthStart = date('Y-m-01', strtotime('first day of last month'));
            $lastMonthEnd = date('Y-m-t', strtotime('last day of last month')) . ' 23:59:59'; 
            $todayDate = date('Y-m-d') . ' 23:59:59'; 
           

            $trainingQuery = Training::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);
               
            // if ($request['user_id']!='' && isset($request['user_id'])) {
            //     $trainingQuery->where('user_id', $request->user_id);
            // }

            $totalTrainings = $trainingQuery->count();
            $inprogress = (clone $trainingQuery)->where('status', '0')->count(); // In progress
            $pending = (clone $trainingQuery)->where('status', '1')->count(); // Pending
            $completed = (clone $trainingQuery)->where('status', '2')->count(); // Completed

            $trainingStats[] = [
                'total_training_count' => $totalTrainings,
                'total_inprogress' => $inprogress,
                'total_pending' => $pending,
                'total_completed' => $completed,
            ];
    
            $response = [
                'training_stats' => $trainingStats,
                'success' => true,
                'message' => 'success',
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred: ' . $e->getMessage()
            ], 500);
        }
    }
    

    public function organisationReportEmployeeAttritionTrend(Request $request)
    {
        try {
            // Fetch the year from the request or default to the current year
            $year = $request->input('year', now()->year);
            $fromDate = Carbon::create($year)->startOfYear()->format('d-m-Y');
            $toDate = Carbon::create($year)->endOfYear()->format('d-m-Y');
    
            $attritionData = Employee::selectRaw("MONTH(STR_TO_DATE(date_of_exit, '%d-%m-%Y')) as month, COUNT(*) as attrition_count")
                ->where('employee_status', 'Resigned')
                ->whereRaw("STR_TO_DATE(date_of_exit, '%d-%m-%Y') BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')",
                [$fromDate, $toDate])->groupByRaw("MONTH(STR_TO_DATE(date_of_exit, '%d-%m-%Y'))")
                ->pluck('attrition_count', 'month');
    
            // Initialize response with all months
            $trendData = [];
            for ($month = 1; $month <= 12; $month++) {
                $trendData[] = [
                    'month_name' => Carbon::create()->month($month)->format('F'),
                    'attrition_count' => $attritionData[$month] ?? 0, 
                ];
            }
            return response()->json(['attrition_report_year' => $trendData]);
        } catch (\Exception $e) {
            return response()->json([
                'error' => 'An error occurred while fetching the attrition trend.',
                'message' => $e->getMessage(),
            ], 500);
        }
    }

    public function organisationReportEmployeeAdditionTrend(Request $request)
    {
        try {
            // Fetch the year from the request or default to the current year
            $year = $request->input('year', now()->year);
            $fromDate = Carbon::create($year)->startOfYear()->format('d-m-Y');
            $toDate = Carbon::create($year)->endOfYear()->format('d-m-Y');
    
            // Fetch addition data grouped by month
            $additionData = Employee::selectRaw("MONTH(STR_TO_DATE(joining_date, '%d-%m-%Y')) as month, COUNT(*) as addition_count")
                ->whereRaw(
                    "STR_TO_DATE(joining_date, '%d-%m-%Y') BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')",
                    [$fromDate, $toDate]
                )
                ->groupByRaw("MONTH(STR_TO_DATE(joining_date, '%d-%m-%Y'))")
                ->pluck('addition_count', 'month');
    
            // Initialize response with all months
            $trendData = [];
            for ($month = 1; $month <= 12; $month++) {
                $trendData[] = [
                    'month_name' => Carbon::create()->month($month)->format('F'), // Get month name
                    'addition_count' => $additionData[$month] ?? 0, // Use addition count or default to 0
                ];
            }
    
            return response()->json(['addition_report_year' => $trendData]);
        } catch (\Exception $e) {
            return response()->json([
                'error' => 'An error occurred while fetching the addition trend.',
                'message' => $e->getMessage(),
            ], 500);
        }
    }
    

    public function organisationReportHeadcount(Request $request)
    {
        try {
            // Fetch the year from the request or default to the current year
            $year = $request->input('year', now()->year);
            $fromDate = Carbon::create($year)->startOfYear()->format('d-m-Y');
            $toDate = Carbon::create($year)->endOfYear()->format('d-m-Y');
    
            // Fetch headcount data grouped by month
            $headcountData = Employee::selectRaw("
                MONTH(STR_TO_DATE(joining_date, '%d-%m-%Y')) as month,
                SUM(CASE WHEN employee_status = 'Active' THEN 1 ELSE 0 END) as active_count,
                SUM(CASE WHEN employee_status IN ('Inactive', 'Resigned', 'Terminated') THEN 1 ELSE 0 END) as inactive_count
            ")
            ->whereRaw("
                STR_TO_DATE(joining_date, '%d-%m-%Y') BETWEEN STR_TO_DATE(?, '%d-%m-%Y') AND STR_TO_DATE(?, '%d-%m-%Y')
            ", [$fromDate, $toDate])
            ->groupByRaw("MONTH(STR_TO_DATE(joining_date, '%d-%m-%Y'))")
            ->get()
            ->keyBy('month');
    
            $trendData = [];
            for ($month = 1; $month <= 12; $month++) {
                $trendData[] = [
                    'month_name' => Carbon::create()->month($month)->format('F'), // Get month name
                    'active_count' => $headcountData[$month]['active_count'] ?? 0, // Default active count to 0
                    'inactive_count' => $headcountData[$month]['inactive_count'] ?? 0, // Default inactive count to 0
                ];
            }
    
            return response()->json(['headcount_report_year' => $trendData]);
        } catch (\Exception $e) {
            return response()->json([
                'error' => 'An error occurred while fetching the headcount trend.',
                'message' => $e->getMessage(),
            ], 500);
        }
    }
    
    public function organisationReportJobsStatsSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            $jobOpeningStats = [];
           
            $jobOpeningQuery = JobOpening::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);
               
            $totalJobOpening = $jobOpeningQuery->count();
            $job_open = (clone $jobOpeningQuery)->where('job_status', 'Open')->count(); // Open
            $job_draft = (clone $jobOpeningQuery)->where('job_status', 'Draft')->count(); // Draft
            $job_filled = (clone $jobOpeningQuery)->where('job_status', 'Filled')->count(); // Filled
            $job_onhold = (clone $jobOpeningQuery)->where('job_status', 'On Hold')->count(); // On Hold
            $job_cancelled = (clone $jobOpeningQuery)->where('job_status', 'Cancelled')->count(); // job_cancelled

            $jobOpeningStats= [
                'job_open' => $job_open,
                'job_draft' => $job_draft,
                'job_filled' => $job_filled,
                'job_onhold' => $job_onhold,
                'job_cancelled' => $job_cancelled,
            ];
    
            $response = [
                'job_opening_stats' => $jobOpeningStats,
                'total_job_opening_count' => $totalJobOpening,
                'success' => true,
                'message' => 'success',
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred: ' . $e->getMessage()
            ], 500);
        }
    }

    public function organisationReportApplicantStatsSummary(Request $request)
    {
        $auth = Auth::user();
    
        try {
            $applicantStats = [];
           
            $applicantQuery = Applicant::where('is_disabled', '0')
                ->where('organisation_id', $auth->active_organisation);
               
            $totalApplicant = $applicantQuery->count();
            $applicant_new = (clone $applicantQuery)->where('status', 'New')->count(); // New
            $applicant_interviewed = (clone $applicantQuery)->where('status', 'Interviewed')->count(); // Interviewed
            $applicant_schedule = (clone $applicantQuery)->where('status', 'Schedule')->count(); // Schedule
            $applicant_hired = (clone $applicantQuery)->where('status', 'Hired')->count(); // Hired
            $applicant_OnHold = (clone $applicantQuery)->where('status', 'On Hold')->count(); // On Hold
            $applicant_rejected = (clone $applicantQuery)->where('status', 'Rejected')->count(); // Rejected

            $applicantStats= [
                'applicant_new' => $applicant_new,
                'applicant_interviewed' => $applicant_interviewed,
                'applicant_schedule' => $applicant_schedule,
                'applicant_hired' => $applicant_hired,
                'applicant_OnHold' => $applicant_OnHold,
                'applicant_rejected' => $applicant_rejected,
            ];
    
            $response = [
                'applicant_stats' => $applicantStats,
                'total_applicant_count' => $totalApplicant,
                'success' => true,
                'message' => 'success',
            ];
    
            return response()->json($response);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'An error occurred: ' . $e->getMessage()
            ], 500);
        }
    }
    

    public function organisationReportAttendanceStatsSummary(Request $request)
    {
        $user = Auth::user();
        $response = [];
        
        if ($user != null) {
            $todayDate = date('d-m-Y');
    
            $filter = $request->input('filter', 'today'); // Default to "today"
            $startDate = $todayDate;
            $endDate = $todayDate;
    
            if ($filter == 'week') {
                $startDate = date('d-m-Y', strtotime('monday this week'));
                $endDate = date('d-m-Y', strtotime('sunday this week'));
            } elseif ($filter == 'month') {
                $startDate = date('01-m-Y');
                $endDate = date('t-m-Y');
            }
    
            // Base attendance query
            $attendanceList = Attendance::where('is_disabled', '0')
                ->where('organisation_id', $user['active_organisation'])
                ->where('date', '>=', $startDate)->where('date', '<=', $endDate);
    
                        
            if (!empty($request->user_id)) {
                $attendanceList->where('user_id', $request->user_id);
            }

            // Counts for the selected period
            $total_present = (clone $attendanceList)->where('status', '0')->count();
            $total_absent = (clone $attendanceList)->where('status', '1')->count();
            $total_halfday = (clone $attendanceList)->where('status', '2')->count();

            $total_emp = Employee::where('is_disabled', '0')
                ->where('organisation_id', $user['active_organisation'])
                ->count();

    
            // Response data
            $response = [
                'total_employee' => $total_emp,
                'total_present' => $total_present,
                'total_absent' => $total_absent,
                'total_halfday' => $total_halfday,
                'message' => 'Successful',
            ];
        } else {
            $response = [
                'message' => 'Invalid User',
            ];
        }
    
        return response()->json($response);
    }

    public function organisationReportAttendanceCalendarSummary(Request $request)
    {
        $user = Auth::user();
        $todayDate = date('Y-m-d');
    
        if ($user != null) {
            $attendanceSummaryQuery = Attendance::where('is_disabled', '0')
                ->with('employee:user_id,first_name,last_name')
                ->where('organisation_id', $user['active_organisation']);
    
            // Filter by fiscal year (fy) if provided
            if (!empty($request->fy)) {
                $attendanceSummaryQuery = $attendanceSummaryQuery->whereYear('date', $request->fy);
            }
    
            // Filter by month if provided
            if (!empty($request->month)) {
                $month = date('m', strtotime($request->month)); // Convert month name to month number
                $attendanceSummaryQuery = $attendanceSummaryQuery->whereMonth('date', $month);
            }
    
                                    
            if (!empty($request->user_id)) {
                $attendanceSummaryQuery->where('user_id', $request->user_id);
            }

            // Fetch holidays for the given organisation
            $holidays = Holiday::where('is_disabled', '0')
                ->where('organisation_id', $user['active_organisation'])
                ->get();
    
            // Apply pagination before fetching the results
            $attendanceSummary = $attendanceSummaryQuery->orderBy('id', 'desc')
                ->skip($request['noofrec'] * ($request['currentpage'] - 1))
                ->take($request['noofrec'] ?? 100)
                ->get();
    
            // Iterate over each attendance entry and check for matching holidays
            foreach ($attendanceSummary as $attendance) {
                $attendanceDate = $attendance->date;
    
                // Check if the attendance date matches any holiday range
                $matchingHoliday = $holidays->first(function ($holiday) use ($attendanceDate) {
                    return $attendanceDate >= $holiday->from_date && $attendanceDate <= $holiday->to_date;
                });
    
                // If a matching holiday is found, attach its name to the attendance record
                if ($matchingHoliday) {
                    $attendance->holiday_name = $matchingHoliday->holiday_name ?? 'Unnamed Holiday';
                } else {
                    $attendance->holiday_name = null; // No holiday on this date
                }
            }
    
            $response = [
                'attendanceSummary' => $attendanceSummary,
                'message' => 'Successful',
            ];
    
        } else {
            $response = [
                'attendanceSummary' => [],
                'message' => 'Invalid User',
            ];
        }
    
        return response()->json($response);
    }
    
}
