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

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

require_once $base_path_to_learning_resources . "includes/connect.php";
require_once $base_path_to_learning_resources . 'lib/function.php';
require_once $base_path_to_learning_resources . 'lib/validate_api_signature.php';

// Validation file
require_once $base_path_to_learning_resources . 'lib/validate_function.php';
require_once $base_path_to_learning . '/user_management/check_username_availability.php';
require_once $base_path_to_learning . 'user_management/authenticate.php';

require_once $base_path_to_learning_resources . 'includes/specific_db_config.php';
require_once $base_path_to_learning . 'functions/common_functions.php';



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

$requiredFields = ['api_signature', 'function_type', 'jwt_token', 'institution_id'];
foreach ($requiredFields as $field) {
    if (!isset($postdata[$field]) || empty($postdata[$field])) {
        errorReturn("Field '$field' is required.");
    }
}
validateApiSignature($postdata['api_signature']);
$function_type = $postdata['function_type'];
$jwt_token = $postdata['jwt_token'];
$institution_id = $postdata['institution_id'];
$branch_id = isset($postdata['branch_id']) && checkIfIssetAndNotEmpty($postdata['branch_id']) ? ($postdata['branch_id']) : null;
$search_string = isset($postdata['search_string']) ? $postdata['search_string'] : null;
$limit = isset($postdata['limit']) ?  $postdata['limit'] : 'all';
$offset = isset($postdata['offset']) ? (int) $postdata['offset'] : 0; // Default offset is 0

// Validate JWT Token
$jwt_validate = authenticate_jwt($jwt_token);
if (!$jwt_validate) {
    errorReturn("Invalid JWT token.");
}

// Extract user details from token
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$username = $jwt_validate->data->username;
$role = $jwt_validate->data->role;

// Validate user access
$specific_db_name = validateUserAccess($conn_rjllearning, $userid, $role, $workspace_id, ['superadmin', 'admin'], null);
if (!$specific_db_name) {
    errorReturn("Access denied.");
}

// Connect to the specific database
$conn_rjllearning_specific = dynamicDbConnection($specific_db_name);
if (!$conn_rjllearning_specific) {
    errorReturn("Failed to connect to the database.");
}

if ($function_type !== "fetch_payroll_category") {
    errorReturn("Invalid function_type.");
}
$academic_id = CheckAcademicSession($conn_rjllearning_specific, $institution_id);
if ($academic_id == false) {
    errorreturn("Access denied. Academic session not active.");
}

// function fetchPayrollCategories($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset)
// {
//     try {
//         // Base Query
//         $query = "SELECT id, institution_id, branch_id, name, payroll_profile_params, allowed_leaves, allowed_leaves_basis, created_by, 
//                 created_on, status FROM payroll_category WHERE institution_id = ?";

//         // Dynamic Conditions
//         $conditions = [];
//         $params = [$institution_id];

//         if ($branch_id!= null) {
//             $conditions[] = "(branch_id = ? or branch_id = 'all')";
//             $params[] = $branch_id;
//         }

//         if (!empty($search_string)) {
//             $conditions[] = "name LIKE ? OR allowed_leaves_basis LIKE ?";
//             $params[] = "%" . $search_string . "%"; // Partial match search
//             $params[] = "%" . $search_string . "%";
//         }

//         // Append conditions to the query
//         if (!empty($conditions)) {
//             $query .= " AND " . implode(" AND ", $conditions);
//         }

//         // Order by latest
//         $query .= " ORDER BY created_on ASC";

//         // Count total rows (without LIMIT)
//         $countQuery = "SELECT COUNT(*) as total FROM payroll_category WHERE institution_id = ?";
//         if (!empty($conditions)) {
//             $countQuery .= " AND " . implode(" AND ", $conditions);
//         }

//         $countStmt = $conn_rjllearning_specific->prepare($countQuery);
//         $countStmt->execute($params);
//         $totalrows = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];

//         // Apply limit and offset if provided

//         if ($limit !== 'all') {
//             $query .= " LIMIT ? OFFSET ?";
//             $params[] = (int)$limit;
//             $params[] = (int)$offset;
//         }


//         // Prepare and execute the query
//         $stmt = $conn_rjllearning_specific->prepare($query);
//         $stmt->execute($params);
//         $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

//         // Process the results
//         $payroll_categories = [];
//         foreach ($result as $row) {
//             // $branch_info = getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id']);
//             $branch_info = $row['branch_id'] == 'all' ? array('branch_id' => 'all', 'name' => ucwords('all')) :
//                 getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id']);

//             // Fetch salary param names
//             $paramQuery = "SELECT id, name_of_profile FROM payroll_profile WHERE id = ?";
//             $paramStmt = $conn_rjllearning_specific->prepare($paramQuery);
//             $paramStmt->execute([$row['payroll_profile_params']]);
//             $profile_params = $paramStmt->fetchAll(PDO::FETCH_ASSOC);
//             if (count($profile_params) > 0) {
//                 $salary_params =  array("id" => $profile_params[0]['id'], "name_of_profile" => $profile_params[0]['name_of_profile']);
//             } else {
//                 errorreturn("payroll profile not found.");
//             }

//             $payroll_categories[] = [
//                 "category_id" => $row["id"],
//                 "institution_id" => $row["institution_id"],
//                 "branch_id" => $branch_info,
//                 "name" => $row["name"],
//                 "params" => $salary_params,
//                 "allowed_leaves" => $row["allowed_leaves"],
//                 "allowed_leaves_basis" => $row["allowed_leaves_basis"],
//                 "created_by" => $row["created_by"],
//                 "created_on" => $row["created_on"],
//                 "status" => $row["status"]
//             ];
//         }

