<?php
namespace AIZLabs\ChatAgent;

use DateTime;
use DateTimeZone;

class DBHandler {

    private $wpdb;
    private $table_article;
    private $table_agent;
    private $table_function;
    private $table_conversation;
    private $table_global;
    public function __construct() {
        global $wpdb;
        $this->wpdb = $wpdb;
        $this->table_article = Config::get_table_name('article');
        $this->table_agent = Config::get_table_name('agent');
        $this->table_function = Config::get_table_name('function');
        $this->table_conversation = Config::get_table_name('conversation');
        $this->table_global = Config::get_table_name('global');
    }

    public function get_articles() {
        $table = esc_sql($this->table_article);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        $articles = $this->wpdb->get_results("SELECT id, file_name,created_time FROM `{$table}` ORDER BY created_time DESC");

        // Add in_use flag to each article
        foreach ($articles as $article) {
            $article->in_use = $this->is_article_in_use($article->id);
        }

        return $articles;
    }

    public function get_article_by_id($id) {
        $table = esc_sql($this->table_article);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM `{$table}` WHERE id = %d", $id));
    }

    public function get_vector_store_id_by_article_id($id) {
        $table = esc_sql($this->table_article);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_var($this->wpdb->prepare("SELECT vector_store_id FROM `{$table}` WHERE id = %d", $id));
    }

    public function get_file_id_by_article_id($id) {
        $table = esc_sql($this->table_article);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_var($this->wpdb->prepare("SELECT file_id FROM `{$table}` WHERE id = %d", $id));
    }

    public function is_article_in_use($article_id) {
        $table = esc_sql($this->table_agent);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        $agents = $this->wpdb->get_results("SELECT article_ids FROM `{$table}`");

        // Ensure type consistency - convert to integer for comparison
        $article_id = intval($article_id);

        foreach ($agents as $agent) {
            $article_ids = json_decode($agent->article_ids, true);
            if ($article_ids && in_array($article_id, $article_ids, true)) {
                return true;
            }
        }

        return false;
    }

    public function get_functions() {
        $table = esc_sql($this->table_function);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return $this->wpdb->get_results("SELECT id, name, description, dependency_table, dependency_plugin FROM `{$table}`");
    }

    public function get_available_functions() {
        $functions = $this->get_functions();
        $available_functions = [];
        
        foreach ($functions as $function) {
            if ($this->is_function_available($function)) {
                $available_functions[] = $function;
            }
        }
        
        return $available_functions;
    }

    public function is_function_available($function) {
        if (empty($function->dependency_table)) {
            return true;
        }

        $dependency_table = $this->wpdb->prefix . $function->dependency_table;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- Using prepare() with placeholder
        $table_exists = $this->wpdb->get_var($this->wpdb->prepare("SHOW TABLES LIKE %s", $dependency_table));

        return $table_exists === $dependency_table;
    }

    public function get_function_by_id($id) {
        $table = esc_sql($this->table_function);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_row($this->wpdb->prepare("SELECT id, name, description, definition, dependency_table, dependency_plugin FROM `{$table}` WHERE id = %d", $id));
    }

    public function get_agents() {
        $table = esc_sql($this->table_agent);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return $this->wpdb->get_results("SELECT id, name, scope, greeting_message, function_ids FROM `{$table}` ORDER BY id DESC");
    }

    public function get_agents_header() {
        $table = esc_sql($this->table_global);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return $this->wpdb->get_var("SELECT agents_header FROM `{$table}` LIMIT 1");
    }

    public function get_agent_by_id($id) {
        $table = esc_sql($this->table_agent);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM `{$table}` WHERE id = %d", $id));
    }

    public function get_conversation_by_id($id) {
        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_row($this->wpdb->prepare("SELECT * FROM `{$table}` WHERE id = %d", $id));
    }

    public function update_conversation_source($id, $source) {
        $table = esc_sql($this->table_conversation);
        return $this->wpdb->update(
            $table,
            ['source' => $source],
            ['id' => $id],
            ['%s'],
            ['%d']
        );
    }

    public function get_latest_response_id($agent_id, $session_id) {
        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        $result = $this->wpdb->get_row($this->wpdb->prepare("SELECT response_id, response_time FROM `{$table}` WHERE agent_id = %d AND session_id = %s ORDER BY response_time DESC LIMIT 1", $agent_id, $session_id));

        #reset conversation state if it's older than 5 minutes (AIZL_CHAT_HISTORY_RANGE)
        if ($result) {
            $conversation_pause = time() - strtotime($result->response_time);
            //error_log('conversation_pause: ' . $conversation_pause);

            if ($conversation_pause >= AIZL_CHAT_HISTORY_RANGE || $result->response_id == null) {
                return null;
            } else {
                return $result->response_id;
            }
        }
        
        return null;
    }

