<?php

/**
* Class MeowPro_MWAI_Statistics
*
* Main statistics manager. Handles database logic, logging, querying stats, shortcodes, etc.
*/
class MeowPro_MWAI_Statistics {
  #region Properties

  private $core = null;
  private $wpdb = null;
  private $db_check = false;
  private $table_logs = null;
  private $table_logmeta = null;

  #endregion

  #region Constructor

  public function __construct() {
    global $wpdb, $mwai_core;
    $this->core = $mwai_core;
    $this->wpdb = $wpdb;
    $this->table_logs = $wpdb->prefix . 'mwai_logs';
    $this->table_logmeta = $wpdb->prefix . 'mwai_logmeta';

    // Filters
    add_filter( 'mwai_stats_query', [ $this, 'stats_query' ], 10, 1 );
    add_filter( 'mwai_stats_logs_list', [ $this, 'stats_logs_list' ], 10, 5 );
    add_filter( 'mwai_stats_logs_delete', [ $this, 'stats_logs_delete' ], 10, 2 );
    add_filter( 'mwai_stats_logs_meta', [ $this, 'stats_logs_meta' ], 10, 5 );
    add_filter( 'mwai_stats_logs_activity', [ $this, 'stats_logs_activity' ], 10, 1 );
    add_filter( 'mwai_stats_logs_activity_daily', [ $this, 'stats_logs_activity_daily' ], 10, 2 );
    add_filter( 'mwai_stats_logs_activity_daily_by_model', [ $this, 'stats_logs_activity_daily_by_model' ], 10, 2 );

    // Shortcodes
    add_shortcode( 'mwai_stats_current', [ $this, 'shortcode_current' ] );
    add_shortcode( 'mwai_stats', [ $this, 'shortcode_current' ] );

    // For backward-compat: log every AI reply
    add_filter( 'mwai_ai_reply', function ( $reply, $query ) {
      global $mwai_stats;
      $mwai_stats->commit_stats_from_query( $query, $reply, [] );
      return $reply;
    }, 10, 2 );

    // Session cookie is now set lazily when get_session_id() is called
    // This happens when the user first interacts with the chatbot

    // Limits
    $limits = $this->core->get_option( 'limits' );
    if ( isset( $limits['enabled'] ) && $limits['enabled'] ) {
      add_filter( 'mwai_ai_allowed', [ $this, 'check_limits' ], 10, 3 );
    }
  }

  #endregion

  #region Public API

  /**
  * Commits a stats object into the database (inserting or updating if refId is found).
  *
  * @param MeowPro_MWAI_Stats $statsObject
  * @return bool True if success, false if error.
  */
  public function commit_stats( MeowPro_MWAI_Stats $statsObject ): bool {
    $this->check_db();

    // 1. Check if refId is set and if an entry already exists for that refId.
    $existingId = null;
    if ( !empty( $statsObject->refId ) ) {
      $sql = $this->wpdb->prepare(
        "SELECT id FROM {$this->table_logs} WHERE refId = %s LIMIT 1",
        $statsObject->refId
      );
      $existingId = $this->wpdb->get_var( $sql );
    }

    // 2. Prepare data
    $data = [
      'refId' => $statsObject->refId,
      'session' => $statsObject->session,
      'feature' => $statsObject->feature,
      'model' => $statsObject->model,
      'envId' => $statsObject->envId,
      'units' => $statsObject->units,
      'type' => $statsObject->type,
      'price' => $statsObject->price,
      'scope' => $statsObject->scope,
      'accuracy' => $statsObject->accuracy,
      // Store the stats array as JSON if not empty
      'stats' => !empty( $statsObject->stats ) ? wp_json_encode( $statsObject->stats ) : null,
    ];
    $data = $this->validate_data( $data );

    // 3. If refId found, update; else insert new row.
    if ( $existingId ) {
      $res = $this->wpdb->update(
        $this->table_logs,
        $data,
        [ 'id' => $existingId ]
      );
      if ( $res === false ) {
        Meow_MWAI_Logging::error( 'Error while updating logs (' . $this->wpdb->last_error . ')' );
        return false;
      }
      $logId = $existingId;
    }
    else {
      $logId = $this->add_log( $data );
      if ( !$logId ) {
        return false;
      }
    }

    // 4. Handle metadata
    if ( !empty( $statsObject->metadata ) && $this->core->get_option( 'statistics_data' ) ) {
      foreach ( $statsObject->metadata as $metaKey => $metaValue ) {
        $this->add_metadata(
          $logId,
          $metaKey,
          is_array( $metaValue ) ? json_encode( $metaValue ) : $metaValue
        );
      }
    }

    return true;
  }

