<?php
if ( ! defined( 'ABSPATH' ) ) {
  exit; // Exit if accessed directly
}
global $wpdb,$current_user;
$current_user_id = get_current_user_id();
$wppm_project_time = get_option('wppm_project_time');
$wppm_task_time = get_option('wppm_task_time');
$wppm_task_statuses = $wpdb->get_results("SELECT id,name,bg_color FROM {$wpdb->prefix}wppm_task_statuses");
$wppm_project_statuses = $wpdb->get_results("SELECT id,name,bg_color FROM {$wpdb->prefix}wppm_project_statuses");
$wppm_current_user_capability = get_user_meta( $current_user->ID, 'wppm_capability', true );
$is_admin = ( $wppm_current_user_capability === 'wppm_admin' || current_user_can('manage_options') ) ? 1 : 0;
$query = $wpdb->prepare(
    "SELECT COUNT(DISTINCT p.id)
     FROM {$wpdb->prefix}wppm_project p
     LEFT JOIN {$wpdb->prefix}wppm_project_users pu
     ON p.id = pu.proj_id
     LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
     ON p.id = pm.project_id AND pm.meta_key = 'public_project'
     WHERE (
        FIND_IN_SET(%d, p.users)
        OR p.created_by = %d
        OR %d = 1 
        OR pm.meta_value = '1'
     )
     AND p.status != %d",
    $current_user_id,
    $current_user_id,
    $is_admin,
    4
);
$active_projects = $wpdb->get_var($query);
$query = $wpdb->prepare(
    "SELECT DISTINCT Task.*
     FROM {$wpdb->prefix}wppm_task AS Task
     WHERE (
        FIND_IN_SET(%d, Task.users)
        OR Task.created_by = %d 
     )
     AND Task.status != %d
     AND Task.active = 1
     AND Task.is_archived = 0
     LIMIT 5",
    $current_user_id,        // for JOIN pu
    $current_user_id,                   // role_id
    4                        // status
);
$tasks = $wpdb->get_results($query);
$query = $wpdb->prepare(
    "SELECT DISTINCT Task.*
     FROM {$wpdb->prefix}wppm_task AS Task
     LEFT JOIN {$wpdb->prefix}wppm_project p 
        ON Task.project = p.id
     LEFT JOIN {$wpdb->prefix}wppm_project_users pu
        ON p.id = pu.proj_id AND pu.user_id = %d
     LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
        ON p.id = pm.project_id AND pm.meta_key = 'public_project'
     WHERE (
        %d = 1
        OR FIND_IN_SET(%d, Task.users)
        OR Task.created_by = %d
        OR p.created_by = %d
        OR (FIND_IN_SET(%d, p.users) AND pu.role_id = %d)
        OR pm.meta_value = '1'
     )
     AND Task.status = %d
     AND Task.active = 1
     AND Task.is_archived = 0
     LIMIT 5",
    
    $current_user_id,  // 1 → pu.user_id
    $is_admin,         // 2
    $current_user_id,  // 3
    $current_user_id,  // 4
    $current_user_id,  // 5
    $current_user_id,  // 6
    2,                 // 7 → role_id
    4                  // 8 → status
);
$completed_tasks = $wpdb->get_results($query);
$query = $wpdb->prepare(
    "SELECT COUNT(DISTINCT Task.id)
     FROM {$wpdb->prefix}wppm_task AS Task
     LEFT JOIN {$wpdb->prefix}wppm_project p 
        ON Task.project = p.id
     LEFT JOIN {$wpdb->prefix}wppm_project_users pu
        ON p.id = pu.proj_id AND pu.user_id = %d
     LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
        ON p.id = pm.project_id AND pm.meta_key = 'public_project'
     WHERE (
        %d = 1
        OR FIND_IN_SET(%d, Task.users)
        OR Task.created_by = %d
        OR p.created_by = %d
        OR (FIND_IN_SET(%d, p.users) AND pu.role_id = %d)
        OR pm.meta_value = '1'
     )
     AND Task.status = %d
     AND Task.active = 1
     AND Task.is_archived = 0",
    
    $current_user_id,  // pu.user_id
    $is_admin,
    $current_user_id,
    $current_user_id,
    $current_user_id,
    $current_user_id,
    2,                 // role_id ✅ FIXED
    4                  // status
);
$completed_task_count = $wpdb->get_var($query);
$query = $wpdb->prepare(
    "SELECT DISTINCT count(*) AS task_count
     FROM {$wpdb->prefix}wppm_task AS Task
     WHERE (
        FIND_IN_SET(%d, Task.users)
        OR Task.created_by = %d 
     )
     AND Task.status != %d
     AND Task.active = 1
     AND Task.is_archived = 0
     LIMIT 5",
    $current_user_id,        // for JOIN pu
    $current_user_id,                     // role_id
    4                        // status
);
$task_count = $wpdb->get_var($query);
$query = $wpdb->prepare(
    "SELECT DISTINCT Task.*,p.project_name
     FROM {$wpdb->prefix}wppm_task AS Task
     LEFT JOIN {$wpdb->prefix}wppm_project p 
        ON Task.project = p.id
     LEFT JOIN {$wpdb->prefix}wppm_project_users pu
        ON p.id = pu.proj_id AND pu.user_id = %d
     LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
        ON p.id = pm.project_id AND pm.meta_key = 'public_project'
     WHERE (
        %d = 1
        OR FIND_IN_SET(%d, Task.users)
        OR Task.created_by = %d 
        OR p.created_by = %d
        OR (FIND_IN_SET(%d, p.users) AND pu.role_id = %d)
        OR pm.meta_value = '1'
     )
     AND DATE(Task.end_date) = CURDATE()
     AND Task.status != %d
     AND Task.active = 1
     AND Task.is_archived = 0
     LIMIT 5",
    $current_user_id,        // for JOIN pu
    $is_admin,
    $current_user_id,
    $current_user_id,
    $current_user_id,
    $current_user_id,
    2,                       // role_id
    4                        // status
);
$todays_tasks = $wpdb->get_results($query);
$status_counts = [];
$task_bg_color = [];
$project_status_counts = [];
$project_bg_color = [];
if(!empty($wppm_task_statuses)){
  foreach($wppm_task_statuses as $key => $status){
      $count = $wpdb->get_var($wpdb->prepare(
           "SELECT DISTINCT COUNT(DISTINCT Task.id) 
          FROM {$wpdb->prefix}wppm_task AS Task
          LEFT JOIN {$wpdb->prefix}wppm_project p 
              ON Task.project = p.id
          LEFT JOIN {$wpdb->prefix}wppm_project_users pu
              ON p.id = pu.proj_id AND pu.user_id = %d
          LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
              ON p.id = pm.project_id AND pm.meta_key = 'public_project'
          WHERE (
              %d = 1
              OR FIND_IN_SET(%d, Task.users)
              OR Task.created_by = %d 
              OR p.created_by = %d
              OR (FIND_IN_SET(%d, p.users) AND pu.role_id = %d)
              OR pm.meta_value = '1'
          )
          AND Task.status = %d
          AND Task.active = 1
          AND Task.is_archived = 0
          LIMIT 5",
          $current_user_id,        // for JOIN pu
          $is_admin,
          $current_user_id,
          $current_user_id,
          $current_user_id,
          $current_user_id,
          2,                       // role_id
          $status->id                    // status
       ) 
    );
    $status_counts[$status->name] = $count;
    $task_bg_color[$status->name] = $status->bg_color;
  }
}
if(!empty($wppm_project_statuses)){
  foreach($wppm_project_statuses as $key => $status){
      $count = $wpdb->get_var(
      $wpdb->prepare(
            "SELECT COUNT(DISTINCT p.id)
            FROM {$wpdb->prefix}wppm_project p
            LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
                ON p.id = pm.project_id 
                AND pm.meta_key = 'public_project'
            WHERE (
                FIND_IN_SET(%d, p.users)
                OR p.created_by = %d
                OR %d = 1
                OR pm.meta_value = '1'
            )
            AND p.status = %d",
            $current_user_id,
            $current_user_id,
            $is_admin,
            $status->id
        )
      );
      $project_status_counts[$status->name] = $count;
      $project_bg_color[$status->name] = $status->bg_color;
  }
}
$overdue = $wpdb->get_var(
    $wpdb->prepare( 
        "SELECT COUNT(DISTINCT t.id)
         FROM {$wpdb->prefix}wppm_task t
         LEFT JOIN {$wpdb->prefix}wppm_project p 
            ON t.project = p.id
         LEFT JOIN {$wpdb->prefix}wppm_project_users pu
            ON p.id = pu.proj_id 
            AND pu.user_id = %d
         LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
            ON p.id = pm.project_id 
            AND pm.meta_key = 'public_project'
         WHERE 
            t.end_date < CURDATE()
            AND t.status != 4
            AND t.active = 1
            AND t.is_archived = 0
            AND (
                %d = 1
                OR FIND_IN_SET(%d, t.users)
                OR t.created_by = %d 
                OR p.created_by = %d
                OR pu.role_id = %d
                OR pm.meta_value = '1'
            )",
        $current_user_id,   // pu.user_id
        $is_admin,
        $current_user_id,
        $current_user_id,
        $current_user_id,
        2                   // role_id
    )
);
$labels = array_keys($status_counts);
$data   = array_values($status_counts);
$task_bg_colors = array_values($task_bg_color);
$project_overdue = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT COUNT(DISTINCT p.id)
            FROM {$wpdb->prefix}wppm_project p
            LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
                ON p.id = pm.project_id 
                AND pm.meta_key = 'public_project'
            WHERE (
                FIND_IN_SET(%d, p.users)
                OR p.created_by = %d
                OR %d = 1
                OR pm.meta_value = '1'
            )
            AND p.status != %d AND p.end_date < CURDATE()",
            $current_user_id,
            $current_user_id,
            $is_admin,
            4
      )
);
$project_labels = array_keys($project_status_counts);
$project_data   = array_values($project_status_counts);
$project_bg_colors = array_values($project_bg_color);
$labels[] = 'Overdue';
$data[]   = $overdue;
$task_bg_colors [] = '#e74c3c';
$project_labels[] = 'Overdue';
$project_data[]   = $project_overdue;
$project_bg_colors[] = '#e74c3c';
?>
<div class="wppm-dashboard">
  <div class="wppm-dashboard-top">
      <div class="wppm-small-widget">
          <div class="wppm-widget-icon">📁</div>
          <h3><?php echo esc_html_e('Active Projects', 'taskbuilder'); ?></h3>
          <span class="wppm-number"><?php echo intval($active_projects); ?></span>
      </div>
      <div class="wppm-small-widget">
          <div class="wppm-widget-icon">📝</div>
          <h3><?php echo esc_html_e('My Tasks', 'taskbuilder'); ?></h3>
          <span class="wppm-number"><?php echo intval($task_count); ?></span>
      </div>
      <div class="wppm-small-widget">
          <div class="wppm-widget-icon">✅</div>
          <h3><?php echo esc_html_e('Completed Tasks', 'taskbuilder'); ?></h3>
          <span class="wppm-number"><?php echo intval($completed_task_count); ?></span>
      </div>
      <div class="wppm-small-widget overdue">
          <div class="wppm-widget-icon">⚠</div>
          <h3><?php echo esc_html_e('Overdue Tasks', 'taskbuilder'); ?></h3>
          <span class="wppm-number"><?php echo intval($overdue); ?></span>
      </div>
  </div>
  <div class="wppm-divider"></div>
  <div class="wppm-row">
    <div class="wppm-widget">
      <h2><?php echo esc_html_e('Today\'s Tasks', 'taskbuilder'); ?><a href="<?php echo esc_url(admin_url('admin.php?page=wppm-tasks')); ?>" class="wppm-view-all" target="_blank"><?php echo esc_html_e('View All', 'taskbuilder'); ?></a></h2>
      <form id="wppm_get_my_tasks_frm" name="wppm_get_my_tasks_frm">
        <div class="table-responsive">
          <table id="tbl_todays_tasks" class="table table-striped table-bordered" cellspacing="0" width="100%">
              <thead>
                <tr>
                  <th class="wppm_table_header"><?php echo esc_html_e('Task Name','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Task Status','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Project','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Due date','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Progress','taskbuilder');?> </th>
                </tr>
              </thead>
              <tbody>
                <?php
                if(!empty($todays_tasks)){
                  foreach ($todays_tasks as $task) {
                    $status = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_task_statuses WHERE id = %d", (int) $task->status ) );
                    $users = array();
                    if($task->users != null){
                      $users = explode(",",$task->users);
                    }
                    if(!empty($task->project)){
                      $project = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_project WHERE id = %d",(int) $task->project));
                    }
                    if($wppm_task_time == 1){
                      $task_end_date = $task->end_date;
                    } elseif($wppm_task_time == 0){
                        $teDate = new DateTime($task->end_date);
                        $task_end_date = $teDate->format('Y-m-d');
                    }
                    $priority_color = $wpdb->get_var(
                      $wpdb->prepare(
                          "SELECT bg_color 
                          FROM {$wpdb->prefix}wppm_task_priorities 
                          WHERE id = %d",
                          (int) $task->priority
                      )
                    );
                    // fallback color if not found
                    if(empty($priority_color)){
                        $priority_color = '#cccccc';
                    }
                    ?>
                  <tr onclick="wppm_open_task(<?php echo esc_attr($task->id); ?>)" class="wppm-task-row" data-task="<?php echo esc_attr($task->id); ?>" data-project="<?php echo esc_attr($task->project); ?>">
                    <td>
                        <span class="wppm-priority-dot" style="background: <?php echo esc_attr($priority_color); ?>;"></span>
                        <?php echo esc_html($task->task_name); ?>
                    </td>
                    <td>
                      <span class="wppm_td_task_status" style="background-color:<?php echo (isset($status->bg_color))? esc_attr($status->bg_color):"";?>; color: <?php echo (isset($status->color))? esc_attr($status->color):""; ?>"><?php echo (isset($status->name))? esc_html($status->name):"" ?></span>
                    </td>
                    <td>
                      <?php echo esc_html($project->project_name); ?>
                    </td>
                    <td>
                      <?php echo (isset($task_end_date))? esc_html($task_end_date):""?>
                    </td>
                    <td>
                        <?php
                        $checklists = $wpdb->get_results( $wpdb->prepare("SELECT * FROM {$wpdb->prefix}wppm_checklist where task_id = %d",$task->id) );
                        $row = $wpdb->get_row(
                          $wpdb->prepare(
                              "SELECT 
                              COUNT(Items.id) as total,
                              SUM(CASE WHEN Items.checked = 1 THEN 1 ELSE 0 END) as completed
                              FROM {$wpdb->prefix}wppm_checklist_items AS Items
                              LEFT JOIN {$wpdb->prefix}wppm_checklist checklist 
                              ON Items.checklist_id = checklist.id
                              WHERE checklist.task_id = %d",
                              $task->id
                          )
                      );
                      $total = $row->total;
                      $total_checked_items = intval($row->completed);
                      $progress = ($total > 0) ? round(($total_checked_items / $total) * 100) : 0;
                      $color = '#ef4444'; // red
                      if($progress >= 50){
                        $color = '#f59e0b'; // orange
                      }
                      if($progress >= 80){
                        $color = '#22c55e'; // green
                      }
                        ?>
                        <div class="dashboard-progress-wrapper">
                          <div class="dashboard-progress-bar">
                              <div class="dashboard-progress" style="width: <?php echo esc_attr($progress); ?>%; background: <?php echo esc_attr($color); ?>;"></div>
                          </div>
                          <span class="dashboard-progress-text"><?php echo esc_html($progress); ?>%</span>
                        </div>
                    </td>
                  </tr>
                  <?php } 
                }
                ?>
              </tbody>
          </table>
        </div>
      </form>
    </div>
    <div class="wppm-widget">
      <h2><?php echo esc_html__('Upcoming Tasks(Next 7 Days)', 'taskbuilder'); ?> <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-tasks')); ?>" class="wppm-view-all" target="_blank"><?php echo esc_html__('View All','taskbuilder');?></a></h2>
      <?php 
      $query = $wpdb->prepare(
                "SELECT t.*, p.project_name
                FROM {$wpdb->prefix}wppm_task t
                LEFT JOIN {$wpdb->prefix}wppm_project p 
                ON t.project = p.id
                LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
                ON p.id = pm.project_id 
                AND pm.meta_key = 'public_project'
                WHERE 
                    (
                        FIND_IN_SET(%d, t.users)
                        OR t.created_by = %d
                        OR %d = 1
                        OR pm.meta_value = '1'
                        OR t.project IN (
                            SELECT id FROM {$wpdb->prefix}wppm_project 
                            WHERE created_by = %d 
                              OR FIND_IN_SET(%d, users)
                        )
                    )
                    AND DATE(t.end_date) BETWEEN CURDATE() 
                    AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
                    AND t.status != 4
                    AND t.active = 1
                    AND t.is_archived = 0

                ORDER BY t.end_date ASC
                LIMIT 5",
                $current_user_id,
                $current_user_id,
                $is_admin,
                $current_user_id,
                $current_user_id
            );
        $upcoming_tasks = $wpdb->get_results($query);?>
        <form id="wppm_get_upcoming_tasks_frm" name="wppm_get_upcoming_tasks_frm">
          <div class="table-responsive">
            <table id="tbl_upcoming_tasks" class="table table-striped table-bordered" cellspacing="0" width="100%">
                <thead>
                  <tr>
                    <th class="wppm_table_header"><?php echo esc_html_e('Name','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Status','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Project','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Due date','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Progress','taskbuilder');?> </th>
                  </tr>
                </thead>
                <tbody>
                <?php
                if(!empty($upcoming_tasks)){
                  foreach ($upcoming_tasks as $task) {
                    $status = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_task_statuses WHERE id = %d", (int) $task->status ) );
                    $users = array();
                    if($task->users != null){
                      $users = explode(",",$task->users);
                    }
                    if(!empty($task->project)){
                      $project = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_project WHERE id = %d",(int) $task->project));
                    }
                    if($wppm_task_time == 1){
                      $task_end_date = $task->end_date;
                    } elseif($wppm_task_time == 0){
                        $teDate = new DateTime($task->end_date);
                        $task_end_date = $teDate->format('Y-m-d');
                    }
                    $priority_color = $wpdb->get_var(
                    $wpdb->prepare(
                        "SELECT bg_color 
                        FROM {$wpdb->prefix}wppm_task_priorities 
                        WHERE id = %d",
                        (int) $task->priority
                    )
                    );
                    // fallback color if not found
                    if(empty($priority_color)){
                        $priority_color = '#cccccc';
                    }
                    ?>
                  <tr onclick="wppm_open_task(<?php echo esc_attr($task->id);?>)" class="wppm-task-row" data-task="<?php echo esc_attr($task->id); ?>" data-project="<?php echo esc_attr($task->project); ?> ">
                    <td>
                        <span class="wppm-priority-dot" style="background: <?php echo esc_attr($priority_color); ?>;"></span>
                        <?php echo esc_html($task->task_name); ?>
                    </td>
                      <td>
                        <span class="wppm_td_task_status" style="background-color:<?php echo (isset($status->bg_color))? esc_attr($status->bg_color):"";?>; color: <?php echo (isset($status->color))? esc_attr($status->color):""; ?>"><?php echo (isset($status->name))? esc_html($status->name):"" ?></span>
                      </td>
                      <td>
                        <?php echo esc_html($project->project_name); ?>
                      </td>
                      <td>
                        <?php echo (isset($task_end_date))? esc_html($task_end_date):""?>
                      </td>
                      <td>
                        <?php
                        $checklists = $wpdb->get_results( $wpdb->prepare("SELECT * FROM {$wpdb->prefix}wppm_checklist where task_id = %d",$task->id) );
                       $row = $wpdb->get_row(
                          $wpdb->prepare(
                              "SELECT 
                              COUNT(Items.id) as total,
                              SUM(CASE WHEN Items.checked = 1 THEN 1 ELSE 0 END) as completed
                              FROM {$wpdb->prefix}wppm_checklist_items AS Items
                              LEFT JOIN {$wpdb->prefix}wppm_checklist checklist 
                              ON Items.checklist_id = checklist.id
                              WHERE checklist.task_id = %d",
                              $task->id
                          )
                      );

                      $total = $row->total;
                      $total_checked_items = intval($row->completed);

                      $progress = ($total > 0) ? round(($total_checked_items / $total) * 100) : 0;
                      $color = '#ef4444'; // red
                        if($progress >= 50){
                          $color = '#f59e0b'; // orange
                      }
                        if($progress >= 80){
                          $color = '#22c55e'; // green
                        }
                        ?>
                        <div class="dashboard-progress-wrapper">
                          <div class="dashboard-progress-bar">
                              <div class="dashboard-progress" style="width: <?php echo esc_attr($progress); ?>%; background: <?php echo esc_attr($color); ?>;"></div>
                          </div>
                          <span class="dashboard-progress-text"><?php echo esc_html($progress); ?>%</span>
                        </div>
                    </td>
                    </tr>
            <?php } 
                }
                ?>
              </tbody>
            </table>
          </div>
        </form>
    </div>
  </div>
  <div class="wppm-row">
    <div class="wppm-widget">
      <h2><?php echo esc_html_e('My Tasks','taskbuilder'); ?>   <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-tasks')); ?>" class="wppm-view-all" target="_blank"><?php echo esc_html_e('View All','taskbuilder'); ?></a></h2>
      <form id="wppm_get_my_tasks_frm" name="wppm_get_my_tasks_frm">
        <div class="table-responsive">
          <table id="tbl_my_tasks" class="table table-striped table-bordered" cellspacing="0" width="100%">
              <thead>
                <tr>
                  <th class="wppm_table_header"><?php echo esc_html_e('Task Name','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Task Status','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Project','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Due date','taskbuilder');?> </th>
                  <th class="wppm_table_header"><?php echo esc_html_e('Progress','taskbuilder');?> </th>
                </tr>
              </thead>
              <tbody>
                <?php
                if(!empty($tasks)){
                  foreach ($tasks as $task) {
                    $status = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_task_statuses WHERE id = %d", (int) $task->status ) );
                    $users = array();
                    if($task->users != null){
                      $users = explode(",",$task->users);
                    }
                    if(!empty($task->project)){
                      $project = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}wppm_project WHERE id = %d",(int) $task->project));
                    }
                    if($wppm_task_time == 1){
                      $task_end_date = $task->end_date;
                    } elseif($wppm_task_time == 0){
                        $teDate = new DateTime($task->end_date);
                        $task_end_date = $teDate->format('Y-m-d');
                    }
                    $priority_color = $wpdb->get_var( $wpdb->prepare(
                        "SELECT bg_color 
                        FROM {$wpdb->prefix}wppm_task_priorities 
                        WHERE id = %d",
                        (int) $task->priority 
                      )
                    );
                    // fallback color if not found
                    if(empty($priority_color)){
                        $priority_color = '#cccccc';
                    }
                    ?>
                    <tr class="wppm-task-row" data-task="<?php echo esc_attr($task->id); ?>" data-project="<?php echo esc_attr($task->project); ?>">
                      <td>
                          <span class="wppm-priority-dot" style="background: <?php echo esc_attr($priority_color); ?>;"></span>
                          <?php echo esc_html($task->task_name); ?>
                      </td>
                      <td>
                        <span class="wppm_td_task_status" style="background-color:<?php echo (isset($status->bg_color))? esc_attr($status->bg_color):"";?>; color: <?php echo (isset($status->color))? esc_attr($status->color):""; ?>"><?php echo (isset($status->name))? esc_html($status->name):"" ?></span>
                      </td>
                      <td>
                        <?php echo esc_html($project->project_name); ?>
                      </td>
                      <td>
                        <?php echo (isset($task_end_date))? esc_html($task_end_date):""?>
                      </td>
                      <td>
                        <?php
                        $checklists = $wpdb->get_results( $wpdb->prepare("SELECT * FROM {$wpdb->prefix}wppm_checklist where task_id = %d",$task->id) );
                        $row = $wpdb->get_row(
                            $wpdb->prepare(
                                "SELECT 
                                COUNT(Items.id) as total,
                                SUM(CASE WHEN Items.checked = 1 THEN 1 ELSE 0 END) as completed
                                FROM {$wpdb->prefix}wppm_checklist_items AS Items
                                LEFT JOIN {$wpdb->prefix}wppm_checklist checklist 
                                ON Items.checklist_id = checklist.id
                                WHERE checklist.task_id = %d",
                                $task->id
                            )
                        );
                        $total = $row->total;
                        $total_checked_items = intval($row->completed);

                        $progress = ($total > 0) ? round(($total_checked_items / $total) * 100) : 0;
                        $color = '#ef4444'; // red
                          if($progress >= 50){
                            $color = '#f59e0b'; // orange
                        }
                          if($progress >= 80){
                            $color = '#22c55e'; // green
                          }
                          ?>
                          <div class="dashboard-progress-wrapper">
                            <div class="dashboard-progress-bar">
                                <div class="dashboard-progress" style="width: <?php echo esc_attr($progress); ?>%; background: <?php echo esc_attr($color); ?>;"></div>
                            </div>
                            <span class="dashboard-progress-text"><?php echo esc_html($progress); ?>%</span>
                          </div>
                      </td>
                    </tr>
                  <?php } 
                }
                ?>
              </tbody>
          </table>
        </div>
      </form>
    </div>
    <div class="wppm-widget">
      <h2><?php echo esc_html_e('Active Projects','taskbuilder'); ?> <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-projects')); ?>" class="wppm-view-all" target="_blank"><?php echo esc_html_e('View All','taskbuilder'); ?></a></h2>
      <?php 
      $query = $wpdb->prepare(
              "SELECT 
              p.id,
              p.project_name AS name,
              ps.name AS status_name,
              ps.bg_color AS status_color,
              ps.color AS status_text_color,
              p.end_date,
              COUNT(t.id) AS total_tasks,
              SUM(CASE WHEN t.status = '4' THEN 1 ELSE 0 END) AS completed_tasks
              FROM {$wpdb->prefix}wppm_project p
              LEFT JOIN {$wpdb->prefix}wppm_project_statuses ps
              ON p.status = ps.id
              LEFT JOIN {$wpdb->prefix}wppm_project_users pu
              ON p.id = pu.proj_id
              LEFT JOIN {$wpdb->prefix}wppm_task t
              ON p.id = t.project
              LEFT JOIN {$wpdb->prefix}wppm_project_meta pm
              ON p.id = pm.project_id 
              AND pm.meta_key = 'public_project'
              WHERE (FIND_IN_SET(%d, p.users) OR p.created_by = %d OR %d = 1 OR pm.meta_value = 1)
              AND p.status != %d
              GROUP BY p.id
              ORDER BY p.end_date ASC
              LIMIT 5",
              $current_user_id,
              $current_user_id,
              $is_admin,
              4
      );
        $projects = $wpdb->get_results($query);
        ?>
        <form id="wppm_get_active_projects_frm" name="wppm_get_active_projects_frm">
          <div class="table-responsive">
            <table id="tbl_active_projects" class="table table-striped table-bordered" cellspacing="0" width="100%">
                <thead>
                  <tr>
                    <th class="wppm_table_header"><?php echo esc_html_e('Name','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Status','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Due date','taskbuilder');?> </th>
                    <th class="wppm_table_header"><?php echo esc_html_e('Progress','taskbuilder');?> </th>
                  </tr>
                </thead>
                <tbody>
                  <?php
                  if(!empty($projects)){
                    foreach ($projects as $project) {
                      if($wppm_project_time == 1){
                        $project_end_date = $project->end_date;
                      } elseif($wppm_project_time == 0){
                          $peDate = new DateTime($project->end_date);
                          $project_end_date = $peDate->format('Y-m-d');
                      }
                      ?>
                      <tr onclick= "wppm_open_project(<?php echo esc_attr($project->id) ?>)" class="wppm-project-row" data-project="<?php echo esc_attr($project->id); ?>">
                        <td>
                          <?php echo esc_html($project->name); ?>
                        </td>
                        <td>
                          <span class="wppm_td_project_status" style="background-color:<?php echo (isset($project->status_color))? esc_attr($project->status_color):"";?>; color: <?php echo (isset($project->status_text_color))? esc_attr($project->status_text_color):""; ?>"><?php echo (isset($project->status_name))? esc_html($project->status_name):"" ?></span>
                        </td>
                        <td>
                          <?php echo esc_html($project_end_date); ?>
                        </td>
                        <td>
                            <?php
                              $total = $project->total_tasks;
                              $completed = $project->completed_tasks;
                              if($total > 0){
                                $proj_progress = round(($completed / $total) * 100);
                              }else{
                                $proj_progress = 0;
                              }
                              $color = '#ef4444'; // red
                                if($proj_progress >= 50){
                                  $color = '#f59e0b'; // orange
                              }
                              if($proj_progress >= 80){
                                $color = '#22c55e'; // green
                              }
                              ?>
                              <div class="dashboard-progress-wrapper">
                                  <div class="dashboard-progress-bar">
                                      <div class="dashboard-progress" style="width:<?php echo esc_attr($proj_progress); ?>%; background: <?php echo esc_attr($color); ?>;"></div>
                                  </div>
                                  <span class="dashboard-progress-text"><?php echo esc_html($proj_progress); ?>%</span>
                              </div>
                          </div>
                        </td>
                      </tr>
                    <?php }
                  }?>
                </tbody>
            </table>
          </div>
        </form>
    </div>
  </div>
  <div class="wppm-row">
    <div class="wppm-widget">
        <h2><?php echo esc_html_e('Project Overview', 'taskbuilder'); ?></h2>
        <canvas id="wppm_project_chart"></canvas>
    </div>
    <div class="wppm-widget">
        <h2><?php echo esc_html_e('Task Overview', 'taskbuilder'); ?></h2>
        <canvas id="wppm_task_chart"></canvas>
    </div>
  </div>
  <div class="wppm-row">
    <div class="wppm-widget">
        <h2><?php echo esc_html_e('User Workload', 'taskbuilder'); ?></h2>
        <?php 
