000

<?php
namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use App\Models\User;
use Illuminate\Support\Facades\DB;
use Carbon\Carbon;
USE Illuminate\Support\Facades\Log;

class DashboardController extends Controller
{

//new notification add
// public function ccbf_dashboard(Request $request)
//     {
//         $loggedInUser = Auth::user();
//         $userRole = $loggedInUser->role;
//         $userPrimarySiteName = $loggedInUser->site_name; // Use site_name from users table

//         $targetSiteName = null; // This will be the site_name used for filtering queries
//         $displayMessage = null;

//         $defaultMonthlyData = array_replace(array_fill_keys(range(1, 12), 0), []);
//         $defaultLandStageData = ['Standing Crop' => 0, 'Crop Sowing within month' => 0, 'Empty Plot' => 0];

//         if ($userRole == 1) { // Super Admin
//             // Super admin can view a specific site if 'site_name' is in the request query
//             if ($request->has('site_name') && !empty($request->input('site_name'))) {
//                 $targetSiteName = $request->input('site_name');
//             }
//         } else { // Non-Super Admin Users
//             $targetSiteName = $userPrimarySiteName;
//             if (!$targetSiteName) {
//                 $displayMessage = 'You are not currently assigned to a specific site. Please contact an administrator. Displaying global data or zeros where site-specific data is required.';
//                 Log::warning("User {$loggedInUser->id} (Role: {$userRole}) has no site_name assigned.");
//             }
//         }

//         $currentMonth = now()->month;

//         // --- Apply site_name filtering to all relevant queries ---
//         // IMPORTANT: Assumes other tables have a 'site_name' column for filtering.

//         $monthlyQuery = DB::table('land_prepration');
//         if ($targetSiteName) {
//             // Assuming 'site_id' in land_prepration corresponds to 'site_name' in users for filtering
//             $monthlyQuery->where('site_id', $targetSiteName);
//         }
//         $monthlyDataRaw = $monthlyQuery
//             ->selectRaw('MONTH(date) as month, SUM(area_covered) as total_area')
//             ->groupBy(DB::raw('MONTH(date)'))
//             ->pluck('total_area', 'month')
//             ->toArray();
//         $monthlyData = array_replace(array_fill_keys(range(1, 12), 0), $monthlyDataRaw);

//         $totalStockQuery = DB::table('master_fertilizer');
//         if ($targetSiteName) {
//             $totalStockQuery->where('site_id', $targetSiteName);
//         }
//         $totalStock = $totalStockQuery->sum('stock_kg');

//         $totalUsedQuery = DB::table('fertilizer_soil_record');
//         if ($targetSiteName) {
//             $totalUsedQuery->where('site_id', $targetSiteName);
//         }
//         $totalUsed = $totalUsedQuery
//             ->whereNotNull('fertilizer_quantity')
//             ->sum('fertilizer_quantity');

//         $seedStockQuery = DB::table('master_seed');
//         if ($targetSiteName) {
//             $seedStockQuery->where('site_id', $targetSiteName);
//         }
//         $seedStock = $seedStockQuery->sum('seed_stock_kg');

//         $seedUsedQuery = DB::table('showing_oprations');
//         if ($targetSiteName) {
//             $seedUsedQuery->where('site_id', $targetSiteName);
//         }
//         $seedUsed = $seedUsedQuery
//             ->whereNotNull('seed_consumption')
//             ->sum('seed_consumption');

//         $totalHayQuery = DB::table('hay_making');
//         if ($targetSiteName) {
//             $totalHayQuery->where('site_id', $targetSiteName);
//         }
//         $totalHay = $totalHayQuery->sum('yield_mt');
//         $usedHay = $totalHay; // Assuming usedHay is the same as totalHay for now based on your code

//         $baseShowingQuery = DB::table('showing_oprations');
//         if ($targetSiteName) {
//             $baseShowingQuery->where('site_id', $targetSiteName);
//         }

//         $totalPlots = (clone $baseShowingQuery)->count();
//         $totalWithCrop = (clone $baseShowingQuery)->whereNotNull('date')->count();
//         $cropSowingThisMonth = (clone $baseShowingQuery)
//             ->whereMonth('date', $currentMonth)
//             ->whereNotNull('date')
//             ->count();

//         $standingCrop = $totalWithCrop - $cropSowingThisMonth;
//         $emptyPlots = $totalPlots - $totalWithCrop;

//         $landStageData = [
//             'Standing Crop' => $standingCrop,
//             'Crop Sowing within month' => $cropSowingThisMonth,
//             'Empty Plot' => $emptyPlots,
//         ];

//         // Base user query for total user count stat card
//         $baseUserCountQuery = User::query();
//         if ($targetSiteName) {
//             $baseUserCountQuery->where('site_name', $targetSiteName);
//         }
//         $userCount = $baseUserCountQuery->count(); // Total users for the 'Users' stat card

//         $fertilizerMasterQuery = DB::table('master_fertilizer');
//         if ($targetSiteName) {
//             $fertilizerMasterQuery->where('site_id', $targetSiteName);
//         }
//         $fertilizerCount = $fertilizerMasterQuery->count();

//         $machineMasterQuery = DB::table('master_machine');
//         if ($targetSiteName) {
//             $machineMasterQuery->where('site_id', $targetSiteName);
//         }
//         $machineCount = $machineMasterQuery->count();

//         $sitesForSelector = [];
//         if ($userRole == 1) {
//             // Fetch distinct site_names from the users table for the selector
//             $sitesForSelector = User::select('site_name')
//                 ->whereNotNull('site_name')
//                 ->distinct()
//                 ->orderBy('site_name')
//                 ->get();
//         }

//         // This block handles cases where non-admin users have no site assigned
//         // It sets data to default zeros or global counts as per your original logic
//         if ($userRole != 1 && $userPrimarySiteName == null && !$targetSiteName) {
//             $monthlyData = $defaultMonthlyData;
//             $landStageData = $defaultLandStageData;
//             $totalStock = 0; $totalUsed = 0;
//             $seedStock = 0; $seedUsed = 0;
//             $totalHay = 0; $usedHay = 0;
//             // For users with no site, show global counts for these summary stats
//             $userCount = DB::table('users')->count(); // Revert to global count if no site assigned
//             $fertilizerCount = DB::table('master_fertilizer')->count();
//             $machineCount = DB::table('master_machine')->count();
//         }

//         // --- START NEW LOGIC FOR USER ONLINE/OFFLINE STATUS ---
//         // Define what "online" means: e.g., active in the last 5 minutes.
//         // You can adjust this threshold as per your requirements.
//         $onlineThresholdMinutes = 5;
//         $offlineTime = Carbon::now()->subMinutes($onlineThresholdMinutes);

//         // Build a query specifically for online/offline counts, applying site filtering
//         $onlineOfflineUserQuery = User::query();
//         if ($targetSiteName) {
//             $onlineOfflineUserQuery->where('site_name', $targetSiteName);
//         }

//         // Count online users
//         $onlineUsersCount = (clone $onlineOfflineUserQuery)
//                                 ->where('last_activity_at', '>=', $offlineTime)
//                                 ->count();

//         // Count offline users (activity before threshold or never recorded)
//         $offlineUsersCount = (clone $onlineOfflineUserQuery)
//                                 ->where(function($query) use ($offlineTime) {
//                                     $query->where('last_activity_at', '<', $offlineTime)
//                                           ->orWhereNull('last_activity_at');
//                                 })
//                                 ->count();
//         // --- END NEW LOGIC ---

//         // --- START NEW LOGIC FOR MOVING COUNTING AND MOBILE USER GPS ---
//         $recentGpsThresholdMinutes = 10; // GPS data considered recent if within this many minutes
//         $recentGpsTime = Carbon::now()->subMinutes($recentGpsThresholdMinutes);

//         $mobileUserQuery = User::query();
//         if ($targetSiteName) {
//             $mobileUserQuery->where('site_name', $targetSiteName);
//         }

//         // Count users currently detected as moving (and have sent recent GPS data)
//         $movingUsersCount = (clone $mobileUserQuery)
//                                 ->where('is_moving', true)
//                                 ->where('last_gps_update_at', '>=', $recentGpsTime)
//                                 ->count();

//         // Count users not moving (and have sent recent GPS data)
//         $notMovingUsersCount = (clone $mobileUserQuery)
//                                 ->where('is_moving', false)
//                                 ->where('last_gps_update_at', '>=', $recentGpsTime)
//                                 ->count();

//         // Count users with GPS ON (and have sent recent GPS data, and gps_status is true)
//         $gpsOnUsersCount = (clone $mobileUserQuery)
//                                 ->where('gps_status', true)
//                                 ->where('last_gps_update_at', '>=', $recentGpsTime)
//                                 ->count();

//         // Count users with GPS OFF (or no recent GPS data, or gps_status is explicitly false)
//         $gpsOffUsersCount = (clone $mobileUserQuery)
//                                 ->where(function($query) use ($recentGpsTime) {
//                                     $query->where('gps_status', false) // GPS explicitly reported as off
//                                           ->orWhereNull('last_gps_update_at') // Never sent GPS data
//                                           ->orWhere('last_gps_update_at', '<', $recentGpsTime); // GPS data is too old
//                                 })
//                                 ->count();
//         // --- END NEW LOGIC ---

//         // --- NEW LOGIC FOR FETCHING NOTIFICATIONS ---
//         // --- Notifications Fetching Logic ---
//         $notificationsQuery = DB::table('notifications')
//             ->leftJoin('users', 'notifications.user_id', '=', 'users.id')
//             ->select(
//                 'notifications.*',
//                 'users.name as user_name' // Assuming 'name' is the user's name column
//             )
//             ->orderBy('notifications.created_at', 'desc'); // Order by newest first

//         // Apply site_name filtering to notifications if a target site is selected
//         // IMPORTANT: This assumes your 'notifications' table has a 'site_name' column.
//         // If not, you might need to filter based on 'users.site_name' after the join,
//         // or a different join/filter logic if notifications are site-specific but not directly on the notification record.
//         if ($targetSiteName) {
//              $notificationsQuery->where('notifications.site_name', $targetSiteName);
//              // ALTERNATIVE if site_name is only on users table:
//              // $notificationsQuery->where('users.site_name', $targetSiteName);
//         }
        
//         $notifications = $notificationsQuery->get(); // Consider adding ->paginate(10) for large number of notifications
//         // --- END NEW LOGIC FOR FETCHING NOTIFICATIONS ---

//         return view('admin.dashboard', compact(
//             'monthlyData',
//             'totalStock', 'totalUsed',
//             'seedStock', 'seedUsed',
//             'landStageData',
//             'totalHay', 'usedHay',
//             'userCount', 'fertilizerCount', 'machineCount',
//             'sitesForSelector',
//             'targetSiteName',
//             'displayMessage',
//             'onlineUsersCount',
//             'offlineUsersCount',
//             'movingUsersCount',
//             'notMovingUsersCount',
//             'gpsOnUsersCount',
//             'gpsOffUsersCount',
//             'notifications' // Pass the notifications to the view
//         ));
//     }

public function ccbf_dashboard(Request $request)
{
    $loggedInUser = Auth::user();
    $userRole = $loggedInUser->role;
    $userPrimarySiteName = $loggedInUser->site_name; // Use site_name from users table

$targetSiteName = null; // This will be the site_name used for filtering queries
    $displayMessage = null;

$defaultMonthlyData = array_replace(array_fill_keys(range(1, 12), 0), []);
    $defaultLandStageData = ['Standing Crop' => 0, 'Crop Sowing within month' => 0, 'Empty Plot' => 0];

if ($userRole == 1) { // Super Admin
        // Super admin can view a specific site if 'site_name' is in the request query
        if ($request->has('site_name') && !empty($request->input('site_name'))) {
            $targetSiteName = $request->input('site_name');
        }
    } elseif ($userRole == 3) { // Role 3
        $targetSiteName = '2'; // Set targetSiteName to site 2 for role 3
    } else { // Non-Super Admin Users
        $targetSiteName = $userPrimarySiteName;
        if (!$targetSiteName) {
            $displayMessage = 'You are not currently assigned to a specific site. Please contact an administrator. Displaying global data or zeros where site-specific data is required.';
            Log::warning("User {$loggedInUser->id} (Role: {$userRole}) has no site_name assigned.");
        }
    }

$currentMonth = now()->month;

// --- Updated logic for total land graph ---
    $monthlyQuery = DB::table('master_land');
    if ($targetSiteName) {
        $monthlyQuery->where('site_id', $targetSiteName);
    }
    $monthlyDataRaw = $monthlyQuery
        ->selectRaw('MONTH(created_at) as month, SUM(area_ha) as total_area')
        ->where('is_deleted', 0) // Exclude deleted records
        ->groupBy(DB::raw('MONTH(created_at)'))
        ->pluck('total_area', 'month')
        ->toArray();
    $monthlyData = array_replace(array_fill_keys(range(1, 12), 0), $monthlyDataRaw);

$totalStockQuery = DB::table('master_fertilizer');
    if ($targetSiteName) {
        $totalStockQuery->where('site_id', $targetSiteName);
    }
    $totalStock = $totalStockQuery->sum('stock_kg');

$totalUsedQuery = DB::table('fertilizer_soil_record');
    if ($targetSiteName) {
        $totalUsedQuery->where('site_id', $targetSiteName);
    }
    $totalUsed = $totalUsedQuery
        ->whereNotNull('fertilizer_quantity')
        ->sum('fertilizer_quantity');

$seedStockQuery = DB::table('master_seed');
    if ($targetSiteName) {
        $seedStockQuery->where('site_id', $targetSiteName);
    }
    $seedStock = $seedStockQuery->sum('seed_stock_kg');

$seedUsedQuery = DB::table('showing_oprations');
    if ($targetSiteName) {
        $seedUsedQuery->where('site_id', $targetSiteName);
    }
    $seedUsed = $seedUsedQuery
        ->whereNotNull('seed_consumption')
        ->sum('seed_consumption');

$totalHayQuery = DB::table('hay_making');
    if ($targetSiteName) {
        $totalHayQuery->where('site_id', $targetSiteName);
    }
    $totalHay = $totalHayQuery->sum('yield_mt');
    $usedHay = $totalHay; // Assuming usedHay is the same as totalHay for now based on your code

$baseShowingQuery = DB::table('showing_oprations');
    if ($targetSiteName) {
        $baseShowingQuery->where('site_id', $targetSiteName);
    }

$totalPlots = (clone $baseShowingQuery)->count();
    $totalWithCrop = (clone $baseShowingQuery)->whereNotNull('date')->count();
    $cropSowingThisMonth = (clone $baseShowingQuery)
        ->whereMonth('date', $currentMonth)
        ->whereNotNull('date')
        ->count();

$standingCrop = $totalWithCrop - $cropSowingThisMonth;
    $emptyPlots = $totalPlots - $totalWithCrop;

$landStageData = [
        'Standing Crop' => $standingCrop,
        'Crop Sowing within month' => $cropSowingThisMonth,
        'Empty Plot' => $emptyPlots,
    ];

// Base user query for total user count stat card
    $baseUserCountQuery = User::query();
    if ($targetSiteName) {
        $baseUserCountQuery->where('site_name', $targetSiteName);
    }
    $userCount = $baseUserCountQuery->count(); // Total users for the 'Users' stat card

$fertilizerMasterQuery = DB::table('master_fertilizer');
    if ($targetSiteName) {
        $fertilizerMasterQuery->where('site_id', $targetSiteName);
    }
    $fertilizerCount = $fertilizerMasterQuery->count();

$machineMasterQuery = DB::table('master_machine');
    if ($targetSiteName) {
        $machineMasterQuery->where('site_id', $targetSiteName);
    }
    $machineCount = $machineMasterQuery->count();
    
    // Dynamic crop count from master_seed table
        $cropCountQuery = DB::table('master_seed');
        if ($targetSiteName) {
            $cropCountQuery->where('site_id', $targetSiteName);
        }
$cropCount = $cropCountQuery->count(); // No distinct, all rows count

$sitesForSelector = [];
    if ($userRole == 1) {
        // Fetch distinct site_names from the users table for the selector
        $sitesForSelector = User::select('site_name')
            ->whereNotNull('site_name')
            ->distinct()
            ->orderBy('site_name')
            ->get();
    }

// This block handles cases where non-admin users have no site assigned
    // It sets data to default zeros or global counts as per your original logic
    if ($userRole != 1 && $userPrimarySiteName == null && !$targetSiteName) {
        $monthlyData = $defaultMonthlyData;
        $landStageData = $defaultLandStageData;
        $totalStock = 0; $totalUsed = 0;
        $seedStock = 0; $seedUsed = 0;
        $totalHay = 0; $usedHay = 0;
        // For users with no site, show global counts for these summary stats
        $userCount = DB::table('users')->count(); // Revert to global count if no site assigned
        $fertilizerCount = DB::table('master_fertilizer')->count();
        $machineCount = DB::table('master_machine')->count();
        $cropCount = DB::table('master_seed')->distinct()->count('seed_name'); // Global crop count
    }

// --- START NEW LOGIC FOR USER ONLINE/OFFLINE STATUS ---
    // Define what "online" means: e.g., active in the last 5 minutes.
    // You can adjust this threshold as per your requirements.
    $onlineThresholdMinutes = 5;
    $offlineTime = Carbon::now()->subMinutes($onlineThresholdMinutes);

// Build a query specifically for online/offline counts, applying site filtering
    $onlineOfflineUserQuery = User::query();
    if ($targetSiteName) {
        $onlineOfflineUserQuery->where('site_name', $targetSiteName);
    }

// Count online users
    $onlineUsersCount = (clone $onlineOfflineUserQuery)
                            ->where('last_activity_at', '>=', $offlineTime)
                            ->count();

// Count offline users (activity before threshold or never recorded)
    $offlineUsersCount = (clone $onlineOfflineUserQuery)
                            ->where(function($query) use ($offlineTime) {
                                $query->where('last_activity_at', '<', $offlineTime)
                                      ->orWhereNull('last_activity_at');
                            })
                            ->count();
    // --- END NEW LOGIC ---

// --- START NEW LOGIC FOR MOVING COUNTING AND MOBILE USER GPS ---
    $recentGpsThresholdMinutes = 10; // GPS data considered recent if within this many minutes
    $recentGpsTime = Carbon::now()->subMinutes($recentGpsThresholdMinutes);

$mobileUserQuery = User::query();
    if ($targetSiteName) {
        $mobileUserQuery->where('site_name', $targetSiteName);
    }

// Count users currently detected as moving (and have sent recent GPS data)
    $movingUsersCount = (clone $mobileUserQuery)
                            ->where('is_moving', true)
                            ->where('last_gps_update_at', '>=', $recentGpsTime)
                            ->count();

// Count users not moving (and have sent recent GPS data)
    $notMovingUsersCount = (clone $mobileUserQuery)
                            ->where('is_moving', false)
                            ->where('last_gps_update_at', '>=', $recentGpsTime)
                            ->count();

// Count users with GPS ON (and have sent recent GPS data, and gps_status is true)
    $gpsOnUsersCount = (clone $mobileUserQuery)
                            ->where('gps_status', true)
                            ->where('last_gps_update_at', '>=', $recentGpsTime)
                            ->count();

// Count users with GPS OFF (or no recent GPS data, or gps_status is explicitly false)
    $gpsOffUsersCount = (clone $mobileUserQuery)
                            ->where(function($query) use ($recentGpsTime) {
                                $query->where('gps_status', false) // GPS explicitly reported as off
                                      ->orWhereNull('last_gps_update_at') // Never sent GPS data
                                      ->orWhere('last_gps_update_at', '<', $recentGpsTime); // GPS data is too old
                            })
                            ->count();
    // --- END NEW LOGIC ---

// --- NEW LOGIC FOR FETCHING NOTIFICATIONS ---
    // --- Notifications Fetching Logic ---
    $notificationsQuery = DB::table('notifications')
        ->leftJoin('users', 'notifications.user_id', '=', 'users.id')
        ->select(
            'notifications.*',
            'users.name as user_name' // Assuming 'name' is the user's name column
        )
        ->orderBy('notifications.created_at', 'desc'); // Order by newest first

// Apply site_name filtering to notifications if a target site is selected
    if ($targetSiteName) {
         $notificationsQuery->where('notifications.site_name', $targetSiteName);
    }
    
    $notifications = $notificationsQuery->get(); // Consider adding ->paginate(10) for large number of notifications
    // --- END NEW LOGIC FOR FETCHING NOTIFICATIONS ---

return view('admin.dashboard', compact(
        'monthlyData',
        'totalStock', 'totalUsed',
        'seedStock', 'seedUsed',
        'landStageData',
        'totalHay', 'usedHay',
        'userCount', 'fertilizerCount', 'machineCount','cropCount',
        'sitesForSelector',
        'targetSiteName',
        'displayMessage',
        'onlineUsersCount',
        'offlineUsersCount',
        'movingUsersCount',
        'notMovingUsersCount',
        'gpsOnUsersCount',
        'gpsOffUsersCount',
        'notifications' // Pass the notifications to the view
    ));
}
    
}