  /**
  * For backward compatibility: commits stats from a query/reply pair.
  *
  * @param Meow_MWAI_Query_Base $query
  * @param Meow_MWAI_Reply      $reply
  * @param array                $overrides
  */
  public function commit_stats_from_query( $query, $reply, array $overrides ): void {
    $type = null;
    $units = 0;

    if (
      is_a( $query, 'Meow_MWAI_Query_Text' ) ||
        is_a( $query, 'Meow_MWAI_Query_Embed' ) ||
          is_a( $query, 'Meow_MWAI_Query_Assistant' ) ||
            is_a( $query, 'Meow_MWAI_Query_Feedback' )
    ) {
      $type = 'tokens';
      $units = $reply->get_units();
    }
    elseif ( is_a( $query, 'Meow_MWAI_Query_Image' ) ) {
      $type = 'images';
      $units = $reply->get_units();
    }
    elseif ( is_a( $query, 'Meow_MWAI_QueryTranscribe' ) ) {
      $type = 'seconds';
      $units = $reply->get_units();
    }
    else {
      return;
    }

    $statsObject = new MeowPro_MWAI_Stats();
    $statsObject->session = $query->session;
    $statsObject->scope = $query->scope;
    $statsObject->feature = $query->feature;
    $statsObject->model = $query->model;
    $statsObject->envId = $query->envId;
    $statsObject->units = $units;
    $statsObject->type = $type;
    $statsObject->accuracy = $reply->get_usage_accuracy();

    // Overriding or setting a refId
    if ( isset( $overrides['refId'] ) ) {
      $statsObject->refId = $overrides['refId'];
    }

    // Price
    if ( empty( $overrides['price'] ) ) {
      $engine = Meow_MWAI_Engines_Factory::get( $this->core, $query->envId );
      $statsObject->price = $engine->get_price( $query, $reply );
    }
    else {
      $statsObject->price = $overrides['price'];
    }

    // Storing the raw query/reply for debugging (if enabled)
    if ( $this->core->get_option( 'statistics_data' ) ) {
      $statsObject->metadata['query'] = $query->toJson();
      $statsObject->metadata['reply'] = $reply->toJson();
    }

    // If it's a form, also store the form fields
    if ( $this->core->get_option( 'statistics_forms_data' ) ) {
      if ( $query->scope === 'form' && $query instanceof Meow_MWAI_Query_Text ) {
        $fields = $query->getExtraParam( 'fields' );
        if ( !empty( $fields ) ) {
          $statsObject->metadata['fields'] = $fields;
        }
      }
    }

    // Finally commit
    $this->commit_stats( $statsObject );
  }

  /**
  * Commits stats from a real-time request, calculates the price from complex tokens data, etc.
  *
  * @param MeowPro_MWAI_Stats $statsObject
  * @return bool
  */
  public function commit_stats_from_realtime( MeowPro_MWAI_Stats $statsObject ): bool {
    $statsArray = is_array( $statsObject->stats ) ? $statsObject->stats : [];
    if ( empty( $statsObject->model ) ) {
      Meow_MWAI_Logging::warn( 'Realtime stats: no model specified.' );
      return false;
    }

    // Attempt to fetch the model definition from the engine
    $engine = Meow_MWAI_Engines_Factory::get( $this->core, $statsObject->envId );
    if ( !$engine ) {
      Meow_MWAI_Logging::warn( "Realtime stats: no engine found for envId ({$statsObject->envId})." );
      return false;
    }

    $modelDef = $engine->retrieve_model_info( $statsObject->model );
    if ( !$modelDef ) {
      Meow_MWAI_Logging::warn( "Realtime stats: model definition not found ({$statsObject->model})." );
      return false;
    }

    // Now we can calculate the price
    $textIn = isset( $statsArray['text_input_tokens'] ) ? intval( $statsArray['text_input_tokens'] ) : 0;
    $audioIn = isset( $statsArray['audio_input_tokens'] ) ? intval( $statsArray['audio_input_tokens'] ) : 0;
    $textOut = isset( $statsArray['text_output_tokens'] ) ? intval( $statsArray['text_output_tokens'] ) : 0;
    $audioOut = isset( $statsArray['audio_output_tokens'] ) ? intval( $statsArray['audio_output_tokens'] ) : 0;
    $textCache = isset( $statsArray['text_cached_tokens'] ) ? intval( $statsArray['text_cached_tokens'] ) : 0;
    $audioCache = isset( $statsArray['audio_cached_tokens'] ) ? intval( $statsArray['audio_cached_tokens'] ) : 0;

    $priceSum = 0.0;
    $unit = !empty( $modelDef['unit'] ) ? floatval( $modelDef['unit'] ) : 1.0;
    $price = isset( $modelDef['price'] ) ? $modelDef['price'] : [];

    // TEXT
    if ( !empty( $price['text'] ) ) {
      $textPrices = $price['text'];
      $priceSum += $textIn * floatval( $textPrices['in'] ?? 0 );
      $priceSum += $textOut * floatval( $textPrices['out'] ?? 0 );
      $priceSum += $textCache * floatval( $textPrices['cache'] ?? 0 );
    }

    // AUDIO
    if ( !empty( $price['audio'] ) ) {
      $audioPrices = $price['audio'];
      $priceSum += $audioIn * floatval( $audioPrices['in'] ?? 0 );
      $priceSum += $audioOut * floatval( $audioPrices['out'] ?? 0 );
      $priceSum += $audioCache * floatval( $audioPrices['cache'] ?? 0 );
    }

    // Multiply by unit factor
    $priceSum = $priceSum * $unit;

    // Let's add the total of tokens in total_tokens
    $statsArray['total_tokens'] = $textIn + $audioIn + $textOut + $audioOut + $textCache + $audioCache;

    // Now store it in the stats object
    $statsObject->price = round( $priceSum, 6 );
    $statsObject->type = 'tokens'; // or something more relevant
    $statsObject->units = isset( $statsArray['total_tokens'] )
        ? intval( $statsArray['total_tokens'] )
            : 0;

    // Then commit
    return $this->commit_stats( $statsObject );
  }