    #get chat history within 5 minutes interval (AIZL_CHAT_AGENT_RANGE), longer paused message will be excluded
    public function get_chat_history($agent_id, $session_id) {
        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        $results = $this->wpdb->get_results($this->wpdb->prepare("SELECT content, content_time, response, response_time FROM `{$table}` WHERE agent_id = %d AND session_id = %s ORDER BY response_time DESC", $agent_id, $session_id));
        //return array_reverse($results);
        
        //reserved for future use
        $filtered = [];
        $prev_time = strtotime(gmdate('Y-m-d H:i:s'));
        foreach ($results as $row) {
            $current_time = strtotime($row->response_time);
            if ($prev_time !== null) {
                $interval = abs($prev_time - $current_time);
                if ($interval >= AIZL_CHAT_HISTORY_RANGE) {
                    break;
                }
            }
            $filtered[] = $row;
            $prev_time = $current_time;
        }
        // in ASC order
        return array_reverse($filtered);
         //reserved for future use
    }


    public function get_global_setting() {
        $table = esc_sql($this->table_global);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return $this->wpdb->get_row("SELECT * FROM `{$table}` LIMIT 1");
    }

    public function update_global_setting($global_setting) {
        $global_setting_array = (array) $global_setting;
        $this->wpdb->update($this->table_global, $global_setting_array, array('id' => $global_setting->id));
    }

    public function insert_global_setting($global_setting) {
        $global_setting_array = (array) $global_setting;
        $this->wpdb->insert($this->table_global, $global_setting_array);
    }

    public function get_access_key() {
        $table = esc_sql($this->table_global);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return $this->wpdb->get_var("SELECT access_key FROM `{$table}` LIMIT 1");
    }

    // Dashboard KPI methods
    public function get_total_agents() {
        $table = esc_sql($this->table_agent);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return (int) $this->wpdb->get_var("SELECT COUNT(id) FROM `{$table}`");
    }
    public function get_published_agents() {
        $table = esc_sql($this->table_agent);
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is escaped with esc_sql()
        return (int) $this->wpdb->get_var("SELECT COUNT(id) FROM `{$table}` WHERE scope != ''");
    }
    public function get_unique_users($timezone, $from, $to) {
        $site_timezone = $timezone;
        if ($from) {
            $from_dt = new DateTime($from, new DateTimeZone($site_timezone));
            $from_dt->setTimezone(new DateTimeZone('UTC'));
            $from_utc = $from_dt->format('Y-m-d H:i:s');
        }
        if ($to) {
            $to_dt = new DateTime($to, new DateTimeZone($site_timezone));
            $to_dt->setTimezone(new DateTimeZone('UTC'));
            $to_utc = $to_dt->format('Y-m-d H:i:s');
        }
        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return (int) $this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(DISTINCT session_id) FROM `{$table}` WHERE response_time >= %s AND response_time <= %s AND source != 'system'", $from_utc, $to_utc));
    }
    public function get_total_interactions($timezone, $from, $to) {
        $site_timezone = $timezone;
        if ($from) {
            $from_dt = new DateTime($from, new DateTimeZone($site_timezone));
            $from_dt->setTimezone(new DateTimeZone('UTC'));
            $from_utc = $from_dt->format('Y-m-d H:i:s');
        }
        if ($to) {
            $to_dt = new DateTime($to, new DateTimeZone($site_timezone));
            $to_dt->setTimezone(new DateTimeZone('UTC'));
            $to_utc = $to_dt->format('Y-m-d H:i:s');
        }
        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return (int) $this->wpdb->get_var($this->wpdb->prepare("SELECT COUNT(id) FROM `{$table}` WHERE response_time >= %s AND response_time <= %s AND source != 'system'", $from_utc, $to_utc));
    }


    public function get_timeseries_data($timezone, $from, $to, $bucket) {
        // Validate bucket parameter to prevent SQL injection
        $valid_buckets = ['hour', 'year', 'month', 'week', 'day'];
        if (!in_array($bucket, $valid_buckets, true)) {
            $bucket = 'day'; // Default to day if invalid
        }

        // Convert timezone to UTC offset string
        $timezone_obj = new DateTimeZone($timezone);
        $timezone_offset = $timezone_obj->getOffset(new DateTime()) / 3600;
        $tz_offset_str = ($timezone_offset >= 0 ? '+' : '-') .
            sprintf("%02d:%02d", abs(floor($timezone_offset)), abs(($timezone_offset - floor($timezone_offset)) * 60));

        // Convert dates to UTC
        $from_utc = null;
        $to_utc = null;

        if ($from) {
            $from_dt = new DateTime($from, new DateTimeZone($timezone));
            $from_dt->setTimezone(new DateTimeZone('UTC'));
            $from_utc = $from_dt->format('Y-m-d H:i:s');
        }
        if ($to) {
            $to_dt = new DateTime($to, new DateTimeZone($timezone));
            $to_dt->setTimezone(new DateTimeZone('UTC'));
            $to_utc = $to_dt->format('Y-m-d H:i:s');
        }

        // Get results by delegating to bucket-specific method
        return $this->get_timeseries_results_by_bucket($bucket, $tz_offset_str, $from_utc, $to_utc);
    }

