<?php
// File: admin-questions-list.php

if (!class_exists('WP_List_Table')) {
    require_once ABSPATH . 'wp-admin/includes/class-wp-list-table.php';
}

add_action('wp_ajax_bcforum_approve_question', 'bcforum_approve_question');

function bcforum_approve_question() {
    global $wpdb;

    if (!check_ajax_referer('bc-forum-nonce', 'security', false)) {
        wp_send_json_error('Invalid security nonce');
        return;
    }

    $question_id = isset($_POST['question_id']) ? intval($_POST['question_id']) : 0;
    $questions_table = $wpdb->prefix . 'questions';

    // Try fetching from cache
    $cache_key = 'question_' . $question_id;
    $cached_question = wp_cache_get($cache_key, 'questions');

    if (!$cached_question) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $cached_question = $wpdb->get_row($wpdb->prepare("SELECT * FROM %i WHERE id = %d",$questions_table, $question_id), ARRAY_A);
        wp_cache_set($cache_key, $cached_question, 'questions', 3600); // Cache for 1 hour
    }

    // Update database and clear cache after update
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
    $updated = $wpdb->update(
        $questions_table,
        array('approved' => 1),
        array('id' => $question_id),
        array('%d'),
        array('%d')
    );

    if ($updated) {
        wp_cache_delete($cache_key, 'questions'); // Clear cache
        wp_send_json_success('Question approved successfully');
    } else {
        wp_send_json_error('Failed to update the question');
    }
}

add_action('wp_ajax_bcforum_delete_question', 'bcforum_delete_question');

function bcforum_delete_question() {
    if (!check_ajax_referer('bc-forum-nonce', 'security', false)) {
        wp_send_json_error('Invalid security nonce');
        return;
    }
    global $wpdb;  
    // Get question ID from the AJAX request
    $question_id = isset($_POST['question_id']) ? intval($_POST['question_id']) : 0;
    
    if ($question_id > 0) {
        $cache_key = 'question_' . $question_id;
        wp_cache_delete($cache_key, 'questions'); 
        // First, delete related answers
        $answers_table = $wpdb->prefix . 'answers';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $deleted_answers = $wpdb->delete(
            $answers_table,
            array('question_id' => $question_id),
            array('%d')
        );

        if ($deleted_answers === false) {
            wp_send_json_error("Failed to delete related answers.");
            die();
        }
        // Then, delete the question
        $questions_table = $wpdb->prefix . 'questions';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $deleted_question = $wpdb->delete(
            $questions_table,
            array('id' => $question_id),
            array('%d')
        );

        if ($deleted_question !== false) {
            wp_cache_delete($cache_key, 'questions');
            wp_send_json_success("Question and related answers deleted successfully.");
        } else {
            wp_send_json_error("Failed to delete the question.");
        }
    } else {
        wp_send_json_error("Invalid question ID.");
    }
    die();   
}

class BCForum_Questions_List extends WP_List_Table {
 
    private $table_name;     
    public function __construct() {       
        
        global $wpdb;
        $this->table_name = $wpdb->prefix . 'questions';
        parent::__construct(array(
            'singular' => 'question_entry',
            'plural'   => 'question_entries',
            'ajax'     => true,
        ));

        add_action('wp_enqueue_scripts', array($this, 'load_assets'));  
    }  
    
    public function load_assets()
    {
        wp_enqueue_script('bc-forum', plugin_dir_url(__FILE__) . 'bc-forum.js', array('jquery'), filemtime(plugin_dir_path(__FILE__) . 'bc-forum.js'), true);

    }
    public function display() {
        $this->search_box('Search', 'bc-forum');
        parent::display();
    }

    public function bc_forum_search_box(){
        ?>
<div class="alignright">

    <form role="search" method="get" class="custom-search-form" action="<?php echo esc_url(home_url('/')); ?>">

        <?php wp_nonce_field('admin_search_action', 'admin_search_nonce'); ?>
        <input type="search" id="search-field" name="s" value="<?php echo esc_attr(get_search_query()); ?>" />
        <input type="submit" value="Search" />
    </form>
</div>
<?php

    }

