000

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;
class PreIrrigationController extends Controller
{
 public function index(Request $request)
    {
        $user = Auth::user();
        $role = $user->role;
        $userSiteId = $user->site_name;

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

// 🔹 2. Selected site
        $selectedSiteId = $request->input('site_id');
        if ($role != 1) {
            $selectedSiteId = $userSiteId;
        }

// 🔹 3. Get distinct blocks
        $blocksQuery = DB::table('pre_irrigation');
        if (!empty($selectedSiteId)) {
            $blocksQuery->where('site_id', $selectedSiteId);
        }
        $blocks = $blocksQuery->select('block_name')->distinct()->pluck('block_name');

// 🔹 4. Main query with joins
        $query = DB::table('pre_irrigation as pre')
            ->leftJoin('master_irrigation_types as it', 'pre.irrigation_type_id', '=', 'it.id')
            ->leftJoin('water_sources as ws', 'pre.water_source_id', '=', 'ws.id')
            ->leftJoin('water_sources as cap', 'pre.capacity_id', '=', 'cap.id')
            ->leftJoin('users', 'pre.user_id', '=', 'users.id')
            ->select(
                'pre.*',
                'it.type_name as irrigation_type',
                'ws.name as source_of_water',
                'cap.capacity_lph as capacity',
                'users.name as user_name'
            );

// Site filter
        if (!empty($selectedSiteId)) {
            $query->where('pre.site_id', $selectedSiteId);
        }

// 🔹 5. SESSION FILTER - FIXED AND WORKING
        if ($request->filled('session')) {
            $session = $request->session;
            switch ($session) {
                case 'kharif':
                    $query->whereMonth('pre.date', '>=', 6)
                          ->whereMonth('pre.date', '<=', 10);
                    break;
                case 'rabi':
                    $query->where(function($q) {
                        $q->whereMonth('pre.date', '>=', 11)
                          ->orWhereMonth('pre.date', '<=', 3);
                    });
                    break;
                case 'zaid':
                    $query->whereMonth('pre.date', '>=', 4)
                          ->whereMonth('pre.date', '<=', 6);
                    break;
            }
        }

// 🔹 6. Block filter
        if ($request->filled('block_name')) {
            $query->where('pre.block_name', $request->block_name);
        }

// 🔹 7. Search filter
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function ($q) use ($search) {
                $q->where('pre.plot_name', 'like', "%$search%")
                  ->orWhere('it.type_name', 'like', "%$search%")
                  ->orWhere('ws.name', 'like', "%$search%")
                  ->orWhere('cap.capacity_lph', 'like', "%$search%")
                  ->orWhere('pre.irrigation_no', 'like', "%$search%");
            });
        }

// 🔹 8. Get paginated data
        $data = $query->orderBy('pre.id', 'desc')
                      ->paginate(10)
                      ->appends($request->all());

return view('pre_irrigation.index', compact('data', 'blocks', 'role', 'sites', 'selectedSiteId'));
    }
}