<?php

/**
 * Members List Table
 *
 * @package KarateClubManager
 */
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
if ( !class_exists( 'WP_List_Table' ) ) {
    require_once ABSPATH . 'wp-admin/includes/class-wp-list-table.php';
}
/**
 * Members List Table class
 */
class MACM_Admin_Members_List_Table extends WP_List_Table {
    /**
     * Constructor
     */
    public function __construct() {
        parent::__construct( array(
            'singular' => 'member',
            'plural'   => 'members',
            'ajax'     => false,
        ) );
    }

    /**
     * Get columns
     *
     * @return array
     */
    public function get_columns() {
        $columns = array(
            'cb'              => '<input type="checkbox" id="macm-select-all-members" />',
            'photo'           => __( 'Photo', 'martial-arts-club-manager' ),
            'full_name'       => __( 'Name', 'martial-arts-club-manager' ),
            'date_of_birth'   => __( 'Date of Birth', 'martial-arts-club-manager' ),
            'age'             => __( 'Age', 'martial-arts-club-manager' ),
            'belt_color'      => __( 'Belt Color', 'martial-arts-club-manager' ),
            'membership_type' => __( 'Membership', 'martial-arts-club-manager' ),
        );
        $unit_system = get_option( 'macm_unit_system', 'metric' );
        $weight_label = ( 'imperial' === $unit_system ? __( 'Weight (lbs)', 'martial-arts-club-manager' ) : __( 'Weight (kg)', 'martial-arts-club-manager' ) );
        $height_label = ( 'imperial' === $unit_system ? __( 'Height (ft/in)', 'martial-arts-club-manager' ) : __( 'Height (cm)', 'martial-arts-club-manager' ) );
        $columns['weight'] = $weight_label;
        $columns['height'] = $height_label;
        $columns['license_number'] = __( 'License Number', 'martial-arts-club-manager' );
        $columns['license_expiration'] = __( 'License Expiration', 'martial-arts-club-manager' );
        $columns['user_name'] = __( 'User', 'martial-arts-club-manager' );
        $columns['actions'] = __( 'Actions', 'martial-arts-club-manager' );
        return $columns;
    }

    /**
     * Get sortable columns
     *
     * @return array
     */
    public function get_sortable_columns() {
        $sortable = array(
            'full_name'          => array('full_name', false),
            'date_of_birth'      => array('date_of_birth', false),
            'belt_color'         => array('belt_color', false),
            'membership_type'    => array('membership_type_name', false),
            'license_expiration' => array('license_expiration', false),
            'user_name'          => array('user_name', false),
        );
        return $sortable;
    }

    /**
     * Get bulk actions
     *
     * @return array Bulk actions.
     */
    public function get_bulk_actions() {
        return array(
            'archive'  => __( 'Archive', 'martial-arts-club-manager' ),
            'activate' => __( 'Activate', 'martial-arts-club-manager' ),
        );
    }

    /**
     * Process bulk actions
     */
    public function process_bulk_action() {
        $action = $this->current_action();
        if ( !$action || !in_array( $action, array('archive', 'activate'), true ) ) {
            return;
        }
        // Verify nonce (check both GET and POST for form compatibility).
        $nonce = ( isset( $_REQUEST['_wpnonce'] ) ? sanitize_text_field( wp_unslash( $_REQUEST['_wpnonce'] ) ) : '' );
        if ( !wp_verify_nonce( $nonce, 'bulk-members' ) ) {
            return;
        }
        // Check capability.
        if ( !current_user_can( 'manage_macm_members' ) ) {
            wp_die( esc_html__( 'You do not have permission to perform this action.', 'martial-arts-club-manager' ) );
        }
        // Get selected member IDs (check both GET and POST for form compatibility).
        $member_ids = ( isset( $_REQUEST['member_ids'] ) ? array_map( 'absint', wp_unslash( $_REQUEST['member_ids'] ) ) : array() );
        $member_ids = array_filter( $member_ids );
        // Remove zeros.
        if ( empty( $member_ids ) ) {
            return;
        }
        global $wpdb;
        $count = 0;
        $new_status = ( 'archive' === $action ? 'inactive' : 'active' );
        foreach ( $member_ids as $member_id ) {
            $updated = $wpdb->update(
                $wpdb->prefix . 'macm_members',
                array(
                    'status' => $new_status,
                ),
                array(
                    'id' => $member_id,
                ),
                array('%s'),
                array('%d')
            );
            if ( $updated ) {
                wp_cache_delete( 'macm_member_status_' . $member_id, 'macm' );
                ++$count;
            }
        }
        if ( $count > 0 ) {
            $message_type = ( 'archive' === $action ? 'archived' : 'activated' );
            if ( 'archive' === $action ) {
                $message = sprintf( 
                    /* translators: %d: number of members */
                    _n(
                        '%d member archived.',
                        '%d members archived.',
                        $count,
                        'martial-arts-club-manager'
                    ),
                    $count
                 );
            } else {
                $message = sprintf( 
                    /* translators: %d: number of members */
                    _n(
                        '%d member activated.',
                        '%d members activated.',
                        $count,
                        'martial-arts-club-manager'
                    ),
                    $count
                 );
            }
            add_settings_error(
                'macm_members',
                'members_' . $message_type,
                $message,
                'success'
            );
        }
    }

