<?php
/**
 * Certificate Manager Class
 *
 * Handles the management of certificates, including creating, retrieving, and revoking.
 *
 * @package HVAC_Community_Events
 * @subpackage Certificates
 */

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Certificate Manager class.
 *
 * Manages certificates for event attendees.
 *
 * @since 1.0.0
 */
class HVAC_Certificate_Manager {

    /**
     * The single instance of the class.
     *
     * @var HVAC_Certificate_Manager
     */
    protected static $_instance = null;

    /**
     * Main HVAC_Certificate_Manager Instance.
     *
     * Ensures only one instance of HVAC_Certificate_Manager is loaded or can be loaded.
     *
     * @return HVAC_Certificate_Manager - Main instance.
     */
    public static function instance() {
        if (is_null(self::$_instance)) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    /**
     * Constructor.
     */
    public function __construct() {
        // Make sure table exists
        require_once HVAC_PLUGIN_DIR . 'includes/certificates/class-certificate-installer.php';
        $installer = HVAC_Certificate_Installer::instance();
        $installer->check_tables();
    }

    /**
     * Generate a unique certificate number.
     *
     * @return string The generated certificate number.
     */
    public function generate_certificate_number() {
        $prefix = get_option('hvac_certificate_prefix', 'HVAC-');
        $counter = intval(get_option('hvac_certificate_counter', 0));
        
        // Increment counter
        $counter++;
        update_option('hvac_certificate_counter', $counter);
        
        // Format: PREFIX-YEAR-SEQUENTIAL (e.g., HVAC-2023-00001)
        $year = date('Y');
        $formatted_counter = str_pad($counter, 5, '0', STR_PAD_LEFT);
        
        return $prefix . $year . '-' . $formatted_counter;
    }

    /**
     * Creates a new certificate record in the database.
     *
     * @param int    $event_id    The event ID.
     * @param int    $attendee_id The attendee ID.
     * @param int    $user_id     The associated user ID (if available).
     * @param string $file_path   The path to the certificate file.
     * @param int    $generated_by The ID of the user who generated the certificate.
     * 
     * @return int|false The certificate ID if successful, false otherwise.
     */
    public function create_certificate($event_id, $attendee_id, $user_id = 0, $file_path = '', $generated_by = 0) {
        global $wpdb;
        
        // Get current user if not specified
        if (empty($generated_by)) {
            $generated_by = get_current_user_id();
        }
        
        // Generate certificate number
        $certificate_number = $this->generate_certificate_number();
        
        // Current date/time
        $date_generated = current_time('mysql');
        
        // Insert certificate record
        $result = $wpdb->insert(
            $wpdb->prefix . 'hvac_certificates',
            array(
                'event_id'         => $event_id,
                'attendee_id'      => $attendee_id,
                'user_id'          => $user_id,
                'certificate_number' => $certificate_number,
                'file_path'        => $file_path,
                'date_generated'   => $date_generated,
                'generated_by'     => $generated_by,
                'revoked'          => 0,
                'email_sent'       => 0
            ),
            array(
                '%d', // event_id
                '%d', // attendee_id
                '%d', // user_id
                '%s', // certificate_number
                '%s', // file_path
                '%s', // date_generated
                '%d', // generated_by
                '%d', // revoked
                '%d'  // email_sent
            )
        );
        
        if ($result) {
            return $wpdb->insert_id;
        }
        
        return false;
    }

    /**
     * Update the file paths for a certificate.
     *
     * @param int    $certificate_id The certificate ID.
     * @param string $file_path      The PDF file path.
     * @param string $png_path       The PNG file path (optional).
     * 
     * @return bool True if successful, false otherwise.
     */
    public function update_certificate_file($certificate_id, $file_path, $png_path = null) {
        global $wpdb;
        
        $update_data = array(
            'file_path' => $file_path
        );
        $format = array('%s');
        
        if ($png_path !== null) {
            $update_data['png_path'] = $png_path;
            $format[] = '%s';
        }
        
        $result = $wpdb->update(
            $wpdb->prefix . 'hvac_certificates',
            $update_data,
            array(
                'certificate_id' => $certificate_id
            ),
            $format,
            array('%d')
        );
        
        return $result !== false;
    }

    /**
     * Mark a certificate as sent via email.
     *
     * @param int $certificate_id The certificate ID.
     * 
     * @return bool True if successful, false otherwise.
     */
    public function mark_certificate_emailed($certificate_id) {
        global $wpdb;
        
        $result = $wpdb->update(
            $wpdb->prefix . 'hvac_certificates',
            array(
                'email_sent'     => 1,
                'email_sent_date' => current_time('mysql')
            ),
            array(
                'certificate_id' => $certificate_id
            ),
            array('%d', '%s'),
            array('%d')
        );
        
        return $result !== false;
    }

    /**
     * Revoke a certificate.
     *
     * @param int    $certificate_id The certificate ID.
     * @param int    $revoked_by     The ID of the user who revoked the certificate.
     * @param string $reason         The reason for revocation.
     * 
     * @return bool True if successful, false otherwise.
     */
    public function revoke_certificate($certificate_id, $revoked_by = 0, $reason = '') {
        global $wpdb;
        
        // Get current user if not specified
        if (empty($revoked_by)) {
            $revoked_by = get_current_user_id();
        }
        
        $result = $wpdb->update(
            $wpdb->prefix . 'hvac_certificates',
            array(
                'revoked'       => 1,
                'revoked_date'  => current_time('mysql'),
                'revoked_by'    => $revoked_by,
                'revoked_reason' => $reason
            ),
            array(
                'certificate_id' => $certificate_id
            ),
            array('%d', '%s', '%d', '%s'),
            array('%d')
        );
        
        return $result !== false;
    }

    /**
     * Get a certificate by ID.
     *
     * @param int $certificate_id The certificate ID.
     * 
     * @return object|false The certificate object if found, false otherwise.
     */
    public function get_certificate($certificate_id) {
        global $wpdb;
        
        $query = $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}hvac_certificates WHERE certificate_id = %d",
            $certificate_id
        );
        
