000

<?php

namespace App\Http\Controllers\Auth;

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

class LoginController extends Controller
{
    // public function showLoginForm()
    // {
    //     $email = Cookie::get('remember_email');
    //     $passwordValue = Cookie::get('remember_password');
    //     $password = $passwordValue ? Crypt::decryptString($passwordValue) : null;
    //     $remember = Cookie::get('remember_checked') === 'true';

//     return view('auth.login', compact('email', 'password', 'remember'));
    // }

// public function loginDashboard(Request $request)
    // {
    //     $request->validate([
    //         'email' => 'required|email|string',
    //         'password' => 'required|string',
    //     ]);

//     $remember = $request->has('remember');
    //     $credentials = $request->only('email', 'password');

//     if (Auth::attempt($credentials, $remember)) {
    //         $request->session()->regenerate();
    //         $user = Auth::user();

//         if ($user && Schema::hasColumn('users', 'last_login')) {
    //             $user->last_login = now();
    //             $user->saveQuietly();
    //         }

//         if ($remember) {
    //             Cookie::queue('remember_email', $request->email, 60 * 24 * 30);
    //             Cookie::queue('remember_password', Crypt::encryptString($request->password), 60 * 24 * 30);
    //             Cookie::queue('remember_checked', 'true', 60 * 24 * 30);
    //         } else {
    //             Cookie::queue(Cookie::forget('remember_email'));
    //             Cookie::queue(Cookie::forget('remember_password'));
    //             Cookie::queue(Cookie::forget('remember_checked'));
    //         }

//         if ($user->role == 1) {
    //             return redirect()->route('site.summary');
    //         } elseif (in_array($user->role, [2, 3])) {
    //             return redirect()->route('dashboard');
    //         }

//         return redirect()->route('dashboard');
    //     }

//     return back()->with('error', 'The provided credentials do not match our records.')
    //                  ->withInput($request->only('email', 'remember'));
    // }

// public function showSiteSummaryPage()
    // {
    //     $user = Auth::user();

//     if ($user->role != 1 && !$user->site_id) {
    //         return redirect()->route('dashboard')->with('error', 'You are not authorized to view this page.');
    //     }

//     // ✅ Determine site access
    //     if ($user->role == 1) {
    //         // Admin - access to all sites
    //         $siteIds = DB::table('master_land')->distinct()->pluck('site_id')->toArray();
    //     } else {
    //         // Regular user - only their own site
    //         $siteIds = [$user->site_id];
    //     }

//     // --- Aggregated Stats ---
    //     $totalSites = DB::table('master_land')->whereIn('site_id', $siteIds)->distinct()->count('site_id');
    //     $totalAreaOverall = DB::table('master_land')->whereIn('site_id', $siteIds)->sum('area_ha');
    //     $totalProductionOverall = 0; // Future: can pull from showing_oprations
    //     $totalMachinesOverall = DB::table('master_machine')->whereIn('site_id', $siteIds)->count();

//     $stats = [
    //         'total_sites' => $totalSites,
    //         'total_area' => $totalAreaOverall,
    //         'total_production' => $totalProductionOverall,
    //         'total_machines' => $totalMachinesOverall,
    //     ];

//     // --- Per-Site Summary Table ---
    //     $sites_data_query = DB::table('master_land as ml')
    //         ->select(
    //             'ml.site_id as site_name',
    //             DB::raw('MAX(ml.super_wiser_name) as unit_incharge'),
    //             DB::raw('SUM(ml.area_ha) as area_acre'),
    //             DB::raw('(SELECT GROUP_CONCAT(DISTINCT mf.fertilizer_type SEPARATOR ", ")
    //                       FROM fertilizer_soil_record fsr
    //                       JOIN master_fertilizer mf ON fsr.fertilizer_id = mf.id
    //                       WHERE fsr.site_id = ml.site_id) as fertilizer_used'),
    //             DB::raw('MAX(YEAR(ml.created_at)) as year'),
    //             DB::raw('(SELECT SUM(so.area_covered)
    //                       FROM showing_oprations so
    //                       WHERE so.site_id = ml.site_id) as crop_production_mt'),
    //             DB::raw('(SELECT SUM(mf.stock_kg) FROM master_fertilizer mf WHERE mf.site_id = ml.site_id) as fertilizer_stock_kg'),
    //             DB::raw('(SELECT SUM(ms.seed_stock_kg) FROM master_seed ms WHERE ms.site_id = ml.site_id) as seed_stock_kg'),
    //             DB::raw('(SELECT COUNT(DISTINCT mm.id) FROM master_machine mm WHERE mm.site_id = ml.site_id) as machines_count'),
    //             DB::raw('(SELECT COUNT(DISTINCT mt.id) FROM master_tractors mt WHERE mt.site_id = ml.site_id) as tractors_count')
    //         )
    //         ->whereIn('ml.site_id', $siteIds)
    //         ->groupBy('ml.site_id')
    //         ->orderBy('ml.site_id');

//     $sites_data = $sites_data_query->get()->map(fn($item) => (array)$item)->all();

//     if (empty($sites_data)) {
    //         $sites_data = [
    //             ['site_name' => 1, 'unit_incharge' => 'Pawan Sir', 'area_acre' => 2345, 'fertilizer_used' => 'Urea, DAP', 'year' => 2024, 'crop_production_mt' => 5245, 'fertilizer_stock_kg' => 654, 'seed_stock_kg' => 455, 'machines_count' => 33, 'tractors_count' => 13],
    //             ['site_name' => 2, 'unit_incharge' => 'Mr. B', 'area_acre' => 4345, 'fertilizer_used' => 'Potash', 'year' => 2024, 'crop_production_mt' => 5424, 'fertilizer_stock_kg' => 987, 'seed_stock_kg' => 654, 'machines_count' => 54, 'tractors_count' => 15],
    //         ];
    //         $stats['total_sites'] = count($sites_data);
    //         $stats['total_area'] = array_sum(array_column($sites_data, 'area_acre'));
    //         $stats['total_production'] = array_sum(array_column($sites_data, 'crop_production_mt'));
    //         $stats['total_machines'] = array_sum(array_column($sites_data, 'machines_count'));
    //     }

//     return view('admin.site_summary', compact('stats', 'sites_data'));
    // }

// public function logout(Request $request)
    // {
    //     Auth::logout();
    //     $request->session()->invalidate();
    //     $request->session()->regenerateToken();

//     Cookie::queue(Cookie::forget('remember_email'));
    //     Cookie::queue(Cookie::forget('remember_password'));
    //     Cookie::queue(Cookie::forget('remember_checked'));

//     return redirect()->route('login.form');
    // }
    
    
      public function showLoginForm()
    {
        $email = Cookie::get('remember_email');
        $passwordValue = Cookie::get('remember_password');
        $password = $passwordValue ? Crypt::decryptString($passwordValue) : null;
        $remember = Cookie::get('remember_checked') === 'true';

return view('auth.login', compact('email', 'password', 'remember'));
    }

/**
     * Handle an authentication attempt.
     */
    public function loginDashboard(Request $request)
    {
        $request->validate([
            'email' => 'required|email|string',
            'password' => 'required|string',
        ]);

$remember = $request->has('remember');
        $credentials = $request->only('email', 'password');

if (Auth::attempt($credentials, $remember)) {
            $request->session()->regenerate();
            $user = Auth::user();

if ($user && Schema::hasColumn('users', 'last_login')) {
                $user->last_login = now();
                $user->saveQuietly();
            }

if ($remember) {
                Cookie::queue('remember_email', $request->email, 60 * 24 * 30);
                Cookie::queue('remember_password', Crypt::encryptString($request->password), 60 * 24 * 30);
                Cookie::queue('remember_checked', 'true', 60 * 24 * 30);
            } else {
                Cookie::queue(Cookie::forget('remember_email'));
                Cookie::queue(Cookie::forget('remember_password'));
                Cookie::queue(Cookie::forget('remember_checked'));
            }

if ($user->role == 1) {
                return redirect()->route('site.summary');
            } elseif ($user->role == 2) {
                return redirect()->route('dashboard');
            } elseif ($user->role == 3) {
                // This line correctly redirects role 3 users to the 'dashboard' route.
                // The logic for displaying site 2 data for role 3 is handled in DashboardController.
                return redirect()->route('dashboard'); 
            }
            // Fallback redirection if a role is not explicitly handled above
            return redirect()->route('dashboard');
        }

return back()->with('error', 'The provided credentials do not match our records.')
                     ->withInput($request->only('email', 'remember'));
    }

/**
     * Display the site summary page after login for specific roles.
     */
    // public function showSiteSummaryPage()
    // {
    //     $user = Auth::user();
    //     if (!($user->role == 1)) {
    //         return redirect()->route('dashboard')->with('error', 'You are not authorized to view this page.');
    //     }

//     // --- Fetch Aggregated Data for Stat Cards (Overall Summary) ---
    //     $totalSites = DB::table('master_land')->distinct('site_id')->count('site_id');
    //     $totalAreaOverall = DB::table('master_land')->sum('area_ha');
    //     // TODO: Update this logic. 'estimated_yield' column not found in 'showing_oprations'.
    //     $totalProductionOverall = 0; // DB::table('showing_oprations')->sum('your_actual_production_column');
    //     $totalMachinesOverall = DB::table('master_machine')->count(); // This counts all machines, not site-specific for the card

//     // --- Fetch Aggregated Data for each Site for the Table ---
    //     $sites_data_query = DB::table('master_land as ml')
    //         ->select(
    //             'ml.site_id as site_name', // This is the site_id, will be used to display "Andeshnagar" or "CCBF2" in Blade
    //             DB::raw('MAX(ml.super_wiser_name) as unit_incharge'), // Get one supervisor name per site, or adjust logic
    //             DB::raw('SUM(ml.area_ha) as area_acre'), // Sum area for each site
    //             DB::raw('(SELECT GROUP_CONCAT(DISTINCT mf.fertilizer_type SEPARATOR ", ")
    //                         FROM fertilizer_soil_record fsr
    //                         JOIN master_fertilizer mf ON fsr.fertilizer_id = mf.id
    //                         WHERE fsr.site_id = ml.site_id) as fertilizer_used'),
    //             // TODO: Determine how 'year' should be aggregated. Using MAX as an example.
    //             // If 'year' comes from a different table related to production/activity, adjust subquery.
    //             // For now, using a placeholder or MAX from a relevant date column if available in master_land.
    //             // If master_land has a date like 'establishment_date', you could use YEAR(MAX(ml.establishment_date))
    //             DB::raw('MAX(YEAR(ml.created_at)) as year'), // Example: using the year of the latest land record for that site
    //             // TODO: Update this subquery for actual crop production per site.
    //             DB::raw('(SELECT SUM(so.area_covered) /* Replace area_covered with actual yield column */
    //                         FROM showing_oprations so
    //                         WHERE so.site_id = ml.site_id) as crop_production_mt'), // Example: summing a column from showing_oprations
    //             DB::raw('(SELECT SUM(mf.stock_kg) FROM master_fertilizer mf WHERE mf.site_id = ml.site_id) as fertilizer_stock_kg'),
    //             DB::raw('(SELECT SUM(ms.seed_stock_kg) FROM master_seed ms WHERE ms.site_id = ml.site_id) as seed_stock_kg'),
    //             DB::raw('(SELECT COUNT(DISTINCT mm.id) FROM master_machine mm WHERE mm.site_id = ml.site_id) as machines_count'),
    //             DB::raw('(SELECT COUNT(DISTINCT mt.id) FROM master_tractors mt WHERE mt.site_id = ml.site_id) as tractors_count')
    //         )
    //         ->groupBy('ml.site_id')
    //         ->orderBy('ml.site_id');

//     $sites_data = $sites_data_query->get()->map(function ($item) {
    //         return (array)$item;
    //     })->all();

//     // Fallback static data for UI testing if the query is complex to set up initially
    //     if (empty($sites_data)) {
    //          $sites_data = [
    //              ['site_name' => 1, 'unit_incharge' => 'Pawan Sir', 'area_acre' => 2345, 'fertilizer_used' => 'Urea, DAP', 'year' => 2024, 'crop_production_mt' => 5245, 'fertilizer_stock_kg' => 654, 'seed_stock_kg' => 455, 'machines_count' => 33, 'tractors_count' => 13],
    //              ['site_name' => 2, 'unit_incharge' => 'Mr. B', 'area_acre' => 4345, 'fertilizer_used' => 'Potash', 'year' => 2024, 'crop_production_mt' => 5424, 'fertilizer_stock_kg' => 987, 'seed_stock_kg' => 654, 'machines_count' => 54, 'tractors_count' => 15],
    //          ];
    //          // Recalculate overall stats if using static site data for consistency
    //          $stats['total_sites'] = count($sites_data);
    //          $stats['total_area'] = array_sum(array_column($sites_data, 'area_acre'));
    //          $stats['total_production'] = array_sum(array_column($sites_data, 'crop_production_mt'));
    //          $stats['total_machines'] = array_sum(array_column($sites_data, 'machines_count'));
    //     }

//     return view('admin.site_summary', compact('stats', 'sites_data'));
    // }
    