//         return [
//             'total_rows' => $totalrows,
//             'payroll_categories' => $payroll_categories,
//             'maxreach' => count($payroll_categories) < $limit ? 1 : 0
//         ];
//     } catch (PDOException $e) {
//         errorReturn("Database Error: " . $e->getMessage());
//     }
// }

function fetchPayrollCategories($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset)
{
    try {
        // Base Query
        $query = "SELECT id, institution_id, branch_id, name, payroll_profile_params, allowed_leaves, allowed_leaves_basis, created_by, 
                created_on, status FROM payroll_category WHERE institution_id = ?";

        // Dynamic Conditions
        $conditions = [];
        $params = [$institution_id];

        if ($branch_id != null) {
            $conditions[] = "(branch_id = ? OR branch_id = 'all')";
            $params[] = $branch_id;
        }

        if (!empty($search_string)) {
            $conditions[] = "name LIKE ? OR allowed_leaves_basis LIKE ?";
            $params[] = "%" . $search_string . "%"; // Partial match search
            $params[] = "%" . $search_string . "%";
        }

        // Append conditions to the query
        if (!empty($conditions)) {
            $query .= " AND " . implode(" AND ", $conditions);
        }

        // Order by latest
        $query .= " ORDER BY created_on ASC";

        // Count total rows (without LIMIT)
        $countQuery = "SELECT COUNT(*) as total FROM payroll_category WHERE institution_id = ?";
        if (!empty($conditions)) {
            $countQuery .= " AND " . implode(" AND ", $conditions);
        }

        $countStmt = $conn_rjllearning_specific->prepare($countQuery);
        $countStmt->execute($params);
        $totalrows = $countStmt->fetch(PDO::FETCH_ASSOC)['total'];

        // Apply limit and offset if provided
        if ($limit !== 'all') {
            $query .= " LIMIT ? OFFSET ?";
            $params[] = (int)$limit;
            $params[] = (int)$offset;
        }

        // Prepare and execute the query
        $stmt = $conn_rjllearning_specific->prepare($query);
        $stmt->execute($params);
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Process the results
        $payroll_categories = [];
        foreach ($result as $row) {
            $branch_info = $row['branch_id'] == 'all' ? 
                ["branch_id" => "all", "name" => ucwords("all")] :
                getBranchInfo($conn_rjllearning_specific, $institution_id, $row['branch_id']);

            // Fetch salary param names
            $paramQuery = "SELECT id, name_of_profile FROM payroll_profile WHERE id = ?";
            $paramStmt = $conn_rjllearning_specific->prepare($paramQuery);
            $paramStmt->execute([$row['payroll_profile_params']]);
            $profile_params = $paramStmt->fetchAll(PDO::FETCH_ASSOC);

            if (count($profile_params) > 0) {
                $salary_params = ["id" => $profile_params[0]['id'], "name_of_profile" => $profile_params[0]['name_of_profile']];
            }else{
                $salary_params = null; 
            }  

            $payroll_categories[] = [
                "category_id" => $row["id"],
                "institution_id" => $row["institution_id"],
                "branch_id" => $branch_info,
                "name" => $row["name"],
                "params" => $salary_params,
                "allowed_leaves" => $row["allowed_leaves"],
                "allowed_leaves_basis" => $row["allowed_leaves_basis"],
                "created_by" => $row["created_by"],
                "created_on" => $row["created_on"],
                "status" => $row["status"]
            ];
        }

        $facultyBranchCondition = ($branch_id === 'all') ? "" : "AND f.branch_id = ?";
        $staffGroupBranchCondition = ($branch_id === 'all') ? "" : "AND g.branch_id LIKE ?";

        // Fetch distinct roles from faculty_info (excluding 'other')
        $facultyQuery = "SELECT DISTINCT f.role AS name 
                         FROM faculty_info f
                         WHERE f.institution_id = ? $facultyBranchCondition";
                        //  AND f.role != 'other'";

        $facultyStmt = $conn_rjllearning_specific->prepare($facultyQuery);
        ($branch_id === 'all') ? $facultyStmt->execute([$institution_id]) : $facultyStmt->execute([$institution_id, $branch_id]);
        $facultyRoles = $facultyStmt->fetchAll();

        // Fetch distinct staff group names where role = 'other'
        $staffGroupQuery = "SELECT DISTINCT g.id, g.group_name AS name 
                            FROM other_staff_groups g
                            WHERE g.institution_id = ? $staffGroupBranchCondition";

        $staffGroupStmt = $conn_rjllearning_specific->prepare($staffGroupQuery);
        ($branch_id === 'all') ? $staffGroupStmt->execute([$institution_id]) : $staffGroupStmt->execute([$institution_id, '%'.$branch_id .'%']);
        $staffGroups = $staffGroupStmt->fetchAll();
        
        $others_staff_roles = [];
        foreach ($staffGroups as $role) {
            $others_staff_roles[] = [
                "other_staff_group_id" => $role['id'],
                "other_staff_group_name" => $role['name']
            ];
        }
        
        // if (empty($staffGroups)) {
        //     errorReturn("No 'other' staff roles found for the provided institution and branch.");
        // }

        $roles = $facultyRoles;

        return [
            "total_rows" => $totalrows,
            "payroll_categories" => $payroll_categories,
            "maxreach" => count($payroll_categories) < $limit ? 1 : 0,
            "roles" => array_column($facultyRoles, 'name'), // Extract only role names
            "others_staff_roles" => $others_staff_roles
        ];
        
    } catch (PDOException $e) {
        errorReturn("Database Error: " . $e->getMessage());
    }
}


if ($function_type == "fetch_payroll_category") {
    $data = fetchPayrollCategories($conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $limit, $offset);
    successreturn($data);
}
