<?php
/**
 * ProQyz Database Utilities
 * 
 * @package ProQyz
 * @subpackage Utils
 */

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Convert string data (JSON or serialized) to PHP object or array
 * 
 * This function accepts a value that can be:
 * - JSON string
 * - Serialized string
 * - Already decoded data (object/array)
 * 
 * It automatically detects the type and returns the converted PHP data structure
 * Uses WordPress built-in functions for serialized data handling
 * 
 * @param mixed $data The data to convert (string, object, or array)
 * @return object|array|null Returns object or array based on decoded data, null on failure
 */
function st_proqyz_decode_data($data) {
    // If data is null or empty, return null
    if (empty($data) && $data !== '0' && $data !== 0) {
        return null;
    }
    
    // If it's already an object or array, return as is
    if (is_object($data) || is_array($data)) {
        return $data;
    }
    
    // If it's not a string, return null
    if (!is_string($data)) {
        return null;
    }
    
    // Trim whitespace
    $data = trim($data);
    
    // Try JSON decode first
    if (st_proqyz_is_json($data)) {
        $decoded = json_decode($data, false); // Keep as object if it was JSON object
        if ($decoded !== null) {
            return $decoded;
        }
    }
    
    // Use WordPress built-in function for serialized data
    // maybe_unserialize() will check if data is serialized and unserialize it, or return as-is
    $maybe_unserialized = maybe_unserialize($data);
    
    // If maybe_unserialize returned something different, it was serialized
    if ($maybe_unserialized !== $data) {
        return $maybe_unserialized;
    }
    
    // If nothing worked, return null
    return null;
}

/**
 * Check if a string is valid JSON
 * 
 * @param string $string
 * @return bool
 */
function st_proqyz_is_json($string) {
    if (!is_string($string)) {
        return false;
    }
    
    // Basic JSON format check
    $string = trim($string);
    if (empty($string)) {
        return false;
    }
    
    // Must start with { or [
    if (!in_array($string[0], ['{', '['])) {
        return false;
    }
    
    json_decode($string);
    return json_last_error() === JSON_ERROR_NONE;
}

/**
 * Update or insert evaluation metadata for a submission
 * 
 * Similar to WordPress update_user_meta(), but for the v3 evaluation metadata table.
 * This function checks if a meta entry exists for the given submission_id, user_id, and meta_key.
 * If it exists, updates it. If not, inserts a new record.
 * 
 * @param int|string $submission_id The submission/session ID
 * @param string $meta_key The metadata key
 * @param mixed $meta_value The metadata value (will be serialized if array/object)
 * @param int $user_id The user ID (defaults to current user if not provided)
 * @return int|false The meta_id on success, false on failure
 * 
 * @since 2.1.0
 */
function st_proqyz_update_submission_evaluation_meta( $submission_id, $meta_key, $meta_value, $user_id = null ) {
    global $wpdb, $table_proqyz_v3_submissions_evaluation_meta;
    
    // Use current user ID if not provided
    if ( $user_id === null ) {
        $user_id = get_current_user_id();
    }
    
    // Validate inputs
    if ( empty( $submission_id ) || empty( $user_id ) || empty( $meta_key ) ) {
        return false;
    }
    
    // Get table name (use global if available, otherwise construct it)
    $table_name = isset( $table_proqyz_v3_submissions_evaluation_meta ) 
        ? $table_proqyz_v3_submissions_evaluation_meta 
        : $wpdb->prefix . 'st_proqyz_v3_submissions_evaluation_meta';
    
    // Convert submission_id to string (session_id is varchar)
    $session_id = (string) $submission_id;
    $user_id = absint( $user_id );
    $meta_key = sanitize_text_field( $meta_key );
    
    // Serialize meta_value if it's an array or object
    if ( is_array( $meta_value ) || is_object( $meta_value ) ) {
        $meta_value = maybe_serialize( $meta_value );
    } else {
        $meta_value = $meta_value;
    }
    
    // Current timestamp
    $current_time = current_time( 'timestamp', true );
    
    // Check if meta entry already exists
    $existing = $wpdb->get_row( $wpdb->prepare(
        "SELECT meta_id FROM {$table_name} 
         WHERE session_id = %s AND user_id = %d AND meta_key = %s 
         LIMIT 1",
        $session_id,
        $user_id,
        $meta_key
    ) );
    
    if ( $existing ) {
        // Update existing record
        $result = $wpdb->update(
            $table_name,
            array(
                'meta_value' => $meta_value,
                'updated_at' => $current_time,
            ),
            array(
                'meta_id' => $existing->meta_id,
            ),
            array(
                '%s', // meta_value
                '%d', // updated_at
            ),
            array(
                '%d', // meta_id
            )
        );
        
        if ( $result !== false ) {
            return $existing->meta_id;
        }
    } else {
        // Insert new record
        $result = $wpdb->insert(
            $table_name,
            array(
                'session_id' => $session_id,
                'user_id' => $user_id,
                'meta_key' => $meta_key,
                'meta_value' => $meta_value,
                'created_at' => $current_time,
                'updated_at' => null,
            ),
            array(
                '%s', // session_id
                '%d', // user_id
                '%s', // meta_key
                '%s', // meta_value
                '%d', // created_at
                '%s', // updated_at (null)
            )
        );
        
        if ( $result !== false ) {
            return $wpdb->insert_id;
        }
    }
    
    return false;
}

