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\Schema;
use Illuminate\Pagination\LengthAwarePaginator;
use Carbon\Carbon;
use Symfony\Component\HttpFoundation\StreamedResponse;

use Illuminate\Support\Facades\Auth;

class CostAnalysisController extends Controller
{
    protected $activityTables = [
        'activity_monitoring',
        'area_levelings',
        'crop_protection',
        'fertilizer_soil_record',
        'harvestings',
        'harvesting_update',
        'inter_culture',
        'irrigation_records',
        'land_prepration',
        'post_irrigation',
        'pre_irrigation',
        'pre_land_preparation',
        'showing_oprations',
        'silage_making',
        'hay_making',
        'harvest_store_manage',
    ];

// DISPLAY name and table names with new ordered activity mapping
    protected $orderedActivities = [
        ['display' => 'Area Leveling', 'table' => 'area_levelings'],
        ['display' => 'Pre Land Preparation', 'table' => 'pre_land_preparation'],
        ['display' => 'Pre Irrigation', 'table' => 'pre_irrigation'],
        ['display' => 'Land Preparation', 'table' => 'land_prepration'],
        ['display' => 'Sowing', 'table' => 'showing_oprations'],
        ['display' => 'Post Irrigation', 'table' => 'post_irrigation'],
        ['display' => 'Fertilizer', 'table' => 'fertilizer_soil_record'],
        ['display' => 'Inter Culture', 'table' => 'inter_culture'],
        ['display' => 'Crop Protection', 'table' => 'crop_protection'],
        ['display' => 'Harvest', 'table' => 'harvesting_update'], // Renamed for clarity, assumed to be the main 'Harvest'
        ['display' => 'Hay Making', 'table' => 'hay_making'],
        ['display' => 'Silage Making', 'table' => 'silage_making'],
        // ['display' => 'Harvest Store Manage', 'table' => 'harvest_store_manage'], // REMOVED from dropdown as requested
    ];

protected $seasons = [
        'kharif' => ['display' => 'Kharif (जनवरी से अक्टूबर)', 'start_month' => 6, 'end_month' => 10],
        'rabi' => ['display' => 'Rabi (नवंबर से अप्रैल)', 'start_month' => 11, 'end_month' => 4],
        'zaid' => ['display' => 'Zaid (अप्रैल से जून)', 'start_month' => 4, 'end_month' => 6]
    ];

protected $expectedColumns = [
        'block_name',
        'plot_name',
        'date',
        'area',
        'irrigation_date',
        'area_acre',
        'machine_id',
        'tractor_id', // Added tractor_id column
        'hsd_consumption',
        'manpower_type',
        'electricity_units',
        'cost_per_unit',
        'fertilizer_id',
        'fertilizer_quantity',
        'major_maintenance',
        // 'production_cost', // REMOVED - Process Cost column removed
        'profit',
        'loss',
        'area_covered',

'seed_name',
        'total_cost',
        'created_at',
        'unskilled', 'semi_skilled_1', 'semi_skilled_2',
        'yield_mt',
        'total_mt',
        'price_per_mt',
        'sale_price',
        'product_id',
    ];

protected $costConfig = [
        'hsd_rate_per_liter' => 88.21,
        'fertilizer_rate_per_kg' => 5.36,
        'manpower_rates' => [
            'skilled' => 500,
            'unskilled' => 300,
            'default' => 400
        ]
    ];

protected function determineSeason($date)
    {
        if (!$date) return null;

if (is_string($date)) {
            $date = Carbon::parse($date);
        }

$month = (int)$date->format('n');

if ($month >= 6 && $month <= 10) {
            return 'kharif';
        }

if ($month >= 11 || $month <= 4) {
            return 'rabi';
        }

if ($month >= 4 && $month <= 6) {
            return 'zaid';
        }

return null;
    }

protected function getSeedBasedYieldTotals($blockFilter = null, $plotFilter = null, $seedNameFilter = null, $siteId = null)
{
    if (
        !Schema::hasTable('harvesting_update') ||
        !Schema::hasColumn('harvesting_update', 'yield_mt') ||
        !Schema::hasTable('master_seed') ||
        !Schema::hasColumn('master_seed', 'sale_price')
    ) {
        return (object)[
            'seedTotals' => [],
            'blockwiseTotals' => [],
            'grandTotal' => 0,
            'grandTotalPrice' => 0,
            'grandTotalSold' => 0
        ];
    }

$baseQuery = "FROM harvesting_update hu
                  LEFT JOIN master_seed ms ON hu.seed_name = ms.seed_name
                  WHERE hu.seed_name IS NOT NULL AND hu.seed_name <> ''";
    $params = [];

if ($siteId && Schema::hasColumn('harvesting_update', 'site_id')) {
        $baseQuery .= " AND hu.site_id = ?";
        $params[] = $siteId;
    }

if ($blockFilter) {
        $baseQuery .= " AND hu.block_name = ?";
        $params[] = $blockFilter;
    }

if ($plotFilter) {
        $baseQuery .= " AND hu.plot_name = ?";
        $params[] = $plotFilter;
    }
    if ($seedNameFilter) {
        $baseQuery .= " AND hu.seed_name = ?";
        $params[] = $seedNameFilter;
    }

$seedTotalsQuery = "
        SELECT
            hu.seed_name,
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2))) AS total_production_mt,
            COALESCE(CAST(ms.sale_price AS DECIMAL(10,2)), 0) AS price_per_mt,
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2)) * COALESCE(CAST(ms.sale_price AS DECIMAL(10,2)), 0)) AS total_price
        " . $baseQuery . "
        GROUP BY hu.seed_name, ms.sale_price
    ";

$seedTotals = DB::select($seedTotalsQuery, $params);

