<?php
// AI Report Assistant API Endpoint
header('Content-Type: application/json');

// Start session to check authentication
session_start();

// Load config
require_once(__DIR__ . '/ai_config.php');

// Check user is logged in
if (!isset($_SESSION['UserID'])) {
    echo json_encode(['error' => 'Not authenticated. Please log in to the ERP system first.']);
    exit;
}

// Check user is in allowed list
$currentUser = $_SESSION['UserID'];
if (!in_array($currentUser, $AI_ALLOWED_USERS)) {
    echo json_encode(['error' => 'Access denied. You do not have permission to use AI Reports.']);
    exit;
}

// Only accept POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
    echo json_encode(['error' => 'Invalid request method.']);
    exit;
}

// Get the user question
$input = json_decode(file_get_contents('php://input'), true);
$question = isset($input['question']) ? trim($input['question']) : '';

if (empty($question)) {
    echo json_encode(['error' => 'Please enter a question.']);
    exit;
}

// Load schema context
$schema = file_get_contents(AI_SCHEMA_FILE);
if ($schema === false) {
    echo json_encode(['error' => 'Schema context file not found.']);
    exit;
}

// Build prompt for Ollama
$prompt = $schema . "\n\nUser question: " . $question;

// Call Ollama API
$ollamaPayload = json_encode([
    'model' => OLLAMA_MODEL,
    'prompt' => $prompt,
    'stream' => false,
    'options' => [
        'temperature' => 0.1,
        'num_predict' => 500
    ]
]);

$ch = curl_init(OLLAMA_URL);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, $ollamaPayload);
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Content-Type: application/json']);
curl_setopt($ch, CURLOPT_TIMEOUT, 120);

$ollamaResponse = curl_exec($ch);
$curlError = curl_error($ch);
curl_close($ch);

if ($ollamaResponse === false) {
    echo json_encode(['error' => 'Failed to connect to AI service: ' . $curlError]);
    exit;
}

$ollamaData = json_decode($ollamaResponse, true);
$aiResponse = isset($ollamaData['response']) ? trim($ollamaData['response']) : '';

if (empty($aiResponse)) {
    echo json_encode(['error' => 'AI returned empty response. Please try rephrasing your question.']);
    exit;
}

// Extract SQL from response - remove markdown code blocks if present
$sql = $aiResponse;
$sql = preg_replace('/```sql\s*/i', '', $sql);
$sql = preg_replace('/```\s*/', '', $sql);
$sql = trim($sql);

// Remove any explanation text after the SQL (look for first semicolon or double newline)
if (strpos($sql, ';') !== false) {
    $sql = substr($sql, 0, strpos($sql, ';'));
}
$sql = trim($sql);

// SECURITY: Validate the SQL
$sqlUpper = strtoupper($sql);

// Must start with SELECT
if (strpos($sqlUpper, 'SELECT') !== 0) {
    echo json_encode([
        'error' => 'AI did not generate a valid SELECT query. Please rephrase your question.',
        'ai_response' => $aiResponse
    ]);
    exit;
}

// Check for blocked keywords
foreach ($AI_BLOCKED_KEYWORDS as $blocked) {
    // Check for blocked keyword as a whole word (not part of column name)
    if (preg_match('/\b' . $blocked . '\b/i', $sql)) {
        echo json_encode([
            'error' => 'Query blocked for security: contains restricted keyword "' . $blocked . '".',
            'sql' => $sql
        ]);
        exit;
    }
}

// Block multiple statements (semicolons)
if (substr_count($sql, ';') > 0) {
    echo json_encode(['error' => 'Multiple statements not allowed.']);
    exit;
}

// Connect to database with read-only user
$dbConn = mysqli_connect(AI_DB_HOST, AI_DB_USER, AI_DB_PASS, AI_DB_NAME);
if (!$dbConn) {
    echo json_encode(['error' => 'Database connection failed: ' . mysqli_connect_error()]);
    exit;
}
mysqli_set_charset($dbConn, 'utf8');

// Execute the query
$result = mysqli_query($dbConn, $sql);
if (!$result) {
    $dbError = mysqli_error($dbConn);
    mysqli_close($dbConn);
    echo json_encode([
        'error' => 'Query execution failed: ' . $dbError,
        'sql' => $sql
    ]);
    exit;
}

// Fetch results
$columns = [];
$rows = [];

// Get column names
$fieldCount = mysqli_num_fields($result);
$fields = mysqli_fetch_fields($result);
foreach ($fields as $field) {
    $columns[] = $field->name;
}

// Get rows (limit to 500 for safety)
$rowCount = 0;
while ($row = mysqli_fetch_assoc($result) and $rowCount < 500) {
    $rows[] = $row;
    $rowCount++;
}

$totalRows = mysqli_num_rows($result);
mysqli_free_result($result);
mysqli_close($dbConn);

// Return results
echo json_encode([
    'success' => true,
    'sql' => $sql,
    'columns' => $columns,
    'rows' => $rows,
    'rowCount' => $totalRows,
    'truncated' => ($totalRows > 500),
    'user' => $currentUser,
    'question' => $question
]);
?>