    public function get_bulk_actions() {
        $actions = array(
            'delete' => 'Delete',
            'approve' => "Approve",
        );
        return $actions;
    }

    public function process_bulk_action() {  
    

        if ('delete' === $this->current_action()) {
            check_admin_referer('bulk-' . $this->_args['plural']);
    
            $question_entry_ids = isset($_REQUEST['question_entry']) ? array_map('intval', $_REQUEST['question_entry']) : [];
    
            if (!empty($question_entry_ids)) {
                global $wpdb;
                $answers_table = $wpdb->prefix . 'answers';
                $questions_table = $wpdb->prefix . 'questions';
    
                foreach ($question_entry_ids as $entry_id) {
                    wp_cache_delete('question_' . $entry_id, 'questions');
    
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->delete($answers_table, ['question_id' => $entry_id], ['%d']);
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->delete($questions_table, ['id' => $entry_id], ['%d']);
                }
            }
        }
    
        if ('approve' === $this->current_action()) {
            check_admin_referer('bulk-' . $this->_args['plural']);
    
            $question_entry_ids = isset($_REQUEST['question_entry']) ? array_map('intval', $_REQUEST['question_entry']) : [];
    
            if (!empty($question_entry_ids)) {
                global $wpdb;
                $questions_table = $wpdb->prefix . 'questions';

                foreach ($question_entry_ids as $entry_id) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $wpdb->update(
                        $questions_table,
                        ['approved' => 1],
                        ['id' => $entry_id],
                        ['%d'],
                        ['%d']
                    );
    
                    $cache_key = 'question_' . $entry_id;
                    $cached_question = wp_cache_get($cache_key, 'questions');
    
                    if ($cached_question) {
                        $cached_question['approved'] = 1;
                        wp_cache_set($cache_key, $cached_question, 'questions', 3600);
                    }
                }
            }
        }
    }    

    public function prepare_items() {
        global $wpdb;
        
        $this->process_bulk_action();
        $columns = $this->get_columns();
        $hidden = array();
        $sortable = $this->get_sortable_columns();

        $this->_column_headers = array($columns, $hidden, $sortable);

        $per_page = $this->get_items_per_page('contact_form_data_per_page', 8);
        $current_page = $this->get_pagenum();
        $total_items = $this->get_total_items();

        $this->set_pagination_args(array(
            'total_items' => $total_items,
            'per_page'    => $per_page,
        ));

        $this->items = $this->get_questions($per_page, $current_page);
  
        // Retrieve the search term       

        $search_term = '';
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        if ( isset($_REQUEST['s']) && !empty($_REQUEST['s']) ) {
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
            $search_term = sanitize_text_field( wp_unslash( $_REQUEST['s'] ) );
            
        }
        // phpcs:ignore WordPress.Security.NonceVerification.Recommended
        $forum_id = isset($_GET['forum-id']) ? intval($_GET['forum-id']) : 0;
        $cache_key = 'my_plugin_questions_' . md5(serialize([
            'forum_id' => $forum_id,
            'search_term' => $search_term,
            'per_page' => $per_page,
            'current_page' => $current_page
        ]));
        $cache_group = 'my_plugin_questions';

        $query_result = wp_cache_get($cache_key, $cache_group);

        if(false === $query_result){
            if ($forum_id) {
                if (is_numeric($search_term)) {
                    // Search by ID in a specific forum
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $query_result = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$wpdb->prefix}questions WHERE forum_id = %d AND id = %d ORDER BY id DESC LIMIT %d OFFSET %d",
                            $forum_id,
                            intval($search_term),
                            $per_page,

                            ($current_page - 1) * $per_page
                        ),
                        ARRAY_A
                    );
                } else {
                    // Search by title in a specific forum
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $query_result = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$wpdb->prefix}questions WHERE forum_id = %d AND question_title LIKE %s ORDER BY id DESC LIMIT %d OFFSET %d",
                            $forum_id,
                            '%' . $wpdb->esc_like($search_term) . '%',
                            $per_page,
                            ($current_page - 1) * $per_page
                        ),
                        ARRAY_A
                    );
                }
            } else {
                if (is_numeric($search_term)) {
                    // Search by ID across all forums
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $query_result = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$wpdb->prefix}questions WHERE id = %d ORDER BY id DESC LIMIT %d OFFSET %d",
                            intval($search_term),
                            $per_page,
                            ($current_page - 1) * $per_page
                        ),
                        ARRAY_A
                    );
                } else {
                    // Search by title across all forums
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
                    $query_result = $wpdb->get_results(
                        $wpdb->prepare(
                            "SELECT * FROM {$wpdb->prefix}questions WHERE question_title LIKE %s ORDER BY id DESC LIMIT %d OFFSET %d",
                            '%' . $wpdb->esc_like($search_term) . '%',
                            $per_page,
                            ($current_page - 1) * $per_page
                        ),
                        ARRAY_A
                    );
                }
            }
            
        }
         wp_cache_set($cache_key, $query_result, $cache_group, 300); 

        $this->items = $query_result;
    }

    public function get_total_items() {
        global $wpdb;

        $cache_key = 'total_items_' . $this->table_name;
        $cache_group = 'my_plugin_questions';

        $total_items = wp_cache_get($cache_key, $cache_group);

        if (false === $total_items) {   
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery       
            $total_items = $wpdb->get_var("SELECT COUNT(id) FROM {$wpdb->prefix}questions");
            wp_cache_set($cache_key, $total_items, $cache_group, 300); // Cache for 5 minutes
        }

        return $total_items;
    }

    public function get_questions($per_page, $current_page) {
        global $wpdb;

        $offset = ($current_page - 1) * $per_page;

        $cache_key = 'questions_' . $this->table_name . "_{$per_page}_{$current_page}";
        $cache_group = 'my_plugin_questions';

        $questions = wp_cache_get($cache_key, $cache_group);

        if (false === $questions) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $questions = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT * FROM {$wpdb->prefix}questions ORDER BY id DESC LIMIT %d OFFSET %d",
                    $per_page,
                    $offset
                ),
                ARRAY_A
            );

            wp_cache_set($cache_key, $questions, $cache_group, 300); // Cache for 5 minutes
        }

        return $questions;
    }
    
    public function get_columns() {
        return array(
            'cb'                => '<input type="checkbox" />',
            'question_title'    => 'Question Title',
            'status'            =>"Status",          
            'author'            => 'Author',
        );
    }

    public function get_sortable_columns() {
        return array(         
            'question_title'  => array('question_title', false),                    
        );
    }

    public function column_cb($item) {
        return sprintf('<input type="checkbox" name="question_entry[]" value="%s" />', $item['id']);
    }
    
    public function column_default($item, $column_name) {
        switch ($column_name) {
            case 'question_title':
                $title = esc_html($item['question_title']);
                $question_id = intval($item['id']);
    
                $edit_link = add_query_arg(array(
                    'page' => 'bcforum-edit-question',
                    'action' => 'edit',
                    'question_id' => $question_id,
                    'question_nonce' => wp_create_nonce('view_question_nonce_action'),
                ), admin_url('admin.php'));
    
                $view_link = add_query_arg(array(
                    'page' => 'bcforum-view-question',
                    'question_id' => $question_id,
                    '_wpnonce' => wp_create_nonce('view_question_' . $question_id),
                ), admin_url('admin.php'));
    
                $buttons = sprintf(
                    '<span class="row-actions">
                        <a href="%s">Edit</a> |
                        <span class="delete"><a href="#" class="delete-question" data-question-id="%d">Delete</a></span> |
                        <a href="#" class="approve-question" data-question-id="%d">Approve</a> |
                        <a href="%s">View</a>
                    </span>',
                    esc_url($edit_link),
                    $question_id,
                    $question_id,
                    esc_url($view_link)
                );
    
                return "<strong style='color:#2271b1;font-size:16px'>$title</strong><br>$buttons";
    
            case 'status':
                return $item['approved'] == 1
                    ? '<span style="color: green;">Approved</span>'
                    : '<span style="color: red;">Pending</span>';
    
            default:
                return esc_html($item[$column_name]);
        }
    }
     
}