/**
 * Get evaluation metadata for a submission
 * 
 * Similar to WordPress get_user_meta(), but for the v3 evaluation metadata table.
 * Retrieves metadata value(s) for the given submission_id, user_id, and meta_key.
 * Returns raw meta_value as stored in database (no unserialization or json_decode).
 * 
 * @param int|string $submission_id The submission/session ID
 * @param string $meta_key The metadata key (optional, if empty returns all meta for submission+user)
 * @param int $user_id The user ID (defaults to current user if not provided)
 * @param bool $single Whether to return a single value (true) or array of values (false). Default true.
 * @return mixed Single raw meta value if $single is true, array of raw values if false, empty string/array if not found
 * 
 * @since 2.1.0
 */
function st_proqyz_get_submission_evaluation_meta( $submission_id, $meta_key = '', $user_id = null, $single = true ) {
    global $wpdb, $table_proqyz_v3_submissions_evaluation_meta;
    
    // Use current user ID if not provided
    if ( $user_id === null ) {
        $user_id = get_current_user_id();
    }
    
    // Validate inputs
    if ( empty( $submission_id ) || empty( $user_id ) ) {
        return $single ? '' : array();
    }
    
    // Get table name (use global if available, otherwise construct it)
    $table_name = isset( $table_proqyz_v3_submissions_evaluation_meta ) 
        ? $table_proqyz_v3_submissions_evaluation_meta 
        : $wpdb->prefix . 'st_proqyz_v3_submissions_evaluation_meta';
    
    // Convert submission_id to string (session_id is varchar)
    $session_id = (string) $submission_id;
    $user_id = absint( $user_id );
    
    if ( ! empty( $meta_key ) ) {
        // Get specific meta key
        $meta_key = sanitize_text_field( $meta_key );
        
        $results = $wpdb->get_results( $wpdb->prepare(
            "SELECT meta_value FROM {$table_name} 
             WHERE session_id = %s AND user_id = %d AND meta_key = %s 
             ORDER BY meta_id ASC",
            $session_id,
            $user_id,
            $meta_key
        ) );
        
        if ( empty( $results ) ) {
            return $single ? '' : array();
        }
        
        $values = array();
        foreach ( $results as $row ) {
            $values[] = $row->meta_value; // Return raw value without unserialization
        }
        
        if ( $single ) {
            return $values[0];
        } else {
            return $values;
        }
    } else {
        // Get all meta keys for this submission+user
        $results = $wpdb->get_results( $wpdb->prepare(
            "SELECT meta_key, meta_value FROM {$table_name} 
             WHERE session_id = %s AND user_id = %d 
             ORDER BY meta_key ASC, meta_id ASC",
            $session_id,
            $user_id
        ) );
        
        if ( empty( $results ) ) {
            return array();
        }
        
        $meta = array();
        foreach ( $results as $row ) {
            $key = $row->meta_key;
            $value = $row->meta_value; // Return raw value without unserialization
            
            if ( $single ) {
                // For single mode, return first value for each key
                if ( ! isset( $meta[ $key ] ) ) {
                    $meta[ $key ] = $value;
                }
            } else {
                // For multiple mode, return array of values for each key
                if ( ! isset( $meta[ $key ] ) ) {
                    $meta[ $key ] = array();
                }
                $meta[ $key ][] = $value;
            }
        }
        
        return $meta;
    }
}