000

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Validator;
use App\Models\User;
use Laravel\Sanctum\Sanctum;
use Illuminate\Support\Facades\Route;
use Illuminate\Support\Facades\Hash;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Http;
use Illuminate\Support\Carbon;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Schema;

class ApiController extends Controller
{
     //wether api
     public function getForecast(Request $request)
     {
         $lat = $request->lat ?? '26.8682246';
         $lon = $request->lon ?? '80.9933125';
         $apiKey = 'b41274328f52dcb04a9f6aff4c48c85a';
 
         $url = "https://api.openweathermap.org/data/2.5/forecast?lat={$lat}&lon={$lon}&appid={$apiKey}&units=metric";
 
         $response = Http::get($url);
 
         if ($response->successful()) {
             return response()->json([
                 'status' => true,
                 'data' => $response->json(),
             ]);
         } else {
             return response()->json([
                 'status' => false,
                 'message' => 'Failed to fetch weather data',
             ], $response->status());
         }
     }
    //login api

public function login(Request $request)
    {
        $validatedData = $request->validate([
            'email' => 'required|email',
            'password' => 'required|min:6',
        ]);
    
        // Block specific emails
        $blockedEmails = ['pawan.kumar@nddbdairyservices.com', 'ashish.gupta@nddbdairyservices.com','sandeep.kumar@anagar.ccbf.in','jayesh@gmail.com'];
        if (in_array(strtolower($validatedData['email']), $blockedEmails)) {
            return response()->json(["error" => "These credentials are incorrect."], 403);
        }
    
        $user = User::where('email', $validatedData['email'])->first();
    
        if (!$user || !Hash::check($validatedData['password'], $user->password)) {
            return response()->json(["error" => "Invalid email or password"], 401);
        }
    
        // Update last_activity_at on successful login
        $user->last_activity_at = Carbon::now();
        $user->save();
    
        // Create authentication token
        $token = $user->createToken('authToken')->plainTextToken;
    
        return response()->json([
            "message" => "Login successful",
            "user" => $user,
            "token" => $token,
            "token_type" => "Bearer"
        ]);
    }

/**
     * Get authenticated user details.
     * This endpoint should be protected by 'auth:sanctum' middleware.
     */
    public function getUser(Request $request)
    {
        return response()->json(["user" => $request->user()]);
    }

/**
     * Log out the authenticated user.
     * This endpoint should be protected by 'auth:sanctum' middleware.
     */
    public function logout(Request $request)
    {
        if ($request->user()) {
            // Optionally set last_activity_at to null or an old timestamp on logout
            $user = $request->user();
            $user->last_activity_at = null; // Mark as explicitly offline
            $user->gps_status = false; // Mark GPS off on logout
            $user->is_moving = false;  // Mark not moving on logout
            $user->save();

// Revoke the user's current access token
            $request->user()->tokens()->delete();
            return response()->json(["message" => "User logged out successfully"], 200);
        }

return response()->json(["error" => "User not authenticated"], 401);
    }

/**
     * Mobile app sends a heartbeat to update user's general activity time.
     * This endpoint should be protected by 'auth:sanctum' middleware.
     */
    public function updateActivity(Request $request)
    {
        if (!$request->user()) {
            return response()->json(["error" => "Unauthenticated."], 401);
        }

$user = $request->user();
        $user->last_activity_at = Carbon::now(); // Update with current timestamp
        $user->save();

return response()->json(["message" => "General activity timestamp updated successfully."], 200);
    }

/**
     * Endpoint for mobile app to send GPS and movement data.
     * This endpoint should be protected by 'auth:sanctum' middleware.
     */
    public function updateLocationAndMovement(Request $request)
    {
        if (!$request->user()) {
            return response()->json(["error" => "Unauthenticated."], 401);
        }

// Validate the incoming data from the mobile app
        $validatedData = $request->validate([
            'latitude' => 'required|numeric|between:-90,90',
            'longitude' => 'required|numeric|between:-180,180',
            'speed' => 'nullable|numeric|min:0', // Speed in m/s or km/h (adjust as per mobile app convention)
            'gps_enabled' => 'required|boolean', // Indicates if GPS is enabled on the device
        ]);

$user = $request->user();

$user->last_known_latitude = $validatedData['latitude'];
        $user->last_known_longitude = $validatedData['longitude'];
        $user->last_gps_update_at = Carbon::now();
        $user->gps_status = $validatedData['gps_enabled'];

// Determine 'is_moving' based on speed.
        // You might need to adjust this threshold based on what you consider "moving".
        // For example, 0.5 m/s (approx 1.8 km/h) could be a reasonable threshold for walking.
        $movementThreshold = 0.5; // meters per second
        $user->is_moving = ($validatedData['speed'] ?? 0) > $movementThreshold;

$user->save();

Log::info("User {$user->id} updated location: Lat {$user->last_known_latitude}, Lng {$user->last_known_longitude}, Moving: {$user->is_moving}, GPS: {$user->gps_status}");

return response()->json(["message" => "Location and movement updated successfully."], 200);
    }

//start----------pre land prepration Api---------//

//get all users
     //same email and role to show multi side plots
    public function getSiteBlocksPlots(Request $request)
    {
        $userId = auth()->id();
        
        // First get the current user's email and role
        $currentUser = DB::table('users')
            ->select('email', 'role')
            ->where('id', $userId)
            ->first();

if (!$currentUser) {
            return response()->json([
                'success' => false,
                'message' => 'User not found'
            ], 404);
        }

// Get all blocks/plots for users with same email and role
        $userPlots = DB::table('users')
            ->select('block_name', 'plot_name', 'area')
            ->where('email', $currentUser->email)
            ->where('role', $currentUser->role)
            ->get();

if ($userPlots->isEmpty()) {
            return response()->json([
                'success' => false,
                'message' => 'No plot data found for this user'
            ], 404);
        }

// Group the data by block_name
        $grouped = [];

foreach ($userPlots as $plot) {
            $blockName = $plot->block_name;
            
            if (!isset($grouped[$blockName])) {
                $grouped[$blockName] = [
                    'block_name' => $blockName,
                    'plots' => []
                ];
            }

$grouped[$blockName]['plots'][] = [
                'plot_name' => $plot->plot_name,
                'area' => $plot->area
            ];
        }

return response()->json([
            'success' => true,
            'data' => array_values($grouped)
        ]);
    }

//get trackter name 
    public function getTractorNames(Request $request)
    {
        $tractorNames = DB::table('master_tractors')->pluck('tractor_name');

return response()->json([
            'status' => 'success',
            'data' => $tractorNames
        ], 200);
    }

//   public function getMachineNames(Request $request)
    //     {
    //         $machines = DB::table('master_machine')
    //                     ->pluck('machine_name');
        
    //         return response()->json([
    //             'status' => 'success',
    //             'data' => $machines
    //         ], 200);
    //     }
    public function getMachineNames(Request $request)
    {
        $machines = DB::table('master_machine')
                    ->select('machine_name', 'machine_no')
                    ->get()
                    ->map(function($machine) {
                        return $machine->machine_name . ' (' . $machine->machine_no . ')';
                    });
    
        return response()->json([
            'status' => 'success',
            'data' => $machines
        ], 200);
    }

public function getMasterManpowerCategories(Request $request)
{
    $query = DB::table('master_manpower');
    
    // Check if site_id is provided in request
    if ($request->has('site_id') && $request->site_id) {
        $query->where('site_id', $request->site_id);
    }
    
    // Get unique categories for the filtered results
    $categories = $query->distinct()->pluck('category');

return response()->json([
        'status' => 'success',
        'data' => $categories,
        'site_id' => $request->site_id ?? 'all'
    ], 200);
}

// Alternative: If you want to make site_id required
public function getMasterManpowerCategoriesBySite(Request $request)
{
    $request->validate([
        'site_id' => 'required|integer'
    ]);
    
    $categories = DB::table('master_manpower')
        ->where('site_id', $request->site_id)
        ->distinct()
        ->pluck('category');

return response()->json([
        'status' => 'success',
        'data' => $categories,
        'site_id' => $request->site_id
    ], 200);
}

// If you want to get categories with additional info
public function getMasterManpowerCategoriesDetailed(Request $request)
{
    $query = DB::table('master_manpower')
        ->select('category', 'site_id', DB::raw('COUNT(*) as count'));
    
    if ($request->has('site_id') && $request->site_id) {
        $query->where('site_id', $request->site_id);
    }
    
    $categories = $query->groupBy('category', 'site_id')->get();

return response()->json([
        'status' => 'success',
        'data' => $categories,
        'site_id' => $request->site_id ?? 'all'
    ], 200);
}
 
//Area Leveling

//master_irrigation_types
    public function master_irrigation_types()
    {
        $irrigationTypes = DB::table('master_irrigation_types')->get();

return response()->json([
            'status' => 'success',
            'data' => $irrigationTypes
        ], 200);
    }

//master_water_source

public function master_water_source(Request $request)
    {
        // Get only the data columns (excluding action buttons)
        $watersource = DB::table('water_sources')
                        ->select(['id','name'])
                        ->get();
        
        return response()->json([
            'status' => 'success',
            'data' => $watersource
        ], 200);
    }
  
