<?php
/**
 * TalkGenAI Database Class
 * Minimal implementation to support plugin initialization and migration
 */

// Prevent direct access
if (!defined('ABSPATH')) {
	exit('Direct access not allowed.');
}

class TalkGenAI_Database {
	
	/**
	 * @var string Fully-qualified table name for apps
	 */
	private $apps_table;
	
	/**
	 * Constructor
	 */
	public function __construct() {
		global $wpdb;
		$this->apps_table = $wpdb->prefix . 'talkgenai_apps';
	}
	
	/**
	 * Get apps table name
	 */
	public function get_apps_table() {
		return $this->apps_table;
	}
	
	/**
	 * Create required database tables
	 */
	public function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		
		$charset_collate = $wpdb->get_charset_collate();
		
	// phpcs:disable WordPress.DB.DirectDatabaseQuery.SchemaChange
	$sql = "CREATE TABLE " . esc_sql($this->apps_table) . " (
		id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
		user_id BIGINT(20) UNSIGNED NULL,
		title VARCHAR(255) NULL,
		description TEXT NULL,
		app_class VARCHAR(100) NULL,
		app_type VARCHAR(100) NULL,
		status VARCHAR(50) DEFAULT 'active',
		sticky TINYINT(1) NOT NULL DEFAULT 0,
		html_content LONGTEXT NULL,
		css_content LONGTEXT NULL,
		js_content LONGTEXT NULL,
		json_spec LONGTEXT NULL,
		css_file_url VARCHAR(500) NULL,
		js_file_url VARCHAR(500) NULL,
		file_version VARCHAR(100) NULL,
		security_hash VARCHAR(64) NULL,
		created_at DATETIME NULL DEFAULT NULL,
		updated_at DATETIME NULL DEFAULT NULL,
		PRIMARY KEY  (id),
		KEY user_id (user_id),
		KEY status (status),
		KEY app_type (app_type)
	) $charset_collate;";
		
		dbDelta($sql);
	}
	
	/**
	 * Drop database tables (used on uninstall)
	 */
	public function drop_tables() {
		global $wpdb;
		$table_name = esc_sql($this->apps_table);
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Direct query required for uninstallation; Schema change is intentional
		$wpdb->query("DROP TABLE IF EXISTS " . esc_sql($table_name));
	}
	
	/**
	 * Get a single app by ID
	 */
	public function get_app($app_id) {
		global $wpdb;
		$app_id = intval($app_id);
		if ($app_id <= 0) {
			return false;
		}
		$table_name = esc_sql($this->apps_table);
		// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is required for database abstraction layer
		$row = $wpdb->get_row(
			$wpdb->prepare("SELECT * FROM " . esc_sql($table_name) . " WHERE id = %d", $app_id),
			ARRAY_A
		);
		
		return $row ?: false;
	}

	/**
	 * Get recent apps for a user by status
	 */
	public function get_user_apps($user_id, $status = 'active', $limit = 5) {
		global $wpdb;
		$user_id = intval($user_id);
		$limit = max(1, intval($limit));
		$status = $status ? $status : 'active';
		$table_name = esc_sql($this->apps_table);
		
        if ($user_id <= 0) {
            // If user is not specified, fallback to latest public/active apps
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is required for database abstraction layer
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, title, description, created_at FROM " . esc_sql($table_name) . " WHERE status = %s ORDER BY created_at DESC LIMIT %d",
                    $status,
                    $limit
                ),
                ARRAY_A
            );
        } else {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is required for database abstraction layer
            $rows = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT id, title, description, created_at FROM " . esc_sql($table_name) . " WHERE user_id = %d AND status = %s ORDER BY created_at DESC LIMIT %d",
                    $user_id,
                    $status,
                    $limit
                ),
                ARRAY_A
            );
        }
		return is_array($rows) ? $rows : array();
	}

	/**
	 * Update app fields and recompute security_hash when content changes
	 */
	public function update_app($app_id, array $fields) {
		global $wpdb;
		$app_id = intval($app_id);
		if ($app_id <= 0) {
			return new WP_Error('invalid_app_id', 'Invalid app ID');
		}
		if (empty($fields)) {
			return new WP_Error('no_fields', 'No fields to update');
		}
		
		// Fetch existing to build new security hash if needed
		$current = $this->get_app($app_id) ?: array();
		
	$allowed_columns = array(
		'title' => '%s',
		'description' => '%s',
		'app_class' => '%s',
		'app_type' => '%s',
		'html_content' => '%s',
		'css_content' => '%s',
		'js_content' => '%s',
		'json_spec' => '%s',
		'status' => '%s',
		'sticky' => '%d',
		'css_file_url' => '%s',
		'js_file_url' => '%s',
		'file_version' => '%s'
	);
		
		$data = array();
		$format = array();
		foreach ($fields as $key => $value) {
			if (array_key_exists($key, $allowed_columns)) {
				$data[$key] = $value;
				$format[] = $allowed_columns[$key];
			}
		}
		if (empty($data)) {
			return new WP_Error('no_valid_fields', 'No valid fields to update');
		}
		
		// Recompute security hash if any content changed
		$final_html = array_key_exists('html_content', $data) ? $data['html_content'] : ($current['html_content'] ?? '');
		$final_css = array_key_exists('css_content', $data) ? $data['css_content'] : ($current['css_content'] ?? '');
		$final_js = array_key_exists('js_content', $data) ? $data['js_content'] : ($current['js_content'] ?? '');
		$final_spec = array_key_exists('json_spec', $data) ? $data['json_spec'] : ($current['json_spec'] ?? '');
		
		$recompute = (
			array_key_exists('html_content', $data) ||
			array_key_exists('css_content', $data) ||
			array_key_exists('js_content', $data) ||
			array_key_exists('json_spec', $data)
		);
		if ($recompute) {
			$data['security_hash'] = hash('sha256', (string)$final_html . (string)$final_css . (string)$final_js . (string)$final_spec);
			$format[] = '%s';
		}

		// Set updated_at explicitly for MySQL 5.5 compatibility
		$data['updated_at'] = current_time('mysql');
		$format[] = '%s';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is required for database abstraction layer; Caching not applicable for write operations
		$result = $wpdb->update(
			$this->apps_table,
			$data,
			array('id' => $app_id),
			$format,
			array('%d')
		);
		
		if ($result === false) {
			return new WP_Error('db_update_failed', 'Database update failed: ' . $wpdb->last_error);
		}
		
		return true;
	}

	/**
	 * Insert a new app record and compute security_hash
	 */
	public function insert_app(array $fields) {
		global $wpdb;
		
		// Debug logging removed for WordPress.org submission
		// if (defined('WP_DEBUG') && WP_DEBUG) {
		// 	error_log('TalkGenAI DEBUG: insert_app() called with ' . count($fields) . ' fields');
		// }

	$allowed_columns = array(
		'user_id' => '%d',
		'title' => '%s',
		'description' => '%s',
		'app_class' => '%s',
		'app_type' => '%s',
		'status' => '%s',
		'sticky' => '%d',
		'html_content' => '%s',
		'css_content' => '%s',
		'js_content' => '%s',
		'json_spec' => '%s',
		'css_file_url' => '%s',
		'js_file_url' => '%s',
		'file_version' => '%s'
	);

		$data = array();
		$format = array();
		foreach ($fields as $key => $value) {
			if (array_key_exists($key, $allowed_columns)) {
				$data[$key] = $value;
				$format[] = $allowed_columns[$key];
			}
		}

		// Default status
		if (!isset($data['status'])) {
			$data['status'] = 'active';
			$format[] = '%s';
		}

		// Default sticky flag (false)
		if (!isset($data['sticky'])) {
			$data['sticky'] = 0;
			$format[] = '%d';
		}
		
		// Debug logging removed for WordPress.org submission
		// if (defined('WP_DEBUG') && WP_DEBUG) {
		// 	error_log('TalkGenAI DEBUG: About to compute security hash');
		// }

		// Compute security hash from content columns
		$final_html = (string)($data['html_content'] ?? '');
		$final_css = (string)($data['css_content'] ?? '');
		$final_js = (string)($data['js_content'] ?? '');
		$final_spec = (string)($data['json_spec'] ?? '');
		
		// Debug logging removed for WordPress.org submission
		// if (defined('WP_DEBUG') && WP_DEBUG) {
		// 	error_log('TalkGenAI DEBUG: About to hash(' . strlen($final_html . $final_css . $final_js . $final_spec) . ' bytes total)');
		// }
		
		$data['security_hash'] = hash('sha256', $final_html . $final_css . $final_js . $final_spec);
		$format[] = '%s';

		// Set timestamps explicitly for MySQL 5.5 compatibility
		$data['created_at'] = current_time('mysql');
		$format[] = '%s';
		$data['updated_at'] = current_time('mysql');
		$format[] = '%s';

		// Debug logging removed for WordPress.org submission
		// if (defined('WP_DEBUG') && WP_DEBUG) {
		// 	error_log('TalkGenAI DEBUG: Security hash computed. About to call wpdb->insert()');
		// }

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Direct query is required for database abstraction layer
		$result = $wpdb->insert($this->apps_table, $data, $format);
		
		// Debug logging removed for WordPress.org submission
		// if (defined('WP_DEBUG') && WP_DEBUG) {
		// 	error_log('TalkGenAI DEBUG: wpdb->insert() completed. Result: ' . ($result === false ? 'FALSE' : $result));
		// }
		
		if ($result === false) {
			return new WP_Error('db_insert_failed', 'Database insert failed: ' . $wpdb->last_error);
		}

		return intval($wpdb->insert_id);
	}

	/**
	 * Delete an app by ID
	 */
	public function delete_app($app_id) {
		global $wpdb;
		$app_id = intval($app_id);
		if ($app_id <= 0) {
			return new WP_Error('invalid_app_id', 'Invalid app ID');
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Direct query is required for database abstraction layer; Caching not applicable for write operations
		$result = $wpdb->delete($this->apps_table, array('id' => $app_id), array('%d'));
		if ($result === false) {
			return new WP_Error('db_delete_failed', 'Database delete failed: ' . $wpdb->last_error);
		}
		return true;
	}
}