  /**
  * Retrieves usage stats.
  */
  public function stats_query( $timeFrame = null, $isAbsolute = null, $userId = null, $ipAddress = null, $system = false ) {
    return $this->query_stats( $timeFrame, $isAbsolute, $userId, $ipAddress, $system );
  }

  #endregion

  #region Shortcodes

  /**
  * Handles [mwai_stats_current] and [mwai_stats] shortcodes.
  *
  * Usage examples:
  * [mwai_stats_current display="usage"]
  * [mwai_stats display="debug"]
  */
  public function shortcode_current( $atts ) {

    // Determine what to display (usage, debug, etc.)
    $display = isset( $atts['display'] ) ? $atts['display'] : 'debug';
    if ( $display === 'debug' ) {
      $display = 'stats'; // old fallback
    }
    elseif ( $display === 'usage' ) {
      $display = 'usagebar';
    }

    // Additional "display_*" attributes
    $showWho = filter_var( $atts['display_who'] ?? true, FILTER_VALIDATE_BOOLEAN );
    $showQueries = filter_var( $atts['display_queries'] ?? true, FILTER_VALIDATE_BOOLEAN );
    $showUnits = filter_var( $atts['display_units'] ?? true, FILTER_VALIDATE_BOOLEAN );
    $showPrice = filter_var( $atts['display_price'] ?? true, FILTER_VALIDATE_BOOLEAN );
    $showUsage = filter_var( $atts['display_usage'] ?? true, FILTER_VALIDATE_BOOLEAN );
    $showCoins = filter_var( $atts['display_coins'] ?? true, FILTER_VALIDATE_BOOLEAN );

    // Fetch the stats using the new query_stats method
    $stats = $this->query_stats();

    // If there's no stats at all
    if ( empty( $stats ) ) {
      return 'No stats available.';
    }

    // ============== Display: usage bar ==============
    if ( $display === 'usagebar' ) {
      $percent = $stats['usagePercentage'] ?? 0;
      $cssPercent = min( 100, $percent ); // cap at 100
      $output = '<div class="mwai-statistics mwai-statistics-usage">';
      $output .= '  <div class="mwai-statistics-bar-container">';
      $output .= '    <div class="mwai-statistics-bar" style="width: ' . $cssPercent . '%;"></div>';
      $output .= '  </div>';
      $output .= '  <div class="mwai-statistics-bar-text">' . $percent . '%</div>';
      $output .= '</div>';

      // Optional: inline CSS from file
      $css = file_get_contents( MWAI_PATH . '/premium/styles/stats_ChatGPT.css' );
      $output .= '<style>' . $css . '</style>';

      return $output;
    }

    // ============== Display: stats / debug ==============
    elseif ( $display === 'stats' ) {
      $output = '<div class="mwai-statistics mwai-statistics-debug">';

      // Show user ID / IP
      if ( $showWho ) {
        if ( !empty( $stats['userId'] ) ) {
          $output .= "<div>User ID: {$stats['userId']}</div>";
        }
        if ( !empty( $stats['ipAddress'] ) ) {
          $output .= "<div>IP Address: {$stats['ipAddress']}</div>";
        }
      }

      // Show queries
      if ( $showQueries ) {
        $output .= "<div>Queries: {$stats['queries']}";
        if ( !empty( $stats['queriesLimit'] ) ) {
          $output .= " / {$stats['queriesLimit']}";
        }
        $output .= '</div>';
      }

      // Show token/units
      if ( $showUnits ) {
        $output .= "<div>Units: {$stats['units']}";
        if ( !empty( $stats['unitsLimit'] ) ) {
          $output .= " / {$stats['unitsLimit']}";
        }
        $output .= '</div>';
        $output .= '<small>Note: Units can be tokens, images, etc.</small>';
      }

      // Show price
      if ( $showPrice ) {
        $output .= "<div>Price: {$stats['price']}$";
        if ( !empty( $stats['priceLimit'] ) ) {
          $output .= " / {$stats['priceLimit']}$";
        }
        $output .= '</div>';
      }

      // Show 'coins' or custom currency, via a filter
      if ( $showCoins ) {
        $coins = apply_filters( 'mwai_stats_coins', $stats['price'], $stats, $atts );
        $coinsLimit = apply_filters( 'mwai_stats_coins_limit', $stats['priceLimit'], $stats, $atts );
        $output .= "<div>Coins: {$coins}";
        if ( !empty( $coinsLimit ) ) {
          $output .= " / {$coinsLimit}";
        }
        $output .= '</div>';
      }

      // Show usage % & whether user is over the limit
      if ( $showUsage && isset( $stats['usagePercentage'] ) ) {
        $output .= "<div>Usage: {$stats['usagePercentage']}% ";
        $output .= (
          $stats['overLimit']
            ? '<span class="mwai-over">(OVER LIMIT)</span>'
            : '<span class="mwai-ok">(OK)</span>'
        );
        $output .= '</div>';
      }

      $output .= '</div>';
      return $output;
    }

    // if no recognized display...
    return 'No valid display mode was provided.';
  }