// Get sold MT data from harvest_store_manage and harvest_sale_records
    foreach ($seedTotals as &$seedTotal) {
        $soldMtQuery = "
            SELECT SUM(COALESCE(hsr.sold_mt, 0)) as total_sold_mt
            FROM harvest_store_manage hsm
            LEFT JOIN harvest_sale_records hsr ON hsm.id = hsr.harvest_store_id
            WHERE hsm.seed_name = ?
            AND hsm.product_id = 1
        ";
        $soldParams = [$seedTotal->seed_name];
        
        if ($siteId) {
            $soldMtQuery .= " AND hsm.site_id = ?";
            $soldParams[] = $siteId;
        }
        if ($blockFilter) {
            $soldMtQuery .= " AND hsm.block_name = ?";
            $soldParams[] = $blockFilter;
        }
        if ($plotFilter) {
            $soldMtQuery .= " AND hsm.plot_name = ?";
            $soldParams[] = $plotFilter;
        }

$soldResult = DB::select($soldMtQuery, $soldParams);
        $seedTotal->total_sold_mt = $soldResult[0]->total_sold_mt ?? 0;
    }

$blockwiseTotalsQuery = "
        SELECT
            hu.block_name,
            hu.seed_name,
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2))) AS total_production_mt,
            COALESCE(CAST(ms.sale_price AS DECIMAL(10,2)), 0) AS price_per_mt,
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2)) * COALESCE(CAST(ms.sale_price AS DECIMAL(10,2)), 0)) AS total_price
        " . $baseQuery . "
        GROUP BY hu.block_name, hu.seed_name, ms.sale_price
        ORDER BY hu.block_name, hu.seed_name
    ";

$blockwiseTotals = DB::select($blockwiseTotalsQuery, $params);

$grandTotalQuery = "
        SELECT
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2))) AS total_production_mt,
            SUM(CAST(hu.yield_mt AS DECIMAL(10,2)) * COALESCE(CAST(ms.sale_price AS DECIMAL(10,2)), 0)) AS total_price
        " . $baseQuery . "
    ";

$grandTotalResult = DB::select($grandTotalQuery, $params);
    $grandTotal = $grandTotalResult[0]->total_production_mt ?? 0;
    $grandTotalPrice = $grandTotalResult[0]->total_price ?? 0;

// Get grand total sold MT
    $grandSoldMtQuery = "
        SELECT SUM(COALESCE(hsr.sold_mt, 0)) as grand_total_sold_mt
        FROM harvest_store_manage hsm
        LEFT JOIN harvest_sale_records hsr ON hsm.id = hsr.harvest_store_id
        WHERE hsm.product_id = 1
        AND hsm.seed_name IS NOT NULL AND hsm.seed_name <> ''
    ";
    $grandSoldParams = [];
    
    if ($siteId) {
        $grandSoldMtQuery .= " AND hsm.site_id = ?";
        $grandSoldParams[] = $siteId;
    }
    if ($blockFilter) {
        $grandSoldMtQuery .= " AND hsm.block_name = ?";
        $grandSoldParams[] = $blockFilter;
    }
    if ($plotFilter) {
        $grandSoldMtQuery .= " AND hsm.plot_name = ?";
        $grandSoldParams[] = $plotFilter;
    }
    if ($seedNameFilter) {
        $grandSoldMtQuery .= " AND hsm.seed_name = ?";
        $grandSoldParams[] = $seedNameFilter;
    }

$grandSoldResult = DB::select($grandSoldMtQuery, $grandSoldParams);
    $grandTotalSold = $grandSoldResult[0]->grand_total_sold_mt ?? 0;

$organizedBlockTotals = [];
    foreach ($blockwiseTotals as $item) {
        $blockName = $item->block_name;

if (!isset($organizedBlockTotals[$blockName])) {
            $organizedBlockTotals[$blockName] = [
                'seeds' => [],
                'blockTotal' => 0,
                'blockTotalPrice' => 0
            ];
        }

$organizedBlockTotals[$blockName]['seeds'][] = (object)[
            'seed_name' => $item->seed_name,
            'total_production_mt' => (float)$item->total_production_mt,
            'price_per_mt' => (float)$item->price_per_mt,
            'total_price' => (float)$item->total_price
        ];

$organizedBlockTotals[$blockName]['blockTotal'] += (float)$item->total_production_mt;
        $organizedBlockTotals[$blockName]['blockTotalPrice'] += (float)$item->total_price;
    }

return (object)[
        'seedTotals' => $seedTotals,
        'blockwiseTotals' => $organizedBlockTotals,
        'grandTotal' => (float)$grandTotal,
        'grandTotalPrice' => (float)$grandTotalPrice,
        'grandTotalSold' => (float)$grandTotalSold
    ];
}

// Fixed getSilageYieldTotals method
protected function getSilageYieldTotals($blockFilter = null, $plotFilter = null, $seedNameFilter = null, $siteId = null)
{
    if (!Schema::hasTable('harvest_store_manage') || !Schema::hasTable('harvest_sale_records')) {
        return (object)[
            'seedTotals' => [],
            'blockwiseTotals' => [],
            'grandTotal' => 0,
            'grandTotalPrice' => 0,
            'grandTotalSold' => 0
        ];
    }

$yieldQuery = DB::table('harvest_store_manage as hsm')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(hsm.yield_mt) as total_yield_mt')
        )
        ->where('hsm.product_id', 3) // Silage product_id = 3
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '');

$saleQuery = DB::table('harvest_store_manage as hsm')
        ->leftJoin('harvest_sale_records as hsr', 'hsm.id', '=', 'hsr.harvest_store_id')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(COALESCE(hsr.total_price, 0)) as total_price'),
            DB::raw('SUM(COALESCE(hsr.sold_mt, 0)) as total_sold_mt')
        )
        ->where('hsm.product_id', 3) // Silage product_id = 3
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '');

