<?php namespace App\Http\Controllers;

use App\Models\Rekaptimbangpetaktakmar;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator as Paginator;
use Validator, Input, Redirect ; 


class RekaptimbangpetaktakmarController extends Controller {

	protected $layout = "layouts.main";
	protected $data = array();	
	public $module = 'rekaptimbangpetaktakmar';
	static $per_page	= '50';

	public function __construct()
	{
		
		parent::__construct();
		$this->model = new Rekaptimbangpetaktakmar();
		$this->info = $this->model->makeInfo( $this->module);
		$this->access = array();
	
		$this->data = array_merge(array(
			'pageTitle'	=> 	$this->info['title'],
			'pageNote'	=>  $this->info['note'],
			'pageModule'=> 'rekaptimbangpetaktakmar',
			'return'	=> self::returnUrl()
			
		),$this->data);


	}

	public function index( Request $request )
	{
		// Make Sure users Logged 
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		// Ambil parameter filter dari request
		
		// Render into template
		return view( $this->module.'.index',$this->data);
	}	

	public function getDatatable(Request $request)
	{
		$filter_kebun = $request->input('filter_kebun', '');
		$filter_rayon = $request->input('filter_rayon', '');
		$filter_afdeling = $request->input('filter_afdeling', '');
		$filter_tanggal_awal = $request->input('filter_tanggal_awal', '');
		$filter_tanggal_akhir = $request->input('filter_tanggal_akhir', '');

		$results = \DB::select(
			"CALL sp_rekap_timbang_petakvstakmar(?, ?, ?, ?, ?)",
			[$filter_kebun, $filter_rayon, $filter_afdeling, $filter_tanggal_awal, $filter_tanggal_akhir]
		);

		return response()->json([
			'data' => $results
		]);
	}

	public function getChart(Request $request)
	{
		$filter_kebun = $request->input('filter_kebun', 'SK09');
		$filter_rayon = $request->input('filter_rayon', '');
		$filter_afdeling = $request->input('filter_afdeling', '');
		$filter_tanggal_awal = $request->input('filter_tanggal_awal', '');
		$filter_tanggal_akhir = $request->input('filter_tanggal_akhir', '');

		$results = \DB::select(
			"CALL sp_rekap_timbang_petakvstakmar_grafik(?, ?, ?, ?, ?,?)",
			[$filter_kebun, $filter_rayon, $filter_afdeling, $filter_tanggal_awal, $filter_tanggal_akhir, NULL]
		);

		$grouped = [];

		foreach ($results as $row) {
			$umur = isset($row->umur_tanaman_ditebang) ? trim((string)$row->umur_tanaman_ditebang) : '0';
			$jml = isset($row->jml_petak_tebang) ? (float)$row->jml_petak_tebang : (isset($row->jml_petak) ? (float)$row->jml_petak : 0.0);
			$ha = isset($row->ha_tebang) ? (float)$row->ha_tebang : 0.0;
			$protas = isset($row->protas) ? (float)$row->protas : 0.0;
			$ton = isset($row->ton_tebang) ? (float)$row->ton_tebang : 0.0;
			$rendemen = isset($row->rendemen) ? (float)$row->rendemen : 0.0;

			if (!isset($grouped[$umur])) {
				$grouped[$umur] = [
					'jml_petak' => 0.0,
					'ha_tebang' => 0.0,
					'protas_weight' => 0.0,
					'ton_tebang' => 0.0,
					'rendemen_weight' => 0.0,
				];
			}

			$grouped[$umur]['jml_petak'] += $jml;
			$grouped[$umur]['ha_tebang'] += $ha;
			$grouped[$umur]['protas_weight'] += ($protas * $ha);
			$grouped[$umur]['ton_tebang'] += $ton;
			$grouped[$umur]['rendemen_weight'] += ($rendemen * $ton);
		}

		$umur_keys = array_keys($grouped);
		usort($umur_keys, function ($a, $b) {
			return ((int)$a) <=> ((int)$b);
		});

		$categories = [];
		$series_jml = [];
		$series_ha = [];
		$series_protas = [];
		$series_ton = [];
		$series_rendemen = [];

		foreach ($umur_keys as $u) {
			$categories[] = $u;
			$jml_val = $grouped[$u]['jml_petak'];
			$ha_val = $grouped[$u]['ha_tebang'];
			$protas_val = $ha_val > 0 ? ($grouped[$u]['protas_weight'] / $ha_val) : 0.0;
			$ton_val = $grouped[$u]['ton_tebang'];
			$rendemen_val = $ton_val > 0 ? ($grouped[$u]['rendemen_weight'] / $ton_val) : 0.0;

			$series_jml[] = round($jml_val, 2);
			$series_ha[] = round($ha_val, 3);
			$series_protas[] = round($protas_val, 2);
			$series_ton[] = round($ton_val, 2);
			$series_rendemen[] = round($rendemen_val, 2);
		}

		return response()->json([
			'categories' => $categories,
			'series' => [
				['name' => 'Jml Petak', 'data' => $series_jml],
				['name' => 'Ha Tebang', 'data' => $series_ha],
				['name' => 'Protas', 'data' => $series_protas],
				['name' => 'Ton Tebu', 'data' => $series_ton],
				['name' => 'Rendemen', 'data' => $series_rendemen],
			]
		]);
	}

