000

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

class HayMakingController extends Controller
{
  
//  public function index(Request $request)
// {
//     $user = Auth::user();
//     $siteId = $user->site_name;
//     $role = $user->role;

//     // ✅ 1. Site dropdown for admin
//     $sites = DB::table('master_sites')->pluck('site_name', 'id');

//     // ✅ 2. Get selected site ID
//     $selectedSiteId = $request->input('site_id');
//     if ($role != 1) {
//         $selectedSiteId = $siteId;
//     }

//     // ✅ 3. Block filter based on selected site
//     $blocksQuery = DB::table('hay_making')
//         ->select('block_name')->distinct()->orderBy('block_name');
//     if (!empty($selectedSiteId)) {
//         $blocksQuery->where('site_id', $selectedSiteId);
//     }
//     $blocks = $blocksQuery->pluck('block_name');

//     // ✅ 4. Main query
//     $q = DB::table('hay_making as h')
//         ->select('h.*', 'users.name as user_name')
//         ->leftJoin('users', 'h.user_id', '=', 'users.id');

//     if (!empty($selectedSiteId)) {
//         $q->where('h.site_id', $selectedSiteId);
//     }

//     // ✅ 5. Filters
//     if ($request->filled('block_name')) {
//         $q->where('h.block_name', $request->block_name);
//     }

//     // ✅ 6. Paginate
//     $haymakings = $q->orderBy('h.harvest_date', 'desc')
//                     ->paginate(10)
//                     ->appends($request->only(['block_name', 'search', 'site_id']));

//     // ✅ 7. Machines and tractors
//     $allMachines = DB::table('master_machine')->pluck('machine_name', 'id');
//     $allTractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

//     foreach ($haymakings as $haymaking) {
//         $machineIds = explode(',', $haymaking->machine_id ?? '');
//         $tractorIds = explode(',', $haymaking->tractor_id ?? '');

//         $haymaking->machine_names_display = collect($machineIds)->map(function($id) use ($allMachines) {
//             return $allMachines[trim($id)] ?? null;
//         })->filter()->implode(', ') ?: '-';

//         $haymaking->tractor_names_display = collect($tractorIds)->map(function($id) use ($allTractors) {
//             return $allTractors[trim($id)] ?? null;
//         })->filter()->implode(', ') ?: '-';

//         $haymaking->parsed_major_maintenance = [];
//         if ($haymaking->major_maintenance) {
//             $decoded = json_decode($haymaking->major_maintenance, true);
//             if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
//                 $haymaking->parsed_major_maintenance = $decoded;
//             }
//         }
//     }

//     if ($request->ajax()) {
//         return view('haymaking.partials.table', compact('haymakings'))->render();
//     }

//     return view('haymaking.index', compact('blocks', 'haymakings', 'sites', 'selectedSiteId', 'role'));
// }

// public function index(Request $request)
//     {
//         $user = Auth::user();
//         $siteId = $user->site_name;
//         $role = $user->role;

//         // ✅ 1. Site dropdown for admin
//         $sites = DB::table('master_sites')->pluck('site_name', 'id');

//         // ✅ 2. Get selected site ID
//         $selectedSiteId = $request->input('site_id');
//         if ($role != 1) {
//             $selectedSiteId = $siteId;
//         }

//         // ✅ 3. Block filter based on selected site (from hay_making table)
//         $blocksQuery = DB::table('hay_making')
//             ->select('block_name')->distinct()->orderBy('block_name');
        
//         if (!empty($selectedSiteId)) {
//             $blocksQuery->where('site_id', $selectedSiteId);
//         }
//         $blocks = $blocksQuery->pluck('block_name');

//         // ✅ 4. Get Total Available Yield per seed_name from harvesting_update 
//         // (matching by seed_name, not seed_id)
//         $totalAvailableYields = DB::table('harvesting_update')
//             ->select('seed_name', DB::raw('SUM(yield_mt) as total_available_yield'))
//             ->where('harvest_purpose', 'Silage Making')
//             ->groupBy('seed_name')
//             ->pluck('total_available_yield', 'seed_name');

//         // ✅ 5. Main query - Get data from hay_making table
//         $q = DB::table('hay_making as h')
//             ->select(
//                 'h.*', 
//                 'users.name as user_name'
//             )
//             ->leftJoin('users', 'h.user_id', '=', 'users.id');

//         if (!empty($selectedSiteId)) {
//             $q->where('h.site_id', $selectedSiteId);
//         }

//         // ✅ 6. Filters
//         if ($request->filled('block_name')) {
//             $q->where('h.block_name', $request->block_name);
//         }

//         if ($request->filled('search')) {
//             $term = '%' . $request->search . '%';
//             $q->where(function($sub) use ($term) {
//                 $sub->where('h.plot_name', 'like', $term)
//                     ->orWhere('h.block_name', 'like', $term)
//                     ->orWhere('h.seed_name', 'like', $term)
//                     ->orWhere('users.name', 'like', $term);
//             });
//         }

//         // ✅ 7. Paginate
//         $haymakings = $q->orderBy('h.harvest_date', 'desc')
//                         ->paginate(10)
//                         ->appends($request->only(['block_name', 'search', 'site_id']));

//         // ✅ 8. Machines and tractors
//         $allMachines = DB::table('master_machine')->pluck('machine_name', 'id');
//         $allTractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

//         foreach ($haymakings as $haymaking) {
//             // ✅ Calculate yield metrics based on seed_name matching
//             $seedName = $haymaking->seed_name;
            
//             // Total Available Yield from harvesting_update table (matching by seed_name)
//             $haymaking->total_available_yield = $totalAvailableYields[$seedName] ?? 0;
            
//             // Required Yield MT (from hay_making table yield_mt)
//             $haymaking->required_yield_mt = $haymaking->yield_mt;
            
//             // Adjusted Yield MT (Required Yield MT से 80% कम, मतलब 20% बचे)
//             $haymaking->adjusted_yield_mt = $haymaking->required_yield_mt * 0.2;
            
//             // Remaining Yield MT (Total Available - Adjusted Yield)
//             $haymaking->remaining_yield_mt = $haymaking->total_available_yield - $haymaking->adjusted_yield_mt;
//             // Handle machine names
//             if (!empty($haymaking->machine_id)) {
//                 $machineIds = explode(',', $haymaking->machine_id);
//                 $haymaking->machine_names_display = collect($machineIds)->map(function($id) use ($allMachines) {
//                     return $allMachines[trim($id)] ?? null;
//                 })->filter()->implode(', ') ?: '-';
//             } else {
//                 $haymaking->machine_names_display = '-';
//             }

//             // Handle tractor names
//             if (!empty($haymaking->tractor_id)) {
//                 $tractorIds = explode(',', $haymaking->tractor_id);
//                 $haymaking->tractor_names_display = collect($tractorIds)->map(function($id) use ($allTractors) {
//                     return $allTractors[trim($id)] ?? null;
//                 })->filter()->implode(', ') ?: '-';
//             } else {
//                 $haymaking->tractor_names_display = '-';
//             }

//             // Handle major maintenance JSON
//             $haymaking->parsed_major_maintenance = [];
//             if ($haymaking->major_maintenance) {
//                 $decoded = json_decode($haymaking->major_maintenance, true);
//                 if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
//                     $haymaking->parsed_major_maintenance = $decoded;
//                 }
//             }

//             // Map harvest_date from hay_making table
//             $haymaking->harvest_date = $haymaking->harvest_date;
//         }

//         if ($request->ajax()) {
//             return view('haymaking.partials.table', compact('haymakings'))->render();
//         }

//         return view('haymaking.index', compact('blocks', 'haymakings', 'sites', 'selectedSiteId', 'role'));
//     }
// public function index(Request $request)
// {
//     $user = Auth::user();
//     $siteId = $user->site_name;
//     $role = $user->role;

//     // ✅ 1. Site dropdown for admin
//     $sites = DB::table('master_sites')->pluck('site_name', 'id');

//     // ✅ 2. Get selected site ID
//     $selectedSiteId = $request->input('site_id');
//     if ($role != 1) {
//         $selectedSiteId = $siteId;
//     }

//     // ✅ 3. Block filter based on selected site (from hay_making table)
//     $blocksQuery = DB::table('hay_making')
//         ->select('block_name')->distinct()->orderBy('block_name');
    
//     if (!empty($selectedSiteId)) {
//         $blocksQuery->where('site_id', $selectedSiteId);
//     }
//     $blocks = $blocksQuery->pluck('block_name');

//     // ✅ 4. MODIFIED: Get Total Available Yield per seed_name from harvest_store_manage 
//     // (product_id = 1 for harvested products, matching by seed_name and site)
//     $totalAvailableYieldsQuery = DB::table('harvest_store_manage')
//         ->select('seed_name', DB::raw('SUM(yield_mt) as total_available_yield'))
//         ->where('product_id', 1) // Only harvested products
//         ->groupBy('seed_name');
    
//     if (!empty($selectedSiteId)) {
//         $totalAvailableYieldsQuery->where('site_id', $selectedSiteId);
//     }
    
//     $totalAvailableYields = $totalAvailableYieldsQuery->pluck('total_available_yield', 'seed_name');

//     // ✅ 5. Main query - Get data from hay_making table
//     $q = DB::table('hay_making as h')
//         ->select(
//             'h.*', 
//             'users.name as user_name'
//         )
//         ->leftJoin('users', 'h.user_id', '=', 'users.id');

//     if (!empty($selectedSiteId)) {
//         $q->where('h.site_id', $selectedSiteId);
//     }

//     // ✅ 6. Filters
//     if ($request->filled('block_name')) {
//         $q->where('h.block_name', $request->block_name);
//     }

//     if ($request->filled('search')) {
//         $term = '%' . $request->search . '%';
//         $q->where(function($sub) use ($term) {
//             $sub->where('h.plot_name', 'like', $term)
//                 ->orWhere('h.block_name', 'like', $term)
//                 ->orWhere('h.seed_name', 'like', $term)
//                 ->orWhere('users.name', 'like', $term);
//         });
//     }

//     // ✅ 7. Paginate
//     $haymakings = $q->orderBy('h.harvest_date', 'desc')
//                     ->paginate(10)
//                     ->appends($request->only(['block_name', 'search', 'site_id']));

//     // ✅ 8. Machines and tractors
//     $allMachines = DB::table('master_machine')->pluck('machine_name', 'id');
//     $allTractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

//     foreach ($haymakings as $haymaking) {
//         // ✅ MODIFIED: Calculate yield metrics based on seed_name matching from harvest_store_manage
//         $seedName = $haymaking->seed_name;
        
//         // Total Available Yield from harvest_store_manage table (product_id = 1, matching by seed_name)
//         $haymaking->total_available_yield = $totalAvailableYields[$seedName] ?? 0;
        
//         // Required Yield MT (from hay_making table yield_mt)
//         $haymaking->required_yield_mt = $haymaking->yield_mt;
        
//         // Adjusted Yield MT (Required Yield MT से 80% कम, मतलब 20% बचे)
//         $haymaking->adjusted_yield_mt = $haymaking->required_yield_mt * 0.2;
        
//         // Remaining Yield MT (Total Available - Required Yield, not Adjusted)
//         $haymaking->remaining_yield_mt = $haymaking->total_available_yield - $haymaking->required_yield_mt;
        
//         // Handle machine names
//         if (!empty($haymaking->machine_id)) {
//             $machineIds = explode(',', $haymaking->machine_id);
//             $haymaking->machine_names_display = collect($machineIds)->map(function($id) use ($allMachines) {
//                 return $allMachines[trim($id)] ?? null;
//             })->filter()->implode(', ') ?: '-';
//         } else {
//             $haymaking->machine_names_display = '-';
//         }

//         // Handle tractor names
//         if (!empty($haymaking->tractor_id)) {
//             $tractorIds = explode(',', $haymaking->tractor_id);
//             $haymaking->tractor_names_display = collect($tractorIds)->map(function($id) use ($allTractors) {
//                 return $allTractors[trim($id)] ?? null;
//             })->filter()->implode(', ') ?: '-';
//         } else {
//             $haymaking->tractor_names_display = '-';
//         }

//         // Handle major maintenance JSON
//         $haymaking->parsed_major_maintenance = [];
//         if ($haymaking->major_maintenance) {
//             $decoded = json_decode($haymaking->major_maintenance, true);
//             if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
//                 $haymaking->parsed_major_maintenance = $decoded;
//             }
//         }
                    
//         // Map harvest_date from hay_making table
//         $haymaking->harvest_date = $haymaking->harvest_date;
//     }

//     if ($request->ajax()) {
//         return view('haymaking.partials.table', compact('haymakings'))->render();
//     }

//     return view('haymaking.index', compact('blocks', 'haymakings', 'sites', 'selectedSiteId', 'role'));
// }
// public function index(Request $request)
// {
//     $user = Auth::user();
//     $siteId = $user->site_name;
//     $role = $user->role;

//     // ✅ 1. Site dropdown for admin
//     $sites = DB::table('master_sites')->pluck('site_name', 'id');

//     // ✅ 2. Get selected site ID
//     $selectedSiteId = $request->input('site_id');
//     if ($role != 1) {
//         $selectedSiteId = $siteId;
//     }

//     // ✅ 3. Block filter based on selected site
//     $blocksQuery = DB::table('hay_making')
//         ->select('block_name')->distinct()->orderBy('block_name');
    
//     if (!empty($selectedSiteId)) {
//         $blocksQuery->where('site_id', $selectedSiteId);
//     }
//     $blocks = $blocksQuery->pluck('block_name');

//     // ✅ 4. Get Total Available Yield per block + plot + seed_name (more accurate)
//     $totalAvailableYieldsQuery = DB::table('harvest_store_manage')
//         ->select(
//             'block_name',
//             'plot_name',
//             'seed_name',
//             DB::raw('SUM(yield_mt) as total_available_yield')
//         )
//         ->where('product_id', 1); // harvested products

//     if (!empty($selectedSiteId)) {
//         $totalAvailableYieldsQuery->where('site_id', $selectedSiteId);
//     }

//     $totalAvailableYields = $totalAvailableYieldsQuery
//         ->groupBy('block_name', 'plot_name', 'seed_name')
//         ->get()
//         ->keyBy(function ($item) {
//             return $item->block_name . '|' . $item->plot_name . '|' . $item->seed_name;
//         });

//     // ✅ 5. Main hay_making query
//     $q = DB::table('hay_making as h')
//         ->select('h.*', 'users.name as user_name')
//         ->leftJoin('users', 'h.user_id', '=', 'users.id');

//     if (!empty($selectedSiteId)) {
//         $q->where('h.site_id', $selectedSiteId);
//     }

//     // ✅ 6. Filters
//     if ($request->filled('block_name')) {
//         $q->where('h.block_name', $request->block_name);
//     }

//     if ($request->filled('search')) {
//         $term = '%' . $request->search . '%';
//         $q->where(function ($sub) use ($term) {
//             $sub->where('h.plot_name', 'like', $term)
//                 ->orWhere('h.block_name', 'like', $term)
//                 ->orWhere('h.seed_name', 'like', $term)
//                 ->orWhere('users.name', 'like', $term);
//         });
//     }

//     // ✅ 7. Paginate
//     $haymakings = $q->orderBy('h.harvest_date', 'desc')
//         ->paginate(10)
//         ->appends($request->only(['block_name', 'search', 'site_id']));

//     // ✅ 8. Machines and tractors
//     $allMachines = DB::table('master_machine')->pluck('machine_name', 'id');
//     $allTractors = DB::table('master_tractors')->pluck('tractor_name', 'id');

//     foreach ($haymakings as $haymaking) {
//         $key = $haymaking->block_name . '|' . $haymaking->plot_name . '|' . $haymaking->seed_name;

//         // ✅ Correct total available yield from harvest_store_manage (per block+plot+seed_name)
//         $haymaking->total_available_yield = $totalAvailableYields[$key]->total_available_yield ?? 0;

//         // ✅ Required Yield MT (from hay_making)
//         $haymaking->required_yield_mt = $haymaking->yield_mt;

//         // ✅ Adjusted Yield MT (20% of original yield)
//         $haymaking->adjusted_yield_mt = $haymaking->required_yield_mt * 0.2;

//         // ✅ Remaining Yield MT
//         $haymaking->remaining_yield_mt = $haymaking->total_available_yield - $haymaking->required_yield_mt;

//         // ✅ Machine names
//         if (!empty($haymaking->machine_id)) {
//             $machineIds = explode(',', $haymaking->machine_id);
//             $haymaking->machine_names_display = collect($machineIds)->map(function ($id) use ($allMachines) {
//                 return $allMachines[trim($id)] ?? null;
//             })->filter()->implode(', ') ?: '-';
//         } else {
//             $haymaking->machine_names_display = '-';
//         }

//         // ✅ Tractor names
//         if (!empty($haymaking->tractor_id)) {
//             $tractorIds = explode(',', $haymaking->tractor_id);
//             $haymaking->tractor_names_display = collect($tractorIds)->map(function ($id) use ($allTractors) {
//                 return $allTractors[trim($id)] ?? null;
//             })->filter()->implode(', ') ?: '-';
//         } else {
//             $haymaking->tractor_names_display = '-';
//         }

//         // ✅ Spare parts JSON parsing
//         $haymaking->parsed_major_maintenance = [];
//         if ($haymaking->major_maintenance) {
//             $decoded = json_decode($haymaking->major_maintenance, true);
//             if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
//                 $haymaking->parsed_major_maintenance = $decoded;
//             }
//         }

//         // ✅ Harvest date (optional)
//         $haymaking->harvest_date = $haymaking->harvest_date;
//     }

//     // ✅ 9. Return blade or partial view
//     if ($request->ajax()) {
//         return view('haymaking.partials.table', compact('haymakings'))->render();
//     }

//     return view('haymaking.index', compact('blocks', 'haymakings', 'sites', 'selectedSiteId', 'role'));
// }
public function index(Request $request)
{
    $user = Auth::user();
    $role = $user->role;
    $siteId = $user->site_name;
    
    // ✅ 1. Load all sites (for admin dropdown)
    $sites = DB::table('master_sites')->pluck('site_name', 'id');
    
    // ✅ 2. Determine selected site
    $selectedSiteId = $request->input('site_id');
    if ($role != 1) {
        $selectedSiteId = $siteId;
    }
    
    // ✅ 3. Grab distinct blocks for dropdown filter from hay_making table
    $blocksQuery = DB::table('hay_making')
        ->select('block_name')->distinct()->orderBy('block_name');
    
    if (!empty($selectedSiteId)) {
        $blocksQuery->where('site_id', $selectedSiteId);
    }
    $blocks = $blocksQuery->pluck('block_name');
    
    // ✅ 4. Update total_yield_mt for all hay_making records using sequential logic
    $this->updateTotalYieldMt($selectedSiteId);
    
    // ✅ 5. Get Current Remaining Yield per seed_name from harvest_store_manage 
    // (product_id = 1 for harvested products, matching by seed_name and site)
    $currentRemainingYieldsQuery = DB::table('harvest_store_manage')
        ->select('seed_name', DB::raw('SUM(yield_mt) as current_remaining_yield'))
        ->where('product_id', 1) // Only harvested products
        ->groupBy('seed_name');
    
    if (!empty($selectedSiteId)) {
        $currentRemainingYieldsQuery->where('site_id', $selectedSiteId);
    }
    
    $currentRemainingYields = $currentRemainingYieldsQuery->pluck('current_remaining_yield', 'seed_name');
    
    // ✅ 6. Main query - Get data from hay_making table
    $q = DB::table('hay_making as h')
        ->select(
            'h.*', 
            'users.name as user_name'
        )
        ->leftJoin('users', 'h.user_id', '=', 'users.id');
    
    if (!empty($selectedSiteId)) {
        $q->where('h.site_id', $selectedSiteId);
    }
    
    // ✅ 7. Filters
    if ($request->filled('block_name')) {
        $q->where('h.block_name', $request->block_name);
    }
    
    if ($request->filled('search')) {
        $term = '%' . $request->search . '%';
        $q->where(function ($sub) use ($term) {
            $sub->where('h.plot_name', 'like', $term)
                ->orWhere('h.block_name', 'like', $term)
                ->orWhere('h.seed_name', 'like', $term)
                ->orWhere('h.hay_making_method', 'like', $term)
                ->orWhere('users.name', 'like', $term);
        });
    }
    
    // ✅ Session filter - FIXED: Using correct query variable $q instead of $query
    if ($request->filled('session')) {
        $session = $request->session;
        switch ($session) {
            case 'kharif':
                $q->whereMonth('h.harvest_date', '>=', 6)
                  ->whereMonth('h.harvest_date', '<=', 10);
                break;
            case 'rabi':
                $q->where(function($subQuery) {
                    $subQuery->whereMonth('h.harvest_date', '>=', 11)
                             ->orWhereMonth('h.harvest_date', '<=', 3);
                });
                break;
            case 'zaid':
                $q->whereMonth('h.harvest_date', '>=', 4)
                  ->whereMonth('h.harvest_date', '<=', 6);
                break;
        }
    }
    
    // ✅ 8. Paginate
    $haymakings = $q->orderBy('h.harvest_date', 'desc')
        ->paginate(10)
        ->appends($request->only(['block_name', 'search', 'site_id', 'session']));
    
    // ✅ 9. Load machines and tractors
    $allMachines = DB::table('master_machine')->pluck('machine_name', 'id');
    $allTractors = DB::table('master_tractors')->pluck('tractor_name', 'id');
    
    // ✅ 10. Process display fields and calculate yield metrics
    foreach ($haymakings as $haymaking) {
        
        // Calculate yield metrics based on seed_name matching
        $seedName = $haymaking->seed_name;
        
        // ✅ NEW LOGIC: Total Available Yield from hay_making table total_yield_mt
        $haymaking->total_available_yield = $haymaking->total_yield_mt ?? 0;
        
        // Required Yield MT (from hay_making table yield_mt)
        $haymaking->required_yield_mt = $haymaking->yield_mt ?? 0;
        
        // Adjusted Yield MT (Required Yield MT से 80% कम, मतलब 20% बचे)
        $haymaking->adjusted_yield_mt = $haymaking->required_yield_mt * 0.2;
        
        // ✅ NEW LOGIC: Remaining Yield MT from current harvest_store_manage yield_mt
        $haymaking->remaining_yield_mt = $currentRemainingYields[$seedName] ?? 0;
        
        // Handle machine names
        if (!empty($haymaking->machine_id)) {
            $machineIds = explode(',', $haymaking->machine_id);
            $haymaking->machine_names_display = collect($machineIds)->map(function($id) use ($allMachines) {
                return $allMachines[trim($id)] ?? null;
            })->filter()->implode(', ') ?: '-';
        } else {
            $haymaking->machine_names_display = '-';
        }
        
        // Handle tractor names
        if (!empty($haymaking->tractor_id)) {
            $tractorIds = explode(',', $haymaking->tractor_id);
            $haymaking->tractor_names_display = collect($tractorIds)->map(function($id) use ($allTractors) {
                return $allTractors[trim($id)] ?? null;
            })->filter()->implode(', ') ?: '-';
        } else {
            $haymaking->tractor_names_display = '-';
        }
        
        // Handle major maintenance JSON
        $haymaking->parsed_major_maintenance = [];
        if ($haymaking->major_maintenance) {
            $decoded = json_decode($haymaking->major_maintenance, true);
            if (json_last_error() === JSON_ERROR_NONE && is_array($decoded)) {
                $haymaking->parsed_major_maintenance = $decoded;
            }
        }
        
        // Map harvest_date from hay_making table
        $haymaking->harvest_date = $haymaking->harvest_date;
    }
    
    // ✅ Handle AJAX requests
    if ($request->ajax()) {
        return view('haymaking.partials.table', compact('haymakings'))->render();
    }
    
    // ✅ Return view
    return view('haymaking.index', compact(
        'blocks',
        'haymakings',
        'sites',
        'selectedSiteId',
        'role'
    ));
}

/**
 * Update total_yield_mt in hay_making table
 * Logic: total_yield_mt should show available yield at the time of each activity
 */
private function updateTotalYieldMt($selectedSiteId = null)
{
    // Get all hay_making records ordered by date to calculate sequential remaining yields
    $haymakingQuery = DB::table('hay_making')
        ->select('*')
        ->orderBy('harvest_date', 'asc')
        ->orderBy('id', 'asc');
    
    if (!empty($selectedSiteId)) {
        $haymakingQuery->where('site_id', $selectedSiteId);
    }
    
    $haymakingRecords = $haymakingQuery->get();
    
    // Group by seed_name and site_id to process each combination separately
    $groupedRecords = $haymakingRecords->groupBy(function($item) {
        return $item->seed_name . '_' . $item->site_id;
    });
    
    foreach ($groupedRecords as $key => $records) {
        $firstRecord = $records->first();
        $seedName = $firstRecord->seed_name;
        $siteId = $firstRecord->site_id;
        
        // Get original harvest yield (before any activities)
        $originalYield = DB::table('harvest_store_manage')
            ->where('seed_name', $seedName)
            ->where('site_id', $siteId)
            ->where('product_id', 1) // Only harvested products
            ->sum('yield_mt');
        
        // Get total used in all activities to calculate original yield
        $totalUsedInActivities = DB::table('hay_making')
            ->where('seed_name', $seedName)
            ->where('site_id', $siteId)
            ->sum('yield_mt');
        
        // Calculate original yield (current available + total used)
        $originalTotalYield = $originalYield + $totalUsedInActivities;
        
        // Process each record sequentially to calculate available yield at time of activity
        $cumulativeUsed = 0;
        
        foreach ($records as $record) {
            // Available yield at the time of this activity = original - cumulative used before this activity
            $availableAtTimeOfActivity = $originalTotalYield - $cumulativeUsed;
            
            // Update this record with the available yield at time of activity
            DB::table('hay_making')
                ->where('id', $record->id)
                ->update(['total_yield_mt' => $availableAtTimeOfActivity]);
            
            // Add current activity's usage to cumulative
            $cumulativeUsed += $record->yield_mt;
        }
    }
}

}