<?php 
/**
 * v3.1.0
 * type: Shortcode
 * shortcode: [learderboard]
 * callback: 
 * status: pending
 */

    

add_shortcode('proqyz-leaderboard', 'shortcode_proqyz_leaderboard');
function shortcode_proqyz_leaderboard( $atts = [], $content = null, $tag = '' ) {
    if( !is_user_logged_in() ) return '';
    if(isset($_GET['action'])) return '';

    $atts = array_change_key_case((array)$atts, CASE_LOWER);
    # override default attributes with user attributes
    $wporg_atts = shortcode_atts(
        array(
            'result_id' => null
        ) , $atts, $tag
    );

    if( $wporg_atts['result_id'] === null ) return '';
    $result_id = $wporg_atts['result_id'];

    global $wpdb, 
    $table_proqyz_v3_progress_submissions, 
    $table_proqyz_v3_progress_submissions_meta,
    $table_proqyz_v3_progress_submissions_user_response,
    $table_proqyz_v3_progress_submissions_session,
    $table_proqyz_v3_progress_submissions_score,
    $table_proqyz_v3_submissions_evaluation_meta;

    $single_result = $wpdb->get_row("SELECT * FROM {$table_proqyz_v3_progress_submissions} WHERE `ID` = '$result_id' LIMIT 1");
    if( $single_result === null ) return '';


    $quiz_category = $single_result->quiz_category;
    $quiz_id = $single_result->api_id;
    $current_user_id = $single_result->user_id;

    // Define your parameters
    $api_id = $quiz_id;
    $status = 1;

    // Query to get the latest record for each user_id
    $query = "
        SELECT qp.*
        FROM {$table_proqyz_v3_progress_submissions} qp
        INNER JOIN (
            SELECT 
                `user_id`,
                MAX(`date_created`) AS latest_date
            FROM 
                {$table_proqyz_v3_progress_submissions}
            WHERE 
                `api_id` = %s
                AND `status` = %s
                AND `user_id` != $current_user_id
            GROUP BY 
                `user_id`
        ) latest ON qp.`user_id` = latest.`user_id` AND qp.`date_created` = latest.`latest_date`
        WHERE 
            qp.`api_id` = %s 
            AND qp.`status` = %s
            AND qp.`user_id` != $current_user_id
        ORDER BY 
            qp.`date_created` DESC LIMIT 10
    ";

    $ul_data = "";
    // Prepare and execute the query
    $results = $wpdb->get_results(
        $wpdb->prepare($query, $api_id, $status, $api_id, $status)
    );

    $course_subcategory = 'academic';
    
    
    $single_record = [];
    if( $single_result ) {
        $user_id                = $single_result->user_id;
        $avatar_url             = get_avatar_url($user_id);
        $student_info           = get_userdata($user_id);
        $student_display_name   = $student_info->display_name; // User's display name
        $student_username       = $student_info->user_login; 
        $quiz_category          = $single_result->quiz_category;

        // need to fetch score,json, quiz from submissions_meta table 
        # $score = $wpdb->get_var("SELECT `meta_value` FROM {$table_proqyz_progress_submissions_meta} WHERE `submission_id` = '$result_id' AND `meta_key` = 'score' LIMIT 1");;
        $score = $wpdb->get_var("SELECT `score_data` FROM {$table_proqyz_v3_progress_submissions_score} WHERE `submission_id` = '$result_id' LIMIT 1");
        // get json from user response table
        // $json = $wpdb->get_var("SELECT `response_data` FROM {$table_proqyz_v3_progress_submissions_user_response} WHERE `submission_id` = '$result_id' LIMIT 1");
        $json = $wpdb->get_var("SELECT `response_data` FROM {$table_proqyz_v3_progress_submissions_user_response} WHERE `submission_id` = '$result_id' LIMIT 1");
        // get course subcategory from submissions_meta table
        $course_subcategory_val = $wpdb->get_var("SELECT `meta_value` FROM {$table_proqyz_v3_progress_submissions_meta} WHERE `submission_id` = '$result_id' AND `meta_key` = 'course_subcategory' LIMIT 1");
        if( $course_subcategory_val === null) {
            $course_subcategory = 'academic';
        } else if($course_subcategory_val == 'academic' || $course_subcategory_val == 'general'){
            $course_subcategory = $course_subcategory_val;
        }

        // only useful when quiz is listening, reading (use json_decode)
        if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
            $report = (object) json_decode($score, true);
        } else if( $quiz_category === 'ielts-writing' || $quiz_category === 'ielts-speaking') {
            $report = maybe_unserialize($score);
        } else {
            $report = (object) json_decode($score, true);
        }
        
        $json                   = (object) maybe_unserialize($json);
        $recors                 = (object) $json->quiz;

        if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
            $quiz_score_type    = 'band';
        } else {
            $quiz_score_type = "percentage";
        }
        

        $rank               = 0;
        $percentage         = 0;
        $time_percentage    = 0;
        $spend_time         = 0;

            


        if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
            $total_questions    = isset($report->total)? (int) $report->total : 0;
            $total_correct      = isset($report->correct)? (int) $report->correct : 0;
            $remaining_time     = (int) $recors->diff;
            $total_time         = (int) $recors->time;
        } else {
            $main_report        = $report->report;
            $report_quiz_stats  = $report->user_response->quiz;
            $total_questions    = isset($main_report->total_set_questions)? (int) $main_report->total_set_questions : 0;
            $total_correct      = isset($main_report->total_correct)? (int) $main_report->total_correct : 0;
            $remaining_time     = (int) $report_quiz_stats->diff;
            $total_time         = (int) $report_quiz_stats->time;
        }

            
        # for time    
        if( $total_time > 0 ) {
            $spend_time = $total_time - $remaining_time;
        }


        // Format the time into H:i:s (mm:ss format)
        $formatted_time = gmdate('i:s', $spend_time);

        // If the formatted time starts with "00:", it's less than a minute, so format as seconds.
        if (strpos($formatted_time, '00:') === 0) {
            $formatted_time = substr($formatted_time, 3); // Remove "00:" to just show the seconds
            $unit = 's'; // It's in seconds
        } else {
            $unit = 'm'; // It's in minutes
        }

        // If the time is less than 10 seconds, don't pad it with a leading zero
        if ($unit === 's' && strlen($formatted_time) == 1) {
            $formatted_time = ltrim($formatted_time, '0'); // Remove leading zero if seconds is a single digit
        }

        // Add the appropriate unit to the formatted time
        $formatted_time .= $unit;




        $score_total_for_rank = 0;
        if( $total_questions > 0 ) {
            $score_total_for_rank = ($total_correct / $total_questions) * 100 * 0.7;
        }
        // 90/3600 =0.025*0.3*100 =0.75
        $time_total_for_rank = 0;
        if($spend_time > 0){
            $time_total_for_rank  = ($remaining_time / $total_time) * 0.3 * 100;
        }

        $rank = ($score_total_for_rank + $time_total_for_rank);

        if( $total_questions > 0 && $total_time > 0 ) {

            $total_score = ($total_correct / $total_questions);
            $display_score = '';
            if($quiz_score_type == "band") {
                $display_score = ProQyz_Ielts_scoreToBand($total_correct, $course_subcategory). " Band";
            } else {
                $percentage = ($total_correct*100)/$total_questions;
                $percentage = round($percentage / 5) * 5;
                $display_score = $percentage. "%";
            }

            $single_record[] = (object) [
                "avatar_url"                => $avatar_url,
                "username"                  => $student_username,
                "display_name"              => ucfirst($student_display_name),
                "rank"                      => $rank,
                "time"                      => $formatted_time,
                "correct"                   => $total_correct,
                "total"                     => $total_questions,
                "total_score_percentage"    => $score_total_for_rank,
                "time_percentage"           => $time_total_for_rank,
                "time_spend"                => $spend_time,
                "total_time"                => $total_time,
                "score"                     => $display_score,
                "user_id"                   => $user_id
            ];

        }
    }

    $is_found = false;
    $leaderboard_scores = [];
    if( count($results) > 0 ) {
        foreach( $results as $result ) {
            $user_id                = $result->user_id;
            $avatar_url             = get_avatar_url($user_id);
            $student_info           = get_userdata($user_id);
            $student_display_name   = $student_info->display_name; // User's display name
            $student_username       = $student_info->user_login; 
            $student_result_id         = $result->ID;    

            // need to fetch score,json, quiz from submissions_meta table 
            // $score = $wpdb->get_var("SELECT `meta_value` FROM {$table_proqyz_v3_progress_submissions_meta} WHERE `submission_id` = '$student_result_id' AND `meta_key` = 'score' LIMIT 1");
            // $json = $wpdb->get_var("SELECT `response_data` FROM {$table_proqyz_v3_progress_submissions_user_response} WHERE `submission_id` = '$student_result_id' LIMIT 1");
            $score = $wpdb->get_var("SELECT `score_data` FROM {$table_proqyz_v3_progress_submissions_score} WHERE `submission_id` = '$student_result_id' LIMIT 1");
            $json = $wpdb->get_var("SELECT `response_data` FROM {$table_proqyz_v3_progress_submissions_user_response} WHERE `submission_id` = '$student_result_id' LIMIT 1");
            // get course subcategory from submissions_meta table
            // use the same course subcategory as the current user
            
            if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
                $report = (object) json_decode($score, true);
            } else if( $quiz_category === 'ielts-writing' || $quiz_category === 'ielts-speaking') {
                $report = maybe_unserialize($score);
            } else {
                $report = (object) json_decode($score, true);
            }
            // only useful when quiz is listening, reading (use json_decode)

            $json                   = (object) maybe_unserialize($json);
            $recors                 = (object) $json->quiz;

            if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
                $quiz_score_type    = 'band';
            } else {
                $quiz_score_type = "percentage";
            }



            $rank               = 0;
            $percentage         = 0;
            $time_percentage    = 0;
            $spend_time         = 0;

            


            if( $quiz_category === "ielts-reading" || $quiz_category === "ielts-listening") {
                $total_questions    = isset($report->total)? (int) $report->total : 0;
                $total_correct      = isset($report->correct)? (int) $report->correct : 0;
                $remaining_time     = (int) $recors->diff;
                $total_time         = (int) $recors->time;
            } else if( $quiz_category === "proqyz-pdf-quiz") {
                
            } else {
                $main_report        = $report->report;
                $report_quiz_stats  = $report->user_response->quiz;
                $total_questions    = isset($main_report->total_set_questions)? (int) $main_report->total_set_questions : 0;
                $total_correct      = isset($main_report->total_correct)? (int) $main_report->total_correct : 0;
                $remaining_time     = (int) $report_quiz_stats->diff;
                $total_time         = (int) $report_quiz_stats->time;
            }

            
            # for time    
            if( $total_time > 0 ) {
                $spend_time = $total_time - $remaining_time;
            }


            // Format the time into H:i:s (mm:ss format)
            $formatted_time = gmdate('i:s', $spend_time);

            // If the formatted time starts with "00:", it's less than a minute, so format as seconds.
            if (strpos($formatted_time, '00:') === 0) {
                $formatted_time = substr($formatted_time, 3); // Remove "00:" to just show the seconds
                $unit = ' s'; // It's in seconds
            } else {
                $unit = ' m'; // It's in minutes
            }

            // If the time is less than 10 seconds, don't pad it with a leading zero
            if ($unit === 's' && strlen($formatted_time) == 1) {
                $formatted_time = ltrim($formatted_time, '0'); // Remove leading zero if seconds is a single digit
            }

            // Add the appropriate unit to the formatted time
            $formatted_time .= $unit;




            $score_total_for_rank = 0;
            if( $total_questions > 0 ) {
                $score_total_for_rank = ($total_correct / $total_questions) * 100 * 0.7;
            }
            // 90/3600 =0.025*0.3*100 =0.75
            $time_total_for_rank = 0;
            if($spend_time > 0){
                $time_total_for_rank  = ($remaining_time / $total_time) * 0.3 * 100;
            }

            $rank = ($score_total_for_rank + $time_total_for_rank);

            if( $total_questions > 0 && $total_time > 0 ) {


                $total_score = ($total_correct / $total_questions);
                $display_score = '';
                if($quiz_score_type == "band") {
                    $display_score = ProQyz_Ielts_scoreToBand($total_correct, $course_subcategory). " Band";
                } else {
                    $percentage = ($total_correct*100)/$total_questions;
                    $percentage = round($percentage / 5) * 5;
                    $display_score = $percentage. "%";
                }

                $leaderboard_scores[] = (object) [
                    "avatar_url"                => $avatar_url,
                    "username"                  => $student_username,
                    "display_name"              => ucfirst($student_display_name),
                    "rank"                      => $rank,
                    "time"                      => $formatted_time,
                    "correct"                   => $total_correct,
                    "total"                     => $total_questions,
                    "total_score_percentage"    => $score_total_for_rank,
                    "time_percentage"           => $time_total_for_rank,
                    "time_spend"                => $spend_time,
                    "total_time"                => $total_time,
                    "score"                     => $display_score,
                    "user_id"                   => $user_id
                ];

                if($user_id == $current_user_id ) {
                    $is_found = true;
                }

            }

        }
    }

    if( $is_found == false ){
        if(count($single_record) > 0 ) {
            $leaderboard_scores[] = $single_record[0];
        }
    }

    usort($leaderboard_scores, function($a, $b) {
        return $b->rank <=> $a->rank;  // Swap $a and $b to reverse the order
    });

    
    ob_start();
    foreach( $leaderboard_scores as $key => $ls ) { 
        if($ls->user_id == $current_user_id ) {
            
            ob_start(); ?>
            <li class="user-<?php echo $ls->user_id; ?> <?php echo ($ls->user_id == $current_user_id)? ' current' : ''; ?>">
                <div class="rank">
                    <?php if($key+1 == 1){ ?>
                        <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/1.svg'; ?>" alt="1st Place" />
                    <?php } else if($key+1 == 2){ ?>
                        <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/2.svg'; ?>" alt="2st Place" />
                    <?php } else if($key+1 == 3){ ?>
                        <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/3.svg'; ?>" alt="3st Place" />
                    <?php } else {
                        echo $key+1; 
                    } ?>
                    
                </div>
                <div class="avatar">
                    <img src="<?php echo $ls->avatar_url; ?>" alt="<?php echo $ls->display_name; ?>" />
                </div>
                <div class="info">
                    <p class="name"><?php echo $ls->display_name; ?></p>
                    <p class="band"><?php echo $ls->score; ?></p>
                    
                </div>
                <div class="stats">
                    <p class="score"><?php echo $ls->correct; ?>/<?php echo $ls->total; ?></p>
                    <p class="time"><?php echo $ls->time; ?></p>
                </div>
                
            </li>
            <?php $single_layer = ob_get_clean();
        }
    
        ?>
        
        <li class="user-<?php echo $ls->user_id; ?> <?php echo ($ls->user_id == $current_user_id)? ' current' : ''; ?>">
            <div class="rank">
                <?php if($key+1 == 1){ ?>
                    <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/1.svg'; ?>" alt="1st Place" />
                <?php } else if($key+1 == 2){ ?>
                    <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/2.svg'; ?>" alt="2st Place" />
                <?php } else if($key+1 == 3){ ?>
                    <img src="<?php echo ST_PROQYZ_PUBLIC_URL.'/dist/svg/3.svg'; ?>" alt="3st Place" />
                <?php } else {
                    echo $key+1; 
                } ?>
                
            </div>
            <div class="avatar">
                <img src="<?php echo $ls->avatar_url; ?>" alt="<?php echo $ls->display_name; ?>" />
            </div>
            <div class="info">
                <p class="name"><?php echo $ls->display_name; ?></p>
                <p class="band"><?php echo $ls->score; ?></p>
                
            </div>
            <div class="stats">
                <p class="score"><?php echo $ls->correct; ?>/<?php echo $ls->total; ?></p>
                <p class="time"><?php echo $ls->time; ?></p>
            </div>
            
        </li>
    <?php }
    $ul_data = ob_get_clean();


    // Add CSS only once per page - Universal approach for both WordPress and custom templates
    static $leaderboard_css_added = false;
    if (!$leaderboard_css_added) {
        $leaderboard_css_added = true;
        
        // Check if we're in a custom template without wp_head
        $is_custom_template = !did_action('wp_head') && !doing_action('wp_head') && 
                             !is_admin() && (!defined('DOING_AJAX') || !DOING_AJAX);
        $custom_css = '';
        if ($is_custom_template) {
            ob_start(); ?>
            <style type="text/css">
            .leaderboard {
                list-style: none;
                padding: 0;
                margin: 20px auto;
                background: #fff;
                border-radius: 10px;
                box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
            }
            .leaderboard li {
                display: flex;
                align-items: center;
                padding: 15px;
                border-bottom: 1px solid #eee;
            }
            .leaderboard li:last-child {
                border-bottom: none;
            }
            .rank {
                font-size: 16px;
                font-weight: bold;
                width: 40px;
                text-align: center;
            }
            .rank img {
                width: 35px;
                height: 35px;
            }
            .avatar {
                margin-right: 15px;
            }
            .avatar img {
                width: 40px;
                height: 40px;
                border-radius: 50%;
            }
            .info {
                flex-grow: 1;
            }
            .info .name {
                font-size: 14px;
                font-weight: bold;
                margin: 0;
            }
            .info .band {
                font-size: 12px;
                padding: 2px 8px;
                border-radius: 12px;
                display: inline-block;
                margin-top: 4px;
            }
            .listening-result p.band {
                color: #32b3c7;
                background: #d6f0f4;
            }
            .reading-result p.band {
                color: #327846;
                background: #d7e6dc;
            }
            .stats {
                text-align: right;
            }
            .stats .score {
                font-size: 14px;
                color: #555;
                margin: 0;
            }
            .stats .time {
                font-size: 12px;
                color: #999;
                margin-top: 4px;
            }
            .li-sticky-top {
                position: sticky;
                top: 0;
                background: white;
                z-index: 10;
            }
            .li-sticky-bottom {
                position: sticky;
                bottom: 0;
                background: white;
                z-index: 10;
            }
            </style>
            <?php $custom_css = ob_get_clean();
        } else {
            // Normal WordPress page - use enqueue system
            wp_register_style('proqyz-leaderboard-css', false);
            wp_enqueue_style('proqyz-leaderboard-css');
            wp_add_inline_style('proqyz-leaderboard-css', '
                .leaderboard {
                    list-style: none;
                    padding: 0;
                    margin: 20px auto;
                    background: #fff;
                    border-radius: 10px;
                    box-shadow: 0 2px 4px rgba(0, 0, 0, 0.1);
                }
                .leaderboard li {
                    display: flex;
                    align-items: center;
                    padding: 15px;
                    border-bottom: 1px solid #eee;
                }
                .leaderboard li:last-child {
                    border-bottom: none;
                }
                .rank {
                    font-size: 16px;
                    font-weight: bold;
                    width: 40px;
                    text-align: center;
                }
                .rank img {
                    width: 35px;
                    height: 35px;
                }
                .avatar {
                    margin-right: 15px;
                }
                .avatar img {
                    width: 40px;
                    height: 40px;
                    border-radius: 50%;
                }
                .info {
                    flex-grow: 1;
                }
                .info .name {
                    font-size: 14px;
                    font-weight: bold;
                    margin: 0;
                }
                .info .band {
                    font-size: 12px;
                    padding: 2px 8px;
                    border-radius: 12px;
                    display: inline-block;
                    margin-top: 4px;
                }
                .listening-result p.band {
                    color: #32b3c7;
                    background: #d6f0f4;
                }
                .reading-result p.band {
                    color: #327846;
                    background: #d7e6dc;
                }
                .stats {
                    text-align: right;
                }
                .stats .score {
                    font-size: 14px;
                    color: #555;
                    margin: 0;
                }
                .stats .time {
                    font-size: 12px;
                    color: #999;
                    margin-top: 4px;
                }
                .li-sticky-top {
                    position: sticky;
                    top: 0;
                    background: white;
                    z-index: 10;
                }
                .li-sticky-bottom {
                    position: sticky;
                    bottom: 0;
                    background: white;
                    z-index: 10;
                }
            ');
        }
    }

    ob_start(); ?>
    <?php echo $custom_css; ?>
    <div class="tip-theday" style="background:transparent;">
        <h6 class="tip-theday__caption">&nbsp; Leaderboard (<?php echo ucfirst($course_subcategory); ?>)</h6>
        <div class="tip-theday__contents">
            <div id="leaderboard-container">
                
                <div class="div-2">
                    <ul class="leaderboard">
                        <?php echo $ul_data; ?>
                    </ul>
                </div>
                
            </div>        
        </div>
    </div>
    <?php return ob_get_clean();

}