      public function showSiteSummaryPage()
{
    $user = Auth::user();
    if (!($user->role == 1)) {
        return redirect()->route('dashboard')->with('error', 'You are not authorized to view this page.');
    }

// --- Fetch Aggregated Data for Stat Cards (Overall Summary) ---
    $totalSites = DB::table('master_land')->distinct('site_id')->count('site_id');
    $totalAreaOverall = DB::table('master_land')->sum('area_ha');
    $totalProductionOverall = DB::table('harvesting_update')
        ->whereYear('created_at', now()->year)
        ->sum(DB::raw('CAST(COALESCE(yield_mt, 0) AS DECIMAL(10,2))'));
    /*
    // Alternative: Use seed_total_mt (float) if preferred
    $totalProductionOverall = DB::table('harvesting_update')
        ->whereYear('created_at', now()->year)
        ->sum('seed_total_mt');
    */
    $totalMachinesOverall = DB::table('master_machine')->count();

$stats = [
        'total_sites' => $totalSites,
        'total_area' => $totalAreaOverall,
        'total_production' => $totalProductionOverall,
        'total_machines' => $totalMachinesOverall,
    ];

// --- Fetch Aggregated Data for each Site for the Table ---
    $sites_data_query = DB::table('master_land as ml')
        ->select(
            'ml.site_id as site_name',
            DB::raw('MAX(ml.super_wiser_name) as unit_incharge'),
            DB::raw('SUM(ml.area_ha) as area_acre'),
            DB::raw('(SELECT GROUP_CONCAT(DISTINCT mf.fertilizer_type SEPARATOR ", ")
                        FROM fertilizer_soil_record fsr
                        JOIN master_fertilizer mf ON fsr.fertilizer_id = mf.id
                        WHERE fsr.site_id = ml.site_id) as fertilizer_used'),
            DB::raw('MAX(YEAR(ml.created_at)) as year'),
            DB::raw('(SELECT SUM(CAST(COALESCE(hu.yield_mt, 0) AS DECIMAL(10,2)))
                        FROM harvesting_update hu
                        WHERE hu.site_id = ml.site_id AND YEAR(hu.created_at) = YEAR(CURDATE())) as crop_production_mt'),
            /*
            // Alternative: Use seed_total_mt
            DB::raw('(SELECT SUM(hu.seed_total_mt)
                        FROM harvesting_update hu
                        WHERE hu.site_id = ml.site_id AND YEAR(hu.created_at) = YEAR(CURDATE())) as crop_production_mt'),
            */
            DB::raw('(SELECT SUM(mf.stock_kg) FROM master_fertilizer mf WHERE mf.site_id = ml.site_id) as fertilizer_stock_kg'),
            DB::raw('(SELECT SUM(ms.seed_stock_kg) FROM master_seed ms WHERE ms.site_id = ml.site_id) as seed_stock_kg'),
            DB::raw('(SELECT COUNT(DISTINCT mm.id) FROM master_machine mm WHERE mm.site_id = ml.site_id) as machines_count'),
            DB::raw('(SELECT COUNT(DISTINCT mt.id) FROM master_tractors mt WHERE mt.site_id = ml.site_id) as tractors_count')
        )
        ->groupBy('ml.site_id')
        ->orderBy('ml.site_id');

$sites_data = $sites_data_query->get()->map(function ($item) {
            return (array)$item;
        })->all();

// Fallback static data for UI testing if the query is complex to set up initially
        if (empty($sites_data)) {
             $sites_data = [
                 ['site_name' => 1, 'unit_incharge' => 'Pawan Sir', 'area_acre' => 2345, 'fertilizer_used' => 'Urea, DAP', 'year' => 2024, 'crop_production_mt' => 5245, 'fertilizer_stock_kg' => 654, 'seed_stock_kg' => 455, 'machines_count' => 33, 'tractors_count' => 13],
                 ['site_name' => 2, 'unit_incharge' => 'Mr. B', 'area_acre' => 4345, 'fertilizer_used' => 'Potash', 'year' => 2024, 'crop_production_mt' => 5424, 'fertilizer_stock_kg' => 987, 'seed_stock_kg' => 654, 'machines_count' => 54, 'tractors_count' => 15],
             ];
             // Recalculate overall stats if using static site data for consistency
             $stats['total_sites'] = count($sites_data);
             $stats['total_area'] = array_sum(array_column($sites_data, 'area_acre'));
             $stats['total_production'] = array_sum(array_column($sites_data, 'crop_production_mt'));
             $stats['total_machines'] = array_sum(array_column($sites_data, 'machines_count'));
        }

return view('admin.site_summary', compact('stats', 'sites_data'));
    }

/**
     * Log the user out of the application.
     */
    public function logout(Request $request)
    {
        Auth::logout();
        $request->session()->invalidate();
        $request->session()->regenerateToken();

Cookie::queue(Cookie::forget('remember_email'));
        Cookie::queue(Cookie::forget('remember_password'));
        Cookie::queue(Cookie::forget('remember_checked'));

return redirect()->route('login.form');
    }
}