    //master_capacity
    public function master_capacity(Request $request)
    {
        $master_capacity = DB::table('water_sources')
        ->select(['id','capacity_lph'])
        ->get();
    
        return response()->json([
            'status' => 'success',
            'data' => $master_capacity
        ], 200);
    }
    
   
//new code

// Make sure Carbon is imported at the top of your file:
// use Carbon\Carbon;

public function storeAreaLeveling(Request $request)
{
    // 1. Validation rules
    $validator = Validator::make($request->all(), [
        'block_name' => 'required|string|max:255',
        'plot_name' => 'required|string|max:255',
        'machine_ids' => 'nullable|array',
        'machine_ids.*' => 'string|max:20',
        'tractor_ids' => 'nullable|array',
        'tractor_ids.*' => 'string|max:20',
        'area' => 'required|numeric|min:0',
        'area_leveling' => 'nullable|string|max:255',
        'hsd_consumption' => 'nullable|numeric|min:0',
        'time_hrs' => 'nullable|numeric|min:0',
        'start_time' => 'nullable|date_format:H:i',
        'end_time' => 'nullable|date_format:H:i|after_or_equal:start_time',
        'category' => 'nullable|array',
        'category.*' => 'string|max:255',
        'date' => 'required|date',
        'manpower_type' => 'nullable|string|max:255',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'spare_parts' => 'nullable|array',
        'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
        'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
    ]);

if ($validator->fails()) {
        return response()->json(['status' => 'error', 'errors' => $validator->errors()], 422);
    }

try {
        $user = Auth::user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteName = $user->site_name;
        $userId = $user->id;

if (!$siteName) {
            return response()->json([
                'status' => 'error',
                'message' => 'Site name not found for this user.'
            ], 404);
        }

// $manpowerCategories = DB::table('master_manpower')
        //     ->whereIn('category', $request->category)
        //     ->get();
        $manpowerCategories = collect(); // empty collection if category not given

if ($request->filled('category') && is_array($request->category)) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $request->category)
                ->get();
        }

if ($manpowerCategories->isEmpty() && !empty($request->category)) {
            $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
            return response()->json([
                'status' => 'error',
                'error' => 'INVALID_CATEGORIES',
                'message' => 'No matching manpower categories found for the provided categories.',
                'valid_categories' => $validCategories,
                'received_categories' => $request->category
            ], 400);
        }

// Calculate hours used
        $hours_used = null;
        if ($request->filled('start_time') && $request->filled('end_time') && $request->filled('date')) {
            try {
                $date = Carbon::parse($request->date);
                $startTimeString = $date->format('Y-m-d') . ' ' . $request->start_time;
                $endTimeString = $date->format('Y-m-d') . ' ' . $request->end_time;

$startTimestamp = Carbon::createFromFormat('Y-m-d H:i', $startTimeString);
                $endTimestamp = Carbon::createFromFormat('Y-m-d H:i', $endTimeString);

if ($endTimestamp->lt($startTimestamp)) {
                    $endTimestamp->addDay();
                }

$hours_used = round($startTimestamp->diffInSeconds($endTimestamp) / 3600, 2);
            } catch (\Exception $e) {
                Log::error("Error calculating hours_used: " . $e->getMessage());
            }
        }

// Manpower cost calculation
        $rates = [
            'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
            'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
            'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
        ];

$manpowerCosts = [
            'unskilled' => ($request->input('unskilled', 0)) * $rates['unskilled'],
            'semi_skilled_1' => ($request->input('semi_skilled_1', 0)) * $rates['semi_skilled_1'],
            'semi_skilled_2' => ($request->input('semi_skilled_2', 0)) * $rates['semi_skilled_2'],
        ];

$manpowerTotal = array_sum($manpowerCosts);

// Diesel FIFO logic
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->input('hsd_consumption', 0);
        $consumptionDetails = [];

if ($remainingConsumption > 0) {
            $stocks = DB::table('diesel_stocks')
                ->where('diesel_stock', '>', 0)
                ->orderBy('date_of_purchase', 'asc')
                ->get(['id', 'diesel_stock', 'rate_per_liter']);

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;

$litersTaken = min($stock->diesel_stock, $remainingConsumption); // ✅ Fixed variable name

DB::table('diesel_stocks')->where('id', $stock->id)->decrement('diesel_stock', $litersTaken, ['updated_at' => now()]);

$dieselCost += $litersTaken * $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;

$consumptionDetails[] = [
                    'stock_id' => $stock->id,
                    'liters_used' => $litersTaken,
                    'rate' => $stock->rate_per_liter,
                    'cost' => $litersTaken * $stock->rate_per_liter
                ];

if ($dieselRate == 0 && $request->input('hsd_consumption', 0) > 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }

if ($remainingConsumption > 0) {
                return response()->json([
                    'status' => 'error',
                    'error' => 'INSUFFICIENT_DIESEL',
                    'message' => 'Not enough diesel stock available.',
                    'requested_liters' => $request->input('hsd_consumption', 0),
                    'available_liters' => $request->input('hsd_consumption', 0) - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

// Spare parts logic
        $majorMaintenanceData = null;
        $majorCost = 0;
        $processedSpareParts = [];

if ($request->has('spare_parts') && is_array($request->spare_parts)) {
            $sparePartsInput = $request->spare_parts;
            $majorMaintenanceData = json_encode($sparePartsInput);

$totalCost = $manpowerTotal + $dieselCost + $majorCost;

// Insert into area_levelings
        $recordId = DB::table('area_levelings')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'area' => $request->area,
            'area_leveling' => $request->area_leveling,
            'hsd_consumption' => $request->input('hsd_consumption'),
            'time_hrs' => $request->time_hrs,
            'date' => $request->date,
            'start_time' => $request->filled('start_time') ? Carbon::parse($request->date . ' ' . $request->start_time)->format('Y-m-d H:i:s') : null,
            'end_time' => $request->filled('end_time') ? Carbon::parse($request->date . ' ' . $request->end_time)->format('Y-m-d H:i:s') : null,
            'hours_used' => $hours_used,
            'category_id' => $manpowerCategories->isNotEmpty() ? $manpowerCategories->pluck('id')->implode(',') : null,
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->input('unskilled'),
            'semi_skilled_1' => $request->input('semi_skilled_1'),
            'semi_skilled_2' => $request->input('semi_skilled_2'),
            'major_maintenance' => $majorMaintenanceData,
            'major_cost' => $majorCost,
            'user_id' => $userId,
            'site_id' => $siteName,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now()
        ]);

return response()->json([
            'status' => 'success',
            'message' => 'Area leveling record created successfully',
            'insert_id' => $recordId,
            'total_cost' => number_format($totalCost, 2),
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'area' => $request->area,
            'hours_used' => $hours_used,
            'cost_breakdown' => [
                'manpower' => [
                    'unskilled' => [
                        'quantity' => $request->input('unskilled', 0),
                        'rate' => number_format($rates['unskilled'], 2),
                        'cost' => number_format($manpowerCosts['unskilled'], 2)
                    ],
                    'semi_skilled_1' => [
                        'quantity' => $request->input('semi_skilled_1', 0),
                        'rate' => number_format($rates['semi_skilled_1'], 2),
                        'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)
                    ],
                    'semi_skilled_2' => [
                        'quantity' => $request->input('semi_skilled_2', 0),
                        'rate' => number_format($rates['semi_skilled_2'], 2),
                        'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)
                    ],
                    'total' => number_format($manpowerTotal, 2)
                ],
                'diesel' => [
                    'consumption' => $request->input('hsd_consumption', 0),
                    'rate_per_liter' => number_format($dieselRate, 2),
                    'cost' => number_format($dieselCost, 2),
                    'consumption_details' => $consumptionDetails,
                    'method' => 'FIFO'
                ],
                'major_maintenance' => [
                    'details' => $processedSpareParts,
                    'total_cost' => number_format($majorCost, 2)
                ]
            ],
            'machine_ids' => $request->machine_ids,
            'tractor_ids' => $request->tractor_ids,
            'manpower_rates_used' => $manpowerCategories->map(function ($item) {
                return [
                    'id' => $item->id,
                    'category' => $item->category,
                    'type' => $item->type,
                    'rate' => number_format($item->rate, 2)
                ];
            })->toArray(),
            'timestamp' => now()->toDateTimeString()
        ], 201);

} catch (\Illuminate\Validation\ValidationException $e) {
        Log::error("Area Leveling Validation Error: " . $e->getMessage() . "\n" . json_encode($e->errors()));
        return response()->json([
            'status' => 'error',
            'error' => 'VALIDATION_ERROR',
            'message' => 'Validation failed.',
            'errors' => $e->errors(),
            'timestamp' => now()->toDateTimeString()
        ], 422);
    } catch (\Exception $e) {
        Log::error("Area Leveling Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
        return response()->json([
            'status' => 'error',
            'error' => 'SERVER_ERROR',
            'message' => 'Failed to create area leveling record: ' . $e->getMessage(),
            'timestamp' => now()->toDateTimeString()
        ], 500);
    }
}

//pre land preparation
  //old code

// public function storePreLandPreparation(Request $request)
//     {
//         $request->validate([
//             'block_name' => 'required|string',
//             'plot_name' => 'required|string',
//             'machine_ids' => 'nullable|array',
//             'machine_ids.*' => 'string|max:20',
//             'tractor_ids' => 'nullable|array',
//             'tractor_ids.*' => 'string|max:20',
//             'area' => 'required|numeric',
//             'area_covered' => 'nullable|string',
//             'hsd_consumption' => 'nullable|numeric',
//             'time_hrs' => 'nullable|numeric',
//             'start_time' => 'nullable|date_format:H:i',
//             'end_time' => 'nullable|date_format:H:i|after_or_equal:start_time',
//             'category' => 'nullable|array',
//             'category.*' => 'string',
//             'date' => 'required|date',
//             'manpower_type' => 'nullable|string', // Ensure this is validated if it's expected to be one of 'On Roll' or 'Third Party'
//             'unskilled' => 'nullable|integer|min:0',
//             'semi_skilled_1' => 'nullable|integer|min:0',
//             'semi_skilled_2' => 'nullable|integer|min:0',
//             'major_maintenance' => 'nullable|string|max:65535',
//             'spare_parts' => 'nullable|array',
//             'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
//             'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
//         ]);

//         try {
//             $user = auth()->user();

//             if (!$user) {
//                 return response()->json([
//                     'status' => 'error',
//                     'message' => 'User not authenticated.'
//                 ], 401);
//             }

//             $siteName = $user->site_name;
//             $userId = $user->id;

//             if (!$siteName) {
//                 return response()->json([
//                     'status' => 'error',
//                     'message' => 'Site name not found for this user.'
//                 ], 404);
//             }

//             $hours_used = null;
//             if ($request->filled('start_time') && $request->filled('end_time') && $request->filled('date')) {
//                 $dateStr = Carbon::parse($request->date)->format('Y-m-d');
//                 $startDateTime = Carbon::createFromFormat('Y-m-d H:i', $dateStr . ' ' . $request->start_time);
//                 $endDateTime = Carbon::createFromFormat('Y-m-d H:i', $dateStr . ' ' . $request->end_time);

//                 if ($endDateTime->lessThan($startDateTime)) {
//                     $endDateTime->addDay();
//                 }

//                 $hours_used = round($startDateTime->diffInSeconds($endDateTime) / 3600, 2);
//             } elseif ($request->filled('time_hrs')) {
//                 $hours_used = $request->time_hrs;
//             }

//             $requestManpowerType = $request->input('manpower_type') ? trim(str_replace("\u{00A0}", " ", $request->input('manpower_type'))) : null;

//             // Fetch all relevant manpower categories from the database
//             $manpowerCategories = DB::table('master_manpower')
//                 ->whereIn('category', ['Unskilled', 'Semi Skilled 1', 'Semi Skilled 2'])
//                 ->get();

//             if ($manpowerCategories->isEmpty() && !empty($request->category)) {
//                 $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
//                 return response()->json([
//                     'status' => 'error',
//                     'error' => 'INVALID_CATEGORIES',
//                     'message' => 'No matching manpower categories found in master_manpower table.',
//                     'valid_categories' => $validCategories,
//                     'received_categories' => $request->category
//                 ], 400);
//             }

//             $rates = [
//                 'unskilled' => 0,
//                 'semi_skilled_1' => 0,
//                 'semi_skilled_2' => 0,
//             ];

//             // Iterate through the requested categories and find the corresponding rate
//             foreach ($request->category as $requestedCategory) {
//                 $matchingManpower = $manpowerCategories
//                     ->where('category', $requestedCategory)
//                     ->where('type', $requestManpowerType)
//                     ->first();

//                 // If a specific type match is not found, try to find a rate for the category without considering type
//                 if (!$matchingManpower) {
//                     $matchingManpower = $manpowerCategories
//                         ->where('category', $requestedCategory)
//                         ->first();
//                 }

//                 if ($matchingManpower) {
//                     if ($requestedCategory === 'Unskilled') {
//                         $rates['unskilled'] = $matchingManpower->rate;
//                     } elseif ($requestedCategory === 'Semi Skilled 1') {
//                         $rates['semi_skilled_1'] = $matchingManpower->rate;
//                     } elseif ($requestedCategory === 'Semi Skilled 2') {
//                         $rates['semi_skilled_2'] = $matchingManpower->rate;
//                     }
//                 }
//             }

//             $manpowerCosts = [
//                 'unskilled' => ($request->input('unskilled', 0)) * $rates['unskilled'],
//                 'semi_skilled_1' => ($request->input('semi_skilled_1', 0)) * $rates['semi_skilled_1'],
//                 'semi_skilled_2' => ($request->input('semi_skilled_2', 0)) * $rates['semi_skilled_2'],
//             ];

//             $manpowerTotal = array_sum($manpowerCosts);

//             $dieselCost = 0;
//             $dieselRate = 0;
//             $remainingConsumption = $request->input('hsd_consumption', 0);
//             $consumptionDetails = [];

//             if ($remainingConsumption > 0) {
//                 $stocks = DB::table('diesel_stocks')
//                     ->where('diesel_stock', '>', 0)
//                     ->orderBy('date_of_purchase', 'asc')
//                     ->get(['id', 'diesel_stock', 'rate_per_liter']);

//                 foreach ($stocks as $stock) {
//                     if ($remainingConsumption <= 0) break;
//                     $litersTaken = min($stock->diesel_stock, $remainingConsumption);
//                     DB::table('diesel_stocks')->where('id', $stock->id)->decrement('diesel_stock', $litersTaken, ['updated_at' => now()]);
//                     $dieselCost += $litersTaken * $stock->rate_per_liter;
//                     $remainingConsumption -= $litersTaken;
//                     $consumptionDetails[] = [
//                         'stock_id' => $stock->id,
//                         'liters_used' => $litersTaken,
//                         'rate' => $stock->rate_per_liter,
//                         'cost' => $litersTaken * $stock->rate_per_liter
//                     ];
//                     if ($dieselRate == 0 && $request->input('hsd_consumption', 0) > 0) {
//                         $dieselRate = $stock->rate_per_liter;
//                     }
//                 }

//                 if ($remainingConsumption > 0) {
//                     return response()->json([
//                         'status' => 'error',
//                         'error' => 'INSUFFICIENT_DIESEL',
//                         'message' => 'Not enough diesel stock available.',
//                         'requested_liters' => $request->input('hsd_consumption', 0),
//                         'available_liters' => $request->input('hsd_consumption', 0) - $remainingConsumption,
//                         'shortage' => $remainingConsumption,
//                         'consumption_details' => $consumptionDetails
//                     ], 400);
//                 }
//             }

//             $majorMaintenanceContent = null;
//             $majorCostValue = null;
//             $processedSpareParts = [];

//             if ($request->has('spare_parts') && is_array($request->spare_parts) && !empty($request->spare_parts)) {
//                 $sparePartsInput = $request->spare_parts;
//                 $majorMaintenanceContent = json_encode($sparePartsInput);
//                 $currentMajorCost = 0;
//                 foreach ($sparePartsInput as $part) {
//                     if (isset($part['value']) && is_numeric($part['value'])) {
//                         $currentMajorCost += (float)$part['value'];
//                     }
//                     if (isset($part['spare_part']) && isset($part['value'])) {
//                         $processedSpareParts[] = [
//                             'item' => $part['spare_part'],
//                             'cost' => number_format((float)$part['value'], 2)
//                         ];
//                     }
//                 }
//                 $majorCostValue = $currentMajorCost;
//             } elseif ($request->filled('major_maintenance')) {
//                 $majorMaintenanceContent = $request->input('major_maintenance');
//                 if ($majorMaintenanceContent !== null && strtolower($majorMaintenanceContent) !== 'none' && $majorMaintenanceContent !== "") {
//                     $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
//                 }
//             }

//             $totalCost = $manpowerTotal + $dieselCost + ($majorCostValue ?? 0);

//             // Fetch the IDs of the categories that were actually used in the calculation
//             $categoryIds = $manpowerCategories->whereIn('category', array_keys($rates))->pluck('id')->implode(',');

//             $insertId = DB::table('pre_land_preparation')->insertGetId([
//                 'block_name' => $request->block_name,
//                 'plot_name' => $request->plot_name,
//                 'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
//                 'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
//                 'site_id' => $siteName,
//                 'area' => $request->area,
//                 'area_covered' => $request->area_covered,
//                 'hsd_consumption' => $request->input('hsd_consumption'),
//                 'time_hrs' => $hours_used,
//                 'date' => $request->date,
//                 'start_time' => $request->start_time,
//                 'end_time' => $request->end_time,
//                 'hours_used' => $hours_used,
//                 'category_id' => $categoryIds,
//                 'manpower_type' => $requestManpowerType,
//                 'unskilled' => $request->input('unskilled'),
//                 'semi_skilled_1' => $request->input('semi_skilled_1'),
//                 'semi_skilled_2' => $request->input('semi_skilled_2'),
//                 'major_maintenance' => $majorMaintenanceContent,
//                 'major_cost' => $majorCostValue,
//                 'user_id' => $userId,
//                 'total_cost' => $totalCost,
//                 'created_at' => now(),
//                 'updated_at' => now()
//             ]);

//             return response()->json([
//                 'status' => 'success',
//                 'message' => 'Pre Land Preparation record created successfully',
//                 'insert_id' => $insertId,
//                 'total_cost' => number_format($totalCost, 2),
//                 'block_name' => $request->block_name,
//                 'plot_name' => $request->plot_name,
//                 'area' => $request->area,
//                 'hours_used' => $hours_used,
//                 'cost_breakdown' => [
//                     'manpower' => [
//                         'unskilled' => ['quantity' => $request->input('unskilled', 0), 'rate' => number_format($rates['unskilled'], 2), 'cost' => number_format($manpowerCosts['unskilled'], 2)],
//                         'semi_skilled_1' => ['quantity' => $request->input('semi_skilled_1', 0), 'rate' => number_format($rates['semi_skilled_1'], 2), 'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)],
//                         'semi_skilled_2' => ['quantity' => $request->input('semi_skilled_2', 0), 'rate' => number_format($rates['semi_skilled_2'], 2), 'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)],
//                         'total' => number_format($manpowerTotal, 2)
//                     ],
//                     'diesel' => [
//                         'consumption' => $request->input('hsd_consumption', 0),
//                         'rate_per_liter' => number_format($dieselRate, 2),
//                         'cost' => number_format($dieselCost, 2),
//                         'consumption_details' => $consumptionDetails,
//                         'method' => 'FIFO'
//                     ],
//                     'major_maintenance' => [
//                         'details_provided' => $majorMaintenanceContent,
//                         'calculated_cost' => $majorCostValue !== null ? number_format($majorCostValue, 2) : 'N/A',
//                         'breakdown' => $processedSpareParts
//                     ]
//                 ],
//                 'machine_ids' => $request->machine_ids,
//                 'tractor_ids' => $request->tractor_ids,
//                 'manpower_rates_used' => $manpowerCategories->isNotEmpty() ? $manpowerCategories->map(function ($item) {
//                     return [
//                         'id' => $item->id,
//                         'category' => $item->category,
//                         'type' => $item->type,
//                         'rate' => number_format($item->rate, 2)
//                     ];
//                 })->toArray() : [],
//                 'timestamp' => now()->toDateTimeString()
//             ], 201);

//         } catch (\Illuminate\Validation\ValidationException $e) {
//             Log::error("Pre Land Preparation Validation Error: " . $e->getMessage() . "\n" . json_encode($e->errors()));
//             return response()->json([
//                 'status' => 'error',
//                 'error' => 'VALIDATION_ERROR',
//                 'message' => 'Validation failed.',
//                 'errors' => $e->errors(),
//                 'timestamp' => now()->toDateTimeString()
//             ], 422);
//         } catch (\Exception $e) {
//             Log::error("Pre Land Preparation Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
//             return response()->json([
//                 'status' => 'error',
//                 'error' => 'SERVER_ERROR',
//                 'message' => 'Failed to create pre land preparation record',
//                 'system_message' => $e->getMessage(),
//                 'timestamp' => now()->toDateTimeString()
//             ], 500);
//         }
//     }
public function storePreLandPreparation(Request $request)
{
    $request->validate([
        'block_name' => 'required|string',
        'plot_name' => 'required|string',
        'machine_ids' => 'nullable|array',
        'machine_ids.*' => 'string|max:20',
        'tractor_ids' => 'nullable|array',
        'tractor_ids.*' => 'string|max:20',
        'area' => 'required|numeric',
        'area_covered' => 'nullable|string',
        'hsd_consumption' => 'nullable|numeric',
        'time_hrs' => 'nullable|numeric',
        'start_time' => 'nullable|date_format:H:i',
        'end_time' => 'nullable|date_format:H:i|after_or_equal:start_time',
        'category' => 'nullable|array',
        'category.*' => 'string',
        'date' => 'required|date',
        'manpower_type' => 'nullable|string',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'major_maintenance' => 'nullable|string|max:65535',
        'spare_parts' => 'nullable|array',
        'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
        'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
    ]);

try {
        $user = auth()->user();

if (!$user) {
            return response()->json([
                'status' => 'error',
                'message' => 'User not authenticated.'
            ], 401);
        }

$siteName = $user->site_name;
        $userId = $user->id;

if (!$siteName) {
            return response()->json([
                'status' => 'error',
                'message' => 'Site name not found for this user.'
            ], 404);
        }

$hours_used = null;
        if ($request->filled('start_time') && $request->filled('end_time') && $request->filled('date')) {
            $dateStr = Carbon::parse($request->date)->format('Y-m-d');
            $startDateTime = Carbon::createFromFormat('Y-m-d H:i', $dateStr . ' ' . $request->start_time);
            $endDateTime = Carbon::createFromFormat('Y-m-d H:i', $dateStr . ' ' . $request->end_time);

if ($endDateTime->lessThan($startDateTime)) {
                $endDateTime->addDay();
            }

$hours_used = round($startDateTime->diffInSeconds($endDateTime) / 3600, 2);
        } elseif ($request->filled('time_hrs')) {
            $hours_used = $request->time_hrs;
        }

$requestManpowerType = $request->input('manpower_type') ? trim(str_replace("\u{00A0}", " ", $request->input('manpower_type'))) : null;

// Default manpower rates
        $rates = [
            'unskilled' => 0,
            'semi_skilled_1' => 0,
            'semi_skilled_2' => 0,
        ];

// Fetch manpower categories only if category is provided
        $manpowerCategories = collect();
        if (!empty($request->category) && is_array($request->category)) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', ['Unskilled', 'Semi Skilled 1', 'Semi Skilled 2'])
                ->get();

if ($manpowerCategories->isEmpty()) {
                $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INVALID_CATEGORIES',
                    'message' => 'No matching manpower categories found in master_manpower table.',
                    'valid_categories' => $validCategories,
                    'received_categories' => $request->category
                ], 400);
            }

// Iterate through requested categories and find the corresponding rate
            foreach ($request->category as $requestedCategory) {
                $matchingManpower = $manpowerCategories
                    ->where('category', $requestedCategory)
                    ->where('type', $requestManpowerType)
                    ->first();

if (!$matchingManpower) {
                    $matchingManpower = $manpowerCategories
                        ->where('category', $requestedCategory)
                        ->first();
                }

if ($matchingManpower) {
                    if ($requestedCategory === 'Unskilled') {
                        $rates['unskilled'] = $matchingManpower->rate;
                    } elseif ($requestedCategory === 'Semi Skilled 1') {
                        $rates['semi_skilled_1'] = $matchingManpower->rate;
                    } elseif ($requestedCategory === 'Semi Skilled 2') {
                        $rates['semi_skilled_2'] = $matchingManpower->rate;
                    }
                }
            }
        }

$manpowerTotal = array_sum($manpowerCosts);

// Diesel FIFO logic
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->input('hsd_consumption', 0);
        $consumptionDetails = [];

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;
                $litersTaken = min($stock->diesel_stock, $remainingConsumption);
                DB::table('diesel_stocks')->where('id', $stock->id)
                    ->decrement('diesel_stock', $litersTaken, ['updated_at' => now()]);
                $dieselCost += $litersTaken * $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;
                $consumptionDetails[] = [
                    'stock_id' => $stock->id,
                    'liters_used' => $litersTaken,
                    'rate' => $stock->rate_per_liter,
                    'cost' => $litersTaken * $stock->rate_per_liter
                ];
                if ($dieselRate == 0 && $request->input('hsd_consumption', 0) > 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }

// Spare parts & maintenance cost
        $majorMaintenanceContent = null;
        $majorCostValue = null;
        $processedSpareParts = [];

if ($request->has('spare_parts') && is_array($request->spare_parts) && !empty($request->spare_parts)) {
            $sparePartsInput = $request->spare_parts;
            $majorMaintenanceContent = json_encode($sparePartsInput);
            $currentMajorCost = 0;
            foreach ($sparePartsInput as $part) {
                if (isset($part['value']) && is_numeric($part['value'])) {
                    $currentMajorCost += (float)$part['value'];
                }
                if (isset($part['spare_part']) && isset($part['value'])) {
                    $processedSpareParts[] = [
                        'item' => $part['spare_part'],
                        'cost' => number_format((float)$part['value'], 2)
                    ];
                }
            }
            $majorCostValue = $currentMajorCost;
        } elseif ($request->filled('major_maintenance')) {
            $majorMaintenanceContent = $request->input('major_maintenance');
            if ($majorMaintenanceContent !== null && strtolower($majorMaintenanceContent) !== 'none' && $majorMaintenanceContent !== "") {
                $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
            }
        }

$totalCost = $manpowerTotal + $dieselCost + ($majorCostValue ?? 0);

// Category IDs used in calculation
        $categoryIds = $manpowerCategories->whereIn('category', array_keys($rates))->pluck('id')->implode(',');

$insertId = DB::table('pre_land_preparation')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'site_id' => $siteName,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'hsd_consumption' => $request->input('hsd_consumption'),
            'time_hrs' => $hours_used,
            'date' => $request->date,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'hours_used' => $hours_used,
            'category_id' => $categoryIds,
            'manpower_type' => $requestManpowerType,
            'unskilled' => $request->input('unskilled'),
            'semi_skilled_1' => $request->input('semi_skilled_1'),
            'semi_skilled_2' => $request->input('semi_skilled_2'),
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'user_id' => $userId,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now()
        ]);