if ($siteId) {
        $yieldQuery->where('hsm.site_id', $siteId);
        $saleQuery->where('hsm.site_id', $siteId);
    }
    if ($blockFilter) {
        $yieldQuery->where('hsm.block_name', $blockFilter);
        $saleQuery->where('hsm.block_name', $blockFilter);
    }
    if ($plotFilter) {
        $yieldQuery->where('hsm.plot_name', $plotFilter);
        $saleQuery->where('hsm.plot_name', $plotFilter);
    }
    if ($seedNameFilter) {
        $yieldQuery->where('hsm.seed_name', $seedNameFilter);
        $saleQuery->where('hsm.seed_name', $seedNameFilter);
    }

$yieldQuery->groupBy('hsm.seed_name');
    $saleQuery->groupBy('hsm.seed_name');

$yieldResults = $yieldQuery->get()->keyBy('seed_name');
    $saleResults = $saleQuery->get()->keyBy('seed_name');

$seedTotals = [];
    $grandYield = 0;
    $grandPrice = 0;
    $grandSold = 0;

foreach ($yieldResults as $seed => $yieldData) {
        $yield_mt = (float)$yieldData->total_yield_mt;
        $total_price = isset($saleResults[$seed]) ? (float)$saleResults[$seed]->total_price : 0;
        $total_sold_mt = isset($saleResults[$seed]) ? (float)$saleResults[$seed]->total_sold_mt : 0;
        $price_per_mt = $yield_mt > 0 ? round($total_price / $yield_mt, 2) : 0;

$seedTotals[] = (object)[
            'seed_name' => $seed,
            'total_yield_mt' => $yield_mt,
            'price_per_mt' => $price_per_mt,
            'total_price' => $total_price,
            'total_sold_mt' => $total_sold_mt
        ];

$grandYield += $yield_mt;
        $grandPrice += $total_price;
        $grandSold += $total_sold_mt;
    }

return (object)[
        'seedTotals' => $seedTotals,
        'blockwiseTotals' => [],
        'grandTotal' => $grandYield,
        'grandTotalPrice' => $grandPrice,
        'grandTotalSold' => $grandSold
    ];
}

// Fixed getHayMakingYieldTotals method
protected function getHayMakingYieldTotals($blockFilter = null, $plotFilter = null, $seedNameFilter = null, $siteId = null)
{
    if (!Schema::hasTable('harvest_store_manage') || !Schema::hasTable('harvest_sale_records')) {
        return (object)[
            'seedTotals' => [],
            'blockwiseTotals' => [],
            'grandTotal' => 0,
            'grandTotalPrice' => 0,
            'grandTotalSold' => 0
        ];
    }

$yieldQuery = DB::table('harvest_store_manage as hsm')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(hsm.yield_mt) as total_yield_mt')
        )
        ->where('hsm.product_id', 2) // Hay product_id = 2
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '');

$saleQuery = DB::table('harvest_store_manage as hsm')
        ->leftJoin('harvest_sale_records as hsr', 'hsm.id', '=', 'hsr.harvest_store_id')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(COALESCE(hsr.total_price, 0)) as total_price'),
            DB::raw('SUM(COALESCE(hsr.sold_mt, 0)) as total_sold_mt')
        )
        ->where('hsm.product_id', 2) // Hay product_id = 2
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '');

$yieldQuery->groupBy('hsm.seed_name');
    $saleQuery->groupBy('hsm.seed_name');

$yieldResults = $yieldQuery->get()->keyBy('seed_name');
    $saleResults = $saleQuery->get()->keyBy('seed_name');

$seedTotals = [];
    $grandYield = 0;
    $grandPrice = 0;
    $grandSold = 0;

$grandYield += $yield_mt;
        $grandPrice += $total_price;
        $grandSold += $total_sold_mt;
    }

// Fixed getHarvestStoreManageYieldTotals method
protected function getHarvestStoreManageYieldTotals($blockFilter = null, $plotFilter = null, $seedNameFilter = null, $siteId = null)
{
    if (
        !Schema::hasTable('harvest_store_manage') ||
        !Schema::hasTable('harvest_sale_records')
    ) {
        return (object)[
            'seedTotals' => [],
            'blockwiseTotals' => [],
            'grandTotal' => 0,
            'grandTotalPrice' => 0,
            'grandTotalSold' => 0
        ];
    }

$yieldQuery = DB::table('harvest_store_manage as hsm')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(hsm.yield_mt) as total_yield_mt')
        )
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '')
        ->where('hsm.product_id', 1); // Harvest product_id = 1

$saleQuery = DB::table('harvest_store_manage as hsm')
        ->leftJoin('harvest_sale_records as hsr', 'hsm.id', '=', 'hsr.harvest_store_id')
        ->select(
            'hsm.seed_name',
            DB::raw('SUM(COALESCE(hsr.total_price, 0)) as total_price'),
            DB::raw('SUM(COALESCE(hsr.sold_mt, 0)) as total_sold_mt')
        )
        ->whereNotNull('hsm.seed_name')
        ->where('hsm.seed_name', '<>', '')
        ->where('hsm.product_id', 1); // Harvest product_id = 1

$yieldQuery->groupBy('hsm.seed_name');
    $saleQuery->groupBy('hsm.seed_name');

$yieldResults = $yieldQuery->get()->keyBy('seed_name');
    $saleResults = $saleQuery->get()->keyBy('seed_name');

$seedTotals = [];
    $grandYield = 0;
    $grandPrice = 0;
    $grandSold = 0;

$grandYield += $yield_mt;
        $grandPrice += $total_price;
        $grandSold += $total_sold_mt;
    }

