<?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."],
        ["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']);

// 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;
}

 // Ensure the $conn is correctly initialized

// STEP 3: Execute on MySQL
if ($conn->connect_error) {
    echo json_encode(["reply" => "DB connection failed."]);
    exit;
}

$result = $conn->query($sql);
if (!$result) {
    echo json_encode(["reply" => "SQL Error: " . $conn->error]);
    exit;
}

$row = $result->fetch_row();
$reply = "Result: " . $row[0];

echo json_encode(["reply"=>$reply]);
?>