$query = $wpdb->prepare( "SELECT u.ID, u.display_name, 
        COUNT(DISTINCT t.id) AS total_tasks
        FROM {$wpdb->users} u
        INNER JOIN {$wpdb->prefix}wppm_project_users pu 
        ON u.ID = pu.user_id
        INNER JOIN {$wpdb->prefix}wppm_task t 
        ON FIND_IN_SET(u.ID, t.users)
        AND t.project = pu.proj_id AND pu.role_id!=3
     WHERE 
        t.status != 4 
        AND t.active = 1 
        AND t.is_archived = 0
        AND (
            %d = 1
            OR EXISTS (
                SELECT 1 
                FROM {$wpdb->prefix}wppm_project_users current_pu
                LEFT JOIN {$wpdb->prefix}wppm_project p
                ON current_pu.proj_id = p.id
                WHERE (current_pu.proj_id = pu.proj_id
                AND current_pu.user_id = %d
                AND current_pu.role_id != 3 OR p.created_by = %d )
            )
        )
     GROUP BY u.ID
     HAVING total_tasks > 0
     ORDER BY total_tasks DESC
     LIMIT 5",
    $is_admin,
    $current_user_id,
    $current_user_id
);

$workloads = $wpdb->get_results($query);
    // Get max task count
    $max_tasks = 0;
    if(!empty($workloads)){
      foreach($workloads as $w){
        if($w->total_tasks > $max_tasks){
          $max_tasks = $w->total_tasks;
        }
      }
    }
    ?>
    <ul class="wppm-workload-list">
    <?php
    if(!empty($workloads)){ 
      foreach($workloads as $workload){ 
        $percent = 0;
        if($max_tasks > 0){
            $percent = ($workload->total_tasks / $max_tasks) * 100;
        }
        $color = '#4CAF50';
        if($percent > 80){
            $color = '#ff4d4d';
        }elseif($percent > 50){
            $color = '#ffa500';
        }?>
          <li>
              <span><?php echo esc_html($workload->display_name); ?></span>
              <div class="wppm-workload-bar">
                  <div class="wppm-progress" style="width:<?php echo esc_attr($percent); ?>%; background:<?php echo esc_attr($color); ?>"></div>
              </div>
              <span><?php echo esc_html($workload->total_tasks); ?></span>
          </li>
    <?php } 
    }?>
    </ul>
  </div>
    <div class="wppm-widget">
      <h2><?php echo esc_html_e('Quick Actions', 'taskbuilder'); ?></h2>
      <div class="wppm-quick-actions">
        <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-projects&action=add')); ?>" class="wppm-add-new-btn btn-primary" target="_blank"> ➕ <?php echo esc_html_e('Add New Project', 'taskbuilder'); ?></a>
        <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-projects&action=view')); ?>" class="wppm-add-new-btn btn-primary" target="_blank"> 📂 <?php echo esc_html_e('View Projects', 'taskbuilder'); ?></a>
        <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-tasks&action=add')); ?>" class="wppm-add-new-btn btn-primary" target="_blank"> ✅ <?php echo esc_html_e('Add New Task', 'taskbuilder'); ?></a>
        <a href="<?php echo esc_url(admin_url('admin.php?page=wppm-tasks&action=view')); ?>" class="wppm-add-new-btn btn-primary" target="_blank"> 📋 <?php echo esc_html_e('View Tasks', 'taskbuilder'); ?></a>
      </div>
    </div>
  </div>