  #endregion

  #region Internal API

  public function check_limits( $allowed, $query, $limits ) {
    global $mwai_stats;
    if ( empty( $mwai_stats ) ) {
      return $allowed;
    }

    $hasLimits = $limits && $limits['enabled'];
    if ( !$hasLimits ) {
      return $allowed;
    }


    // System
    if ( isset( $limits['system'] ) && $limits['system']['credits'] > 0 ) {
      $credits = $limits['system']['credits'];
      if ( $credits > 0 ) {
        $stats = $this->query_stats( null, null, null, null, true );
        if ( !empty( $stats ) && $stats['overLimit'] ) {
          return $limits['system']['overLimitMessage'];
        }
      }
    }

    // Identify target: user or guest
    $userId = $this->core->get_user_id();
    if ( $userId >= 0 ) {
      wp_set_current_user( $userId );
    }
    $target = $userId ? 'users' : 'guests';

    // Check ignored users
    if ( $target === 'users' ) {
      $ignoredUsers = $limits['users']['ignoredUsers'];
      $isAdministrator = current_user_can( 'administrator', $userId );
      if ( $isAdministrator && strpos( $ignoredUsers, 'administrator' ) !== false ) {
        return $allowed;
      }
      $isEditor = current_user_can( 'editor' );
      if ( $isEditor && strpos( $ignoredUsers, 'editor' ) !== false ) {
        return $allowed;
      }
    }

    // Check usage
    $credits = apply_filters( 'mwai_stats_credits', $limits[ $target ]['credits'], $userId );
    if ( $credits === 0 ) {
      return $limits[ $target ]['overLimitMessage'];
    }
    $stats = $this->query_stats();
    if ( !empty( $stats ) && $stats['overLimit'] ) {
      return $limits[ $target ]['overLimitMessage'];
    }

    return $allowed;
  }

