<?php namespace App\Http\Controllers;

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

class ReportlkpController extends Controller {

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

	public function __construct()
	{
		
		parent::__construct();
		$this->model = new Reportlkp();
		$this->info = $this->model->makeInfo( $this->module);
		$this->access = array();
	
		$this->data = array_merge(array(
			'pageTitle'	=> 	$this->info['title'],
			'pageNote'	=>  $this->info['note'],
			'pageModule'=> 'reportlkp',
			'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');
		$filter = '';
			$filter = ' where 0=0 ';
			$params = array(
				'params'	=> $filter
			);
		$this->grab( $request, $params) ;
		if($this->access['is_view'] ==0) 
			return redirect('dashboard')->with('message', __('core.note_restric'))->with('status','error');				
		// Render into template
		return view( $this->module.'.index',$this->data);
	}	
	function show( Request $request , $id ) 
	{
		/* Handle import , export and view */
		$task =$id ;
		switch( $task)
		{

			case 'export':
				return $this->getExport( $request );
				break;
			case 'formRekapidlahanPekerjaan':
				return $this->formRekapidlahanPekerjaan( $request );
				break;
			case 'getRekapidlahanPekerjaan':
				return $this->getRekapidlahanPekerjaan( $request );
				break;
			case 'formRekapPcrc':
				return $this->formRekapPcrc( $request );
				break;
			case 'getRekapPcrc':
				return $this->getRekapPcrc( $request );
				break;	
			case 'getDataVerifikasiLkp':
				return $this->getDataVerifikasiLkp( $request );
				break;	
			case 'viewVerifikasiLkp':
				$id = $request->input('id');
				return $this->viewVerifikasiLkp( $request, $id );
				break;
			case 'formRekapPcrcDetail':
				return $this->formRekapPcrcDetail( $request );
				break;
			case 'getRekapPcrcDetail':
				return $this->getRekapPcrcDetail( $request );
				break;	
			case 'getMonitoringVerifikasiLkp':
				return $this->getMonitoringVerifikasiLkp( $request );
				break;
			case 'formRekapPcrcRayonBulanan':
				return $this->formRekapPcrcRayonBulanan( $request );
				break;
			case 'getRekapPcrcRayonBulanan':
				return $this->getRekapPcrcRayonBulanan( $request );
				break;
			case 'formTableMonitoringPiPemeliharaanPanenPica':
				return $this->formTableMonitoringPiPemeliharaanPanenPica( $request );
				break;
			case 'getTableMonitoringPiPemeliharaanPanenPica':
				return $this->getTableMonitoringPiPemeliharaanPanenPica( $request );
				break;	
			case 'getMonitoringUmur':
				return $this->getMonitoringUmur( $request );
				break;
			case 'formMonitoringUmur':
				return $this->formMonitoringUmur( $request );
				break;	
			case 'formRekapVarietas':
				return $this->formRekapVarietas( $request );
				break;
			case 'getRekapVarietas':
				return $this->getRekapVarietas( $request );
				break;
			case 'getRekapPcrcRayonSdVerifikasi':
				return $this->getRekapPcrcRayonSdVerifikasi( $request );
				break;
			case 'formRekapPcrcRayonKomparasi':
				return $this->formRekapPcrcRayonKomparasi( $request );
				break;
			case 'getRekapPcrcRayonKomparasi':
				return $this->getRekapPcrcRayonKomparasi( $request );
				break;
	
		}
	}

		public function store( Request $request){

		$task = $request->input('action_task');
		switch ($task)
		{
			default:
				return $this->postData( $request);
				break;
			case 'ajukanVerifikasiPcrc':
				return $this->ajukanVerifikasiPcrc( $request );
				break;	
			case 'destroydataverifikasi':
				return $this->destroydataverifikasi( $request );
				break;	
			case 'VerifGM':
				return $this->VerifGM( $request );
				break;	

		}
	}

	public function formRekapidlahanPekerjaan( Request $request )
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Rekap LKP by ID Lahan Pekerjaan';
		$this->data['pageNote'] = 'Menampilkan rekap LKP berdasarkan ID Lahan Pekerjaan';	
		
		return view($this->module.'.rekapidlahanpekerjaan', $this->data);
	} 

