<?php

namespace App\Http\Controllers;
namespace App\Http\Controllers;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
use App\Models\Unit;
use App\Models\State;
use App\Models\Project;
use App\Models\Household;
use App\Models\Livelihood;
use App\Models\Season;
use App\Models\Category;
use App\Models\Crop;
use App\Models\FinancialYear;
use App\Models\Baseline;
use App\Models\land;
use App\Models\livestock;
use App\Models\ProjectType;
use App\Models\TechnicalFeasibility;
use App\Models\WaterUserGroup;
use App\Models\Cropping;
use App\Models\AlliedSector;
use App\Models\MicroEnterprise;
use App\Models\DailyWageLabor;
use App\Models\WaterHarvestingStructuresPlanning;
use App\Models\SourceOfFamilyIncome;
use App\Models\PreWorkLevel;
use App\Models\User;
use App\Models\MeasurementBook;
use Illuminate\Support\Facades\Response;


class UserReportController extends Controller
{
    public function index(Request $request)
    {
        $units = Unit::all();
        $states = State::all();
        //$query = Baseline::with(['village', 'block', 'district', 'user', 'unit','households' ,'projectType', 'waterHarvestingStructuresPlanning'])->where('user_id', Auth::id());


$userIds = User::where('user_id', Auth::id())->pluck('id')->toArray();

$userIds[] = Auth::id();

$query = Baseline::with([
        'village','block','district','user','unit','households',
        'projectType','waterHarvestingStructuresPlanning'
    ])
    ->whereIn('user_id', $userIds)
    ->orderByDesc('id');


        // Apply filters
        if ($request->unit_id) {
            $query->where('unit_id', $request->unit_id);
        }
    
        if ($request->state_id) {
            $query->where('state_id', $request->state_id);
        }
    
        if ($request->surveyed_by) {
            $query->whereHas('user', function ($q) use ($request) {
                $q->where('name', 'like', '%' . $request->surveyed_by . '%');
            });
        }
    
        if ($request->status) {
            // Case-insensitive match
            $query->whereRaw('LOWER(status) = ?', [strtolower($request->status)]);
        }
    
        if ($request->date_from) {
            $query->whereDate('date_of_survey', '>=', $request->date_from);
        }
    
        if ($request->date_to) {
            $query->whereDate('date_of_survey', '<=', $request->date_to);
        }
    
        $reports = $query->paginate(25);
        return view('reports.UserBaselineReport', compact('reports', 'units', 'states'));
    }


        public function indexPrelevel(Request $request)
            {
                 $units = Unit::all(['id','name']);
                 $states = State::all(['id','name']);
                
                $userIds = User::where('user_id', Auth::id())->pluck('id')->toArray();

                $userIds[] = Auth::id();

                $query = Baseline::with([
                'village:id,name','block:id,name','district:id,name','user:id,name', 'unit:id,name','households',
                'projectType:id,name', 'waterHarvestingStructuresPlanning:id,baseline_id,width,length,depth_or_height'
                ])
                ->whereIn('user_id', $userIds)
                ->orderByDesc('id');
                // Apply filters
                if ($request->unit_id) {
                $query->where('unit_id', $request->unit_id);
                }
        
                if ($request->state_id) {
                $query->where('state_id', $request->state_id);
                }
        
                if ($request->surveyed_by) {
                $query->whereHas('user', function ($q) use ($request) {
                    $q->where('name', 'like', '%' . $request->surveyed_by . '%');
                });
            }
        
            if ($request->status) {
                // Case-insensitive match
                $query->whereRaw('LOWER(status) = ?', [strtolower($request->status)]);
            }
        
            if ($request->date_from) {
                $query->whereDate('date_of_survey', '>=', $request->date_from);
            }
        
            if ($request->date_to) {
                $query->whereDate('date_of_survey', '<=', $request->date_to);
            }
        
            $reports = $query->paginate(25);
            return view('reports.P', compact('reports', 'units', 'states'));
        }


public function storeMbShow ($id)
{
    // Fetch baseline with related pre work level and other necessary relations
    $baseline = Baseline::with([
        'preWorkLevels', 
        'lands', 
        'waterHarvestingStructuresPlannings', 
        'state', 
        'district', 
        'panchayat', 
        'village'
    ])->findOrFail($id);
    // Optional: Check if preWorkLevel data exists
    if ($baseline->preWorkLevels->isEmpty()) {
        return redirect()->back()->with('error', 'No Pre Work Level data found for this baseline.');
    }

    
    // Return the view with the data
    return view('userData.PreWorkLevelsReport', compact('baseline'));
}