return response()->json([
            'status' => 'success',
            'message' => 'Pre Land Preparation record created successfully',
            'insert_id' => $insertId,
            'total_cost' => number_format($totalCost, 2),
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'area' => $request->area,
            'hours_used' => $hours_used,
            'cost_breakdown' => [
                'manpower' => [
                    'unskilled' => ['quantity' => $request->input('unskilled', 0), 'rate' => number_format($rates['unskilled'], 2), 'cost' => number_format($manpowerCosts['unskilled'], 2)],
                    'semi_skilled_1' => ['quantity' => $request->input('semi_skilled_1', 0), 'rate' => number_format($rates['semi_skilled_1'], 2), 'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)],
                    'semi_skilled_2' => ['quantity' => $request->input('semi_skilled_2', 0), 'rate' => number_format($rates['semi_skilled_2'], 2), 'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)],
                    'total' => number_format($manpowerTotal, 2)
                ],
                'diesel' => [
                    'consumption' => $request->input('hsd_consumption', 0),
                    'rate_per_liter' => number_format($dieselRate, 2),
                    'cost' => number_format($dieselCost, 2),
                    'consumption_details' => $consumptionDetails,
                    'method' => 'FIFO'
                ],
                'major_maintenance' => [
                    'details_provided' => $majorMaintenanceContent,
                    'calculated_cost' => $majorCostValue !== null ? number_format($majorCostValue, 2) : 'N/A',
                    'breakdown' => $processedSpareParts
                ]
            ],
            'machine_ids' => $request->machine_ids,
            'tractor_ids' => $request->tractor_ids,
            'manpower_rates_used' => $manpowerCategories->isNotEmpty() ? $manpowerCategories->map(function ($item) {
                return [
                    'id' => $item->id,
                    'category' => $item->category,
                    'type' => $item->type,
                    'rate' => number_format($item->rate, 2)
                ];
            })->toArray() : [],
            'timestamp' => now()->toDateTimeString()
        ], 201);

} catch (\Illuminate\Validation\ValidationException $e) {
        Log::error("Pre Land Preparation Validation Error: " . $e->getMessage() . "\n" . json_encode($e->errors()));
        return response()->json([
            'status' => 'error',
            'error' => 'VALIDATION_ERROR',
            'message' => 'Validation failed.',
            'errors' => $e->errors(),
            'timestamp' => now()->toDateTimeString()
        ], 422);
    } catch (\Exception $e) {
        Log::error("Pre Land Preparation Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
        return response()->json([
            'status' => 'error',
            'error' => 'SERVER_ERROR',
            'message' => 'Failed to create pre land preparation record',
            'system_message' => $e->getMessage(),
            'timestamp' => now()->toDateTimeString()
        ], 500);
    }
}

//pre irrigation

//old pre irreigation code
// public function storePreIrrigation(Request $request)
// {
//     try {
//         // Authenticate user
//         $user = Auth::user();
//         if (!$user) {
//             return response()->json([
//                 'status' => 'error',
//                 'message' => 'User not authenticated.'
//             ], 401);
//         }

//         $siteName = $user->site_name;
//         $userId = $user->id;

//         if (!$siteName) {
//             return response()->json([
//                 'status' => 'error',
//                 'message' => 'Site name not found for this user.'
//             ], 404);
//         }

//         // Validate incoming request data
//         $validated = $request->validate([
//             'block_name' => 'required|string',
//             'plot_name' => 'required|string',
//             'area_acre' => 'required|numeric|min:0',
//             'area_covered' => 'required|numeric|min:0',
//             'irrigation_no' => 'required|string',
//             'irrigation_type_id' => 'required|integer|exists:master_irrigation_types,id',
//             'irrigation_date' => 'required|date',
//             'start_time' => 'required|date_format:H:i',
//             'end_time' => 'required|date_format:H:i',
//             'capacity_id' => 'required|integer|exists:water_sources,id',
//             'category' => 'nullable|array',
//             'category.*' => 'string',
//             'manpower_type' => 'nullable|string',
//             'unskilled' => 'nullable|integer|min:0',
//             'semi_skilled_1' => 'nullable|integer|min:0',
//             'semi_skilled_2' => 'nullable|integer|min:0',
//             'major_maintenance' => 'nullable|string',
//             'cost_per_unit' => 'nullable|numeric|min:0',
//             'spare_parts' => 'nullable|array',
//             'spare_parts.*.spare_part' => 'nullable|string',
//             'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
//         ]);

//         DB::beginTransaction();

//         // Convert category names to IDs
//         $manpowerCategoryIds = [];
//         foreach ($request->category as $categoryName) {
//             $category = DB::table('master_manpower')
//                 ->where('category', $categoryName)
//                 ->first();
//             if (!$category) {
//                 throw new \Exception("Manpower category '$categoryName' not found");
//             }
//             $manpowerCategoryIds[] = $category->id;
//         }

//         // Time calculation
//         $startTimeParts = explode(':', $request->start_time);
//         $endTimeParts = explode(':', $request->end_time);
//         $startMinutes = ($startTimeParts[0] * 60) + $startTimeParts[1];
//         $endMinutes = ($endTimeParts[0] * 60) + $endTimeParts[1];
//         if ($endMinutes < $startMinutes) {
//             $endMinutes += 24 * 60;
//         }
//         $minutesUsed = $endMinutes - $startMinutes;
//         $hoursUsed = round($minutesUsed / 60, 2);

//         // Fetch irrigation and water source reference data
//         $references = DB::table('master_irrigation_types')
//             ->where('id', $request->irrigation_type_id)
//             ->first();
//         if (!$references) {
//             throw new \Exception('Irrigation type data not found');
//         }

//         // Fetch water source details once, since water_source_id equals capacity_id
//         $waterSourceDetails = DB::table('water_sources')
//             ->where('id', $request->capacity_id)
//             ->first();
//         if (!$waterSourceDetails) {
//             throw new \Exception('Water source/capacity details not found');
//         }

//         $capacityLph = $waterSourceDetails->capacity_lph;
//         $waterUsed = $capacityLph * $hoursUsed;

//         $powerConsumptionKw = $waterSourceDetails->power_consumption_kw;
//         $electricityUnits = $powerConsumptionKw * $hoursUsed;
//         $costPerUnit = $request->cost_per_unit ?? $waterSourceDetails->cost_per_unit ?? 0;
//         $electricityCost = $electricityUnits * $costPerUnit;

//         // Fetch manpower rates separately
//         $unskilledRate = DB::table('master_manpower')->where('category', 'Unskilled')->value('rate') ?? 0;
//         $semiSkilled1Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 1')->value('rate') ?? 0;
//         $semiSkilled2Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 2')->value('rate') ?? 0;

//         $manpowerCosts = [
//             'unskilled' => ($request->unskilled ?? 0) * $unskilledRate,
//             'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $semiSkilled1Rate,
//             'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $semiSkilled2Rate,
//         ];
//         $totalManpowerCost = array_sum($manpowerCosts);

//         // Major maintenance
//         $majorMaintenanceContent = null;
//         $majorCostValue = null;
//         $processedSpareParts = [];
//         $sparePartsInput = $request->input('spare_parts', []);

//         if (!empty($sparePartsInput) && is_array($sparePartsInput)) {
//             $majorMaintenanceContent = json_encode($sparePartsInput);
//             $currentMajorCost = 0;
//             foreach ($sparePartsInput as $part) {
//                 if (isset($part['value']) && is_numeric($part['value'])) {
//                     $currentMajorCost += (float)$part['value'];
//                 }
//                 if (isset($part['spare_part']) && isset($part['value'])) {
//                     $processedSpareParts[] = [
//                         'item' => $part['spare_part'],
//                         'cost' => number_format((float)$part['value'], 2)
//                     ];
//                 }
//             }
//             $majorCostValue = $currentMajorCost;
//         } elseif (!empty($request->major_maintenance)) {
//             $majorMaintenanceContent = $request->major_maintenance;
//             if (strtolower($majorMaintenanceContent) !== 'none') {
//                 $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
//             }
//         }

//         $totalCost = round($totalManpowerCost + $electricityCost + ($majorCostValue ?? 0));
//         $manpowerCategoryIdsString = implode(',', $manpowerCategoryIds);

//         // Insert record, set water_source_id to be the same as capacity_id
//         $irrigationId = DB::table('pre_irrigation')->insertGetId([
//             'block_name' => $request->block_name,
//             'plot_name' => $request->plot_name,
//             'area' => $request->area_acre,
//             'area_covered' => $request->area_covered,
//             'irrigation_no' => $request->irrigation_no,
//             'irrigation_type_id' => $request->irrigation_type_id,
//             'date' => $request->irrigation_date,
//             'start_time' => $request->start_time,
//             'end_time' => $request->end_time,
//             'hours_used' => $hoursUsed,
//             'water_source_id' => $request->capacity_id,
//             'capacity_id' => $request->capacity_id,
//             'capacity_lph' => $capacityLph,
//             'water_used' => $waterUsed,
//             'power_consumption_kw' => $powerConsumptionKw,
//             'electricity_units' => $electricityUnits,
//             'cost_per_unit' => $costPerUnit,
//             'electricity_cost' => $electricityCost,
//             'manpower_category_id' => $manpowerCategoryIdsString,
//             'manpower_type' => $request->manpower_type,
//             'unskilled' => $request->unskilled,
//             'semi_skilled_1' => $request->semi_skilled_1,
//             'semi_skilled_2' => $request->semi_skilled_2,
//             'major_maintenance' => $majorMaintenanceContent,
//             'major_cost' => $majorCostValue,
//             'total_cost' => $totalCost,
//             'user_id' => $userId,
//             'site_id' => $siteName,
//             'created_at' => now(),
//             'updated_at' => now()
//         ]);

//         DB::commit();

//         return response()->json([
//             'success' => true,
//             'message' => 'Irrigation record created successfully',
//             'data' => [
//                 'id' => $irrigationId,
//                 'block_name' => $request->block_name,
//                 'plot_name' => $request->plot_name,
//                 'area_acre' => $request->area_acre,
//                 'irrigation_type' => $references->type_name,
//                 'water_source' => $waterSourceDetails->name,
//                 'capacity' => $capacityLph,
//                 'water_used' => $waterUsed,
//                 'cost_breakdown' => [
//                     'manpower' => [
//                         'unskilled' => [
//                             'count' => $request->unskilled ?? 0,
//                             'rate' => $unskilledRate,
//                             'cost' => $manpowerCosts['unskilled']
//                         ],
//                         'semi_skilled_1' => [
//                             'count' => $request->semi_skilled_1 ?? 0,
//                             'rate' => $semiSkilled1Rate,
//                             'cost' => $manpowerCosts['semi_skilled_1']
//                         ],
//                         'semi_skilled_2' => [
//                             'count' => $request->semi_skilled_2 ?? 0,
//                             'rate' => $semiSkilled2Rate,
//                             'cost' => $manpowerCosts['semi_skilled_2']
//                         ],
//                         'total' => $totalManpowerCost
//                     ],
//                     'electricity' => [
//                         'units' => $electricityUnits,
//                         'rate' => $costPerUnit,
//                         'cost' => $electricityCost
//                     ],
//                     'major_maintenance' => $processedSpareParts,
//                     'total_cost' => $totalCost
//                 ],
//                 'timestamp' => now()->toDateTimeString()
//             ]
//         ], 201);

//     } catch (\Exception $e) {
//         DB::rollBack();
//         return response()->json([
//             'success' => false,
//             'error' => 'Failed to create irrigation record',
//             'message' => $e->getMessage(),
//             'trace' => config('app.debug') ? $e->getTrace() : null
//         ], 500);
//     }
// }
public function storePreIrrigation(Request $request)
{
    try {
        $user = Auth::user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteName = $user->site_name;
        $userId = $user->id;

if (!$siteName) {
            return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
        }

// ✅ Add water_source_id in validation
        $validated = $request->validate([
            'block_name' => 'required|string',
            'plot_name' => 'required|string',
            'area_acre' => 'required|numeric|min:0',
            'area_covered' => 'required|numeric|min:0',
            'irrigation_no' => 'required|string',
            'irrigation_type_id' => 'required|integer|exists:master_irrigation_types,id',
            'irrigation_date' => 'required|date',
            'start_time' => 'required|date_format:H:i',
            'end_time' => 'required|date_format:H:i',
            'water_source_id' => 'required|integer|exists:water_sources,id', // ✅ new
            'capacity_id' => 'required|integer|exists:water_sources,id',
            'category' => 'nullable|array',
            'category.*' => 'string',
            'manpower_type' => 'nullable|string',
            'unskilled' => 'nullable|integer|min:0',
            'semi_skilled_1' => 'nullable|integer|min:0',
            'semi_skilled_2' => 'nullable|integer|min:0',
            'major_maintenance' => 'nullable|string',
            'cost_per_unit' => 'nullable|numeric|min:0',
            'spare_parts' => 'nullable|array',
            'spare_parts.*.spare_part' => 'nullable|string',
            'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
        ]);

DB::beginTransaction();

// Convert category names to IDs
        // $manpowerCategoryIds = [];
        // foreach ($request->category as $categoryName) {
        //     $category = DB::table('master_manpower')->where('category', $categoryName)->first();
        //     if (!$category) throw new \Exception("Manpower category '$categoryName' not found");
        //     $manpowerCategoryIds[] = $category->id;
        // }
                $manpowerCategoryIds = [];
        if (!empty($request->category) && is_array($request->category)) {
            foreach ($request->category as $categoryName) {
                $category = DB::table('master_manpower')->where('category', $categoryName)->first();
                if (!$category) throw new \Exception("Manpower category '$categoryName' not found");
                $manpowerCategoryIds[] = $category->id;
            }
        }

// Time calculation
        $startMinutes = (explode(':', $request->start_time)[0] * 60) + explode(':', $request->start_time)[1];
        $endMinutes = (explode(':', $request->end_time)[0] * 60) + explode(':', $request->end_time)[1];
        if ($endMinutes < $startMinutes) $endMinutes += 1440;
        $hoursUsed = round(($endMinutes - $startMinutes) / 60, 2);

$references = DB::table('master_irrigation_types')->find($request->irrigation_type_id);
        if (!$references) throw new \Exception('Irrigation type data not found');

// ✅ Fetch both water_source and capacity
        $waterSource = DB::table('water_sources')->find($request->water_source_id);
        if (!$waterSource) throw new \Exception('Water source data not found');

$capacityDetails = DB::table('water_sources')->find($request->capacity_id);
        if (!$capacityDetails) throw new \Exception('Capacity details not found');

$capacityLph = $capacityDetails->capacity_lph;
        $waterUsed = $capacityLph * $hoursUsed;

$powerConsumptionKw = $capacityDetails->power_consumption_kw;
        $electricityUnits = $powerConsumptionKw * $hoursUsed;
        $costPerUnit = $request->cost_per_unit ?? $waterSource->cost_per_unit ?? 0;
        $electricityCost = $electricityUnits * $costPerUnit;

// Manpower rates
        $unskilledRate = DB::table('master_manpower')->where('category', 'Unskilled')->value('rate') ?? 0;
        $semiSkilled1Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 1')->value('rate') ?? 0;
        $semiSkilled2Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 2')->value('rate') ?? 0;

$manpowerCosts = [
            'unskilled' => ($request->unskilled ?? 0) * $unskilledRate,
            'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $semiSkilled1Rate,
            'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $semiSkilled2Rate,
        ];
        $totalManpowerCost = array_sum($manpowerCosts);

// Spare parts logic
        $majorMaintenanceContent = null;
        $majorCostValue = null;
        $processedSpareParts = [];
        if (!empty($request->spare_parts)) {
            $majorMaintenanceContent = json_encode($request->spare_parts);
            foreach ($request->spare_parts as $part) {
                $majorCostValue += $part['value'];
                $processedSpareParts[] = [
                    'item' => $part['spare_part'],
                    'cost' => number_format((float)$part['value'], 2)
                ];
            }
        } elseif (!empty($request->major_maintenance)) {
            $majorMaintenanceContent = $request->major_maintenance;
            if (strtolower($majorMaintenanceContent) !== 'none') {
                $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
            }
        }

$totalCost = round($totalManpowerCost + $electricityCost + ($majorCostValue ?? 0));
        $manpowerCategoryIdsString = implode(',', $manpowerCategoryIds);

// ✅ Insert with separate water_source_id and capacity_id
        $irrigationId = DB::table('pre_irrigation')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'area' => $request->area_acre,
            'area_covered' => $request->area_covered,
            'irrigation_no' => $request->irrigation_no,
            'irrigation_type_id' => $request->irrigation_type_id,
            'date' => $request->irrigation_date,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'hours_used' => $hoursUsed,
            'water_source_id' => $request->water_source_id,
            'capacity_id' => $request->capacity_id,
            'capacity_lph' => $capacityLph,
            'water_used' => $waterUsed,
            'power_consumption_kw' => $powerConsumptionKw,
            'electricity_units' => $electricityUnits,
            'cost_per_unit' => $costPerUnit,
            'electricity_cost' => $electricityCost,
            'manpower_category_id' => $manpowerCategoryIdsString,
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->unskilled,
            'semi_skilled_1' => $request->semi_skilled_1,
            'semi_skilled_2' => $request->semi_skilled_2,
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'total_cost' => $totalCost,
            'user_id' => $userId,
            'site_id' => $siteName,
            'created_at' => now(),
            'updated_at' => now()
        ]);

DB::commit();

return response()->json([
            'success' => true,
            'message' => 'Irrigation record created successfully',
            'data' => [
                'id' => $irrigationId,
                'block_name' => $request->block_name,
                'plot_name' => $request->plot_name,
                'area_acre' => $request->area_acre,
                'irrigation_type' => $references->type_name,
                'water_source' => $waterSource->name,
                'capacity' => $capacityLph,
                'water_used' => $waterUsed,
                'cost_breakdown' => [
                    'manpower' => [
                        'unskilled' => ['count' => $request->unskilled ?? 0, 'rate' => $unskilledRate, 'cost' => $manpowerCosts['unskilled']],
                        'semi_skilled_1' => ['count' => $request->semi_skilled_1 ?? 0, 'rate' => $semiSkilled1Rate, 'cost' => $manpowerCosts['semi_skilled_1']],
                        'semi_skilled_2' => ['count' => $request->semi_skilled_2 ?? 0, 'rate' => $semiSkilled2Rate, 'cost' => $manpowerCosts['semi_skilled_2']],
                        'total' => $totalManpowerCost
                    ],
                    'electricity' => [
                        'units' => $electricityUnits,
                        'rate' => $costPerUnit,
                        'cost' => $electricityCost
                    ],
                    'major_maintenance' => $processedSpareParts,
                    'total_cost' => $totalCost
                ],
                'timestamp' => now()->toDateTimeString()
            ]
        ], 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'error' => 'Failed to create irrigation record',
            'message' => $e->getMessage(),
            'trace' => config('app.debug') ? $e->getTrace() : null
        ], 500);
    }
}

//soil condition
public function getSoilCondition(Request $request)
{
    // Get only the data columns (excluding action buttons)
    $watersource = DB::table('master_soil_condition')
                    ->select(['id','soil_condition'])
                    ->get();
    
    return response()->json([
        'status' => 'success',
        'data' => $watersource
    ], 200);
}
    //land preparation
   // new code

//  public function LandPreparation(Request $request)
//   {
//       // Validation rules
//       $request->validate([
//           'block_name' => 'required|string',
//           'plot_name' => 'required|string',
//           'machine_ids' => 'nullable|array',
//           'machine_ids.*' => 'string|max:20',
//           'tractor_ids' => 'nullable|array',
//           'tractor_ids.*' => 'string|max:20',
//           'area' => 'required|numeric',
//           'area_covered' => 'nullable|string',
//           'hsd_consumption' => 'nullable|numeric|min:0',
//           'time_hrs' => 'nullable|numeric',
//           'start_time' => 'nullable|date_format:H:i',
//           'end_time' => 'nullable|date_format:H:i',
//           'soil_condition_id' => 'required|integer|exists:master_soil_condition,id',
//           'category' => 'required|array',
//           'date' => 'required|date',
//           'manpower_type' => 'nullable|string',
//           'unskilled' => 'nullable|integer|min:0',
//           'semi_skilled_1' => 'nullable|integer|min:0',
//           'semi_skilled_2' => 'nullable|integer|min:0',
//           'spare_parts' => 'nullable|array',
//           'user_id' => 'sometimes|required|integer',
//       ]);
   
//       try {
//           // Authenticate user
//           $user = auth()->user();
//           if (!$user) {
//               return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
//           }
   
//           $siteName = $user->site_name;
//           $userId = $user->id;
   
//           if (!$siteName) {
//               return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
//           }
   
//           DB::beginTransaction();
   
//           // Calculate hours used
//           $hours_used = $request->time_hrs;
//           if ($request->start_time && $request->end_time) {
//               $start = Carbon::createFromFormat('Y-m-d H:i', $request->date . ' ' . $request->start_time);
//               $end = Carbon::createFromFormat('Y-m-d H:i', $request->date . ' ' . $request->end_time);
//               if ($end->lessThan($start)) {
//                   $end->addDay();
//               }
//               $hours_used = round($start->floatDiffInHours($end), 2);
//           }
   
//           // Fetch manpower categories
//           $manpowerCategories = DB::table('master_manpower')
//               ->whereIn('category', $request->category)->get();
   
//           if ($manpowerCategories->isEmpty()) {
//               return response()->json([
//                   'error' => 'No matching manpower categories found',
//                   'valid_categories' => ['Unskilled', 'Semi Skilled 1', 'Semi Skilled 2']
//               ], 404);
//           }
   
//           // Rates by type
//           $rates = [
//               'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
//               'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
//               'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
//           ];
   
//           // Calculate manpower cost
//           $manpowerCosts = [
//               'unskilled' => ($request->unskilled ?? 0) * $rates['unskilled'],
//               'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $rates['semi_skilled_1'],
//               'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $rates['semi_skilled_2'],
//           ];
//           $manpowerTotal = array_sum($manpowerCosts);
   
//           // Calculate major maintenance cost
//           $majorMaintenanceContent = null;
//           $majorCostValue = null;
//           $processedSpareParts = [];
   
//           if (!empty($request->spare_parts) && is_array($request->spare_parts)) {
//               $majorMaintenanceContent = json_encode($request->spare_parts);
//               $majorCostValue = 0;
   
//               foreach ($request->spare_parts as $part) {
//                   if (isset($part['value']) && is_numeric($part['value'])) {
//                       $majorCostValue += (float)$part['value'];
//                   }
//                   if (isset($part['spare_part'], $part['value'])) {
//                       $processedSpareParts[] = [
//                           'item' => $part['spare_part'],
//                           'cost' => number_format((float)$part['value'], 2)
//                       ];
//                   }
//               }
//           } elseif (!empty($request->major_maintenance)) {
//               $majorMaintenanceContent = $request->major_maintenance;
//               if (strtolower($majorMaintenanceContent) !== 'none') {
//                   $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
//               }
//           }
   
//           // Diesel FIFO logic
//           $dieselCost = 0;
//           $dieselRate = 0;
//           $remainingConsumption = $request->hsd_consumption ?? 0;
//           $consumptionDetails = [];
   
//           if ($remainingConsumption > 0) {
//               $stocks = DB::table('diesel_stocks')
//                   ->where('diesel_stock', '>', 0)
//                   ->orderBy('date_of_purchase')
//                   ->get();
   
//               foreach ($stocks as $stock) {
//                   if ($remainingConsumption <= 0) break;
   
//                   $liters = min($stock->diesel_stock, $remainingConsumption);
//                   DB::table('diesel_stocks')
//                       ->where('id', $stock->id)
//                       ->update([
//                           'diesel_stock' => $stock->diesel_stock - $liters,
//                           'updated_at' => now()
//                       ]);
   
//                   $dieselCost += $liters * $stock->rate_per_liter;
//                   $remainingConsumption -= $liters;
   
//                   $consumptionDetails[] = [
//                       'stock_id' => $stock->id,
//                       'liters_used' => $liters,
//                       'rate' => $stock->rate_per_liter,
//                       'cost' => $liters * $stock->rate_per_liter,
//                   ];
   
//                   if ($dieselRate == 0) {
//                       $dieselRate = $stock->rate_per_liter;
//                   }
//               }
   
//               if ($remainingConsumption > 0) {
//                   DB::rollBack();
//                   return response()->json([
//                       'status' => 'error',
//                       'error' => 'INSUFFICIENT_DIESEL',
//                       'message' => 'Not enough diesel stock available',
//                       'requested_liters' => $request->hsd_consumption,
//                       'available_liters' => $request->hsd_consumption - $remainingConsumption,
//                       'shortage' => $remainingConsumption,
//                       'consumption_details' => $consumptionDetails
//                   ], 400);
//               }
//           }
   
//           $totalCost = $manpowerTotal + $dieselCost;
   
//           // Insert into land preparation
//           $insertId = DB::table('land_prepration')->insertGetId([
//               'block_name' => $request->block_name,
//               'plot_name' => $request->plot_name,
//               'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
//               'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
//               'site_id' => $siteName,
//               'area' => $request->area,
//               'area_covered' => $request->area_covered,
//               'soil_condition_id' => $request->soil_condition_id,
//               'hsd_consumption' => $request->hsd_consumption,
//               'time_hrs' => $hours_used,
//               'date' => $request->date,
//               'start_time' => $request->start_time,
//               'end_time' => $request->end_time,
//               'hours_used' => $hours_used,
//               'category_id' => $manpowerCategories->pluck('id')->implode(','),
//               'manpower_type' => $request->manpower_type,
//               'unskilled' => $request->unskilled,
//               'semi_skilled_1' => $request->semi_skilled_1,
//               'semi_skilled_2' => $request->semi_skilled_2,
//               'major_maintenance' => $majorMaintenanceContent,
//               'user_id' => $userId,
//               'total_cost' => $totalCost,
//               'created_at' => now(),
//               'updated_at' => now(),
//           ]);
   
//           $soilCondition = DB::table('master_soil_condition')
//               ->where('id', $request->soil_condition_id)
//               ->value('soil_condition');
   
//           DB::commit();
   
//           return response()->json([
//               'message' => 'Land Preparation record inserted successfully',
//               'insert_id' => $insertId,
//               'soil_condition' => $soilCondition,
//               'total_cost' => number_format($totalCost, 2),
//               'time_data' => [
//                   'start_time' => $request->start_time,
//                   'end_time' => $request->end_time,
//                   'hours_used' => $hours_used
//               ],
//               'cost_breakdown' => [
//                   'manpower' => array_merge($manpowerCosts, ['total' => number_format($manpowerTotal, 2)]),
//                   'diesel' => $request->hsd_consumption ? [
//                       'consumption' => $request->hsd_consumption,
//                       'rate_per_liter' => number_format($dieselRate, 2),
//                       'cost' => number_format($dieselCost, 2),
//                       'consumption_details' => $consumptionDetails,
//                       'method' => 'FIFO'
//                   ] : null
//               ],
//               'manpower_rates_used' => $manpowerCategories->map(function ($item) {
//                   return [
//                       'id' => $item->id,
//                       'category' => $item->category,
//                       'type' => $item->type,
//                       'rate' => number_format($item->rate, 2)
//                   ];
//               })
//           ], 201);
   
//       } catch (\Exception $e) {
//           DB::rollBack();
//           return response()->json([
//               'error' => 'Failed to create land preparation record',
//               'message' => $e->getMessage(),
//               'trace' => config('app.debug') ? $e->getTrace() : null
//           ], 500);
//       }
//   }
public function LandPreparation(Request $request)
{
    // Validation rules
    $request->validate([
        'block_name' => 'required|string',
        'plot_name' => 'required|string',
        'machine_ids' => 'nullable|array',
        'machine_ids.*' => 'string|max:20',
        'tractor_ids' => 'nullable|array',
        'tractor_ids.*' => 'string|max:20',
        'area' => 'required|numeric',
        'area_covered' => 'nullable|string',
        'hsd_consumption' => 'nullable|numeric|min:0',
        'time_hrs' => 'nullable|numeric',
        'start_time' => 'nullable|date_format:H:i',
        'end_time' => 'nullable|date_format:H:i',
        'soil_condition_id' => 'required|integer|exists:master_soil_condition,id',
        'category' => 'nullable|array', // changed from required
        'date' => 'required|date',
        'manpower_type' => 'nullable|string',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'spare_parts' => 'nullable|array',
        'user_id' => 'sometimes|required|integer',
    ]);

try {
        $user = auth()->user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteName = $user->site_name;
        $userId = $user->id;

if (!$siteName) {
            return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
        }

DB::beginTransaction();

// Calculate hours used
        $hours_used = $request->time_hrs;
        if ($request->start_time && $request->end_time) {
            $start = Carbon::createFromFormat('Y-m-d H:i', $request->date . ' ' . $request->start_time);
            $end = Carbon::createFromFormat('Y-m-d H:i', $request->date . ' ' . $request->end_time);
            if ($end->lessThan($start)) {
                $end->addDay();
            }
            $hours_used = round($start->floatDiffInHours($end), 2);
        }

// Manpower defaults
        $manpowerCategories = collect();
        $rates = ['unskilled' => 0, 'semi_skilled_1' => 0, 'semi_skilled_2' => 0];
        $manpowerCosts = ['unskilled' => 0, 'semi_skilled_1' => 0, 'semi_skilled_2' => 0];
        $manpowerTotal = 0;

// Only fetch manpower if category provided
        if (!empty($request->category) && is_array($request->category)) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $request->category)->get();

if ($manpowerCategories->isEmpty()) {
                return response()->json([
                    'error' => 'No matching manpower categories found',
                    'valid_categories' => ['Unskilled', 'Semi Skilled 1', 'Semi Skilled 2']
                ], 404);
            }

// Rates by type
            $rates = [
                'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
                'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
                'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
            ];

// Costs
            $manpowerCosts = [
                'unskilled' => ($request->unskilled ?? 0) * $rates['unskilled'],
                'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $rates['semi_skilled_1'],
                'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $rates['semi_skilled_2'],
            ];
            $manpowerTotal = array_sum($manpowerCosts);
        }

// --- rest of your code remains unchanged (spare parts, diesel, etc.) ---

