<?php namespace App\Http\Controllers\Services;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator as Paginator;
use Validator, Input, Redirect ; 
use Illuminate\Support\Str;
use Illuminate\Support\Facades\DB;

use Config;

class InterfaceDataController extends Controller {

	public function __construct()
	{		
		parent::__construct();
        
	}

	public function index( Request $request )
	{
	}	


    public function aiTools(Request $request)
    {
        $search = trim($request->input('search', ''));

        $query = DB::table('master_api_data')
            ->select([
                'id',
                'title',
                'description',
                'endpoint',
                'procedure',
                'request',
                'response'
            ]);

        if ($search !== '') {
            $query->where(function ($q) use ($search) {
                $q->where('title', 'like', '%' . $search . '%')
                ->orWhere('description', 'like', '%' . $search . '%')
                ->orWhere('procedure', 'like', '%' . $search . '%');
            });
        }

        $rows = $query
            ->orderBy('title')
            ->limit(20)
            ->get();

        $tools = $rows->map(function ($row) {

            $parameters = json_decode($row->request, true);

            if (!is_array($parameters)) {
                $parameters = [];
            }

            $response = json_decode($row->response, true);

            if (!is_array($response)) {
                $response = [];
            }

            return [
                'id' => $row->id,
                'title' => $row->title,
                'description' => $row->description,
                'endpoint' => $row->endpoint,
                'procedure' => $row->procedure,

                'parameters' => $parameters,

                // cukup sebagai referensi struktur
                'response_schema' => $response
            ];
        });

        return response()->json([
            'success' => true,
            'count' => $tools->count(),
            'data' => $tools
        ]);
    }