	public function getGrafikRaw(Request $request)
	{
		$kebun    = $request->input('filter_kebun', '');
		$rayon    = $request->input('filter_rayon', '');
		$afdeling = $request->input('filter_afdeling', '');
		$tglMulai = $request->input('filter_tanggal_awal', '');
		$tglSelesai = $request->input('filter_tanggal_akhir', '');
		$umur = $request->input('filter_umur', '');

		$rows = \DB::select(
			"CALL sp_rekap_timbang_petakvstakmar_grafik2(?, ?, ?, ?, ?, ?)",
			[$kebun, $rayon, $afdeling, $tglMulai, $tglSelesai, $umur]
		);


		// Build HTML table from result rows
		$columns = [
			'kode_kebun','nama_kebun','kode_rayon','nama_rayon','afdeling',
			'lahan_id','deskripsi_petak','luas_poligon','kemasakan','bulan_tanam',
			'tgl_timbang_terakhir','umur_tanaman_ditebang','jml_petak_tebang','ha_tebang','protas'
		];

		// Attractive card + summary + responsive table
		$html = '<div class="card mb-3">';
		$html .= '<div class="card-header d-flex justify-content-between align-items-center">';
		$html .= '<div><strong>Rekap Grafik Raw</strong> <small class="text-muted">data tabel</small></div>';
		// filters summary (use available variables if present)
		$filterBadges = [];
		if (!empty($kebun)) $filterBadges[] = '<span class="badge badge-primary">Kebun: ' . htmlspecialchars($kebun, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($rayon)) $filterBadges[] = '<span class="badge badge-secondary">Rayon: ' . htmlspecialchars($rayon, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($afdeling)) $filterBadges[] = '<span class="badge badge-info">Afdeling: ' . htmlspecialchars($afdeling, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($tglMulai) || !empty($tglSelesai)) {
			$dr = htmlspecialchars($tglMulai, ENT_QUOTES, 'UTF-8') . ' - ' . htmlspecialchars($tglSelesai, ENT_QUOTES, 'UTF-8');
			$filterBadges[] = '<span class="badge badge-light">Periode: ' . $dr . '</span>';
		}
		if (!empty($umur)) $filterBadges[] = '<span class="badge badge-warning">Umur: ' . htmlspecialchars($umur, ENT_QUOTES, 'UTF-8') . '</span>';
		$html .= '<div>' . implode(' ', $filterBadges) . '</div>';
		$html .= '<button class="btn btn-sm btn-success" onclick="exportTableToExcel(\'rekap-table\', \'rekap-grafik-raw.xlsx\')"><i class="fa fa-download"></i> Download Excel</button>';
		$html .= '</div>'; // card-header

		// compute totals while building rows
		$total_rows = 0;
		$total_jml = 0.0;
		$total_ha = 0.0;
		$protas_weight = 0.0;

		$html .= '<div class="card-body p-0">';
		$html .= '<div class="p-3">';

		$html .= '<div class="row mb-2 small text-muted">';
		$html .= '<div class="col">Total Baris: <strong id="total-rows">' . count($rows) . '</strong></div>';
		$html .= '<div class="col text-right">Ringkasan: <span id="summary" class="ml-2"></span></div>';
		$html .= '</div>';

		// Responsive table
		$html .= '<div class="table-responsive">';
		$html .= '<table id="rekap-table" class="table table-striped table-hover table-sm mb-0">';
		$html .= '<thead class="thead-light"><tr>';

		foreach ($columns as $col) {
			$label = ucwords(str_replace('_', ' ', $col));
			$html .= '<th>' . htmlspecialchars($label, ENT_QUOTES, 'UTF-8') . '</th>';
		}
		$html .= '</tr></thead><tbody>';

		foreach ($rows as $row) {
			$total_rows++;
			$html .= '<tr>';
			// gather row values for potential totals
			$jml = (isset($row->jml_petak_tebang) && is_numeric($row->jml_petak_tebang)) ? (float)$row->jml_petak_tebang : ((isset($row->jml_petak) && is_numeric($row->jml_petak)) ? (float)$row->jml_petak : 0.0);
			$ha = (isset($row->ha_tebang) && is_numeric($row->ha_tebang)) ? (float)$row->ha_tebang : 0.0;
			$protas = (isset($row->protas) && is_numeric($row->protas)) ? (float)$row->protas : 0.0;

			$total_jml += $jml;
			$total_ha += $ha;
			$protas_weight += ($protas * $ha);

			foreach ($columns as $col) {
				$value = isset($row->$col) ? $row->$col : '';

				// format numbers for readability
				if (is_numeric($value)) {
					// preserve integer vs float
					if (strpos((string)$value, '.') !== false) {
						// choose precision: more precision for ha, 2 decimals for protas, 0/2 for others
						if (in_array($col, ['ha_tebang','luas_poligon'])) {
							$value = number_format((float)$value, 3, '.', ',');
						} elseif ($col === 'protas') {
							$value = number_format((float)$value, 2, '.', ',');
						} else {
							$value = number_format((float)$value, 2, '.', ',');
						}
					} else {
						$value = (int)$value;
					}
				}

				$html .= '<td>' . htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8') . '</td>';
			}
			$html .= '</tr>';
		}

		// footer totals row
		$avg_protas = $total_ha > 0 ? ($protas_weight / $total_ha) : 0.0;

		$html .= '</tbody>';
		$html .= '<tfoot class="font-weight-bold bg-light"><tr>';
		foreach ($columns as $col) {
			if ($col === 'kode_kebun') {
				$html .= '<td>Total</td>';
			} elseif (in_array($col, ['jml_petak_tebang','jml_petak'])) {
				$html .= '<td>' . number_format($total_jml, 0, '.', ',') . '</td>';
			} elseif ($col === 'ha_tebang' || $col === 'luas_poligon') {
				$html .= '<td>' . number_format($total_ha, 3, '.', ',') . '</td>';
			} elseif ($col === 'protas') {
				$html .= '<td>' . number_format($avg_protas, 2, '.', ',') . '</td>';
			} else {
				$html .= '<td></td>';
			}
		}
		$html .= '</tr></tfoot>';

		$html .= '</table>';
		$html .= '</div>'; // table-responsive

		// small stats row
		$html .= '<div class="mt-3 d-flex justify-content-between small text-muted">';
		$html .= '<div>Total Baris: <strong>' . $total_rows . '</strong></div>';
		$html .= '<div>Total Petak: <strong>' . number_format($total_jml, 0, '.', ',') . '</strong></div>';
		$html .= '<div>Total Ha Tebang: <strong>' . number_format($total_ha, 3, '.', ',') . ' ha</strong></div>';
		$html .= '<div>Rata-rata Protas (berat): <strong>' . number_format($avg_protas, 2, '.', ',') . '</strong></div>';
		$html .= '</div>'; // stats row

		$html .= '</div>'; // p-3
		$html .= '</div>'; // card-body
		$html .= '</div>'; // card
		
		$html .= '<script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>';
		$html .= '<script>
			function exportTableToExcel(tableId, filename) {
				const table = document.getElementById(tableId);
				const wb = XLSX.utils.table_to_book(table);
				XLSX.writeFile(wb, filename);
			}
		</script>';

		// ensure $html exists for return

		return response($html, 200)->header('Content-Type', 'text/html; charset=utf-8');
	}

