<?php namespace App\Http\Controllers;

use App\Models\Interfacedata;
use App\Library\DataHelpers;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator as Paginator;
use Illuminate\Support\Facades\DB;
use Validator, Input, Redirect ; 

class InterfacedataController extends Controller {

	protected $layout = "layouts.main";
	protected $data = array();	
	public $module = 'interfacedata';
	static $per_page	= '10';
	
	public function __construct() 
	{
		parent::__construct();
		$this->model = new Interfacedata();
		
		$this->info = $this->model->makeInfo( $this->module);
		$this->access = $this->model->validAccess($this->info['id']);
	
		$this->data = array(
			'pageTitle'			=> 	$this->info['title'],
			'pageNote'			=>  $this->info['note'],
			'pageModule'		=> 'interfacedata',
			'pageUrl'			=>  url('interfacedata'),
			'return' 			=> 	self::returnUrl()	
		);	
			
				
	} 
	public function index()
	{
		if(!\Auth::check()) 
			return redirect('user/login')->with('status', 'error')->with('message','You are not login');

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_view'] ==0) 
			return redirect('dashboard')->with('message',__('core.note_restric'))->with('status','error');
		
		$this->data['setting'] 		= $this->info['setting'];
		$this->data['tableForm'] 	= $this->info['config']['forms'];
		$this->data['tableGrid'] 	= $this->info['config']['grid'];
		usort($this->data['tableGrid'], "SiteHelpers::_sort");
			$cols = '{"data":"rowId"},';
			if($this->data['setting']['view-method'] =='expand'){
				$cols .= '{"className": "details-control","orderable": false,"data": null,"defaultContent": ""},';
			}		
				
			foreach($this->data['tableGrid'] as $field)
			{				
				
				if($field['view'] =='1')
				{
					$cols .= '{"data":"'.$field['field'].'"},';
				}						
			}
			$cols .= '{"data":"action"},';
		$this->data['column'] = $cols ;	
		$this->data['access']		= $this->access;	
		$this->data['setting'] 		= $this->info['setting'];
		return view('interfacedata.index',$this->data);
	}
	function create( Request $request ) 
	{
		$this->hook( $request  );
		if($this->access['is_add'] ==0) 
			return redirect('dashboard')->with('message', __('core.note_restric'))->with('status','error');

		$this->data['row'] = $this->model->getColumnTable( $this->info['table']); 
		
		$this->data['setting'] 		= $this->info['setting'];
		$this->data['id'] = '';
		return view($this->module.'.form',$this->data);
	}
	function edit( Request $request , $id ) 
	{
		$this->hook( $request , $id );
		if(!isset($this->data['row']))
			return redirect($this->module)->with('message','Record Not Found !')->with('status','error');
		if($this->access['is_edit'] ==0 )
			return redirect('dashboard')->with('message',__('core.note_restric'))->with('status','error');
		$this->data['row'] = (array) $this->data['row'];
		
		$this->data['setting'] 		= $this->info['setting'];
		$this->data['id'] = $id;
		return view($this->module.'.form',$this->data);
	}
	function show( Request $request , $id ) 
	{
		/* Handle import , export and view */
		$task =$id ;
		switch( $task)
		{
			case 'search':
				return $this->getSearch();
				break;
			case 'lookup':
				return $this->getLookup($request );
				break;
			case 'comboselect':
				return $this->getComboselect( $request );
				break;
			case 'import':
				return $this->getImport( $request );
			case 'removefiles':
				return $this->getRemovefiles( $request );	
				break;
			case 'export':
				return $this->getExport( $request );
				break;
			case 'getStoreProcedureList':
				return $this->getStoreProcedureList( $request );
				break;
			case 'getStoredProcedureParameters':
				return $this->getStoredProcedureParameters( $request );
				break;	
			case 'runProcedure':
				return $this->runProcedure( $request );
				break;	
			case 'getMasterApiData':
				return $this->getMasterApiData( $request );
				break;
			case 'formRunProcedure':
				return $this->formRunProcedure( $request );
				break;
			default:
				$this->hook( $request , $id );
				if(!isset($this->data['row']))
					return redirect($this->module)->with('message','Record Not Found !')->with('status','error');

				if($this->access['is_detail'] ==0) 
					return redirect('dashboard')->with('message', __('core.note_restric'))->with('status','error');

				return view($this->module.'.view',$this->data);	
				break;		
		}
	}	
	public function store( Request $request){

		$task = $request->input('action_task');
		switch ($task)
		{
			default:
				return $this->postData( $request);
				break;
			case 'save':
				$rules = $this->validateForm();
				$validator = Validator::make($request->all(), $rules);
				if ($validator->passes()) 
				{
					$data = $this->validatePost( $request );
					$id = $this->model->insertRow($data , $request->input( $this->info['key']));
					
					/* Insert logs */
					$this->model->logs($request , $id);
					return response()->json(array('status'=>'success','message'=> __('core.note_success')));	
					
				} 
				else {
					$message = $this->validateListError(  $validator->getMessageBag()->toArray() );
					return response()->json(array(
						'message'	=> $message,
						'status'	=> 'error'
					));	
				}
				break;
			case 'delete':
				return $result = $this->destroy( $request );
				break;

			case 'import':
				return $this->PostImport( $request );
				break;

			case 'copy':
				$result = $this->copy( $request );
				return response()->json($result);
				break;		
			case 'runStoredProcedure':
				return $this->runStoredProcedure( $request );
				break;	

		}
	}
	public function postData( $request)
	{ 
		config(['app.debug' => true]);
		
		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));

		$tables 	= $this->info['config']['grid'];
		$cols = array();
		foreach($tables as $field)
		{				
			//$cols[$this->info['key']]	= array('db'=> $field['alias'] ,$this->info['key'] );
			if($field['view'] =='1')
			{
				$cols[] = array('db'=> $field['alias'],'dt'=> $field['field'] );
			}					
		}
		$conf = DataHelpers::simple($request->all(),$cols);
		$filter =  '';
		if(!is_null($request->input('search')))
		{
			$filter .= $this->liveSearch( $request);
		}

		$params = array(
			'page'		=> $conf['page'] ,
			'limit'		=> $conf['limit'],
			'sort'		=> ($conf['order']['sort'] !='' ? $conf['order']['sort'] : $this->info['setting']['orderby']) ,
			'order'		=> ($conf['order']['by'] !='' ? $conf['order']['by'] : $this->info['setting']['ordertype']),
			'params'	=> $filter,
			'global'	=> (isset($this->access['is_global']) ? $this->access['is_global'] : 0 )
		);


		$results = $this->model->getRows($params);
		
		$array = array();
		foreach($results['rows'] as $row)
		{
			$values = array();
			$values['rowId'] = $row->{$this->info['key']};
			foreach($tables as $field)
			{			
				
				if($field['view'] =='1')
				{
					if($field['field'] == 'request'){
						$arrayData = [];
						$arrayRequest = json_decode((string)($row->{$field['field']} ?? ''), true);
						if (is_array($arrayRequest)) {
							foreach ($arrayRequest as $requestItem) {
								if (is_array($requestItem) && isset($requestItem['PARAMETER_NAME'], $requestItem['DATA_TYPE'])) {
									$arrayData[] = $requestItem['PARAMETER_NAME'] . ' (' . $requestItem['DATA_TYPE'] . ')';
								}
							}
						}
						$values[$field['field']] = implode(', ', $arrayData);
						if ($values[$field['field']] === '' && !empty($row->{$field['field']})) {
							$values[$field['field']] = $row->{$field['field']};
						}
					}else{
	
						$values[$field['field']] = \SiteHelpers::formatRows($row->{$field['field']}, $field , $row);	
					}
				}	
			}
			//button action runStoredProcedure
			$values['action'] =  '<button type="button" class="btn btn-sm btn-primary" onclick="runProcedure('.$row->{$this->info['key']}.')">Run Procedure</button>';
			$values['action'] .=  ' <a href="' . url('interfacedata/' . $row->{$this->info['key']} . '/edit') . '" onclick="ajaxViewDetail(\'#interfacedata\',this.href); return false; " class="btn btn-sm btn-success">  Edit </a>' ;
			$array[] = $values; 	
		}
		
		$data = array(
			'draw'			=> (!is_null($request->input('draw')) ? intval($request->input('draw') ) : 0), 
			'recordsTotal'	=> $results['total'] ,
			'recordsFiltered'	=> $results['total'],
			'data'				=> $array

		);
		return response()->json($data);
	}

	public function destroy( Request $request)
	{

		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_remove'] ==0) 
			return response()->json(array('status'=>'error','message'=> __('core.note_restric')));	


		// delete multipe rows 
		if(count($request->input('ids')) >=1)
		{
			$this->model->destroy($request->input('ids'));
			
			return response()->json(array(
				'status'=>'success',
				'message'=> __('core.note_success_delete')
			));
		} else {
			return response()->json(array(
				'status'=>'error',
				'message'=> __('core.note_error')
			));

		} 		

	}

	//fungsi untuk get list store procedure
	public function getStoreProcedureList( Request $request )
	{
		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_view'] ==0) 
			return response()->json(array('status'=>'error','message'=> __('core.note_restric')));

		$list = array();
		//parameters to get list of stored procedures from the database
		$params = $request->input('term', '');
		$andwhere = '';
		if($params != '')
		{
			$andwhere = " AND ROUTINE_NAME LIKE '%".$params."%'";
		}
		$procedures = \DB::select("SELECT ROUTINE_NAME FROM information_schema.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_SCHEMA = DATABASE()".$andwhere);

		foreach($procedures as $procedure)
		{
			$list[] = $procedure->ROUTINE_NAME;
		}

		return response()->json(array(
			'status' => 'success',
			'data' => $list
		));
	}

	//get parameters of a specific stored procedure
	public function getStoredProcedureParameters(Request $request)
	{
		$procedureName = $request->input('procedureName', '');
		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_view'] ==0) 
			return response()->json(array('status'=>'error','message'=> __('core.note_restric')));

		$parameters = \DB::select("SELECT DISTINCT PARAMETER_NAME, DATA_TYPE FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME = ?", [$procedureName]);

		return response()->json(array(
			'status' => 'success',
			'data' => $parameters
		));
	}

	//run a stored procedure with parameters
	public function runStoredProcedure(Request $request)
	{
		$procedureName = $request->input('procedureName', '');
		$parameters = $request->input('parameters', []);

		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_view'] ==0) 
			return response()->json(array('status'=>'error','message'=> __('core.note_restric')));

		try {
			// Execute the stored procedure. If there are no parameters, don't pass the parameters array
			if (count($parameters) > 0) {
				$placeholders = implode(',', array_fill(0, count($parameters), '?'));
				$result = \DB::select("CALL $procedureName($placeholders)", array_values($parameters));
			} else {
				$result = \DB::select("CALL $procedureName()");
			}

			return response()->json(array(
				'status' => 'success',
				'data' => $result
			));
		} catch (\Exception $e) {
			return response()->json(array(
				'status' => 'error',
				'message' => $e->getMessage()
			));
		}

	}

	//fungsi runProcedure
	public function runProcedure(Request $request)
	{
		config(['app.debug' => true]);
		$procedureId = $request->input('id', '');

		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$this->access = $this->model->validAccess($this->info['id'] , session('gid'));
		if($this->access['is_view'] ==0) 
			return response()->json(array('status'=>'error','message'=> __('core.note_restric')));

		if (empty($procedureId)) {
			return response()->json(array('status' => 'error', 'message' => 'Procedure ID is required'));
		}

		$dataProcedure = \DB::table('master_api_data')->where('id', $procedureId)->first();
		if (!$dataProcedure) {
			return response()->json(array('status' => 'error', 'message' => 'Procedure not found'));
		}

		$procedureName = $dataProcedure->procedure;
		$parameterDefinitions = json_decode($dataProcedure->request, true);
		$inputParameters = [];

		if (is_array($parameterDefinitions)) {
			foreach ($parameterDefinitions as $paramDef) {
				if (isset($paramDef['PARAMETER_NAME'])) {
					$value = $request->input($paramDef['PARAMETER_NAME']);
					if (is_string($value)) {
						$value = urldecode($value);
					}
					$inputParameters[] = $value;
				}
			}
		}
		

		try {
			if (count($inputParameters) > 0) {
				$placeholders = implode(',', array_fill(0, count($inputParameters), '?'));
				$result = \DB::select("CALL {$procedureName}({$placeholders})", $inputParameters);
			} else {
				$result = \DB::select("CALL {$procedureName}()");
			}

			return response()->json(array(
				'status' => 'success',
				'data' => $result
			));
		} catch (\Exception $e) {
			return response()->json(array(
				'status' => 'error',
				'message' => $e->getMessage()
			));
		}
	}

	//form html for run procedure 
	public function formRunProcedure(Request $request)
	{
		$procedureId = $request->input('id', '');

		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));

		$data = \DB::table('master_api_data')->where('id', $procedureId)->first();
		if ($data) {
			$parameterDefinitions = json_decode($data->request, true);
			$formHtml = '<form id="formdata">';
			if (is_array($parameterDefinitions)) {
				foreach ($parameterDefinitions as $paramDef) {
					if (isset($paramDef['PARAMETER_NAME'])) {
						//datatype handling can be added here based on $paramDef['DATA_TYPE'] if needed
						$dataType = isset($paramDef['DATA_TYPE']) ? $paramDef['DATA_TYPE'] : 'text';
						$formHtml .= '<div class="form-group">';
						$formHtml .= '<label for="'.$paramDef['PARAMETER_NAME'].'">'.$paramDef['PARAMETER_NAME'].'</label>';
						$formHtml .= '<input type="'.$dataType.'" class="form-control" name="'.$paramDef['PARAMETER_NAME'].'" id="'.$paramDef['PARAMETER_NAME'].'" required>';
						$formHtml .= '</div>';
					}
				}
			}
			$formHtml .= '<button id="runProcedureForm" type="button" class="btn btn-xs btn-primary">Run Procedure</button>';
			$formHtml .= '</form>';

			$formHtml .= '<table id="procedureResult" class="table table-bordered table-responsive mt-3"></table>';

			$formHtml .= '<script>
				$("#runProcedureForm").on("click", function(e) {
				//loading indicator
				$("#procedureResult").html("<p class=\"text-center\">Loading Content .. Please wait</p>");
					e.preventDefault();
					var formData = $("#formdata").serializeArray();
					var parameters = {};
					formData.forEach(function(item) {
						parameters[item.name] = item.value;
					});
					$.ajax({
						url: "'.url("interfacedata?action_task=runStoredProcedure").'",
						type: "POST",
						data: {
							procedureName: "'.$data->procedure.'",
							parameters: parameters,
							_token: "'.csrf_token().'"
						},
						success: function(response) {
							if (response.status === "success") {
								$("#procedureResult").empty(); // Clear previous table data
								if (response.data.length > 0) {
									var tableHeader = "<thead><tr>";
									Object.keys(response.data[0]).forEach(function (key) {
										tableHeader += "<th>" + key + "</th>";
									});
									tableHeader += "</tr></thead>";
									$("#procedureResult").append(tableHeader);

									var tableBody = "<tbody>";
									response.data.forEach(function (row) {
										tableBody += "<tr>";
										Object.values(row).forEach(function (value) {
											tableBody += "<td>" + value + "</td>";
										});
										tableBody += "</tr>";
									});
									tableBody += "</tbody>";
									$("#procedureResult").append(tableBody);
									} else {
										$("#procedureResult").html("<tr><td colspan=\"100%\">No data returned.</td></tr>");
									}
							} else {
								alert("Error: " + response.message);
							}
						},
						error: function(xhr, status, error) {
							alert("An error occurred while executing the procedure.");
						}
					});
				});
				</script>';
			return $formHtml;
		} else {
			return '<p class="text-danger">Procedure not found</p>';
		}
	}

	//get data master_api_data untuk membuat prompt di halaman conten.blade.php
	public function getMasterApiData(Request $request)
	{
		if(!\Auth::check()) 
			return response()->json(array(
				'status'=>'error',
				'message'=> 'You are not login'
			));


		$masterApiData = \DB::table('master_api_data')->where('id', $request->input('id'))->first();
		$promptData = [];
		if ($masterApiData) {
			$parameterNames = [];
			$requestData = $masterApiData->request;
			if (!empty($requestData)) {
				$decoded = json_decode($requestData, true);
				if (is_array($decoded)) {
					$parameterNames = array_column($decoded, 'PARAMETER_NAME');
				}
			}

			$promptData = [
				'id' => $masterApiData->id,
				'title' => $masterApiData->title,
				'procedure_name' => $masterApiData->procedure,
				'parameters' => implode(', ', $parameterNames),
				'response' => $masterApiData->response
			];
		}

		return response()->json(array(
			'status' => 'success',
			'data' => $promptData
		));
	} 


}