    public function show($id)
    {
        $baseline = Baseline::with([
            'households',
            'lands',
            'livestocks',
            'waterUserGroups',
            'technicalFeasibility',
          //'WaterHarvestingStructuresPlanning',
            'waterHarvestingStructuresPlanning.projectType',
            'croppings',
            'alliedsectors',
            'microEnterprises',
            'dailyWageLabors',
            'sourceOfFamilyIncome'
        ])->findOrFail($id);
    
        return view('userData.BaselineView', compact('baseline'));
    }


    public function technicalReport(Request $request)
{
    // Fetch units and states for the filter dropdowns
    $units = Unit::all();
    $states = State::all();

    // Filter Baseline records and paginate
    $reports = Baseline::with([
        'village', 
        'block',
        'district',
        'user',
        'waterHarvestingStructuresPlanning',
    ])
    ->when($request->state_id, function($query) use ($request) {
        $query->whereHas('village', function ($q) use ($request) {
            $q->where('state_id', $request->state_id);
        });
    })
    ->paginate(25);

    // Filter WaterHarvestingStructuresPlanning records and get results
    $query = WaterHarvestingStructuresPlanning::with([
        'user',
        'technicalFeasibility',
        'baseline',
        'baseline.village',
        'baseline.block',
        'baseline.district',
    ]);

    // Apply additional filters if present in the request
    if ($request->unit_id) {
        $query->whereHas('baseline', function ($q) use ($request) {
            $q->where('unit_id', $request->unit_id);
        });
    }

    if ($request->state_id) {
        $query->whereHas('baseline.village', function ($q) use ($request) {
            $q->where('state_id', $request->state_id);
        });
    }

    if ($request->surveyed_by) {
        $query->where('name_of_respondent', 'like', '%' . $request->surveyed_by . '%');
    }

    if ($request->filled('status')) {
        $query->where('status', $request->status);
    }

    if ($request->search_name) {
        $query->where('name_of_respondent', 'like', '%' . $request->search_name . '%');
    }

    // Execute the query and get records
    $records = $query->get();

    // Return the view with the required data
    return view('userData.TechnicalFeasibility', compact('records', 'units', 'reports', 'states'));
}


 public function addMb($id)
    {
        $data = PreWorkLevel::with('preWorkLevels') // assuming related rows
            ->findOrFail($id);

        return view('pre_work_levels.show', compact('data'));
    }


public function prelLevelReportIndex(Request $request)
    {
        $units = Unit::all();
        $states = State::all();

       // $query = Baseline::with(['village','PreWorkLevel', 'block', 'district', 'user', 'unit','households' ,'projectType', 'waterHarvestingStructuresPlanning'])->where('user_id', Auth::id());

$reports = PreWorkLevel::with([
    'village',
    'block',
    'district',
    'user',
    'projectType',
    'baseline.unit',
      'baseline.waterHarvestingStructuresPlanning'
])
->where('user_id', Auth::id())
->whereHas('baseline')
->paginate(25);


        // Apply filters
         return view('reports.UserPreLevelReport', compact('reports'));
    }