	public function getChartKemasakan(Request $request)
	{
		$filter_kebun = $request->input('filter_kebun', 'SK01');
		$filter_rayon = $request->input('filter_rayon', '');
		$filter_afdeling = $request->input('filter_afdeling', '');
		$filter_tanggal_awal = $request->input('filter_tanggal_awal', '');
		$filter_tanggal_akhir = $request->input('filter_tanggal_akhir', '');

		$results = \DB::select(
			"CALL sp_rekap_timbang_petakvstakmar_grafik_kemasakan(?, ?, ?, ?, ?, ?)",
			[$filter_kebun, $filter_rayon, $filter_afdeling, $filter_tanggal_awal, $filter_tanggal_akhir, NULL]
		);

		$grouped = [];

		foreach ($results as $row) {
			$kemasakan = isset($row->kemasakan) ? trim((string)$row->kemasakan) : 'Unknown';
			$jml = isset($row->jml_petak_tebang) ? (float)$row->jml_petak_tebang : 0.0;
			$ha = isset($row->ha_tebang) ? (float)$row->ha_tebang : 0.0;
			$protas = isset($row->protas) ? (float)$row->protas : 0.0;
			$ton = isset($row->ton_tebang) ? (float)$row->ton_tebang : 0.0;
			$rendemen = isset($row->rendemen) ? (float)$row->rendemen : 0.0;

			if (!isset($grouped[$kemasakan])) {
				$grouped[$kemasakan] = [
					'jml_petak' => 0.0,
					'ha_tebang' => 0.0,
					'protas_weight' => 0.0,
					'ton_tebang' => 0.0,
					'rendemen_weight' => 0.0,
				];
			}

			$grouped[$kemasakan]['jml_petak'] += $jml;
			$grouped[$kemasakan]['ha_tebang'] += $ha;
			$grouped[$kemasakan]['protas_weight'] += ($protas * $ha);
			$grouped[$kemasakan]['ton_tebang'] += $ton;
			$grouped[$kemasakan]['rendemen_weight'] += ($rendemen * $ton);
		}

		$categories = array_keys($grouped);
		$series_jml = [];
		$series_ha = [];
		$series_protas = [];
		$series_ton = [];
		$series_rendemen = [];

		foreach ($categories as $kemasakan) {
			$jml_val = $grouped[$kemasakan]['jml_petak'];
			$ha_val = $grouped[$kemasakan]['ha_tebang'];
			$protas_val = $ha_val > 0 ? ($grouped[$kemasakan]['protas_weight'] / $ha_val) : 0.0;
			$ton_val = $grouped[$kemasakan]['ton_tebang'];
			$rendemen_val = $ton_val > 0 ? ($grouped[$kemasakan]['rendemen_weight'] / $ton_val) : 0.0;

			$series_jml[] = round($jml_val, 2);
			$series_ha[] = round($ha_val, 3);
			$series_protas[] = round($protas_val, 2);
			$series_ton[] = round($ton_val, 2);
			$series_rendemen[] = round($rendemen_val, 2);
		}

		return response()->json([
			'categories' => $categories,
			'series' => [
				['name' => 'Jml Petak', 'data' => $series_jml],
				['name' => 'Ha Tebang', 'data' => $series_ha],
				['name' => 'Protas', 'data' => $series_protas],
				['name' => 'Ton Tebu', 'data' => $series_ton],
				['name' => 'Rendemen', 'data' => $series_rendemen],
			]
		]);
	}