// Diesel FIFO logic
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->hsd_consumption ?? 0;
        $consumptionDetails = [];
        if ($remainingConsumption > 0) {
            $stocks = DB::table('diesel_stocks')
                ->where('diesel_stock', '>', 0)
                ->orderBy('date_of_purchase')
                ->get();
            foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;
                $liters = min($stock->diesel_stock, $remainingConsumption);
                DB::table('diesel_stocks')
                    ->where('id', $stock->id)
                    ->update([
                        'diesel_stock' => $stock->diesel_stock - $liters,
                        'updated_at' => now()
                    ]);
                $dieselCost += $liters * $stock->rate_per_liter;
                $remainingConsumption -= $liters;
                $consumptionDetails[] = [
                    'stock_id' => $stock->id,
                    'liters_used' => $liters,
                    'rate' => $stock->rate_per_liter,
                    'cost' => $liters * $stock->rate_per_liter,
                ];
                if ($dieselRate == 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }
            if ($remainingConsumption > 0) {
                DB::rollBack();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INSUFFICIENT_DIESEL',
                    'message' => 'Not enough diesel stock available',
                    'requested_liters' => $request->hsd_consumption,
                    'available_liters' => $request->hsd_consumption - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

$totalCost = $manpowerTotal + $dieselCost;

// Insert into land preparation
        $insertId = DB::table('land_prepration')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'site_id' => $siteName,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'soil_condition_id' => $request->soil_condition_id,
            'hsd_consumption' => $request->hsd_consumption,
            'time_hrs' => $hours_used,
            'date' => $request->date,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'hours_used' => $hours_used,
            'category_id' => $manpowerCategories->pluck('id')->implode(','),
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->unskilled,
            'semi_skilled_1' => $request->semi_skilled_1,
            'semi_skilled_2' => $request->semi_skilled_2,
            'major_maintenance' => $majorMaintenanceContent ?? null,
            'user_id' => $userId,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

$soilCondition = DB::table('master_soil_condition')
            ->where('id', $request->soil_condition_id)
            ->value('soil_condition');

DB::commit();

return response()->json([
            'message' => 'Land Preparation record inserted successfully',
            'insert_id' => $insertId,
            'soil_condition' => $soilCondition,
            'total_cost' => number_format($totalCost, 2),
            'time_data' => [
                'start_time' => $request->start_time,
                'end_time' => $request->end_time,
                'hours_used' => $hours_used
            ],
            'cost_breakdown' => [
                'manpower' => array_merge($manpowerCosts, ['total' => number_format($manpowerTotal, 2)]),
                'diesel' => $request->hsd_consumption ? [
                    'consumption' => $request->hsd_consumption,
                    'rate_per_liter' => number_format($dieselRate, 2),
                    'cost' => number_format($dieselCost, 2),
                    'consumption_details' => $consumptionDetails,
                    'method' => 'FIFO'
                ] : null
            ],
            'manpower_rates_used' => $manpowerCategories->map(function ($item) {
                return [
                    'id' => $item->id,
                    'category' => $item->category,
                    'type' => $item->type,
                    'rate' => number_format($item->rate, 2)
                ];
            })
        ], 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'error' => 'Failed to create land preparation record',
            'message' => $e->getMessage(),
            'trace' => config('app.debug') ? $e->getTrace() : null
        ], 500);
    }
}

//getSeedVarieties
    // public function getSeedVarieties(Request $request)
    // {
    //     $seeds = DB::table('master_seed')
    //         ->select('seed_name', 'variety_of_seed')
    //         ->orderByRaw("FIELD(seed_name, 'Wheat') DESC") // Show 'Wheat' on top
    //         ->orderBy('seed_name')
    //         ->get();

//     $formattedData = $seeds->groupBy('seed_name')->map(function ($item) {
    //         return $item->pluck('variety_of_seed');
    //     });

//     return response()->json([
    //         'status' => 'success',
    //         'data' => $formattedData
    //     ]);
    // }
    
public function getSeedVarieties(Request $request)
{
    $user = Auth::user();
    if (!$user) {
        return response()->json([
            'status' => 'error',
            'message' => 'User not authenticated.'
        ], 401);
    }

// Get seeds for user's site and only non-deleted ones
    $seeds = DB::table('master_seed')
        ->where('site_id', $user->site_id)       // ✅ Only seeds from user's site
        ->where('is_deleted', 0)                 // ✅ Exclude deleted seeds
        ->select('seed_name', 'variety_of_seed')
        ->orderByRaw("FIELD(seed_name, 'Wheat') DESC")
        ->orderBy('seed_name')
        ->get();

// Group by seed_name
    $formattedData = $seeds->groupBy('seed_name')->map(function ($item) {
        return $item->pluck('variety_of_seed');
    });

return response()->json([
        'status' => 'success',
        'data' => $formattedData
    ]);
}

//Sowing Method
public function getSowingMethods(Request $request)
{
    $sowingMethods = DB::table('master_seed')
        ->select('id', 'sowing_method')
        ->whereNotNull('sowing_method')  // Exclude null values
        ->get();

return response()->json([
        'status' => 'success',
        'data' => $sowingMethods
    ]);
}

public function getPurpose(Request $request)
{
    $purposes = DB::table('master_seed')
        ->distinct()
        ->pluck('purpose');

return response()->json([
        'status' => 'success',
        'data' => $purposes
    ]);
}
//sowing source 
public function getswoing_source(Request $request)
{
    $purposes = DB::table('master_seed')
        ->distinct()
        ->whereNotNull('sowing_source')  // Exclude null values
        ->pluck('sowing_source');

return response()->json([
        'status' => 'success',
        'data' => $purposes
    ]);
}
    //sowing opration

//new code

public function storeSowingOperation(Request $request)
{
    $user = auth()->user();
    if (!$user) {
        return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
    }

$siteName = $user->site_name;
    $userId = $user->id;

if (!$siteName) {
        return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
    }

try {
        DB::beginTransaction();

$seedInfo = DB::table('master_seed')->where('seed_name', $request->seed_name)->first();
        if (!$seedInfo) {
            return response()->json(['error' => 'Invalid seed name'], 422);
        }

if ($request->seed_consumption && $request->seed_consumption > $seedInfo->seed_stock_kg) {
            return response()->json([
                'error' => 'Insufficient seed stock',
                'available_stock' => $seedInfo->seed_stock_kg,
                'requested_consumption' => $request->seed_consumption
            ], 422);
        }

$seedId = $seedInfo->id;
        $seedUnitInfo = $seedInfo->uom ?? 'unknown';
        $seedRateInfo = $seedInfo->rate_of_seed ?? 0;

$sowingMethodName = $request->sowing_method ?? $seedInfo->sowing_method;
        $sowingMethodId = null;
        if ($sowingMethodName) {
            $methodSeed = DB::table('master_seed')->where('sowing_method', $sowingMethodName)->first();
            $sowingMethodId = $methodSeed->id ?? null;
        }

$purposeMap = ['Fodder Crop' => 1, 'Cash Crop' => 2, 'Seed Crop' => 3];
        $purposeName = $request->purpose_name ?? $seedInfo->purpose ?? 'unknown';
        $purposeId = $purposeMap[$purposeName] ?? null;

$categoryNames = $request->category ?? []; // Handle when category is not selected
        $manpowerCategories = collect();
        $categoryIds = [];

// Handle category processing - allow null/empty categories
        if (!empty($categoryNames) && is_array($categoryNames)) {
            $manpowerCategories = DB::table('master_manpower')->whereIn('category', $categoryNames)->get();
            
            if ($manpowerCategories->isEmpty()) {
                return response()->json(['error' => 'Invalid manpower categories provided.'], 404);
            }
            
            $categoryIds = $manpowerCategories->pluck('id')->toArray();
        }

$unskilledRate = $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0;
        $semiSkilled1Rate = $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0;
        $semiSkilled2Rate = $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0;

$unskilledCost = ($request->unskilled ?? 0) * $unskilledRate;
        $semiSkilled1Cost = ($request->semi_skilled_1 ?? 0) * $semiSkilled1Rate;
        $semiSkilled2Cost = ($request->semi_skilled_2 ?? 0) * $semiSkilled2Rate;
        $manpowerTotal = $unskilledCost + $semiSkilled1Cost + $semiSkilled2Cost;

$majorMaintenanceContent = null;
        $majorCostValue = null;
        $processedSpareParts = [];

if (!empty($request->spare_parts) && is_array($request->spare_parts)) {
            $majorMaintenanceContent = json_encode($request->spare_parts);
            $majorCostValue = 0;

foreach ($request->spare_parts as $part) {
                if (isset($part['value']) && is_numeric($part['value'])) {
                    $majorCostValue += (float)$part['value'];
                }
                if (isset($part['spare_part'], $part['value'])) {
                    $processedSpareParts[] = [
                        'item' => $part['spare_part'],
                        'cost' => number_format((float)$part['value'], 2)
                    ];
                }
            }
        } elseif (!empty($request->major_maintenance)) {
            $majorMaintenanceContent = $request->major_maintenance;
            if (strtolower($majorMaintenanceContent) !== 'none') {
                $processedSpareParts[] = ['item' => $majorMaintenanceContent, 'cost' => 'N/A'];
            }
        }

// FIFO Diesel logic
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->hsd_consumption ?? 0;
        $consumptionDetails = [];

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;

$litersTaken = min($stock->diesel_stock, $remainingConsumption);

DB::table('diesel_stocks')
                    ->where('id', $stock->id)
                    ->update([
                        'diesel_stock' => $stock->diesel_stock - $litersTaken,
                        'updated_at' => now()
                    ]);

$dieselCost += $litersTaken * $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;

if ($dieselRate == 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }

if ($remainingConsumption > 0) {
                DB::rollBack();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INSUFFICIENT_DIESEL',
                    'message' => 'Not enough diesel stock available',
                    'requested_liters' => $request->hsd_consumption,
                    'available_liters' => $request->hsd_consumption - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

$pricePerUnit = ($seedUnitInfo == '1ton') ? $seedRateInfo / 1000 : $seedRateInfo;
        $seedCost = $request->seed_consumption * $pricePerUnit;
        $totalCost = $manpowerTotal + $dieselCost + $seedCost;

$hours_used = null;
        if ($request->start_time && $request->end_time) {
            $startDateTime = Carbon::createFromFormat('Y-m-d H:i', $request->sowing_date . ' ' . $request->start_time);
            $endDateTime = Carbon::createFromFormat('Y-m-d H:i', $request->sowing_date . ' ' . $request->end_time);

if ($endDateTime->lessThan($startDateTime)) {
                $endDateTime->addDay();
            }

$hours_used = round($startDateTime->floatDiffInHours($endDateTime), 2);
        } else {
            $hours_used = $request->time_hrs;
        }

$insertId = DB::table('showing_oprations')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'seed_id' => $seedId,
            'variety' => $request->variety,
            'sowing_method_id' => $sowingMethodId,
            'date' => $request->sowing_date,
            'seed_consumption' => $request->seed_consumption,
            'purpose_name' => $request->purpose_name,
            'swowing_source_id' => $request->sowing_source_id,
            'row_distance' => $request->row_distance,
            'sowing_depth' => $request->sowing_depth,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'hsd_consumption' => $request->hsd_consumption,
            'time_hrs' => $hours_used,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'hours_used' => $hours_used,
            'site_id' => $siteName,
            'category_id' => json_encode($categoryIds),
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->unskilled,
            'semi_skilled_1' => $request->semi_skilled_1,
            'semi_skilled_2' => $request->semi_skilled_2,
            'major_maintenance' => $majorMaintenanceContent,
             'major_cost' => $majorCostValue,
            'user_id' => $userId,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now()
        ]);

if ($request->seed_consumption) {
            DB::table('master_seed')
                ->where('id', $seedId)
                ->decrement('seed_stock_kg', $request->seed_consumption);
        }

DB::commit();

$response = [
            'message' => 'Sowing operation record created successfully',
            'insert_id' => $insertId,
            'total_cost' => number_format($totalCost, 2),
            'updated_seed_stock' => $seedInfo->seed_stock_kg - ($request->seed_consumption ?? 0),
            'cost_breakdown' => [
                'manpower' => [
                    'unskilled' => [
                        'quantity' => $request->unskilled ?? 0,
                        'rate' => number_format($unskilledRate, 2),
                        'cost' => number_format($unskilledCost, 2)
                    ],
                    'semi_skilled_1' => [
                        'quantity' => $request->semi_skilled_1 ?? 0,
                        'rate' => number_format($semiSkilled1Rate, 2),
                        'cost' => number_format($semiSkilled1Cost, 2)
                    ],
                    'semi_skilled_2' => [
                        'quantity' => $request->semi_skilled_2 ?? 0,
                        'rate' => number_format($semiSkilled2Rate, 2),
                        'cost' => number_format($semiSkilled2Cost, 2)
                    ],
                    'total' => number_format($manpowerTotal, 2)
                ],
                'seed' => [
                    'consumption' => $request->seed_consumption ?? 0,
                    'unit' => $seedUnitInfo,
                    'original_rate' => number_format($seedRateInfo, 2),
                    'calculated_rate_per_kg' => number_format($pricePerUnit, 2),
                    'cost' => number_format($seedCost, 2)
                ]
            ],
            'seed_info' => [
                'id' => $seedInfo->id,
                'sowing_method' => $sowingMethodName,
                'purpose_id' => $purposeId,
                'purpose_name' => $purposeName
            ],
            'time_data' => [
                'start_time' => $request->start_time,
                'end_time' => $request->end_time,
                'hours_used' => $hours_used
            ]
        ];

if ($request->hsd_consumption) {
            $response['cost_breakdown']['diesel'] = [
                'consumption' => $request->hsd_consumption,
                'rate_per_liter' => number_format($dieselRate, 2),
                'cost' => number_format($dieselCost, 2),
                'consumption_details' => $consumptionDetails,
                'method' => 'FIFO'
            ];
        }

return response()->json($response, 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'error' => 'Failed to create sowing operation record',
            'message' => $e->getMessage(),
            'trace' => config('app.debug') ? $e->getTrace() : null
        ], 500);
    }
}

//post irrigation

//oldcode

//new code

public function postIrrigation(Request $request)
    {
        $user = Auth::user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteId = $user->site_id;
        $userId = $user->id;

if (!$siteId) {
            return response()->json(['status' => 'error', 'message' => 'Site ID not found for this user.'], 404);
        }

try {
            DB::beginTransaction();

$manpowerCategoryIds = [];
          if (!empty($request->category) && is_array($request->category)) {
                    foreach ($request->category as $categoryName) {
                        $category = DB::table('master_manpower')
                            ->where('category', $categoryName)
                            ->first();
        
                        if (!$category) {
                            throw new \Exception("Manpower category '$categoryName' not found");
                        }
        
                        $manpowerCategoryIds[] = $category->id;
                    }
                }

$startTimeParts = explode(':', $request->start_time);
            $endTimeParts = explode(':', $request->end_time);
            $startMinutes = ($startTimeParts[0] * 60) + $startTimeParts[1];
            $endMinutes = ($endTimeParts[0] * 60) + $endTimeParts[1];

if ($endMinutes < $startMinutes) {
                $endMinutes += 24 * 60;
            }

$minutesUsed = $endMinutes - $startMinutes;
            $hoursUsed = round($minutesUsed / 60, 2);

$references = DB::table('master_irrigation_types')
                ->where('id', $request->irrigation_type_id)
                ->first();
            if (!$references) {
                throw new \Exception('Irrigation type data not found');
            }

$waterSource = DB::table('water_sources')
                ->where('id', $request->water_source_id)
                ->first();
            if (!$waterSource) {
                throw new \Exception('Water source data not found');
            }

$capacityDetails = DB::table('water_sources')
                ->where('id', $request->capacity_id)
                ->first();
            if (!$capacityDetails) {
                throw new \Exception('Capacity details not found');
            }

$unskilledRate = DB::table('master_manpower')->where('category', 'Unskilled')->value('rate') ?? 0;
            $semiSkilled1Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 1')->value('rate') ?? 0;
            $semiSkilled2Rate = DB::table('master_manpower')->where('category', 'Semi Skilled 2')->value('rate') ?? 0;

$capacityLph = $capacityDetails->capacity_lph;
            $waterUsed = $capacityLph * $hoursUsed;
            $powerConsumptionKw = $capacityDetails->power_consumption_kw;
            $electricityUnits = $powerConsumptionKw * $hoursUsed;
            $costPerUnit = $request->cost_per_unit ?? $waterSource->cost_per_unit ?? 0;
            $electricityCost = $electricityUnits * $costPerUnit;

$manpowerCosts = [
                'unskilled' => ($request->unskilled ?? 0) * $unskilledRate,
                'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $semiSkilled1Rate,
                'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $semiSkilled2Rate,
            ];

$majorMaintenanceData = null;
            $majorCost = 0;
            $processedSpareParts = [];

if ($request->has('spare_parts') && is_array($request->spare_parts)) {
                $sparePartsInput = $request->spare_parts;
                $majorMaintenanceData = json_encode($sparePartsInput);

$totalManpowerCost = array_sum($manpowerCosts);
            $totalCost = round($totalManpowerCost + $electricityCost + $majorCost);
            $manpowerCategoryIdsString = implode(',', $manpowerCategoryIds);

$irrigationId = DB::table('post_irrigation')->insertGetId([
                'block_name' => $request->block_name,
                'plot_name' => $request->plot_name,
                'area' => $request->area_acre,
                'area_covered' => $request->area_covered,
                'irrigation_no' => $request->irrigation_no,
                'irrigation_type_id' => $request->irrigation_type_id,
                'date' => $request->irrigation_date,
                'day_ofter_swowing' => $request->day_ofter_swowing,
                'start_time' => $request->start_time,
                'end_time' => $request->end_time,
                'hours_used' => $hoursUsed,
                'water_source_id' => $request->water_source_id,
                'capacity_id' => $request->capacity_id,
                'capacity_lph' => $capacityLph,
                'water_used' => $waterUsed,
                'power_consumption_kw' => $powerConsumptionKw,
                'electricity_units' => $electricityUnits,
                'cost_per_unit' => $costPerUnit,
                'electricity_cost' => $electricityCost,
                'manpower_category_id' => $manpowerCategoryIdsString,
                'manpower_type' => $request->manpower_type,
                'unskilled' => $request->unskilled,
                'semi_skilled_1' => $request->semi_skilled_1,
                'semi_skilled_2' => $request->semi_skilled_2,
                'major_maintenance' => $majorMaintenanceData,
                'major_cost' => $majorCost,
                'user_id' => $userId,
                'site_id' => $siteId,
                'total_cost' => $totalCost,
                'created_at' => now(),
                'updated_at' => now()
            ]);

DB::commit();

return response()->json([
                'success' => true,
                'message' => 'Post Irrigation record created successfully',
                'data' => [
                    'id' => $irrigationId,
                    'block_name' => $request->block_name,
                    'plot_name' => $request->plot_name,
                    'area_acre' => $request->area_acre,
                    'irrigation_type' => $references->type_name,
                    'water_source' => $waterSource->name,
                    'capacity' => $capacityLph,
                    'water_used' => $waterUsed,
                    'cost_breakdown' => [
                        'manpower' => [
                            'unskilled' => [
                                'count' => $request->unskilled ?? 0,
                                'rate' => $unskilledRate,
                                'cost' => $manpowerCosts['unskilled']
                            ],
                            'semi_skilled_1' => [
                                'count' => $request->semi_skilled_1 ?? 0,
                                'rate' => $semiSkilled1Rate,
                                'cost' => $manpowerCosts['semi_skilled_1']
                            ],
                            'semi_skilled_2' => [
                                'count' => $request->semi_skilled_2 ?? 0,
                                'rate' => $semiSkilled2Rate,
                                'cost' => $manpowerCosts['semi_skilled_2']
                            ],
                            'total' => $totalManpowerCost
                        ],
                        'electricity' => [
                            'units' => $electricityUnits,
                            'rate' => $costPerUnit,
                            'cost' => $electricityCost
                        ],
                        'major_maintenance_cost' => $majorCost,
                        'major_maintenance_items' => $processedSpareParts,
                        'total_cost' => $totalCost
                    ],
                    'timestamp' => now()->toDateTimeString()
                ]
            ], 201);

} catch (\Exception $e) {
            DB::rollBack();
            return response()->json([
                'success' => false,
                'error' => 'Failed to create irrigation record',
                'message' => $e->getMessage(),
                'trace' => config('app.debug') ? $e->getTrace() : null
            ], 500);
        }
    }

//master fotilizer name 
public function masetr_fatilizer(Request $request)
{
    $tractorNames = DB::table('master_fertilizer')->pluck('fertilizer_name');

return response()->json([
        'status' => 'success',
        'data' => $tractorNames
    ], 200);
}

//fatilizer soil

public function postFertilizer(Request $request)
{
    DB::beginTransaction();
    try {
        $user = auth()->user();
        if (!$user) return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);

$siteName = $user->site_name;
        $userId = $user->id;
        if (!$siteName) return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);

$fertilizers = $request->input('fertilizer_ids');
        $totalQuantity = 0;

$userData = DB::table('users')->where('id', $userId)->select('block_name', 'plot_name')->first();
        $blockName = $request->block_name ?? $userData->block_name;
        $plotName = $request->plot_name ?? $userData->plot_name;
        if (!$blockName || !$plotName) return response()->json(['error' => 'Block name and plot name are required.'], 422);

[$sh, $sm] = explode(':', $request->start_time);
        [$eh, $em] = explode(':', $request->end_time);
        $startMinutes = ($sh * 60) + $sm;
        $endMinutes = ($eh * 60) + $em;
        if ($endMinutes < $startMinutes) $endMinutes += 1440;
        $hoursUsed = round(($endMinutes - $startMinutes) / 60, 2);

// $manpowerCategories = DB::table('master_manpower')->whereIn('category', $request->category)->get();
        // if ($manpowerCategories->isEmpty()) throw new \Exception('No matching manpower categories found');
        // $rates = [];
        // foreach ($manpowerCategories as $cat) {
        //     $rates[strtolower(str_replace(' ', '_', $cat->category))] = $cat->rate;
        // }
     // Handle category processing - allow when category is not selected
        $manpowerCategories = collect();
        $rates = [];
        
        if (!empty($request->category) && is_array($request->category)) {
            $manpowerCategories = DB::table('master_manpower')->whereIn('category', $request->category)->get();
            if ($manpowerCategories->isEmpty()) throw new \Exception('No matching manpower categories found');
            
            foreach ($manpowerCategories as $cat) {
                $rates[strtolower(str_replace(' ', '_', $cat->category))] = $cat->rate;
            }
        }

$manpowerCosts = [
            'unskilled' => ($request->unskilled ?? 0) * ($rates['unskilled'] ?? 0),
            'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * ($rates['semi_skilled_1'] ?? 0),
            'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * ($rates['semi_skilled_2'] ?? 0),
        ];
        $totalManpowerCost = array_sum($manpowerCosts);

// Diesel FIFO
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->hsd_consumption ?? 0;
        $consumptionDetails = [];
        if ($remainingConsumption > 0) {
            $stocks = DB::table('diesel_stocks')->where('diesel_stock', '>', 0)->orderBy('date_of_purchase')->get();
            foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;
                $used = min($remainingConsumption, $stock->diesel_stock);
                DB::table('diesel_stocks')->where('id', $stock->id)->update([
                    'diesel_stock' => $stock->diesel_stock - $used,
                    'updated_at' => now()
                ]);
                $cost = $used * $stock->rate_per_liter;
                $dieselCost += $cost;
                $consumptionDetails[] = [
                    'stock_id' => $stock->id,
                    'liters_used' => $used,
                    'rate' => $stock->rate_per_liter,
                    'cost' => $cost
                ];
                if ($dieselRate == 0) $dieselRate = $stock->rate_per_liter;
                $remainingConsumption -= $used;
            }
            if ($remainingConsumption > 0) {
                DB::rollBack();
                return response()->json([
                    'status' => 'error',
                    'message' => 'Not enough diesel stock available',
                    'requested_liters' => $request->hsd_consumption,
                    'available_liters' => $request->hsd_consumption - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

// Spare parts
        $majorCostValue = 0;
        $majorMaintenanceContent = null;
        if ($request->has('spare_parts') && is_array($request->spare_parts)) {
            foreach ($request->spare_parts as $part) {
                $majorCostValue += (float) ($part['value'] ?? 0);
            }
            $majorMaintenanceContent = json_encode($request->spare_parts);
        } elseif ($request->filled('major_maintenance')) {
            $majorMaintenanceContent = $request->major_maintenance;
        }

// ✅ Updated Fertilizer Logic
        $fertilizerDetails = [];
        $fertilizerCost = 0;
        foreach ($request->fertilizer_ids as $fert) {
            $fertRow = DB::table('master_fertilizer')->where('id', $fert['id'])->first();
            if (!$fertRow) throw new \Exception("Fertilizer ID {$fert['id']} not found");

if ($fert['quantity'] > $fertRow->stock_kg) {
                DB::rollBack();
                return response()->json([
                    'error' => "Insufficient stock for Fertilizer ID {$fert['id']}",
                    'available_stock' => $fertRow->stock_kg,
                    'requested_quantity' => $fert['quantity']
                ], 422);
            }

$cost = $fert['quantity'] * $fertRow->rate;
            $fertilizerCost += $cost;
            $fertilizerDetails[] = [
                'id' => $fertRow->id,
                'name' => $fertRow->fertilizer_name,
                'type' => $fertRow->fertilizer_type,
                'quantity' => $fert['quantity'],
                'rate' => $fertRow->rate,
                'cost' => $cost
            ];

DB::table('master_fertilizer')->where('id', $fertRow->id)->update([
                'stock_kg' => DB::raw("stock_kg - {$fert['quantity']}"),
                'updated_at' => now()
            ]);

$totalQuantity += $fert['quantity'];
        }

$totalCost = round($fertilizerCost + $totalManpowerCost + $dieselCost + $majorCostValue, 2);

$fertilizerRecordId = DB::table('fertilizer_soil_record')->insertGetId([
            'block_name' => $blockName,
            'plot_name' => $plotName,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'fertilizer_id' => json_encode($request->fertilizer_ids),
            'activity_type' => $request->activity_type,
            'fertilizer_quantity' => $totalQuantity,
            'uom' => $request->uom,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'hsd_consumption' => $request->hsd_consumption,
            'date' => $request->date,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'hours_used' => $hoursUsed,
            // 'category_id' => implode(',', $request->category),
             'category_id' => (!empty($request->category) && is_array($request->category)) ? implode(',', $request->category) : null,
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->unskilled,
            'semi_skilled_1' => $request->semi_skilled_1,
            'semi_skilled_2' => $request->semi_skilled_2,
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'total_cost' => $totalCost,
            'user_id' => $userId,
            'site_id' => $siteName,
            'created_at' => now(),
            'updated_at' => now()
        ]);

DB::commit();

return response()->json([
            'success' => true,
            'message' => 'Fertilizer record created successfully',
            'data' => [
                'id' => $fertilizerRecordId,
                'fertilizer_details' => $fertilizerDetails,
                'cost_breakdown' => [
                    'fertilizer' => $fertilizerCost,
                    'manpower' => array_merge($manpowerCosts, ['total' => $totalManpowerCost]),
                    'diesel' => $request->hsd_consumption ? [
                        'consumption' => $request->hsd_consumption,
                        'rate_per_liter' => number_format($dieselRate, 2),
                        'cost' => number_format($dieselCost, 2),
                        'consumption_details' => $consumptionDetails,
                        'method' => 'FIFO'
                    ] : null,
                    'major_cost' => $majorCostValue,
                    'total_cost' => $totalCost
                ],
                'time_data' => [
                    'start_time' => $request->start_time,
                    'end_time' => $request->end_time,
                    'hours_used' => $hoursUsed
                ]
            ]
        ], 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'success' => false,
            'error' => 'Failed to create fertilizer record',
            'message' => $e->getMessage()
        ], 500);
    }
}

//inter culture

public function storeInterCulture(Request $request)
{
    DB::beginTransaction();
    try {
        $user = auth()->user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteName = $user->site_name;
        $userId = $user->id;
        if (!$siteName) {
            return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
        }

// Handle case when category is null or empty
        $manpowerCategories = collect();
        if ($request->has('category') && is_array($request->category) && !empty($request->category)) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $request->category)
                ->get();

if ($manpowerCategories->isEmpty()) {
                return response()->json([
                    'error' => 'No matching manpower categories found',
                    'valid_categories' => ['Unskilled Labor', 'Semi Skilled 1', 'Semi Skilled 2']
                ], 404);
            }
        }

$rates = [
            'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
            'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
            'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
        ];

$manpowerCosts = [
            'unskilled' => ($request->unskilled ?? 0) * $rates['unskilled'],
            'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $rates['semi_skilled_1'],
            'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $rates['semi_skilled_2'],
        ];

$manpowerTotal = array_sum($manpowerCosts);

// FIFO Diesel Consumption Logic
        $dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $request->hsd_consumption ?? 0;
        $consumptionDetails = [];

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;

$litersTaken = min($stock->diesel_stock, $remainingConsumption);

$dieselCost += $litersTaken * $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;

if ($dieselRate == 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }

// Spare parts handling
        $majorCostValue = 0;
        $majorMaintenanceContent = null;

if ($request->has('spare_parts') && is_array($request->spare_parts)) {
            foreach ($request->spare_parts as $part) {
                $majorCostValue += (float) ($part['value'] ?? 0);
            }
            $majorMaintenanceContent = json_encode($request->spare_parts);
        } elseif ($request->filled('major_maintenance')) {
            $majorMaintenanceContent = $request->major_maintenance;
        }

$totalCost = $manpowerTotal + $dieselCost + $majorCostValue;
        $categoryIds = $manpowerCategories->isNotEmpty() ? $manpowerCategories->pluck('id')->implode(',') : null;

$insertId = DB::table('inter_culture')->insertGetId([
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'machine_id' => $request->machine_ids ? implode(',', $request->machine_ids) : null,
            'tractor_id' => $request->tractor_ids ? implode(',', $request->tractor_ids) : null,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'hsd_consumption' => $request->hsd_consumption,
            'time_hrs' => $request->time_hrs,
            'start_time' => $request->start_time,
            'end_time' => $request->end_time,
            'category_id' => $categoryIds, // ✅ Will be null if no categories selected
            'manpower_type' => $request->manpower_type,
            'unskilled' => $request->unskilled,
            'semi_skilled_1' => $request->semi_skilled_1,
            'semi_skilled_2' => $request->semi_skilled_2,
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'user_id' => $userId,
            'site_id' => $siteName,
            'total_cost' => $totalCost,
            'activity_type' => $request->activity_type,
            'date' => $request->date,
            'created_at' => now(),
            'updated_at' => now()
        ]);

DB::commit();

return response()->json([
            'message' => 'Inter Culture record inserted successfully',
            'insert_id' => $insertId,
            'total_cost' => number_format($totalCost, 2),
            'cost_breakdown' => [
                'manpower' => [
                    'unskilled' => [
                        'quantity' => $request->unskilled ?? 0,
                        'rate' => number_format($rates['unskilled'], 2),
                        'cost' => number_format($manpowerCosts['unskilled'], 2)
                    ],
                    'semi_skilled_1' => [
                        'quantity' => $request->semi_skilled_1 ?? 0,
                        'rate' => number_format($rates['semi_skilled_1'], 2),
                        'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)
                    ],
                    'semi_skilled_2' => [
                        'quantity' => $request->semi_skilled_2 ?? 0,
                        'rate' => number_format($rates['semi_skilled_2'], 2),
                        'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)
                    ],
                    'total' => number_format($manpowerTotal, 2)
                ],
                'diesel' => [
                    'consumption' => $request->hsd_consumption ?? 0,
                    'rate_per_liter' => number_format($dieselRate, 2),
                    'cost' => number_format($dieselCost, 2)
                ]
            ],
            'manpower_rates_used' => $manpowerCategories->map(function ($item) {
                return [
                    'id' => $item->id,
                    'category' => $item->category,
                    'type' => $item->type,
                    'rate' => number_format($item->rate, 2)
                ];
            })
        ], 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'error' => 'Failed to create Inter Culture record',
            'message' => $e->getMessage()
        ], 500);
    }
}