    /**
     * Execute timeseries query based on bucket type
     *
     * @param string $bucket The time bucket (hour, year, month, week, day)
     * @param string $tz_offset_str Timezone offset string
     * @param string|null $from_utc Start date in UTC
     * @param string|null $to_utc End date in UTC
     * @return array Results data
     */
    private function get_timeseries_results_by_bucket($bucket, $tz_offset_str, $from_utc, $to_utc) {
        // Define the SQL templates with proper escaping for date format percent signs
        $sql_templates = [
            'hour'  => "SELECT DATE_FORMAT(CONVERT_TZ(response_time, '+00:00', %s), '%%Y-%%m-%%d %%H:00') as label, COUNT(id) as request_count, COUNT(DISTINCT session_id) as unique_users FROM %i WHERE response_time >= %s AND response_time <= %s AND source != %s GROUP BY label ORDER BY label ASC",
            'year'  => "SELECT DATE_FORMAT(CONVERT_TZ(response_time, '+00:00', %s), '%%Y') as label, COUNT(id) as request_count, COUNT(DISTINCT session_id) as unique_users FROM %i WHERE response_time >= %s AND response_time <= %s AND source != %s GROUP BY label ORDER BY label ASC",
            'month' => "SELECT DATE_FORMAT(CONVERT_TZ(response_time, '+00:00', %s), '%%Y-%%m') as label, COUNT(id) as request_count, COUNT(DISTINCT session_id) as unique_users FROM %i WHERE response_time >= %s AND response_time <= %s AND source != %s GROUP BY label ORDER BY label ASC",
            'week'  => "SELECT DATE_FORMAT(CONVERT_TZ(response_time, '+00:00', %s), '%%x-W%%v') as label, COUNT(id) as request_count, COUNT(DISTINCT session_id) as unique_users FROM %i WHERE response_time >= %s AND response_time <= %s AND source != %s GROUP BY label ORDER BY label ASC",
            'day'   => "SELECT DATE_FORMAT(CONVERT_TZ(response_time, '+00:00', %s), '%%Y-%%m-%%d') as label, COUNT(id) as request_count, COUNT(DISTINCT session_id) as unique_users FROM %i WHERE response_time >= %s AND response_time <= %s AND source != %s GROUP BY label ORDER BY label ASC",
        ];

        // Choose the template, defaulting to 'day'
        $sql_template = $sql_templates[$bucket] ?? $sql_templates['day'];

        // Prepare the ENTIRE query in one step
        $prepared_sql = $this->wpdb->prepare(
            $sql_template,
            $tz_offset_str,
            $this->table_conversation,
            $from_utc,
            $to_utc,
            'system'
        );

        // Execute the query
        return $this->wpdb->get_results($prepared_sql);
    }


    public function get_conversations($agent_id, $timezone, $from, $to) {

        $site_timezone = $timezone;
        if ($from) {
            $from_dt = new DateTime($from, new DateTimeZone($site_timezone));
            $from_dt->setTimezone(new DateTimeZone('UTC'));
            $from_utc = $from_dt->format('Y-m-d H:i:s');
        }
        if ($to) {
            $to_dt = new DateTime($to, new DateTimeZone($site_timezone));
            $to_dt->setTimezone(new DateTimeZone('UTC'));
            $to_utc = $to_dt->format('Y-m-d H:i:s');
        }

        $table = esc_sql($this->table_conversation);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        $results = $this->wpdb->get_results($this->wpdb->prepare("SELECT id, session_id, content, content_time, response, response_id, response_time, source, score FROM `{$table}` WHERE agent_id = %d AND source != 'system' AND response_time >= %s AND response_time <= %s ORDER BY id DESC", $agent_id, $from_utc, $to_utc));
        //convert response_time to local time
        foreach ($results as $result) {

            //remove escape character from content and response display
            $result->content = wp_kses_post(str_replace('\\', '', $result->content));
            $result->response = wp_kses_post(str_replace('\\', '', $result->response));

            $result->content_time = wp_date('H:i:s Y-m-d', strtotime($result->content_time));
            $result->response_time = wp_date('H:i:s Y-m-d', strtotime($result->response_time));

            $content_time = strtotime($result->content_time);
            $response_time = strtotime($result->response_time);
            if ($content_time && $response_time) {
                $result->processing_time = abs($response_time - $content_time);
            } else {
                $result->processing_time = 0;
            }

            $result->session_id = strlen($result->session_id) > 15 ? substr(htmlspecialchars($result->session_id), 0, 15) . '...' : htmlspecialchars($result->session_id);
        }

        return $results;
    }

    public function get_function_tool($function_id) {
        $table = esc_sql($this->table_function);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Using prepare() with escaped table name
        return $this->wpdb->get_row($this->wpdb->prepare("SELECT id, name, description, definition, dependency_table, dependency_plugin FROM `{$table}` WHERE id = %d", $function_id));
    }


}