	public function getGrafikRawKemasakan(Request $request)
	{
		$kebun    = $request->input('filter_kebun', '');
		$rayon    = $request->input('filter_rayon', '');
		$afdeling = $request->input('filter_afdeling', '');
		$tglMulai = $request->input('filter_tanggal_awal', '');
		$tglSelesai = $request->input('filter_tanggal_akhir', '');
		$kemasakan = $request->input('filter_kemasakan', '');

		$rows = \DB::select(
			"CALL sp_rekap_timbang_petakvstakmar_grafik_kemasakan2(?, ?, ?, ?, ?, ?)",
			[$kebun, $rayon, $afdeling, $tglMulai, $tglSelesai, $kemasakan]
		);

		$columns = [
			'kode_kebun','nama_kebun','kode_rayon','nama_rayon','afdeling',
			'lahan_id','deskripsi_petak','luas_poligon','kemasakan','bulan_tanam',
			'tgl_timbang_terakhir','umur_tanaman_ditebang','jml_petak_tebang','ha_tebang','protas'
		];

		$html = '<div class="card mb-3">';
		$html .= '<div class="card-header d-flex justify-content-between align-items-center">';
		$html .= '<div><strong>Rekap Grafik Raw Kemasakan</strong> <small class="text-muted">data tabel</small></div>';
		
		$filterBadges = [];
		if (!empty($kebun)) $filterBadges[] = '<span class="badge badge-primary">Kebun: ' . htmlspecialchars($kebun, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($rayon)) $filterBadges[] = '<span class="badge badge-secondary">Rayon: ' . htmlspecialchars($rayon, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($afdeling)) $filterBadges[] = '<span class="badge badge-info">Afdeling: ' . htmlspecialchars($afdeling, ENT_QUOTES, 'UTF-8') . '</span>';
		if (!empty($tglMulai) || !empty($tglSelesai)) {
			$dr = htmlspecialchars($tglMulai, ENT_QUOTES, 'UTF-8') . ' - ' . htmlspecialchars($tglSelesai, ENT_QUOTES, 'UTF-8');
			$filterBadges[] = '<span class="badge badge-light">Periode: ' . $dr . '</span>';
		}
		if (!empty($kemasakan)) $filterBadges[] = '<span class="badge badge-warning">Kemasakan: ' . htmlspecialchars($kemasakan, ENT_QUOTES, 'UTF-8') . '</span>';
		$html .= '<div>' . implode(' ', $filterBadges) . '</div>';
		$html .= '<button class="btn btn-sm btn-success" onclick="exportTableToExcel(\'rekap-table-kemasakan\', \'rekap-grafik-raw-kemasakan.xlsx\')"><i class="fa fa-download"></i> Download Excel</button>';
		$html .= '</div>';

		$total_rows = 0;
		$total_jml = 0.0;
		$total_ha = 0.0;
		$protas_weight = 0.0;

		$html .= '<div class="card-body p-0">';
		$html .= '<div class="p-3">';

		$html .= '<div class="row mb-2 small text-muted">';
		$html .= '<div class="col">Total Baris: <strong id="total-rows">' . count($rows) . '</strong></div>';
		$html .= '<div class="col text-right">Ringkasan: <span id="summary" class="ml-2"></span></div>';
		$html .= '</div>';

		$html .= '<div class="table-responsive">';
		$html .= '<table id="rekap-table-kemasakan" class="table table-striped table-hover table-sm mb-0">';
		$html .= '<thead class="thead-light"><tr>';

		foreach ($columns as $col) {
			$label = ucwords(str_replace('_', ' ', $col));
			$html .= '<th>' . htmlspecialchars($label, ENT_QUOTES, 'UTF-8') . '</th>';
		}
		$html .= '</tr></thead><tbody>';

		foreach ($rows as $row) {
			$total_rows++;
			$html .= '<tr>';
			
			$jml = (isset($row->jml_petak_tebang) && is_numeric($row->jml_petak_tebang)) ? (float)$row->jml_petak_tebang : ((isset($row->jml_petak) && is_numeric($row->jml_petak)) ? (float)$row->jml_petak : 0.0);
			$ha = (isset($row->ha_tebang) && is_numeric($row->ha_tebang)) ? (float)$row->ha_tebang : 0.0;
			$protas = (isset($row->protas) && is_numeric($row->protas)) ? (float)$row->protas : 0.0;

			$total_jml += $jml;
			$total_ha += $ha;
			$protas_weight += ($protas * $ha);

			foreach ($columns as $col) {
				$value = isset($row->$col) ? $row->$col : '';

				if (is_numeric($value)) {
					if (strpos((string)$value, '.') !== false) {
						if (in_array($col, ['ha_tebang','luas_poligon'])) {
							$value = number_format((float)$value, 3, '.', ',');
						} elseif ($col === 'protas') {
							$value = number_format((float)$value, 2, '.', ',');
						} else {
							$value = number_format((float)$value, 2, '.', ',');
						}
					} else {
						$value = (int)$value;
					}
				}

				$html .= '<td>' . htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8') . '</td>';
			}
			$html .= '</tr>';
		}

		$avg_protas = $total_ha > 0 ? ($protas_weight / $total_ha) : 0.0;

		$html .= '</tbody>';
		$html .= '<tfoot class="font-weight-bold bg-light"><tr>';
		foreach ($columns as $col) {
			if ($col === 'kode_kebun') {
				$html .= '<td>Total</td>';
			} elseif (in_array($col, ['jml_petak_tebang','jml_petak'])) {
				$html .= '<td>' . number_format($total_jml, 0, '.', ',') . '</td>';
			} elseif ($col === 'ha_tebang' || $col === 'luas_poligon') {
				$html .= '<td>' . number_format($total_ha, 3, '.', ',') . '</td>';
			} elseif ($col === 'protas') {
				$html .= '<td>' . number_format($avg_protas, 2, '.', ',') . '</td>';
			} else {
				$html .= '<td></td>';
			}
		}
		$html .= '</tr></tfoot>';

		$html .= '</table>';
		$html .= '</div>';

		$html .= '<div class="mt-3 d-flex justify-content-between small text-muted">';
		$html .= '<div>Total Baris: <strong>' . $total_rows . '</strong></div>';
		$html .= '<div>Total Petak: <strong>' . number_format($total_jml, 0, '.', ',') . '</strong></div>';
		$html .= '<div>Total Ha Tebang: <strong>' . number_format($total_ha, 3, '.', ',') . ' ha</strong></div>';
		$html .= '<div>Rata-rata Protas (berat): <strong>' . number_format($avg_protas, 2, '.', ',') . '</strong></div>';
		$html .= '</div>';

		$html .= '</div>';
		$html .= '</div>';
		$html .= '</div>';
		
		$html .= '<script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>';
		$html .= '<script>
			function exportTableToExcel(tableId, filename) {
				const table = document.getElementById(tableId);
				const wb = XLSX.utils.table_to_book(table);
				XLSX.writeFile(wb, filename);
			}
		</script>';

		return response($html, 200)->header('Content-Type', 'text/html; charset=utf-8');
	}


	function show( Request $request , $id ) 
	{
		/* Handle import , export and view */
		$task =$id ;
		switch( $task)
		{

			case 'export':
				return $this->getExport( $request );
				break;
			case 'getDatatable':
				return $this->getDatatable( $request );
				break;
			case 'getChart':
				return $this->getChart( $request );
				break;
			case 'getGrafikRaw':
				return $this->getGrafikRaw( $request );
			case 'getChartKemasakan':
				return $this->getChartKemasakan( $request );
				break;
			case 'getGrafikRawKemasakan':
				return $this->getGrafikRawKemasakan( $request );
				break;	
		}
	}
}