  public function query_stats( $timeFrame = null, $isAbsolute = null, $userId = null, $ipAddress = null, $system = false ) {
    if ( $system ) {
      $userId = null;
      $ipAddress = null;
      $target = 'system';
    }
    else {
      $target = 'guests';
      if ( $userId === null && $ipAddress === null ) {
        $userId = $this->core->get_user_id();
        if ( $userId ) {
          $target = 'users';
        }
        else {
          $ipAddress = $this->core->get_ip_address();
          if ( $ipAddress === null ) {
            Meow_MWAI_Logging::warn( 'There should be an userId or an ipAddress.' );
            return null;
          }
        }
      }
    }

    $limitsOption = $this->core->get_option( 'limits' );
    $hasLimits = $limitsOption && isset( $limitsOption['enabled'] ) && $limitsOption['enabled'];
    $limits = $hasLimits ? $limitsOption[ $target ] : [];
    if ( $timeFrame === null && !empty( $limits['timeFrame'] ) ) {
      $timeFrame = $limits['timeFrame'];
    }
    if ( $isAbsolute === null && isset( $limits['isAbsolute'] ) ) {
      $isAbsolute = $limits['isAbsolute'];
    }

    $this->check_db();
    $prefix = esc_sql( $this->wpdb->prefix );
    $sql = "SELECT COUNT(*) AS queries, SUM(units) AS units, SUM(price) AS price FROM {$prefix}mwai_logs WHERE ";

    // Condition: userId or ipAddress or system
    if ( $target === 'users' ) {
      $sql .= "userId = '" . esc_sql( $userId ) . "'";
    }
    elseif ( $target === 'guests' ) {
      $sql .= "ip = '" . esc_sql( $ipAddress ) . "'";
    }
    else {
      $sql .= '1 = 1';
    }

    // Time frame
    $timeUnits = [ 'second', 'minute', 'hour', 'day', 'week', 'month', 'year' ];
    if ( in_array( $timeFrame, $timeUnits ) ) {
      $now = date( 'Y-m-d H:i:s' );
      if ( $isAbsolute ) {
        $sql .= ' AND ' . strtoupper( $timeFrame ) . '(time) = ' . strtoupper( $timeFrame ) . "(\"$now\")";
      }
      else {
        $timeAgo = date( 'Y-m-d H:i:s', strtotime( "-1 $timeFrame" ) );
        $sql .= " AND time >= \"$timeAgo\"";
      }
    }
    else {
      Meow_MWAI_Logging::warn( 'TimeFrame should be hour, day, week, month, or year.' );
      return null;
    }

    $results = $this->wpdb->get_results( $sql );
    if ( count( $results ) === 0 ) {
      Meow_MWAI_Logging::warn( 'No results found for the statistics query.' );
      return null;
    }

    $result = $results[0];
    $stats = [];
    $stats['userId'] = $userId;
    $stats['ipAddress'] = $ipAddress;
    $stats['queries'] = intval( $result->queries );
    $stats['units'] = intval( $result->units );
    $stats['price'] = round( floatval( $result->price ), 4 );

    $credits = 0;
    if ( $hasLimits ) {
      $credits = apply_filters( 'mwai_stats_credits', $limits['credits'], $userId );
    }

    $stats['queriesLimit'] = intval( $hasLimits && $limits['creditType'] === 'queries' ? $credits : 0 );
    $stats['unitsLimit'] = intval( $hasLimits && $limits['creditType'] === 'units' ? $credits : 0 );
    $stats['priceLimit'] = floatval( $hasLimits && $limits['creditType'] === 'price' ? $credits : 0 );

    $stats['overLimit'] = false;
    $stats['usagePercentage'] = 0;

    if ( $hasLimits ) {
      if ( $limits['creditType'] === 'queries' ) {
        $stats['overLimit'] = $stats['queries'] >= $credits;
        $stats['usagePercentage'] = $stats['queriesLimit'] > 0
            ? round( $stats['queries'] / $stats['queriesLimit'] * 100, 2 )
                  : 0;
      }
      elseif ( $limits['creditType'] === 'units' ) {
        $stats['overLimit'] = $stats['units'] >= $credits;
        $stats['usagePercentage'] = $stats['unitsLimit'] > 0
            ? round( $stats['units'] / $stats['unitsLimit'] * 100, 2 )
                  : 0;
      }
      elseif ( $limits['creditType'] === 'price' ) {
        $stats['overLimit'] = $stats['price'] >= $credits;
        $stats['usagePercentage'] = $stats['priceLimit'] > 0
            ? round( $stats['price'] / $stats['priceLimit'] * 100, 2 )
                  : 0;
      }
    }
    return $stats;
  }

  #endregion

  #region Helpers

  public function validate_data( $data ): array {
    $data['time'] = date( 'Y-m-d H:i:s' );
    $data['userId'] = $this->core->get_user_id( $data );
    $data['session'] = isset( $data['session'] ) ? (string) $data['session'] : null;
    $data['ip'] = $this->core->get_ip_address();
    $data['model'] = isset( $data['model'] ) ? (string) $data['model'] : null;
    $data['feature'] = isset( $data['feature'] ) ? (string) $data['feature'] : null;
    $data['units'] = isset( $data['units'] ) ? intval( $data['units'] ) : 0;
    $data['type'] = isset( $data['type'] ) ? (string) $data['type'] : null;
    $data['price'] = isset( $data['price'] ) ? floatval( $data['price'] ) : 0;
    $data['scope'] = isset( $data['scope'] ) ? (string) $data['scope'] : null;
    $data['envId'] = isset( $data['envId'] ) ? (string) $data['envId'] : null;

    if ( isset( $data['refId'] ) ) {
      $data['refId'] = (string) $data['refId'];
    }
    else {
      $data['refId'] = null;
    }

    // stats is a LONGTEXT column, so we store the JSON.
    $data['stats'] = isset( $data['stats'] ) ? $data['stats'] : null;

    return $data;
  }

