<?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: Get metadata for wko_mst columns
$metaQuery = "
    SELECT
        cf_table.table_name,
        cf_table.table_type,
        parent_table,
        cf_label.column_name,
        column_description = customize_header,
        rowid_column = CASE WHEN table_type = 'Master' THEN 'rowid' ELSE NULL END,
        foreign_key_column = CASE WHEN table_type = 'Master' THEN NULL ELSE 'mst_rowid' END
    FROM cf_table (NOLOCK)
    LEFT JOIN cf_label (NOLOCK) ON cf_table.table_name = cf_label.table_name
    WHERE cf_label.language_cd = 'DEFAULT'
    AND cf_table.table_desc = 'Work Order Master'

    UNION

    SELECT
        cf_table.table_name,
        cf_table.table_type,
        parent_table,
        cf_label.column_name,
        column_description = customize_header,
        rowid_column = CASE WHEN table_type = 'Master' THEN 'rowid' ELSE NULL END,
        foreign_key_column = CASE WHEN table_type = 'Master' THEN NULL ELSE 'mst_rowid' END
    FROM cf_table (NOLOCK)
    LEFT JOIN cf_label (NOLOCK) ON cf_table.table_name = cf_label.table_name
    WHERE cf_label.language_cd = 'DEFAULT'
    AND cf_table.parent_table = 'wko_mst'
";

$metaResult = sqlsrv_query($conn, $metaQuery);
$columnsInfo = [];

if (!$metaResult) {
    echo json_encode(["reply" => "Error fetching table metadata."]);
    exit;
}

while ($row = sqlsrv_fetch_array($metaResult, SQLSRV_FETCH_ASSOC)) {
    $table = $row['table_name'];
    $column = $row['column_name'];
    $desc = $row['column_description'];
    $columnsInfo[] = "$table.$column - $desc";
}

$columnDescriptions = implode("\n", $columnsInfo);

// STEP 2: Create the system prompt dynamically
$systemPrompt = "You are an assistant who only replies with MySQL SELECT queries for a CMMS system. 
Only use SELECT queries, no UPDATE/DELETE/INSERT.
Refer to these tables and columns:

$columnDescriptions

To join theses tables, Table_type Master is always the header table and rowid of the Header table is joining the mst_rowid of Table_Type Detail and List.
Only use columns relevant to the user's message.";

// STEP 3: Ask GPT to generate the SQL
$payload = [
    "model" => "gpt-3.5-turbo",
    "messages" => [
        ["role" => "system", "content" => $systemPrompt],
        ["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);

echo json_encode($result, JSON_PRETTY_PRINT); 

if (isset($result['error'])) {
    echo json_encode(["reply" => "OpenAI Error: " . $result['error']['message']]);
    exit;
}

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 ```sql
$sql = preg_replace('/```$/', '', $sql);        // Remove closing ```

echo $sql;

// STEP 4: Basic protection
if (
    stripos($sql, 'select') !== 0 ||
    preg_match('/(delete|update|insert|drop)/i', $sql)
) {
    echo json_encode(["reply" => "Unsafe or malformed SQL query blocked."]);
    exit;
}

// STEP 5: Execute the generated query
$result = sqlsrv_query($conn, $sql);
if (!$result) {
    echo json_encode(["reply" => "SQL Error: " . print_r(sqlsrv_errors(), true)]);
    exit;
}

// STEP 6: Format the result
$reply = "";
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    foreach ($row as $key => $value) {
        $reply .= $key . ": " . $value . "\n";
    }
    $reply .= "\n";
}

if (empty(trim($reply))) {
    $reply = "No results found.";
}

echo json_encode(["reply" => $reply]);

// Step 9: Include token usage info
$promptTokens = $result['usage']['prompt_tokens'] ?? 0;
$completionTokens = $result['usage']['completion_tokens'] ?? 0;
$totalTokens = $result['usage']['total_tokens'] ?? 0;

echo json_encode([
    "reply" => $reply,
    "tokens_used" => [
        "prompt_tokens" => $promptTokens,
        "completion_tokens" => $completionTokens,
        "total_tokens" => $totalTokens
    ]
]);
?>