<?php

function get_holiday($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $search_string, $from_date, $to_date, $limit, $offset)
{

    try {
        $sql_count = "SELECT  count(*) as total_rows FROM holiday_calendar WHERE status = ?";
        $array_params = [1];

        if ($institution_id == 'all') {
            $sql_count .= "";
        } else if ($institution_id !=  null  && $institution_id != 'all') {
            $sql_count .= " AND institution_id = ? ";
            $array_params[] = $institution_id;
        }
        if ($branch_id == 'all') {
            $sql_count .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_count .= " AND branch_id = ? ";
            $array_params[] = $branch_id;
        }

        if ($search_string !== null) {
            $sql_count .= " AND (holiday_name LIKE ? or holiday_type LIKE ? or applicable_to LIKE ?)";
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
            $array_params[] = '%' . $search_string . '%';
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_count .= " AND holiday_date BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_count);
        $stmt->execute($array_params);
        $result_total = $stmt->fetchAll();
        $totalrows = $result_total[0]['total_rows'];

        if ($limit == 'all') {

            $sql = "SELECT * FROM holiday_calendar where status =?";
            $array_params = [1];

            if ($institution_id == 'all') {
                $sql .= "";
            } else if ($institution_id !=  null  && $institution_id != 'all') {
                $sql .= " AND institution_id = ? ";
                $array_params[] = $institution_id;
            }
            if ($branch_id == 'all') {
                $sql .= "";
            } else if ($branch_id !=  null  && $branch_id != 'all') {
                $sql .= " AND branch_id = ? ";
                $array_params[] = $branch_id;
            }

            if ($search_string !== null) {
                $sql .= " AND (holiday_name LIKE ? or holiday_type LIKE ? or applicable_to LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
            }

            if ($from_date != null && $to_date != null) {
                $sql .= " AND holiday_date BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql .=  "order by date_created desc, time_created desc";
        } else {
            $sql = "SELECT * FROM holiday_calendar where status =?";
            $array_params = [1];

            if ($institution_id == 'all') {
                $sql .= "";
            } else if ($institution_id !=  null  && $institution_id != 'all') {
                $sql .= " AND institution_id = ? ";
                $array_params[] = $institution_id;
            }
            if ($branch_id == 'all') {
                $sql .= "";
            } else if ($branch_id !=  null  && $branch_id != 'all') {
                $sql .= " AND branch_id = ? ";
                $array_params[] = $branch_id;
            }

            if ($search_string !== null) {
                $sql .= " AND (holiday_name LIKE ? or holiday_type LIKE ? or applicable_to LIKE ?)";
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
                $array_params[] = '%' . $search_string . '%';
            }

            if ($from_date != null && $to_date != null) {
                $sql .= " AND holiday_date BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql .= " order by date_created desc, time_created desc ";
            $sql .= "LIMIT " . $offset . " ," . $limit . "";
        }

        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($array_params);
        $holiday_rows = $stmt->fetchAll();

        if (empty($holiday_rows)) {
            errorreturn("No data found for the provided criteria.");
        }

        $array_return = [];

        foreach ($holiday_rows as $rows) {

            $institution_info = $rows['institution_id'] == 'all' ? array('institution_id' => 'all', 'name' => ucwords('all')) : getInstitution($conn_rjllearning, $rows['institution_id']);
            $branch_info = $rows['branch_id'] == 'all' ? array('branch_id' => 'all', 'name' => ucwords('all')) : getBranchInfo($conn_rjllearning_specific, $rows['institution_id'], $rows['branch_id']);

            $array_return[] = array(
                'holiday_id' => $rows['id'],
                'institution' => $institution_info,
                'branch' => $branch_info,
                // 'institution' => $rows['institution_id'],
                // 'branch' => $rows['branch_id'],
                'holiday_name' => $rows['holiday_name'],
                'holiday_date' => $rows['holiday_date'],
                'holiday_type' => $rows['holiday_type'],
                'applicable_to' => $rows['applicable_to'],
                'date_created' => $rows['date_created'],
                'time_created' => $rows['time_created'],
                'status' => $rows['status'],
            );
        }

        if (count($holiday_rows) < 50) {
            return array('total_rows' => $totalrows, 'holiday_info' => $array_return, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'holiday_info' => $array_return, 'maxreach' => 0);
        }
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function get_specific_holiday($conn_rjllearning_specific, $holiday_id)
{
    try {
        $sql_holiday = "SELECT * FROM holiday_calendar WHERE id = ?";
        $array_params = [$holiday_id];
        $stmt = $conn_rjllearning_specific->prepare($sql_holiday);
        $stmt->execute($array_params);
        $holiday_row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$holiday_row) {
            return errorreturn("Holiday not found.");
        }

        $array_return = [];
        $branch_info = ($holiday_row['branch_id'] == 'all')
            ? array('branch_id' => 'all', 'name' => ucwords('all'))
            : getBranchInfo($conn_rjllearning_specific, $holiday_row['institution_id'], $holiday_row['branch_id']);

        return [
            'holiday_id' => $holiday_row['id'],
            'institution_id' => $holiday_row['institution_id'],
            'branch_id' => $branch_info,
            'holiday_name' => $holiday_row['holiday_name'],
            'holiday_date' => $holiday_row['holiday_date'],
            'holiday_type' => $holiday_row['holiday_type'],
            'applicable_to' => $holiday_row['applicable_to']
        ];
    } catch (PDOException $e) {
        return errorreturn("Database error: " . $e->getMessage());
    } catch (Exception $e) {
        return errorreturn("Unexpected error: " . $e->getMessage());
    }
}

function get_payroll_profile($conn_rjllearning_specific, $institution_id, $search_string, $branch_id, $date_created, $limit, $offset)
{
    $sql_count = "SELECT count(*) as total_rows FROM  payroll_profile where institution_id =?";
    $array_params = [$institution_id];

    if ($branch_id == 'all') {
        $sql_count .= "";
    } else if ($branch_id !=  null  && $branch_id != 'all') {
        $sql_count .= " AND (branch_id = ?  or branch_id = 'all')";
        $array_params[] = $branch_id;
    }

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

    if ($date_created != null) {
        $sql_count .= " AND date_created = ?";
        $array_params[] = $date_created;
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_count);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalrows = $result_total[0]['total_rows'];

    if ($limit == 'all') {

        $sql_payroll = "SELECT * FROM payroll_profile where institution_id =?";
        $array_params = [$institution_id];

        if ($branch_id == 'all') {
            $sql_payroll .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_payroll .= " AND (branch_id = ? OR branch_id = 'all') ";
            $array_params[] = $branch_id;
        }

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

        if ($date_created != null) {
            $sql_payroll .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        $sql_payroll .=  "order by date_created desc, created_at desc";
    } else {
        $sql_payroll = "SELECT * FROM payroll_profile where institution_id =?";
        $array_params = [$institution_id];

        if ($branch_id == 'all') {
            $sql_payroll .= "";
        } else if ($branch_id !=  null  && $branch_id != 'all') {
            $sql_payroll .= " AND (branch_id = ? OR branch_id = 'all') ";
            $array_params[] = $branch_id;
        }

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

        if ($date_created != null) {
            $sql_payroll .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        $sql_payroll .= " order by date_created desc, created_at desc ";
        $sql_payroll .= "LIMIT " . $offset . " ," . $limit . "";
    }

    $stmt = $conn_rjllearning_specific->prepare($sql_payroll);
    $stmt->execute($array_params);
    $session_rows = $stmt->fetchAll();


    $array_return = [];

    foreach ($session_rows as $rows) {

        $branch_info = $rows['branch_id'] == 'all' ? array('branch_id' => 'all', 'name' => ucwords('all')) : getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);
        $params_link = json_decode($rows['params_link'], true) ?: [];
        $salary_params_info = [];

        if (!empty($params_link)) {
            // Convert JSON array to a comma-separated string for the SQL query
            $placeholders = implode(',', array_fill(0, count($params_link), '?'));
            $sql_params = "SELECT id, name FROM salary_params WHERE institution_id = ? AND id IN ($placeholders)";
            $stmt_params = $conn_rjllearning_specific->prepare($sql_params);
            $stmt_params->execute(array_merge([$institution_id], $params_link));
            $salary_params = $stmt_params->fetchAll(PDO::FETCH_ASSOC);

            foreach ($salary_params as $param) {
                $salary_params_info[] = ["salary_params_id" => $param['id'], "name" => $param['name']];
            }
        }

        $array_return[] = array(
            'payroll_profile_id' => $rows['id'],
            'institution_id' => $rows['institution_id'],
            'branch' => $branch_info,
            'name_of_profile' => $rows['name_of_profile'],
            'params_link' => $salary_params_info,
            'date_created' => $rows['date_created'],
            'created_by' => $rows['created_by'],
            'created_at' => $rows['created_at'],
            'last_modified_by' => $rows['last_modified_by'],
            'last_modified_at' => $rows['last_modified_at']
        );
    }

    if (count($session_rows) < 50) {

        return array('total_rows' => $totalrows, 'payroll_profile_info' => $array_return, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'payroll_profile_info' => $array_return, 'maxreach' => 0);
    }
}

function get_specific_payroll_profile($conn_rjllearning_specific, $payroll_profile_id, $institution_id)
{
    try {
        $sql = "SELECT * FROM payroll_profile WHERE id = ? AND institution_id = ?";
        $array_params = [$payroll_profile_id, $institution_id];
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($array_params);
        $payroll_profile_row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$payroll_profile_row) {
            errorreturn("payroll profile not found.");
        }

        $branch_info = ($payroll_profile_row['branch_id'] == 'all')
            ? array('branch_id' => 'all', 'name' => ucwords('all'))
            : getBranchInfo($conn_rjllearning_specific, $institution_id, $payroll_profile_row['branch_id']);

        $params_link = json_decode($payroll_profile_row['params_link'], true) ?: [];
        $salary_params_info = [];

        if (!empty($params_link)) {
            // Convert JSON array to a comma-separated string for the SQL query
            $placeholders = implode(',', array_fill(0, count($params_link), '?'));
            $sql_params = "SELECT id, name FROM salary_params WHERE institution_id = ? AND id IN ($placeholders)";
            $stmt_params = $conn_rjllearning_specific->prepare($sql_params);
            $stmt_params->execute(array_merge([$institution_id], $params_link));
            $salary_params = $stmt_params->fetchAll(PDO::FETCH_ASSOC);

            foreach ($salary_params as $param) {
                $salary_params_info[] = ["salary_params_id" => $param['id'], "name" => $param['name']];
            }
        }

        return [
            'payroll_profile_id' => $payroll_profile_row['id'],
            'institution_id' => $payroll_profile_row['institution_id'],
            'branch' => $branch_info,
            'name_of_profile' => $payroll_profile_row['name_of_profile'],
            'params_link' => $salary_params_info,
            'date_created' => $payroll_profile_row['date_created'],
            'created_by' => $payroll_profile_row['created_by'],
            'created_at' => $payroll_profile_row['created_at'],
            'last_modified_by' => $payroll_profile_row['last_modified_by'],
            'last_modified_at' => $payroll_profile_row['last_modified_at']
        ];
    } catch (PDOException $e) {
        return errorreturn("Database error: " . $e->getMessage());
    } catch (Exception $e) {
        return errorreturn("Unexpected error: " . $e->getMessage());
    }
}

function Getspecific_holiday($conn_rjllearning_specific, $holiday_id, $institution_id, $branch_id)
{
    try {
        $sql_holiday = "SELECT * FROM holiday_calendar WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $array_params = [$holiday_id, $institution_id, $branch_id];
        $stmt = $conn_rjllearning_specific->prepare($sql_holiday);
        $stmt->execute($array_params);
        $holiday_row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$holiday_row) {
            return errorreturn("Holiday not found.");
        }

        $array_return = [];
        $branch_info = ($holiday_row['branch_id'] == 'all')
            ? array('branch_id' => 'all', 'name' => ucwords('all'))
            : getBranchInfo($conn_rjllearning_specific, $holiday_row['institution_id'], $holiday_row['branch_id']);

        return [
            'holiday_id' => $holiday_row['id'],
            'institution_id' => $holiday_row['institution_id'],
            'branch_id' => $branch_info,
            'holiday_name' => $holiday_row['holiday_name'],
            'holiday_date' => $holiday_row['holiday_date'],
            'holiday_type' => $holiday_row['holiday_type'],
            'applicable_to' => $holiday_row['applicable_to']
        ];
    } catch (PDOException $e) {
        return errorreturn("Database error: " . $e->getMessage());
    } catch (Exception $e) {
        return errorreturn("Unexpected error: " . $e->getMessage());
    }
}

function Getspecific_payrollprofile($conn_rjllearning_specific, $payroll_profile_id, $institution_id, $branch_id)
{
    try {
        $sql = "SELECT * FROM payroll_profile WHERE id = ? AND institution_id = ? AND branch_id = ?";
        $array_params = [$payroll_profile_id, $institution_id, $branch_id];
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute($array_params);
        $payroll_profile_row = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$payroll_profile_row) {
            errorreturn("payroll profile not found.");
        }

        $branch_info = ($payroll_profile_row['branch_id'] == 'all')
            ? array('branch_id' => 'all', 'name' => ucwords('all'))
            : getBranchInfo($conn_rjllearning_specific, $institution_id, $payroll_profile_row['branch_id']);

        $params_link = json_decode($payroll_profile_row['params_link'], true) ?: [];
        $salary_params_info = [];

        if (!empty($params_link)) {
            // Convert JSON array to a comma-separated string for the SQL query
            $placeholders = implode(',', array_fill(0, count($params_link), '?'));
            $sql_params = "SELECT id, name FROM salary_params WHERE institution_id = ? AND id IN ($placeholders)";
            $stmt_params = $conn_rjllearning_specific->prepare($sql_params);
            $stmt_params->execute(array_merge([$institution_id], $params_link));
            $salary_params = $stmt_params->fetchAll(PDO::FETCH_ASSOC);

            foreach ($salary_params as $param) {
                $salary_params_info[] = ["salary_params_id" => $param['id'], "name" => $param['name']];
            }
        }

        return [
            'payroll_profile_id' => $payroll_profile_row['id'],
            'institution_id' => $payroll_profile_row['institution_id'],
            'branch' => $branch_info,
            'name_of_profile' => $payroll_profile_row['name_of_profile'],
            'params_link' => $salary_params_info,
            'date_created' => $payroll_profile_row['date_created'],
            'created_by' => $payroll_profile_row['created_by'],
            'created_at' => $payroll_profile_row['created_at'],
            'last_modified_by' => $payroll_profile_row['last_modified_by'],
            'last_modified_at' => $payroll_profile_row['last_modified_at']
        ];
    } catch (PDOException $e) {
        return errorreturn("Database error: " . $e->getMessage());
    } catch (Exception $e) {
        return errorreturn("Unexpected error: " . $e->getMessage());
    }
}

function getSalaryDetails($conn, $institution_id, $branch_id, $start_date, $end_date, $role, $other_staff_role, $category_id)
{
    try {
        if (!$start_date || !$end_date || !$role || !$category_id) {
            errorreturn("Missing required parameters.");
        }

        // Validate date format
        if (!strtotime($start_date) || !strtotime($end_date)) {
            errorreturn("Invalid date format.");
        }

        // Ensure start date is not greater than end date
        if (strtotime($start_date) > strtotime($end_date)) {
            errorreturn("Start date cannot be greater than end date.");
        }

        $current_date = date('Y-m-d');
        if (strtotime($start_date) > strtotime($current_date) || strtotime($end_date) > strtotime($current_date)) {
            errorreturn("Dates cannot be in the future.");
        }

        // Calculate working days (excluding Sundays)
        $begin = new DateTime($start_date);
        $end = new DateTime($end_date);
        $working_days = 0;

        while ($begin <= $end) {
            if ($begin->format('N') != 7) { // Exclude Sundays
                $working_days++;
            }
            $begin->modify('+1 day');
        }

        // Fetch payroll category details
        $categoryQuery = "SELECT payroll_profile_params, allowed_leaves, allowed_leaves_basis FROM payroll_category WHERE id = ?";
        $categoryStmt = $conn->prepare($categoryQuery);
        $categoryStmt->execute([$category_id]);
        $categoryData = $categoryStmt->fetch(PDO::FETCH_ASSOC);

        if (!$categoryData) {
            errorreturn("Invalid category_id.");
        }

        $salary_params_id = $categoryData['payroll_profile_params'];
        $allowed_leaves_count = $categoryData['allowed_leaves'];
        $allowed_leaves_basis = $categoryData['allowed_leaves_basis'];

        $payroll_period = getPayrollPeriodType($start_date, $end_date);
        $applicable_leaves = getApplicableLeaves($allowed_leaves_count, $allowed_leaves_basis, $payroll_period);

        // Fetch payroll profile details
        $paramsQuery = "SELECT name_of_profile, params_link FROM payroll_profile WHERE id = ?";
        $paramsStmt = $conn->prepare($paramsQuery);
        $paramsStmt->execute([$salary_params_id]);
        $payrollProfile = $paramsStmt->fetch(PDO::FETCH_ASSOC);

        if (!$payrollProfile) {
            errorreturn("Invalid salary_params_id.");
        }

        $param_links = json_decode($payrollProfile['params_link'], true) ?? [];
        if (!is_array($param_links) || empty($param_links)) {
            errorreturn("Invalid salary_params_id.");
        }

        // Fetch salary parameters
        $placeholders = implode(',', array_fill(0, count($param_links), '?'));
        $salaryParamsQuery = "SELECT name, percentage_bool, percent_amount, calculate_on, type, 
                                     applicable_if_amount_lte, applicable_if_amount_gte 
                              FROM salary_params WHERE id IN ($placeholders)";
        $salaryParamsStmt = $conn->prepare($salaryParamsQuery);
        $salaryParamsStmt->execute($param_links);
        $salaryParams = $salaryParamsStmt->fetchAll(PDO::FETCH_ASSOC);

        if (!$salaryParams) {
            errorreturn("No salary parameters found.");
        }

        // Main query to fetch staff data with improved role handling
        $query = "SELECT 
            f.id, 
            f.userid,
            f.name, 
            f.designation, 
            f.role,  
            osd.basic_salary, 
            COUNT(a.id) AS present_days, 
            SUM(CASE WHEN a.value = 'H' THEN 0.5 ELSE 0 END) AS half_days, 
            SUM(CASE WHEN a.value = 'L' THEN 1 ELSE 0 END) AS leave_days,
            (SELECT COUNT(*) FROM staff_attendance_daywise WHERE user_id = f.id AND value = 'P' AND date BETWEEN ? AND ?) AS present_days
          FROM faculty_info f
          LEFT JOIN other_staff_details osd ON f.id = osd.faculty_id
          LEFT JOIN staff_attendance_daywise a ON f.id = a.user_id AND a.date BETWEEN ? AND ?
          WHERE 
            f.institution_id = ? 
            AND f.branch_id = ? 
            AND osd.joining_date <= ? 
            AND (osd.date_of_termination > ? OR osd.date_of_termination IS NULL)";

        $arrays_params = [
            $start_date,
            $end_date,
            $start_date,
            $end_date,
            $institution_id,
            $branch_id,
            $end_date,
            $start_date
        ];

        // **Dynamically Add Role Condition**
        if ($role !== 'all') {
            $query .= " AND (";
            $query .= " (f.role = 'other' AND ? = 'other' AND f.other_staff_group_id = ?) OR";
            $query .= " (? <> 'other' AND f.role = ?)";
            $query .= " )";

            $arrays_params[] = $role;  // For (f.role = 'other' AND ? = 'other'...)
            $arrays_params[] = $other_staff_role;
            $arrays_params[] = $role;  // For (f.role <> 'other'...)
            $arrays_params[] = $role;
        }

        // **Group By Clause**
        $query .= " GROUP BY f.id, f.userid, f.name, f.designation, f.role, osd.basic_salary";
        // echo $query;die();

        $stmt = $conn->prepare($query);
        $stmt->execute($arrays_params);
        $staffData = $stmt->fetchAll(PDO::FETCH_ASSOC);
        // print_R($staffData);die();
        if (!$staffData) {
            errorreturn("No data found.");
        }

        $result = [];
        foreach ($staffData as $staff) {
            $basic_salary = (float)$staff['basic_salary'];
            $present_days = (int)$staff['present_days'];
            $half_days = (float)$staff['half_days'];
            $leave_days = (int)$staff['leave_days'];

            // Calculate per day salary
            $per_day_salary = $basic_salary / $working_days;

            // Calculate absent days and deduction
            $absent_days = $working_days - ($present_days + $half_days + $leave_days);
            $absent_deduction_amount = $absent_days * $per_day_salary;

            // Apply leave deductions
            $deduction_days = max($leave_days - $applicable_leaves, 0);
            $leave_deduction_amount = $deduction_days * $per_day_salary;

            // Calculate salary components
            $salaryComponents = [];
            $gross_salary = $basic_salary;
            $total_deductions = 0;

            // Process additive components
            foreach ($salaryParams as $param) {
                if ($param['type'] === 'add') {
                    $base_salary = ($param['calculate_on'] === 'basic') ? $basic_salary : $gross_salary;
                    $calculated_value = $param['percentage_bool'] ? ($base_salary * $param['percent_amount'] / 100) : $param['percent_amount'];
                    
                    // Check applicability
                    if (($param['applicable_if_amount_lte'] > 0 && $basic_salary > $param['applicable_if_amount_lte']) ||
                        ($param['applicable_if_amount_gte'] > 0 && $basic_salary < $param['applicable_if_amount_gte'])) {
                        continue;
                    }

                    $salaryComponents[$param['name']] = $calculated_value;
                    $gross_salary += $calculated_value;
                }
            }

            // Process deductive components
            foreach ($salaryParams as $param) {
                if ($param['type'] === 'deduct') {
                    $base_salary = ($param['calculate_on'] === 'basic') ? $basic_salary : $gross_salary;
                    $calculated_value = $param['percentage_bool'] ? ($base_salary * $param['percent_amount'] / 100) : $param['percent_amount'];
                    
                    // Check applicability
                    if (($param['applicable_if_amount_lte'] > 0 && $basic_salary > $param['applicable_if_amount_lte']) ||
                        ($param['applicable_if_amount_gte'] > 0 && $basic_salary < $param['applicable_if_amount_gte'])) {
                        continue;
                    }

                    $salaryComponents[$param['name']] = -$calculated_value;
                    $total_deductions += $calculated_value;
                }
            }

            $net_salary = $gross_salary - $total_deductions - $leave_deduction_amount - $absent_deduction_amount;

            $result[] = [
                "faculty_id" => (int)$staff['id'],
                "name" => $staff['name'],
                "designation" => $staff['designation'],
                "role" => $staff['role'],
                "basic_salary" => $basic_salary,
                "per_day_salary" => number_format($per_day_salary, 2),
                "working_days" => $working_days,
                "present_days" => $present_days,
                "half_days" => $half_days,
                "absent_days" => $absent_days,
                "leave_days" => $leave_days,
                "allowed_leaves" => $applicable_leaves,
                "deduction_leaves" => $deduction_days,
                "leave_deduction" => number_format($leave_deduction_amount, 2),
                "absent_days_salary_cut" => number_format($absent_deduction_amount, 2),
                "salary_components" => $salaryComponents,
                "gross_salary" => number_format($gross_salary, 2),
                "total_deductions" => number_format($total_deductions, 2),
                "net_salary" => number_format($net_salary, 2)
            ];
        }
        return [
            "start_date" => $start_date,
            "end_date" => $end_date,
            "salary_data" => $result
        ];
    } catch (PDOException $e) {
        errorreturn(["message" => $e->getMessage()]);
    }
}

function getApplicableLeaves($allowed_leaves, $basis, $payroll_period)
{
    $divisor = 1; // Default is yearly

    switch ($basis) {
        case 'weekly':
            $divisor = 52; // 52 weeks in a year
            break;
        case 'monthly':
            $divisor = 12; // 12 months in a year
            break;
        case 'quarterly':
            $divisor = 4; // 4 quarters in a year
            break;
        case 'half_yearly':
            $divisor = 2; // 2 half-years in a year
            break;
        case 'yearly':
        default:
            $divisor = 1; // Full year, no division
            break;
    }

    // Calculate applicable leaves for the given payroll period
    return round($allowed_leaves / $divisor, 2);
}
function getPayrollPeriodType($start_date, $end_date)
{
    $start = new DateTime($start_date);
    $end = new DateTime($end_date);
    $diff = $start->diff($end)->days; // Get difference in days

    if ($diff <= 7) {
        return 'weekly';
    } elseif ($diff <= 31) {
        return 'monthly';
    } elseif ($diff <= 93) {
        return 'quarterly';
    } elseif ($diff <= 186) {
        return 'half-yearly';
    } else {
        return 'yearly';
    }
}

function fetch_payroll_log($conn_rjllearning_specific, $conn_rjllearning, $institution_id, $branch_id, $search_string, $from_date, $to_date, $date_created, $limit, $offset)
{

    try {
        $sql_count = "SELECT  count(*) as total_rows FROM payroll_log where institution_id =?";
        $array_params = [$institution_id];

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

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

        if ($date_created != null) {
            $sql_count .= " AND date_created = ?";
            $array_params[] = $date_created;
        }

        // Add date range condition
        if ($from_date != null && $to_date != null) {
            $sql_count .= " AND date_created BETWEEN ? AND ?";
            $array_params[] = $from_date;
            $array_params[] = $to_date;
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_count);
        $stmt->execute($array_params);
        $result_total = $stmt->fetchAll();
        $totalrows = $result_total[0]['total_rows'];

        if ($limit == 'all') {

            $sql_payroll = "SELECT * FROM payroll_log where institution_id = ?";
            $array_params = [$institution_id];

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

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

            if ($date_created != null) {
                $sql_payroll .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_payroll .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }

            $sql_payroll .=  "order by date_created desc, time_created desc";
        } else {
            $sql_payroll = "SELECT * FROM payroll_log where institution_id =?";
            $array_params = [$institution_id];

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

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

            if ($date_created != null) {
                $sql_payroll .= " AND date_created = ?";
                $array_params[] = $date_created;
            }

            // Add date range condition
            if ($from_date != null && $to_date != null) {
                $sql_payroll .= " AND date_created BETWEEN ? AND ?";
                $array_params[] = $from_date;
                $array_params[] = $to_date;
            }
            $sql_payroll .= " order by date_created desc, time_created desc ";
            $sql_payroll .= "LIMIT " . $offset . " ," . $limit . "";
        }

        $stmt = $conn_rjllearning_specific->prepare($sql_payroll);
        $stmt->execute($array_params);
        $session_rows = $stmt->fetchAll();

        // if (!$session_rows) {
        //     errorreturn("No data found");
        // }

        $array_return = [];

        foreach ($session_rows as $rows) {
            $other_staff_role = $rows['other_staff_role'];
            $time_created = $rows['time_created'] !== null ? date('H:i a', $rows['time_created']) : null;
            $branch_info = ($rows['branch_id'] == 'all')
                ? array('branch_id' => 'all', 'name' => ucwords('all'))
                : getBranchInfo($conn_rjllearning_specific, $institution_id, $rows['branch_id']);

            $categoryQuery = "SELECT id, name FROM payroll_category WHERE id = ? AND institution_id = ?";
            $categoryStmt = $conn_rjllearning_specific->prepare($categoryQuery);
            $categoryStmt->execute([$rows['category_id'], $institution_id]);
            $categoryData = $categoryStmt->fetch(PDO::FETCH_ASSOC);

            if (!$categoryData) {
                errorreturn("Invalid category_id.");
            }

            if ($categoryData && is_array($categoryData)) {
                $category_details = [
                    "category_id" => $categoryData['id'],
                    "category_name" => $categoryData['name']
                ];
            }

            $staff_group_details = null;

            if ($other_staff_role != null) {
                $query = "SELECT id, group_name FROM other_staff_groups WHERE id = ? AND institution_id = ?";
                $stmt = $conn_rjllearning_specific->prepare($query);
                $stmt->execute([$other_staff_role, $institution_id]);
                $staff_group_data = $stmt->fetch(PDO::FETCH_ASSOC);

                if ($staff_group_data && is_array($staff_group_data)) {
                    $staff_group_details = [
                        "id" => $staff_group_data['id'],
                        "group_name" => $staff_group_data['group_name']
                    ];
                }
            }
            $array_return[] = array(
                'payroll_log_id' => $rows['id'],
                'institution_id' => $rows['institution_id'],
                'branch' => $branch_info,
                'start_date' => $rows['start_date'],
                'end_date' => $rows['end_date'],
                'role' => $rows['role'],
                'other_staff_role' => $staff_group_details,
                'category' => $category_details,
                'log_id' => $rows['log_id'],
                'date_created' => $rows['date_created'],
                'time_created' => $time_created,
                'created_by' => $rows['created_by'],
            );
        }

        if (count($session_rows) < 50) {
            return array('total_rows' => $totalrows, 'payroll_log__info' => $array_return, 'maxreach' => 1);
        } else {
            return array('total_rows' => $totalrows, 'payroll_log__info' => $array_return, 'maxreach' => 0);
        }
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}

function get_specific_payroll_log($conn_rjllearning_specific, $dynamoDb, $dynamoDB_table_name, $institution_id, $payroll_log_id)
{
    try {

        $sql = "SELECT * FROM payroll_log WHERE institution_id = ? AND id = ? ";
        $stmt = $conn_rjllearning_specific->prepare($sql);
        $stmt->execute([$institution_id, $payroll_log_id]);
        $payroll_log = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$payroll_log) {
            errorreturn("Payroll log not found.");
        }

        $array_return = [];
        $time_created = $payroll_log['time_created'] !== null ? date('H:i a', $payroll_log['time_created']) : null;
        $branch_info = ($payroll_log['branch_id'] == 'all')
            ? array('branch_id' => 'all', 'name' => ucwords('all'))
            : getBranchInfo($conn_rjllearning_specific, $institution_id, $payroll_log['branch_id']);

        $sql_payroll_log = "SELECT id, name FROM payroll_category WHERE id = ? AND institution_id = ?";
        $categoryStmt = $conn_rjllearning_specific->prepare($sql_payroll_log);
        $categoryStmt->execute([$payroll_log['category_id'], $institution_id]);
        $categoryData = $categoryStmt->fetch(PDO::FETCH_ASSOC);

        if (!$categoryData) {
            errorreturn("Invalid category_id.");
        }

        if ($categoryData && is_array($categoryData)) {
            $category_details = [
                "category_id" => $categoryData['id'],
                "category_name" => $categoryData['name']
            ];
        }

        $other_staff_role = $payroll_log['other_staff_role'];

        $staff_group_details = null;

        if ($other_staff_role != null) {
            $query = "SELECT id, group_name FROM other_staff_groups WHERE id = ? AND institution_id = ?";
            $stmt = $conn_rjllearning_specific->prepare($query);
            $stmt->execute([$other_staff_role, $institution_id]);
            $staff_group_data = $stmt->fetch(PDO::FETCH_ASSOC);

            if ($staff_group_data && is_array($staff_group_data)) {
                $staff_group_details = [
                    "id" => $staff_group_data['id'],
                    "group_name" => $staff_group_data['group_name']
                ];
            }
        }

        $salaryDataResponse = get_Salary_Data_ById($dynamoDb, $dynamoDB_table_name, $institution_id, $payroll_log['log_id']);
        if (isset($salaryDataResponse['status']) && $salaryDataResponse['status'] === 'error') {
            errorreturn("Error fetching salary data: " . $salaryDataResponse['message']);
        }

        $array_return = ([
            'payroll_log_id' => $payroll_log['id'],
            'institution_id' => $payroll_log['institution_id'],
            'branch' => $branch_info,
            'start_date' => $payroll_log['start_date'],
            'end_date' => $payroll_log['end_date'],
            'role' => $payroll_log['role'],
            'other_staff_role' => $staff_group_details,
            'category' => $category_details,
            'log_id' => $payroll_log['log_id'],
            'date_created' => $payroll_log['date_created'],
            'time_created' => $time_created,
            'created_by' => $payroll_log['created_by'],
            'salary_data' => $salaryDataResponse['salary_data']
        ]);

        return  $array_return;
    } catch (Exception $e) {
        errorreturn("Error: " . $e->getMessage());
        return;
    }
}
