<?php
ini_set('display_errors', 1);

require_once '../../../../resources/base_path_include.php';

require_once $base_path_to_learning_resources  . "includes/connect.php"; // use require for mandatory files
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

//valdation file
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';
require_once $base_path_to_learning . 'functions/common_functions.php';
//db access function
require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';

$postdata = json_decode(file_get_contents("php://input"), true);
// print_r($postdata);
checkIfNoPhpContentsReceive();

if (
    !isset($postdata['institution_id']) or !checkIfIssetAndNotEmpty($postdata['institution_id']) or
    !isset($postdata['api_signature']) or !checkIfIssetAndNotEmpty($postdata['api_signature']) or
    !isset($postdata['function_type']) or !checkIfIssetAndNotEmpty($postdata['function_type'])  or
    !isset($postdata['jwt_token']) or  !checkIfIssetAndNotEmpty($postdata['jwt_token'])
) {
    errorreturn("All * fields are required.");
}
validateApiSignature($postdata['api_signature']);

$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;

$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id, ['superadmin', 'admin'], null);
if (!$specific_db_name) {
    errorreturn('Access denied.');
} else {
    $conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
}


// function getStaffGroups($conn_rjllearning_specific, $institution_id, $branch_id,$search_string,$limit, $offset)
// {

//   // Query to get total rows count
//     $count_query = "SELECT count(*) as total_rows FROM other_staff_groups WHERE institution_id = ?";
//     $count_params = [$institution_id];

//     if ($branch_id == 'all') {
//         $count_query .= "";
//     } else if ($branch_id !=  null  && $branch_id != 'all') {
//         $count_query .= " AND branch_id = ? ";
//         $count_params[] = $branch_id;
//     }

//     $count_stmt = $conn_rjllearning_specific->prepare($count_query);
//     $count_stmt->execute($count_params);
//     $result_total = $count_stmt->fetchAll();
//     $totalrows = $result_total[0]['total_rows'];

//     if ($limit == 'all') {
//         $staff_group_query = "SELECT * FROM  other_staff_groups WHERE institution_id = ?";
//         $array_params = [$institution_id];
//          if ($branch_id == 'all') {
//             $staff_group_query .= "";
//         } else if ($branch_id !=  null  && $branch_id != 'all') {
//             $staff_group_query .= " AND branch_id LIKE ? ";
//             $array_params[] = '%' . $branch_id . '%';;
//         }

//           if ($search_string != null) {
//             $staff_group_query .= " AND (group_name LIKE ? )  ";
//             $array_params[] = '%' . $search_string . '%';
//         }

//         $staff_group_query .=  "order by date_created desc, time_created desc";

//     } else {

//         $staff_group_query = "SELECT * FROM  other_staff_groups WHERE institution_id = ?  ";
//         $array_params = [$institution_id];
//           if ($branch_id == 'all') {
//             $staff_group_query .= "";
//         } else if ($branch_id !=  null  && $branch_id != 'all') {
//             $staff_group_query .= " AND branch_id LIKE ? ";
//             $array_params[] = '%' . $branch_id . '%';;
//         }

//         if ($search_string != null) {
//             $staff_group_query .= " AND (group_name LIKE ? )  ";
//             $array_params[] = '%' . $search_string . '%';
//         }

//       $staff_group_query .= " order by date_created desc, time_created desc ";
//       $staff_group_query .= "LIMIT " . $offset . " ," . $limit . "";

//     }
//     $staff_group_stmt = $conn_rjllearning_specific->prepare($staff_group_query);
//     $staff_group_stmt->execute($array_params);
//     $result = $staff_group_stmt->fetchAll();

//     $array_return = [];

//     foreach ($result as $rows) {
//     $branch_id = $rows['branch_id'];
//     $branches = []; // Initialize branches array

//     if ($branch_id === 'all') {
//         $branches[] = array('branch_id' => $branch_id, 'name' => ucwords($branch_id));
//     } else {
//         // Query to get branch details based on branch ID
//         $branch_query = "SELECT * FROM branch WHERE institution_id = ? AND id IN ($branch_id) AND status = ?";
//         $branch_query_params = [$institution_id, '1'];
//         $branch_stmt = $conn_rjllearning_specific->prepare($branch_query);
//         $branch_stmt->execute($branch_query_params);
//         $branch_rows = $branch_stmt->fetchAll();

//         foreach ($branch_rows as $branch) {
//             $branches[] = array('branch_id' => $branch['id'], 'name' => $branch['name']);
//         }
//     }
//         $array_return[] = array('staff_group_id' => $rows['id'], 'branch_id' => $branches,  'group_name' => $rows['group_name']);
//     }