//crop protrction
  
//  public function storeCropProtection(Request $request)
//     {
//         $user = auth()->user();
//         if (!$user) {
//             return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
//         }

//         $siteIdToStore = $user->site_id;

//         $validator = Validator::make($request->all(), [
//             'block_name' => 'required|string',
//             'plot_name' => 'required|string',
//             'area' => 'required|numeric',
//             'area_covered' => 'nullable|string',
//             'start_time' => 'nullable|date_format:H:i',
//             'end_time' => 'nullable|date_format:H:i',
//             'category' => 'required|array',
//             'date' => 'required|date',
//             'manpower_type' => 'nullable|string',
//             'unskilled' => 'nullable|integer|min:0',
//             'semi_skilled_1' => 'nullable|integer|min:0',
//             'semi_skilled_2' => 'nullable|integer|min:0',
//             'major_maintenance' => 'nullable|string',
//             'application_method' => 'required|string',
//             'application_stage_source' => 'required|string',
//             'chemical_ids' => 'required|array|min:1',
//             'chemical_ids.*.id' => 'required|integer|exists:master_chemical,id',
//             'chemical_ids.*.dose' => 'required|numeric|min:0',
//             'chemical_ids.*.uom' => 'required|string',
//             'chemical_ids.*.company' => 'required|string',
//             'user_id' => 'nullable|integer|exists:users,id',
//         ]);

//         if ($validator->fails()) {
//             return response()->json(['errors' => $validator->errors()], 422);
//         }

//         DB::beginTransaction();
//         try {
//             $userIdToStore = $user->id;
//             if (in_array($user->role, ['admin', 'manager'])) {
//                 if ($request->has('user_id') && $request->user_id !== null) {
//                     $userIdToStore = $request->user_id;
//                 } else {
//                     DB::rollBack();
//                     return response()->json(['error' => 'For admin/manager roles, user_id is required in the request body.'], 422);
//                 }
//             }

//             $manpowerCategories = DB::table('master_manpower')
//                 ->whereIn('category', $request->category)
//                 ->get();

//             $rates = [
//                 'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
//                 'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
//                 'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
//             ];

//             $manpowerCosts = [
//                 'unskilled' => ($request->unskilled ?? 0) * $rates['unskilled'],
//                 'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $rates['semi_skilled_1'],
//                 'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $rates['semi_skilled_2'],
//             ];

//             $manpowerTotal = array_sum($manpowerCosts);
//             $categoryIds = $manpowerCategories->pluck('id')->implode(',');

//             $totalChemicalCostOverall = 0;
//             $processedChemicalsDetails = [];

//             $chemicalIdsForDb = [];
//             $dosesForDb = [];
//             $uomsForDb = [];
//             $companiesForDb = [];

//             foreach ($request->chemical_ids as $chemicalData) {
//                 $chemical = DB::table('master_chemical')->find($chemicalData['id']);

//                 if (!$chemical) {
//                     DB::rollBack();
//                     return response()->json(['error' => "Selected chemical with ID {$chemicalData['id']} not found"], 404);
//                 }

//                 if ($chemical->stock_qty < $chemicalData['dose']) {
//                     DB::rollBack();
//                     return response()->json([
//                         'error' => "Insufficient stock for chemical: {$chemical->chemical_name} (ID: {$chemicalData['id']})",
//                         'chemical_id' => $chemicalData['id'],
//                         'chemical_name' => $chemical->chemical_name,
//                         'available_stock' => $chemical->stock_qty,
//                         'requested_dose' => $chemicalData['dose']
//                     ], 422);
//                 }

//                 $individualChemicalCost = $chemicalData['dose'] * $chemical->rate;
//                 $totalChemicalCostOverall += $individualChemicalCost;

//                 $processedChemicalsDetails[] = [
//                     'chemical_id' => $chemicalData['id'],
//                     'chemical_name' => $chemical->chemical_name ?? 'N/A',
//                     'company' => $chemicalData['company'],
//                     'active_ingredients' => $chemical->active_ingredients ?? null,
//                     'dose' => $chemicalData['dose'],
//                     'uom' => $chemicalData['uom'],
//                     'rate' => number_format($chemical->rate, 2),
//                     'cost' => number_format($individualChemicalCost, 2),
//                     'initial_stock' => number_format($chemical->stock_qty, 2)
//                 ];

//                 $chemicalIdsForDb[] = $chemicalData['id'];
//                 $dosesForDb[] = $chemicalData['dose'];
//                 $uomsForDb[] = $chemicalData['uom'];
//                 $companiesForDb[] = $chemicalData['company'];
//             }

//             $chemicalIdsString = implode(',', $chemicalIdsForDb);
//             $dosesString = implode(',', $dosesForDb);
//             $uomsString = implode(',', $uomsForDb);
//             $companiesString = implode(',', $companiesForDb);

//             $totalCostForSingleRow = $totalChemicalCostOverall + $manpowerTotal;

//             $insertId = DB::table('crop_protection')->insertGetId([
//                 'block_name' => $request->block_name,
//                 'plot_name' => $request->plot_name,
//                 'area' => $request->area,
//                 'area_covered' => $request->area_covered,
//                 'date' => $request->date,
//                 'start_time' => $request->start_time,
//                 'end_time' => $request->end_time,
//                 'category_id' => $categoryIds,
//                 'manpower_type' => $request->manpower_type,
//                 'unskilled' => $request->unskilled,
//                 'semi_skilled_1' => $request->semi_skilled_1,
//                 'semi_skilled_2' => $request->semi_skilled_2,
//                 'major_maintenance' => $request->major_maintenance,
//                 'application_method' => $request->application_method,
//                 'application_stage_source' => $request->application_stage_source,
//                 'chemical_id' => $chemicalIdsString,
//                 'dose' => $dosesString,
//                 'uom' => $uomsString,
//                 'company' => $companiesString,
//                 'site_id' => $siteIdToStore,
//                 'user_id' => $userIdToStore,
//                 'major_cost' => $request->major_maintenance ? (float)$request->major_maintenance : 0,
//                 'total_cost' => $totalCostForSingleRow,
//                 'created_at' => now(),
//                 'updated_at' => now()
//             ]);

//             foreach ($request->chemical_ids as $index => $chemicalData) {
//                 $chemical = DB::table('master_chemical')->find($chemicalData['id']);
//                 if ($chemical) {
//                     DB::table('master_chemical')
//                         ->where('id', $chemicalData['id'])
//                         ->decrement('stock_qty', $chemicalData['dose']);
                    
//                     $updatedChemical = DB::table('master_chemical')->find($chemicalData['id']);
//                     $processedChemicalsDetails[$index]['remaining_stock'] = number_format($updatedChemical->stock_qty, 2);
//                 }
//             }

//             DB::commit();

//             return response()->json([
//                 'message' => 'Crop Protection record inserted successfully (single entry with comma-separated chemical data)',
//                 'insert_id' => $insertId,
//                 'grand_total_operation_cost' => number_format($totalCostForSingleRow, 2),
//                 'cost_breakdown' => [
//                     'manpower' => [
//                         'unskilled' => [
//                             'quantity' => $request->unskilled ?? 0,
//                             'rate' => number_format($rates['unskilled'], 2),
//                             'cost' => number_format($manpowerCosts['unskilled'], 2)
//                         ],
//                         'semi_skilled_1' => [
//                             'quantity' => $request->semi_skilled_1 ?? 0,
//                             'rate' => number_format($rates['semi_skilled_1'], 2),
//                             'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)
//                         ],
//                         'semi_skilled_2' => [
//                             'quantity' => $request->semi_skilled_2 ?? 0,
//                             'rate' => number_format($rates['semi_skilled_2'], 2),
//                             'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)
//                         ],
//                         'total' => number_format($manpowerTotal, 2)
//                     ],
//                     'chemicals' => $processedChemicalsDetails,
//                     'total_chemical_cost' => number_format($totalChemicalCostOverall, 2),
//                     'application_stage_source' => $request->application_stage_source
//                 ],
//                 'manpower_rates_used' => $manpowerCategories->map(function ($item) {
//                     return [
//                         'id' => $item->id,
//                         'category' => $item->category,
//                         'type' => $item->type,
//                         'rate' => number_format($item->rate, 2)
//                     ];
//                 })
//             ], 201);