    /**
     * Checkbox column
     *
     * @param object $item Member item.
     * @return string Checkbox HTML.
     */
    public function column_cb( $item ) {
        return sprintf( '<input type="checkbox" name="member_ids[]" value="%s" />', esc_attr( $item->id ) );
    }

    /**
     * Extra controls to be displayed between bulk actions and pagination
     *
     * @param string $which Top or bottom.
     */
    protected function extra_tablenav( $which ) {
        if ( 'top' !== $which ) {
            return;
        }
        global $wpdb;
        // Get all membership types - use cache for dropdown performance.
        $membership_cache_key = 'macm_membership_types_active';
        $membership_types = wp_cache_get( $membership_cache_key, 'macm' );
        if ( false === $membership_types ) {
            $membership_types = $wpdb->get_results( "SELECT id, type_name FROM {$wpdb->prefix}macm_membership_types WHERE is_active = 1 ORDER BY type_name" );
            wp_cache_set(
                $membership_cache_key,
                $membership_types,
                'macm',
                300
            );
        }
        // Get all clubs - use cache for dropdown performance.
        $clubs_cache_key = 'macm_clubs_list';
        $clubs = wp_cache_get( $clubs_cache_key, 'macm' );
        if ( false === $clubs ) {
            $clubs = $wpdb->get_results( "SELECT id, club_name FROM {$wpdb->prefix}macm_clubs ORDER BY club_name" );
            wp_cache_set(
                $clubs_cache_key,
                $clubs,
                'macm',
                300
            );
        }
        // Get all groups - use cache for dropdown performance.
        $groups_cache_key = 'macm_groups_list_active';
        $groups = wp_cache_get( $groups_cache_key, 'macm' );
        if ( false === $groups ) {
            $groups = $wpdb->get_results( "SELECT id, group_name FROM {$wpdb->prefix}macm_groups WHERE is_active = 1 ORDER BY group_name" );
            wp_cache_set(
                $groups_cache_key,
                $groups,
                'macm',
                300
            );
        }
        // Get all belt colors.
        $belt_colors = MACM_Member::get_belt_colors();
        // Verify nonce when filter form was submitted.
        $filter_nonce_valid = true;
        $filter_action_input = filter_input( INPUT_GET, 'filter_action', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
        if ( !empty( $filter_action_input ) ) {
            $filter_nonce_input = ( isset( $_GET['macm_filter_nonce'] ) ? sanitize_text_field( wp_unslash( $_GET['macm_filter_nonce'] ) ) : '' );
            if ( !wp_verify_nonce( $filter_nonce_input, 'macm_members_filter' ) ) {
                $filter_nonce_valid = false;
            }
        }
        // Get filter parameters - read-only display filtering with validated values.
        // Only apply filters if nonce is valid (or no filter was submitted).
        $membership_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'membership_type', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $current_membership = ( !empty( $membership_input ) ? sanitize_text_field( $membership_input ) : '' );
        $club_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'club_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $current_club = ( !empty( $club_input ) ? absint( $club_input ) : '' );
        $group_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'group_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $current_group = ( !empty( $group_input ) ? absint( $group_input ) : '' );
        $belt_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'belt_color', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $current_belt = ( !empty( $belt_input ) ? sanitize_text_field( $belt_input ) : '' );
        $search_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'macm_search', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $current_search = ( !empty( $search_input ) ? sanitize_text_field( $search_input ) : '' );
        $allowed_statuses = array('active', 'inactive', 'all');
        $status_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'member_status', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $status_sanitized = ( !empty( $status_input ) ? sanitize_key( $status_input ) : 'active' );
        $current_status = ( in_array( $status_sanitized, $allowed_statuses, true ) ? $status_sanitized : 'active' );
        ?>
		<div class="alignleft actions">
			<input type="search" name="macm_search" id="macm-member-search" value="<?php 
        echo esc_attr( $current_search );
        ?>" placeholder="<?php 
        esc_attr_e( 'Search by Name or User...', 'martial-arts-club-manager' );
        ?>" style="width: 200px;" />

			<select name="member_status" id="filter-by-status">
				<option value="active" <?php 
        selected( $current_status, 'active' );
        ?>><?php 
        esc_html_e( 'Active', 'martial-arts-club-manager' );
        ?></option>
				<option value="inactive" <?php 
        selected( $current_status, 'inactive' );
        ?>><?php 
        esc_html_e( 'Archived', 'martial-arts-club-manager' );
        ?></option>
				<option value="all" <?php 
        selected( $current_status, 'all' );
        ?>><?php 
        esc_html_e( 'All Statuses', 'martial-arts-club-manager' );
        ?></option>
			</select>

			<select name="membership_type" id="filter-by-membership">
				<option value=""><?php 
        esc_html_e( 'All Membership Types', 'martial-arts-club-manager' );
        ?></option>
				<?php 
        foreach ( $membership_types as $type ) {
            ?>
					<option value="<?php 
            echo esc_attr( $type->id );
            ?>" <?php 
            selected( $current_membership, $type->id );
            ?>>
						<?php 
            echo esc_html( $type->type_name );
            ?>
					</option>
				<?php 
        }
        ?>
				<option value="none" <?php 
        selected( $current_membership, 'none' );
        ?>>
					<?php 
        esc_html_e( 'No Membership', 'martial-arts-club-manager' );
        ?>
				</option>
			</select>

			<?php 
        ?>

			<select name="belt_color" id="filter-by-belt">
				<option value=""><?php 
        esc_html_e( 'All Belt Colors', 'martial-arts-club-manager' );
        ?></option>
				<?php 
        foreach ( $belt_colors as $value => $label ) {
            ?>
					<option value="<?php 
            echo esc_attr( $value );
            ?>" <?php 
            selected( $current_belt, $value );
            ?>>
						<?php 
            echo esc_html( $label );
            ?>
					</option>
				<?php 
        }
        ?>
			</select>

			<?php 
        wp_nonce_field( 'macm_members_filter', 'macm_filter_nonce', false );
        ?>
			<?php 
        submit_button(
            __( 'Filter', 'martial-arts-club-manager' ),
            '',
            'filter_action',
            false
        );
        ?>
		</div>
		<div class="alignleft actions" style="margin-left: 15px;">
			<a href="<?php 
        echo esc_url( admin_url( 'admin.php?page=kcm-members' ) );
        ?>" class="button">
				<span class="dashicons dashicons-dismiss" style="margin-top: 3px;"></span>
				<?php 
        esc_html_e( 'Clear Filter', 'martial-arts-club-manager' );
        ?>
			</a>
			<a href="<?php 
        echo esc_url( $this->get_export_url() );
        ?>" class="button">
				<span class="dashicons dashicons-download" style="margin-top: 3px;"></span>
				<?php 
        esc_html_e( 'Export to CSV', 'martial-arts-club-manager' );
        ?>
			</a>
		</div>
		<?php 
    }

    /**
     * Prepare items for display
     */
    public function prepare_items() {
        // Handle bulk actions first.
        $this->process_bulk_action();
        global $wpdb;
        $per_page = 20;
        $current_page = $this->get_pagenum();
        $offset = ($current_page - 1) * $per_page;
        // Get orderby and order - read-only sorting parameters with validation.
        $allowed_orderby = array(
            'full_name',
            'date_of_birth',
            'belt_color',
            'membership_type_name',
            'club_name',
            'license_expiration',
            'user_name'
        );
        $orderby_raw = filter_input( INPUT_GET, 'orderby', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
        $orderby_input = ( !empty( $orderby_raw ) ? sanitize_key( $orderby_raw ) : 'full_name' );
        $orderby = ( in_array( $orderby_input, $allowed_orderby, true ) ? $orderby_input : 'full_name' );
        $allowed_order = array('asc', 'desc');
        $order_raw = filter_input( INPUT_GET, 'order', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
        $order_input = ( !empty( $order_raw ) ? sanitize_key( $order_raw ) : 'asc' );
        $order = ( in_array( strtolower( $order_input ), $allowed_order, true ) ? strtoupper( $order_input ) : 'ASC' );
        // Check if filter form was submitted - verify nonce for security.
        $filter_nonce_valid = true;
        $filter_action_input = filter_input( INPUT_GET, 'filter_action', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
        if ( !empty( $filter_action_input ) ) {
            $filter_nonce_input = ( isset( $_GET['macm_filter_nonce'] ) ? sanitize_text_field( wp_unslash( $_GET['macm_filter_nonce'] ) ) : '' );
            if ( !wp_verify_nonce( $filter_nonce_input, 'macm_members_filter' ) ) {
                $filter_nonce_valid = false;
            }
        }
        // Determine filter values with flags for static WHERE pattern - read-only filtering with validation.
        // Status filter (default to 'active' unless 'all' specified).
        // Only apply filters if nonce is valid (or no filter was submitted).
        $allowed_member_statuses = array('active', 'inactive', 'all');
        $member_status_raw_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'member_status', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $member_status_input = ( !empty( $member_status_raw_input ) ? sanitize_key( $member_status_raw_input ) : 'active' );
        $member_status_raw = ( in_array( $member_status_input, $allowed_member_statuses, true ) ? $member_status_input : 'active' );
        $apply_status = ( 'all' !== $member_status_raw ? 1 : 0 );
        $status_value = ( $apply_status ? $member_status_raw : '' );
        // Membership type filter (3 modes: 0=off, 1=filter by ID, 2=filter for NULL).
        $apply_membership_id = 0;
        $membership_id_value = 0;
        $apply_membership_null = 0;
        $membership_type_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'membership_type', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $membership_type_input = ( !empty( $membership_type_raw ) ? sanitize_text_field( $membership_type_raw ) : '' );
        if ( !empty( $membership_type_input ) ) {
            if ( 'none' === $membership_type_input ) {
                $apply_membership_null = 1;
            } elseif ( is_numeric( $membership_type_input ) ) {
                $apply_membership_id = 1;
                $membership_id_value = absint( $membership_type_input );
            }
        }
        // Club filter.
        $club_id_raw_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'club_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $club_id_raw = ( !empty( $club_id_raw_input ) ? absint( $club_id_raw_input ) : 0 );
        $apply_club = ( $club_id_raw > 0 ? 1 : 0 );
        $club_id_value = $club_id_raw;
        // Belt color filter.
        $belt_color_raw_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'belt_color', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $belt_color_raw = ( !empty( $belt_color_raw_input ) ? sanitize_text_field( $belt_color_raw_input ) : '' );
        $apply_belt = ( !empty( $belt_color_raw ) ? 1 : 0 );
        $belt_color_value = $belt_color_raw;
        // Group filter (uses junction table macm_member_groups).
        $group_id_raw_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'group_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $group_id_raw = ( !empty( $group_id_raw_input ) ? absint( $group_id_raw_input ) : 0 );
        $apply_group = ( $group_id_raw > 0 ? 1 : 0 );
        $group_id_value = $group_id_raw;
        // Free text search filter (searches Name and User display name).
        $search_term_raw_input = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'macm_search', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $search_term_raw = ( !empty( $search_term_raw_input ) ? sanitize_text_field( $search_term_raw_input ) : '' );
        $apply_search = ( !empty( $search_term_raw ) ? 1 : 0 );
        // Add wildcards for LIKE search.
        $search_term_value = '%' . $wpdb->esc_like( $search_term_raw ) . '%';
        // Build cache key from filter parameters.
        $cache_key_parts = array(
            'members_list',
            $member_status_raw,
            $membership_type_input,
            $club_id_raw,
            $belt_color_raw,
            $group_id_raw,
            $search_term_raw,
            $orderby,
            $order,
            $current_page
        );
        $cache_key = 'macm_' . md5( implode( '_', $cache_key_parts ) );
        // Try to get from cache.
        $cached_data = wp_cache_get( $cache_key, 'macm' );
        if ( false !== $cached_data ) {
            $this->items = $cached_data['members'];
            $this->set_pagination_args( array(
                'total_items' => $cached_data['total'],
                'per_page'    => $per_page,
                'total_pages' => ceil( $cached_data['total'] / $per_page ),
            ) );
            return;
        }
        // Get total items for pagination - with caching.
        $count_cache_key = 'macm_members_count_' . md5( implode( '_', array(
            $apply_status,
            $status_value,
            $apply_membership_id,
            $membership_id_value,
            $apply_membership_null,
            $apply_club,
            $club_id_value,
            $apply_belt,
            $belt_color_value,
            $apply_group,
            $group_id_value,
            $apply_search,
            $search_term_raw
        ) ) );
        $total_items = wp_cache_get( $count_cache_key, 'macm' );
        if ( false === $total_items ) {
            $total_items = (int) $wpdb->get_var( $wpdb->prepare(
                "SELECT COUNT(DISTINCT m.id)\n\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))",
                $apply_status,
                $status_value,
                $apply_membership_id,
                $membership_id_value,
                $apply_membership_null,
                $apply_club,
                $club_id_value,
                $apply_belt,
                $belt_color_value,
                $apply_group,
                $group_id_value,
                $apply_search,
                $search_term_value,
                $search_term_value
            ) );
            wp_cache_set(
                $count_cache_key,
                $total_items,
                'macm',
                60
            );
        }
        // Execute members query with static WHERE and ORDER BY based on validated whitelist.
        $members = $this->execute_members_query(
            $orderby,
            $order,
            $apply_status,
            $status_value,
            $apply_membership_id,
            $membership_id_value,
            $apply_membership_null,
            $apply_club,
            $club_id_value,
            $apply_belt,
            $belt_color_value,
            $apply_group,
            $group_id_value,
            $apply_search,
            $search_term_value,
            $per_page,
            $offset
        );
        // Get first and last names from user meta and groups for each member.
        foreach ( $members as $member ) {
            $first_name = get_user_meta( $member->user_id, 'first_name', true );
            $last_name = get_user_meta( $member->user_id, 'last_name', true );
            if ( $first_name && $last_name ) {
                $member->user_full_name = $first_name . ' ' . $last_name;
            } elseif ( $first_name ) {
                $member->user_full_name = $first_name;
            } elseif ( $last_name ) {
                $member->user_full_name = $last_name;
            } else {
                $member->user_full_name = $member->user_name;
            }
            // Get member's groups with caching.
            $group_cache_key = 'macm_member_groups_' . $member->id;
            $member->group_ids = wp_cache_get( $group_cache_key, 'macm' );
            if ( false === $member->group_ids ) {
                $member->group_ids = $wpdb->get_col( $wpdb->prepare( "SELECT group_id FROM {$wpdb->prefix}macm_member_groups WHERE member_id = %d", $member->id ) );
                wp_cache_set(
                    $group_cache_key,
                    $member->group_ids,
                    'macm',
                    300
                );
            }
            // Get group names for display using individual cached queries (avoids dynamic IN clause).
            if ( !empty( $member->group_ids ) ) {
                $group_names = array();
                foreach ( $member->group_ids as $group_id ) {
                    $group_id = absint( $group_id );
                    $group_name_cached = wp_cache_get( 'macm_group_name_' . $group_id, 'macm' );
                    if ( false === $group_name_cached ) {
                        $group_name_cached = $wpdb->get_var( $wpdb->prepare( "SELECT group_name FROM {$wpdb->prefix}macm_groups WHERE id = %d", $group_id ) );
                        wp_cache_set(
                            'macm_group_name_' . $group_id,
                            $group_name_cached,
                            'macm',
                            300
                        );
                    }
                    if ( $group_name_cached ) {
                        $group_names[] = $group_name_cached;
                    }
                }
                $member->group_names = $group_names;
            } else {
                $member->group_names = array();
            }
        }
        // Cache the results.
        wp_cache_set(
            $cache_key,
            array(
                'members' => $members,
                'total'   => $total_items,
            ),
            'macm',
            60
        );
        $this->items = $members;
        $this->set_pagination_args( array(
            'total_items' => $total_items,
            'per_page'    => $per_page,
            'total_pages' => ceil( $total_items / $per_page ),
        ) );
    }

    /**
     * Execute members query with static ORDER BY based on validated whitelist.
     *
     * This method uses a switch statement to select literal ORDER BY clauses,
     * avoiding dynamic string concatenation that triggers static analysis warnings.
     *
     * @param string $orderby              Validated orderby column name.
     * @param string $order                Validated order direction (ASC/DESC).
     * @param int    $apply_status         Flag for status filter (0=skip, 1=apply).
     * @param string $status_value         Status value to filter by.
     * @param int    $apply_membership_id  Flag for membership ID filter.
     * @param int    $membership_id_value  Membership type ID to filter by.
     * @param int    $apply_membership_null Flag for NULL membership filter.
     * @param int    $apply_club           Flag for club filter.
     * @param int    $club_id_value        Club ID to filter by.
     * @param int    $apply_belt           Flag for belt color filter.
     * @param string $belt_color_value     Belt color to filter by.
     * @param int    $apply_group          Flag for group filter.
     * @param int    $group_id_value       Group ID to filter by.
     * @param int    $apply_search         Flag for search filter.
     * @param string $search_term_value    Search term with wildcards for LIKE.
     * @param int    $per_page             Items per page.
     * @param int    $offset               Query offset.
     * @return array Array of member objects.
     */
    private function execute_members_query(
        $orderby,
        $order,
        $apply_status,
        $status_value,
        $apply_membership_id,
        $membership_id_value,
        $apply_membership_null,
        $apply_club,
        $club_id_value,
        $apply_belt,
        $belt_color_value,
        $apply_group,
        $group_id_value,
        $apply_search,
        $search_term_value,
        $per_page,
        $offset
    ) {
        global $wpdb;
        // Build cache key from all parameters.
        $query_cache_key = 'macm_members_query_' . md5( implode( '_', array(
            $orderby,
            $order,
            $apply_status,
            $status_value,
            $apply_membership_id,
            $membership_id_value,
            $apply_membership_null,
            $apply_club,
            $club_id_value,
            $apply_belt,
            $belt_color_value,
            $apply_group,
            $group_id_value,
            $apply_search,
            $search_term_value,
            $per_page,
            $offset
        ) ) );
        $cached_results = wp_cache_get( $query_cache_key, 'macm' );
        if ( false !== $cached_results ) {
            return $cached_results;
        }
        // Common query parameters for all ORDER BY variants.
        $params = array(
            $apply_status,
            $status_value,
            $apply_membership_id,
            $membership_id_value,
            $apply_membership_null,
            $apply_club,
            $club_id_value,
            $apply_belt,
            $belt_color_value,
            $apply_group,
            $group_id_value,
            $apply_search,
            $search_term_value,
            $search_term_value,
            // Twice for both full_name and display_name LIKE.
            $per_page,
            $offset,
        );
        // Use switch to select literal ORDER BY clause based on validated whitelist values.
        // Each case uses a fully static query string to satisfy static analysis.
        $is_desc = 'DESC' === $order;
        $results = array();
        switch ( $orderby ) {
            case 'date_of_birth':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.date_of_birth DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.date_of_birth ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            case 'belt_color':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.belt_color DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.belt_color ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            case 'membership_type_name':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY mt.type_name DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY mt.type_name ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            case 'club_name':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY c.club_name DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY c.club_name ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            case 'license_expiration':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.license_expiration DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.license_expiration ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            case 'user_name':
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY u.display_name DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY u.display_name ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
            default:
                // full_name.
                if ( $is_desc ) {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.full_name DESC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                } else {
                    $results = $wpdb->get_results( $wpdb->prepare(
                        "SELECT DISTINCT m.*, u.display_name as user_name, u.user_email, c.club_name, mt.type_name as membership_type_name\n\t\t\t\t\t\t\tFROM {$wpdb->prefix}macm_members m\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->users} u ON m.user_id = u.ID\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_clubs c ON m.club_id = c.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_membership_types mt ON m.membership_type_id = mt.id\n\t\t\t\t\t\t\tLEFT JOIN {$wpdb->prefix}macm_member_groups mg ON m.id = mg.member_id\n\t\t\t\t\t\t\tWHERE (%d = 0 OR m.status = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.membership_type_id IS NULL)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.club_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR m.belt_color = %s)\n\t\t\t\t\t\t\t  AND (%d = 0 OR mg.group_id = %d)\n\t\t\t\t\t\t\t  AND (%d = 0 OR (m.full_name LIKE %s OR u.display_name LIKE %s))\n\t\t\t\t\t\t\tORDER BY m.full_name ASC\n\t\t\t\t\t\t\tLIMIT %d OFFSET %d",
                        $params[0],
                        $params[1],
                        $params[2],
                        $params[3],
                        $params[4],
                        $params[5],
                        $params[6],
                        $params[7],
                        $params[8],
                        $params[9],
                        $params[10],
                        $params[11],
                        $params[12],
                        $params[13],
                        $params[14],
                        $params[15]
                    ) );
                }
                break;
        }
        // Cache and return results.
        wp_cache_set(
            $query_cache_key,
            $results,
            'macm',
            60
        );
        return $results;
    }

    /**
     * Default column output
     *
     * @param object $item Item.
     * @param string $column_name Column name.
     * @return string
     */
    public function column_default( $item, $column_name ) {
        switch ( $column_name ) {
            case 'date_of_birth':
                return esc_html( MACM_Member::format_date( $item->date_of_birth ) );
            case 'age':
                return esc_html( MACM_Member::calculate_age( $item->date_of_birth ) ) . ' ' . __( 'years', 'martial-arts-club-manager' );
            case 'belt_color':
                $belt_colors = MACM_Member::get_belt_colors();
                return esc_html( ( isset( $belt_colors[$item->belt_color] ) ? $belt_colors[$item->belt_color] : $item->belt_color ) );
            case 'membership_type':
                if ( $item->membership_type_name ) {
                    return '<span class="kcm-badge kcm-badge-' . esc_attr( sanitize_title( $item->membership_type_name ) ) . '">' . esc_html( $item->membership_type_name ) . '</span>';
                }
                return '<span style="color: #6B7280;">—</span>';
            case 'club_name':
                return ( $item->club_name ? esc_html( $item->club_name ) : '<span style="color: #6B7280;">—</span>' );
            case 'groups':
                if ( !empty( $item->group_names ) ) {
                    return esc_html( implode( ', ', $item->group_names ) );
                }
                return '<span style="color: #6B7280;">—</span>';
            case 'weight':
                $formatted_weight = MACM_Member::format_weight( $item->weight );
                return ( $formatted_weight ? esc_html( $formatted_weight ) : '<span style="color: #6B7280;">—</span>' );
            case 'height':
                $formatted_height = MACM_Member::format_height( $item->height );
                return ( $formatted_height ? esc_html( $formatted_height ) : '<span style="color: #6B7280;">—</span>' );
            case 'license_number':
                return ( $item->license_number ? esc_html( $item->license_number ) : '<span style="color: #6B7280;">—</span>' );
            case 'license_expiration':
                if ( !$item->license_expiration ) {
                    return '<span style="color: #6B7280;">—</span>';
                }
                $expiration_date = strtotime( $item->license_expiration );
                $today = strtotime( 'today' );
                $one_month_from_now = strtotime( '+1 month' );
                $formatted_date = esc_html( MACM_Member::format_date( $item->license_expiration ) );
                // Expired - red.
                if ( $expiration_date < $today ) {
                    return '<span class="kcm-license-expired">' . $formatted_date . '</span>';
                }
                // Expiring within 30 days - yellow.
                if ( $expiration_date <= $one_month_from_now ) {
                    return '<span class="kcm-license-expiring-soon">' . $formatted_date . '</span>';
                }
                // Valid - normal.
                return $formatted_date;
            default:
                return '';
        }
    }

    /**
     * Photo column
     *
     * @param object $item Item.
     * @return string
     */
    public function column_photo( $item ) {
        if ( $item->photo_id ) {
            return wp_get_attachment_image(
                $item->photo_id,
                array(50, 50),
                false,
                array(
                    'style' => 'border-radius: 50%;',
                )
            );
        } else {
            return '<span class="dashicons dashicons-admin-users" style="font-size: 50px; color: #D1D5DB;"></span>';
        }
    }

    /**
     * Full name column
     *
     * @param object $item Item.
     * @return string
     */
    public function column_full_name( $item ) {
        return '<strong>' . esc_html( $item->full_name ) . '</strong>';
    }

    /**
     * User name column
     *
     * @param object $item Item.
     * @return string
     */
    public function column_user_name( $item ) {
        return sprintf(
            '<a href="#" class="kcm-view-user-info" data-user-id="%d" data-user-full-name="%s" data-user-email="%s" style="text-decoration: none; color: #B11226;">%s</a>',
            esc_attr( $item->user_id ),
            esc_attr( $item->user_full_name ),
            esc_attr( $item->user_email ),
            esc_html( $item->user_name )
        );
    }

    /**
     * Actions column
     *
     * @param object $item Item.
     * @return string
     */
    public function column_actions( $item ) {
        $output = '<div class="kcm-member-actions">';
        // View Details action.
        $output .= sprintf( '<a href="#" class="kcm-admin-view-member-details kcm-action-link" data-member-id="%d">%s</a>', esc_attr( $item->id ), esc_html__( 'View Details', 'martial-arts-club-manager' ) );
        // Edit action.
        $output .= sprintf( '<a href="#" class="kcm-admin-edit-member kcm-action-link" data-member-id="%d">%s</a>', esc_attr( $item->id ), esc_html__( 'Edit', 'martial-arts-club-manager' ) );
        // Download Photo action.
        if ( $item->photo_id ) {
            $photo_url = wp_get_attachment_url( $item->photo_id );
            if ( $photo_url ) {
                $output .= sprintf( '<a href="%s" download target="_blank" class="kcm-action-link">%s</a>', esc_url( $photo_url ), esc_html__( 'Download Photo', 'martial-arts-club-manager' ) );
            }
        }
        // Archive/Activate action.
        $is_active = isset( $item->status ) && 'active' === $item->status || !isset( $item->status );
        if ( $is_active ) {
            $output .= sprintf( '<a href="#" class="kcm-admin-archive-member kcm-action-link" data-member-id="%d">%s</a>', esc_attr( $item->id ), esc_html__( 'Archive', 'martial-arts-club-manager' ) );
        } else {
            $output .= sprintf( '<a href="#" class="kcm-admin-activate-member kcm-action-link" data-member-id="%d">%s</a>', esc_attr( $item->id ), esc_html__( 'Activate', 'martial-arts-club-manager' ) );
        }
        $output .= '</div>';
        return $output;
    }

    /**
     * Message to display when no items found
     */
    public function no_items() {
        esc_html_e( 'No members found. Members can add themselves via the member area.', 'martial-arts-club-manager' );
    }

    /**
     * Generate the table rows
     *
     * @since 1.0.0
     */
    public function display_rows() {
        foreach ( $this->items as $item ) {
            $this->single_row( $item );
        }
    }

    /**
     * Generate a single table row
     *
     * @param object $item Current item.
     */
    public function single_row( $item ) {
        echo '<tr>';
        // Manually output each column.
        foreach ( $this->get_columns() as $column_name => $column_display_name ) {
            $classes = 'column-' . sanitize_html_class( $column_name );
            // Add check-column class for checkbox column.
            if ( 'cb' === $column_name ) {
                $classes .= ' check-column';
            }
            // Get column content.
            if ( method_exists( $this, 'column_' . $column_name ) ) {
                $content = call_user_func( array($this, 'column_' . $column_name), $item );
            } else {
                $content = $this->column_default( $item, $column_name );
            }
            // Checkbox column needs special escaping - wp_kses_post strips input elements.
            if ( 'cb' === $column_name ) {
                // Allow checkbox input HTML only - safe because column_cb uses esc_attr on values.
                $allowed_checkbox_html = array(
                    'input' => array(
                        'type'  => array(),
                        'name'  => array(),
                        'value' => array(),
                    ),
                );
                echo '<td class="' . esc_attr( $classes ) . '">' . wp_kses( $content, $allowed_checkbox_html ) . '</td>';
            } else {
                echo '<td class="' . esc_attr( $classes ) . '">' . wp_kses_post( $content ) . '</td>';
            }
        }
        echo '</tr>';
    }

    /**
     * Display the table
     */
    public function display() {
        $this->display_tablenav( 'top' );
        echo '<table class="wp-list-table widefat fixed striped">';
        // Manual header output.
        echo '<thead><tr>';
        foreach ( $this->get_columns() as $column_key => $column_display_name ) {
            $class = array('manage-column', "column-{$column_key}");
            // Check if sortable.
            $sortable_columns = $this->get_sortable_columns();
            if ( isset( $sortable_columns[$column_key] ) ) {
                $class[] = 'sortable';
                $order = 'asc';
                // Get sorting parameters - read-only display with validation.
                $orderby_filter_input = filter_input( INPUT_GET, 'orderby', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
                $current_orderby_input = ( !empty( $orderby_filter_input ) ? sanitize_key( $orderby_filter_input ) : '' );
                $current_orderby = ( isset( $sortable_columns[$current_orderby_input] ) ? $current_orderby_input : '' );
                $allowed_order_values = array('asc', 'desc');
                $order_filter_input = filter_input( INPUT_GET, 'order', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
                $current_order_input = ( !empty( $order_filter_input ) ? sanitize_key( $order_filter_input ) : 'asc' );
                $current_order = ( in_array( $current_order_input, $allowed_order_values, true ) ? $current_order_input : 'asc' );
                if ( $column_key === $current_orderby ) {
                    $order = ( 'asc' === $current_order ? 'desc' : 'asc' );
                    $class[] = 'sorted';
                    $class[] = $current_order;
                }
                $column_key_for_sort = ( is_array( $sortable_columns[$column_key] ) ? $sortable_columns[$column_key][0] : $column_key );
                $url = add_query_arg( array(
                    'orderby' => $column_key_for_sort,
                    'order'   => $order,
                ) );
                echo '<th scope="col" class="' . esc_attr( implode( ' ', $class ) ) . '">';
                echo '<a href="' . esc_url( $url ) . '"><span>' . esc_html( $column_display_name ) . '</span><span class="sorting-indicators"><span class="sorting-indicator asc" aria-hidden="true"></span><span class="sorting-indicator desc" aria-hidden="true"></span></span></a>';
                echo '</th>';
            } elseif ( 'cb' === $column_key ) {
                // Handle checkbox column specially - it contains HTML input element.
                $class[] = 'check-column';
                // Allow checkbox input in header - wp_kses_post strips input elements.
                $allowed_checkbox_html = array(
                    'input' => array(
                        'type'  => array(),
                        'id'    => array(),
                        'class' => array(),
                        'style' => array(),
                    ),
                );
                echo '<th scope="col" class="' . esc_attr( implode( ' ', $class ) ) . '">' . wp_kses( $column_display_name, $allowed_checkbox_html ) . '</th>';
            } else {
                echo '<th scope="col" class="' . esc_attr( implode( ' ', $class ) ) . '">' . esc_html( $column_display_name ) . '</th>';
            }
        }
        echo '</tr></thead>';
        echo '<tbody id="the-list">';
        if ( $this->has_items() ) {
            $this->display_rows();
        } else {
            echo '<tr class="no-items"><td class="colspanchange" colspan="' . esc_attr( $this->get_column_count() ) . '">';
            $this->no_items();
            echo '</td></tr>';
        }
        echo '</tbody>';
        echo '</table>';
        $this->display_tablenav( 'bottom' );
    }

    /**
     * Get export URL with current filters
     *
     * @since 1.0.20
     * @return string Export URL.
     */
    private function get_export_url() {
        $params = array(
            'action'   => 'macm_export_members_csv',
            '_wpnonce' => wp_create_nonce( 'macm_export_members' ),
        );
        // Verify nonce when filter form was submitted.
        $filter_nonce_valid = true;
        $filter_action_input = filter_input( INPUT_GET, 'filter_action', FILTER_SANITIZE_FULL_SPECIAL_CHARS );
        if ( !empty( $filter_action_input ) ) {
            $filter_nonce_input = ( isset( $_GET['macm_filter_nonce'] ) ? sanitize_text_field( wp_unslash( $_GET['macm_filter_nonce'] ) ) : '' );
            if ( !wp_verify_nonce( $filter_nonce_input, 'macm_members_filter' ) ) {
                $filter_nonce_valid = false;
            }
        }
        // Add current filters - read-only parameters for building export URL with validation.
        // Only include filters if nonce is valid (or no filter was submitted).
        // Default to 'active' if not set, matching the page default.
        $export_allowed_statuses = array('active', 'inactive', 'all');
        $export_status_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'member_status', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $export_status_input = ( !empty( $export_status_raw ) ? sanitize_key( $export_status_raw ) : 'active' );
        $params['member_status'] = ( in_array( $export_status_input, $export_allowed_statuses, true ) ? $export_status_input : 'active' );
        $membership_type_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'membership_type', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $membership_type_param = ( !empty( $membership_type_raw ) ? sanitize_text_field( $membership_type_raw ) : '' );
        if ( !empty( $membership_type_param ) ) {
            $params['membership_type'] = $membership_type_param;
        }
        $club_id_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'club_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $club_id_param = ( !empty( $club_id_raw ) ? absint( $club_id_raw ) : 0 );
        if ( $club_id_param > 0 ) {
            $params['club_id'] = $club_id_param;
        }
        $belt_color_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'belt_color', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $belt_color_param = ( !empty( $belt_color_raw ) ? sanitize_text_field( $belt_color_raw ) : '' );
        if ( !empty( $belt_color_param ) ) {
            $params['belt_color'] = $belt_color_param;
        }
        $group_id_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'group_id', FILTER_SANITIZE_NUMBER_INT ) : null );
        $group_id_param = ( !empty( $group_id_raw ) ? absint( $group_id_raw ) : 0 );
        if ( $group_id_param > 0 ) {
            $params['group_id'] = $group_id_param;
        }
        $search_raw = ( $filter_nonce_valid ? filter_input( INPUT_GET, 'macm_search', FILTER_SANITIZE_FULL_SPECIAL_CHARS ) : null );
        $search_param = ( !empty( $search_raw ) ? sanitize_text_field( $search_raw ) : '' );
        if ( !empty( $search_param ) ) {
            $params['macm_search'] = $search_param;
        }
        return add_query_arg( $params, admin_url( 'admin-post.php' ) );
    }

}
