<?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\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\Performance;
use App\Models\Hrms\Holiday;
use App\Models\Hrms\Leave;
use App\Models\User;
use Validator;
use Auth;

class MyReportController extends Controller
{

    public function exportDatabase(Request $request)
    {
        // try {
            // Database configuration
            $dbName = env('DB_DATABASE');
            $dbUser = env('DB_USERNAME');
            $dbPass = env('DB_PASSWORD');
            $dbHost = env('DB_HOST');
            $fileName = "backup_" . date('Ymd_His') . ".sql";
            $filePath = storage_path("app/public/{$fileName}");
    
            // Ensure the storage path exists
            if (!file_exists(storage_path('app/public'))) {
                mkdir(storage_path('app/public'), 0755, true);
            }
    
            // Command to dump the database
            $command = sprintf(
                'mysqldump --user=%s --password=%s --host=%s %s > %s',
                escapeshellarg($dbUser),
                escapeshellarg($dbPass),
                escapeshellarg($dbHost),
                escapeshellarg($dbName),
                escapeshellarg($filePath)
            );
    
            // Execute the command
            exec($command, $output, $result);
    
            // Check if the command succeeded
            if ($result !== 0) {
                return response()->json([
                    'message' => 'Failed to create database backup. Please check your configuration.',
                    'error' => implode("\n", $output),
                ], 500);
            }
    
            // Return the file for download
            return response()->download($filePath)->deleteFileAfterSend(true);
    
        // } catch (\Exception $e) {
        //     // Handle errors gracefully
        //     return response()->json([
        //         'message' => 'An error occurred while exporting the database.',
        //         'error' => $e->getMessage(),
        //     ], 500);
        // }
    }
    
    public function myLeaveReportStatus(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('user_id', $auth['id'])
                    ->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('user_id', $auth['id'])
                    ->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 myLeaveReportSummary(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('user_id', $auth['id'])
                    ->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 myLeaveReportYear(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('user_id', $auth['id'])
                ->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 myReportPerformance(Request $request) 
    {
        $auth = Auth::user();
        try {

            if ($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
            }

            $performance = Performance::where('is_disabled', '0')->where('user_id', $auth['id'])
            ->where('organisation_id', $auth->active_organisation) 
            ->where('updated_at', '>=', $fromDate)->where('updated_at', '<=', $toDate)->first();

        
            $response = [
                'result' => $performance,
                'success' => true,
            ];
        
            return response()->json($response);

        } catch (\Exception $e) {
            return response()->json(['error' => true, 'message' => $e->getMessage()], 500);
        }
    }


    public function myReportAttendanceStatsSummary(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('user_id', $user['id'])
                ->where('organisation_id', $user['active_organisation'])
                ->where('date', '>=', $startDate)->where('date', '<=', $endDate);
    
            // 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 myReportemployeeAttendance(Request $request)
    {
        $auth = Auth::user();
        $response = [];
    
        if ($auth != null) {
            $todayDate = date('d-m-Y');
            $startDateWeek = date('d-m-Y', strtotime('monday this week'));
            $endDateWeek = date('d-m-Y', strtotime('sunday this week')) . ' 23:59:59';
            $startDateMonth = date('01-m-Y');
            $endDateMonth = date('t-m-Y') . ' 23:59:59';
    
            // Base query
            $query = Attendance::where('is_disabled', '0')->where('user_id', $auth['id'])
                ->where('organisation_id', $auth['active_organisation']);
    
            // Fetch today's attendance
            $todayData = (clone $query)->where('date', $todayDate)->first();
    
            // Fetch weekly attendance
            $weekData = (clone $query)
                ->whereBetween('date', [$startDateWeek, $endDateWeek])
                ->get();
    
            // Fetch monthly attendance
            $monthData = (clone $query)
                ->whereBetween('date', [$startDateMonth, $endDateMonth])
                ->get();
    
            // Calculate required fields
            $today = $this->processAttendanceData($todayData, 'today');
            $week = $this->processAttendanceData($weekData, 'week');
            $month = $this->processAttendanceData($monthData, 'month');
    
            // Prepare response
            $response = [
                'EmployeeAttendance' => [
                    [
                        'today' => $today,
                        'week' => $week,
                        'month' => $month,
                    ]
                ],
                'message' => 'Successful',
            ];
        } else {
            $response = [
                'message' => 'Invalid User',
            ];
        }
    
        return response()->json($response);
    }
  
    private function processAttendanceData($data, $scope)
    {
        if ($scope === 'today' && $data) {
            return [
                'total_hours_worked' => $data->total_hours_worked,
                'needHours' => 9, // Assume 9 hours are needed daily
                'PunchInTime' => $data->punch_in,
                'PunchOutTime' => $data->punch_out,
                'doBreakTime' => '40min', // Placeholder for actual break time calculation
                'OverTime' => $data->overtime,
                'ThisWeekHours' => 36, // Placeholder for weekly performance
            ];
        }
    
        if ($scope === 'week') {
            return [
                'total_hours_worked' => $data->total_hours_worked,
                'needHours' => 48, // Assume 8 hours/day * 6 days
                'PunchInTime' => $data->punch_in,
                'PunchOutTime' => $data->punch_out,
                'doBreakTime' => '4.6hr', // Placeholder
                'needBreakTime' => '4hr', // Placeholder
                'OverTime' => $data->overtime,
                'weekHours' => [38, 48, 48, 48, 44, 29], // Placeholder for actual daily hours
                'lastWeekHours' => 46, // Placeholder for previous week's total
            ];
        }
    
        if ($scope === 'month') {
            return [
                'total_hours_worked' => $data->total_hours_worked,
                'needHours' => 180, // Assume 9 hours/day * 20 workdays
                'PunchInTime' => $data->punch_in,
                'PunchOutTime' => $data->punch_out,
                'doBreakTime' => '11.6hr', // Placeholder
                'needBreakTime' => '14hr', // Placeholder
                'OverTime' => $data->overtime,
                'monthWeekHours' => [38, 48, 48, 48, 44, 29], // Placeholder for weekly data
                'lastMonthHours' => 198, // Placeholder for last month's total
            ];
        }
    
        return [];
    }

    public function myReportAttendanceCalendarSummary(Request $request)
    {
        $user = Auth::user();
        $todayDate = date('Y-m-d');
    
        if ($user != null) {
            $attendanceSummaryQuery = Attendance::where('is_disabled', '0')->where('user_id', $user['id'])
                ->with('employee:user_id,first_name,last_name')
                ->where('organisation_id', $user['active_organisation']);
    
            if (!empty($request->fy)) {
                $attendanceSummaryQuery = $attendanceSummaryQuery->whereYear('date', $request->fy);
            }
    
            if (!empty($request->month)) {
                $month = date('m', strtotime($request->month)); 
                $attendanceSummaryQuery = $attendanceSummaryQuery->whereMonth('date', $month);
            }

            $holidays = Holiday::where('is_disabled', '0')
                ->where('organisation_id', $user['active_organisation'])
                ->get();
    
            $attendanceSummary = $attendanceSummaryQuery->orderBy('id', 'desc')
                ->skip($request['noofrec'] * ($request['currentpage'] - 1))
                ->take($request['noofrec'] ?? 100)
                ->get();
    
            foreach ($attendanceSummary as $attendance) {
                $attendanceDate = $attendance->date;
    
                $matchingHoliday = $holidays->first(function ($holiday) use ($attendanceDate) {
                    return $attendanceDate >= $holiday->from_date && $attendanceDate <= $holiday->to_date;
                });
    
                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);
    }
    

}