</div>
</div>
<script type="text/javascript">
  jQuery(document).ready(function() {
    var table = jQuery('#tbl_my_tasks').DataTable({
      "paging":   false,
      "ordering": false,
      "info":     false,
      "searching": false,
      "language": {
          "emptyTable": "<?php echo esc_html_e('No tasks found','taskbuilder'); ?>"
        }
    });
    var table = jQuery('#tbl_todays_tasks').DataTable({
      "paging":   false,
      "ordering": false,
      "info":     false,
      "searching": false,
      "language": {
          "emptyTable": "<?php echo esc_html_e('No tasks found','taskbuilder'); ?>"
        }
    });
    var proj_table = jQuery('#tbl_active_projects').DataTable({
      "paging":   false,
      "ordering": false,
      "info":     false,
      "searching": false,
      "language": {
          "emptyTable": "<?php echo esc_html_e('No Projects found','taskbuilder'); ?>"
        }
    });
    jQuery('div.dataTables_filter input', proj_table.table().container()).focus();	
    var upcoming_tasks_table = jQuery('#tbl_upcoming_tasks').DataTable({
      "paging":   false,
      "ordering": false,
      "info":     false,
      "searching": false,
      "language": {
          "emptyTable": "<?php echo esc_html_e('No Upcoming Tasks found','taskbuilder'); ?>"
        }
    });
    //jQuery('div.dataTables_filter input', upcoming_tasks_table.table().container()).focus();	
    var ctx = document.getElementById('wppm_task_chart').getContext('2d');
    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode($labels); ?>,
            datasets: [{
                data:<?php echo json_encode($data); ?>,
                backgroundColor: [
                    <?php 
                      foreach($task_bg_colors as $color){
                        echo "'".esc_attr($color)."',";
                      }
                    ?>
                ]
            }]
        },
        options: {
            responsive: true,
            plugins: {
                legend: {
                    position: 'bottom'
                }
            }
        }
    });
    var ctx = document.getElementById('wppm_project_chart').getContext('2d');
    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: <?php echo json_encode($project_labels); ?>,
            datasets: [{
                data: <?php echo json_encode($project_data); ?>,
                backgroundColor: [
                    <?php 
                      foreach($project_bg_colors as $color){
                        echo "'".esc_attr($color)."',";
                      }
                    ?>
                ]
            }]
        },
        options: {
            responsive: true,
            plugins: {
                legend: {
                    position: 'bottom'
                }
            }
        }
    });
} );
jQuery(document).on('click','.wppm-task-row',function(){
    var task_id = jQuery(this).data('task');
    var project_id = jQuery(this).data('project');
    event.stopPropagation();
    window.location.href = '<?php echo admin_url("admin.php?page=wppm-tasks"); ?>' + '&task_id=' + task_id;
});
jQuery(document).on('click','.wppm-project-row',function(){
    var project_id = jQuery(this).data('project');
    event.stopPropagation();
    window.location.href = '<?php echo admin_url("admin.php?page=wppm-projects"); ?>' + '&project_id=' + project_id;
});
</script>
<style type="text/css">
  #wpfooter{
    display: none;
  }
</style>