public function index(Request $request)
    {
        $user = Auth::user();
        $loggedInSiteId = $user->site_id;
        $role = $user->role;

$selectedSiteId = $request->input('site_id', $loggedInSiteId);

if ($role != 1) {
            $selectedSiteId = $loggedInSiteId;
        }

$block = $request->input('block');
        $plot = $request->input('plot');
        $from = $request->input('from');
        $to = $request->input('to');
        $activity = $request->input('activity');
        $season = $request->input('season');
        $seedName = $request->input('seed_name');
        $showOnlySeedData = $request->input('show_only_seed_data', false);
        $perPage = 8;
        $currentPage = $request->input('page', 1);

$blocks = [];
        $plots = [];
        $plotsByBlock = [];
        $seedNames = [];
        $sites = [];

if ($role == 1) {
            $sites = DB::table('master_sites')->pluck('site_name', 'id')->toArray();
        } else {
            $userSite = DB::table('master_sites')->where('id', $loggedInSiteId)->first();
            if ($userSite) {
                $sites = [$userSite->id => $userSite->site_name];
            }
        }

$masterManpowerRates = DB::table('master_manpower')
            ->pluck('rate', 'category')
            ->toArray();

$masterManpowerNoOfPerson = DB::table('master_manpower')
            ->pluck('no_of_person', 'category')
            ->toArray();

$masterFertilizers = DB::table('master_fertilizer')
                                ->get(['id', 'fertilizer_name', 'rate'])
                                ->keyBy('id')
                                ->toArray();

// Collect unique blocks, plots, and seeds across all tables regardless of activity filter
        foreach ($this->activityTables as $table) {
            if (!Schema::hasTable($table)) continue;

$blockQuery = DB::table($table);
            if (Schema::hasColumn($table, 'site_id')) {
                $blockQuery->where('site_id', $selectedSiteId);
            }

if (Schema::hasColumn($table, 'block_name')) {
                $newBlocks = $blockQuery->distinct()->pluck('block_name')->toArray();
                $blocks = array_merge($blocks, $newBlocks);

if (Schema::hasColumn($table, 'plot_name')) {
                    $blockPlotPairsQuery = DB::table($table)
                        ->select('block_name', 'plot_name')
                        ->distinct();

if (Schema::hasColumn($table, 'site_id')) {
                        $blockPlotPairsQuery->where('site_id', $selectedSiteId);
                    }
                    $blockPlotPairs = $blockPlotPairsQuery->get();

foreach ($blockPlotPairs as $pair) {
                        if (!empty($pair->block_name) && !empty($pair->plot_name)) {
                            if (!isset($plotsByBlock[$pair->block_name])) {
                                $plotsByBlock[$pair->block_name] = [];
                            }
                            if (!in_array($pair->plot_name, $plotsByBlock[$pair->block_name])) {
                                $plotsByBlock[$pair->block_name][] = $pair->plot_name;
                            }
                        }
                    }
                }
            }

$plotQuery = DB::table($table);
            if (Schema::hasColumn($table, 'site_id')) {
                $plotQuery->where('site_id', $selectedSiteId);
            }
            if (Schema::hasColumn($table, 'plot_name')) {
                $newPlots = $plotQuery->distinct()->pluck('plot_name')->toArray();
                $plots = array_merge($plots, $newPlots);
            }

$seedNameQuery = DB::table($table);
            if (Schema::hasColumn($table, 'site_id')) {
                $seedNameQuery->where('site_id', $selectedSiteId);
            }
            if (Schema::hasColumn($table, 'seed_name')) {
                $tableSeeds = $seedNameQuery
                    ->distinct()
                    ->whereNotNull('seed_name')
                    ->where('seed_name', '!=', '')
                    ->pluck('seed_name')
                    ->toArray();
                $seedNames = array_merge($seedNames, $tableSeeds);
            }
        }

$blocks = array_unique($blocks);
        $plots = array_unique($plots);
        $seedNames = array_unique($seedNames);
        sort($blocks);
        sort($plots);
        sort($seedNames);

foreach ($plotsByBlock as $blockName => $blockPlots) {
            sort($plotsByBlock[$blockName]);
        }

$machines = DB::table('master_machine')->pluck('machine_name', 'id')->toArray();
        $tractors = DB::table('master_tractors')->pluck('tractor_name', 'id')->toArray();
        $fertilizers = DB::table('master_fertilizer')->pluck('fertilizer_name', 'id')->toArray();
        $chemicals = DB::table('master_chemical')->pluck('chemical_name', 'id')->toArray();
        $seeds = DB::table('master_seed')->get(['id', 'seed_name', 'seed_stock_kg'])->keyBy('id')->toArray();

$summary = [];
        $totalStats = [
            'electricityCost' => 0,
            'hsdCost' => 0,
            'fertilizerCost' => 0,
            'maintenanceCost' => 0,
            'manpowerCost' => 0,
            'productionCost' => 0,
            'profitSum' => 0,
            'lossSum' => 0,
            'totalCost' => 0, // This will accumulate costs for filtered activity records
            'seasons' => [
                'kharif' => ['count' => 0, 'totalCost' => 0, 'productionCost' => 0, 'profit' => 0, 'loss' => 0],
                'rabi' => ['count' => 0, 'totalCost' => 0, 'productionCost' => 0, 'profit' => 0, 'loss' => 0],
                'zaid' => ['count' => 0, 'totalCost' => 0, 'productionCost' => 0, 'profit' => 0, 'loss' => 0]
            ]
        ];

// This variable will hold the total revenue for the financial overview,
        // calculated based on the selected activity.
        $overallTotalSalePriceForFinancialOverview = 0;

foreach ($this->activityTables as $table) {
            // New logic: If a specific activity is selected, only process that table for detailed rows
            // Skip 'harvest_store_manage' from the main detailed display as it's a summary table
            if ($activity && $activity !== $table) {
                continue; // Skip tables not matching the selected activity
            }
            if ($table === 'harvest_store_manage') {
                continue; // Always skip harvest_store_manage from detailed rows
            }

if (!Schema::hasTable($table)) continue;

$colsInTable = Schema::getColumnListing($table);

$selectCols = ['id'];
            foreach ($this->expectedColumns as $col) {
                if (in_array($col, $colsInTable) && !in_array($col, $selectCols)) {
                    $selectCols[] = $col;
                }
            }

if ($table === 'crop_protection' && Schema::hasColumn($table, 'chemical_id') && !in_array('chemical_id', $selectCols)) {
                $selectCols[] = 'chemical_id';
            }
            if ($table === 'showing_oprations') {
                if (Schema::hasColumn($table, 'seed_id') && !in_array('seed_id', $selectCols)) {
                    $selectCols[] = 'seed_id';
                }
                if (Schema::hasColumn($table, 'seed_consumption') && !in_array('seed_consumption', $selectCols)) {
                    $selectCols[] = 'seed_consumption';
                }
                if (Schema::hasColumn($table, 'variety') && !in_array('variety', $selectCols)) {
                    $selectCols[] = 'variety';
                }
            }
            if (Schema::hasColumn($table, 'seed_name') && !in_array('seed_name', $selectCols)) {
                $selectCols[] = 'seed_name';
            }
            if (Schema::hasColumn($table, 'yield') && !in_array('yield', $selectCols)) {
                $selectCols[] = 'yield';
            }
            if (Schema::hasColumn($table, 'yield_mt') && !in_array('yield_mt', $selectCols)) {
                $selectCols[] = 'yield_mt';
            }
            if (Schema::hasColumn($table, 'production_value') && !in_array('production_value', $selectCols)) {
                $selectCols[] = 'production_value';
            }
            if ($table === 'fertilizer_soil_record' && Schema::hasColumn($table, 'fertilizer_id') && !in_array('fertilizer_id', $selectCols)) {
                $selectCols[] = 'fertilizer_id';
            }
            if ($table !== 'fertilizer_soil_record' && Schema::hasColumn($table, 'fertilizer_quantity') && !in_array('fertilizer_quantity', $selectCols)) {
                $selectCols[] = 'fertilizer_quantity';
            }
            $hasCreatedAt = Schema::hasColumn($table, 'created_at');
            if ($hasCreatedAt && !in_array('created_at', $selectCols)) {
                $selectCols[] = 'created_at';
            }
            if (Schema::hasColumn($table, 'site_id') && !in_array('site_id', $selectCols)) {
                $selectCols[] = 'site_id';
            }
            // Removed specific harvest_store_manage column selection here as it's skipped from detailed view

if (empty($selectCols)) continue;

$q = DB::table($table)->select($selectCols);

if (Schema::hasColumn($table, 'site_id')) {
                $q->where($table . '.site_id', $selectedSiteId);
            }

if ($block && in_array('block_name', $colsInTable)) $q->where('block_name', $block);
            if ($plot && in_array('plot_name', $colsInTable)) $q->where('plot_name', $plot);
            if ($from && $to && in_array('date', $colsInTable)) $q->whereBetween('date', [$from, $to]);

if (Schema::hasColumn($table, 'seed_name')) {
                if ($seedName) {
                    $q->where('seed_name', $seedName)->whereRaw('seed_name COLLATE utf8mb4_unicode_ci = ?', [$seedName]);
                } else if ($showOnlySeedData) {
                    $q->whereNotNull('seed_name')->where('seed_name', '!=', '');
                }
            } else {
                if ($seedName || $showOnlySeedData) {
                    continue;
                }
            }

if ($season && $hasCreatedAt) {
                $seasonInfo = $this->seasons[$season] ?? null;
                if ($seasonInfo) {
                    $startMonth = $seasonInfo['start_month'];
                    $endMonth = $seasonInfo['end_month'];
                    if ($startMonth > $endMonth) {
                        $q->whereRaw("(MONTH(created_at) >= ? OR MONTH(created_at) <= ?)", [$startMonth, $endMonth]);
                    } else {
                        $q->whereRaw("MONTH(created_at) >= ? AND MONTH(created_at) <= ?", [$startMonth, $endMonth]);
                    }
                }
            }

$records = $q->get();

foreach ($records as $rec) {
                $row = ['table' => $table];

$electricityCost = 0;
                $hsdCost = 0;
                $fertilizerCost = 0;
                $maintenanceCost = 0;
                $manpowerCost = 0;
                $recordTotalCost = 0;
                $productionValue = 0;
                $pricePerMt = 0;
                $yieldMt = 0;
                $currentSalePrice = 0;
                $recordSeason = null;

$createdAt = property_exists($rec, 'created_at') ? $rec->created_at : null;
                $recordSeason = $this->determineSeason($createdAt);
                $row['season'] = $recordSeason ? $this->seasons[$recordSeason]['display'] : '-';

foreach ($this->expectedColumns as $col) {
                    $val = property_exists($rec, $col) ? $rec->$col : null;

if ($col === 'machine_id' && isset($val)) {
                        if (strpos($val, ',') !== false) {
                            $ids = explode(',', $val);
                            $names = [];
                            foreach ($ids as $id) {
                                $id = trim($id);
                                $names[] = $machines[$id] ?? 'Unknown';
                            }
                            $val = implode(', ', $names);
                        } else {
                            $val = $machines[$val] ?? 'Unknown';
                        }
                    }
                    // NEW: Handle tractor_id column
                    if ($col === 'tractor_id' && isset($val)) {
                        if (strpos($val, ',') !== false) {
                            $ids = explode(',', $val);
                            $names = [];
                            foreach ($ids as $id) {
                                $id = trim($id);
                                $names[] = $tractors[$id] ?? 'Unknown';
                            }
                            $val = implode(', ', $names);
                        } else {
                            $val = $tractors[$val] ?? 'Unknown';
                        }
                    }
                    if ($table === 'fertilizer_soil_record' && $col === 'fertilizer_id' && !empty($val)) {
                        $fertilizerDetails = json_decode($val, true);
                        if (json_last_error() === JSON_ERROR_NONE && is_array($fertilizerDetails)) {
                            $displayNames = [];
                            $totalFertilizerQuantity = 0;
                            $currentRecordFertilizerCost = 0;

foreach ($fertilizerDetails as $item) {
                                $fertId = $item['id'] ?? null;
                                $qty = $item['quantity'] ?? 0;
                                $uom = $item['uom'] ?? 'kg';

if ($fertId && isset($masterFertilizers[$fertId])) {
                                    $masterFert = (object)$masterFertilizers[$fertId];
                                    $fertName = $masterFert->fertilizer_name ?? 'Unknown';
                                    $fertRate = $masterFert->rate ?? $this->costConfig['fertilizer_rate_per_kg'];

$displayNames[] = "{$fertName}: {$qty}{$uom}";
                                    $totalFertilizerQuantity += (float)$qty;
                                    $currentRecordFertilizerCost += ((float)$qty * (float)$fertRate);
                                } else {
                                    $displayNames[] = "Unknown: {$qty}{$uom}";
                                }
                            }
                            $row['fertilizer_name'] = implode(', ', $displayNames);
                            $row['fertilizer_used'] = round($totalFertilizerQuantity, 2);
                            $fertilizerCost = round($currentRecordFertilizerCost, 2);
                        } else {
                            $fertName = $fertilizers[$val] ?? $val;
                            $row['fertilizer_name'] = $fertName;
                            $row['fertilizer_used'] = $rec->fertilizer_quantity ?? '-';
                            $fertRate = $masterFertilizers[$val]->rate ?? $this->costConfig['fertilizer_rate_per_kg'];
                            $fertilizerCost = is_numeric($row['fertilizer_used']) ? round((float)$row['fertilizer_used'] * (float)$fertRate, 1) : 0;
                        }
                    } elseif ($col === 'fertilizer_id' && isset($val)) {
                        $fertName = $fertilizers[$val] ?? $val;
                        $row['fertilizer_name'] = $fertName;
                        $row['fertilizer_id_raw'] = $val;
                    } else {
                        $row[$col] = $val ?? '-';
                    }
                }

if ($table !== 'fertilizer_soil_record' && property_exists($rec, 'fertilizer_quantity') && property_exists($row, 'fertilizer_id_raw')) {
                    $fertQty = (float)($rec->fertilizer_quantity ?? 0);
                    $fertId = $row['fertilizer_id_raw'];
                    $fertRate = (float)($masterFertilizers[$fertId]->rate ?? $this->costConfig['fertilizer_rate_per_kg']);
                    $fertilizerCost = is_numeric($fertQty) && is_numeric($fertRate) ? round($fertQty * $fertRate, 1) : 0;
                    $row['fertilizer_used'] = $fertQty;
                } elseif ($table !== 'fertilizer_soil_record') {
                    $row['fertilizer_used'] = '-';
                    $fertilizerCost = 0;
                }
                $row['fertilizer_cost'] = $fertilizerCost ?: '-';

$hasSeedName = false;
                if (property_exists($rec, 'seed_name') && !empty($rec->seed_name)) {
                    $row['seed_name'] = $rec->seed_name;
                    $hasSeedName = true;
                } else {
                    if ($table === 'showing_oprations' && property_exists($rec, 'seed_id') && !empty($rec->seed_id)) {
                        $seedId = $rec->seed_id;
                        if (isset($seeds[$seedId])) {
                            $seedInfo = (object)$seeds[$seedId];
                            $seedNameFromMaster = $seedInfo->seed_name ?? '';
                            if (!empty($seedNameFromMaster)) {
                                $row['seed_name'] = $seedNameFromMaster;
                                $hasSeedName = true;
                            }
                        }
                    }
                }
                if (!$hasSeedName) {
                    $row['seed_name'] = '-';
                }

if (property_exists($rec, 'electricity_units') && property_exists($rec, 'cost_per_unit')) {
                    $u = (float)($rec->electricity_units ?? 0);
                    $r = (float)($rec->cost_per_unit ?? 0);
                    $electricityCost = is_numeric($u) && is_numeric($r) ? round($u * $r, 2) : 0;
                    $row['electricity_cost'] = $electricityCost ?: '-';
                } else {
                    $row['electricity_cost'] = '-';
                }

if (property_exists($rec, 'hsd_consumption')) {
                    $hsd = (float)($rec->hsd_consumption ?? 0);
                    $latestRate = DB::table('diesel_stocks')
                        ->where('site_id', $rec->site_id ?? 1)
                        ->orderByDesc('date_of_purchase')
                        ->value('rate_per_liter');
                    $rate = is_numeric($latestRate) ? (float)$latestRate : 0;
                    $hsdCost = is_numeric($hsd) ? round($hsd * $rate, 2) : 0;
                    $row['hsd_cost'] = $hsdCost ?: '-';
                } else {
                    $row['hsd_cost'] = '-';
                }

if (property_exists($rec, 'major_maintenance')) {
                    $maint = (float)($rec->major_maintenance ?? 0);
                    $maintenanceCost = is_numeric($maint) ? round($maint, 2) : 0;
                    $row['maintenance_cost'] = $maintenanceCost ?: '-';
                } else {
                    $row['maintenance_cost'] = '-';
                }

$currentRecordManpowerCost = 0;
                foreach (['unskilled', 'semi_skilled_1', 'semi_skilled_2'] as $skillColumn) {
                    if (property_exists($rec, $skillColumn) && is_numeric($rec->$skillColumn) && $rec->$skillColumn > 0) {
                        $numberOfPersons = (int) $rec->$skillColumn;
                        $categoryName = '';
                        if ($skillColumn === 'unskilled') { $categoryName = 'Unskilled'; }
                        elseif ($skillColumn === 'semi_skilled_1') { $categoryName = 'Semi Skilled 1'; }
                        elseif ($skillColumn === 'semi_skilled_2') { $categoryName = 'Semi Skilled 2'; }
                        if (isset($masterManpowerRates[$categoryName])) {
                            $ratePerPerson = (float)$masterManpowerRates[$categoryName];
                            $currentRecordManpowerCost += ($numberOfPersons * $ratePerPerson);
                        }
                    }
                }
                if (property_exists($rec, 'manpower_type') && !empty($rec->manpower_type) && $currentRecordManpowerCost === 0) {
                    $manTypeCategories = explode(',', $rec->manpower_type);
                    foreach ($manTypeCategories as $type) {
                        $type = trim($type);
                        if (isset($masterManpowerRates[$type]) && isset($masterManpowerNoOfPerson[$type])) {
                            $rate = (float)$masterManpowerRates[$type];
                            $noOfPersonsInMaster = (int)$masterManpowerNoOfPerson[$type];
                            $currentRecordManpowerCost += ($rate * $noOfPersonsInMaster);
                        }
                    }
                }
                $manpowerCost = round($currentRecordManpowerCost, 2);
                $row['manpower_cost'] = $manpowerCost ?: '-';

if (property_exists($rec, 'total_cost') && is_numeric($rec->total_cost)) {
                    $recordTotalCost = (float)$rec->total_cost;
                } else {
                    $recordTotalCost = $electricityCost + $hsdCost + $fertilizerCost + $maintenanceCost + $manpowerCost;
                }
                $row['total_cost'] = is_numeric($recordTotalCost) ? number_format($recordTotalCost, 2) : '-';

if (!$hasSeedName && ($seedName || $showOnlySeedData)) {
                    continue;
                }

$yieldMt = property_exists($rec, 'yield_mt') ? (is_numeric($rec->yield_mt) ? (float)$rec->yield_mt : 0) : 0;

$currentSalePrice = 0;

// FIXED: For all harvest-related activities, use yield_mt directly from their respective tables
                // Only lookup sale_price from harvest_store_manage for production value calculation
                if (in_array($table, ['harvesting_update', 'silage_making', 'hay_making'])) {
                    // For ALL these tables, use yield_mt directly from the current record
                    $yieldMt = property_exists($rec, 'yield_mt') ? (is_numeric($rec->yield_mt) ? (float)$rec->yield_mt : 0) : 0;
                    
                    // Determine product_id for sale price lookup
                    $productId = null;
                    if ($table === 'silage_making') {
                        $productId = 3; // Silage
                    } elseif ($table === 'hay_making') {
                        $productId = 2; // Hay
                    } elseif ($table === 'harvesting_update') {
                        $productId = 1; // Harvest
                    }
                    
                    // Look up sale price from harvest_store_manage for production value calculation
                    if ($productId !== null) {
                        $seedNameForLookup = $row['seed_name'] ?? null;
                        $blockNameForLookup = $row['block_name'] ?? null;
                        $plotNameForLookup = $row['plot_name'] ?? null;

if ($seedNameForLookup && $blockNameForLookup && $plotNameForLookup) {
                            $hsmRecord = DB::table('harvest_store_manage')
                                ->where('product_id', $productId)
                                ->where('seed_name', $seedNameForLookup)
                                ->where('block_name', $blockNameForLookup)
                                ->where('plot_name', $plotNameForLookup)
                                ->where('site_id', $selectedSiteId)
                                ->orderByDesc('date')
                                ->first();

if ($hsmRecord && is_numeric($hsmRecord->sale_price)) {
                                $currentSalePrice = (float)$hsmRecord->sale_price;
                            }
                        }
                    }
                } elseif (property_exists($rec, 'production_value') && is_numeric($rec->production_value)) {
                    $productionValue = (float)$rec->production_value;
                } elseif (property_exists($rec, 'yield') && is_numeric($rec->yield)) {
                    $yieldMt = (float)$rec->yield;
                }

if ($yieldMt > 0 && $currentSalePrice > 0) {
                    $productionValue = $yieldMt * $currentSalePrice;
                    $pricePerMt = $currentSalePrice;
                }

$row['yield_mt'] = is_numeric($yieldMt) ? number_format($yieldMt, 2) : '-';
                $row['price_per_mt'] = is_numeric($pricePerMt) ? number_format($pricePerMt, 2) : '-';
                $row['production_value'] = is_numeric($productionValue) ? number_format($productionValue, 2) : '-';

$profit = 0;
                $loss = 0;
                if (is_numeric($recordTotalCost) && is_numeric($productionValue) && (float)$productionValue > (float)$recordTotalCost) {
                    $profit = round((float)$productionValue - (float)$recordTotalCost, 2);
                    $row['profit'] = is_numeric($profit) ? number_format($profit, 2) : '-';
                    $row['loss'] = '-';
                } else if (is_numeric($recordTotalCost) && is_numeric($productionValue)) {
                    $loss = round((float)$recordTotalCost - (float)$productionValue, 2);
                    $row['profit'] = '-';
                    $row['loss'] = is_numeric($loss) ? number_format($loss, 2) : '-';
                } else {
                    $row['profit'] = '-';
                    $row['loss'] = '-';
                }

if ($recordSeason && isset($totalStats['seasons'][$recordSeason])) {
                    $totalStats['seasons'][$recordSeason]['count']++;
                    $totalStats['seasons'][$recordSeason]['totalCost'] += is_numeric($recordTotalCost) ? (float)$recordTotalCost : 0;
                    $totalStats['seasons'][$recordSeason]['productionCost'] += is_numeric($productionValue) ? (float)$productionValue : 0;
                    $totalStats['seasons'][$recordSeason]['profit'] += (float)$profit;
                    $totalStats['seasons'][$recordSeason]['loss'] += (float)$loss;
                }

// Accumulate costs for the financial overview based on the current filtered records
                $totalStats['electricityCost'] += (float)$electricityCost;
                $totalStats['hsdCost'] += (float)$hsdCost;
                $totalStats['maintenanceCost'] += (float)$maintenanceCost;
                $totalStats['manpowerCost'] += (float)$manpowerCost;
                $totalStats['totalCost'] += is_numeric($recordTotalCost) ? (float)$recordTotalCost : 0;
                // Accumulate revenue from detailed rows for the financial overview
                // This is specifically for activities that show up in the main table.
                $overallTotalSalePriceForFinancialOverview += is_numeric($productionValue) ? (float)$productionValue : 0;

$summary[] = $row;
            }
        }

// Fetch all yield totals from harvest_store_manage for the summary blocks,
        // irrespective of the activity filter. This data is then conditionally used in the UI.
        $silageTotals = $this->getSilageYieldTotals($request->block, $request->plot, $seedName, $selectedSiteId);
        $hayTotals = $this->getHayMakingYieldTotals($request->block, $request->plot, $seedName, $selectedSiteId);
        $harvestStoreManageTotals = $this->getHarvestStoreManageYieldTotals($block, $plot, $seedName, $selectedSiteId);
        // $seedYieldTotals is for old harvesting_update logic, kept as is but likely less relevant now for total revenue
        $seedYieldTotals = $this->getSeedBasedYieldTotals($block, $plot, $seedName, $selectedSiteId);

// Adjust overallTotalSalePriceForFinancialOverview based on specific activity selected for financial overview
        if (!empty($activity)) { // If a specific activity is selected (not 'All Activities')
            if ($activity == 'harvesting_update') {
                // When 'Harvest' is selected, the Total Revenue should come from Harvest Store Manage totals
                $overallTotalSalePriceForFinancialOverview = ((float)$harvestStoreManageTotals->grandTotalPrice ?? 0);
            } elseif ($activity == 'hay_making') {
                // When 'Hay Making' is selected, the Total Revenue should come from Hay Making totals
                $overallTotalSalePriceForFinancialOverview = ((float)$hayTotals->grandTotalPrice ?? 0);
            } elseif ($activity == 'silage_making') {
                // When 'Silage Making' is selected, the Total Revenue should come from Silage Making totals
                $overallTotalSalePriceForFinancialOverview = ((float)$silageTotals->grandTotalPrice ?? 0);
            } else {
                // For any other specific activity (e.g., Fertilizer, Sowing),
                // the revenue will already be accumulated in $overallTotalSalePriceForFinancialOverview
                // from the detailed loop above (productionValue of that specific activity).
                // These activities typically have 0 productionValue.
                // $overallTotalSalePriceForFinancialOverview would already be correct from the loop for non-harvest activities
            }
        } else {
            // If 'All Activities' is selected, sum up all grand totals for revenue
            $overallTotalSalePriceForFinancialOverview = ((float)$silageTotals->grandTotalPrice ?? 0) +
                                                         ((float)$hayTotals->grandTotalPrice ?? 0) +
                                                         ((float)$harvestStoreManageTotals->grandTotalPrice ?? 0);
            // $totalStats['totalCost'] already sums up all costs from all activities in the main loop for 'All Activities'
        }

// --- Paging and View Data ---
        $total = count($summary);
        $currentItems = array_slice($summary, ($currentPage - 1) * $perPage, $perPage);

$paginator = new LengthAwarePaginator(
            $currentItems,
            $total,
            $perPage,
            $currentPage,
            ['path' => $request->url(), 'query' => $request->query()]
        );

return view('cost_analysis.index', [
            'summary' => $currentItems,
            'paginator' => $paginator,
            'totalStats' => $totalStats, // totalCost will now be filtered correctly
            'blocks' => $blocks,
            'plots' => $plots,
            'plotsByBlock' => $plotsByBlock,
            'seedNames' => $seedNames,
            'sites' => $sites,
            'selectedSiteId' => $selectedSiteId,
            'loggedInUserRole' => $role,
            'expectedColumns' => $this->expectedColumns,
            'activityTables' => $this->activityTables,
            'orderedActivities' => $this->orderedActivities ?? [],
            'seasons' => $this->seasons,
            'silageTotals' => $silageTotals,
            'hayTotals' => $hayTotals,
            'seedYieldTotals' => $seedYieldTotals,
            'harvestStoreManageTotals' => $harvestStoreManageTotals,
            'overallTotalSalePrice' => $overallTotalSalePriceForFinancialOverview, // Pass the computed revenue
            'sel' => compact('block', 'plot', 'from', 'to', 'activity', 'season', 'seedName', 'selectedSiteId')
        ]);
    }

// AJAx method to get plots for a specific block
    public function getPlotsByBlock(Request $request)
    {
        $blockName = $request->input('block');
        $plots = [];

if (!empty($blockName)) {
            // Get all plots for the selected block from all applicable tables
            foreach ($this->activityTables as $table) {
                if (!Schema::hasTable($table)) continue;

if (Schema::hasColumn($table, 'block_name') && Schema::hasColumn($table, 'plot_name')) {
                    $blockPlots = DB::table($table)
                        ->where('block_name', $blockName)
                        ->distinct()
                        ->pluck('plot_name')
                        ->toArray();

$plots = array_merge($plots, $blockPlots);
                }
            }

$plots = array_unique($plots);
            sort($plots);
        }

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