<?php 
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\DB;

// Get selected tables from request
$selectedTables = request('tables', []);
if (empty($selectedTables)) {
    echo json_encode([
        'success' => false,
        'message' => 'No tables selected'
    ]);
    exit;
}

// Get all tables and their columns
$tables = Schema::getConnection()->getDoctrineSchemaManager()->listTableNames();
$dbStructure = [];

// Only include selected tables and their columns
foreach ($selectedTables as $table) {
    if (in_array($table, $tables)) {
        $columns = Schema::getColumnListing($table);
        $columnTypes = [];
        
        foreach ($columns as $column) {
            $type = DB::connection()->getDoctrineColumn($table, $column)->getType()->getName();
            $columnTypes[$column] = $type;
        }
        
        $dbStructure[$table] = $columnTypes;
    }
}

// Get the prompt from the request
$prompt = request('prompt');

// Prepare the context for Gemini
$context = "Database Structure:\n";
foreach ($dbStructure as $table => $columns) {
    $context .= "Table: $table\n";
    foreach ($columns as $column => $type) {
        $context .= "  - $column ($type)\n";
    }
    $context .= "\n";
}

$context .= "\nUser Request: $prompt\n\n";
$context .= "Please generate a valid MySQL query that satisfies the user's request. Only return the SQL query without any explanations or markdown formatting.";

// Call Gemini API
$apiKey = setting('gemini_api_key');
$url = "https://generativelanguage.googleapis.com/v1beta/models/gemini-1.5-flash:generateContent?key=" . $apiKey;

$data = [
    "contents" => [
        [
            "parts" => [
                ["text" => $context]
            ]
        ]
    ],
    "generationConfig" => [
        "temperature" => 0.1,
        "topK" => 1,
        "topP" => 1,
        "maxOutputTokens" => 2048,
    ]
];

$ch = curl_init($url);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($data));
curl_setopt($ch, CURLOPT_HTTPHEADER, [
    'Content-Type: application/json'
]);

$response = curl_exec($ch);
$httpCode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);

if ($httpCode === 200) {
    $result = json_decode($response, true);
    if (isset($result['candidates'][0]['content']['parts'][0]['text'])) {
        $sql = trim($result['candidates'][0]['content']['parts'][0]['text']);
        
        // Remove any markdown code block formatting if present
        $sql = preg_replace('/^```sql\s*|\s*```$/m', '', $sql);
        
        echo json_encode([
            'success' => true,
            'sql' => $sql
        ]);
    } else {
        echo json_encode([
            'success' => false,
            'message' => 'Failed to generate SQL query'
        ]);
    }
} else {
    echo json_encode([
        'success' => false,
        'message' => 'Failed to communicate with Gemini API'
    ]);
}
?>