000

<?php
namespace App\Http\Controllers\Admin;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log; // Added for debugging
use Auth;
use Carbon\Carbon;
use Illuminate\Pagination\LengthAwarePaginator;

use Illuminate\Support\Facades\Validator;
class MasterController extends Controller
{

public function consolidated(Request $request)
{
    $currentUser = Auth::user();
    
    // Role-based site access logic
    if ($currentUser->role == 1) {
        // Role 1: Can see all sites data
        $siteCondition = function($query) {
            // No site restriction for role 1
        };
        $siteId = null; // Will be used for queries without join
    } else {
        // Roles 2, 3, 4: Can only see their own site_id data
        $siteCondition = function($query) use ($currentUser) {
            $query->where('site_id', $currentUser->site_id);
        };
        $siteId = $currentUser->site_id;
    }
    
    // Get all blocks and plots based on role
    $plotsQuery = DB::table('master_plots as mp')
        ->join('master_block as mb', 'mp.block_id', '=', 'mb.id')
        ->select('mp.id', 'mb.block_name', 'mp.plot_name', 'mp.area', 'mb.site_id');
    
    if ($currentUser->role != 1) {
        $plotsQuery->where('mb.site_id', $siteId);
    }
    
    $plots = $plotsQuery->orderBy('mb.block_name')
        ->orderBy('mp.plot_name')
        ->get();
    
    // ✅ Total area for first card
    $grandTotal = $plots->sum('area');
    
    // Seed usage with role-based filtering
    $seedUsageQuery = DB::table('showing_oprations')
        ->select('block_name', 'plot_name', DB::raw('SUM(seed_consumption) as total_seed'));
    
    if ($currentUser->role != 1) {
        $seedUsageQuery->where('site_id', $siteId);
    }
    
    $seedUsage = $seedUsageQuery->groupBy('block_name', 'plot_name')
        ->get()
        ->keyBy(fn($s) => $s->block_name . '_' . $s->plot_name);
    
    // Fertilizer usage with role-based filtering
    $fertilizerUsageQuery = DB::table('fertilizer_soil_record')
        ->select('block_name', 'plot_name', DB::raw('SUM(fertilizer_quantity) as total_fertilizer'));
    
    if ($currentUser->role != 1) {
        $fertilizerUsageQuery->where('site_id', $siteId);
    }
    
    $fertilizerUsage = $fertilizerUsageQuery->groupBy('block_name', 'plot_name')
        ->get()
        ->keyBy(fn($f) => $f->block_name . '_' . $f->plot_name);
    
    // Final formatted data
    $processedPlots = $plots->map(function ($plot, $index) use ($seedUsage, $fertilizerUsage) {
        $key = $plot->block_name . '_' . $plot->plot_name;
        $plot->sr_no = $index + 1;
        $plot->seed_used = isset($seedUsage[$key]) ? $seedUsage[$key]->total_seed : 0;
        $plot->fertilizer_used = isset($fertilizerUsage[$key]) ? $fertilizerUsage[$key]->total_fertilizer : 0;
        
        if ($plot->seed_used > 0 && $plot->fertilizer_used > 0) {
            $plot->stage = 'harvesting';
        } elseif ($plot->seed_used > 0) {
            $plot->stage = 'growing';
        } else {
            $plot->stage = 'planted';
        }
        
        $plot->supervisor = 'Not Assigned';
        return $plot;
    });
    
    // ✅ Group by block name
    $groupedPlots = $processedPlots->groupBy('block_name');
    
    // Info cards count with role-based filtering
    $landsCountQuery = DB::table('master_land');
    $seedsCountQuery = DB::table('master_seed');
    $machinesCountQuery = DB::table('master_machine');
    $fertilizersCountQuery = DB::table('master_fertilizer');
    
    // ✅ FIXED: Tractor count query using correct table name
    $tractorsCountQuery = DB::table('master_tractors'); // Using correct table name
    
    if ($currentUser->role != 1) {
        $landsCountQuery->where('site_id', $siteId);
        $seedsCountQuery->where('site_id', $siteId);
        $machinesCountQuery->where('site_id', $siteId);
        $fertilizersCountQuery->where('site_id', $siteId);
        $tractorsCountQuery->where('site_id', $siteId); // ✅ Apply role-based filtering to tractors
    }
    
    $landsCount = $landsCountQuery->count();
    $seedsCount = $seedsCountQuery->count();
    $machinesCount = $machinesCountQuery->count();
    $fertilizersCount = $fertilizersCountQuery->count();
    $tractorsCount = $tractorsCountQuery->count(); // ✅ Get tractor count from master_tractors table
    
    // Dropdown filter with role-based filtering
    $blocksQuery = DB::table('master_block');
    
    if ($currentUser->role != 1) {
        $blocksQuery->where('site_id', $siteId);
    }
    
    $blocks = $blocksQuery->pluck('block_name');
    
    // ✅ Pass everything to view (including tractorsCount)
    return view('admin.consolidated', compact(
        'groupedPlots',
        'currentUser',
        'landsCount',
        'seedsCount',
        'machinesCount',
        'fertilizersCount',
        'tractorsCount', // ✅ Add tractorsCount to compact
        'blocks',
        'grandTotal'
    ));
}
   
//==============================================================================================================================================

protected $soilTypes = [
    'Loamy', 'Sandy', 'Clay', 
    'Silty', 'Peaty', 'Chalky'
];

// ==============================================================================
// VIEW LAND RECORDS

// public function indexland(Request $request)
// {
//     $user = Auth::user();
//     $role = $user->role;
//     $userSiteId = $user->site_name; // Assuming site_name contains site_id, correct if needed

//     // Prepare sites dropdown
//     $sitesQuery = DB::table('master_sites')->orderBy('site_name');
//     if ($role == 2) {
//         $sitesQuery->where('id', 1);
//     } elseif ($role == 3) {
//         $sitesQuery->where('id', 2);
//     } elseif ($role == 4) {
//         // Role 4 can only see site_id 3
//         $sitesQuery->where('id', 3);
//     }
//     $sites = $sitesQuery->get();

//     // Determine applicable site_id for filter
//     $siteId = null;
//     if ($role == 2) {
//         $siteId = 1;
//     } elseif ($role == 3) {
//         $siteId = 2;
//     } elseif ($role == 4) {
//         // Role 4 is restricted to site_id 3
//         $siteId = 3;
//     } elseif ($role == 1 && $request->filled('site_id')) {
//         $siteId = $request->site_id;
//     }

//     // Filter blocks site-wise
//     $blocksQuery = DB::table('master_block')->orderBy('block_name');
//     if ($siteId) {
//         $blocksQuery->where('site_id', $siteId);
//     }
//     $blocks = $blocksQuery->get();

//     // Years range
//     $years = range(date('Y') - 5, date('Y') + 5);

//     // Initialize land query with site restriction
//     $query = DB::table('master_land')->where('is_deleted', 0);
//     if ($siteId) {
//         $query->where('site_id', $siteId);
//     }

//     // Block filter
//     if ($request->filled('block')) {
//         $query->where('block_name', $request->block);
//     }

//     // Year filter
//     if ($request->filled('year')) {
//         $query->whereYear('created_at', $request->year);
//     }

//     // Search filter
//     if ($request->filled('search')) {
//         $search = $request->search;
//         $query->where(function($q) use ($search) {
//             $q->where('plot_no', 'like', "%{$search}%")
//               ->orWhere('block_name', 'like', "%{$search}%")
//               ->orWhere('super_wiser_name', 'like', "%{$search}%")
//               ->orWhere('soil_type', 'like', "%{$search}%")
//               ->orWhere('previous_crop', 'like', "%{$search}%")
//               ->orWhere('current_crop', 'like', "%{$search}%");
//         });
//     }

//     $lands = $query->orderBy('id', 'desc')->paginate(10);

//     return view('admin.land', compact('lands', 'blocks', 'years', 'sites', 'role'));
// }
public function indexland(Request $request)
{
    $user = Auth::user();
    $role = $user->role;
    $userSiteId = $user->site_name; // Adjust if actual site_id column is different

// Prepare sites dropdown
    $sitesQuery = DB::table('master_sites')->orderBy('site_name');
    if ($role == 2) {
        $sitesQuery->where('id', 1);
    } elseif ($role == 3) {
        $sitesQuery->where('id', 2);
    } elseif ($role == 4) {
        $sitesQuery->where('id', 3);
    } elseif ($role == 5) {
        $sitesQuery->where('id', 4);
    }
    $sites = $sitesQuery->get();

// Determine applicable site_id for filter
    $siteId = null;
    if ($role == 2) {
        $siteId = 1;
    } elseif ($role == 3) {
        $siteId = 2;
    } elseif ($role == 4) {
        $siteId = 3;
    } elseif ($role == 5) {
        $siteId = 4;
    } elseif ($role == 1 && $request->filled('site_id')) {
        $siteId = $request->site_id;
    }

// Filter blocks site-wise
    $blocksQuery = DB::table('master_block')->orderBy('block_name');
    if ($siteId) {
        $blocksQuery->where('site_id', $siteId);
    }
    $blocks = $blocksQuery->get();

// Years range
    $years = range(date('Y') - 5, date('Y') + 5);

// Initialize land query with site restriction
    $query = DB::table('master_land')->where('is_deleted', 0);
    if ($siteId) {
        $query->where('site_id', $siteId);
    }

// Block filter
    if ($request->filled('block')) {
        $query->where('block_name', $request->block);
    }

// Year filter
    if ($request->filled('year')) {
        $query->whereYear('created_at', $request->year);
    }

// Search filter
    if ($request->filled('search')) {
        $search = $request->search;
        $query->where(function($q) use ($search) {
            $q->where('plot_no', 'like', "%{$search}%")
              ->orWhere('block_name', 'like', "%{$search}%")
              ->orWhere('super_wiser_name', 'like', "%{$search}%")
              ->orWhere('soil_type', 'like', "%{$search}%")
              ->orWhere('previous_crop', 'like', "%{$search}%")
              ->orWhere('current_crop', 'like', "%{$search}%");
        });
    }

$lands = $query->orderBy('id', 'desc')->paginate(10);

return view('admin.land', compact('lands', 'blocks', 'years', 'sites', 'role'));
}

// ==============================================================================
// STORE LAND RECORD
// public function storeland(Request $request)
// {
//     $validator = Validator::make($request->all(), [
//         'block_name' => 'required',

//         'plot_no' => 'required',
//         'area_ha' => 'required|numeric',
//         'super_wiser_name' => 'required',
//         'soil_type' => 'required',
//         'previous_crop' => 'required',
//         'current_crop' => 'required',
//     ]);
    
//     if ($validator->fails()) {
//         return response()->json(['errors' => $validator->errors()], 422);
//     }
    
//     try {
//         DB::beginTransaction();
        
//         DB::table('master_land')->insert([
//             'plot_no' => $request->plot_no,
//             'block_name' => $request->block_name,
//     'site_id' => $request->site_id, // <-- add this line

//             'super_wiser_name' => $request->super_wiser_name,
//             'area_ha' => $request->area_ha,
//             'soil_type' => $request->soil_type,
//             'previous_crop' => $request->previous_crop,
//             'current_crop' => $request->current_crop,
//             'created_at' => now(),
//             'updated_at' => now(),
//         ]);
        
//         DB::commit();
        
//         return response()->json(['success' => 'Land record added successfully.']);
//     } catch (\Exception $e) {
//         DB::rollBack();
//         return response()->json(['error' => 'Failed to add land record. ' . $e->getMessage()], 500);
//     }
// }
public function storeland(Request $request)
{
    $validator = Validator::make($request->all(), [
        'block_name' => 'required',
        'plot_no' => 'required',
        'area_ha' => 'required|numeric',
        'super_wiser_name' => 'required',
        'soil_type' => 'required',
        'previous_crop' => 'required',
        'current_crop' => 'required',
        'site_id' => 'required',
    ]);

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

try {
        // Check if the same block_name and plot_no already exist in the database
        $exists = DB::table('master_land')
            ->where('block_name', $request->block_name)
            ->where('plot_no', $request->plot_no)
            ->exists();

if ($exists) {
            return response()->json(['error' => 'This block and plot already exist.'], 409);
        }

DB::beginTransaction();

DB::table('master_land')->insert([
            'plot_no' => $request->plot_no,
            'block_name' => $request->block_name,
            'site_id' => $request->site_id,
            'super_wiser_name' => $request->super_wiser_name,
            'area_ha' => $request->area_ha,
            'soil_type' => $request->soil_type,
            'previous_crop' => $request->previous_crop,
            'current_crop' => $request->current_crop,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

DB::commit();

return response()->json(['success' => 'Land record added successfully.']);
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json(['error' => 'Failed to add land record. ' . $e->getMessage()], 500);
    }
}

// ==============================================================================
// UPDATE LAND RECORD
public function updateland(Request $request, $id)
{
    $validator = Validator::make($request->all(), [
        'block_name' => 'required',
        'plot_no' => 'required',
        'area_ha' => 'required|numeric',
        'super_wiser_name' => 'required',
        'soil_type' => 'required',
        'previous_crop' => 'required',
        'current_crop' => 'required',
    ]);
    
    if ($validator->fails()) {
        return response()->json(['errors' => $validator->errors()], 422);
    }
    
    try {
        DB::beginTransaction();
        
        DB::table('master_land')
            ->where('id', $id)
            ->update([
                'plot_no' => $request->plot_no,
                'block_name' => $request->block_name,
                    'site_id' => $request->site_id, // <-- add this line

'super_wiser_name' => $request->super_wiser_name,
                'area_ha' => $request->area_ha,
                'soil_type' => $request->soil_type,
                'previous_crop' => $request->previous_crop,
                'current_crop' => $request->current_crop,
                'updated_at' => now(),
            ]);
        
        DB::commit();
        
        return response()->json(['success' => 'Land record updated successfully.']);
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json(['error' => 'Failed to update land record. ' . $e->getMessage()], 500);
    }
}
public function editland($id)
{
$land = DB::table('master_land')->where('id', $id)->first();
$blocks = DB::table('master_block')->orderBy('block_name')->get();
$plots = DB::table('master_plots')
            ->join('master_block', 'master_plots.block_id', '=', 'master_block.id')
            ->where('master_block.block_name', $land->block_name)
            ->select('master_plots.*')
            ->get();
            
return response()->json([
    'land' => $land,
    'blocks' => $blocks,
    'plots' => $plots
]);
}

public function getPlotArealand(Request $request)
{
$plot = DB::table('master_plots')
            ->where('plot_name', $request->plot_name)
            ->first();

if ($plot) {
    return response()->json(['area' => $plot->area]);
}

return response()->json(['area' => 0]);
}

public function getPlotsByBlockland(Request $request)
{
$block_id = DB::table('master_block')
              ->where('block_name', $request->block_name)
              ->value('id');
$plots = DB::table('master_plots')
           ->where('block_id', $block_id)
           ->get();
return response()->json($plots);
}

// ==============================================================================
// DELETE LAND RECORD (Soft Delete)
public function destroyland($id)
{
    try {
        DB::beginTransaction();
        
        DB::table('master_land')
            ->where('id', $id)
            ->update(['is_deleted' => 1]);
        
        DB::commit();
        
        return response()->json(['success' => 'Land record deleted successfully.']);
    } catch (\Exception $e) {
        DB::rollBack();
        return response()->json(['error' => 'Failed to delete land record. ' . $e->getMessage()], 500);
    }
}

//==============================================================================================================================================

public function seedIndex(Request $request)
{
    $user = Auth::user();
    $role = $user->role;
    $userSiteId = $user->site_id;
    $selectedSiteId = $request->input('site_id');

$baseQuery = DB::table('master_seed as ms1')
        ->select(
            'ms1.*',
            DB::raw(
                '(SELECT sale_price FROM master_seed as ms_prev
                  WHERE ms_prev.seed_name = ms1.seed_name
                    AND ms_prev.variety_of_seed = ms1.variety_of_seed
                    AND (
                         ms_prev.date_of_packing < ms1.date_of_packing OR
                         (ms_prev.date_of_packing = ms1.date_of_packing AND ms_prev.id < ms1.id)
                        )
                    AND ms_prev.is_deleted = 0
                  ORDER BY ms_prev.date_of_packing DESC, ms_prev.id DESC
                  LIMIT 1) as previous_sale_price'
            ),
            DB::raw('(ms1.seed_stock_kg * ms1.rate_of_seed) as total_amount_sale')
        )
        ->where('ms1.is_deleted', 0)
        ->whereRaw('ms1.id = (SELECT id FROM master_seed as ms_inner
                                WHERE ms_inner.seed_name = ms1.seed_name
                                AND ms_inner.variety_of_seed = ms1.variety_of_seed
                                AND ms_inner.is_deleted = 0
                                ORDER BY ms_inner.date_of_packing DESC, ms_inner.id DESC
                                LIMIT 1)');

if ($role == 1 && $selectedSiteId) {
        $baseQuery->where('ms1.site_id', $selectedSiteId);
    } elseif ($role != 1) {
        $baseQuery->where('ms1.site_id', $userSiteId);
    }

if ($request->filled('search')) {
        $search = $request->input('search');
        $baseQuery->where(function ($q) use ($search) {
            $q->where('ms1.seed_name', 'like', '%' . $search . '%')
              ->orWhere('ms1.variety_of_seed', 'like', '%' . $search . '%')
              ->orWhere('ms1.location', 'like', '%' . $search . '%');
        });
    }

$seeds = $baseQuery
        ->orderBy('ms1.seed_name', 'asc')
        ->orderBy('ms1.variety_of_seed', 'asc')
        ->paginate(15);

// Add weighted average calculation and stock history
    foreach ($seeds as $seed) {
        $history = DB::table('seed_stock_history')
            ->where('seed_id', $seed->id)
            ->orderBy('created_at', 'asc')
            ->get();

$seed->stockHistoryRecords = $history;

if ($history->isEmpty()) {
            $seed->calculated_amount = $seed->seed_stock_kg * $seed->rate_of_seed;
        } else {
            $totalQty = 0;
            $totalCost = 0;

foreach ($history as $h) {
                $qty = $h->add_new_seed_stock;
                $rate = $h->add_new_seed_rate;
                if ($qty !== null && $rate !== null) {
                    $totalQty += $qty;
                    $totalCost += ($qty * $rate);
                }
            }

$originalQty = $seed->seed_stock_kg - $totalQty;
            if ($originalQty > 0) {
                $totalQty += $originalQty;
                $totalCost += ($originalQty * $seed->rate_of_seed);
            }

$avgRate = $totalQty > 0 ? ($totalCost / $totalQty) : $seed->rate_of_seed;
            $seed->calculated_amount = $avgRate * $seed->seed_stock_kg;
        }
    }

// Sites for dropdown
    $sitesQuery = DB::table('master_sites')->orderBy('site_name');
    if ($role != 1) {
        $sitesQuery->where('id', $userSiteId);
    }
    $sites = $sitesQuery->get();

return view('admin.seed', compact('seeds', 'sites'));
}

// public function getSeedStockHistory($id)
// {
//     try {
//         // Fetch all stock history records for the given seed ID
//         // This matches master_seed.id with seed_stock_history.seed_id
//         $history = DB::table('seed_stock_history')
//             ->select([
//                 'seed_stock_history.*',
//                 'master_seed.seed_name',
//                 'master_seed.variety_of_seed'
//             ])
//             ->leftJoin('master_seed', 'seed_stock_history.seed_id', '=', 'master_seed.id')
//             ->where('seed_stock_history.seed_id', $id)
//             ->orderBy('seed_stock_history.created_at', 'desc')
//             ->get();

//         return response()->json([
//             'success' => true,
//             'history' => $history,
//             'total_records' => $history->count()
//         ]);
//     } catch (\Exception $e) {
//         return response()->json([
//             'success' => false,
//             'message' => 'Error fetching stock history: ' . $e->getMessage(),
//             'history' => []
//         ], 500);
//     }
// }
public function getSeedStockHistory($id)
{
try {
        Log::info('Fetching history for seed_id: ' . $id);

$masterSeed = DB::table('master_seed')
            ->where('id', $id)
            ->first();

if (!$masterSeed) {
            return response()->json(['message' => 'Seed not found.'], 404);
        }

$history = DB::table('seed_stock_history as ssh')
            ->select([
                'ssh.id',
                'ssh.seed_id',
                'ssh.date_of_packing',
                'ssh.type_of_packing',
                'ssh.packing_size',
                'ssh.seed_stock_kg',
                'ssh.add_new_seed_stock',
                'ssh.rate_of_seed',
                'ssh.add_new_seed_rate',
                'ssh.uom',
                'ssh.site_id',
                'ssh.created_at',
                'ssh.updated_at',
                'ms.seed_name',
                'ms.variety_of_seed'
            ])
            ->leftJoin('master_seed as ms', 'ssh.seed_id', '=', 'ms.id')
            ->where('ssh.seed_id', $id)
            ->orderBy('ssh.created_at', 'desc')
            ->get();

// ✅ Format existing history dates
        $history = $history->map(function ($item) {
            $item->date_of_packing = \Carbon\Carbon::parse($item->date_of_packing)->format('j /n/y');
            return $item;
        });

// ✅ Create virtual entry from master_seed (ALWAYS insert at top)
        $initialRecord = (object)[
            'id' => null,
            'seed_id' => $masterSeed->id,
            'date_of_packing' => \Carbon\Carbon::parse($masterSeed->date_of_packing)->format('j /n/y'),
            'type_of_packing' => $masterSeed->type_of_packing,
            'packing_size' => $masterSeed->packing_size,
            'seed_stock_kg' => $masterSeed->seed_stock_kg,
            'add_new_seed_stock' => 0,
            'rate_of_seed' => $masterSeed->rate_of_seed,
            'add_new_seed_rate' => null,
            'uom' => $masterSeed->uom,
            'site_id' => $masterSeed->site_id,
            'created_at' => $masterSeed->created_at,
            'updated_at' => $masterSeed->updated_at,
            'seed_name' => $masterSeed->seed_name,
            'variety_of_seed' => $masterSeed->variety_of_seed,
        ];

// ✅ Add at beginning (prepend)
        $history->prepend($initialRecord);

// ✅ Debug output (optional)
        // dd($history);

return response()->json([
            'success' => true,
            'master_seed' => $masterSeed,
            'history' => $history,
            'total_records' => $history->count()
        ]);
    } catch (\Exception $e) {
        Log::error('Error fetching seed history: ' . $e->getMessage());
        return response()->json(['message' => 'Error fetching seed history.', 'error' => $e->getMessage()], 500);
    }
}

// Alternative method with more detailed information
public function getSeedHistory($id)
{
    try {
        Log::info('Fetching history for seed_id: ' . $id);

$masterSeed = DB::table('master_seed')
            ->where('id', $id)
            ->first();

if (!$masterSeed) {
            return response()->json(['message' => 'Seed not found.'], 404);
        }

// ✅ Format date_of_packing
        $history = $history->map(function ($item) {
            $item->date_of_packing = \Carbon\Carbon::parse($item->date_of_packing)->format('j -n-y');
            return $item;
        });

Log::info('Found ' . $history->count() . ' history records for seed_id: ' . $id);
        Log::info('History data: ' . json_encode($history));

public function getSeedStockHistoryDetailed($id)
    {
        try {
            $masterSeed = DB::table('master_seed')
                ->where('id', $id)
                ->first();

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

$history = DB::table('seed_stock_history as ssh')
                ->select([
                    'ssh.id',
                    'ssh.seed_id',
                    'ssh.date_of_packing',
                    'ssh.type_of_packing',
                    'ssh.packing_size',
                    'ssh.seed_stock_kg',
                    'ssh.add_new_seed_stock',
                    'ssh.rate_of_seed',
                    'ssh.add_new_seed_rate',
                    'ssh.uom',
                    'ssh.site_id',
                    'ssh.created_at',
                    'ssh.updated_at',
                    'ms.seed_name',
                    'ms.variety_of_seed',
                    'sites.site_name as location'
                ])
                ->leftJoin('master_seed as ms', 'ssh.seed_id', '=', 'ms.id')
                ->leftJoin('sites', 'ssh.site_id', '=', 'sites.id')
                ->where('ssh.seed_id', $id)
                ->orderBy('ssh.created_at', 'desc')
                ->get();

return response()->json([
                'success' => true,
                'master_seed' => $masterSeed,
                'history' => $history,
                'total_records' => $history->count()
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => 'Error fetching stock history: ' . $e->getMessage(),
                'history' => []
            ], 500);
        }
    }
public function storeseed(Request $request)
    {
        $validated = $request->validate([
            'seed_name' => 'required|string|max:255',
            'variety_of_seed' => 'required|string|max:255',
            'purpose' => 'nullable|string|max:255',
            'site_id' => 'required|integer|exists:master_sites,id',

DB::table('master_seed')->insert($validated);

return redirect()->route('seeds.index')
            ->with('success', 'Seed stock added successfully');
    }

//     public function addStock(Request $request, $id)
// {
//     try {
//         // Validate input
//         $validated = $request->validate([
//             'quantity_to_add' => 'required|numeric|min:0.01',
//             'new_rate_of_seed' => 'nullable|numeric|min:0',
//             'new_sale_price' => 'nullable|numeric|min:0',
//             'new_location' => 'nullable|string|max:255',
//             'new_remark' => 'nullable|string',
//         ]);

//         // Fetch existing seed
//         $seed = DB::table('master_seed')
//             ->where('id', $id)
//             ->where('is_deleted', 0)
//             ->first();

//         if (!$seed) {
//             return redirect()->route('seeds.index')->with('error', 'Seed not found.');
//         }

//         // Begin database transaction
//         DB::beginTransaction();

//         // Prepare data for history table
//         $historyData = [
//             'seed_id' => $id,
//             'seed_name' => $seed->seed_name,
//             'variety_of_seed' => $seed->variety_of_seed,
//             'type_of_packing' => $seed->type_of_packing,
//             'packing_size' => $seed->packing_size,
//             'date_of_packing' => now()->toDateString(),
//             'seed_stock_kg' => $seed->seed_stock_kg,
//             'add_new_seed_stock' => $validated['quantity_to_add'],
//             'rate_of_seed' => $seed->rate_of_seed, // Original rate from master_seed
//             'add_new_seed_rate' => $validated['new_rate_of_seed'] ?? $seed->rate_of_seed, // New rate or original if not provided
//             'uom' => $seed->uom,
//             'site_id' => $seed->site_id,
//             'created_at' => now(),
//             'updated_at' => now(),
//         ];

//         // Insert into seed_stock_history
//         DB::table('seed_stock_history')->insert($historyData);

//         // Calculate new stock & prepare update
//         $newStock = $seed->seed_stock_kg + $validated['quantity_to_add'];
//         $updateData = [
//             'seed_stock_kg' => $newStock,
//             // Do not update rate_of_seed, sale_price, location, or remark
//         ];

//         // Update master_seed
//         DB::table('master_seed')
        
//             ->where('id', $id)
//             ->update($updateData);

//         // Commit transaction
//         DB::commit();

//         // Prepare success message
//         $message = 'Stock added successfully for ' . $seed->seed_name . ' (' . $seed->variety_of_seed . '). ';

//         return redirect()->route('seeds.index')->with('success', $message);

//     } catch (\Exception $e) {
//         // Rollback transaction on error
//         DB::rollBack();
//         return redirect()->route('seeds.index')->with('error', 'Failed to add stock: ' . $e->getMessage());
//     }
// }
public function addStock(Request $request, $id)
{
    try {
        // Validate input
        $validated = $request->validate([
            'quantity_to_add' => 'required|numeric|min:0.01',
            'new_rate_of_seed' => 'nullable|numeric|min:0',
            'new_sale_price' => 'nullable|numeric|min:0',
            'new_location' => 'nullable|string|max:255',
            'new_remark' => 'nullable|string',
        ]);

// Fetch existing seed
        $seed = DB::table('master_seed')
            ->where('id', $id)
            ->where('is_deleted', 0)
            ->first();

if (!$seed) {
            return redirect()->route('seeds.index')->with('error', 'Seed not found.');
        }

// Begin transaction
        DB::beginTransaction();

// ✅ Check if this is the first stock addition
        $historyExists = DB::table('seed_stock_history')
            ->where('seed_id', $id)
            ->exists();

if (!$historyExists) {
            // Insert initial record from master_seed
            DB::table('seed_stock_history')->insert([
                'seed_id' => $id,
                'seed_name' => $seed->seed_name,
                'variety_of_seed' => $seed->variety_of_seed,
                'type_of_packing' => $seed->type_of_packing,
                'packing_size' => $seed->packing_size,
                'date_of_packing' => $seed->date_of_packing ?? now()->toDateString(),
                'seed_stock_kg' => $seed->seed_stock_kg,
                'add_new_seed_stock' => 0,
                'rate_of_seed' => $seed->rate_of_seed,
                'add_new_seed_rate' => null,
                'uom' => $seed->uom,
                'site_id' => $seed->site_id,
                'created_at' => now(),
                'updated_at' => now(),
            ]);
        }

// Prepare data for new stock addition
        $historyData = [
            'seed_id' => $id,
            'seed_name' => $seed->seed_name,
            'variety_of_seed' => $seed->variety_of_seed,
            'type_of_packing' => $seed->type_of_packing,
            'packing_size' => $seed->packing_size,
            'date_of_packing' => now()->toDateString(),
            'seed_stock_kg' => $seed->seed_stock_kg,
            'add_new_seed_stock' => $validated['quantity_to_add'],
            'rate_of_seed' => $seed->rate_of_seed,
            'add_new_seed_rate' => $validated['new_rate_of_seed'] ?? $seed->rate_of_seed,
            'uom' => $seed->uom,
            'site_id' => $seed->site_id,
            'created_at' => now(),
            'updated_at' => now(),
        ];

// Insert into history
        DB::table('seed_stock_history')->insert($historyData);

// Calculate new stock & update master
        $newStock = $seed->seed_stock_kg + $validated['quantity_to_add'];
        $updateData = [
            'seed_stock_kg' => $newStock,
        ];

DB::table('master_seed')
            ->where('id', $id)
            ->update($updateData);

DB::commit();

$message = 'Stock added successfully for ' . $seed->seed_name . ' (' . $seed->variety_of_seed . ').';

return redirect()->route('seeds.index')->with('success', $message);

} catch (\Exception $e) {
        DB::rollBack();
        return redirect()->route('seeds.index')->with('error', 'Failed to add stock: ' . $e->getMessage());
    }
}

public function updateseed(Request $request, $id)
{
    // Validate incoming request data
    $validatedNewData = $request->validate([
        'seed_name' => 'required|string|max:255',
        'variety_of_seed' => 'required|string|max:255',
        'site_id' => 'required|integer|exists:master_sites,id',

// Check if seed record exists
    $originalSeed = DB::table('master_seed')->where('id', $id)->first();

if (!$originalSeed) {
        return redirect()->route('seeds.index')
            ->with('error', 'Seed record not found for update.');
    }

// Always update the existing record – no duplicate insert
    DB::table('master_seed')
        ->where('id', $id)
        ->update($validatedNewData);

return redirect()->route('seeds.index')
        ->with('success', 'Seed data updated successfully for ID: ' . $id);
}

public function destroyseed($id)
{
    DB::table('master_seed')
      ->where('id', $id)
      ->update(['is_deleted' => 1]);

return redirect()->route('seeds.index')
        ->with('success', 'Seed deleted successfully');
}

public function searchseed(Request $request)
{
$search = $request->input('search');

$seeds = DB::table('master_seed')
    ->where('seed_name', 'LIKE', "%{$search}%")
    ->orWhere('variety_of_seed', 'LIKE', "%{$search}%")
    ->orWhere('purpose', 'LIKE', "%{$search}%")
    ->orWhere('location', 'LIKE', "%{$search}%")
    ->paginate(10);
$sites = DB::table('master_sites')->orderBy('site_name')->get();

return view('admin.seed', compact('seeds','sites'));
}

//==============================================================================================================================================

// machine add, update, delete

public function machine(Request $request)
{
    try {
        $user = Auth::user();
        $role = $user->role;
        $userSiteId = $user->site_name; // Make sure this is the correct site_id field
        $selectedSiteId = $request->input('site_id'); // From dropdown
        $search = $request->get('search');
        $perPage = 15;

// --- Machine Query ---
        $query = DB::table('master_machine as m')
            ->leftJoin('master_sites as s', 'm.site_id', '=', 's.id')
            ->select('m.*', 's.site_name');

// ✅ Role-based site filtering
        if ($role == 1 && $selectedSiteId) {
            $query->where('m.site_id', $selectedSiteId); // Admin filtered by dropdown
        } elseif ($role != 1) {
            $query->where('m.site_id', $userSiteId); // Others only their site
        }

// 🔍 Search filter
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('m.machine_name', 'LIKE', "%{$search}%")
                  ->orWhere('m.machine_no', 'LIKE', "%{$search}%")
                  ->orWhere('m.machine_type', 'LIKE', "%{$search}%")
                  ->orWhere('m.brand_model_no', 'LIKE', "%{$search}%")
                  ->orWhere('m.status', 'LIKE', "%{$search}%")
                  ->orWhere('s.site_name', 'LIKE', "%{$search}%");
            });
        }

$machines = $query->orderBy('m.created_at', 'desc')->paginate($perPage);

// --- User List (filtered like machine list) ---
        $usersQuery = DB::table('users as u')
            ->leftJoin('master_sites as s', 'u.site_id', '=', 's.id')
            ->select('u.*', 's.site_name');

if ($role == 1 && $selectedSiteId) {
            $usersQuery->where('u.site_id', $selectedSiteId);
        } elseif ($role != 1) {
            $usersQuery->where('u.site_id', $userSiteId);
        }

$users = $usersQuery->orderBy('u.created_at', 'desc')->get();

// --- Site Dropdown List ---
        $sitesQuery = DB::table('master_sites')->select('id', 'site_name')->orderBy('site_name');

if ($role != 1) {
            $sitesQuery->where('id', $userSiteId); // Show only user's site if not admin
        }

$sites = $sitesQuery->get();

return view('admin.machine', compact('machines', 'users', 'sites'));

} catch (\Exception $e) {
        return back()->with('error', 'Error loading data: ' . $e->getMessage());
    }
}

/**
     * Store a newly created machine
     */
public function store(Request $request)
{
    $validator = Validator::make($request->all(), [
        'machine_name' => 'required|string|max:255',
        'machine_no' => 'required|string|max:100|unique:master_machine,machine_no',
        'site_id' => 'required|exists:master_sites,id',
        'machine_type' => 'required|string|max:255',
        'brand_model_no' => 'required|string|max:255',
        'purchase_date' => 'required|date',
        'status' => 'required|in:active,inactive,maintenance',
        'last_service_date' => 'nullable|date',
        'next_service_date' => 'nullable|date|after_or_equal:last_service_date',
        'document' => 'nullable|file|mimes:pdf,doc,docx,jpg,jpeg,png|max:5120',
        'image' => 'nullable|image|mimes:jpg,jpeg,png,gif|max:2048'
    ]);

if ($validator->fails()) {
        return back()->withErrors($validator)->withInput();
    }

try {
        DB::beginTransaction();

$documentPath = null;
        $imagePath = null;

if ($request->hasFile('document')) {
            $documentName = time() . '_' . $request->file('document')->getClientOriginalName();
            $request->file('document')->move(public_path('machine/documents'), $documentName);
            $documentPath = 'machine/documents/' . $documentName;
        }

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

DB::table('master_machine')->insert([
            'machine_name' => $request->machine_name,
            'machine_no' => $request->machine_no,
            'site_id' => $request->site_id,
            'machine_type' => $request->machine_type,
            'brand_model_no' => $request->brand_model_no,
            'purchase_date' => $request->purchase_date,
            'status' => $request->status,
            'last_service_date' => $request->last_service_date,
            'next_service_date' => $request->next_service_date,
            'document_path' => $documentPath,
            'image_path' => $imagePath,
            'created_at' => now(),
            'updated_at' => now()
        ]);

DB::commit();
        return redirect()->route('machines.index')->with('success', 'Machine added successfully!');
    } catch (\Exception $e) {
        DB::rollBack();
        return back()->with('error', 'Error adding machine: ' . $e->getMessage())->withInput();
    }
}

/**
     * Show the form for editing the specified machine (AJAX)
     */
    public function editMachine($id)
    {
        try {
            $machine = DB::table('master_machine')
                        ->where('id', $id)
                        ->first();

if (!$machine) {
                return response()->json(['error' => 'Machine not found'], 404);
            }

return response()->json($machine);

} catch (\Exception $e) {
            return response()->json(['error' => 'Error fetching machine details'], 500);
        }
    }

/**
     * Update the specified machine
     */
public function update(Request $request, $id)
{
    $machine = DB::table('master_machine')->where('id', $id)->first();
    if (!$machine) {
        return back()->with('error', 'Machine not found.');
    }

if ($validator->fails()) {
        return back()->withErrors($validator)->withInput();
    }

try {
        DB::beginTransaction();

$documentPath = $machine->document_path;
        $imagePath = $machine->image_path;

if ($request->hasFile('document')) {
            if ($documentPath && file_exists(public_path($documentPath))) {
                unlink(public_path($documentPath));
            }

$docName = time() . '_' . $request->file('document')->getClientOriginalName();
            $request->file('document')->move(public_path('machine/documents'), $docName);
            $documentPath = 'machine/documents/' . $docName;
        }

if ($request->hasFile('image')) {
            if ($imagePath && file_exists(public_path($imagePath))) {
                unlink(public_path($imagePath));
            }

$imgName = time() . '_' . $request->file('image')->getClientOriginalName();
            $request->file('image')->move(public_path('machine/images'), $imgName);
            $imagePath = 'machine/images/' . $imgName;
        }

DB::table('master_machine')->where('id', $id)->update([
            'machine_name' => $request->machine_name,
            'machine_no' => $request->machine_no,
            'site_id' => $request->site_id,
            'machine_type' => $request->machine_type,
            'brand_model_no' => $request->brand_model_no,
            'purchase_date' => $request->purchase_date,
            'status' => $request->status,
            'last_service_date' => $request->last_service_date,
            'next_service_date' => $request->next_service_date,
            'document_path' => $documentPath,
            'image_path' => $imagePath,
            'updated_at' => now()
        ]);

DB::commit();
        return redirect()->route('machines.index')->with('success', 'Machine updated successfully!');
    } catch (\Exception $e) {
        DB::rollBack();
        return back()->with('error', 'Error updating machine: ' . $e->getMessage())->withInput();
    }
}

public function destroy_machine($id)
{
    $machine = DB::table('master_machine')->where('id', $id)->first();
    if (!$machine) {
        return redirect()->route('machines.index')->with('error', 'Machine not found.');
    }

// Delete associated files if they exist
    if ($machine->document_path && file_exists(public_path($machine->document_path))) {
        unlink(public_path($machine->document_path));
    }

if ($machine->image_path && file_exists(public_path($machine->image_path))) {
        unlink(public_path($machine->image_path));
    }

// Soft delete - update status instead of deleting
    DB::table('master_machine')
        ->where('id', $id)
        ->update(['is_deleted' => 1]);

return redirect()->route('machines.index')->with('success', 'Machine removed successfull .');
}

public function getDetails(Machine $machine)
{
    return response()->json([
        'machine_name' => $machine->machine_name,
        'machine_type' => $machine->machine_type,
        'brand_model_no' => $machine->brand_model_no,
        'purchase_date' => $machine->purchase_date,
        'status' => $machine->status,
        'last_service_date' => $machine->last_service_date,
        'next_service_date' => $machine->next_service_date,
        'site_id' => $machine->site_id
    ]);
}
//==============================================================================================================================================

// Controller methods for fertilizer management

// Updated Controller Logic
  public function indexFertilizerS(Request $request)
{
    try {
        $user = Auth::user();
        $userRole = $user->role;
        $userSiteId = $user->site_id;

// Fetch sites for Admin dropdown
        $sites = DB::table('master_sites')
            ->select('id', 'site_name')
            ->orderBy('site_name')
            ->get();

// Build fertilizer query
        $query = DB::table('master_fertilizer as f')
            ->leftJoin('master_sites as s', 'f.site_id', '=', 's.id')
            ->select('f.*', 's.site_name');

// Admin can filter by selected site
        if ($userRole == 1) {
            if ($request->has('site_id') && $request->site_id != '') {
                $query->where('f.site_id', $request->site_id);
            }
        } else {
            // Other roles can only see their own site
            $query->where('f.site_id', $userSiteId);
        }

// Search filter
        if ($request->has('search') && $request->search != '') {
            $query->where(function ($q) use ($request) {
                $q->where('f.fertilizer_name', 'LIKE', '%' . $request->search . '%')
                  ->orWhere('f.brand_name', 'LIKE', '%' . $request->search . '%')
                  ->orWhere('f.supplier_name', 'LIKE', '%' . $request->search . '%');
            });
        }

// Get fertilizer records
        $fertilizers = $query->orderBy('f.id', 'desc')->get();

// Add stock history and calculated amount using weighted average
        foreach ($fertilizers as $fertilizer) {
            $history = DB::table('fertilizer_stock_history')
                ->where('fertilizer_id', $fertilizer->id)
                ->orderBy('created_at', 'asc') // important for weighted avg
                ->get();

$fertilizer->stockHistoryRecords = $history;

if ($history->isEmpty()) {
                // No stock history, use original rate
                $fertilizer->calculated_amount = $fertilizer->stock_kg * $fertilizer->rate;
            } else {
                $totalQty = 0;
                $totalCost = 0;

foreach ($history as $h) {
                    $qty = $h->added_quantity;
                    $rate = $h->rate;
                    $totalQty += $qty;
                    $totalCost += ($qty * $rate);
                }

// Include original stock also (if not recorded in history)
                $originalQty = $fertilizer->stock_kg - $totalQty;
                if ($originalQty > 0) {
                    $totalQty += $originalQty;
                    $totalCost += ($originalQty * $fertilizer->rate);
                }

$averageRate = $totalQty > 0 ? ($totalCost / $totalQty) : $fertilizer->rate;

$fertilizer->calculated_amount = $averageRate * $fertilizer->stock_kg;
            }
        }

// For edit mode (existing logic)
        $fertilizer = null;
        if ($request->has('edit_id')) {
            $fertilizerQuery = DB::table('master_fertilizer as f')
                ->leftJoin('master_sites as s', 'f.site_id', '=', 's.id')
                ->select('f.*', 's.site_name')
                ->where('f.id', $request->edit_id);

if ($userRole !== 1) {
                $fertilizerQuery->where('f.site_id', $userSiteId);
            }

$fertilizer = $fertilizerQuery->first();

if ($fertilizer) {
                $fertilizer->stockHistoryRecords = DB::table('fertilizer_stock_history')
                    ->where('fertilizer_id', $fertilizer->id)
                    ->orderBy('created_at', 'desc')
                    ->get();
            }
        }

return view('admin.fertilizer', compact('fertilizers', 'fertilizer', 'sites', 'userRole'));

} catch (\Exception $e) {
        Log::error('Error loading fertilizer data: ' . $e->getMessage());
        return back()->with('error', 'An error occurred while loading data.');
    }
}

//fetilizer history
public function showFertilizerHistory($id)
{
    $fertilizer = DB::table('master_fertilizer')->where('id', $id)->first();
    $history = DB::table('fertilizer_stock_history')
                ->where('fertilizer_id', $id)
                ->orderByDesc('created_at')
                ->get();

return view('admin.fertilizer', compact('fertilizer', 'history'));
}

// public function addFertilizerStock(Request $request, $id)
// {
//     try {
//         $validated = $request->validate([
//             'quantity_to_add' => 'required|numeric|min:0.01',
//             'new_rate' => 'nullable|numeric|min:0',
//             'storage_location' => 'nullable|string|max:255',
//         ]);

//         $fertilizer = DB::table('master_fertilizer')
//             ->where('id', $id)
           
//             ->first();

//         if (!$fertilizer) {
//             return redirect()->route('fertilizer.index')->with('error', 'Fertilizer not found.');
//         }

//         DB::beginTransaction();

//         // Prepare history
//         $historyData = [
//             'fertilizer_id' => $id,
//             'fertilizer_name' => $fertilizer->fertilizer_name,
//             'fertilizer_type' => $fertilizer->fertilizer_type,
//             'brand_name' => $fertilizer->brand_name,
//             'uom' => $fertilizer->uom,
//             'stock_before_addition' => $fertilizer->stock_kg,
//             'added_quantity' => $validated['quantity_to_add'],
//             'rate' => $validated['new_rate'] ?? $fertilizer->rate,
//             'storage_location' => $validated['storage_location'] ?? $fertilizer->storage_location,
//             'site_id' => $fertilizer->site_id,
//             'created_at' => now(),
//             'updated_at' => now(),
//         ];

//         DB::table('fertilizer_stock_history')->insert($historyData);

//         // Update new stock
//         $newStock = $fertilizer->stock_kg + $validated['quantity_to_add'];
//         DB::table('master_fertilizer')->where('id', $id)->update([
//             'stock_kg' => $newStock,
//         ]);

//         DB::commit();

//         return redirect()->route('fertilizer.index')
//             ->with('success', 'Fertilizer stock updated successfully.');

//     } catch (\Exception $e) {
//         DB::rollBack();
//         return redirect()->route('fertilizer.index')
//             ->with('error', 'Failed to add stock: ' . $e->getMessage());
//     }
// }
public function addFertilizerStock(Request $request, $id)
{
    try {
        // Step 1: Validate input
        $validated = $request->validate([
            'quantity_to_add' => 'required|numeric|min:0.01',
            'new_rate' => 'nullable|numeric|min:0',
            'storage_location' => 'nullable|string|max:255',
        ]);

// Step 2: Fetch fertilizer record
        $fertilizer = DB::table('master_fertilizer')->where('id', $id)->first();

if (!$fertilizer) {
            return redirect()->route('fertilizer.index')->with('error', 'Fertilizer not found.');
        }

// Step 3: Start DB Transaction
        DB::beginTransaction();

// Step 4: Insert previous master data as baseline entry in history if not already added
        $exists = DB::table('fertilizer_stock_history')
            ->where('fertilizer_id', $fertilizer->id)
            ->where('stock_before_addition', $fertilizer->stock_kg)
            ->where('added_quantity', 0)
            ->exists();

if (!$exists) {
            DB::table('fertilizer_stock_history')->insert([
                'fertilizer_id' => $fertilizer->id,
                'fertilizer_name' => $fertilizer->fertilizer_name,
                'fertilizer_type' => $fertilizer->fertilizer_type,
                'brand_name' => $fertilizer->brand_name,
                'uom' => $fertilizer->uom,
                'stock_before_addition' => $fertilizer->stock_kg,
                'added_quantity' => 0,
                'rate' => $fertilizer->rate,
                'storage_location' => $fertilizer->storage_location,
                'site_id' => $fertilizer->site_id,
                'created_at' => $fertilizer->created_at,
                'updated_at' => $fertilizer->updated_at,
            ]);
        }

// Step 5: Calculate new stock
        $newStock = $fertilizer->stock_kg + $validated['quantity_to_add'];
        $rateToUse = $validated['new_rate'] ?? $fertilizer->rate;
        $locationToUse = $validated['storage_location'] ?? $fertilizer->storage_location;

// Step 6: Insert new entry in stock history
        DB::table('fertilizer_stock_history')->insert([
            'fertilizer_id' => $fertilizer->id,
            'fertilizer_name' => $fertilizer->fertilizer_name,
            'fertilizer_type' => $fertilizer->fertilizer_type,
            'brand_name' => $fertilizer->brand_name,
            'uom' => $fertilizer->uom,
            'stock_before_addition' => $fertilizer->stock_kg,
            'added_quantity' => $validated['quantity_to_add'],
            'rate' => $rateToUse,
            'storage_location' => $locationToUse,
            'site_id' => $fertilizer->site_id,
            'created_at' => now(),
            'updated_at' => now(),
        ]);

// Step 7: Update stock in master
        DB::table('master_fertilizer')->where('id', $id)->update([
            'stock_kg' => $newStock,
            'rate' => $rateToUse,
            'storage_location' => $locationToUse,
            'updated_at' => now(),
        ]);

DB::commit();

return redirect()->route('fertilizer.index')->with('success', 'Fertilizer stock updated successfully.');
    } catch (\Exception $e) {
        DB::rollBack();
        return redirect()->route('fertilizer.index')->with('error', 'Error updating stock: ' . $e->getMessage());
    }
}

public function getFertilizerDetails($id)
{
    $fertilizer = DB::table('master_fertilizer')
        ->where('id', $id)
        ->first();

if (!$fertilizer) {
        return response()->json(['error' => 'Fertilizer not found.'], 404);
    }

return response()->json($fertilizer);
}

public function getFertilizerHistory($id)
{
    $history = DB::table('fertilizer_stock_history')
        ->where('fertilizer_id', $id)
        ->orderByDesc('id') // safer than created_at
        ->get();

$history->map(function ($record) {
        $record->created_at_formatted = $record->created_at
            ? \Carbon\Carbon::parse($record->created_at)->format('d-m-y')
            : '—';
        return $record;
    });

return response()->json($history);
}

public function storefertilizers(Request $request)
{
    try {
        $user = Auth::user();
        $userRole = $user->role;
        $userSiteId = $user->site_id;
        
        // Determine which site_id to use
        $siteId = null;
        if ($userRole == 1) {
            // Admin can choose any site
            $siteId = $request->site_id;
        } else {
            // Other roles can only add to their own site
            $siteId = $userSiteId;
        }
        
        // Validate that site_id is provided
        if (!$siteId) {
            return redirect()->back()->with('error', 'Site selection is required!');
        }
        
        DB::table('master_fertilizer')->insert([
            'fertilizer_name' => $request->fertilizer_name,
            'purchase_date' => $request->purchase_date,
            'site_id' => $siteId,
            'fertilizer_type' => $request->fertilizer_type,
            'stock_kg' => $request->stock_kg,
            'brand_name' => $request->brand_name,
            'expiry_date' => $request->expiry_date,
            'supplier_name' => $request->supplier_name,
            'uom' => $request->uom,
            'rate' => $request->rate,
            'storage_location' => $request->storage_location,
            'created_at' => now(),
            'updated_at' => now()
        ]);
        
        return redirect()->route('fertilizer.index')->with('success', 'Fertilizer added successfully!');
        
    } catch (\Exception $e) {
        \Log::error('Error adding fertilizer: ' . $e->getMessage());
        return redirect()->back()->with('error', 'An error occurred while adding fertilizer.');
    }
}
  
  public function updatefertilizers(Request $request, $id)
  {
      DB::table('master_fertilizer')->where('id', $id)->update([
          'fertilizer_name' => $request->fertilizer_name,
          'purchase_date' => $request->purchase_date,
                  'site_id' => $request->site_id, // <-- add this line

'fertilizer_type' => $request->fertilizer_type,
          'stock_kg'   => $request->stock_kg,
          // We don't update stock_kg here as it might be managed separately
          'brand_name' => $request->brand_name,
          'expiry_date' => $request->expiry_date,
          'supplier_name' => $request->supplier_name,
          'uom' => $request->uom,
'rate' => $request->rate,
          'storage_location' => $request->storage_location,
          'updated_at' => now()
      ]);
  
      return redirect()->route('fertilizer.index')->with('success', 'Fertilizer updated!');
  }
  
  public function editfertilizer($id)
  {
      $fertilizer = DB::table('master_fertilizer')->where('id', $id)->first();
      $fertilizers = DB::table('master_fertilizer')->get();
  
      return view('admin.fertilizer', compact('fertilizer', 'fertilizers'));
  }
  
  public function deletefertilizers($id)
  {
      DB::table('master_fertilizer')->where('id', $id)->delete();
      return redirect()->route('fertilizer.index')->with('success', 'Fertilizer deleted!');
  }

//==============================================================================================================================================
public function indexTractor(Request $request)
{
    $user = Auth::user(); // Logged-in user
    $userRole = $user->role;
    $userSiteId = $user->site_name; // Assuming `site_name` holds site ID

$tractorsQuery = DB::table('master_tractors as m')
        ->leftJoin('master_sites as s', 'm.site_id', '=', 's.id')
        ->select('m.*', 's.site_name')
        ->where('m.is_deleted', 0);

// Role-based site filtering
    if ($userRole == 2) {
        $tractorsQuery->where('m.site_id', 1);
    } elseif ($userRole == 3) {
        $tractorsQuery->where('m.site_id', 2);
    } elseif ($userRole == 4) {
        $tractorsQuery->where('m.site_id', 3);
    } elseif ($userRole == 5 && $userSiteId == 4) {
        $tractorsQuery->where('m.site_id', 4);
    } elseif ($userRole == 1) {
        // Admin (role 1) can filter by site if provided in request
        if ($request->has('site_id') && !empty($request->input('site_id'))) {
            $tractorsQuery->where('m.site_id', $request->input('site_id'));
        }
        // No site_id provided: admin sees all
    }

$tractors = $tractorsQuery->orderBy('m.id', 'DESC')->get();

$sites = DB::table('master_sites')->orderBy('site_name')->get();

return view('admin.tractor', compact('tractors', 'sites'));
}

public function storeTractor(Request $request)
{
    // Create directories if they don't exist
    if (!file_exists(public_path('tractor/documents'))) {
        mkdir(public_path('tractor/documents'), 0777, true);
    }

if (!file_exists(public_path('tractor/images'))) {
        mkdir(public_path('tractor/images'), 0777, true);
    }

// Handle document upload
    $documentName = null;
    if ($request->hasFile('upload_document')) {
        $documentName = time() . '_doc.' . $request->upload_document->extension();
        $request->upload_document->move(public_path('tractor/documents'), $documentName);
    }

// Handle image upload
    $imageName = null;
    if ($request->hasFile('tractor_image')) {
        $imageName = time() . '_img.' . $request->tractor_image->extension();
        $request->tractor_image->move(public_path('tractor/images'), $imageName);
    }

// Insert record
    DB::table('master_tractors')->insert([
        'tractor_name'    => $request->tractor_name,
        'tractor_type'    => $request->tractor_type,
        'brand_model'     => $request->brand_model,
        'site_id'         => $request->site_id,
        'purchase_date'   => $request->purchase_date,
        'upload_document' => $documentName,
        'machine_status'  => $request->machine_status,
        'tractor_image'   => $imageName,
        'service_prev'    => $request->service_prev,
        'service_upco'    => $request->service_upco,
        'created_at'      => now(),
        'updated_at'      => now(),
    ]);

return redirect()->route('tractors.index')->with('success', 'Tractor added successfully!');
}

public function updateTractor(Request $request, $id)
{
    $user = Auth::user(); // Get logged-in user

// Create directories if they don't exist
    if (!file_exists(public_path('tractor/documents'))) {
        mkdir(public_path('tractor/documents'), 0777, true);
    }

if (!file_exists(public_path('tractor/images'))) {
        mkdir(public_path('tractor/images'), 0777, true);
    }

// Use user's site_id, ignore whatever comes from form
    $updateData = [
        'tractor_name'    => $request->tractor_name,
        'tractor_type'    => $request->tractor_type,
        'brand_model'     => $request->brand_model,
        'site_id'         => $user->site_id,
        'purchase_date'   => $request->purchase_date,
        'machine_status'  => $request->machine_status,
        'service_prev'    => $request->service_prev,
        'service_upco'    => $request->service_upco,
        'updated_at'      => now(),
    ];

if ($documentName) {
        $updateData['upload_document'] = $documentName;
    }

if ($imageName) {
        $updateData['tractor_image'] = $imageName;
    }

DB::table('master_tractors')->where('id', $id)->update($updateData);

return redirect()->route('tractors.index')->with('success', 'Tractor updated successfully!');
}

public function destroyTractor($id)
{
    // Get tractor to delete associated files
    $tractor = DB::table('master_tractors')->where('id', $id)->first();

// Delete image if exists
    if ($tractor->tractor_image && file_exists(public_path('tractor/images/' . $tractor->tractor_image))) {
        unlink(public_path('tractor/images/' . $tractor->tractor_image));
    }

// Delete document if exists
    if ($tractor->upload_document && file_exists(public_path('tractor/documents/' . $tractor->upload_document))) {
        unlink(public_path('tractor/documents/' . $tractor->upload_document));
    }

// Soft delete: update is_deleted to 1
    DB::table('master_tractors')->where('id', $id)->update(['is_deleted' => 1]);

return redirect()->route('tractors.index')->with('success', 'Tractor deleted successfully (soft delete)!');
}

public function searchTractor(Request $request)
    {
        $query = $request->input('query');
        
        $tractors = DB::table('master_tractors')
            ->where('tractor_name', 'LIKE', "%{$query}%")
            ->orWhere('brand_model', 'LIKE', "%{$query}%")
            ->orWhere('tractor_type', 'LIKE', "%{$query}%")
            ->orderBy('id', 'DESC')
            ->get();
            
        return view('admin.tractor', compact('tractors'));
    }
   public function diesel()
{
    $user = Auth::user();
    $siteId = $user->site_id;

// Get diesel data for the logged-in user's site
    $dieselData = DB::table('diesel_stocks')
        ->where('site_id', $siteId)
        ->orderByDesc('id')
        ->get();

return view('admin.diesel', [
        'dieselData' => $dieselData
    ]);
}
    //==============================================================================================================================================

// block  add, update, delete
    public function indexblock(Request $request)
    {
        $search = $request->input('search');
        $masterBlocs = DB::table('master_block')
            ->where('name', 'like', "%{$search}%")
            ->orderBy('id', 'desc')
            ->paginate(5);
            
        return view('admin.block', compact('masterBlocs', 'search'));
    }

public function storeblock(Request $request)
    {
        $request->validate(['name' => 'required']);
        DB::table('master_block')->insert(['name' => $request->name, 'created_at' => now(), 'updated_at' => now()]);
        return back()->with('success', 'Master Bloc added successfully');
    }

public function edit($id)
    {
        return response()->json(DB::table('master_block')->where('id', $id)->first());
    }

public function updateblock(Request $request, $id)
    {
        $request->validate(['name' => 'required']);
        DB::table('master_block')->where('id', $id)->update(['name' => $request->name, 'updated_at' => now()]);
        return back()->with('success', 'Master Bloc updated successfully');
    }

public function destroyblock($id)
    {
        DB::table('master_block')->where('id', $id)->delete();
        return back()->with('success', 'Master Bloc deleted successfully');
    }
    //==============================================================================================================================================

// diesel add, update, delete
// public function indexdiesel(Request $request)
// {
//     $currentUser = Auth::user(); // 🔐 Get the logged-in user

//     $query = DB::table('diesel_stocks')
//         ->where('site_id', $currentUser->site_id); // ✅ Filter by site_id

//     if ($request->filled('search')) {
//         $search = $request->search;
//         $query->where(function ($q) use ($search) {
//             $q->where('diesel_stock', 'like', "%{$search}%")
//               ->orWhere('rate_per_liter', 'like', "%{$search}%");
//         });
//     }

//     $diesels = $query->orderByDesc('id')->get();

//     $sites = DB::table('master_sites')->orderBy('site_name')->get();

//     // ✅ Monthly Summary Logic (also filter by user site_id)
//     $monthlySummary = DB::table('diesel_stocks')
//         ->select(
//             DB::raw("DATE_FORMAT(date_of_entry, '%Y-%m') AS month"),
//             DB::raw("SUM(CASE WHEN type = 'in' THEN diesel_stock ELSE 0 END) AS total_in"),
//             DB::raw("SUM(CASE WHEN type = 'out' THEN diesel_stock ELSE 0 END) AS total_out")
//         )
//         ->where('site_id', $currentUser->site_id) // 🔐 Filter summary by site
//         ->groupBy(DB::raw("DATE_FORMAT(date_of_entry, '%Y-%m')"))
//         ->orderBy('month', 'desc')
//         ->get();

//     // ✅ Running Balance
//     $runningBalance = 0;
//     foreach ($monthlySummary as $summary) {
//         $runningBalance += $summary->total_in - $summary->total_out;
//         $summary->balance = $runningBalance;
//     }

//     return view('admin.diesel', compact('diesels', 'sites', 'monthlySummary'));
// }

public function indexdiesel(Request $request)
{
    $currentUser = Auth::user();

// 1. Fetch all 'in' entries (in order)
    $inEntries = DB::table('diesel_stocks')
        ->where('site_id', $currentUser->site_id)
        ->where('type', 'in')
        ->orderBy('date_of_entry')
        ->orderBy('id')
        ->get();

// 2. Calculate total stock IN
    $totalIn = $inEntries->sum('diesel_stock');

// 3. Calculate current diesel balance (remaining)
    $latestBalance = DB::table('diesel_stocks')
        ->where('site_id', $currentUser->site_id)
        ->where('type', 'in')
        ->sum('diesel_stock'); // because stock gets auto-decreased

// 4. Usage = totalIn - currentRemaining
    $usedTotal = $totalIn - $latestBalance;

// 5. Apply FIFO distribution to calculate how much used from each IN entry
    $remainingToSubtract = $usedTotal;
    $fifoStack = [];

foreach ($inEntries as $entry) {
        $entryStock = $entry->diesel_stock;
        $usedFromThis = 0;
        $dateZero = null;

if ($remainingToSubtract > 0) {
            $usedFromThis = min($entryStock, $remainingToSubtract);
            $remainingToSubtract -= $usedFromThis;

if ($entryStock == $usedFromThis) {
                $dateZero = now()->toDateString(); // approximate - real last usage date not tracked
            }
        }

$fifoStack[] = [
            'date_in' => $entry->date_of_entry,
            'stock_in' => $entryStock,
            'used' => $usedFromThis,
            'date_zero' => $dateZero,
            'rate' => $entry->rate_per_liter,
            'site' => $entry->site_id,
        ];
    }

$consumptionReport = collect($fifoStack);

// Load diesel entries for table display
    $query = DB::table('diesel_stocks')
        ->where('site_id', $currentUser->site_id)
        ->orderByDesc('id');

if ($request->filled('search')) {
        $search = $request->search;
        $query->where(function ($q) use ($search) {
            $q->where('diesel_stock', 'like', "%{$search}%")
              ->orWhere('rate_per_liter', 'like', "%{$search}%");
        });
    }

$diesels = $query->get();

$sites = DB::table('master_sites')->orderBy('site_name')->get();

// Monthly summary logic
    $monthlySummary = DB::table('diesel_stocks')
        ->select(
            DB::raw("DATE_FORMAT(date_of_entry, '%Y-%m') AS month"),
            DB::raw("SUM(CASE WHEN type = 'in' THEN diesel_stock ELSE 0 END) AS total_in"),
            DB::raw("0 AS total_out") // Out is now calculated from logic, not table
        )
        ->where('site_id', $currentUser->site_id)
        ->groupBy(DB::raw("DATE_FORMAT(date_of_entry, '%Y-%m')"))
        ->orderBy('month', 'desc')
        ->get();

// Add balance to summary
    $runningMonthly = 0;
    foreach ($monthlySummary as $summary) {
        $runningMonthly += $summary->total_in - $summary->total_out;
        $summary->balance = $runningMonthly;
    }

return view('admin.diesel', compact('diesels', 'sites', 'consumptionReport', 'monthlySummary'));
}

public function storediesel(Request $request)
{
    $user = Auth::user();

$request->validate([
        
        'diesel_stock' => 'required|numeric',
        'date_of_entry' => 'required|date',
        'rate_per_liter' => 'nullable|numeric',
    ]);

DB::table('diesel_stocks')->insert([
        
        'diesel_stock' => $request->diesel_stock,
        'site_id' => $user->site_id,
        'date_of_entry' => $request->date_of_entry,
        'rate_per_liter' => $request->rate_per_liter,
        'created_at' => now(),
        'updated_at' => now(),
    ]);

return redirect()->route('diesels.index')->with('success', 'Diesel entry added successfully!');
}

public function updatediesel(Request $request, $id)
    {
        $request->validate([
            'diesel_stock' => 'required|numeric',
           
            'date_of_purchase' => 'required|date',
            'rate_per_liter' => 'required|numeric',
        ]);

DB::table('diesel_stocks')->where('id', $id)->update([
            'diesel_stock' => $request->diesel_stock,
              'site_id' => $request->site_id,

'date_of_purchase' => $request->date_of_purchase,
            'rate_per_liter' => $request->rate_per_liter,
            'updated_at' => now(),
        ]);

return redirect('/diesels')->with('success', 'Diesel record updated!');
    }

public function destroydiesel($id)
    {
        DB::table('diesel_stocks')->where('id', $id)->delete();
        return redirect('/diesels')->with('success', 'Diesel record deleted!');
    }
//==============================================================================================================================================

//MANPOWER ADD EDIT DELETE
 public function indexManpower(Request $request)
{
    try {
        // Get authenticated user
        $user = Auth::user();
        $userRole = $user->role; // Assuming '2' is Supervisor, '1' is Admin
        $userSiteId = $user->site_id;
        
        // Build manpower query with join to sites
        $query = DB::table('master_manpower as m')
            ->leftJoin('master_sites as s', 'm.site_id', '=', 's.id')
            ->select(
                'm.*',
                's.site_name'
            );
        
        // Role-based filtering
        if ($userRole !== '1') { 
            // Non-Admins (e.g., Supervisors) see only their site's data
            $query->where('m.site_id', $userSiteId);
        } else {
            // Role 1 (Admin) - Apply site filter if provided
            $siteFilter = $request->input('site_filter', '');
            if (!empty($siteFilter)) {
                $query->where('m.site_id', $siteFilter);
            }
        }
        
        // Search filter
        $search = $request->input('search', '');
        if (!empty($search)) {
            $query->where(function ($q) use ($search) {
                $q->where('m.category', 'LIKE', "%$search%")
                  ->orWhere('m.type', 'LIKE', "%$search%")
                  ->orWhere('m.no_of_person', 'LIKE', "%$search%")
                  ->orWhere('m.rate', 'LIKE', "%$search%")
                  ->orWhere('s.site_name', 'LIKE', "%$search%");
            });
        }
        
        // Get paginated manpower records
        $manPowers = $query->orderByDesc('m.id')->paginate(15);
        
        // Fetch categories and types (assuming they are distinct values from the table)
        $categories = DB::table('master_manpower')
            ->select('category')
            ->distinct()
            ->whereNotNull('category')
            ->orderBy('category')
            ->pluck('category');
        
        $types = DB::table('master_manpower')
            ->select('type')
            ->distinct()
            ->whereNotNull('type')
            ->orderBy('type')
            ->pluck('type');
        
        // Fetch sites for dropdown
        $sites = DB::table('master_sites')
            ->select('id', 'site_name')
            ->orderBy('site_name')
            ->get();
        
        return view('admin.manpower', compact('manPowers', 'categories', 'types', 'sites', 'search'));
        
    } catch (\Exception $e) {
        \Log::error('Error loading manpower data: ' . $e->getMessage());
        return back()->with('error', 'An error occurred while loading manpower data.');
    }
}

public function storemanpower(Request $request)
{
    // Get the logged-in user's site_id
    $userSiteId = Auth::user()->site_id;
    
    // Check if a record with the same category and type already exists for this site
    $existingManpower = DB::table('master_manpower')
                        ->where('category', $request->category)
                        ->where('type', $request->type)
                        ->where('site_id', $userSiteId)
                        ->first();
    
    if ($existingManpower) {
        // If it exists, return with an error message
        return back()->with('error', 'Manpower with this category and type already exists for your site.');
    }
    
    // If it doesn't exist, proceed with the insertion using user's site_id
    DB::table('master_manpower')->insert([
        'category' => $request->category,
        'site_id' => $userSiteId, // Use logged-in user's site_id
        'type' => $request->type,
        'no_of_person' => $request->no_of_person,
        'rate' => $request->rate,
        'created_at' => now(),
        'updated_at' => now(),
    ]);
    
    return back()->with('success', 'Man Power added successfully');
}

public function editmanpower($id)
{
    $data = DB::table('master_manpower')->where('id', $id)->first();
    return response()->json($data);
}

public function updatemanpower(Request $request, $id)
{
    // Get the logged-in user's site_id
    $userSiteId = Auth::user()->site_id;
    
    DB::table('master_manpower')->where('id', $id)->update([
        'category' => $request->category,
        'site_id' => $userSiteId, // Use logged-in user's site_id
        'type' => $request->type,
        'no_of_person' => $request->no_of_person,
        'rate' => $request->rate,
        'updated_at' => now(),
    ]);
    
    return back()->with('success', 'Updated successfully');
}

public function destroymanpower($id)
    {
        DB::table('master_manpower')->where('id', $id)->delete();
        return back()->with('success', 'Deleted successfully');
    }

// master_irrigation_types add, update, delete
    public function indexmaster_irrigation_types(Request $request)
    {
        $query = DB::table('master_irrigation_types');

if ($request->has('search') && $request->search != '') {
            $search = $request->search;
            $query->where(function ($q) use ($search) {
                $q->where('type_name', 'like', "%$search%");
             
            });
        }

$master_irrigation_types = $query->orderByDesc('id')->get();

return view('admin.master_irrigation_types', compact('master_irrigation_types'));
    }

public function storedmaster_irrigation_types(Request $request)
    {

DB::table('master_irrigation_types')->insert([
            'type_name' => $request->type_name,
          
            'created_at' => now(),
            'updated_at' => now(),
        ]);

return redirect('/master_irrigation_types')->with('success', 'master_irrigation_types added successfully!');
    }

public function updatedmaster_irrigation_types(Request $request, $id)
    {
        $request->validate([
            'type_name' => 'required|string|max:255',
        ]);

DB::table('master_irrigation_types')->where('id', $id)->update([
            'type_name' => $request->type_name,
           
            'updated_at' => now(),
        ]);

return redirect('/master_irrigation_types')->with('success', 'master_irrigation_types record updated!');
    }

public function destroymaster_irrigation_types($id)
    {
        DB::table('master_irrigation_types')->where('id', $id)->delete();
        return redirect('/master_irrigation_types')->with('success', 'master_irrigation_types record deleted!');
    }
//==============================================================================================================================================
    // master_capacities add, update, delete
    public function indexMasterCapacities(Request $request)
    {
        $query = DB::table('master_capacities');
    
        if ($request->has('search') && $request->search != '') {
            $search = $request->search;
            $query->where('capacity_value', 'like', "%$search%");
        }
    
        $capacities = $query->orderBy('capacity_value', 'asc')->get();
    
        return view('admin.master_capacities', compact('capacities'));
    }
    
    public function storeMasterCapacities(Request $request)
    {
        $request->validate([
            'capacity_value' => 'required|string|max:255'
        ]);
    
        DB::table('master_capacities')->insert([
            'capacity_value' => $request->capacity_value,
            'created_at' => now(),
            'updated_at' => now(),
        ]);
    
        return redirect('/master_capacities')->with('success', 'Capacity added successfully!');
    }
    
    public function updateMasterCapacities(Request $request, $id)
    {
        $request->validate([
            'capacity_value' => 'required|string|max:255'
        ]);
    
        DB::table('master_capacities')->where('id', $id)->update([
            'capacity_value' => $request->capacity_value,
            'updated_at' => now(),
        ]);
    
        return redirect('/master_capacities')->with('success', 'Capacity updated successfully!');
    }
    
    public function destroyMasterCapacities($id)
    {
        DB::table('master_capacities')->where('id', $id)->delete();
        return redirect('/master_capacities')->with('success', 'Capacity deleted successfully!');
    }
    //==============================================================================================================================================
// public function indexWaterSource(Request $request)
//     {
//         try {
//             // Get authenticated user
//             $user = Auth::user();
//             $userRole = $user->role; // Assuming '2' is Supervisor, '1' is Admin
//             $userSiteId = $user->site_id;

//             // Build water source query with join to sites
//             $query = DB::table('water_sources as w')
//                 ->leftJoin('master_sites as s', 'w.site_id', '=', 's.id')
//                 ->select('w.id', 'w.name', 'w.capacity_lph', 's.site_name', 's.id as site_id');

//             // Role-based filtering
//             if ($userRole !== '1') { // Non-Admins (e.g., Supervisors) see only their site's data
//                 $query->where('w.site_id', $userSiteId);
//             }

//             // Site filter
//             if ($request->filled('site_id')) {
//                 $siteId = $request->site_id;
//                 $query->where('w.site_id', $siteId);
//                 // For Supervisors, ensure siteId matches their site if set
//                 if ($userRole !== '1' && $siteId != $userSiteId) {
//                     $query->where('w.site_id', $userSiteId); // Override to user's site
//                 }
//             }

//             // Get paginated water source records with unique constraint
//             $water_sources = $query->orderBy('w.id', 'asc')
//                 ->get()
//                 ->unique(fn($item) => $item->name . '_' . $item->capacity_lph)
//                 ->values()
//                 ->forPage($request->page ?: 1, 10);

//             // Fetch sites for dropdown
//             $sites = DB::table('master_sites')
//                 ->orderBy('site_name')
//                 ->get();

//             return view('admin.water_sources', compact('water_sources', 'sites'));

//         } catch (\Exception $e) {
//             \Log::error('Error loading water source data: ' . $e->getMessage());
//             return back()->with('error', 'An error occurred while loading water source data.');
//         }
//     }
public function indexWaterSource(Request $request)
{
    try {
        // Get authenticated user
        $user = Auth::user();
        $userRole = $user->role; // Assuming '2' is Supervisor, '1' is Admin
        $userSiteId = $user->site_id;
        
        // Build water source query with join to sites
        $query = DB::table('water_sources as w')
            ->leftJoin('master_sites as s', 'w.site_id', '=', 's.id')
            ->select('w.id', 'w.name', 'w.capacity_lph', 'w.borewell_no', 's.site_name', 's.id as site_id');
            
        // Role-based filtering
        if ($userRole !== '1') { // Non-Admins (e.g., Supervisors) see only their site's data
            $query->where('w.site_id', $userSiteId);
        }
        
        // Site filter
        if ($request->filled('site_id')) {
            $siteId = $request->site_id;
            $query->where('w.site_id', $siteId);
            
            // For Supervisors, ensure siteId matches their site if set
            if ($userRole !== '1' && $siteId != $userSiteId) {
                $query->where('w.site_id', $userSiteId); // Override to user's site
            }
        }
        
        // Get paginated water source records with unique constraint
        $water_sources = $query->orderBy('w.id', 'asc')
            ->get()
            ->unique(fn($item) => $item->name . '_' . $item->capacity_lph)
            ->values()
            ->forPage($request->page ?: 1, 10);
            
        // Fetch sites for dropdown
        $sites = DB::table('master_sites')
            ->orderBy('site_name')
            ->get();
            
        return view('admin.water_sources', compact('water_sources', 'sites'));
        
    } catch (\Exception $e) {
        \Log::error('Error loading water source data: ' . $e->getMessage());
        return back()->with('error', 'An error occurred while loading water source data.');
    }
}

public function storeWaterSource(Request $request)
{
    $request->validate([
        'name' => 'required|string|max:255',
        'capacity_lph' => 'required|numeric',
        'power_consumption_kw' => 'required|numeric',
        'cost_per_unit' => 'required|numeric',
        'notes' => 'nullable|string',
        'is_active' => 'required|boolean',
    ]);

$user = Auth::user();
    $siteId = $user->role == 1 ? $request->site_id : $user->site_id;

DB::table('water_sources')->insert([
        'name' => $request->name,
        'site_id' => $siteId,
        'capacity_lph' => $request->capacity_lph,
        'power_consumption_kw' => $request->power_consumption_kw,
        'cost_per_unit' => $request->cost_per_unit,
        'notes' => $request->notes,
        'is_active' => $request->is_active,
        'created_at' => now(),
        'updated_at' => now(),
    ]);

return redirect()->back()->with('success', 'Water source added successfully!');
}

public function updateWaterSource(Request $request, $id)
{
    $request->validate([
        'name' => 'required|string|max:255',
        'capacity_lph' => 'required|numeric',
        'power_consumption_kw' => 'required|numeric',
        'cost_per_unit' => 'required|numeric',
        'notes' => 'nullable|string',
        'is_active' => 'required|boolean',
    ]);

$user = Auth::user();
    $siteId = $user->role == 1 ? $request->site_id : $user->site_id;

DB::table('water_sources')
        ->where('id', $id)
        ->update([
            'name' => $request->name,
            'site_id' => $siteId,
            'capacity_lph' => $request->capacity_lph,
            'power_consumption_kw' => $request->power_consumption_kw,
            'cost_per_unit' => $request->cost_per_unit,
            'notes' => $request->notes,
            'is_active' => $request->is_active,
            'updated_at' => now(),
        ]);

return redirect()->back()->with('success', 'Water source updated successfully!');
}

public function deleteWaterSource($id)
    {
        DB::table('water_sources')->where('id', $id)->delete();
        return redirect()->back()->with('success', 'Water source deleted successfully!');
    }
    //chemicals =======================================================
// public function indexChemicals(Request $request)
//     {
//         try {
//             // Get authenticated user
//             $user = Auth::user();
//             $userRole = $user->role; // Assuming '2' is Supervisor, '1' is Admin
//             $userSiteId = $user->site_id;

//             // Build chemical query with join to sites
//             $query = DB::table('master_chemical as c')
//                 ->leftJoin('master_sites as s', 'c.site_id', '=', 's.id')
//                 ->select('c.*', 's.site_name');

//             // Role-based filtering
//             if ($userRole !== '1') { // Non-Admins (e.g., Supervisors) see only their site's data
//                 $query->where('c.site_id', $userSiteId);
//             }

//             // Search filter
//             $search = $request->input('search', '');
//             if (!empty($search)) {
//                 $query->where('c.chemical_name', 'LIKE', "%{$search}%");
//             }

//             // Site filter
//             $siteId = $request->input('site_id');
//             if (!empty($siteId)) {
//                 $query->where('c.site_id', $siteId);
//                 // For Supervisors, ensure siteId matches their site if set
//                 if ($userRole !== '1' && $siteId != $userSiteId) {
//                     $query->where('c.site_id', $userSiteId); // Override to user's site
//                 }
//             }

//             // Get paginated chemical records
//             $chemicals = $query->orderByDesc('c.id')->paginate(10);

//             // Fetch sites for dropdown
//             $sites = DB::table('master_sites')
//                 ->orderBy('site_name')
//                 ->get();

//             return view('admin.chemicals', compact('chemicals', 'sites', 'search', 'siteId'));

//         } catch (\Exception $e) {
//             \Log::error('Error loading chemical data: ' . $e->getMessage());
//             return back()->with('error', 'An error occurred while loading chemical data.');
//         }
//     }
public function indexChemicals(Request $request)
{
    try {
        $user = Auth::user();
        $userRole = $user->role;
        $userSiteId = $user->site_id;

// Fetch sites for Admin dropdown
        $sites = DB::table('master_sites')
            ->select('id', 'site_name')
            ->orderBy('site_name')
            ->get();

// Build chemical query
        $query = DB::table('master_chemical as c')
            ->leftJoin('master_sites as s', 'c.site_id', '=', 's.id')
            ->select('c.*', 's.site_name');

// Admin can filter by selected site
        if ($userRole == 1) {
            if ($request->has('site_id') && $request->site_id != '') {
                $query->where('c.site_id', $request->site_id);
            }
        } else {
            // Other roles can only see their own site
            $query->where('c.site_id', $userSiteId);
        }

// Search filter
        if ($request->has('search') && $request->search != '') {
            $query->where(function ($q) use ($request) {
                $q->where('c.chemical_name', 'LIKE', '%' . $request->search . '%')
                  ->orWhere('c.brand_name', 'LIKE', '%' . $request->search . '%')
                  ->orWhere('c.supplier_name', 'LIKE', '%' . $request->search . '%');
            });
        }

// Get chemical records
        $chemicals = $query->orderBy('c.id', 'desc')->get();

// Add stock history and calculated amount using weighted average
        foreach ($chemicals as $chemical) {
            $history = DB::table('chemical_stock_history')
                ->where('chemical_id', $chemical->id)
                ->orderBy('created_at', 'asc') // important for weighted avg
                ->get();

$chemical->stockHistoryRecords = $history;

if ($history->isEmpty()) {
                // No stock history, use original rate
                $chemical->calculated_amount = $chemical->stock_qty * $chemical->rate;
            } else {
                $totalQty = 0;
                $totalCost = 0;

// Include original stock also (if not recorded in history)
                $originalQty = $chemical->stock_qty - $totalQty;
                if ($originalQty > 0) {
                    $totalQty += $originalQty;
                    $totalCost += ($originalQty * $chemical->rate);
                }

$averageRate = $totalQty > 0 ? ($totalCost / $totalQty) : $chemical->rate;

$chemical->calculated_amount = $averageRate * $chemical->stock_qty;
            }
        }

// Convert to paginated collection
        $perPage = 10;
        $currentPage = LengthAwarePaginator::resolveCurrentPage();
        $currentItems = $chemicals->slice(($currentPage - 1) * $perPage, $perPage)->all();
        
        $paginatedChemicals = new LengthAwarePaginator(
            $currentItems,
            $chemicals->count(),
            $perPage,
            $currentPage,
            [
                'path' => request()->url(),
                'query' => request()->query()
            ]
        );

// For edit mode (existing logic)
        $chemical = null;
        if ($request->has('edit_id')) {
            $chemicalQuery = DB::table('master_chemical as c')
                ->leftJoin('master_sites as s', 'c.site_id', '=', 's.id')
                ->select('c.*', 's.site_name')
                ->where('c.id', $request->edit_id);

if ($userRole !== 1) {
                $chemicalQuery->where('c.site_id', $userSiteId);
            }

$chemical = $chemicalQuery->first();

if ($chemical) {
                $chemical->stockHistoryRecords = DB::table('chemical_stock_history')
                    ->where('chemical_id', $chemical->id)
                    ->orderBy('created_at', 'desc')
                    ->get();
            }
        }

return view('admin.chemicals', compact('paginatedChemicals', 'chemical', 'sites', 'userRole'));

} catch (\Exception $e) {
        Log::error('Error loading chemical data: ' . $e->getMessage());
        return back()->with('error', 'An error occurred while loading data.');
    }
}

public function storechemicals(Request $request)
{
    DB::table('master_chemical')->insert([
        'chemical_name' => $request->chemical_name,
            'site_id' => $request->site_id,
        'purchase_date' => $request->purchase_date,
        'chemical_type' => $request->chemical_type,
        'stock_qty' => $request->stock_qty,
        'brand_name' => $request->brand_name,
        'expiry_date' => $request->expiry_date,
        'supplier_name' => $request->supplier_name,
        'uom' => $request->uom,
        'rate' => $request->rate,
        'created_at' => now(),
        'updated_at' => now(),
    ]);

return redirect()->back()->with('success', 'Chemical added successfully!');
}
public function updatechemicals(Request $request, $id)
{
    DB::table('master_chemical')
        ->where('id', $id)
        ->update([
            'chemical_name' => $request->chemical_name,
                'site_id' => $request->site_id,
            'purchase_date' => $request->purchase_date,
            'chemical_type' => $request->chemical_type,
            'stock_qty' => $request->stock_qty,
            'brand_name' => $request->brand_name,
            'expiry_date' => $request->expiry_date,
            'supplier_name' => $request->supplier_name,
            'uom' => $request->uom,
            'rate' => $request->rate,
            'updated_at' => now(),
        ]);

return redirect()->back()->with('success', 'Chemical updated successfully!');
}
public function destroychemicals($id)
{
    DB::table('master_chemical')->where('id', $id)->delete();

return redirect()->back()->with('success', 'Chemical delete successfully!');
}
//========================================================================
 public function showChemicalHistory($id)
    {
        $chemical = DB::table('master_chemical')->where('id', $id)->first();
        $history = DB::table('chemical_stock_history')
                    ->where('chemical_id', $id)
                    ->orderByDesc('created_at')
                    ->get();

return view('admin.chemicals', compact('chemical', 'history'));
    }

/**
     * Add stock to an existing chemical.
     * This function handles the form submission for adding stock.
     */
    public function addChemicalStock(Request $request, $id)
    {
        try {
            // Validate incoming request data
            $validated = $request->validate([
                'quantity_to_add' => 'required|numeric|min:0.01',
                'new_rate' => 'nullable|numeric|min:0',
                'storage_location' => 'nullable|string|max:255',
            ]);

// Find the chemical by ID
            $chemical = DB::table('master_chemical')
                ->where('id', $id)
                ->first();

// If chemical not found, redirect with error
            if (!$chemical) {
                return redirect()->route('chemical.index')->with('error', 'Chemical not found.');
            }

// Start a database transaction to ensure atomicity
            DB::beginTransaction();

// Prepare data for the chemical stock history table
            $historyData = [
                'chemical_id' => $id,
                'chemical_name' => $chemical->chemical_name,
                'chemical_type' => $chemical->chemical_type,
                'brand_name' => $chemical->brand_name,
                'uom' => $chemical->uom,
                'stock_before_addition' => $chemical->stock_qty,
                'added_quantity' => $validated['quantity_to_add'],
                'rate' => $validated['new_rate'] ?? $chemical->rate,
                'storage_location' => $validated['storage_location'] ?? $chemical->storage_location,
                'site_id' => $chemical->site_id,
                'created_at' => now(),
                'updated_at' => now(),
            ];

// Insert the history record
            DB::table('chemical_stock_history')->insert($historyData);

// Calculate new stock quantity
            $newStock = $chemical->stock_qty + $validated['quantity_to_add'];

// Update the master_chemical table with the new stock quantity
            DB::table('master_chemical')->where('id', $id)->update([
                'stock_qty' => $newStock,
                'updated_at' => now(), // Also update the updated_at timestamp
            ]);

// Commit the transaction if all operations are successful
            DB::commit();

return redirect()->route('chemicals.index')
                ->with('success', 'Chemical stock updated successfully.');

} catch (\Exception $e) {
            // Rollback the transaction if any error occurs
            DB::rollBack();
            return redirect()->route('chemicals.index')
                ->with('error', 'Failed to add stock: ' . $e->getMessage());
        }
    }

/**
     * Get details of a single chemical (API endpoint).
//      * Used by AJAX to populate the history modal header.
//      */
    public function getChemicalDetails($id)
    {
        $chemical = DB::table('master_chemical')
            ->where('id', $id)
            ->first();

if (!$chemical) {
            return response()->json(['error' => 'Chemical not found.'], 404);
        }

return response()->json($chemical);
    }

/**
     * Get stock history for a single chemical (API endpoint).
     * Used by AJAX to populate the history modal table body.
     */
  public function getChemicalHistory($id)
{
    $history = DB::table('chemical_stock_history')
        ->where('chemical_id', $id)
        ->orderByDesc('created_at')
        ->get();

// Format the created_at as DD-MM-YY
    $history->map(function ($record) {
        $record->created_at_formatted = \Carbon\Carbon::parse($record->created_at)->format('d-m-y');
        return $record;
    });

return response()->json($history);
}
//===========================desiel add history
    public function addStockd(Request $request, $id)
{  try {
        $validated = $request->validate([
            'added_quantity'     => 'required|numeric|min:0.01',
            'new_rate'           => 'nullable|numeric|min:0',
            'storage_location'   => 'nullable|string|max:255',
            'note'               => 'nullable|string',
        ]);

$diesel = DB::table('diesel_stocks')->where('id', $id)->first();

if (!$diesel) {
            return redirect()->route('diesels.index')->with('error', 'Diesel stock not found.');
        }

DB::beginTransaction();

$historyData = [
            'diesel_stock_id'       => $id,
            'type'                  => $diesel->type,
            'date_of_entry'         => now(),
            'stock_before_addition' => $diesel->diesel_stock,
            'added_quantity'        => $validated['added_quantity'],
            'consumed_quantity'     => 0,
            'rate_per_liter'        => $validated['new_rate'] ?? $diesel->rate_per_liter,
            'site_id'               => $diesel->site_id,
            'storage_location'      => $validated['storage_location'] ?? null,
            'note'                  => $validated['note'] ?? null,
            'created_at'            => now(),
            'updated_at'            => now(),
        ];

DB::table('diesel_stock_history')->insert($historyData);

DB::table('diesel_stocks')
            ->where('id', $id)
            ->update([
                'diesel_stock'    => $diesel->diesel_stock + $validated['added_quantity'],
                'rate_per_liter'  => $validated['new_rate'] ?? $diesel->rate_per_liter,
                'updated_at'      => now(),
            ]);

DB::commit();
        return redirect()->route('diesels.index')->with('success', 'Diesel stock updated successfully.');
    } catch (\Exception $e) {
        DB::rollBack();
        return redirect()->route('diesels.index')->with('error', 'Failed to add stock: ' . $e->getMessage());
    }
}
public function getDieselDetails($id)
{
    $diesel = DB::table('diesel_stocks')->where('id', $id)->first();

if (!$diesel) {
        return response()->json(['message' => 'Diesel record not found.'], 404);
    }

return response()->json($diesel);
}

public function getDieselHistory($id)
{
    $history = DB::table('diesel_stock_history')
        ->where('diesel_stock_id', $id)
        ->orderByDesc('created_at')
        ->get()
        ->map(function ($record) {
            $record->date_of_entry = Carbon::parse($record->date_of_entry)->toIso8601String();
            return $record;
        });

return response()->json($history);
}

}