<?php
/**
 * AEO BOB AI Database Handler - Enhanced with LLM Data Storage
 */

if (!defined('ABSPATH')) {
    exit;
}

class AEO_Bob_Database {

    private static $instance = null;

    public static function get_instance() {
        if (null === self::$instance) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    private function __construct() {
        add_action('init', array($this, 'init'));
    }

    public function init() {
        $this->create_tables();
    }

    /**
     * Get whitelisted table name
     * Security: Returns table name ONLY from strict internal allowlist
     * No user input is used in table name construction
     *
     * @param string $key Table key (seo_analysis, llm_events, optimizations)
     * @return string Full table name with prefix, or empty string if invalid
     */
    private function get_table_name($key) {
        global $wpdb;

        // Strict internal allowlist - no user input
        $tables = array(
            'seo_analysis' => $wpdb->prefix . 'aeo_bob_seo_analysis',
            'llm_events' => $wpdb->prefix . 'aeo_bob_llm_events',
            'optimizations' => $wpdb->prefix . 'aeo_bob_optimizations',
        );

        // Invalid key - return empty string for safety
        if (!isset($tables[$key])) {
            return '';
        }

        return $tables[$key];
    }

    public function create_tables() {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        // SEO Analysis table
        $seo_table = $this->get_table_name('seo_analysis');
        $seo_sql = "CREATE TABLE {$seo_table} (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            target_keyword varchar(255) DEFAULT '',
            seo_score int(3) DEFAULT 0,
            analysis_data longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY post_id (post_id),
            KEY target_keyword (target_keyword),
            KEY seo_score (seo_score)
        ) $charset_collate;";

        // LLM Discovery Events table
        $llm_events_table = $this->get_table_name('llm_events');
        $llm_events_sql = "CREATE TABLE {$llm_events_table} (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            event_type varchar(50) NOT NULL,
            post_id bigint(20) DEFAULT 0,
            event_data longtext,
            user_agent varchar(255) DEFAULT '',
            ip_address varchar(45) DEFAULT '',
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY event_type (event_type),
            KEY post_id (post_id),
            KEY created_at (created_at)
        ) $charset_collate;";

        // AI Optimization History table
        $optimization_table = $this->get_table_name('optimizations');
        $optimization_sql = "CREATE TABLE {$optimization_table} (
            id mediumint(9) NOT NULL AUTO_INCREMENT,
            post_id bigint(20) NOT NULL,
            optimization_type varchar(50) NOT NULL,
            before_score int(3) DEFAULT 0,
            after_score int(3) DEFAULT 0,
            suggestions_applied longtext,
            optimization_data longtext,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY post_id (post_id),
            KEY optimization_type (optimization_type),
            KEY created_at (created_at)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');

        dbDelta($seo_sql);
        dbDelta($llm_events_sql);
        dbDelta($optimization_sql);