    public function PreLevelWorkShow($id)
    {
        $baseline = Baseline::with([
        'preWorkLevels', 
        'lands', 
        'waterHarvestingStructuresPlannings', 
        'state', 
        'district', 
        'panchayat', 
        'village'
    ])->findOrFail($id);
    // Optional: Check if preWorkLevel data exists
    if ($baseline->preWorkLevels->isEmpty()) {
        return redirect()->back()->with('error', 'No Pre Work Level data found for this baseline.');
    }

    // Return the view with the data
    return view('userData.PreWorkLevelsReport', compact('baseline'));

    }


public function exportWhsMcft(Request $request)
{
    $role = Auth::user()->role_id;

    // eager-load baseline.households and baseline.waterUserGroups, and readings filtered to serial_no = 1
    $query = MeasurementBook::with([
        'unit','state','district','block','panchayat','village','financialYear','user',
        'baseline.households','baseline.waterUserGroups','projectType',
        'readings' => function ($q) { // use your readings() relation
            $q->where('serial_no', 1);
        }
    ])->latest();

    if ($role != 1) {
        $query->where('user_id', Auth::id());
    }

    $rows = $query->get();

    $filename = 'whs_mcft_' . now()->format('Ymd') . '.csv';

    $headers = [
        "Content-Type" => "text/csv",
        "Content-Disposition" => "attachment; filename=\"{$filename}\"",
        "Pragma" => "no-cache",
        "Cache-Control" => "must-revalidate, post-check=0, pre-check=0",
        "Expires" => "0"
    ];

    // NOTE: column names count must match the columns in $line below
    $columns = [
        'Sl No.','Unit','District','Name of Block','GP','Name of Village','Year of Establishment',
        'Benificary Name (Land Owner)','Categery (SC/ST/OBC)','Type of Structure','UoM','Qty',
        'Plot No.','Ponds Specification (size in m)','Surface Area (sqm)','Latitude','Longitude',
        'Volume of water (cum)','Water Conservation Potential (in Mcft)','Irrigation Potential created (In ha)',
        'HH cover','No of lives reached','User'
    ];

    $callback = function() use ($rows, $columns) {
        $file = fopen('php://output', 'w');
        fputcsv($file, $columns);

        $sl = 1;
        foreach ($rows as $r) {
            $mb = $r;
            $baselineModel = $r->baseline; // may be null

            // --- Category from baseline->households ---
            $category = '';
            $households = collect();
            if ($baselineModel) {
                $households = $baselineModel->households ?? collect();
            }
            if ($households->isNotEmpty()) {
                $head = $households->firstWhere('is_head', 1);
                $category = $head ? ($head->category ?? '') : ($households->first()->category ?? '');
            }

            // --- Beneficiary name preferring baseline ---
            $beneficiaryName = $baselineModel->name_of_respondent
                ?? $baselineModel->beneficiary_name
                ?? $mb->name_of_respondent
                ?? $mb->beneficiary_name
                ?? '';

            // --- Pond spec parsing (same logic, defensive) ---
            $pondSpecRaw = trim((string)($mb->dimension ?? ($baselineModel->dimension ?? '')));
            $extractedType = null;
            $extractedArea = null;
            if ($pondSpecRaw !== '') {
                if (preg_match('/^(.*?)\s*\(([^)]*)\)/u', $pondSpecRaw, $m)) {
                    $extractedType = trim($m[1]);
                    $inside = $m[2];
                    if (preg_match_all('/[\d\.]+/u', $inside, $numMatches) && count($numMatches[0]) >= 2) {
                        $len = (float)$numMatches[0][0];
                        $wid = (float)$numMatches[0][1];
                        $areaVal = $len * $wid;
                        $extractedArea = ($areaVal == floor($areaVal)) ? (int)$areaVal : round($areaVal, 2);
                    }
                } elseif (preg_match('/^([A-Za-z0-9\s\-\&\/]+)[\:\-\s]*([\d\.\sx×\*\-by,]+)/iu', $pondSpecRaw, $m2)) {
                    $extractedType = trim($m2[1]);
                    $inside = $m2[2];
                    if (preg_match_all('/[\d\.]+/u', $inside, $numMatches2) && count($numMatches2[0]) >= 2) {
                        $len = (float)$numMatches2[0][0];
                        $wid = (float)$numMatches2[0][1];
                        $areaVal = $len * $wid;
                        $extractedArea = ($areaVal == floor($areaVal)) ? (int)$areaVal : round($areaVal, 2);
                    }
                } elseif (preg_match_all('/([\d\.]+)\s*[x×\*]\s*([\d\.]+)/u', $pondSpecRaw, $pairs)) {
                    $len = (float)$pairs[1][0];
                    $wid = (float)$pairs[2][0];
                    $areaVal = $len * $wid;
                    $extractedArea = ($areaVal == floor($areaVal)) ? (int)$areaVal : round($areaVal, 2);
                    $typeCandidate = trim(preg_replace('/[\d\.\sx×\*\,\(\)\/\-]+/u', '', $pondSpecRaw));
                    if ($typeCandidate !== '') $extractedType = $typeCandidate;
                }
            }

            $typeOfStructure = $extractedType
                ?: (optional($mb->projectType)->name ?? $mb->type_of_structure ?? ($baselineModel->type_of_structure ?? ''));
            $surfaceArea = $extractedArea !== null
                ? $extractedArea
                : ($mb->total ?? $mb->surface_area ?? ($baselineModel->surface_area ?? ''));

            if (is_numeric($surfaceArea)) {
                $surfaceArea = ($surfaceArea == floor($surfaceArea)) ? (int)$surfaceArea : round((float)$surfaceArea, 2);
            }

            $pondSpecificationCell = $pondSpecRaw;

            // --- reading (serial_no = 1) ---
            $reading = null;
            if ($mb->relationLoaded('readings')) {
                $reading = $mb->readings->first() ?? null;
            } else {
                try {
                    $reading = $mb->readings()->where('serial_no', 1)->first();
                } catch (\Throwable $e) {
                    $reading = null;
                }
            }

            $readingVolume = $reading ? ($reading->total ?? $reading->volume ?? $reading->volume_cum ?? $reading->cum ?? null) : null;
            // prefer reading, then mb, then baseline
            $volumeOfWaterRaw = $readingVolume ?? ($mb->total ?? $mb->volume_cum ?? ($baselineModel->total ?? $baselineModel->volume_cum ?? null));
            $volumeOfWater = is_numeric($volumeOfWaterRaw) ? (float)$volumeOfWaterRaw : null;

            // --- water_user_groups unique beneficiaries count ---
            $wugCount = null;
            if ($baselineModel) {
                if ($baselineModel->relationLoaded('waterUserGroups')) {
                    $names = collect($baselineModel->waterUserGroups)->pluck('name_of_beneficiary')->filter()->unique();
                    $wugCount = $names->count();
                } else {
                    try {
                        $names = optional($baselineModel->waterUserGroups())->pluck('name_of_beneficiary') ?? collect();
                        $names = collect($names)->filter()->unique();
                        $wugCount = $names->count();
                    } catch (\Throwable $e) {
                        $wugCount = null;
                    }
                }
            }

            // final HH cover (number of HHs) — prefer wugCount else existing fields
            $noOfLivesReached = $wugCount !== null ? (int)$wugCount : ($mb->no_of_lives_reached ?? ($baselineModel->no_of_lives_reached ?? ''));

            // --- compute conversions (defensive) ---
            // Water Conservation Potential in Mcft (assuming $volumeOfWater is in cubic metres)
            // 1 cubic metre = 35.3147 cubic feet. Mcft = million cubic feet.
            $waterConservationMcft = '';
            if (is_numeric($volumeOfWater)) {
                $waterConservationMcft = round(($volumeOfWater * 35.3147) / 1e6, 6); // keep 6 decimals
            }

            // Irrigation potential in hectares.
            // NOTE: divisor depends on your local assumption (m3 per ha). User previously used /1000;
            // that implies 1000 m3 per ha (seasonal/crop dependent). Keep as before but round:
            $irrigationPotentialHa = '';
            if (is_numeric($volumeOfWater)) {
                $irrigationPotentialHa = round($volumeOfWater / 1000, 3); // adjust divisor if you use different m3/ha
            }

            // HH cover (number of households) and No of lives reached (persons) — here I use persons = HH * 5 (as before)
            $hhCover = is_numeric($noOfLivesReached) ? (int)$noOfLivesReached : ($noOfLivesReached ?? '');
            $personsReached = is_numeric($hhCover) ? ($hhCover * 5) : ''; // 5 persons per HH assumption

            // Prefer baseline financialYear name first then measurement book's FY
            $fyName = optional($baselineModel->financialYear)->name ?? optional($mb->financialYear)->name ?? '';

            // Safe lat/lon fallback to baseline if null on mb
            $latitude = $mb->latitude !== null ? (string)$mb->latitude : (($baselineModel && $baselineModel->latitude !== null) ? (string)$baselineModel->latitude : '');
            $longitude = $mb->longitude !== null ? (string)$mb->longitude : (($baselineModel && $baselineModel->longitude !== null) ? (string)$baselineModel->longitude : '');

            // build row — make sure this order matches $columns
            $line = [
                $sl,
                optional($mb->unit)->name ?? '',
                optional($mb->district)->name ?? '',
                optional($mb->block)->name ?? '',
                optional($mb->panchayat)->name ?? '',
                optional($mb->village)->name ?? '',
                $fyName,
                $beneficiaryName,
                $category,
                $typeOfStructure,
                $mb->uom ?? 'Nos',
                $mb->qty ?? '1',
                $mb->plot_no ?? ($baselineModel->plot_no ?? ''),
                $pondSpecificationCell,
                $surfaceArea,
                $latitude,
                $longitude,
                $volumeOfWater !== null ? $volumeOfWater : '',
                $waterConservationMcft,
                $irrigationPotentialHa,
                $hhCover,
                $personsReached,
                optional($mb->user)->name ?? ''
            ];

            fputcsv($file, $line);
            $sl++;
        }

        fclose($file);
    };

    return response()->stream($callback, 200, $headers);
}

}