	public function getRekapidlahanPekerjaan(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$idLahan = $request->input('kode_kebun', 'SK01');
		$startDate = $request->input('start_date', '2024-01-01');
		$endDate = $request->input('end_date', '2024-12-31');
		
		$results = \DB::select('CALL sp_lkp_rekap_id_lahan_pekerjaan(?, ?, ?)', 
			[$idLahan, $startDate, $endDate]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-striped table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			foreach(array_keys((array)$results[0]) as $column) {
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa;">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$html .= '<tr>';
				foreach((array)$row as $value) {
					$html .= '<td>' . htmlspecialchars($value ?? '') . '</td>';
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	public function formRekapPcrc( Request $request )
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Verifikasi LKP Kebun';
		$this->data['pageNote'] = 'Menampilkan data verifikasi LKP setiap minggu';	
		
		return view($this->module.'.rekappcrc', $this->data);
	} 

	public function getRekapPcrc(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		config(['app.debug' => true]);
		$startDate = $request->input('start_date');
		$endDate = $request->input('end_date');
		$kebun = $request->input('kebun',null);
		$rayon = $request->input('rayon',null);

		if(session('gid') > 2) {
			$kebun = substr(session('fid'), 0, 4);
		}
		
		$results = \DB::select('CALL sp_lkp_rekap_pcrc(?, ?, ?, ?)', 
			[$startDate, $endDate, $kebun, $rayon]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			foreach(array_keys((array)$results[0]) as $column) {
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa;">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->kode_kat) && strpos($row->kode_kat, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				foreach((array)$row as $value) {
					$html .= '<td>' . htmlspecialchars($value ?? '') . '</td>';
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	public function getRekapPcrcRayonSdVerifikasi(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		config(['app.debug' => true]);
		$endDate = $request->input('end_date');
		$kebun = $request->input('kebun');

		$results = \DB::select('CALL sp_lkp_rekap_pcrc_rayon_sd_verifikasi(?, ?)', 
			[$endDate, $kebun]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			foreach(array_keys((array)$results[0]) as $column) {
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa;">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->kode_kat) && strpos($row->kode_kat, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				foreach((array)$row as $value) {
					$html .= '<td>' . htmlspecialchars($value ?? '') . '</td>';
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	
	//ajukanVerifikasiPcrc
	public function ajukanVerifikasiPcrc(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		config(['app.debug' => true]);
		$tahun = $request->input('tahun');
		$bulan = $request->input('bulan');
		$minggu = $request->input('minggu');
		$startDate = $request->input('start_date');
		$endDate = $request->input('end_date');
		$kebun = $request->input('kebun',null);
		$rayon = $request->input('rayon',null);

		
		$results = \DB::select('CALL sp_lkp_rekap_pcrc(?, ?, ?, ?)', 
			[$startDate, $endDate, $kebun, $rayon]);

		$results2 = \DB::select('CALL sp_lkp_rekap_pcrc_rayon_sd_verifikasi(?, ?)', 
			[$endDate, $kebun]);	

		// Generate PDF from results
		$fileName = 'verifikasi_lkp_' . date('YmdHis') . '.json';
		$filePath = 'uploads/file_verfikasi_lkp/' . $fileName;
		$jsonData = json_encode($results, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
		file_put_contents(public_path($filePath), $jsonData);

		// Generate PDF from results 2
		$fileNameSd = 'verifikasi_lkp_sd_' . date('YmdHis') . '.json';
		$filePathSd = 'uploads/file_verfikasi_lkp/' . $fileNameSd;
		$jsonDataSd = json_encode($results2, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
		file_put_contents(public_path($filePathSd), $jsonDataSd);


		$entryBy = \Auth::user()->id ?? null;


		$exists = \DB::table('tx_verifikasi_lkp')
			->where('tgl_awal', $startDate)
			->where('tgl_akhir', $endDate)
			->where('kebun', $kebun)
			->exists();

		if($exists) {
			return response()->json(['status' => 'error', 'message' => 'Data dengan tanggal dan kebun yang sama sudah ada'], 409);
		}

		\DB::table('tx_verifikasi_lkp')->insert([
			'tgl_akses' => now(),
			'tahun' => $tahun,
			'bulan' => $bulan,
			'minggu' => $minggu,
			'tgl_awal' => $startDate,
			'tgl_akhir' => $endDate,
			'kebun' => $kebun,
			'file_data' => $fileName,
			'file_data_sd' => $fileNameSd,			
			'entry_by' => $entryBy,
			'created_at' => now()
		]);

		return response()->json(['status' => 'success', 'message' => 'Data verifikasi berhasil disimpan']);
	}

	public function getDataVerifikasiLkp(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$query = \DB::table('tx_verifikasi_lkp');
		
		$gid = \Session::get('gid');
		if($gid != 1 && $gid != 2) {
			$fid = \Session::get('fid');
			$query->where('kebun', $fid);
		}
		
		$data = $query->orderBy('tgl_akses', 'desc')
			->paginate(self::$per_page);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-striped table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		$html .= '<th>ID</th><th>Tanggal Akses</th><th>Tahun</th><th>Bulan</th><th>Minggu</th><th>Tgl Awal</th><th>Tgl Akhir</th><th>Kebun</th><th>File</th><th>Status</th><th>Tgl Verif</th><th>Verifikator</th><th>Action</th>';
		$html .= '</tr></thead><tbody>';
		
		if($data->count() > 0) {
			foreach($data as $row) {
				$html .= '<tr>';
				$html .= '<td>' . htmlspecialchars($row->id ?? '') . '</td>';
				$html .= '<td>' . \Carbon\Carbon::parse($row->tgl_akses)->translatedFormat('d F Y | H:i:s') . '</td>';
				$html .= '<td>' . htmlspecialchars($row->tahun ?? '') . '</td>';
				$html .= '<td>' . $this->getMonthName($row->bulan) . '</td>';
				$html .= '<td>' . htmlspecialchars($row->minggu ?? '') . '</td>';
				$html .= '<td>' . \Carbon\Carbon::parse($row->tgl_awal)->translatedFormat('d F Y') . '</td>';
				$html .= '<td>' . \Carbon\Carbon::parse($row->tgl_akhir)->translatedFormat('d F Y') . '</td>';
				$html .= '<td>' . htmlspecialchars($row->kebun ?? '') . '</td>';
				$html .= '<td><a href="viewVerifikasiLkp?id=' . $row->id . '" target="_blank"><i class="fa fa-eye"></i> Lihat</a></td>';
				$html .= '<td>' . ($row->status ? '<span class="badge badge-success">'.$row->status.'</span>' : '') . '</td>';
				$html .= '<td>' . (\Carbon\Carbon::parse($row->status_date)->translatedFormat('d F Y | H:i:s') ?? '-') . '</td>';
				$html .= '<td>' . ($row->verifikator_nama ? $row->verifikator_nama : (\Session::get('gid') == 8 ? '<button class="btn btn-sm btn-primary" onclick="kirimKeGM(' . $row->id . ')"><i class="fa fa-check"></i> Verifikasi</button>' : '')) . '</td>';
				$html .= '<td>' . ($row->status ? '' : '<button class="btn btn-sm btn-danger" onclick="deleteVerifikasi(' . $row->id . ')">Delete</button>') . '</td>';
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';

		// Add pagination links to HTML
		$html .= '<div class="d-flex justify-content-center mt-3">';
		$html .= $data->links();
		$html .= '</div>';

		// Add delete script
		$html .= '<script>
		function deleteVerifikasi(id) {
			if(confirm("Apakah Anda yakin ingin menghapus data ini?")) {
				$.ajax({
					url: "'.url('reportlkp?action_task=destroydataverifikasi').'",
					type: "POST",
					data: { id: id },
					headers: {
						"X-CSRF-TOKEN": $("meta[name=\'csrf-token\']").attr("content")
					},
					success: function(response) {
						if(response.status === "success") {
							alert(response.message);
							fetchVerifikasiData();
						}
					},
					error: function(xhr) {
						alert("Terjadi kesalahan: " + xhr.responseJSON.message);
					}
				});
			}
		}
			function kirimKeGM(id) {
				if(confirm("Apakah Anda yakin ingin verifikasi data ini?")) {
					$.ajax({
						url: "'.url('reportlkp').'",
						type: "POST",
						data: { 
							id: id,
							action_task: "VerifGM"
						},
						headers: {
							"X-CSRF-TOKEN": $("meta[name=\'csrf-token\']").attr("content")
						},
						success: function(response) {
							if(response.status === "success") {
								alert(response.message);
								location.reload();
							}
						},
						error: function(xhr) {
							alert("Terjadi kesalahan: " + xhr.responseJSON.message);
						}
					});
				}
			}
		</script>';
		
		return $html;
	}

	public function getMonthName($monthNumber)
	{
		$months = [
			1 => 'Januari', 2 => 'Februari', 3 => 'Maret', 4 => 'April',
			5 => 'Mei', 6 => 'Juni', 7 => 'Juli', 8 => 'Agustus',
			9 => 'September', 10 => 'Oktober', 11 => 'November', 12 => 'Desember'
		];
		
		return $months[$monthNumber] ?? '';
	}

	public function destroydataverifikasi(Request $request)
	{
		if(!\Auth::check()) 
			return response()->json(['status' => 'error', 'message' => 'You are not login'], 401);
		
		$id = $request->input('id');
		
		$verifikasi = \DB::table('tx_verifikasi_lkp')->where('id', $id)->first();
		
		if(!$verifikasi) {
			return response()->json(['status' => 'error', 'message' => 'Data tidak ditemukan'], 404);
		}
		
		if(!empty($verifikasi->status)) {
			return response()->json(['status' => 'error', 'message' => 'Hanya data dengan status kosong yang dapat dihapus'], 422);
		}
		
		$filePath = public_path('uploads/file_verfikasi_lkp/' . $verifikasi->file_data);
		if(file_exists($filePath)) {
			unlink($filePath);
		}
		
		\DB::table('tx_verifikasi_lkp')->where('id', $id)->delete();
		
		return response()->json(['status' => 'success', 'message' => 'Data verifikasi berhasil dihapus']);
	}

	public function viewVerifikasiLkp(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
			config(['app.debug' => true]);
			$id = $request->input('id');
		$verifikasi = \DB::table('tx_verifikasi_lkp')->where('id', $id)->first();
		$nama_gm = \DB::table('master_kebun')->where('kode_kebun', $verifikasi->kebun)->value('nama_gm');
		
		if(!$verifikasi) {
			return redirect()->back()->with('status', 'error')->with('message', 'Data tidak ditemukan');
		}
		
		$filePath = public_path('uploads/file_verfikasi_lkp/' . $verifikasi->file_data);
		if(!empty($verifikasi->file_data_sd)) {
			$filePathSd = public_path('uploads/file_verfikasi_lkp/' . $verifikasi->file_data_sd);
		} else {
			$filePathSd = null;
		}
		
		if(!file_exists($filePath)) {
			return redirect()->back()->with('status', 'error')->with('message', 'File tidak ditemukan');
		}
		
		$jsonContent = file_get_contents($filePath);
		$data = json_decode($jsonContent, true);
		
		$this->data['pageTitle'] = 'View Verifikasi LKP';
		$this->data['pageNote'] = 'Detail data verifikasi LKP';
		$this->data['startDate'] = $verifikasi->tgl_awal;
		$this->data['endDate'] = $verifikasi->tgl_akhir;
		$this->data['verifikasi'] = $verifikasi;
		$this->data['nama_gm'] = $nama_gm;
		$this->data['status'] = $verifikasi->status;
		$this->data['verificationId'] = md5($verifikasi->id.'is_verfied_gm');
		$this->data['results'] = $data;
		if(!empty($filePathSd)){
			$this->data['results_sd'] = json_decode(file_get_contents($filePathSd), true);
		}else{
			$this->data['results_sd'] = array();
		}
			

		return view($this->module.'.pdf_verifikasi', $this->data);
	}

	public function verify(Request $request, $verificationId)
	{
				config(['app.debug' => true]);
		Config::set('database.default', session('database_name', 'mysql_2526'));

		if(!$verificationId) {
			return redirect()->back()->with('status', 'error')->with('message', 'Verification ID tidak ditemukan');
			echo "Verification ID tidak ditemukan";
		}

		// Search verification by checking all records
		$verifikasi = \DB::table('tx_verifikasi_lkp')->get();
		$found = null;

		foreach($verifikasi as $item) {
			if(md5($item->id.'is_verfied_gm') === $verificationId) {
				$found = $item;
				break;
			}
		}

		if(!$found) {
			return redirect()->back()->with('status', 'error')->with('message', 'Data verifikasi tidak ditemukan');
		}

		$filePath = public_path('uploads/file_verfikasi_lkp/' . $found->file_data);
		if(!empty($found->file_data_sd)){
			$filePathSd = public_path('uploads/file_verfikasi_lkp/' . $found->file_data_sd);
		} else {
			$filePathSd = null;
		}
		
		if(!file_exists($filePath)) {
			return redirect()->back()->with('status', 'error')->with('message', 'File tidak ditemukan');
		}

		$jsonContent = file_get_contents($filePath);
		$data = json_decode($jsonContent, true);

		$jsonContentSd = file_get_contents($filePathSd);
		$dataSd = json_decode($jsonContentSd, true);


		$nama_gm = \DB::table('master_kebun')->where('kode_kebun', $found->kebun)->value('nama_gm');

		$this->data['pageTitle'] = 'Verifikasi LKP';
		$this->data['pageNote'] = 'Halaman verifikasi data LKP dari QR Code';
		$this->data['startDate'] = $found->tgl_awal;
		$this->data['endDate'] = $found->tgl_akhir;
		$this->data['verifikasi'] = $found;
		$this->data['nama_gm'] = $nama_gm;
		$this->data['status'] = $found->status;
		$this->data['verificationId'] = $verificationId;
		$this->data['results'] = $data;
		$this->data['results_sd'] = $dataSd;

		// Generate HTML hasil verifikasi
		$html = '<div class="verification-results-container" style="padding: 20px; background-color: #f8f9fa; border-radius: 8px; margin-top: 20px;">';
		$html .= '<h3 style="border-bottom: 2px solid #007bff; padding-bottom: 10px; margin-bottom: 20px;">Hasil Verifikasi LKP</h3>';
		
		$html .= '<div class="verification-info" style="display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin-bottom: 20px;">';
		$html .= '<div><strong>Nama GM:</strong> ' . htmlspecialchars($nama_gm ?? '-') . '</div>';
		$html .= '<div><strong>Status:</strong> <span style="padding: 5px 10px; border-radius: 4px; background-color: ' . ($found->status === 'VERIFIED' ? '#28a745' : '#ffc107') . '; color: ' . ($found->status === 'VERIFIED' ? '#fff' : '#333') . ';">' . htmlspecialchars($found->status) . '</span></div>';
		$html .= '<div><strong>Tanggal Awal:</strong> ' . date('d-m-Y', strtotime($found->tgl_awal)) . '</div>';
		$html .= '<div><strong>Tanggal Akhir:</strong> ' . date('d-m-Y', strtotime($found->tgl_akhir)) . '</div>';
		$html .= '</div>';
		
		if(!empty($data)) {
			$html .= '<div style="margin-top: 20px; text-align: center; font-style: italic; color: #6c757d;">Mingguan</div>';
			$html .= '<table class="table table-striped table-bordered" style="background-color: white;">';
			$html .= '<thead style="background-color: #007bff; color: white;"><tr>';
			foreach(array_keys((array)$data[0]) as $header) {
				$html .= '<th style="padding: 12px; text-align: left;">' . htmlspecialchars(str_replace('_', ' ', ucwords($header))) . '</th>';
			}
			$html .= '</tr></thead>';
			$html .= '<tbody>';
			
			foreach($data as $row) {
				$html .= '<tr>';
				foreach($row as $cell) {
					$html .= '<td style="padding: 10px;">' . htmlspecialchars($cell ?? '-') . '</td>';
				}
				$html .= '</tr>';
			}
			
			$html .= '</tbody></table>';
		} else {
			$html .= '<p style="color: #dc3545; text-align: center; padding: 20px;">Tidak ada data verifikasi yang ditemukan</p>';
		}

		if(!empty($dataSd)) {
			$html .= '<div style="margin-top: 20px; text-align: center; font-style: italic; color: #6c757d;">Data sampai dengan ' . date('d-m-Y', strtotime($found->tgl_akhir)) . ' (SD)</div>';
			$html .= '<table class="table table-striped table-bordered" style="background-color: white; margin-top: 10px;">';
			$html .= '<thead style="background-color: #17a2b8; color: white;"><tr>';
			foreach(array_keys((array)$dataSd[0]) as $header) {
				$html .= '<th style="padding: 12px; text-align: left;">' . htmlspecialchars(str_replace('_', ' ', ucwords($header))) . '</th>';
			}
			$html .= '</tr></thead>';
			$html .= '<tbody>';
			foreach($dataSd as $row) {
				$html .= '<tr>';
				foreach($row as $cell) {
					$html .= '<td style="padding: 10px;">' . htmlspecialchars($cell ?? '-') . '</td>';
				}
				$html .= '</tr>';
			}
			$html .= '</tbody></table>';
		}else {
			$html .= '<p style="color: #dc3545; text-align: center; padding: 20px;">Tidak ada data verifikasi SD yang ditemukan</p>';
		}
		
		$this->data['htmlHasilVerifikasi'] = $html;
		echo $html;
		
	}

	public function VerifGM(Request $request)
	{
		if(!\Auth::check()) 
			return response()->json(['status' => 'error', 'message' => 'You are not login'], 401);
		
		$id = $request->input('id');
		
		$verifikasi = \DB::table('tx_verifikasi_lkp')->where('id', $id)->first();
		
		if(!$verifikasi) {
			return response()->json(['status' => 'error', 'message' => 'Data tidak ditemukan'], 404);
		}
		$nama_kebun = \DB::table('master_kebun')->where('kode_kebun', (\Auth::user()->first_name ?? ''))->value('nama_kebun');
		\DB::table('tx_verifikasi_lkp')->where('id', $id)->update([
			'status' => 'VERIFIED',
			'status_date' => now(),
			'verifikator_nama' => 'GM '.$nama_kebun,
			'updated_at' => now()
		]);
		
		return response()->json(['status' => 'success', 'message' => 'Data berhasil diverifikasi']);
	}

	public function formRekapPcrcDetail( Request $request )
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Rekap LKP Kebun Rayon Detail';
		$this->data['pageNote'] = 'Menampilkan rekap LKP berdasarkan PCRC dengan detail per kebun dan rayon';	
		
		return view($this->module.'.rekappcrc_detail', $this->data);
	}
	public function getRekapPcrcDetail(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		config(['app.debug' => true]);
		$startDate = $request->input('start_date', '2024-10-01');
		$endDate = $request->input('end_date', '2026-02-24');
		$kebun = $request->input('kebun', 'SK09');
		$rayon = $request->input('rayon', 'SK09K1');
		
		$results = \DB::select('CALL sp_lkp_rekap_pcrc(?, ?, ?, ?)', 
			[$startDate, $endDate, $kebun, $rayon]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			$columnIndex = 0;
			foreach(array_keys((array)$results[0]) as $column) {
				$stickyStyle = '';
				if($columnIndex < 4) {
					$leftPosition = $columnIndex * 90; // Adjust width as needed
					$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; z-index: 11;';
				}
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa; ' . $stickyStyle . '">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
				$columnIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->kode_kat) && strpos($row->kode_kat, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				$columnIndex = 0;
				foreach((array)$row as $value) {
					$stickyStyle = '';
					if($columnIndex < 4) {
						$leftPosition = $columnIndex * 90; // Adjust width as needed
						$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; background-color: #fff; z-index: 9;';
					}
					$cellStyle = $columnIndex >= 6 ? 'text-align: right;' : '';
					$html .= '<td style="' . $cellStyle . ' ' . $stickyStyle . '">' . htmlspecialchars($value ?? '') . '</td>';
					$columnIndex++;
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	public function getMonitoringVerifikasiLkp(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$tahun = $request->input('tahun', date('Y'));
		
		$results = \DB::select('CALL sp_monitoring_verifikasi_lkp(?)', [$tahun]);
		
		$html = '<div style="overflow-x: auto; max-height: 700px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover table-sm" style="font-size: 12px;">';
		$html .= '<thead style="position: sticky; top: 0; z-index: 10;">';
		
		if(!empty($results)) {
			$columns = array_keys((array)$results[0]);
			$monthWeekMap = [];
			$monthColspanMap = [];
			
			// Parse columns and organize by month
			foreach($columns as $column) {
				if(preg_match('/(\d+)_([a-z]+)_(\d+)/i', $column, $matches)) {
					$year = $matches[1];
					$monthName = ucfirst($matches[2]);
					$week = $matches[3];
					
					if(!isset($monthWeekMap[$monthName])) {
						$monthWeekMap[$monthName] = [];
						$monthColspanMap[$monthName] = 0;
					}
					$monthWeekMap[$monthName][] = 'W' . $week;
					$monthColspanMap[$monthName]++;
				} else {
					$monthWeekMap['_extra'][] = $column;
				}
			}
			
			// First row: Month headers with colspan
			$html .= '<tr>';
			$colIndex = 0;
			$monthProcessed = [];
			foreach($columns as $column) {
				if(preg_match('/(\d+)_([a-z]+)_(\d+)/i', $column, $matches)) {
					$monthName = ucfirst($matches[2]);
					// Only add colspan header once per month
					if($colIndex > 1 && !isset($monthProcessed[$monthName])) {
						$html .= '<th colspan="' . $monthColspanMap[$monthName] . '" style="position: sticky; top: 0; background-color: #495057; color: #fff; text-align: center; padding: 6px 8px;">' 
								. $monthName 
								. '</th>';
						$monthProcessed[$monthName] = true;
						$colIndex += $monthColspanMap[$monthName] - 1;
					}
				} else {
					if($colIndex < 2) {
							$html .= '<th style="position: sticky; top: 0; left: ' . ($colIndex * 50) . 'px; background-color: #495057; color: #fff; padding: 6px 8px; z-index: 11;"></th>';
					}
				}
				$colIndex++;
			}
			$html .= '</tr>';
			
			// Second row: Week headers (W1, W2, etc.)
			$html .= '<tr>';
			$colIndex = 0;
			foreach($columns as $column) {
				if($colIndex < 2) {
					$html .= '<th style="position: sticky; top: 24px; left: ' . ($colIndex * 50) . 'px; background-color: #343a40; color: #fff; padding: 6px 8px; z-index: 11;"></th>';
				} else {
					if(preg_match('/(\d+)_([a-z]+)_(\d+)/i', $column, $matches)) {
						$week = $matches[3];
						$html .= '<th style="position: sticky; top: 24px; background-color: #343a40; color: #fff; white-space: nowrap; padding: 6px 8px; text-align: center;">W' . $week . '</th>';
					}
				}
				$colIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$rowArr = (array)$row;
				
				$html .= '<tr>';
				$colIndex = 0;
				foreach($rowArr as $key => $value) {
					if($colIndex < 2) {
						// kebun & nama_ket columns - freeze ketika scroll horizontal
						$html .= '<td style="position: sticky; left: ' . ($colIndex * 50) . 'px; white-space: nowrap; font-weight: bold; padding-left: 20px; background-color: #fff; z-index: 10;">' . htmlspecialchars($value ?? '') . '</td>';
					} else {
						// status columns: VERIFIED / empty
						$cellStyle = 'text-align: center;';
						if($value === 'VERIFIED') {
							$cellContent = '<i class="fa fa-check-circle" style="color: #28a745; font-size: 16px;"></i>';
						} elseif(stripos($value, 'AJUAN') !== false) {
							$cellContent = '<i class="fa fa-exclamation-circle" style="color: #ffc107; font-size: 16px;"></i>';
						} elseif(!empty($value)) {
							$cellContent = '<span class="badge badge-warning" style="font-size:10px;">' . htmlspecialchars($value) . '</span>';
						} else {
							$cellContent = '<span style="color:#ccc;">-</span>';
						}
						$html .= '<td style="' . $cellStyle . '">' . $cellContent . '</td>';
					}
					$colIndex++;
				}
				$html .= '</tr>';
			}
		} else {
			$html .= '<tr><td colspan="100%" class="text-center">Tidak ada data</td></tr>';
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	public function formRekapPcrcRayonBulanan( Request $request )
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Rekap LKP Rayon Bulanan';
		$this->data['pageNote'] = 'Menampilkan rekap LKP per rayon secara bulanan';	
		
		return view($this->module.'.rekappcrc_rayon_bulanan', $this->data);
	}

	public function getRekapPcrcRayonBulanan(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$startDate = $request->input('start_date', '2023-01-01');
		$endDate = $request->input('end_date', '2026-03-29');
		$kebun = $request->input('kebun', 'SK01');
		
		$results = \DB::select('CALL sp_lkp_rekap_pcrc_rayon_bulanan(?, ?, ?)', 
			[$startDate, $endDate, $kebun]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover table-freeze">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			$columnIndex = 0;
			foreach(array_keys((array)$results[0]) as $column) {
				$freezeClass = $columnIndex < 4 ? 'freeze-col' : '';
				$html .= '<th class="' . $freezeClass . '" style="position: sticky; top: 0; background-color: #f8f9fa;">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
				$columnIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->kode_kat) && strpos($row->kode_kat, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				$columnIndex = 0;
				foreach((array)$row as $value) {
					$freezeClass = $columnIndex < 8 ? 'freeze-col' : '';
					$html .= '<td class="' . $freezeClass . '">' . htmlspecialchars($value ?? '') . '</td>';
					$columnIndex++;
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		$html .= '<style>
		.table-freeze { border-collapse: collapse; }
		.table-freeze th.freeze-col,
		.table-freeze td.freeze-col {
			position: sticky;
			background-color: #fff;
			z-index: 10;
		}
		.table-freeze thead th.freeze-col {
			background-color: #f8f9fa;
			z-index: 11;
		}
		.table-freeze th:nth-child(1).freeze-col,
		.table-freeze td:nth-child(1).freeze-col { left: 0; }
		.table-freeze th:nth-child(2).freeze-col,
		.table-freeze td:nth-child(2).freeze-col { left: 91px; }
		.table-freeze th:nth-child(3).freeze-col,
		.table-freeze td:nth-child(3).freeze-col { left: 191px; }
		.table-freeze th:nth-child(4).freeze-col,
		.table-freeze td:nth-child(4).freeze-col { left: 321px; }
		.table-freeze th:nth-child(5).freeze-col,
		.table-freeze td:nth-child(5).freeze-col { left: 418px; }
		.table-freeze th:nth-child(6).freeze-col,
		.table-freeze td:nth-child(6).freeze-col { left: 581px; }
		.table-freeze th:nth-child(7).freeze-col,
		.table-freeze td:nth-child(7).freeze-col { left: 711px; }
		.table-freeze th:nth-child(8).freeze-col,
		.table-freeze td:nth-child(8).freeze-col { left: 775px; }
		</style>';
		
		return $html;
	}

	public function formTableMonitoringPiPemeliharaanPanenPica(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data = array_merge(array(
			'pageTitle'	=> 'Monitoring PI Pemeliharaan Panen PICA',
			'pageNote'	=> 'Data Monitoring PI Pemeliharaan Panen PICA',
		),$this->data);

		return view($this->module.'.monitoring_pi_pemeliharaan_panen_pica', $this->data);
	}

	public function getTableMonitoringPiPemeliharaanPanenPica(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		config(['app.debug' => true]);
		$cluster = $request->get('cluster', '');
		$tgl = $request->get('tgl', date('Y-m-d'));

		$results = \DB::select('CALL sp_monitoring_pi_pemeliharaan_pica(?, ?)', [$cluster, $tgl]);

		$html = '<table class="table table-bordered table-striped table-responsive" border="1" cellpadding="5" cellspacing="0">';
		$html .= '<thead>';
		
		// First row - main group headers
		$html .= '<tr>';
		$html .= '<th rowspan="2" style="background:#0265c8;color:#fff;text-align:center;">Cluster</th>';
		$html .= '<th rowspan="2" style="background:#0265c8;color:#fff;text-align:center;">Kode Kebun</th>';
		$html .= '<th rowspan="2" style="background:#0265c8;color:#fff;text-align:center;">Kebun</th>';
		$html .= '<th rowspan="2" style="background:#0265c8;color:#fff;text-align:center;"></th>';
		$html .= '<th colspan="6" style="background:#4472C4;color:#fff;text-align:center;">Pemeliharaan</th>';
		$html .= '<th colspan="1" style="background:#5B9BD5;color:#fff;text-align:center;">Panen</th>';
		$html .= '</tr>';
		
		// Second row - sub headers
		$html .= '<tr>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Ha Bumbun 1</th>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Ha Bumbun 2</th>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Ha Pupuk 1</th>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Ha Pupuk 2</th>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Klentek 1</th>';
		$html .= '<th style="background:#4472C4;color:#fff;text-align:center;">Klentek 2</th>';
		$html .= '<th style="background:#5B9BD5;color:#fff;text-align:center;">Ha Protas</th>';
		$html .= '</tr>';
		$html .= '</thead><tbody>';

		// Group data by cluster -> kebun
		$groupedData = [];
		foreach ($results as $row) {
			$clusterKey = $row->cluster;
			$kebunKey   = $row->kode_kebun;

			if (!isset($groupedData[$clusterKey])) {
				$groupedData[$clusterKey] = [];
			}
			if (!isset($groupedData[$clusterKey][$kebunKey])) {
				$groupedData[$clusterKey][$kebunKey] = [
					'cluster'        => $row->cluster,
					'kode_kebun'     => $row->kode_kebun,
					'kebun'          => $row->kebun,
					'ha_bumbun_1'    => [],
					'ha_bumbun_2'    => [],
					'klentek_1'      => [],
					'klentek_2'      => [],
					'ha_pupuk_1'     => [],
					'ha_pupuk_2'     => [],
					'ha_protas'      => [],
				];
			}

			$uraian = $row->uraian;
			$ha     = isset($row->ha)     ? (float)$row->ha     : 0.0;
			$persen = isset($row->persen) ? (float)$row->persen : 0.0;

			if (strpos($uraian, 'ha_pupuk_1') !== false) {
				$groupedData[$clusterKey][$kebunKey]['ha_pupuk_1'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'ha_pupuk_2') !== false) {
				$groupedData[$clusterKey][$kebunKey]['ha_pupuk_2'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'ha_bumbun_1') !== false) {
				$groupedData[$clusterKey][$kebunKey]['ha_bumbun_1'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'ha_bumbun_2') !== false) {
				$groupedData[$clusterKey][$kebunKey]['ha_bumbun_2'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'klentek_1') !== false) {
				$groupedData[$clusterKey][$kebunKey]['klentek_1'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'klentek_2') !== false) {
				$groupedData[$clusterKey][$kebunKey]['klentek_2'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			} elseif (strpos($uraian, 'ha_protas') !== false) {
				$groupedData[$clusterKey][$kebunKey]['ha_protas'][] = ['uraian' => $uraian, 'ha' => $ha, 'persen' => $persen];
			}
		}

		// Render rows
		foreach ($groupedData as $clusterName => $kebunList) {
			$clusterRowspan = count($kebunList);
			$firstKebun     = true;
			foreach ($kebunList as $kebunKey => $kebunData) {
				$html .= '<tr>';

				if ($firstKebun) {
					$html .= '<td rowspan="' . $clusterRowspan . '" style="vertical-align:middle;font-weight:600;text-align:center;">' . htmlspecialchars($clusterName) . '</td>';
					$firstKebun = false;
				}
				
				$html .= '<td style="font-weight:600;color:#1a1a2e;">' . htmlspecialchars($kebunData['kode_kebun']) . '</td>';
				$html .= '<td style="font-weight:500;">' . htmlspecialchars($kebunData['kebun']) . '</td>';
				$html .= '<td style="font-weight:500;"><button class="btn btn-sm btn-primary" onclick="modalfishbond(\'' . htmlspecialchars($kebunData['kode_kebun']) . '\')"><i class="fas fa-fish"></i></button></td>';

				// Ha Bumbun 1
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['ha_bumbun_1'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct = number_format($item['persen'], 1);
					$ha  = number_format($item['ha'], 2);

					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}

					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				// Ha Bumbun 2
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['ha_bumbun_2'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct = number_format($item['persen'], 1);
					$ha  = number_format($item['ha'], 2);
					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}
					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';


				// Klentek 1
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['klentek_1'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct = number_format($item['persen'], 1);
					$ha  = number_format($item['ha'], 2);

					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}

					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				// Klentek 2
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['klentek_2'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct = number_format($item['persen'], 1);
					$ha  = number_format($item['ha'], 2);

					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}

					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				// Ha Pupuk 1
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['ha_pupuk_1'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct   = number_format($item['persen'], 1);
					$ha    = number_format($item['ha'], 2);
					
					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}
					
					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				// Ha Pupuk 2
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['ha_pupuk_2'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct   = number_format($item['persen'], 1);
					$ha    = number_format($item['ha'], 2);
					
					if (strpos($uraian_raw, 'tepat_waktu') !== false) {
						$label  = 'TEPAT WAKTU';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'terlambat') !== false) {
						$label  = 'TERLAMBAT';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}
					
					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				// Ha Protas
				$html .= '<td style="padding:6px;min-width:160px;">';
				foreach ($kebunData['ha_protas'] as $item) {
					$uraian_raw = $item['uraian'];
					$pct = number_format($item['persen'], 1);
					$ha  = number_format($item['ha'], 2);

					if (strpos($uraian_raw, 'diatas_rkap') !== false) {
						$label  = 'DIATAS RKAP';
						$bg     = '#E8F5E9';
						$border = '#43A047';
						$badge  = '#43A047';
						$lcolor = '#2e7d32';
						$icon   = '&#10003;';
					} elseif (strpos($uraian_raw, 'dibawah_rkap') !== false) {
						$label  = 'DIBAWAH RKAP';
						$bg     = '#FFF3E0';
						$border = '#E53935';
						$badge  = '#E53935';
						$lcolor = '#c62828';
						$icon   = '&#10007;';
					} else {
						$label  = 'TOTAL';
						$bg     = '#E3F2FD';
						$border = '#1E88E5';
						$badge  = '#1E88E5';
						$lcolor = '#1565c0';
						$icon   = '&#9776;';
					}

					$html .= '<div style="margin-bottom:5px;padding:5px 8px;background:' . $bg . ';border-left:3px solid ' . $border . ';border-radius:0 5px 5px 0;">';
					$html .= '<div style="font-size:10px;font-weight:700;color:' . $lcolor . ';letter-spacing:.4px;">' . $icon . ' ' . $label . '</div>';
					$html .= '<div style="display:flex;justify-content:space-between;align-items:center;margin-top:3px;">';
					$html .= '<span style="font-size:12px;font-weight:700;color:#1a1a2e;">' . $ha . ' <small style="font-weight:400;color:#666;">ha</small></span>';
					$html .= '<span style="font-size:10px;background:' . $badge . ';color:#fff;padding:1px 6px;border-radius:10px;">' . $pct . '%</span>';
					$html .= '</div></div>';
				}
				$html .= '</td>';

				$html .= '</tr>';
			}
		}

		$html .= '</tbody></table>';
		return response($html);
	}

	public function formMonitoringUmur(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Monitoring Umur';
		$this->data['pageNote'] = 'Menampilkan monitoring umur berdasarkan kriteria yang dipilih';	
		
		return view($this->module.'.monitoring_umur', $this->data);
	}

	public function getMonitoringUmur(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$kebun = $request->input('kebun', '');
		$rayon = $request->input('rayon', '');
		$pc_rc = $request->input('pc_rc', '');
		$tgl_awal = $request->input('start_date', '');
		$tgl_akhir = $request->input('end_date', '');
		$jenis_report = $request->input('jenis_report', '');

		$results = \DB::select('CALL sp_lkp_monitoring_umur_2(?, ?, ?, ?, ?, ?)', 
			[$kebun, $rayon, $pc_rc, $tgl_awal, $tgl_akhir, $jenis_report]);

		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			$columnIndex = 0;
			foreach(array_keys((array)$results[0]) as $column) {
				$stickyStyle = '';
				if($columnIndex < 4) {
					$leftPosition = $columnIndex * 100;
					$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; z-index: 11;';
				}
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa; ' . $stickyStyle . '">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
				$columnIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->kode_kat) && strpos($row->kode_kat, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				$columnIndex = 0;
				foreach((array)$row as $value) {
					$stickyStyle = '';
					if($columnIndex < 4) {
						$leftPosition = $columnIndex * 100;
						$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; background-color: #fff; z-index: 10;';
					}
					$html .= '<td style="' . $stickyStyle . '">' . htmlspecialchars($value ?? '') . '</td>';
					$columnIndex++;
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}


	public function formRekapVarietas(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Rekap LKP by Varietas';
		$this->data['pageNote'] = 'Menampilkan rekap LKP berdasarkan Varietas';	
		
		return view($this->module.'.rekapvarietas', $this->data);
	}

	public function getRekapVarietas(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$startDate = $request->input('start_date', '2023-01-01');
		$endDate = $request->input('end_date', '2026-06-22');
		$kebun = $request->input('kebun', 'SK01');
		
		$results = \DB::select('CALL sp_lkp_rekap_varietas(?, ?, ?)', 
			[$startDate, $endDate, $kebun]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			$columnIndex = 0;
			foreach(array_keys((array)$results[0]) as $column) {
				$stickyStyle = '';
				if($columnIndex < 4) {
					$leftPosition = $columnIndex * 100;
					$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; z-index: 11;';
				}
				$textAlign = $columnIndex >= 6 ? 'text-align: right;' : '';
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa; ' . $stickyStyle . $textAlign . '">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
				$columnIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = isset($row->varietas) && strpos($row->varietas, 'TOTAL') !== false;
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				$columnIndex = 0;
				foreach((array)$row as $value) {
					$stickyStyle = '';
					if($columnIndex < 4) {
						$leftPosition = $columnIndex * 100;
						$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; background-color: #fff; z-index: 10;';
					}
					$textAlign = $columnIndex >= 6 ? 'text-align: right;' : '';
					$html .= '<td style="' . $stickyStyle . $textAlign . '">' . htmlspecialchars($value ?? '') . '</td>';
					$columnIndex++;
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

	public function formRekapPcrcRayonKomparasi(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->data['pageTitle'] = 'Rekap LKP by Rayon Komparasi';
		$this->data['pageNote'] = 'Menampilkan rekap LKP berdasarkan Rayon Komparasi';	
		
		return view($this->module.'.rekap_pcrc_rayon_komparasi', $this->data);
	}

	public function getRekapPcrcRayonKomparasi(Request $request)
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');
		
		$startDate = $request->input('start_date');
		$endDate = $request->input('end_date');
		$region = $request->input('jenis_report');
		
		$results = \DB::select('CALL sp_lkp_rekap_pcrc_rayon_komparasi(?, ?, ?)',
			[$startDate, $endDate, $region]);
		
		$html = '<div style="overflow-x: auto; max-height: 600px; overflow-y: auto;">';
		$html .= '<table class="table table-bordered table-hover">';
		$html .= '<thead style="position: sticky; top: 0; background-color: #fff; z-index: 10;"><tr>';
		
		if(!empty($results)) {
			$columnIndex = 0;
			foreach(array_keys((array)$results[0]) as $column) {
				$stickyStyle = '';
				if($columnIndex < 4) {
					$leftPosition = $columnIndex * 100;
					$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; z-index: 11;';
				}
				$textAlign = $columnIndex >= 6 ? 'text-align: right;' : '';
				$html .= '<th style="position: sticky; top: 0; background-color: #f8f9fa; ' . $stickyStyle . $textAlign . '">' . ucfirst(str_replace('_', ' ', $column)) . '</th>';
				$columnIndex++;
			}
			$html .= '</tr></thead><tbody>';
			
			foreach($results as $row) {
				$isTotalRow = false;
				if(isset($row->kode_rayon)) {
					$isTotalRow = strpos($row->kode_rayon, 'TOTAL') !== false;
				} elseif(isset($row->rayon)) {
					$isTotalRow = strpos($row->rayon, 'TOTAL') !== false;
				}
				$rowStyle = $isTotalRow ? 'background-color: #e8f4f8; font-weight: bold;' : '';
				$html .= '<tr style="' . $rowStyle . '">';
				$columnIndex = 0;
				foreach((array)$row as $value) {
					$stickyStyle = '';
					if($columnIndex < 5) {
						$leftPosition = $columnIndex * 100;
						$stickyStyle = 'position: sticky; left: ' . $leftPosition . 'px; background-color: #fff; z-index: 10;';
					}
					$textAlign = $columnIndex >= 6 ? 'text-align: right;' : '';
					$html .= '<td style="' . $stickyStyle . $textAlign . '">' . htmlspecialchars($value ?? '') . '</td>';
					$columnIndex++;
				}
				$html .= '</tr>';
			}
		}
		
		$html .= '</tbody></table></div>';
		
		return $html;
	}

}