        // Update database version
        update_option('aeo_bob_db_version', '3.0.0');
    }

    // SEO Analysis methods
    public function save_seo_analysis($post_id, $target_keyword, $seo_score, $analysis_data) {
        global $wpdb;

        // Sanitize inputs
        $post_id = absint($post_id);
        $target_keyword = sanitize_text_field($target_keyword);
        $seo_score = absint($seo_score);

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_seo_analysis');

        // Check for existing record - inline prepare
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name is safely escaped with esc_sql()
        $existing = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id FROM {$table} WHERE post_id = %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $post_id
            )
        );

        $data = array(
            'post_id' => $post_id,
            'target_keyword' => $target_keyword,
            'seo_score' => $seo_score,
            'analysis_data' => wp_json_encode($analysis_data)
        );

        if ($existing) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cache invalidated below
            $wpdb->update($table, $data, array('post_id' => $post_id));
        } else {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cache invalidated below
            $wpdb->insert($table, $data);
        }

        // Invalidate cache after write
        wp_cache_delete('aeo_bob_seo_' . $post_id, 'aeo_bob_lite');

        return $wpdb->insert_id ?: $existing->id;
    }

    public function get_seo_analysis($post_id) {
        global $wpdb;

        // Sanitize input
        $post_id = absint($post_id);

        // Check cache first (5 minute TTL for editor UI performance)
        $cache_key = 'aeo_bob_seo_' . $post_id;
        $cached = wp_cache_get($cache_key, 'aeo_bob_lite');
        if (false !== $cached) {
            return $cached;
        }

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_seo_analysis');

        // Inline prepare directly in get_row
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table name escaped with esc_sql(), result cached below
        $result = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE post_id = %d ORDER BY updated_at DESC LIMIT 1", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $post_id
            )
        );

        if ($result && $result->analysis_data) {
            $result->analysis_data = json_decode($result->analysis_data, true);
        }

        // Cache for 5 minutes
        wp_cache_set($cache_key, $result, 'aeo_bob_lite', 300);

        return $result;
    }

    // LLM Events methods
    public function log_llm_event($event_type, $post_id = 0, $event_data = array()) {
        global $wpdb;

        // Sanitize inputs
        $event_type = sanitize_text_field($event_type);
        $post_id = absint($post_id);

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_llm_events');

        $user_agent = isset($_SERVER['HTTP_USER_AGENT']) ? sanitize_text_field(wp_unslash($_SERVER['HTTP_USER_AGENT'])) : '';

        $data = array(
            'event_type' => $event_type,
            'post_id' => $post_id,
            'event_data' => json_encode($event_data),
            'user_agent' => $user_agent,
            'ip_address' => $this->get_client_ip()
        );

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Event logging, caching not needed
        return $wpdb->insert($table, $data);
    }

    public function get_llm_events($limit = 50, $event_type = null) {
        global $wpdb;

        // Sanitize inputs
        $limit = absint($limit);
        if ($event_type) {
            $event_type = sanitize_text_field($event_type);
        }

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_llm_events');

        if ($event_type) {
            // Inline prepare directly in get_results
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name escaped with esc_sql()
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table} WHERE event_type = %s ORDER BY created_at DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                    $event_type,
                    $limit
                )
            );
        } else {
            // Inline prepare directly in get_results
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table name escaped with esc_sql()
            $results = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                    $limit
                )
            );
        }

        foreach ($results as $result) {
            if ($result->event_data) {
                $result->event_data = json_decode($result->event_data, true);
            }
        }

        return $results;
    }

    // Optimization History methods
    public function save_optimization($post_id, $type, $before_score, $after_score, $suggestions, $data = array()) {
        global $wpdb;

        // Sanitize inputs
        $post_id = absint($post_id);
        $type = sanitize_text_field($type);
        $before_score = absint($before_score);
        $after_score = absint($after_score);

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_optimizations');

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Cache invalidated below
        $result = $wpdb->insert($table, array(
            'post_id' => $post_id,
            'optimization_type' => $type,
            'before_score' => $before_score,
            'after_score' => $after_score,
            'suggestions_applied' => json_encode($suggestions),
            'optimization_data' => json_encode($data)
        ));

        // Invalidate cache after write (clear common limit variations)
        wp_cache_delete('aeo_bob_opt_history_' . $post_id . '_5', 'aeo_bob_lite');
        wp_cache_delete('aeo_bob_opt_history_' . $post_id . '_10', 'aeo_bob_lite');
        wp_cache_delete('aeo_bob_opt_history_' . $post_id . '_50', 'aeo_bob_lite');

        return $result;
    }

    public function get_optimization_history($post_id, $limit = 10) {
        global $wpdb;

        // Sanitize inputs
        $post_id = absint($post_id);
        $limit = absint($limit);

        // Check cache first (5 minute TTL)
        $cache_key = 'aeo_bob_opt_history_' . $post_id . '_' . $limit;
        $cached = wp_cache_get($cache_key, 'aeo_bob_lite');
        if (false !== $cached) {
            return $cached;
        }

        // Build table name with esc_sql
        $table = esc_sql($wpdb->prefix . 'aeo_bob_optimizations');

        // Inline prepare directly in get_results
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table name escaped with esc_sql(), result cached below
        $results = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE post_id = %d ORDER BY created_at DESC LIMIT %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $post_id,
                $limit
            )
        );

        foreach ($results as $result) {
            if ($result->suggestions_applied) {
                $result->suggestions_applied = json_decode($result->suggestions_applied, true);
            }
            if ($result->optimization_data) {
                $result->optimization_data = json_decode($result->optimization_data, true);
            }
        }

        // Cache for 5 minutes
        wp_cache_set($cache_key, $results, 'aeo_bob_lite', 300);

        return $results;
    }

    // Analytics methods
    public function get_analytics_data($days = 30) {
        global $wpdb;

        // Sanitize input
        $days = absint($days);

        // Check cache first (5 minute TTL)
        $cache_key = 'aeo_bob_analytics_' . $days;
        $cached = wp_cache_get($cache_key, 'aeo_bob_lite');
        if (false !== $cached) {
            return $cached;
        }

        // Build table names with esc_sql
        $seo_table = esc_sql($wpdb->prefix . 'aeo_bob_seo_analysis');
        $events_table = esc_sql($wpdb->prefix . 'aeo_bob_llm_events');
        $optimizations_table = esc_sql($wpdb->prefix . 'aeo_bob_optimizations');

        $date_limit = sanitize_text_field(gmdate('Y-m-d H:i:s', time() - ($days * 24 * 3600)));

        // Get average SEO scores - inline prepare
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table name escaped with esc_sql(), result cached below
        $avg_scores = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT DATE(updated_at) as date, AVG(seo_score) as avg_score, COUNT(*) as count FROM {$seo_table} WHERE updated_at >= %s GROUP BY DATE(updated_at) ORDER BY date DESC", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $date_limit
            )
        );

        // Get LLM events by type - inline prepare
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table name escaped with esc_sql(), result cached below
        $llm_events = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT event_type, COUNT(*) as count FROM {$events_table} WHERE created_at >= %s GROUP BY event_type", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $date_limit
            )
        );

        // Get optimization improvements - inline prepare
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Table name escaped with esc_sql(), result cached below
        $improvements = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT AVG(after_score - before_score) as avg_improvement, COUNT(*) as count FROM {$optimizations_table} WHERE created_at >= %s", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name escaped with esc_sql()
                $date_limit
            )
        );

        $result = array(
            'avg_scores' => $avg_scores,
            'llm_events' => $llm_events,
            'improvements' => $improvements,
            'period_days' => $days
        );

        // Cache for 5 minutes
        wp_cache_set($cache_key, $result, 'aeo_bob_lite', 300);

        return $result;
    }

    private function get_client_ip() {
        $ip_keys = array('HTTP_CLIENT_IP', 'HTTP_X_FORWARDED_FOR', 'REMOTE_ADDR');

        foreach ($ip_keys as $key) {
            if (array_key_exists($key, $_SERVER) === true) {
                foreach (explode(',', sanitize_text_field(wp_unslash($_SERVER[$key]))) as $ip) {
                    $ip = trim($ip);
                    if (filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_NO_PRIV_RANGE | FILTER_FLAG_NO_RES_RANGE) !== false) {
                        return $ip;
                    }
                }
            }
        }

        return isset($_SERVER['REMOTE_ADDR']) ? sanitize_text_field(wp_unslash($_SERVER['REMOTE_ADDR'])) : '0.0.0.0';
    }
}