  public function add_log( $data ) {
    $this->check_db();
    $data = $this->validate_data( $data );
    if ( empty( $data ) ) {
      return false;
    }
    $res = $this->wpdb->insert( $this->table_logs, $data );
    if ( $res === false ) {
      Meow_MWAI_Logging::error( 'Error while writing logs (' . $this->wpdb->last_error . ')' );
      return false;
    }
    return $this->wpdb->insert_id;
  }

  public function add_metadata( int $logId, string $metaKey, string $metaValue ) {
    $data = [
      'log_id' => $logId,
      'meta_key' => $metaKey,
      'meta_value' => $metaValue,
    ];
    $res = $this->wpdb->insert( $this->table_logmeta, $data );
    if ( $res === false ) {
      Meow_MWAI_Logging::error( 'Error while writing logs metadata (' . $this->wpdb->last_error . ')' );
      return false;
    }
    return $this->wpdb->insert_id;
  }

  public function check_db(): bool {
    if ( $this->db_check ) {
      return true;
    }
    $this->db_check = !(
      strtolower( $this->wpdb->get_var( "SHOW TABLES LIKE '$this->table_logs'" ) )
          != strtolower( $this->table_logs )
    );

    if ( !$this->db_check ) {
      $this->create_db();
      $this->db_check = !(
        strtolower( $this->wpdb->get_var( "SHOW TABLES LIKE '$this->table_logs'" ) )
            != strtolower( $this->table_logs )
      );
    }

    // LATER: REMOVE THIS AFTER JANUARY 2025
    // Remove old columns if they exist
    // $this->db_check = $this->db_check &&
    //   ! $this->wpdb->get_var( "SHOW COLUMNS FROM $this->table_logs LIKE 'apiRef'" );
    // if ( ! $this->db_check ) {
    //   $this->wpdb->query( "ALTER TABLE $this->table_logs DROP COLUMN apiRef" );
    //   $this->wpdb->query( "ALTER TABLE $this->table_logs DROP COLUMN apiOwn" );
    //   $this->wpdb->query( "ALTER TABLE $this->table_logs DROP COLUMN tags" );
    //   $this->wpdb->query( "ALTER TABLE $this->table_logs CHANGE COLUMN mode feature VARCHAR(64) NULL" );
    //   $this->db_check = true;
    // }

    // LATER: REMOVE THIS AFTER JULY 2025
    // Add refId column if it doesn't exist
    $hasRefIdColumn = $this->wpdb->get_var( "SHOW COLUMNS FROM $this->table_logs LIKE 'refId'" );
    if ( !$hasRefIdColumn ) {
      $this->wpdb->query( "ALTER TABLE $this->table_logs ADD COLUMN refId VARCHAR(64) NULL" );
    }

    // LATER: REMOVE THIS AFTER JULY 2025
    // Add stats column if it doesn't exist
    $hasStatsColumn = $this->wpdb->get_var( "SHOW COLUMNS FROM $this->table_logs LIKE 'stats'" );
    if ( !$hasStatsColumn ) {
      $this->wpdb->query( "ALTER TABLE $this->table_logs ADD COLUMN stats LONGTEXT NULL" );
    }

    // LATER: REMOVE THIS AFTER JANUARY 2026
    // Add accuracy column if it doesn't exist
    $hasAccuracyColumn = $this->wpdb->get_var( "SHOW COLUMNS FROM $this->table_logs LIKE 'accuracy'" );
    if ( !$hasAccuracyColumn ) {
      $this->wpdb->query( "ALTER TABLE $this->table_logs ADD COLUMN accuracy VARCHAR(20) NULL DEFAULT 'none'" );
    }

    // LATER: REMOVE THIS AFTER SEPTEMBER 2025
    // Migrate guest users from userId = 0 to userId = NULL
    $guest_count = $this->wpdb->get_var( "SELECT COUNT(*) FROM $this->table_logs WHERE userId = 0" );
    if ( $guest_count > 0 ) {
      $this->wpdb->query( "UPDATE $this->table_logs SET userId = NULL WHERE userId = 0" );
    }

    return $this->db_check;
  }