//         } catch (\Illuminate\Validation\ValidationException $e) {
//             DB::rollBack();
//             return response()->json([
//                 'error' => 'Validation Failed',
//                 'messages' => $e->errors()
//             ], 422);
//         } catch (Exception $e) {
//             DB::rollBack();
//             return response()->json([
//                 'error' => 'Failed to create Crop Protection record(s)',
//                 'message' => $e->getMessage(),
//             ], 500);
//         }
//     }
public function storeCropProtection(Request $request)
    {
        $user = auth()->user();
        if (!$user) {
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteIdToStore = $user->site_id;

// 1. Validation rules
        $validator = Validator::make($request->all(), [
            'block_name' => 'required|string',
            'plot_name' => 'required|string',
            'area' => 'required|numeric',
            'area_covered' => 'nullable|string',
            'start_time' => 'nullable|date_format:H:i',
            'end_time' => 'nullable|date_format:H:i',
            'category' => 'nullable|array',
            'date' => 'required|date',
            'manpower_type' => 'nullable|string',
            'unskilled' => 'nullable|integer|min:0',
            'semi_skilled_1' => 'nullable|integer|min:0',
            'semi_skilled_2' => 'nullable|integer|min:0',
            'major_maintenance' => 'nullable|string', // Kept for existing structure, though spare_parts is preferred
            'application_method' => 'required|string',
            'application_stage_source' => 'required|string',
            'chemical_ids' => 'required|array|min:1',
            'chemical_ids.*.id' => 'required|integer|exists:master_chemical,id',
            'chemical_ids.*.dose' => 'required|numeric|min:0',
            'chemical_ids.*.uom' => 'required|string',
            'chemical_ids.*.company' => 'required|string',
            'user_id' => 'nullable|integer|exists:users,id',
            // Validation rules for machine, tractor, and HSD consumption (HSD related removed as requested)
            'machine_ids' => 'nullable|array',
            'machine_ids.*' => 'string|max:20',
            'tractor_ids' => 'nullable|array',
            'tractor_ids.*' => 'string|max:20',
            'hsd_consumption' => 'nullable|numeric|min:0', // Keeping hsd_consumption validation if it's passed but not saved/calculated
            'spare_parts' => 'nullable|array', // Validation for spare_parts (major maintenance)
            'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
            'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
        ]);

if ($validator->fails()) {
            return response()->json(['errors' => $validator->errors()], 422);
        }

DB::beginTransaction();
        try {
            $userIdToStore = $user->id;
            // Logic for admin/manager role to specify user_id
            if (in_array($user->role, ['admin', 'manager'])) {
                if ($request->has('user_id') && $request->user_id !== null) {
                    $userIdToStore = $request->user_id;
                } else {
                    DB::rollBack();
                    return response()->json(['error' => 'For admin/manager roles, user_id is required in the request body.'], 422);
                }
            }

///old
// $manpowerCategories = DB::table('master_manpower')
//     ->whereIn('category', $request->category)
//     ->get();

//new
            // Manpower cost calculation
          $manpowerCategories = collect(); // Initialize as empty collection
            if ($request->has('category') && is_array($request->category) && !empty($request->category)) {
                $manpowerCategories = DB::table('master_manpower')
                    ->whereIn('category', $request->category)
                    ->get();
            }

$rates = [
                'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
                'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
                'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
            ];

$manpowerCosts = [
                'unskilled' => ($request->unskilled ?? 0) * $rates['unskilled'],
                'semi_skilled_1' => ($request->semi_skilled_1 ?? 0) * $rates['semi_skilled_1'],
                'semi_skilled_2' => ($request->semi_skilled_2 ?? 0) * $rates['semi_skilled_2'],
            ];

$manpowerTotal = array_sum($manpowerCosts);
            $categoryIds = $manpowerCategories->pluck('id')->implode(',');

// Chemical cost calculation and stock management
            $totalChemicalCostOverall = 0;
            $processedChemicalsDetails = [];

$chemicalIdsForDb = [];
            $dosesForDb = [];
            $uomsForDb = [];
            $companiesForDb = [];

foreach ($request->chemical_ids as $chemicalData) {
                $chemical = DB::table('master_chemical')->find($chemicalData['id']);

if (!$chemical) {
                    DB::rollBack();
                    return response()->json(['error' => "Selected chemical with ID {$chemicalData['id']} not found"], 404);
                }

if ($chemical->stock_qty < $chemicalData['dose']) {
                    DB::rollBack();
                    return response()->json([
                        'error' => "Insufficient stock for chemical: {$chemical->chemical_name} (ID: {$chemicalData['id']})",
                        'chemical_id' => $chemicalData['id'],
                        'chemical_name' => $chemical->chemical_name,
                        'available_stock' => $chemical->stock_qty,
                        'requested_dose' => $chemicalData['dose']
                    ], 422);
                }

$individualChemicalCost = $chemicalData['dose'] * $chemical->rate;
                $totalChemicalCostOverall += $individualChemicalCost;

$processedChemicalsDetails[] = [
                    'chemical_id' => $chemicalData['id'],
                    'chemical_name' => $chemical->chemical_name ?? 'N/A',
                    'company' => $chemicalData['company'],
                    'active_ingredients' => $chemical->active_ingredients ?? null,
                    'dose' => $chemicalData['dose'],
                    'uom' => $chemicalData['uom'],
                    'rate' => number_format($chemical->rate, 2),
                    'cost' => number_format($individualChemicalCost, 2),
                    'initial_stock' => number_format($chemical->stock_qty, 2)
                ];

$chemicalIdsForDb[] = $chemicalData['id'];
                $dosesForDb[] = $chemicalData['dose'];
                $uomsForDb[] = $chemicalData['uom'];
                $companiesForDb[] = $chemicalData['company'];
            }

$chemicalIdsString = implode(',', $chemicalIdsForDb);
            $dosesString = implode(',', $dosesForDb);
            $uomsString = implode(',', $uomsForDb);
            $companiesString = implode(',', $companiesForDb);

// Calculate hours used
            $hours_used = null;
            if ($request->filled('start_time') && $request->filled('end_time') && $request->filled('date')) {
                try {
                    $date = Carbon::parse($request->date);
                    $startTimeString = $date->format('Y-m-d') . ' ' . $request->start_time;
                    $endTimeString = $date->format('Y-m-d') . ' ' . $request->end_time;

$startTimestamp = Carbon::createFromFormat('Y-m-d H:i', $startTimeString);
                    $endTimestamp = Carbon::createFromFormat('Y-m-d H:i', $endTimeString);

if ($endTimestamp->lt($startTimestamp)) {
                        $endTimestamp->addDay();
                    }

$hours_used = round($startTimestamp->diffInSeconds($endTimestamp) / 3600, 2);
                } catch (\Exception $e) {
                    Log::error("Error calculating hours_used in CropProtection: " . $e->getMessage());
                }
            }

// Diesel/HSD cost calculation and related logic removed as requested.
            // Keeping hsd_consumption as a field in the table if it's still needed,
            // but its cost calculation and stock deduction will not be performed here.
            $hsdConsumptionValue = $request->input('hsd_consumption'); // Just get the value if present, not used for cost calculation

// Spare parts logic (Major Maintenance)
            $majorMaintenanceData = null;
            $majorCost = 0;
            $processedSpareParts = [];

if ($request->has('spare_parts') && is_array($request->spare_parts)) {
                $sparePartsInput = $request->spare_parts;
                $majorMaintenanceData = json_encode($sparePartsInput);

foreach ($sparePartsInput as $part) {
                    if (isset($part['value']) && is_numeric($part['value'])) {
                        $majorCost += (float)$part['value'];
                    }
                    if (isset($part['spare_part']) && isset($part['value'])) {
                        $processedSpareParts[] = [
                            'item' => $part['spare_part'],
                            'cost' => (float)$part['value']
                        ];
                    }
                }
            } else if ($request->filled('major_maintenance')) {
                // If major_maintenance is a string, assume it's a direct cost
                $majorCost = (float)$request->major_maintenance;
                $majorMaintenanceData = json_encode(['description' => $request->major_maintenance, 'value' => $majorCost]);
            }
            // End of Spare parts logic

// Implode machine and tractor IDs if they exist
            $machineIdsString = $request->has('machine_ids') && is_array($request->machine_ids) ? implode(',', $request->machine_ids) : null;
            $tractorIdsString = $request->has('tractor_ids') && is_array($request->tractor_ids) ? implode(',', $request->tractor_ids) : null;

// Total cost calculation including manpower, chemicals, and major maintenance (diesel cost removed)
            $totalCostForSingleRow = $manpowerTotal + $totalChemicalCostOverall + $majorCost;

// Insert into crop_protection table
            $insertId = DB::table('crop_protection')->insertGetId([
                'block_name' => $request->block_name,
                'plot_name' => $request->plot_name,
                'area' => $request->area,
                'area_covered' => $request->area_covered,
                'date' => $request->date,
                'start_time' => $request->start_time,
                'end_time' => $request->end_time,
                'hours_used' => $hours_used,
                'category_id' => $categoryIds,
                'manpower_type' => $request->manpower_type,
                'unskilled' => $request->unskilled,
                'semi_skilled_1' => $request->semi_skilled_1,
                'semi_skilled_2' => $request->semi_skilled_2,
                'major_maintenance' => $majorMaintenanceData,
                'major_cost' => $majorCost,
                'application_method' => $request->application_method,
                'application_stage_source' => $request->application_stage_source,
                'chemical_id' => $chemicalIdsString,
                'dose' => $dosesString,
                'uom' => $uomsString,
                'company' => $companiesString,
                'site_id' => $siteIdToStore,
                'user_id' => $userIdToStore,
                'machine_id' => $machineIdsString,
                'tractor_id' => $tractorIdsString,
                'hsd_consumption' => $hsdConsumptionValue, // Storing HSD consumption value directly without cost/stock logic
                // Removed 'diesel_cost', 'diesel_rate', 'diesel_consumption_details' from here
                'total_cost' => $totalCostForSingleRow,
                'created_at' => now(),
                'updated_at' => now()
            ]);

// Decrement chemical stock after successful insertion
            foreach ($request->chemical_ids as $index => $chemicalData) {
                $chemical = DB::table('master_chemical')->find($chemicalData['id']);
                if ($chemical) {
                    DB::table('master_chemical')
                        ->where('id', $chemicalData['id'])
                        ->decrement('stock_qty', $chemicalData['dose']);
                    
                    // Update remaining stock in the response details
                    $updatedChemical = DB::table('master_chemical')->find($chemicalData['id']);
                    $processedChemicalsDetails[$index]['remaining_stock'] = number_format($updatedChemical->stock_qty, 2);
                }
            }

DB::commit();

// Return success response with detailed breakdown
            return response()->json([
                'message' => 'Crop Protection record inserted successfully',
                'insert_id' => $insertId,
                'grand_total_operation_cost' => number_format($totalCostForSingleRow, 2),
                'cost_breakdown' => [
                    'manpower' => [
                        'unskilled' => [
                            'quantity' => $request->unskilled ?? 0,
                            'rate' => number_format($rates['unskilled'], 2),
                            'cost' => number_format($manpowerCosts['unskilled'], 2)
                        ],
                        'semi_skilled_1' => [
                            'quantity' => $request->semi_skilled_1 ?? 0,
                            'rate' => number_format($rates['semi_skilled_1'], 2),
                            'cost' => number_format($manpowerCosts['semi_skilled_1'], 2)
                        ],
                        'semi_skilled_2' => [
                            'quantity' => $request->semi_skilled_2 ?? 0,
                            'rate' => number_format($rates['semi_skilled_2'], 2),
                            'cost' => number_format($manpowerCosts['semi_skilled_2'], 2)
                        ],
                        'total' => number_format($manpowerTotal, 2)
                    ],
                    'chemicals' => $processedChemicalsDetails,
                    'total_chemical_cost' => number_format($totalChemicalCostOverall, 2),
                    'application_stage_source' => $request->application_stage_source,
                    // Removed 'diesel' breakdown from here
                    'major_maintenance' => [
                        'details' => $processedSpareParts,
                        'total_cost' => number_format($majorCost, 2)
                    ],
                ],
                'machine_ids' => $request->machine_ids,
                'tractor_ids' => $request->tractor_ids,
                'hours_used' => $hours_used,
                'hsd_consumption' => $hsdConsumptionValue, // Include hsd_consumption in response if needed
                'manpower_rates_used' => $manpowerCategories->map(function ($item) {
                    return [
                        'id' => $item->id,
                        'category' => $item->category,
                        'type' => $item->type,
                        'rate' => number_format($item->rate, 2)
                    ];
                })
            ], 201);

} catch (\Illuminate\Validation\ValidationException $e) {
            DB::rollBack();
            Log::error("Crop Protection Validation Error: " . $e->getMessage() . "\n" . json_encode($e->errors()));
            return response()->json([
                'error' => 'Validation Failed',
                'messages' => $e->errors()
            ], 422);
        } catch (\Exception $e) {
            DB::rollBack();
            Log::error("Crop Protection Error: " . $e->getMessage() . "\n" . $e->getTraceAsString());
            return response()->json([
                'error' => 'Failed to create Crop Protection record(s)',
                'message' => $e->getMessage(),
            ], 500);
        }
    }

//storeActivityMonitoring
    
public function storeActivityMonitoring(Request $request)
{
    $authUser = auth()->user();

switch ($authUser->role) {
        case 'admin':
        case 'manager':
            if (!$request->has('user_id')) {
                return response()->json([
                    'error' => 'user_id is required for role: ' . $authUser->role
                ], 422);
            }
            $userIdToStore = $request->user_id;
            break;

case 'field_user':
        default:
            $userIdToStore = $authUser->id;
            break;
    }

try {
        $imagePath = null;

if ($request->hasFile('image_path')) {
            $image = $request->file('image_path');
            $filename = time() . '_' . $image->getClientOriginalName();
            $image->move(public_path('activity_monitoring'), $filename);
            $imagePath = 'activity_monitoring/' . $filename;
        }

$data = [
            'block_name' => $request->block_name,
            'plot_name' => $request->plot_name,
            'area' => $request->area,
            'area_covered' => $request->area_covered,
            'activity_stage' => $request->activity_stage,
            'leaf_condition' => $request->leaf_condition,
            'image_path' => $imagePath,
            'user_id' => $userIdToStore,
            'created_at' => now(),
            'updated_at' => now()
        ];

$id = DB::table('activity_monitoring')->insertGetId($data);

$insertedData = DB::table('activity_monitoring')->where('id', $id)->first();

return response()->json([
            'message' => 'Activity monitoring data stored successfully',
            'data' => $insertedData
        ], 201);

} catch (\Exception $e) {
        return response()->json([
            'error' => 'Failed to store activity monitoring data',
            'message' => $e->getMessage()
        ], 500);
    }
}

//checkFodderCrop

//old
// public function checkFodderCrop(Request $request)
// {
//     try {
     
//         // Get plot data along with seed name from master_seed
//         $plots = DB::table('showing_oprations as so')
//             ->leftJoin('master_seed as ms', 'so.seed_id', '=', 'ms.id')
//             ->select(
//                 'so.block_name',
//                 'so.plot_name',
//                 DB::raw('so.area as area'),
//                 DB::raw('CASE WHEN so.purpose_name = "Fodder Crop" THEN true ELSE false END as is_fodder_crop'),
//                 'ms.seed_name'
//             )
//             ->get();

//         if ($plots->isEmpty()) {
//             return response()->json([
//                 'success' => false,
//                 'message' => 'No plot data found'
//             ], 404);
//         }

//         // Group the data by block_name
//         $grouped = [];

//         foreach ($plots as $plot) {
//             $blockName = $plot->block_name;

//             if (!isset($grouped[$blockName])) {
//                 $grouped[$blockName] = [
//                     'block_name' => $blockName,
//                     'plots' => []
//                 ];
//             }

//             $grouped[$blockName]['plots'][] = [
//                 'plot_name' => $plot->plot_name,
//                 'area' => $plot->area,
//                 'is_fodder_crop' => $plot->is_fodder_crop,
//                 'seed_name' => $plot->seed_name // ✅ Added seed name
//             ];
//         }

//         return response()->json([
//             'success' => true,
//             'data' => array_values($grouped)
//         ]);

//     } catch (\Exception $e) {
//         return response()->json([
//             'success' => false,
//             'message' => 'Error fetching plot information: ' . $e->getMessage(),
//             'details' => [
//                 'table' => 'showing_oprations',
//                 'columns_used' => [
//                     'block_name',
//                     'plot_name',
//                     'area',
//                     'purpose_name',
//                     'seed_id'
//                 ],
//                 'joined_table' => 'master_seed',
//                 'advice' => 'Ensure seed_id exists in showing_oprations and matches id in master_seed'
//             ]
//         ], 500);
//     }
// }
public function checkFodderCrop(Request $request)
{
    try {
        $user = Auth::user();
        if (!$user) {
            return response()->json([
                'success' => false,
                'message' => 'User not authenticated.'
            ], 401);
        }

// ✅ Get plot data ONLY for logged-in user
        $plots = DB::table('showing_oprations as so')
            ->leftJoin('master_seed as ms', 'so.seed_id', '=', 'ms.id')
            ->select(
                'so.block_name',
                'so.plot_name',
                DB::raw('so.area as area'),
                DB::raw('CASE WHEN so.purpose_name = "Fodder Crop" THEN true ELSE false END as is_fodder_crop'),
                'ms.seed_name'
            )
            ->where('so.user_id', $user->id)
            ->get();

// ✅ If no data found, show message: sowing not done yet
        if ($plots->isEmpty()) {
            return response()->json([
                'success' => false,
                'message' => 'No sowing data found. Please complete sowing before performing harvest.'
            ], 404);
        }

// ✅ Group plot data by block
        $grouped = [];
        foreach ($plots as $plot) {
            $blockName = $plot->block_name;

if (!isset($grouped[$blockName])) {
                $grouped[$blockName] = [
                    'block_name' => $blockName,
                    'plots' => []
                ];
            }

$grouped[$blockName]['plots'][] = [
                'plot_name' => $plot->plot_name,
                'area' => $plot->area,
                'is_fodder_crop' => $plot->is_fodder_crop,
                'seed_name' => $plot->seed_name
            ];
        }

// ✅ 1. Get all distinct seed names for A + A1 (example logic)
        $seedData = DB::table('showing_oprations as so')
            ->leftJoin('master_seed as ms', 'so.seed_id', '=', 'ms.id')
            ->select('ms.seed_name')
            ->where('so.block_name', 'A')
            ->where('so.plot_name', 'A1')
            ->groupBy('ms.seed_name')
            ->get();

$seedNames = [];
        foreach ($seedData as $seed) {
            $seedNames[] = $seed->seed_name;
        }

// ✅ 2. Get latest inserted seed_name (highest id) for A + A1
        $latestSeedRow = DB::table('showing_oprations as so')
            ->leftJoin('master_seed as ms', 'so.seed_id', '=', 'ms.id')
            ->select('ms.seed_name')
            ->where('so.block_name', 'A')
            ->where('so.plot_name', 'A1')
            ->orderByDesc('so.id')
            ->first();

$latestSeed = $latestSeedRow ? $latestSeedRow->seed_name : null;

// ✅ Final response
        return response()->json([
            'success' => true,
            'data' => array_values($grouped),
            'seeds' => $seedNames,
            'selected_seed_name' => $latestSeed
        ]);

} catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error fetching plot information: ' . $e->getMessage()
        ], 500);
    }
}

//harvest update

// public function storeHarvestUpdate(Request $request)
// {
   
//     $validatedData = $request->validate([
//         'block_name' => 'required|string|max:255',
//         'plot_name' => 'required|string|max:255',
//         'harvest_method' => 'required|string|max:255',
//         'harvest_purpose' => 'nullable|string|max:255',
//         'machine_ids' => 'nullable|array',
//         'tractor_ids' => 'nullable|array',
//         'area' => 'nullable|numeric|min:0',
//         'area_covered' => 'nullable|numeric|min:0',
//         'seed_name' => 'nullable|string|max:255',
//         'yield_mt' => 'required|numeric|min:0',
//         'hsd_consumption' => 'nullable|numeric|min:0',
//         'start_time' => 'required|date_format:H:i',
//         'end_time' => 'required|date_format:H:i|after_or_equal:start_time',
//         'category' => 'required|array',
//         'category.*' => 'string|max:255|exists:master_manpower,category',
//         'date' => 'required|date',
//         'manpower_type' => 'nullable|string|max:255',
//         'unskilled' => 'nullable|integer|min:0',
//         'semi_skilled_1' => 'nullable|integer|min:0',
//         'semi_skilled_2' => 'nullable|integer|min:0',
//         'spare_parts' => 'nullable|array',
//         'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
//         'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
//         'major_maintenance' => 'nullable|string',
//     ]);

//     DB::beginTransaction();

//     try {
//         $user = auth()->user();
//         if (!$user) {
//             DB::rollBack();
//             return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
//         }

//         $siteId = $user->site_id;
//         $siteName = $user->site_name;
//         $userId = $user->id;

//         if (!$siteName) {
//             DB::rollBack();
//             return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
//         }

//         $showing = DB::table('showing_oprations as s')
//             ->leftJoin('master_seed as sd', 's.seed_id', '=', 'sd.id')
//             ->where('s.block_name', $validatedData['block_name'])
//             ->where('s.plot_name', $validatedData['plot_name'])
//             ->orderByDesc('s.id')
//             ->first(['s.id', 's.seed_id', 'sd.seed_name as seed_name']);

//         $manpowerCategories = DB::table('master_manpower')
//             ->whereIn('category', $validatedData['category'])
//             ->get();

//         if ($manpowerCategories->isEmpty() && !empty($validatedData['category'])) {
//             DB::rollBack();
//             $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
//             return response()->json([
//                 'status' => 'error',
//                 'error' => 'INVALID_CATEGORIES',
//                 'message' => 'No matching manpower categories found for the provided input.',
//                 'valid_categories' => $validCategories,
//                 'received_categories' => $validatedData['category']
//             ], 400);
//         }

//         $startDateTime = Carbon::createFromFormat('Y-m-d H:i', $validatedData['date'] . ' ' . $validatedData['start_time']);
//         $endDateTime = Carbon::createFromFormat('Y-m-d H:i', $validatedData['date'] . ' ' . $validatedData['end_time']);
//         if ($endDateTime->lessThan($startDateTime)) {
//             $endDateTime->addDay();
//         }
//         $hours_used = round($startDateTime->floatDiffInHours($endDateTime), 2);

//         $rates = [
//             'unskilled' => $manpowerCategories->where('category', 'Unskilled')->first()->rate ?? 0,
//             'semi_skilled_1' => $manpowerCategories->where('category', 'Semi Skilled 1')->first()->rate ?? 0,
//             'semi_skilled_2' => $manpowerCategories->where('category', 'Semi Skilled 2')->first()->rate ?? 0,
//         ];

//         $manpowerCosts = [
//             'unskilled' => ($validatedData['unskilled'] ?? 0) * $rates['unskilled'],
//             'semi_skilled_1' => ($validatedData['semi_skilled_1'] ?? 0) * $rates['semi_skilled_1'],
//             'semi_skilled_2' => ($validatedData['semi_skilled_2'] ?? 0) * $rates['semi_skilled_2'],
//         ];
//         $manpowerTotal = array_sum($manpowerCosts);

//         $dieselCost = 0;
//         $dieselRate = 0;
//         $remainingConsumption = $validatedData['hsd_consumption'] ?? 0;
//         $consumptionDetails = [];

//         if ($remainingConsumption > 0) {
//             $stocks = DB::table('diesel_stocks')->where('diesel_stock', '>', 0)->orderBy('date_of_purchase')->get();

//             foreach ($stocks as $stock) {
//                 if ($remainingConsumption <= 0) break;

//                 $litersTaken = min($stock->diesel_stock, $remainingConsumption);

//                 DB::table('diesel_stocks')->where('id', $stock->id)->update([
//                     'diesel_stock' => $stock->diesel_stock - $litersTaken,
//                     'updated_at' => now()
//                 ]);

//                 $dieselCost += $litersTaken * $stock->rate_per_liter;
//                 $dieselRate = $dieselRate ?: $stock->rate_per_liter;
//                 $remainingConsumption -= $litersTaken;
//                 $consumptionDetails[] = [
//                     'stock_id' => $stock->id,
//                     'liters_used' => $litersTaken,
//                     'rate' => $stock->rate_per_liter,
//                     'cost' => $litersTaken * $stock->rate_per_liter
//                 ];
//             }

//             if ($remainingConsumption > 0) {
//                 DB::rollBack();
//                 return response()->json([
//                     'status' => 'error',
//                     'error' => 'INSUFFICIENT_DIESEL',
//                     'message' => 'Not enough diesel stock available',
//                     'requested_liters' => $validatedData['hsd_consumption'],
//                     'available_liters' => $validatedData['hsd_consumption'] - $remainingConsumption,
//                     'shortage' => $remainingConsumption,
//                     'consumption_details' => $consumptionDetails
//                 ], 400);
//             }
//         }

//         $majorCostValue = 0;
//         $majorMaintenanceContent = null;
//         if (!empty($validatedData['spare_parts'])) {
//             foreach ($validatedData['spare_parts'] as $part) {
//                 $majorCostValue += (float) ($part['value'] ?? 0);
//             }
//             $majorMaintenanceContent = json_encode($validatedData['spare_parts']);
//         } elseif (!empty($validatedData['major_maintenance'])) {
//             $majorMaintenanceContent = $validatedData['major_maintenance'];
//         }

//         $totalCost = $manpowerTotal + $dieselCost + $majorCostValue;

//         // Insert into harvesting_update
//         $recordId = DB::table('harvesting_update')->insertGetId([
//             'block_name' => $validatedData['block_name'],
//             'plot_name' => $validatedData['plot_name'],
//             'area' => $validatedData['area'],
//             'area_covered' => $validatedData['area_covered'],
//             'seed_name' => $showing->seed_name ?? null,
//             'seed_id' => $showing->seed_id ?? null,
//             'harvest_method' => $validatedData['harvest_method'],
//             'harvest_purpose' => $validatedData['harvest_purpose'],
//             'machine_id' => !empty($validatedData['machine_ids']) ? implode(',', $validatedData['machine_ids']) : null,
//             'tractor_id' => !empty($validatedData['tractor_ids']) ? implode(',', $validatedData['tractor_ids']) : null,
//             'yield_mt' => $validatedData['yield_mt'],
//             'hsd_consumption' => $validatedData['hsd_consumption'],
//             'start_time' => $validatedData['start_time'],
//             'end_time' => $validatedData['end_time'],
//             'hours_used' => $hours_used,
//             'date' => $validatedData['date'],
//             'category_id' => $manpowerCategories->pluck('id')->implode(','),
//             'manpower_type' => $validatedData['manpower_type'] ?? null,
//             'unskilled' => $validatedData['unskilled'] ?? null,
//             'semi_skilled_1' => $validatedData['semi_skilled_1'] ?? null,
//             'semi_skilled_2' => $validatedData['semi_skilled_2'] ?? null,
//             'major_maintenance' => $majorMaintenanceContent,
//             'major_cost' => $majorCostValue,
//             'user_id' => $userId,
//             'site_id' => $siteName,
//             'total_cost' => $totalCost,
//             'created_at' => now(),
//             'updated_at' => now()
//         ]);

//         // ✅ Update or insert into harvest_store_manage
//         // Modified logic: Only match by seed_name and site_id (not date, block/plot specific)
//         $existingHarvest = DB::table('harvest_store_manage')
//             ->where('seed_name', $showing->seed_name ?? null)
//             ->where('site_id', $siteId)
//             ->first();