        return $wpdb->get_row($query);
    }

    /**
     * Get a certificate by event ID and attendee ID.
     *
     * @param int $event_id    The event ID.
     * @param int $attendee_id The attendee ID.
     * 
     * @return object|false The certificate object if found, false otherwise.
     */
    public function get_certificate_by_attendee($event_id, $attendee_id) {
        global $wpdb;
        
        $query = $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}hvac_certificates WHERE event_id = %d AND attendee_id = %d",
            $event_id, $attendee_id
        );
        
        return $wpdb->get_row($query);
    }

    /**
     * Get all certificates for an event.
     *
     * @param int  $event_id The event ID.
     * @param bool $include_revoked Whether to include revoked certificates.
     * 
     * @return array Array of certificate objects.
     */
    public function get_certificates_by_event($event_id, $include_revoked = false) {
        global $wpdb;
        
        $where = "WHERE event_id = %d";
        $params = array($event_id);
        
        if (!$include_revoked) {
            $where .= " AND revoked = 0";
        }
        
        $query = $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}hvac_certificates $where ORDER BY date_generated DESC",
            $params
        );
        
        return $wpdb->get_results($query);
    }

    /**
     * Get certificates count by event.
     *
     * @param int $event_id The event ID.
     * 
     * @return array Certificate counts (total, active, revoked).
     */
    public function get_certificates_count_by_event($event_id) {
        global $wpdb;
        
        $query = $wpdb->prepare(
            "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN revoked = 0 THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN revoked = 1 THEN 1 ELSE 0 END) as revoked,
                SUM(CASE WHEN email_sent = 1 THEN 1 ELSE 0 END) as emailed
            FROM {$wpdb->prefix}hvac_certificates 
            WHERE event_id = %d",
            $event_id
        );
        
        $result = $wpdb->get_row($query);
        
        return array(
            'total'   => intval($result->total),
            'active'  => intval($result->active),
            'revoked' => intval($result->revoked),
            'emailed' => intval($result->emailed)
        );
    }

    /**
     * Get overall certificate statistics.
     *
     * @return array Certificate statistics.
     */
    public function get_certificate_stats() {
        global $wpdb;
        
        $query = "SELECT 
            COUNT(DISTINCT attendee_id) as total_trainees,
            COUNT(DISTINCT event_id) as total_events_with_certificates,
            COUNT(*) as total_certificates,
            SUM(CASE WHEN revoked = 1 THEN 1 ELSE 0 END) as total_revoked,
            SUM(CASE WHEN email_sent = 1 THEN 1 ELSE 0 END) as total_emailed
        FROM {$wpdb->prefix}hvac_certificates";
        
        $result = $wpdb->get_row($query);
        
        // Calculate average certificates per attendee
        $avg_per_attendee = 0;
        if (!empty($result->total_trainees)) {
            $avg_per_attendee = $result->total_certificates / $result->total_trainees;
        }
        
        return array(
            'total_trainees' => intval($result->total_trainees),
            'total_events' => intval($result->total_events_with_certificates),
            'total_certificates' => intval($result->total_certificates),
            'total_revoked' => intval($result->total_revoked),
            'total_emailed' => intval($result->total_emailed),
            'avg_per_attendee' => round($avg_per_attendee, 2)
        );
    }

    /**
     * Get all certificates for a specific attendee.
     *
     * @param int  $attendee_id The attendee ID.
     * @param bool $include_revoked Whether to include revoked certificates.
     * 
     * @return array Array of certificate objects.
     */
    public function get_certificates_by_attendee($attendee_id, $include_revoked = false) {
        global $wpdb;
        
        $where = "WHERE attendee_id = %d";
        $params = array($attendee_id);
        
        if (!$include_revoked) {
            $where .= " AND revoked = 0";
        }
        
        $query = $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}hvac_certificates $where ORDER BY date_generated DESC",
            $params
        );
        
        return $wpdb->get_results($query);
    }

    /**
     * Get certificates by user ID.
     *
     * @param int  $user_id The user ID.
     * @param bool $include_revoked Whether to include revoked certificates.
     * 
     * @return array Array of certificate objects.
     */
    public function get_certificates_by_user($user_id, $include_revoked = false) {
        global $wpdb;
        
        $where = "WHERE user_id = %d";
        $params = array($user_id);
        
        if (!$include_revoked) {
            $where .= " AND revoked = 0";
        }
        
        $query = $wpdb->prepare(
            "SELECT * FROM {$wpdb->prefix}hvac_certificates $where ORDER BY date_generated DESC",
            $params
        );
        
        return $wpdb->get_results($query);
    }

    /**
     * Get all events that have certificates.
     *
     * @param int $user_id Optional user ID to filter events by author.
     * @return array Array of event objects with certificate data.
     */
    public function get_events_with_certificates($user_id = 0) {
        global $wpdb;
        
        // Get events with certificates
        $query = "SELECT 
            event_id,
            COUNT(*) as total_certificates,
            SUM(CASE WHEN revoked = 0 THEN 1 ELSE 0 END) as active_certificates,
            SUM(CASE WHEN revoked = 1 THEN 1 ELSE 0 END) as revoked_certificates,
            SUM(CASE WHEN email_sent = 1 THEN 1 ELSE 0 END) as emailed_certificates,
            MAX(date_generated) as last_generated
        FROM {$wpdb->prefix}hvac_certificates 
        GROUP BY event_id
        ORDER BY last_generated DESC";
        
        $certificate_data = $wpdb->get_results($query, OBJECT_K);
        
        // Get event data
        $event_ids = array_keys($certificate_data);
        
        if (empty($event_ids)) {
            return array();
        }
        
        // Build WP_Query args
        $args = array(
            'post_type' => Tribe__Events__Main::POSTTYPE,
            'post__in' => $event_ids,
            'posts_per_page' => -1,
            'orderby' => 'post__in',
            'post_status' => 'publish'
        );
        
        // Filter by user if specified
        if ($user_id > 0) {
            $args['author'] = $user_id;
        }
        
        $events = get_posts($args);
        
        return $events;
    }
    
    /**
     * Get certificates for events created by a specific user.
     *
     * @param int   $user_id The user ID.
     * @param array $args    Additional query args (limit, offset, etc.).
     * 
     * @return array Array of certificate objects.
     */
    public function get_user_certificates($user_id, $args = array()) {
        global $wpdb;
        
        if (function_exists('hvac_debug_log')) {
            hvac_debug_log('get_user_certificates called with user_id', $user_id);
            hvac_debug_log('get_user_certificates args', $args);
        }
        
        $defaults = array(
            'page' => 1,
            'per_page' => 20,
            'orderby' => 'date_generated',
            'order' => 'DESC',
            'event_id' => 0,
            'revoked' => null,
            'limit' => 0,
            'search_attendee' => ''
        );
        
        $args = wp_parse_args($args, $defaults);
        
        if (function_exists('hvac_debug_log')) {
            hvac_debug_log('Args after parsing defaults', $args);
        }
        
        // Build WHERE clause
        $where = array();
        $where_values = array();
        
        // Get event IDs authored by this user
        if (function_exists('hvac_debug_log')) {
            hvac_debug_log('Creating WP_Query to get user events');
        }
        
        try {
            // Use direct database query to get user's event IDs (bypassing TEC interference)
            $event_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} 
                 WHERE post_type = %s 
                 AND post_author = %d 
                 AND post_status = 'publish'",
                'tribe_events',
                $user_id
            ));
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Direct DB query completed, event_ids count', count($event_ids));
            }
            
            if (empty($event_ids)) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('No events found for user, returning empty array');
                }
                return array();
            }
            
            // Filter by event ID if specified
            if (!empty($args['event_id'])) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Filter by specific event ID', $args['event_id']);
                }
                
                // Check if the specified event belongs to the user
                if (in_array($args['event_id'], $event_ids)) {
                    $where[] = "event_id = %d";
                    $where_values[] = $args['event_id'];
                    
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Event belongs to user, adding to WHERE clause');
                    }
                } else {
                    // Event doesn't belong to this user
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Event does not belong to user, returning empty array');
                    }
                    return array();
                }
            } else {
                // Include all user's events
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Including all user events in query');
                }
                
                $event_ids_string = implode(',', array_map('intval', $event_ids));
                
                // Check if we have a valid string of event IDs
                if (empty($event_ids_string)) {
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Empty event_ids_string, returning empty array');
                    }
                    return array();
                }
                
                $where[] = "event_id IN ($event_ids_string)";
            }
            
            // Filter by revocation status if specified
            if (isset($args['revoked']) && $args['revoked'] !== null) {
                $where[] = "revoked = %d";
                $where_values[] = (int) $args['revoked'];
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Added revoked filter', $args['revoked']);
                }
            }
            
            // Build WHERE clause
            $where_clause = !empty($where) ? "WHERE " . implode(" AND ", $where) : "";
            
            // Build ORDER BY clause
            $order_by = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']);
            
            // Build LIMIT clause
            $limit_clause = '';
            if ($args['limit'] > 0) {
                $limit_clause = "LIMIT %d";
                $where_values[] = $args['limit'];
            } elseif ($args['per_page'] > 0) {
                $offset = ($args['page'] - 1) * $args['per_page'];
                $limit_clause = "LIMIT %d, %d";
                $where_values[] = $offset;
                $where_values[] = $args['per_page'];
            }
            
            // Check if the table exists before querying
            $table_name = $wpdb->prefix . 'hvac_certificates';
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
            
            if (!$table_exists) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Table does not exist: ' . $table_name);
                }
                return array();
            }
            
            // Add WHERE clause for attendee search if provided
            if (!empty($args['search_attendee'])) {
                $search_term = '%' . $wpdb->esc_like($args['search_attendee']) . '%';
                
                if (empty($where)) {
                    $where[] = "(
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_full_name' AND pm.meta_value LIKE %s
                        )
                        OR
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_email' AND pm.meta_value LIKE %s
                        )
                    )";
                    $where_values[] = $search_term;
                    $where_values[] = $search_term;
                } else {
                    $where[] = "AND (
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_full_name' AND pm.meta_value LIKE %s
                        )
                        OR
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_email' AND pm.meta_value LIKE %s
                        )
                    )";
                    $where_values[] = $search_term;
                    $where_values[] = $search_term;
                }
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Added attendee search filter', $args['search_attendee']);
                }
            }
            
            // Build WHERE clause
            $where_clause = !empty($where) ? "WHERE " . implode(" ", $where) : "";
            
            // Build final query
            $query = "SELECT * FROM {$wpdb->prefix}hvac_certificates $where_clause ORDER BY $order_by $limit_clause";
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Final query before prepare', $query);
                hvac_debug_log('Where values', $where_values);
            }
            
            // Prepare the query if we have where values
            if (!empty($where_values)) {
                $query = $wpdb->prepare($query, $where_values);
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Prepared query', $query);
                }
            }
            
            $results = $wpdb->get_results($query);
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Query executed, results count', is_array($results) ? count($results) : 'null');
                if ($wpdb->last_error) {
                    hvac_debug_log('Database error', $wpdb->last_error);
                }
            }
            
            return $results;
            
        } catch (Exception $e) {
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Exception in get_user_certificates', $e->getMessage());
            }
            return array();
        }
    }
    
    /**
     * Get the total count of certificates for a specific user.
     *
     * @param int   $user_id The user ID.
     * @param array $args    Additional query args.
     * 
     * @return int Total count of certificates.
     */
    public function get_user_certificate_count($user_id, $args = array()) {
        global $wpdb;
        
        if (function_exists('hvac_debug_log')) {
            hvac_debug_log('get_user_certificate_count called with user_id', $user_id);
            hvac_debug_log('get_user_certificate_count args', $args);
        }
        
        try {
            // Use direct database query to get user's event IDs (bypassing TEC interference)
            $event_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} 
                 WHERE post_type = %s 
                 AND post_author = %d 
                 AND post_status = 'publish'",
                'tribe_events',
                $user_id
            ));
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Direct DB query for events completed, count', count($event_ids));
            }
            
            if (empty($event_ids)) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('No events found for user, returning 0');
                }
                return 0;
            }
            
            // Build WHERE clause
            $where = array();
            $where_values = array();
            
            // Filter by event ID if specified
            if (!empty($args['event_id'])) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Filter by event ID', $args['event_id']);
                }
                
                // Check if the specified event belongs to the user
                if (in_array($args['event_id'], $event_ids)) {
                    $where[] = "event_id = %d";
                    $where_values[] = $args['event_id'];
                    
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Event belongs to user, adding to WHERE clause');
                    }
                } else {
                    // Event doesn't belong to this user
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Event does not belong to user, returning 0');
                    }
                    return 0;
                }
            } else {
                // Include all user's events
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Including all user events in query');
                }
                
                $event_ids_string = implode(',', array_map('intval', $event_ids));
                
                // Make sure we have event IDs
                if (empty($event_ids_string)) {
                    if (function_exists('hvac_debug_log')) {
                        hvac_debug_log('Empty event_ids_string, returning 0');
                    }
                    return 0;
                }
                
                $where[] = "event_id IN ($event_ids_string)";
            }
            
            // Filter by revocation status if specified
            if (isset($args['revoked']) && $args['revoked'] !== null) {
                $where[] = "revoked = %d";
                $where_values[] = (int) $args['revoked'];
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Added revoked filter', $args['revoked']);
                }
            }
            
            // Check if table exists
            $table_name = $wpdb->prefix . 'hvac_certificates';
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
            
            if (!$table_exists) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Table does not exist: ' . $table_name);
                }
                return 0;
            }
            
            // Build WHERE clause
            $where_clause = !empty($where) ? "WHERE " . implode(" AND ", $where) : "";
            
            // Add WHERE clause for attendee search if provided
            if (!empty($args['search_attendee'])) {
                $search_term = '%' . $wpdb->esc_like($args['search_attendee']) . '%';
                
                if (empty($where)) {
                    $where[] = "(
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_full_name' AND pm.meta_value LIKE %s
                        )
                        OR
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_email' AND pm.meta_value LIKE %s
                        )
                    )";
                    $where_values[] = $search_term;
                    $where_values[] = $search_term;
                } else {
                    $where[] = "AND (
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_full_name' AND pm.meta_value LIKE %s
                        )
                        OR
                        certificate_id IN (
                            SELECT c.certificate_id 
                            FROM {$wpdb->prefix}hvac_certificates c
                            JOIN {$wpdb->postmeta} pm ON c.attendee_id = pm.post_id 
                            WHERE pm.meta_key = '_tribe_tickets_email' AND pm.meta_value LIKE %s
                        )
                    )";
                    $where_values[] = $search_term;
                    $where_values[] = $search_term;
                }
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Added attendee search filter to count query', $args['search_attendee']);
                }
            }
            
            // Build WHERE clause
            $where_clause = !empty($where) ? "WHERE " . implode(" ", $where) : "";
            
            // Build final query
            $query = "SELECT COUNT(*) FROM {$wpdb->prefix}hvac_certificates $where_clause";
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Final query before prepare', $query);
            }
            
            // Prepare the query if we have where values
            if (!empty($where_values)) {
                $query = $wpdb->prepare($query, $where_values);
                
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Prepared query', $query);
                }
            }
            
            $count = $wpdb->get_var($query);
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Query executed, count result', $count);
                if ($wpdb->last_error) {
                    hvac_debug_log('Database error', $wpdb->last_error);
                }
            }
            
            return intval($count);
            
        } catch (Exception $e) {
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Exception in get_user_certificate_count', $e->getMessage());
            }
            return 0;
        }
    }
    
    /**
     * Get certificate statistics for a specific user.
     *
     * @param int $user_id The user ID.
     * 
     * @return array Certificate statistics.
     */
    public function get_user_certificate_stats($user_id) {
        global $wpdb;
        
        if (function_exists('hvac_debug_log')) {
            hvac_debug_log('get_user_certificate_stats called with user_id', $user_id);
        }
        
        // Default empty stats
        $empty_stats = array(
            'total' => 0,
            'active' => 0,
            'revoked' => 0,
            'emailed' => 0
        );
        
        try {
            // Check if table exists before querying
            $table_name = $wpdb->prefix . 'hvac_certificates';
            $table_exists = $wpdb->get_var("SHOW TABLES LIKE '$table_name'") === $table_name;
            
            if (!$table_exists) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Table does not exist: ' . $table_name);
                }
                return $empty_stats;
            }
            
            // Use direct database query to get user's event IDs (bypassing TEC interference)
            $event_ids = $wpdb->get_col($wpdb->prepare(
                "SELECT ID FROM {$wpdb->posts} 
                 WHERE post_type = %s 
                 AND post_author = %d 
                 AND post_status = 'publish'",
                'tribe_events',
                $user_id
            ));
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Direct DB query for events in stats, count', count($event_ids));
            }
            
            if (empty($event_ids)) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('No events found for user in stats, returning empty stats');
                }
                return $empty_stats;
            }
            
            // Create string of event IDs for query
            $event_ids_string = implode(',', array_map('intval', $event_ids));
            
            if (empty($event_ids_string)) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Empty event_ids_string, returning empty stats');
                }
                return $empty_stats;
            }
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Building statistics query for events', $event_ids_string);
            }
            
            $query = "SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN revoked = 0 THEN 1 ELSE 0 END) as active,
                SUM(CASE WHEN revoked = 1 THEN 1 ELSE 0 END) as revoked,
                SUM(CASE WHEN email_sent = 1 THEN 1 ELSE 0 END) as emailed
            FROM {$wpdb->prefix}hvac_certificates 
            WHERE event_id IN ($event_ids_string)";
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Statistics query', $query);
            }
            
            $result = $wpdb->get_row($query);
            
            if ($wpdb->last_error) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Database error in get_user_certificate_stats', $wpdb->last_error);
                }
                return $empty_stats;
            }
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Query executed, result', $result);
            }
            
            if (is_null($result)) {
                if (function_exists('hvac_debug_log')) {
                    hvac_debug_log('Null result returned, using empty stats');
                }
                return $empty_stats;
            }
            
            $stats = array(
                'total' => intval($result->total),
                'active' => intval($result->active),
                'revoked' => intval($result->revoked),
                'emailed' => intval($result->emailed)
            );
            
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Returning stats', $stats);
            }
            
            return $stats;
        } catch (Exception $e) {
            if (function_exists('hvac_debug_log')) {
                hvac_debug_log('Exception in get_user_certificate_stats', $e->getMessage());
            }
            return $empty_stats;
        }
    }

    /**
     * Get certificate file path.
     *
     * @param int $certificate_id The certificate ID.
     * 
     * @return string|false The file path if found, false otherwise.
     */
    public function get_certificate_file_path($certificate_id) {
        $certificate = $this->get_certificate($certificate_id);
        
        if (!$certificate) {
            return false;
        }
        
        // Get uploads directory
        $upload_dir = wp_upload_dir();
        $base_dir = $upload_dir['basedir'];
        
        // Construct full path
        $full_path = $base_dir . '/' . $certificate->file_path;
        
        if (file_exists($full_path)) {
            return $full_path;
        }
        
        return false;
    }

    /**
     * Get certificate file URL.
     *
     * @param int $certificate_id The certificate ID.
     * 
     * @return string|false The file URL if found, false otherwise.
     */
    public function get_certificate_url($certificate_id) {
        // Create a secure URL with nonce for downloading
        $url = add_query_arg(
            array(
                'action' => 'hvac_download_certificate',
                'certificate_id' => $certificate_id,
                'nonce' => wp_create_nonce('download_certificate_' . $certificate_id)
            ),
            admin_url('admin-ajax.php')
        );
        
        return $url;
    }

    /**
     * Check if an attendee already has a certificate for an event.
     *
     * @param int $event_id    The event ID.
     * @param int $attendee_id The attendee ID.
     * 
     * @return bool True if a certificate exists, false otherwise.
     */
    public function certificate_exists($event_id, $attendee_id) {
        $certificate = $this->get_certificate_by_attendee($event_id, $attendee_id);
        return !empty($certificate);
    }

    /**
     * Delete a certificate record and its file.
     *
     * @param int $certificate_id The certificate ID.
     * 
     * @return bool True if successful, false otherwise.
     */
    public function delete_certificate($certificate_id) {
        global $wpdb;
        
        // Get certificate to get file path
        $certificate = $this->get_certificate($certificate_id);
        
        if (!$certificate) {
            return false;
        }
        
        // Delete file if it exists
        if (!empty($certificate->file_path)) {
            $upload_dir = wp_upload_dir();
            $full_path = $upload_dir['basedir'] . '/' . $certificate->file_path;
            
            if (file_exists($full_path)) {
                unlink($full_path);
            }
        }
        
        // Delete from database
        $result = $wpdb->delete(
            $wpdb->prefix . 'hvac_certificates',
            array('certificate_id' => $certificate_id),
            array('%d')
        );
        
        return $result !== false;
    }
}