  public function create_db(): void {
    $charset_collate = $this->wpdb->get_charset_collate();

    $sqlLogs = "CREATE TABLE $this->table_logs (
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        id BIGINT(20) NOT NULL AUTO_INCREMENT,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          userId BIGINT(20) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            ip VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              session VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                model VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  feature VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    units INT(11) NOT NULL DEFAULT 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      type VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        price FLOAT NOT NULL DEFAULT 0,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        scope VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          envId VARCHAR(128) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            time DATETIME NOT NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            refId VARCHAR(64) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              stats LONGTEXT NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              accuracy VARCHAR(20) NULL DEFAULT 'none',
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              PRIMARY KEY (id)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              ) $charset_collate;";

    $sqlLogMeta = "CREATE TABLE $this->table_logmeta (
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              meta_id BIGINT(20) NOT NULL AUTO_INCREMENT,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                log_id BIGINT(20) NOT NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  meta_key varchar(255) NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    meta_value longtext NULL,
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    PRIMARY KEY (meta_id)
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sqlLogs );
    dbDelta( $sqlLogMeta );
  }

  public function remove_db(): void {
    $sql = "DROP TABLE IF EXISTS $this->table_logs, $this->table_logmeta;";
    $this->wpdb->query( $sql );
  }

  #endregion

  #region Logs CRUD

  public function stats_logs_meta( array $meta, int $logId, array $metaKeys ) {
    $query = "SELECT * FROM $this->table_logmeta";
    $where = [];
    $where[] = 'log_id = ' . intval( $logId );
    if ( !empty( $metaKeys ) ) {
      $where[] = "meta_key IN ('" . implode( "','", $metaKeys ) . "')";
    }
    if ( !empty( $where ) ) {
      $query .= ' WHERE ' . implode( ' AND ', $where );
    }
    $query .= ' ORDER BY meta_key ASC';
    $res = $this->wpdb->get_results( $query, ARRAY_A );

    foreach ( $res as $key => $value ) {
      if ( $value['meta_key'] === 'query' ) {
        $meta['query'] = json_decode( $value['meta_value'], true );
      }
      elseif ( $value['meta_key'] === 'reply' ) {
        $meta['reply'] = json_decode( $value['meta_value'], true );
      }
      elseif ( $value['meta_key'] === 'fields' ) {
        $meta['fields'] = json_decode( $value['meta_value'], true );
      }
    }
    return $meta;
  }

  public function stats_logs_delete( $success, $logIds ) {
    if ( !$success ) {
      return false;
    }
    $logIds = !empty( $logIds ) ? $logIds : [];
    if ( empty( $logIds ) ) {
      $query = "DELETE FROM $this->table_logs";
      $this->wpdb->query( $query );
      $query = "DELETE FROM $this->table_logmeta";
      $this->wpdb->query( $query );
      return true;
    }
    $logIds = array_map( 'intval', $logIds );
    $logIds = implode( ',', $logIds );

    $query = "DELETE FROM $this->table_logs WHERE id IN ($logIds)";
    $this->wpdb->query( $query );

    // Clean up meta
    $query = "DELETE FROM $this->table_logmeta WHERE log_id NOT IN (SELECT id FROM $this->table_logs)";
    $this->wpdb->query( $query );

    return true;
  }

  public function stats_logs_list( $logs = [], $offset = 0, $limit = null, $filters = null, $sort = null ) {
    $this->check_db();
    $offset = !empty( $offset ) ? intval( $offset ) : 0;
    $limit = !empty( $limit ) ? intval( $limit ) : 100;
    $filters = !empty( $filters ) ? $filters : [];
    $this->core->sanitize_sort( $sort, 'time', 'DESC' );

    $query = "SELECT * FROM $this->table_logs";

    // Filters
    $where = [];
    if ( !empty( $filters ) ) {
      foreach ( $filters as $filter ) {
        $accessor = $filter['accessor'];
        $value = $filter['value'];
        if ( empty( $value ) ) {
          continue;
        }
        if ( $accessor === 'user' ) {
          $isIP = filter_var( $value, FILTER_VALIDATE_IP );
          if ( $isIP ) {
            $where[] = "ip = '" . esc_sql( $value ) . "'";
          }
          else {
            $where[] = 'userId = ' . intval( $value );
          }
        }
        elseif ( $accessor === 'session' ) {
          $where[] = "session = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'model' ) {
          $where[] = "model = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'feature' ) {
          $where[] = "feature = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'units' ) {
          $where[] = 'units = ' . intval( $value );
        }
        elseif ( $accessor === 'type' ) {
          $where[] = "type = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'price' ) {
          $where[] = 'price = ' . floatval( $value );
        }
        elseif ( $accessor === 'scope' ) {
          $where[] = "scope IN ('" . implode( "','", $value ) . "')";
        }
        elseif ( $accessor === 'envId' ) {
          $where[] = "envId = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'time' ) {
          $where[] = "time = '" . esc_sql( $value ) . "'";
        }
        elseif ( $accessor === 'refId' ) {
          $where[] = "refId = '" . esc_sql( $value ) . "'";
        }
      }
    }

    if ( count( $where ) > 0 ) {
      $query .= ' WHERE ' . implode( ' AND ', $where );
    }

    $logs['total'] = $this->wpdb->get_var( "SELECT COUNT(*) FROM ($query) AS t" );

    $query .= ' ORDER BY ' . esc_sql( $sort['accessor'] ) . ' ' . esc_sql( $sort['by'] );
    if ( $limit > 0 ) {
      $query .= " LIMIT $offset, $limit";
    }

    $logs['rows'] = $this->wpdb->get_results( $query, ARRAY_A );
    return $logs;
  }

  public function stats_logs_activity( $hours = 24 ) {
    $this->check_db();
    $hours = intval( $hours );
    $query = $this->wpdb->prepare(
      "SELECT DATE_FORMAT(time, '%%Y-%%m-%%d %%H:00:00') AS h, COUNT(*) AS c FROM {$this->table_logs} WHERE time >= DATE_SUB(NOW(), INTERVAL %d HOUR) GROUP BY h ORDER BY h",
      $hours
    );
    $results = $this->wpdb->get_results( $query, ARRAY_A );
    $data = array_fill( 0, $hours, 0 );
    foreach ( $results as $row ) {
      $index = $hours - 1 - intval( ( strtotime( 'now' ) - strtotime( $row['h'] ) ) / 3600 );
      if ( $index >= 0 && $index < $hours ) {
        $data[$index] = intval( $row['c'] );
      }
    }
    return $data;
  }

  public function stats_logs_activity_daily( $data = [], $days = 31 ) {
    $this->check_db();
    $days = intval( $days );

    // Ensure we have a valid days value
    if ( $days <= 0 ) {
      $days = 31;
    }

    // Build query without wpdb->prepare for the DATE_FORMAT to avoid % escaping issues
    $query = "SELECT DATE_FORMAT(time, '%Y-%m-%d') AS d, COUNT(*) AS c FROM {$this->table_logs} WHERE time >= DATE_SUB(NOW(), INTERVAL " . intval( $days ) . ' DAY) GROUP BY d ORDER BY d';

    $results = $this->wpdb->get_results( $query, ARRAY_A );
    $result_data = array_fill( 0, $days, 0 );

    foreach ( $results as $row ) {
      $daysDiff = intval( ( strtotime( 'now' ) - strtotime( $row['d'] . ' 00:00:00' ) ) / 86400 );
      $index = $days - 1 - $daysDiff;
      if ( $index >= 0 && $index < $days ) {
        $result_data[$index] = intval( $row['c'] );
      }
    }

    return $result_data;
  }

  public function stats_logs_activity_daily_by_model( $data = [], $days = 31 ) {
    try {
      $this->check_db();
      $days = intval( $days );

      // Ensure we have a valid days value
      if ( $days <= 0 ) {
        $days = 31;
      }

      // Build query to get data grouped by date and model
      $query = "SELECT DATE_FORMAT(time, '%Y-%m-%d') AS d, model, COUNT(*) AS c 
                FROM {$this->table_logs} 
                WHERE time >= DATE_SUB(NOW(), INTERVAL " . intval( $days ) . ' DAY) 
                GROUP BY d, model 
                ORDER BY d, model';

      $results = $this->wpdb->get_results( $query, ARRAY_A );

      // Initialize result structure: array of days, each containing model counts
      $result_data = [];
      for ( $i = 0; $i < $days; $i++ ) {
        $result_data[$i] = [];
      }

      // Process results
      foreach ( $results as $row ) {
        if ( empty( $row['model'] ) ) {
          continue; // Skip entries without model
        }

        $daysDiff = intval( ( strtotime( 'now' ) - strtotime( $row['d'] . ' 00:00:00' ) ) / 86400 );
        $index = $days - 1 - $daysDiff;

        if ( $index >= 0 && $index < $days ) {
          $model = $row['model'];
          $result_data[$index][$model] = intval( $row['c'] );
        }
      }

      return $result_data;
    }
    catch ( Exception $e ) {
      error_log( 'AI Engine - Error in stats_logs_activity_daily_by_model: ' . $e->getMessage() );
      return array_fill( 0, $days, [] );
    }
  }

  #endregion

}
