<?php
//require_once('vendor_classes/PHPExcel.php');

class lcs_ai_report {
	protected $prompt = '';
	protected $query = '';
	protected $events = [];
	protected $events_json = '';

	public function __construct($query) {
		$schema = $this->get_db_schema();
		$this->query = $query;
		$this->prompt  = "
			You are an expert SQL generator.
			Given the following schema and plain English user query, write a single SQL query.

			Rules:
			- Output ONLY the SQL query (no markdown, no explanations).
			- Anything from the user query needs to be parameterized for prepared statements.
			- PDO::ATTR_EMULATE_PREPARES is set to FALSE, so each parameter must be unique, no reusing parameters in multiple places within the SQL.
			- Output should be JSON with SQL as string and PARAMETERS as associative array.  If nothing in PARAMETERS, return an empty array.
			- SELECT queries only, no drops, creates, updates, deletes, or inserts of any kind.
			- Include a list of relevant fields along with fields requested by the user, avoid 'SELECT *'.
			- Use MySQL syntax.
			- Use table/column names exactly as in the schema.
			- If you need a LIMIT, default to 10.

			Schema:
			$schema

			User query: \"$this->query\"
		";
	}

	function get_db_schema() {
		global $db;
		$sql = "
			SELECT TABLE_NAME
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = :db
				AND TABLE_TYPE = 'BASE TABLE'
			ORDER BY TABLE_NAME
		";
		$tables = db_cached_query($sql, 'arr', [':db' => DB_NAME], 3600);

		$sql = "
			SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA
			FROM INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_SCHEMA = :db
			ORDER BY TABLE_NAME, ORDINAL_POSITION
		";
		$columns = db_cached_query($sql, 'arr', [':db' => DB_NAME], 3600);

		// Build a simple schema string for prompting
		$schema = "Tables and columns:\n";
		foreach ($tables as $table) :
			$schema .= "- {$table['TABLE_NAME']}:\n";
			foreach ($columns as $column) :
				if ($column['TABLE_NAME'] === $table['TABLE_NAME']) :
					$nullable = ($column['IS_NULLABLE'] === 'YES') ? 'NULL' : 'NOT NULL';
					$default = $column['COLUMN_DEFAULT'] !== null ? " DEFAULT ".$column['COLUMN_DEFAULT'] : '';
					$extra = $column['EXTRA'] ? " ".$column['EXTRA'] : '';
					$schema .= "  - {$column['COLUMN_NAME']} ({$column['DATA_TYPE']}), $nullable{$default}{$extra}\n";
				endif;
			endforeach;
		endforeach;
		return $schema;
	}

	function get_sql_from_english_query() {
		$ch = curl_init("https://api.openai.com/v1/chat/completions");

		$payload = [
			"model" => "gpt-5.6-luna",
			"messages" => [
				["role" => "user", "content" => $this->prompt]
			]
		];

		curl_setopt_array($ch, [
			CURLOPT_POST => true,
			CURLOPT_RETURNTRANSFER => true,
			CURLOPT_HTTPHEADER => [
				"Content-Type: application/json",
				"Authorization: Bearer " . APP_OPENAI_API_KEY
			],
			CURLOPT_POSTFIELDS => json_encode($payload)
		]);

		$response = curl_exec($ch);

		if (curl_errno($ch)) :
			throw new Exception("cURL error: " . curl_error($ch));
		endif;

		curl_close($ch);

		$data = json_decode($response, true);
		return json_decode($data["choices"][0]["message"]["content"], true);
	}

}