//         if ($existingHarvest) {
//             // Update existing record - add yield_mt to existing values
//             DB::table('harvest_store_manage')
//                 ->where('id', $existingHarvest->id)
//                 ->update([
//                     'yield_mt' => DB::raw("yield_mt + {$validatedData['yield_mt']}"),
//                     'total_mt' => DB::raw("total_mt + {$validatedData['yield_mt']}"),
//                     'harvest_id' => $recordId, // Update with latest harvest_id
//                     'updated_at' => now()
//                 ]);
//         } else {
//             // Create new record only if no existing record for this seed_name on this date
//             DB::table('harvest_store_manage')->insert([
//                 'product_id' => 1,
//                 'site_id' => $siteId,
//                 'harvest_id' => $recordId,
//                 'silage_id' => null,
//                 'hey_id' => null,
//                 'block_name' => $validatedData['block_name'],
//                 'plot_name' => $validatedData['plot_name'],
//                 'seed_name' => $showing->seed_name ?? null,
//                 'seed_id' => $showing->seed_id ?? null,
//                 'date' => $validatedData['date'],
//                 'yield_mt' => $validatedData['yield_mt'],
//                 'total_mt' => $validatedData['yield_mt'],
//                 'mt_price' => null,
//                 'sale_price' => null,
//                 'quantity' => null,
//                 'created_at' => now(),
//                 'updated_at' => now()
//             ]);
//         }

//         DB::commit();

//         return response()->json([
//             'status' => 'success',
//             'message' => 'Harvest data stored and store updated successfully.',
//             'harvest_id' => $recordId
//         ], 201);
//     } catch (ValidationException $e) {
//         DB::rollBack();
//         return response()->json(['status' => 'error', 'message' => $e->getMessage()], 422);
//     } catch (\Exception $e) {
//         DB::rollBack();
//         return response()->json(['status' => 'error', 'message' => 'Server error', 'error' => $e->getMessage()], 500);
//     }
// }
public function storeHarvestUpdate(Request $request)
{
   
    $validatedData = $request->validate([
        'block_name' => 'required|string|max:255',
        'plot_name' => 'required|string|max:255',
        'harvest_method' => 'required|string|max:255',
        'harvest_purpose' => 'nullable|string|max:255',
        'machine_ids' => 'nullable|array',
        'tractor_ids' => 'nullable|array',
        'area' => 'nullable|numeric|min:0',
        'area_covered' => 'nullable|numeric|min:0',
        'seed_name' => 'required|string|max:255', // Made required to get selected seed from frontend
        'yield_mt' => 'required|numeric|min:0',
        'hsd_consumption' => 'nullable|numeric|min:0',
        'start_time' => 'required|date_format:H:i',
        'end_time' => 'required|date_format:H:i|after_or_equal:start_time',
        'category' => 'nullable|array',
        'category.*' => 'string|max:255|exists:master_manpower,category',
        'date' => 'required|date',
        'manpower_type' => 'nullable|string|max:255',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'spare_parts' => 'nullable|array',
        'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
        'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
        'major_maintenance' => 'nullable|string',
    ]);

DB::beginTransaction();

try {
        $user = auth()->user();
        if (!$user) {
            DB::rollBack();
            return response()->json(['status' => 'error', 'message' => 'User not authenticated.'], 401);
        }

$siteId = $user->site_id;
        $siteName = $user->site_name;
        $userId = $user->id;

if (!$siteName) {
            DB::rollBack();
            return response()->json(['status' => 'error', 'message' => 'Site name not found for this user.'], 404);
        }

// ✅ Updated logic: Get seed_id based on selected seed_name, block_name, and plot_name
        $showing = DB::table('showing_oprations as s')
            ->leftJoin('master_seed as sd', 's.seed_id', '=', 'sd.id')
            ->where('s.block_name', $validatedData['block_name'])
            ->where('s.plot_name', $validatedData['plot_name'])
            ->where('sd.seed_name', $validatedData['seed_name']) // Match with selected seed_name
            ->orderByDesc('s.id')
            ->first(['s.id', 's.seed_id', 'sd.seed_name as seed_name']);

// ✅ Validate if the selected seed exists for the given block and plot
        if (!$showing) {
            DB::rollBack();
            return response()->json([
                'status' => 'error', 
                'message' => 'Selected seed not found for the specified block and plot combination.'
            ], 404);
        }

// $manpowerCategories = DB::table('master_manpower')
        //     ->whereIn('category', $validatedData['category'])
        //     ->get();
        $manpowerCategories = collect(); // Initialize as empty collection
        if (!empty($validatedData['category'])) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $validatedData['category'])
                ->get();
        }

if ($manpowerCategories->isEmpty() && !empty($validatedData['category'])) {
            DB::rollBack();
            $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
            return response()->json([
                'status' => 'error',
                'error' => 'INVALID_CATEGORIES',
                'message' => 'No matching manpower categories found for the provided input.',
                'valid_categories' => $validCategories,
                'received_categories' => $validatedData['category']
            ], 400);
        }

$startDateTime = Carbon::createFromFormat('Y-m-d H:i', $validatedData['date'] . ' ' . $validatedData['start_time']);
        $endDateTime = Carbon::createFromFormat('Y-m-d H:i', $validatedData['date'] . ' ' . $validatedData['end_time']);
        if ($endDateTime->lessThan($startDateTime)) {
            $endDateTime->addDay();
        }
        $hours_used = round($startDateTime->floatDiffInHours($endDateTime), 2);

$rates = [
            'unskilled' => $manpowerCategories->where('category', 'Unskilled')->first()->rate ?? 0,
            'semi_skilled_1' => $manpowerCategories->where('category', 'Semi Skilled 1')->first()->rate ?? 0,
            'semi_skilled_2' => $manpowerCategories->where('category', 'Semi Skilled 2')->first()->rate ?? 0,
        ];

$manpowerCosts = [
            'unskilled' => ($validatedData['unskilled'] ?? 0) * $rates['unskilled'],
            'semi_skilled_1' => ($validatedData['semi_skilled_1'] ?? 0) * $rates['semi_skilled_1'],
            'semi_skilled_2' => ($validatedData['semi_skilled_2'] ?? 0) * $rates['semi_skilled_2'],
        ];
        $manpowerTotal = array_sum($manpowerCosts);

$dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $validatedData['hsd_consumption'] ?? 0;
        $consumptionDetails = [];

if ($remainingConsumption > 0) {
            $stocks = DB::table('diesel_stocks')->where('diesel_stock', '>', 0)->orderBy('date_of_purchase')->get();

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;

$litersTaken = min($stock->diesel_stock, $remainingConsumption);

DB::table('diesel_stocks')->where('id', $stock->id)->update([
                    'diesel_stock' => $stock->diesel_stock - $litersTaken,
                    'updated_at' => now()
                ]);

$dieselCost += $litersTaken * $stock->rate_per_liter;
                $dieselRate = $dieselRate ?: $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;
                $consumptionDetails[] = [
                    'stock_id' => $stock->id,
                    'liters_used' => $litersTaken,
                    'rate' => $stock->rate_per_liter,
                    'cost' => $litersTaken * $stock->rate_per_liter
                ];
            }

if ($remainingConsumption > 0) {
                DB::rollBack();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INSUFFICIENT_DIESEL',
                    'message' => 'Not enough diesel stock available',
                    'requested_liters' => $validatedData['hsd_consumption'],
                    'available_liters' => $validatedData['hsd_consumption'] - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

$majorCostValue = 0;
        $majorMaintenanceContent = null;
        if (!empty($validatedData['spare_parts'])) {
            foreach ($validatedData['spare_parts'] as $part) {
                $majorCostValue += (float) ($part['value'] ?? 0);
            }
            $majorMaintenanceContent = json_encode($validatedData['spare_parts']);
        } elseif (!empty($validatedData['major_maintenance'])) {
            $majorMaintenanceContent = $validatedData['major_maintenance'];
        }

$totalCost = $manpowerTotal + $dieselCost + $majorCostValue;

// Insert into harvesting_update
        $recordId = DB::table('harvesting_update')->insertGetId([
            'block_name' => $validatedData['block_name'],
            'plot_name' => $validatedData['plot_name'],
            'area' => $validatedData['area'],
            'area_covered' => $validatedData['area_covered'],
            'seed_name' => $showing->seed_name, // ✅ Use seed_name from the matched record
            'seed_id' => $showing->seed_id,     // ✅ Use seed_id from the matched record
            'harvest_method' => $validatedData['harvest_method'],
            'harvest_purpose' => $validatedData['harvest_purpose'],
            'machine_id' => !empty($validatedData['machine_ids']) ? implode(',', $validatedData['machine_ids']) : null,
            'tractor_id' => !empty($validatedData['tractor_ids']) ? implode(',', $validatedData['tractor_ids']) : null,
            'yield_mt' => $validatedData['yield_mt'],
            'hsd_consumption' => $validatedData['hsd_consumption'],
            'start_time' => $validatedData['start_time'],
            'end_time' => $validatedData['end_time'],
            'hours_used' => $hours_used,
            'date' => $validatedData['date'],
            'category_id' => $manpowerCategories->pluck('id')->implode(','),
            'manpower_type' => $validatedData['manpower_type'] ?? null,
            'unskilled' => $validatedData['unskilled'] ?? null,
            'semi_skilled_1' => $validatedData['semi_skilled_1'] ?? null,
            'semi_skilled_2' => $validatedData['semi_skilled_2'] ?? null,
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'user_id' => $userId,
            'site_id' => $siteName,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now()
        ]);

// ✅ Update or insert into harvest_store_manage
        // Modified logic: Only match by seed_name and site_id (not date, block/plot specific)
        $existingHarvest = DB::table('harvest_store_manage')
            ->where('seed_name', $showing->seed_name)
            ->where('site_id', $siteId)
            ->first();

if ($existingHarvest) {
            // Update existing record - add yield_mt to existing values
            DB::table('harvest_store_manage')
                ->where('id', $existingHarvest->id)
                ->update([
                    'yield_mt' => DB::raw("yield_mt + {$validatedData['yield_mt']}"),
                    'total_mt' => DB::raw("total_mt + {$validatedData['yield_mt']}"),
                    'harvest_id' => $recordId, // Update with latest harvest_id
                    'updated_at' => now()
                ]);
        } else {
            // Create new record only if no existing record for this seed_name on this date
            DB::table('harvest_store_manage')->insert([
                'product_id' => 1,
                'site_id' => $siteId,
                'harvest_id' => $recordId,
                'silage_id' => null,
                'hey_id' => null,
                'block_name' => $validatedData['block_name'],
                'plot_name' => $validatedData['plot_name'],
                'seed_name' => $showing->seed_name, // ✅ Use seed_name from the matched record
                'seed_id' => $showing->seed_id,     // ✅ Use seed_id from the matched record
                'date' => $validatedData['date'],
                'yield_mt' => $validatedData['yield_mt'],
                'total_mt' => $validatedData['yield_mt'],
                'mt_price' => null,
                'sale_price' => null,
                'quantity' => null,
                'created_at' => now(),
                'updated_at' => now()
            ]);
        }

DB::commit();

return response()->json([
            'status' => 'success',
            'message' => 'Harvest data stored and store updated successfully.',
            'harvest_id' => $recordId
        ], 201);
    } catch (ValidationException $e) {
        DB::rollBack();
        return response()->json(['status' => 'error', 'message' => $e->getMessage()], 422);
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json(['status' => 'error', 'message' => 'Server error', 'error' => $e->getMessage()], 500);
    }
}

//check hey making

// public function checkHayMaking(Request $request)
// {
//     try {
//         $userId = auth()->id();

//         if (!$userId) {
//             return response()->json([
//                 'success' => false,
//                 'message' => 'User not authenticated'
//             ], 401);
//         }

//         $plots = DB::table('harvesting_update')
//             ->select('block_name', 'plot_name', 'area', 'seed_name', 'harvest_purpose') // 👈 Add harvest_purpose here
//             ->where('user_id', $userId)
//             ->get();

//         $grouped = [];

//         foreach ($plots as $plot) {
//             $blockName = $plot->block_name;

//             // ✅ Only fetch yield_mt from harvest_store_manage
//             $yield_mt = DB::table('harvest_store_manage')
//                 ->where('seed_name', $plot->seed_name)
//                 ->value('yield_mt');

//             // ✅ Use harvest_purpose from harvesting_update
//             $isFodderCrop = ($plot->harvest_purpose === 'Hay Making') ? 1 : 0;

//             $grouped[$blockName]['plots'][] = [
//                 'plot_name' => $plot->plot_name,
//                 'area' => $plot->area,
//                 'seed_name' => $plot->seed_name,
//                 'is_fodder_crop' => $isFodderCrop,
//                 'yield_mt' => $yield_mt ?? 0
//             ];
//         }

//         return response()->json([
//             'success' => true,
//             'data' => array_values($grouped)
//         ]);

//     } catch (\Exception $e) {
//         return response()->json([
//             'success' => false,
//             'message' => 'Error: ' . $e->getMessage()
//         ], 500);
//     }
// }
public function checkHayMaking(Request $request)
{
    try {
        $userId = auth()->id();
        if (!$userId) {
            return response()->json([
                'success' => false,
                'message' => 'User not authenticated'
            ], 401);
        }
        
        // Get all plots with relevant purposes (same as silage making logic)
        $plots = DB::table('harvesting_update')
            ->select('block_name', 'plot_name', 'area', 'seed_name', 'harvest_purpose')
            ->where('user_id', $userId)
            ->whereIn('harvest_purpose', ['Silage Making', 'Hay Making']) // Only get relevant records
            ->get();
            
        if ($plots->isEmpty()) {
            return response()->json([
                'success' => false,
                'message' => 'No plots found for Silage or Hay Making'
            ], 404);
        }
        
        $grouped = [];
        $seenSeeds = [];
        
        // 🔁 Group all harvest_purpose by seed_name (same logic as silage making)
        $seedPurposeMap = [];
        foreach ($plots as $plot) {
            if (!isset($seedPurposeMap[$plot->seed_name])) {
                $seedPurposeMap[$plot->seed_name] = [];
            }
            $seedPurposeMap[$plot->seed_name][] = $plot->harvest_purpose;
        }
        
        foreach ($plots as $plot) {
            // ✅ Avoid duplicate seed_name processing
            if (in_array($plot->seed_name, $seenSeeds)) {
                continue;
            }
            $seenSeeds[] = $plot->seed_name;
            
            $blockName = $plot->block_name;
            if (!isset($grouped[$blockName])) {
                $grouped[$blockName] = [
                    'block_name' => $blockName,
                    'plots' => []
                ];
            }
            
            // ✅ Updated logic: Check if ANY harvest_purpose is "Hay Making"
            $purposes = $seedPurposeMap[$plot->seed_name] ?? [];
            $isFodderCrop = 0;
            
            // Only if purpose is "Hay Making", set to 1
            if (in_array('Hay Making', $purposes)) {
                $isFodderCrop = 1;
            }
            
            // ✅ Get yield_mt from harvest_store_manage
            $yield_mt = DB::table('harvest_store_manage')
                ->where('seed_name', $plot->seed_name)
                ->value('yield_mt');
            
            $grouped[$blockName]['plots'][] = [
                'plot_name' => $plot->plot_name,
                'area' => $plot->area,
                'seed_name' => $plot->seed_name,
                'is_fodder_crop' => $isFodderCrop,
                'yield_mt' => $yield_mt ?? '0.00'
            ];
        }
        
        return response()->json([
            'success' => true,
            'data' => array_values($grouped)
        ]);
        
    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ], 500);
    }
}

//check silage making

// public function checkSilageMaking(Request $request)
// {
//     try {
//         $userId = auth()->id();
        
//         if (!$userId) {
//             return response()->json([
//                 'success' => false,
//                 'message' => 'User not authenticated'
//             ], 401);
//         }

//         // Get all plots with relevant purposes
//         $plots = DB::table('harvesting_update')
//             ->select('block_name', 'plot_name', 'area', 'seed_name', 'harvest_purpose')
//             ->where('user_id', $userId)
//             ->whereIn('harvest_purpose', ['Silage Making', 'Hay Making'])
//             ->get();

//         if ($plots->isEmpty()) {
//             return response()->json([
//                 'success' => false,
//                 'message' => 'No plots found for Silage or Hay Making'
//             ], 404);
//         }

//         $grouped = [];
//         $seenSeeds = [];

//         // 🔁 Group all harvest_purpose by seed_name
//         $seedPurposeMap = [];
//         foreach ($plots as $plot) {
//             $seedPurposeMap[$plot->seed_name][] = $plot->harvest_purpose;
//         }

//         foreach ($plots as $plot) {
//             // ✅ Avoid duplicate seed_name processing
//             if (in_array($plot->seed_name, $seenSeeds)) {
//                 continue;
//             }
//             $seenSeeds[] = $plot->seed_name;

//             $blockName = $plot->block_name;

//             // ✅ Default: is_silage_making = 0
//             $isSilageMaking = 0;

//             // ✅ Custom logic for Barseem
//             if ($plot->seed_name === 'Barseem') {
//                 $purposes = $seedPurposeMap['Barseem'] ?? [];

//                 if (in_array('Silage Making', $purposes)) {
//                     // If 'Silage Making' exists alone or with 'Hay Making'
//                     $isSilageMaking = 1;
//                 } else {
//                     // Only 'Hay Making'
//                     $isSilageMaking = 0;
//                 }
//             } else {
//                 // ✅ Other seed names
//                 $isSilageMaking = ($plot->harvest_purpose === 'Silage Making') ? 1 : 0;
//             }

//             // ✅ Get yield_mt
//             $yield_mt = DB::table('harvest_store_manage')
//                 ->where('seed_name', $plot->seed_name)
//                 ->value('yield_mt');

//             $grouped[$blockName]['plots'][] = [
//                 'plot_name' => $plot->plot_name,
//                 'area' => $plot->area,
//                 'seed_name' => $plot->seed_name,
//                 'is_silage_making' => $isSilageMaking,
//                 'yield_mt' => $yield_mt ?? 0
//             ];
//         }

//         return response()->json([
//             'success' => true,
//             'data' => array_values($grouped)
//         ]);

//     } catch (\Exception $e) {
//         return response()->json([
//             'success' => false,
//             'message' => 'Error: ' . $e->getMessage()
//         ], 500);
//     }
// }
public function checkSilageMaking(Request $request)
{
    try {
        $userId = auth()->id();
        
        if (!$userId) {
            return response()->json([
                'success' => false,
                'message' => 'User not authenticated'
            ], 401);
        }
        
        // Get all plots with relevant purposes
        $plots = DB::table('harvesting_update')
            ->select('block_name', 'plot_name', 'area', 'seed_name', 'harvest_purpose')
            ->where('user_id', $userId)
            ->whereIn('harvest_purpose', ['Silage Making', 'Hay Making'])
            ->get();
            
        if ($plots->isEmpty()) {
            return response()->json([
                'success' => false,
                'message' => 'No plots found for Silage or Hay Making'
            ], 404);
        }
        
        $grouped = [];
        $seenSeeds = [];
        
        // 🔁 Group all harvest_purpose by seed_name
        $seedPurposeMap = [];
        foreach ($plots as $plot) {
            if (!isset($seedPurposeMap[$plot->seed_name])) {
                $seedPurposeMap[$plot->seed_name] = [];
            }
            $seedPurposeMap[$plot->seed_name][] = $plot->harvest_purpose;
        }
        
        foreach ($plots as $plot) {
            // ✅ Avoid duplicate seed_name processing
            if (in_array($plot->seed_name, $seenSeeds)) {
                continue;
            }
            $seenSeeds[] = $plot->seed_name;
            
            $blockName = $plot->block_name;
            if (!isset($grouped[$blockName])) {
                $grouped[$blockName] = [
                    'block_name' => $blockName,
                    'plots' => []
                ];
            }
            
            // ✅ Updated logic: Only check for "Silage Making"
            $purposes = $seedPurposeMap[$plot->seed_name] ?? [];
            $isSilageMaking = 0;
            
            // Only if purpose is "Silage Making", set to 1
            if (in_array('Silage Making', $purposes)) {
                $isSilageMaking = 1;
            }
            
            // ✅ Get yield_mt
            $yield_mt = DB::table('harvest_store_manage')
                ->where('seed_name', $plot->seed_name)
                ->value('yield_mt');
            
            $grouped[$blockName]['plots'][] = [
                'plot_name' => $plot->plot_name,
                'area' => $plot->area,
                'seed_name' => $plot->seed_name,
                'is_silage_making' => $isSilageMaking,
                'yield_mt' => $yield_mt ?? '0.00'
            ];
        }
        
        return response()->json([
            'success' => true,
            'data' => array_values($grouped)
        ]);
        
    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error: ' . $e->getMessage()
        ], 500);
    }
}

//check hay making

// public function storeHayMaking(Request $request)
// {
//     $validatedData = $request->validate([
//         'block_name' => 'required|string|max:255',
//         'plot_name' => 'required|string|max:255',
//         'seed_name' => 'required|string|max:255', // Added seed_name validation
//         'hay_making_method' => 'nullable|string|max:255',
//         'machine_ids' => 'nullable|array',
//         'tractor_ids' => 'nullable|array',
//         'area' => 'nullable|numeric|min:0',
//         'area_covered' => 'nullable|string|max:255',
//         'yield_mt' => 'required|numeric|min:0',
//         'hsd_consumption' => 'nullable|numeric|min:0',
//         'start_time' => 'required|date_format:H:i',
//         'end_time' => 'required|date_format:H:i',
//         'category' => 'required|array',
//         'category.*' => 'string|max:255',
//         'date' => 'required|date',
//         'manpower_type' => 'nullable|string|max:255',
//         'unskilled' => 'nullable|integer|min:0',
//         'semi_skilled_1' => 'nullable|integer|min:0',
//         'semi_skilled_2' => 'nullable|integer|min:0',
//         'spare_parts' => 'nullable|array',
//         'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
//         'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
//         'major_maintenance' => 'nullable|string',
//         'user_id' => 'required|integer|exists:users,id',
//     ]);

//     DB::beginTransaction();

//     try {
//         $user = auth()->user();
//         if (!$user || !$user->site_name) {
//             DB::rollBack();
//             return response()->json(['status' => 'error', 'message' => 'User not authenticated or missing site name.'], 401);
//         }

//         $siteName = $user->site_name;
//         $siteId = $user->site_id;
//         $userId = $user->id;

//         // Get harvest data based on block_name, plot_name, and selected seed_name
//         $harvest = DB::table('harvesting_update')
//             ->where('block_name', $validatedData['block_name'])
//             ->where('plot_name', $validatedData['plot_name'])
//             ->where('seed_name', $validatedData['seed_name'])
//             ->latest('date')
//             ->first(['id', 'seed_id', 'seed_name']);

//         if (!$harvest) {
//             DB::rollBack();
//             return response()->json([
//                 'status' => 'error',
//                 'message' => 'No matching harvest data found for the selected block, plot, and seed combination.',
//                 'data' => [
//                     'block_name' => $validatedData['block_name'],
//                     'plot_name' => $validatedData['plot_name'],
//                     'seed_name' => $validatedData['seed_name']
//                 ]
//             ], 404);
//         }