//     if (count($result) < 50) {
//         return array('total_rows' => $totalrows, 'staff_groups_info' => $array_return, 'maxreach' => 1);
//     } else {
//         return array('total_rows' => $totalrows, 'staff_groups_info' => $array_return, 'maxreach' => 0);
//     }
// }

function getStaffGroups($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset)
{
    // Query to get total rows count
    $count_query = "SELECT count(DISTINCT group_name) as total_rows FROM other_staff_groups WHERE institution_id = ?";
    $count_params = [$institution_id];

    if ($branch_id != 'all' && $branch_id != null) {
        $count_query .= " AND (branch_id = 'all' OR branch_id LIKE ? OR branch_id LIKE ? OR branch_id LIKE ?)";
        $count_params[] = "'" . $branch_id . "'"; 
        $count_params[] = "'%" . $branch_id . "%'"; 
        $count_params[] = $branch_id; 
    }

    if ($search_string != null) {
        $count_query .= " AND group_name LIKE ?";
        $count_params[] = '%' . $search_string . '%';
    }

    $count_stmt = $conn_rjllearning_specific->prepare($count_query);
    $count_stmt->execute($count_params);
    $result_total = $count_stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    // Main query
    $staff_group_query = "SELECT DISTINCT group_name, id, institution_id, branch_id, date_created, time_created 
                         FROM other_staff_groups 
                         WHERE institution_id = ?";
    $array_params = [$institution_id];
    
    if ($branch_id != 'all' && $branch_id != null) {
        $staff_group_query .= " AND (branch_id = 'all' OR branch_id LIKE ? OR branch_id LIKE ? OR branch_id LIKE ?)";
        $array_params[] = "'" . $branch_id . "'"; 
        $array_params[] = "'%" . $branch_id . "%'"; 
        $array_params[] = $branch_id;
    }

    if ($search_string != null) {
        $staff_group_query .= " AND group_name LIKE ?";
        $array_params[] = '%' . $search_string . '%';
    }

    $staff_group_query .= " ORDER BY date_created DESC, time_created DESC";

    if ($limit != 'all') {
        $staff_group_query .= " LIMIT " . $offset . " ," . $limit;
    }
    
    $staff_group_stmt = $conn_rjllearning_specific->prepare($staff_group_query);
    $staff_group_stmt->execute($array_params);
    $result = $staff_group_stmt->fetchAll();

    $array_return = [];

    foreach ($result as $rows) {
        $branch_id_value = $rows['branch_id'];
        $branches = [];

        if ($branch_id_value === 'all') {
            $branches[] = array('branch_id' => $branch_id_value, 'name' => ucwords($branch_id_value));
        } else {
            $cleaned_branch_ids = str_replace("'", "", $branch_id_value);
            $branch_ids = array_map('trim', explode(',', $cleaned_branch_ids));
            
            if (!empty($branch_ids)) {
                $placeholders = implode(',', array_fill(0, count($branch_ids), '?'));
                
                $branch_query = "SELECT * FROM branch WHERE institution_id = ? AND id IN ($placeholders) AND status = ?";
                $branch_query_params = array_merge([$institution_id], $branch_ids, ['1']);
                
                $branch_stmt = $conn_rjllearning_specific->prepare($branch_query);
                $branch_stmt->execute($branch_query_params);
                $branch_rows = $branch_stmt->fetchAll();

                foreach ($branch_rows as $branch) {
                    $branches[] = array('branch_id' => $branch['id'], 'name' => $branch['name']);
                }
            }
        }
        
        $array_return[] = array(
            'staff_group_id' => $rows['id'],
            'branch_id' => $branches,
            'group_name' => $rows['group_name']
        );
    }

    if (count($result) < 50) {
        return array('total_rows' => $totalrows, 'staff_groups_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'staff_groups_info' => $array_return, 'maxreach' => 0);
    }
}


if ($postdata['function_type'] == "fetch_staff_groups") {
    $institution_id = trim($postdata['institution_id']);

    if (isset($postdata['branch_id']) and !empty($postdata['branch_id'])) {
        $branch_id = trim($postdata['branch_id']);
    } else {
        $branch_id = null;
    }
    $limit = $postdata['limit'];
    $page = (isset($postdata['page']) and isNumeric($postdata['page'])) ? (int)$postdata['page'] : 1;
    if (isset($postdata['search_string']) and !empty($postdata['search_string'])) {
        $search_string = trim($postdata['search_string']);
    } else {
        $search_string = null;
    }


    if ($limit != 'all') {
        $offset = ($page - 1) * $limit;
        $success['data'] = getStaffGroups($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset);
        successreturn($success);
    } else {
        $success['data'] = getStaffGroups($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, null);
        successreturn($success);
    }
}
