<?php
// script is included in the ajax_hjandler.php and stream_handler.php and is called from Admin page and allows Admin to retrieve chatlog entries (HTML) for analysis
/*  assumes exists in parent script(s):
		TWXChatUtil access
		Wordpress context
	Customer is on Plus or Pro Plan
*/

class TWXChatLog
{


public static function getChatLogColumns(){
	return [
		'thread_id' => ['display'=>__('ThreadID','twx-chat'),'parseFunction'=>'parseThreadId','canFilter'=>true],
		'chatbotId' => ['display'=>__('Chatbot ID','twx-chat'),'sortable'=>true,'isProperty'=>true],
		'created_dt' => ['display'=>__('CreatedDT','twx-chat'),'sortable'=>true],
		'wpUser' => ['display'=>__('User','twx-chat'),'sortable'=>true,'isProperty'=>true,'canFilter'=>true],
		'isVoiceInput' => ['display'=>__('Voice Input?','twx-chat'),'isProperty'=>true],
        'user_query_language_code' => ['display'=>__('User Language','twx-chat'),'isProperty'=>true,'parseFunction'=>'parseUserQueryLanguageCode'],
		'userInput' => ['display'=>__('User Prompt','twx-chat'),'isProperty'=>true,'sortable'=>true],
		'botResponse' => ['display'=>__('Bot Response','twx-chat'),'isProperty'=>true],
		'sources' =>  ['display'=>__('Sources','twx-chat'),'isProperty'=>true,'parseFunction'=>'parseSources'],
        'user_fields' => ['display'=>__('User Fields','twx-chat'),'isProperty'=>true,'parseFunction'=>'parseUserFields'],
		'userHyperlinkClicks' => ['display'=>__('Hyperlink Clicks','twx-chat'),'isProperty'=>true,'parseFunction'=>'parseHyperlinkClicks'],
		'feedback' => ['display'=>__('User Feedback','twx-chat'),'isProperty'=>true],
	];
}

public static function getTableName() {

    global $wpdb;
    return $wpdb->prefix . 'twxchat_chatlog';
}

public static function getMessagesAndSourcesFromChatLog($thread_id, $system_prompt, $chatbot_greeting, $new_user_message_text, $lastN = null){

	// get user and bot messages and sources from chat thread
	// Remember! Chat Log entries are in message pairs (user msg and bot msg) so lastN will actually get back N pairs

	$msgs = [];
	$sources = [];

    if(!empty($system_prompt)){
        $msgs[] = ['role' => 'system', 'content' => $system_prompt];
    }

    $entries = TWXChatLog::getChatLogEntriesByThreadId($thread_id, $lastN);
    //TWXChatUtil::log("getMessagesAndSourcesFromChatLog:: thread_id: $thread_id, lastN: $lastN, entries: ".var_export($entries,true));
    $num_entries = empty($entries) ? 0 : count($entries);
    TWXChatUtil::log("getMessagesAndSourcesFromChatLog:: thread_id: $thread_id, lastN: $lastN, entries: $num_entries");
    if(!empty($entries)) {
        foreach($entries as $row){
            if(!isset($row['properties'])) continue;
            $propData = json_decode($row['properties'],true);
            if(isset($propData['userInput'])){
                $msgs[] = ['role' => 'user', 'content' => $propData['userInput']];
            }
            if(isset($propData['botResponse'])){
                $msgs[] = ['role' => 'assistant', 'content' => $propData['botResponse']];
            }
			if(isset($propData['sources'])){
				foreach($propData['sources'] as $source){
					$source_id = $source['source_id'];
					$sources[$source_id] = $source;
				}
			}
        }
    }

    if(!empty($new_user_message_text)){
        $msgs[] = ['role' => 'user', 'content' => $new_user_message_text];
    }

    return [$msgs, $sources];
}

public static function getUserFieldsFromChatLog($thread_id, $lastN = null){

	// get user and bot messages and sources from chat thread
	// Remember! Chat Log entries are in message pairs (user msg and bot msg) so lastN will actually get back N pairs

	$user_fields = [];

    $entries = TWXChatLog::getChatLogEntriesByThreadId($thread_id, $lastN);
    if(!empty($entries)) {
        foreach($entries as $row){
            if(!isset($row['properties'])) continue;
            $propData = json_decode($row['properties'],true);
			if(isset($propData['user_fields'])){
                $user_fields = array_merge($user_fields, $propData['user_fields']);
			}
        }
    }

    return $user_fields;
}

public static function getNumEntries(){

    global $wpdb;
    $table_name = self::getTableName();
    return $wpdb->get_var("SELECT COUNT(*) FROM $table_name");
}

public static function getChatLogEntries($period, $filters, $limit = 1000) {
    
    global $wpdb;

    $clientTZ = get_option('twx_chat_client_timezone') ?? 'UTC';
    $dbTZ = get_option('twx_chat_db_timezone') ?? 'UTC';

    $wherePlaceholders = [];
    $queryParams = [];

    $columns = TWXChatLog::getChatLogColumns();

    // User Filters (already sanitized)
    foreach ($filters as $colName => $colFilter) {
        if (empty($colFilter)) continue;

        // Only allow whitelisted column names
        if (!isset($columns[$colName])) continue;

        // Allow comma separated OR filter values
        $values = array_map('trim', explode(',', $colFilter));
        $orPlaceholders = [];

        foreach ($values as $val) {

            if(isset($columns[$colName]['isProperty']) && $columns[$colName]['isProperty'] === true){
                $orPlaceholders[] = "JSON_UNQUOTE(JSON_EXTRACT(t.properties, '\$.$colName')) LIKE %s";
            }else{
                $orPlaceholders[] = "t.$colName LIKE %s";
            }
            $queryParams[] = '%' . $wpdb->esc_like($val) . '%';
        }

        if (!empty($orPlaceholders)) {
            $wherePlaceholders[] = '(' . implode(' OR ', $orPlaceholders) . ')';
        }
    }

    // Add filtering based on the selected time period
    if ($period) {

        $clientTZObj = new DateTimeZone($clientTZ);
        $dbTZObj     = new DateTimeZone($dbTZ);

        // init start date at 12AM today
        $start_date = new DateTime('today', $clientTZObj);
        //$to_date = null;

        switch ($period) {
            case 'Today':
                // $start_date alreay set
                break;
            case '1 Day':
                $start_date->modify('-1 days');
                break;
            case '2 Days':
                $start_date->modify('-2 days');
                break;
            case '1 Week':
                $start_date->modify('-1 week');
                break;
            case '1 Month':
                $start_date->modify('-1 month');
                break;
            case '3 Months':
                $start_date->modify('-3 months');
                break;
            case 'All Time':
                $start_date->modify('-99 years');
                break;
            default:
                /*
                $splitPeriod = explode(':',$period);
                if(count($splitPeriod) === 2){
                    // from:to. Expected format: "YYYY-MM-DD:YYYY-MM-DD"
                    $from_str = trim($splitPeriod[0]);
                    $to_str   = trim($splitPeriod[1]);

                    try {
                        $start_date = new DateTime($from_str);
                    } catch (Exception $e) {
                        $start_date = new DateTime('1970-01-01');
                    }

                    try {
                        $to_date = new DateTime($to_str);
                    } catch (Exception $e) {
                        $to_date = new DateTime();
                    }

                    $start_date->setTime(0, 0, 0);
                    $to_date->setTime(23, 59, 59);

                }else{
                    $period = null;
                }
                */
                $period = null;
                break;
        }

        if ($period) {
            // Convert the start-of-period timestamp into DB timezone for accurate comparison
            $start_date->setTimezone($dbTZObj);
            $start_date_str = $start_date->format('Y-m-d H:i:s');

            // Add the date condition to the query
            $wherePlaceholders[] = 't.created_at >= %s';
            $queryParams[] = $start_date_str;

            /*
            if($to_date){
                if ($clientTZ !== $dbTZ) {
                    $to_date->setTimezone(new DateTimeZone($dbTZ));
                }
                $to_date_str = $to_date->format('Y-m-d H:i:s');
                $wherePlaceholders[] = 't.created_at <= %s';
                $queryParams[] = $to_date_str;
            }
            */
        }
    }

    $table = self::getTableName();

    // Subquery to get the maximum created_at for each thread_id
    $subquery = "
        SELECT thread_id, MAX(created_at) AS max_created_at 
        FROM $table 
        GROUP BY thread_id
    ";

    // Adjust the created_at column based on timezone difference
    $created_at_select = ($clientTZ === $dbTZ ? 't.created_at' : "IFNULL(CONVERT_TZ(t.created_at, '".$dbTZ."', '".$clientTZ."'), t.created_at)"); 

    // Main query to select all columns and join with the subquery
    $sql = "
        SELECT 
            t.thread_id, 
            t.created_at, 
            $created_at_select AS created_dt,
            TIMESTAMPDIFF(MINUTE, 
                $created_at_select,
                CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')
            ) AS minutes_ago,
            CASE
                WHEN TIMESTAMPDIFF(MINUTE, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')) < 60 
                    THEN CONCAT(TIMESTAMPDIFF(MINUTE, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' mins ago')
                WHEN TIMESTAMPDIFF(HOUR, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')) < 24 
                    THEN CONCAT(TIMESTAMPDIFF(HOUR, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' hrs ago')
                ELSE CONCAT(TIMESTAMPDIFF(DAY, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' days ago')
            END AS time_ago,
            t.properties
        FROM $table AS t
        INNER JOIN ($subquery) AS recent
        ON t.thread_id = recent.thread_id
    ";

    if (!empty($wherePlaceholders)) {
        $sql .= " WHERE " . implode(" AND ", $wherePlaceholders); // no values are in $wherePlaceholders only place holders which will be parsed by $wpdb->prepare below
    }

    $sql .= " ORDER BY recent.max_created_at DESC, t.created_at ASC LIMIT %d";
    $queryParams[] = $limit;

    $prepared_query = $wpdb->prepare($sql, ...$queryParams); // WP Plugin says WordPress.DB.PreparedSQL.NotPrepared but it is being prepared??
    //TWXChatUtil::adminLog("getChatLogEntries:: prepared_query: $prepared_query");
    
    $results = $wpdb->get_results($prepared_query, ARRAY_A); // WP Plugin says WordPress.DB.PreparedSQL.NotPrepared but it is being prepared??

    return $results;
}

public static function getChatLogEntriesByThreadId($threadId, $lastN = null, $chatbotId = false) {

    if (empty($threadId)) return [];

    global $wpdb;

    // Load timezone settings
    $clientTZ = get_option('twx_chat_client_timezone') ?? 'UTC';
    $dbTZ     = get_option('twx_chat_db_timezone') ?? 'UTC';

    $table_name = self::getTableName();

    // Adjusted created_at column with timezone conversion
    $created_at_select = ($clientTZ === $dbTZ
        ? 'created_at'
        : "IFNULL(CONVERT_TZ(created_at, '".$dbTZ."', '".$clientTZ."'), created_at)"
    );

    // Add the "time ago" and "minutes ago" expressions
    $extra_columns = "
        , TIMESTAMPDIFF(MINUTE, 
            $created_at_select,
            CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')
        ) AS minutes_ago
        , CASE
            WHEN TIMESTAMPDIFF(MINUTE, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')) < 60 
                THEN CONCAT(TIMESTAMPDIFF(MINUTE, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' mins ago')
            WHEN TIMESTAMPDIFF(HOUR, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')) < 24 
                THEN CONCAT(TIMESTAMPDIFF(HOUR, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' hrs ago')
            ELSE CONCAT(TIMESTAMPDIFF(DAY, $created_at_select, CONVERT_TZ(NOW(), '".$dbTZ."', '".$clientTZ."')), ' days ago')
        END AS time_ago
    ";

    // Base SQL
    $sql = "SELECT *, $created_at_select AS created_dt $extra_columns FROM $table_name WHERE thread_id = %s";
    $params = [$threadId];

    // Limit to last N records (preserving ASC order)
    //if($lastN === null) $lastN = 100; causing issues
    if ($lastN !== null) {
        $lastN = (int) $lastN;

        $sql = "
            SELECT sub.* FROM (
                SELECT 
                    *, 
                    $created_at_select AS created_dt
                    $extra_columns
                FROM $table_name
                WHERE thread_id = %s
                ORDER BY id DESC 
                LIMIT %d
            ) AS sub
            ORDER BY id ASC
        ";
        $params = [$threadId, $lastN];
    } else {
        $sql .= " ORDER BY id ASC";
    }

    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    $prepared_query = $wpdb->prepare($sql, ...$params);
    //TWXChatUtil::adminLog("getChatLogEntriesByThreadId:: prepared_query: $prepared_query");

    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    $results = $wpdb->get_results($prepared_query, ARRAY_A);

    // Validate chatbot ID if supplied
    if ($chatbotId) {
        foreach ($results as $row) {
            if (!isset($row['properties'])) continue;
            $propData = json_decode($row['properties'], true);
            if (isset($propData['chatbotId']) && $propData['chatbotId'] !== $chatbotId) {
                return []; // invalid chat log entries for given chatbotID
            }
        }
    }

    return $results;
}

public static function insertChatLog($threadId, $logData){
	
    global $wpdb;

    try {
        // Prepare data for insertion
        $table_name = self::getTableName();
        $data = [
            'thread_id' => $threadId,
            'properties' => json_encode($logData),
        ];

        // Insert chat log into the database
        $result = $wpdb->insert($table_name, $data);

        // Check if the insertion was successful
        if ($result === false) {
            throw new Exception("Failed to insert chat log entry.");
        }


    } catch (Exception $ex) {
        // Log the error message
    }
}

public static function updateChatLogProperties($id, $properties) {

    global $wpdb;

    // Define the table name with the prefix
    $table_name = self::getTableName();

    // Prepare the data for update
    $data = [
        'properties' => $properties
    ];

    // Prepare the where clause
    $where = [
        'id' => $id
    ];

    // Update the record
    $result = $wpdb->update($table_name, $data, $where);

    if ($result === false) {
        // Log the error if the update failed
        return false; // Failure
    }

    return true; // Success
}

public static function sortEntries(&$entries, $fieldIdx, $sortOrder, $cols){
	
	$colKeys = array_keys($cols);
	if($fieldIdx < 0 || $fieldIdx > count($cols)-1) return;
	$field = $colKeys[$fieldIdx];
	
	if(!isset($cols[$field]['sortable'])) return;
	if($cols[$field]['sortable'] !== true) return;
	$colData = $cols[$field];

	usort($entries, function($a, $b) use($field, $colData, $sortOrder) {
		
		if(isset($colData['isProperty'])){
			$propDataA = json_decode($a['properties'],true);
			$propDataB = json_decode($b['properties'],true);
			
			if(isset($propDataA[$field]) && isset($propDataB[$field])) {
				if($sortOrder == 'ASC') return strcmp($propDataA[$field], $propDataB[$field]);
				return strcmp($propDataB[$field], $propDataA[$field]);
			}
			return 0;
		}else{
			if($sortOrder == 'ASC') return strcmp($a[$field], $b[$field]);
			return strcmp($b[$field], $a[$field]);
		}

	});
	
}

public static function hasRecentDuplicate($thread_id, $properties, $within_seconds = 30) {

    global $wpdb;

    $table_name = self::getTableName();

    if (is_array($properties)) {
        $properties = wp_json_encode($properties);
    }

    $properties = trim((string)$properties);

    $sql = $wpdb->prepare(
        "
        SELECT COUNT(*)
        FROM {$table_name}
        WHERE thread_id = %s
        AND properties = %s
        AND created_at >= DATE_SUB(NOW(), INTERVAL %d SECOND)
        ",
        $thread_id,
        $properties,
        (int)$within_seconds
    );

    return ((int)$wpdb->get_var($sql) > 0);
}

public static function cleanupDuplicateEntries($dry_run = false) {

    global $wpdb;

    $table_name = self::getTableName();

    $duplicate_groups = $wpdb->get_results(
        "
        SELECT 
            thread_id,
            properties,
            COUNT(*) AS cnt,
            MIN(id) AS keep_id
        FROM {$table_name}
        GROUP BY thread_id, properties
        HAVING COUNT(*) > 1
        ",
        ARRAY_A
    );

    $deleted_count = 0;
    $groups_processed = 0;

    foreach ((array)$duplicate_groups as $group) {

        $groups_processed++;

        $ids_to_delete = $wpdb->get_col(
            $wpdb->prepare(
                "
                SELECT id
                FROM {$table_name}
                WHERE thread_id = %s
                AND properties = %s
                AND id <> %d
                ORDER BY id ASC
                ",
                $group['thread_id'],
                $group['properties'],
                (int)$group['keep_id']
            )
        );

        foreach ((array)$ids_to_delete as $delete_id) {

            if (!$dry_run) {
                $wpdb->delete(
                    $table_name,
                    ['id' => (int)$delete_id],
                    ['%d']
                );
            }

            $deleted_count++;
        }
    }

    $ret = [
        'groups_processed' => $groups_processed,
        'deleted_count'    => $deleted_count,
        'dry_run'          => $dry_run,
        'duplicate_groups' => $duplicate_groups,
    ];

    return $ret;
}


}