//         $manpowerCategories = DB::table('master_manpower')
//             ->whereIn('category', $validatedData['category'])
//             ->get();

//         if ($endDateTime->lessThan($startDateTime)) {
//             $endDateTime->addDay();
//         }

//         $hours_used = round($startDateTime->floatDiffInHours($endDateTime), 2);

//         $rates = [
//             'unskilled' => $manpowerCategories->where('type', 'On Roll')->first()->rate ?? 0,
//             'semi_skilled_1' => $manpowerCategories->where('type', 'Third Party')->first()->rate ?? 0,
//             'semi_skilled_2' => $manpowerCategories->where('type', 'Of Roll')->first()->rate ?? 0,
//         ];

//         $manpowerTotal = array_sum($manpowerCosts);

//         $dieselCost = 0;
//         $dieselRate = 0;
//         $remainingConsumption = $validatedData['hsd_consumption'] ?? 0;

//         if ($remainingConsumption > 0) {
//             $stocks = DB::table('diesel_stocks')
//                 ->where('diesel_stock', '>', 0)
//                 ->orderBy('date_of_purchase', 'asc')
//                 ->get(['id', 'diesel_stock', 'rate_per_liter']);

//             foreach ($stocks as $stock) {
//                 if ($remainingConsumption <= 0) break;

//                 $litersTaken = min($stock->diesel_stock, $remainingConsumption);
//                 DB::table('diesel_stocks')
//                     ->where('id', $stock->id)
//                     ->update([
//                         'diesel_stock' => $stock->diesel_stock - $litersTaken,
//                         'updated_at' => now()
//                     ]);

//                 $dieselCost += $litersTaken * $stock->rate_per_liter;
//                 $remainingConsumption -= $litersTaken;

//                 if ($dieselRate == 0) {
//                     $dieselRate = $stock->rate_per_liter;
//                 }
//             }

//             if ($remainingConsumption > 0) {
//                 DB::rollBack();
//                 return response()->json([
//                     'status' => 'error',
//                     'error' => 'INSUFFICIENT_DIESEL',
//                     'message' => 'Not enough diesel stock available',
//                     'requested_liters' => $validatedData['hsd_consumption'],
//                     'available_liters' => $validatedData['hsd_consumption'] - $remainingConsumption,
//                     'shortage' => $remainingConsumption
//                 ], 400);
//             }
//         }

//         $majorCostValue = 0;
//         $majorMaintenanceContent = null;

//         if (!empty($validatedData['spare_parts']) && is_array($validatedData['spare_parts'])) {
//             foreach ($validatedData['spare_parts'] as $part) {
//                 $majorCostValue += (float) ($part['value'] ?? 0);
//             }
//             $majorMaintenanceContent = json_encode($validatedData['spare_parts']);
//         } elseif (!empty($validatedData['major_maintenance'])) {
//             $majorMaintenanceContent = $validatedData['major_maintenance'];
//         }

//         $totalCost = $manpowerTotal + $dieselCost + $majorCostValue;

//         $recordId = DB::table('hay_making')->insertGetId([
//             'user_id' => $validatedData['user_id'],
//             'block_name' => $validatedData['block_name'],
//             'plot_name' => $validatedData['plot_name'],
//             'area' => $validatedData['area'],
//             'area_covered' => $validatedData['area_covered'],
//             'seed_name' => $harvest->seed_name, // Use selected seed_name from harvest
//             'seed_id' => $harvest->seed_id,     // Use corresponding seed_id from harvest
//             'hay_making_method' => $request->hay_making_method,
//             'machine_id' => !empty($validatedData['machine_ids']) ? implode(',', $validatedData['machine_ids']) : null,
//             'tractor_id' => !empty($validatedData['tractor_ids']) ? implode(',', $validatedData['tractor_ids']) : null,
//             'yield_mt' => $validatedData['yield_mt'],
//             'hsd_consumption' => $validatedData['hsd_consumption'],
//             'start_time' => $validatedData['start_time'],
//             'end_time' => $validatedData['end_time'],
//             'hours_used' => $hours_used,
//             'harvest_date' => $validatedData['date'],
//             'category_id' => $manpowerCategories->pluck('id')->implode(','),
//             'manpower_type' => $validatedData['manpower_type'] ?? null,
//             'unskilled' => $validatedData['unskilled'] ?? null,
//             'semi_skilled_1' => $validatedData['semi_skilled_1'] ?? null,
//             'semi_skilled_2' => $validatedData['semi_skilled_2'] ?? null,
//             'major_maintenance' => $majorMaintenanceContent,
//             'major_cost' => $majorCostValue,
//             'site_id' => $siteName,
//             'user_id' => $userId,
//             'total_cost' => $totalCost,
//             'created_at' => now(),
//             'updated_at' => now()
//         ]);

//         // ✅ Insert or update harvest_store_manage (product_id = 2 for hay)
//         $matchedSeed = DB::table('master_seed')->where('id', $harvest->seed_id)->first();
//         $mtPrice = $matchedSeed->rate_of_seed ?? null;

//         // Check for existing HAY record (not harvest record) - only check for hey_id
//         $existingHayRecord = DB::table('harvest_store_manage')
//             ->where('block_name', $validatedData['block_name'])
//             ->where('plot_name', $validatedData['plot_name'])
//             ->where('seed_id', $harvest->seed_id)
//             ->where('seed_name', $harvest->seed_name)
//             ->where('site_id', $siteId)
//             ->whereDate('date', $validatedData['date'])
//             ->whereNotNull('hey_id') // Only find records that already have hey_id
//             ->where('product_id', 2) // Only hay records
//             ->first();

//         if ($existingHayRecord) {
//             // Update existing HAY record - same seed, same date, already has hey_id
//             DB::table('harvest_store_manage')
//                 ->where('id', $existingHayRecord->id)
//                 ->update([
//                     'yield_mt' => DB::raw("yield_mt + {$validatedData['yield_mt']}"),
//                     'total_mt' => DB::raw("total_mt + {$validatedData['yield_mt']}"),
//                     'hey_id' => $recordId, // Update with new hey record ID
//                     'updated_at' => now()
//                 ]);
//         } else {
//             // Always insert NEW record for hay making
//             // This ensures harvest and hay records remain separate
//             DB::table('harvest_store_manage')->insert([
//                 'hey_id'       => $recordId,
//                 'harvest_id'   => null, // Always null for hay records
//                 'silage_id'    => null, // Always null for hay records  
//                 'product_id'   => 2,    // product_id = 2 for hay
//                 'site_id'      => $siteId,
//                 'block_name'   => $validatedData['block_name'],
//                 'plot_name'    => $validatedData['plot_name'],
//                 'seed_name'    => $harvest->seed_name,
//                 'seed_id'      => $harvest->seed_id,
//                 'date'         => $validatedData['date'],
//                 'yield_mt'     => $validatedData['yield_mt'],
//                 'total_mt'     => $validatedData['yield_mt'],
//                 'mt_price'     => $mtPrice,
//                 'created_at'   => now(),
//                 'updated_at'   => now()
//             ]);
//         }

//         DB::commit();

//         return response()->json([
//             'status' => 'success',
//             'message' => 'Hay making data stored successfully',
//             'insert_id' => $recordId,
//             'seed_name' => $harvest->seed_name,
//             'seed_id' => $harvest->seed_id,
//             'total_cost' => number_format($totalCost, 2),
//             'cost_breakdown' => [
//                 'manpower' => ['total' => number_format($manpowerTotal, 2)],
//                 'diesel' => ['cost' => number_format($dieselCost, 2)],
//             ],
//             'time_used' => $hours_used,
//         ], 201);

//     } catch (\Exception $e) {
//         DB::rollBack();
//         return response()->json([
//             'status' => 'error',
//             'error' => 'SERVER_ERROR',
//             'message' => 'Failed to store hay making data',
//             'system_message' => $e->getMessage()
//         ], 500);
//     }
// }

public function storeHayMaking(Request $request)
{
    $validatedData = $request->validate([
        'block_name' => 'required|string|max:255',
        'plot_name' => 'required|string|max:255',
        'seed_name' => 'required|string|max:255',
        'hay_making_method' => 'nullable|string|max:255',
        'machine_ids' => 'nullable|array',
        'tractor_ids' => 'nullable|array',
        'area' => 'nullable|numeric|min:0',
        'area_covered' => 'nullable|string|max:255',
        'yield_mt' => 'required|numeric|min:0',
        'hsd_consumption' => 'nullable|numeric|min:0',
        'start_time' => 'required|date_format:H:i',
        'end_time' => 'required|date_format:H:i',
        'category' => 'nullable|array',
        'category.*' => 'string|max:255',
        'date' => 'required|date',
        'manpower_type' => 'nullable|string|max:255',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'spare_parts' => 'nullable|array',
        'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
        'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
        'major_maintenance' => 'nullable|string',
        'user_id' => 'required|integer|exists:users,id',
    ]);

DB::beginTransaction();

try {
        $user = auth()->user();
        if (!$user || !$user->site_name) {
            DB::rollBack();
            return response()->json(['status' => 'error', 'message' => 'User not authenticated or missing site name.'], 401);
        }

$siteName = $user->site_name;
        $siteId = $user->site_id;
        $userId = $user->id;

// Get harvest data based on block_name, plot_name, and selected seed_name
        $harvest = DB::table('harvesting_update')
            ->where('block_name', $validatedData['block_name'])
            ->where('plot_name', $validatedData['plot_name'])
            ->where('seed_name', $validatedData['seed_name'])
            ->latest('date')
            ->first(['id', 'seed_id', 'seed_name']);

if (!$harvest) {
            DB::rollBack();
            return response()->json([
                'status' => 'error',
                'message' => 'No matching harvest data found for the selected block, plot, and seed combination.',
                'data' => [
                    'block_name' => $validatedData['block_name'],
                    'plot_name' => $validatedData['plot_name'],
                    'seed_name' => $validatedData['seed_name']
                ]
            ], 404);
        }

// ✅ Check current available yield_mt in harvest_store_manage before proceeding
        $currentAvailableYield = DB::table('harvest_store_manage')
            ->where('block_name', $validatedData['block_name'])
            ->where('plot_name', $validatedData['plot_name'])
            ->where('seed_id', $harvest->seed_id)
            ->where('seed_name', $harvest->seed_name)
            ->where('site_id', $siteId)
            ->where('product_id', 1) // Harvested products only
            ->sum('yield_mt');

if ($currentAvailableYield <= 0) {
            DB::rollBack();
            return response()->json([
                'status' => 'error',
                'message' => 'आप hay नहीं बना सकते हैं क्योंकि आपके पास yield नहीं है।',
                'english_message' => 'You cannot make hay because you do not have yield available.',
                'available_yield' => $currentAvailableYield,
                'requested_yield' => $validatedData['yield_mt']
            ], 400);
        }

if ($validatedData['yield_mt'] > $currentAvailableYield) {
            DB::rollBack();
            return response()->json([
                'status' => 'error',
                'message' => 'Requested yield amount exceeds available yield.',
                'available_yield' => $currentAvailableYield,
                'requested_yield' => $validatedData['yield_mt']
            ], 400);
        }

$manpowerCategories = collect();
        
        if (!empty($validatedData['category']) && is_array($validatedData['category'])) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $validatedData['category'])
                ->get();

if ($manpowerCategories->isEmpty()) {
                $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INVALID_CATEGORIES',
                    'message' => 'No matching manpower categories found',
                    'valid_categories' => $validCategories,
                    'received_categories' => $validatedData['category']
                ], 400);
            }
        }

if ($endDateTime->lessThan($startDateTime)) {
            $endDateTime->addDay();
        }

$hours_used = round($startDateTime->floatDiffInHours($endDateTime), 2);

$manpowerTotal = array_sum($manpowerCosts);

$dieselCost = 0;
        $dieselRate = 0;
        $remainingConsumption = $validatedData['hsd_consumption'] ?? 0;
        $consumptionDetails = [];

foreach ($stocks as $stock) {
                if ($remainingConsumption <= 0) break;

$litersTaken = min($stock->diesel_stock, $remainingConsumption);
                
                DB::table('diesel_stocks')
                    ->where('id', $stock->id)
                    ->update([
                        'diesel_stock' => $stock->diesel_stock - $litersTaken,
                        'updated_at' => now()
                    ]);

$dieselCost += $litersTaken * $stock->rate_per_liter;
                $remainingConsumption -= $litersTaken;

if ($dieselRate == 0) {
                    $dieselRate = $stock->rate_per_liter;
                }
            }

if ($remainingConsumption > 0) {
                DB::rollBack();
                return response()->json([
                    'status' => 'error',
                    'error' => 'INSUFFICIENT_DIESEL',
                    'message' => 'Not enough diesel stock available',
                    'requested_liters' => $validatedData['hsd_consumption'],
                    'available_liters' => $validatedData['hsd_consumption'] - $remainingConsumption,
                    'shortage' => $remainingConsumption,
                    'consumption_details' => $consumptionDetails
                ], 400);
            }
        }

$majorCostValue = 0;
        $majorMaintenanceContent = null;

if (!empty($validatedData['spare_parts']) && is_array($validatedData['spare_parts'])) {
            foreach ($validatedData['spare_parts'] as $part) {
                $majorCostValue += (float) ($part['value'] ?? 0);
            }
            $majorMaintenanceContent = json_encode($validatedData['spare_parts']);
        } elseif (!empty($validatedData['major_maintenance'])) {
            $majorMaintenanceContent = $validatedData['major_maintenance'];
        }

$totalCost = $manpowerTotal + $dieselCost + $majorCostValue;

// ✅ CORRECTED LOGIC: total_yield_mt should be current available yield (before this activity)
        $totalYieldMt = $currentAvailableYield;

// ✅ Insert into hay_making table with current available yield as total_yield_mt
        $recordId = DB::table('hay_making')->insertGetId([
            'user_id' => $validatedData['user_id'],
            'block_name' => $validatedData['block_name'],
            'plot_name' => $validatedData['plot_name'],
            'area' => $validatedData['area'],
            'area_covered' => $validatedData['area_covered'],
            'seed_name' => $harvest->seed_name,
            'seed_id' => $harvest->seed_id,
            'hay_making_method' => $request->hay_making_method,
            'machine_id' => !empty($validatedData['machine_ids']) ? implode(',', $validatedData['machine_ids']) : null,
            'tractor_id' => !empty($validatedData['tractor_ids']) ? implode(',', $validatedData['tractor_ids']) : null,
            'yield_mt' => $validatedData['yield_mt'],
            'total_yield_mt' => $totalYieldMt, // Current available yield
            'hsd_consumption' => $validatedData['hsd_consumption'],
            'start_time' => $validatedData['start_time'],
            'end_time' => $validatedData['end_time'],
            'hours_used' => $hours_used,
            'harvest_date' => $validatedData['date'],
            // 'category_id' => $manpowerCategories->pluck('id')->implode(','),
               'category_id' => $manpowerCategories->isNotEmpty() ? $manpowerCategories->pluck('id')->implode(',') : null,
            'manpower_type' => $validatedData['manpower_type'] ?? null,
            'unskilled' => $validatedData['unskilled'] ?? null,
            'semi_skilled_1' => $validatedData['semi_skilled_1'] ?? null,
            'semi_skilled_2' => $validatedData['semi_skilled_2'] ?? null,
            'major_maintenance' => $majorMaintenanceContent,
            'major_cost' => $majorCostValue,
            'site_id' => $siteName,
            'user_id' => $userId,
            'total_cost' => $totalCost,
            'created_at' => now(),
            'updated_at' => now()
        ]);

// ✅ Deduct yield_mt from harvest_store_manage (product_id = 1 for harvested products)
        $harvestRecords = DB::table('harvest_store_manage')
            ->where('block_name', $validatedData['block_name'])
            ->where('plot_name', $validatedData['plot_name'])
            ->where('seed_id', $harvest->seed_id)
            ->where('seed_name', $harvest->seed_name)
            ->where('site_id', $siteId)
            ->where('product_id', 1) // Harvested products
            ->where('yield_mt', '>', 0)
            ->orderBy('date', 'asc')
            ->get();

$remainingToDeduct = $validatedData['yield_mt'];

foreach ($harvestRecords as $record) {
            if ($remainingToDeduct <= 0) break;

$deductAmount = min($record->yield_mt, $remainingToDeduct);
            
            DB::table('harvest_store_manage')
                ->where('id', $record->id)
                ->update([
                    'yield_mt' => $record->yield_mt - $deductAmount,
                    'updated_at' => now()
                ]);

$remainingToDeduct -= $deductAmount;
        }

// ✅ Calculate hay yield with 80% reduction
        $originalYield = $validatedData['yield_mt'];
        $hayYield = $originalYield * 0.2; // 80% reduction, so 20% remains
        
        // ✅ Create/Update hay entry in harvest_store_manage (product_id = 2 for hay) with reduced yield
        $matchedSeed = DB::table('master_seed')->where('id', $harvest->seed_id)->first();
        $mtPrice = $matchedSeed->rate_of_seed ?? null;

// Check for existing HAY record
        $existingHayRecord = DB::table('harvest_store_manage')
            ->where('block_name', $validatedData['block_name'])
            ->where('plot_name', $validatedData['plot_name'])
            ->where('seed_id', $harvest->seed_id)
            ->where('seed_name', $harvest->seed_name)
            ->where('site_id', $siteId)
            ->where('product_id', 2) // Only hay records
            ->whereDate('date', $validatedData['date'])
            ->first();

if ($existingHayRecord) {
            // Update existing HAY record
            DB::table('harvest_store_manage')
                ->where('id', $existingHayRecord->id)
                ->update([
                    'yield_mt' => DB::raw("yield_mt + {$hayYield}"),
                    'total_mt' => DB::raw("total_mt + {$hayYield}"),
                    'hey_id' => $recordId,
                    'updated_at' => now()
                ]);
        } else {
            // Insert NEW record for hay making
            DB::table('harvest_store_manage')->insert([
                'hey_id'       => $recordId,
                'harvest_id'   => null,
                'silage_id'    => null,
                'product_id'   => 2, // product_id = 2 for hay
                'site_id'      => $siteId,
                'block_name'   => $validatedData['block_name'],
                'plot_name'    => $validatedData['plot_name'],
                'seed_name'    => $harvest->seed_name,
                'seed_id'      => $harvest->seed_id,
                'date'         => $validatedData['date'],
                'yield_mt'     => $hayYield, // 80% reduced yield
                'total_mt'     => $hayYield, // 80% reduced yield
                'mt_price'     => $mtPrice,
                'created_at'   => now(),
                'updated_at'   => now()
            ]);
        }

// ✅ Calculate remaining yield after this activity
        $remainingYieldAfter = $currentAvailableYield - $validatedData['yield_mt'];

DB::commit();

return response()->json([
            'status' => 'success',
            'message' => 'Hay making data stored successfully',
            'insert_id' => $recordId,
            'seed_name' => $harvest->seed_name,
            'seed_id' => $harvest->seed_id,
            'total_cost' => number_format($totalCost, 2),
            'cost_breakdown' => [
                'manpower' => ['total' => number_format($manpowerTotal, 2)],
                'diesel' => ['cost' => number_format($dieselCost, 2)],
            ],
            'time_used' => $hours_used,
            'original_yield_used' => $originalYield,
            'hay_yield_produced' => $hayYield,
            'yield_reduction_percentage' => '80%',
            'total_yield_mt' => $totalYieldMt, // Shows available yield at time of activity
            'available_yield_before' => $currentAvailableYield,
            'available_yield_after' => $remainingYieldAfter
        ], 201);

} catch (\Exception $e) {
        DB::rollBack();
        return response()->json([
            'status' => 'error',
            'error' => 'SERVER_ERROR',
            'message' => 'Failed to store hay making data',
            'system_message' => $e->getMessage()
        ], 500);
    }
}

//silage making store data

public function storesilageMaking(Request $request)
{
    $validatedData = $request->validate([
        'block_name' => 'required|string|max:255',
        'plot_name' => 'required|string|max:255',
        'seed_name' => 'required|string|max:255',
        'silage_making_method' => 'nullable|string|max:255',
        'machine_ids' => 'nullable|array',
        'tractor_ids' => 'nullable|array',
        'area' => 'nullable|numeric|min:0',
        'area_covered' => 'nullable|string|max:255',
        'yield_mt' => 'required|numeric|min:0',
        'hsd_consumption' => 'nullable|numeric|min:0',
        'start_time' => 'required|date_format:H:i',
        'end_time' => 'required|date_format:H:i',
        'category' => 'nullable|array',
        'category.*' => 'string|max:255',
        'date' => 'required|date',
        'manpower_type' => 'nullable|string|max:255',
        'unskilled' => 'nullable|integer|min:0',
        'semi_skilled_1' => 'nullable|integer|min:0',
        'semi_skilled_2' => 'nullable|integer|min:0',
        'spare_parts' => 'nullable|array',
        'spare_parts.*.spare_part' => 'required_with:spare_parts|string|max:255',
        'spare_parts.*.value' => 'required_with:spare_parts|numeric|min:0',
        'major_maintenance' => 'nullable|string',
        'user_id' => 'required|integer|exists:users,id',
    ]);

DB::beginTransaction();

$siteName = $user->site_name;
        $siteId = $user->site_id;
        $userId = $user->id;

if ($currentAvailableYield <= 0) {
            DB::rollBack();
            return response()->json([
                'status' => 'error',
                'message' => 'आप silage नहीं बना सकते हैं क्योंकि आपके पास yield नहीं है।',
                'english_message' => 'You cannot make silage because you do not have yield available.',
                'available_yield' => $currentAvailableYield,
                'requested_yield' => $validatedData['yield_mt']
            ], 400);
        }

// $manpowerCategories = DB::table('master_manpower')
        //     ->whereIn('category', $validatedData['category'])
        //     ->get();

// if ($manpowerCategories->isEmpty()) {
        //     $validCategories = DB::table('master_manpower')->distinct()->pluck('category')->toArray();
        //     return response()->json([
        //         'status' => 'error',
        //         'error' => 'INVALID_CATEGORIES',
        //         'message' => 'No matching manpower categories found',
        //         'valid_categories' => $validCategories,
        //         'received_categories' => $validatedData['category']
        //     ], 400);
        // }
    $manpowerCategories = collect();
        
        if (!empty($validatedData['category']) && is_array($validatedData['category'])) {
            $manpowerCategories = DB::table('master_manpower')
                ->whereIn('category', $validatedData['category'])
                ->get();