    public function aiRunProcedure(Request $request)
        {
            config(['app.debug' => true]);

            /*
            |--------------------------------------------------------------------------
            | 1. Ambil Tahun Tanam dari Header
            |--------------------------------------------------------------------------
            |
            | thntnm:
            | 2526 = Masa Panen / Giling
            | 2627 = Masa Pemeliharaan
            |
            */

            $tahunTanam = $request->input(
                'tahuntanam',
                $request->header('thntnm', '2526')
            );

            $tahunTanam = trim((string) $tahunTanam);

            $databaseMap = [
                '2526' => 'mysql_2526',
                '2627' => 'mysql_2627',
            ];

            if (!array_key_exists($tahunTanam, $databaseMap)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Tahun tanam tidak valid',
                    'received_tahuntanam' => $tahunTanam
                ], 400);
            }

            $connection = $databaseMap[$tahunTanam];

            /*
            |--------------------------------------------------------------------------
            | 2. Set Database Sesuai Tahun Tanam
            |--------------------------------------------------------------------------
            */

            \Config::set('database.default', $connection);

            // Pastikan Laravel menggunakan koneksi yang dipilih
            \DB::purge($connection);
            \DB::reconnect($connection);


            /*
            |--------------------------------------------------------------------------
            | 3. Ambil ID Procedure
            |--------------------------------------------------------------------------
            */

            $procedureId = $request->input('id');

            if (empty($procedureId)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Procedure ID is required'
                ], 400);
            }


            /*
            |--------------------------------------------------------------------------
            | 4. Ambil Object Parameters dari Request
            |--------------------------------------------------------------------------
            |
            | Format:
            |
            | {
            |     "id": 43,
            |     "parameters": {
            |         "p_jenis_rekap": "kebun",
            |         "p_tgl_awal": "2026-01-01",
            |         "p_tgl_akhir": "2026-10-01",
            |         "p_periode_prognosa": "9a"
            |     }
            | }
            |
            */

           $requestParameters = $request->input('parameters', []);

            /*
            |--------------------------------------------------------------------------
            | Jika parameters dikirim n8n sebagai JSON STRING
            |--------------------------------------------------------------------------
            |
            | Contoh:
            |
            | "{\"p_jenis_rekap\":\"kebun\",\"p_tgl_awal\":\"2026-01-01\"}"
            |
            | ubah menjadi PHP array.
            |
            */

            if (is_string($requestParameters)) {

                $decodedParameters = json_decode(
                    $requestParameters,
                    true
                );

                if (json_last_error() !== JSON_ERROR_NONE) {
                    return response()->json([
                        'status' => 'error',
                        'message' => 'Format parameters tidak valid',
                        'detail' => json_last_error_msg(),
                        'received' => $requestParameters
                    ], 400);
                }

                $requestParameters = $decodedParameters;
            }

            /*
            |--------------------------------------------------------------------------
            | Setelah decode harus berupa array
            |--------------------------------------------------------------------------
            */

            if (!is_array($requestParameters)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Parameters harus berupa object JSON atau JSON string'
                ], 400);
            }

            /*
            |--------------------------------------------------------------------------
            | 5. Cari Konfigurasi Procedure Berdasarkan ID
            |--------------------------------------------------------------------------
            */

            $dataProcedure = \DB::table('master_api_data')
                ->where('id', $procedureId)
                ->first();

            if (!$dataProcedure) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Procedure not found'
                ], 404);
            }


            /*
            |--------------------------------------------------------------------------
            | 6. Ambil Nama Stored Procedure
            |--------------------------------------------------------------------------
            */

            $procedureName = $dataProcedure->procedure;

            if (empty($procedureName)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Stored procedure is not configured'
                ], 400);
            }


            /*
            |--------------------------------------------------------------------------
            | 7. Validasi Nama Stored Procedure
            |--------------------------------------------------------------------------
            |
            | Nama procedure berasal dari master_api_data.
            | Tetap divalidasi agar hanya karakter yang aman yang dapat digunakan.
            |
            */

            if (!preg_match('/^[A-Za-z0-9_]+$/', $procedureName)) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Stored procedure name is invalid'
                ], 400);
            }


            /*
            |--------------------------------------------------------------------------
            | 8. Ambil Definisi Parameter dari master_api_data
            |--------------------------------------------------------------------------
            */

            $parameterDefinitions = json_decode(
                $dataProcedure->request,
                true
            );

            if (
                !empty($dataProcedure->request) &&
                json_last_error() !== JSON_ERROR_NONE
            ) {
                return response()->json([
                    'status' => 'error',
                    'message' => 'Konfigurasi parameter procedure tidak valid'
                ], 500);
            }

            $inputParameters = [];
            $usedParameters = [];


            /*
            |--------------------------------------------------------------------------
            | 9. Susun Parameter Sesuai Urutan Stored Procedure
            |--------------------------------------------------------------------------
            |
            | PENTING:
            |
            | Jangan menggunakan urutan parameter dari request AI.
            |
            | Urutan CALL harus mengikuti parameterDefinitions dari
            | master_api_data.
            |
            */

            if (is_array($parameterDefinitions)) {

                foreach ($parameterDefinitions as $paramDef) {

                    if (!isset($paramDef['PARAMETER_NAME'])) {
                        continue;
                    }

                    $parameterName = $paramDef['PARAMETER_NAME'];

                    $dataType = strtolower(
                        $paramDef['DATA_TYPE'] ?? 'varchar'
                    );


                    /*
                    |--------------------------------------------------------------------------
                    | 10. Periksa Parameter dari AI
                    |--------------------------------------------------------------------------
                    */

                    if (!array_key_exists(
                        $parameterName,
                        $requestParameters
                    )) {

                        return response()->json([
                            'status' => 'error',
                            'message' => "Parameter {$parameterName} is required"
                        ], 400);
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | 11. Ambil Nilai Parameter
                    |--------------------------------------------------------------------------
                    */

                    $value = $requestParameters[$parameterName];


                    /*
                    |--------------------------------------------------------------------------
                    | 12. Normalisasi NULL
                    |--------------------------------------------------------------------------
                    |
                    | Mendukung:
                    |
                    | null
                    | "null"
                    | "NULL"
                    |
                    */

                    if (
                        $value === null ||
                        (
                            is_string($value) &&
                            strtolower(trim($value)) === 'null'
                        )
                    ) {
                        $value = null;
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | 13. Decode URL jika String
                    |--------------------------------------------------------------------------
                    */

                    if (is_string($value)) {
                        $value = urldecode($value);
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | 14. Validasi DATE
                    |--------------------------------------------------------------------------
                    */

                    if (
                        $dataType === 'date' &&
                        $value !== null &&
                        $value !== '' &&
                        !preg_match('/^\d{4}-\d{2}-\d{2}$/', $value)
                    ) {

                        return response()->json([
                            'status' => 'error',
                            'message' =>
                                "Parameter {$parameterName} harus format YYYY-MM-DD"
                        ], 400);
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | 15. Validasi Numeric
                    |--------------------------------------------------------------------------
                    */

                    if (
                        in_array($dataType, [
                            'int',
                            'integer',
                            'bigint',
                            'smallint',
                            'tinyint',
                            'decimal',
                            'double',
                            'float'
                        ]) &&
                        $value !== null &&
                        $value !== '' &&
                        !is_numeric($value)
                    ) {

                        return response()->json([
                            'status' => 'error',
                            'message' =>
                                "Parameter {$parameterName} harus berupa angka"
                        ], 400);
                    }


                    /*
                    |--------------------------------------------------------------------------
                    | 16. Masukkan Parameter Sesuai Urutan
                    |--------------------------------------------------------------------------
                    */

                    $inputParameters[] = $value;

                    $usedParameters[$parameterName] = $value;
                }
            }


            /*
            |--------------------------------------------------------------------------
            | 17. Jalankan Stored Procedure
            |--------------------------------------------------------------------------
            */

            try {

                if (count($inputParameters) > 0) {

                    $placeholders = implode(
                        ',',
                        array_fill(
                            0,
                            count($inputParameters),
                            '?'
                        )
                    );

                    $sql = "CALL {$procedureName}({$placeholders})";

                    $result = \DB::select(
                        $sql,
                        $inputParameters
                    );

                } else {

                    $result = \DB::select(
                        "CALL {$procedureName}()"
                    );
                }


                /*
                |--------------------------------------------------------------------------
                | 18. Response ke DIFA / n8n
                |--------------------------------------------------------------------------
                */

                return response()->json([
                    'status' => 'success',

                    'source' => [
                        'id' => $dataProcedure->id,
                        'title' => $dataProcedure->title,
                        'description' => $dataProcedure->description
                    ],

                    'parameters' => $usedParameters,

                    'data' => $result
                ]);


            } catch (\Throwable $e) {

                /*
                |--------------------------------------------------------------------------
                | 19. Error Response
                |--------------------------------------------------------------------------
                */

                return response()->json([
                    'status' => 'error',
                    'message' => $e->getMessage()
                ], 500);
            }
        }
}