<?php
require_once 'openai_config.php';
require_once 'config.php'; // your DB connection

header('Content-Type: application/json');
$input = json_decode(file_get_contents('php://input'), true);
$userMessage = $input['message'];

// STEP 1: Ask GPT to generate SQL query
$payload = [
    "model" => "gpt-3.5-turbo",
    "messages" => [
        ["role" => "system", "content" => "You're an assistant who only replies with MySQL queries for a CMMS system. Only use SELECT queries, no UPDATE/DELETE/INSERT. Use 'wko_mst' table has the following columns: 
		'site_cd', 
		'wko_mst_wo_no',
		'wko_mst_originator',
		'wko_mst_phone',
		'wko_mst_asset_level',
		'wko_mst_assetno',
		'wko_mst_flt_code',
		'wko_mst_status',
		'wko_mst_due_date',
		'wko_mst_descs',
		'wko_mst_ast_cod'"],
        ["role" => "user", "content" => $userMessage]
    ],
    "temperature" => 0
];




$ch = curl_init('https://api.openai.com/v1/chat/completions');
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    'Content-Type: application/json',
    'Authorization: Bearer ' . OPENAI_API_KEY
]);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($payload));

$response = curl_exec($ch);
curl_close($ch);
$result = json_decode($response, true);

if (!isset($result['choices'][0]['message']['content'])) {
    echo json_encode(["reply" => "No response from AI"]);
    exit;
}

$sql = trim($result['choices'][0]['message']['content']);
$sql = preg_replace('/^```sql\s*/i', '', $sql);   // remove starting sql
$sql = preg_replace('/```$/', '', $sql);          // remove ending 
//$sql = trim($sql);
echo $sql;
// STEP 2: Simple protection
if (stripos($sql, 'select') !== 0 || preg_match('/(delete|update|insert|drop)/i', $sql)) {
    echo json_encode(["reply" => "Unsafe SQL query blocked."]);
    exit;
}



$result = sqlsrv_query($conn, $sql);
if (!$result) {
    echo json_encode(["reply" => "SQL Error: " . print_r(sqlsrv_errors(), true)]);
    exit;
}

/// If it's a regular SELECT query, return the rows
    $reply = "Results:\n";
    while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
        foreach ($row as $key => $value) {
            $reply .= $key . ": " . $value . "\n";
        }
        $reply .= "\n";  // Adding a line break between rows
    }

    if (empty($reply)) {
        $reply = "No results found.";
    }

echo json_encode(["reply"=>$reply]);
?>