<?php
function fetchReminder($conn_rjllearning, $conn_rjllearning_specific, $institution_id, $branch_id, $userid, $search_string, $mindate, $maxdate, $status, $role)
{

    $sql = "SELECT count(*) as total_rows from reminders where (userid = ? or ";
    if ($role == 'admin' or $role == 'superadmin') {
        $sql .= "shared_with = ?";
        $fill_role_specific = ['all_admins'];
    } else if ($role == 'principal') {
        $sql .= "shared_with = ? or shared_with = ?";
        $fill_role_specific = ['all_principals', 'all_teachers'];
    } else if ($role == 'teacher') {
        $sql .= "shared_with = ?";
        $fill_role_specific = ['all_teachers'];
    }
    $sql .= " or (shared_with = ? and shared_users_specific LIKE ?)) and reminder_date >= ? and reminder_date <= ?";
    $array_params = [$userid, ...$fill_role_specific, 'specific_users', '%"' . $userid . '"%', $mindate, $maxdate];
    
    // 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 ($institution_id != 'all') {
        $sql .= " AND (institution_id = ? OR institution_id = 'all')";
        $array_params[] = $institution_id;
        
        if ($branch_id != 'all') {
            $sql .= " AND (branch_id = ? OR branch_id = 'all')";
            $array_params[] = $branch_id;
        }
    }
    
    if ($search_string !== null) {
        $sql .= " AND (title LIKE ? or description LIKE ?)";
        $array_params[] = '%' . $search_string . '%';
        $array_params[] = '%' . $search_string . '%';
    }

    if ($status != 'all') {
        if ($status == 1) {
            // checked
            $sql .= " AND checked_by LIKE ?";
            $array_params[] = '%"user_id":' . $userid . ',%';
        } else if ($status == 0) {
            // unchecked
            $sql .= " AND (checked_by IS NULL or checked_by NOT LIKE ? )";
            $array_params[] = '%"user_id":' . $userid . ',%';
        }
    }

    $sql .= " order by reminder_date desc, date_time_created desc ";

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

    $sql_fetch = "SELECT * FROM reminders WHERE (userid = ?";
    if ($role == 'admin' or $role == 'superadmin') {
        $sql_fetch .= " OR shared_with = ?";
        $fill_role_specific = ['all_admins'];
    } else if ($role == 'principal') {
        $sql_fetch .= " OR shared_with = ? OR shared_with = ?";
        $fill_role_specific = ['all_principals', 'all_teachers'];
    } else if ($role == 'teacher') {
        $sql_fetch .= " OR shared_with = ?";
        $fill_role_specific = ['all_teachers'];
    }
    $sql_fetch .= " or (shared_with = ? and shared_users_specific LIKE ?)) and reminder_date >= ? and reminder_date <= ?";
    $array_params = [$userid, ...$fill_role_specific, 'specific_users', '%"' . $userid . '"%', $mindate, $maxdate,];
    // if ($institution_id != 'all') {
    //     $sql_fetch .= " and (institution_id = ? or (institution_id = ? and (branch_id = ? or branch_id = ?)))";
    //     $array_params = [...$array_params, 'all', $institution_id, $branch_id, 'all'];
    // }
    
    // if ($institution_id == 'all') {
    //         $sql_fetch .= "";
    //     } else if ($institution_id !=  null  && $institution_id != 'all') {
    //         $sql_fetch .= " AND institution_id = ? ";
    //         $array_params[] = $institution_id;
    //     }
    //     if ($branch_id == 'all') {
    //         $sql_fetch .= "";
    //     } else if ($branch_id !=  null  && $branch_id != 'all') {
    //         $sql_fetch .= " AND branch_id = ? ";
    //         $array_params[] = $branch_id;
    //     }
    
    if ($institution_id != 'all') {
        $sql_fetch .= " AND (institution_id = ? OR institution_id = 'all')";
        $array_params[] = $institution_id;
        
        if ($branch_id != 'all') {
            $sql_fetch .= " AND (branch_id = ? OR branch_id = 'all')";
            $array_params[] = $branch_id;
        }
    }

    if ($search_string !== null) {
        $sql_fetch .= " AND (title LIKE ? OR description LIKE ?)";
        $array_params[] = '%' . $search_string . '%';
        $array_params[] = '%' . $search_string . '%';
    }
    if ($status != 'all') {
        if ($status == 1) {
            // checked
            $sql_fetch .= " AND checked_by LIKE ?";
            $array_params[] = '%"user_id":' . $userid . ',%';
        } else if ($status == 0) {
            // unchecked
            $sql_fetch .= " AND (checked_by IS NULL OR checked_by NOT LIKE ?)";
            $array_params[] = '%"user_id":' . $userid . ',%';
        }
    }

    $sql_fetch .= " ORDER BY reminder_date DESC, date_time_created DESC LIMIT 25";

    $stmt_fetch = $conn_rjllearning_specific->prepare($sql_fetch);
    $stmt_fetch->execute($array_params);
    $result = $stmt_fetch->fetchAll(PDO::FETCH_ASSOC);

    $data = array();
    $user_ids_to_fetch = [];
    foreach ($result as $rows) {

        $sql_role = "SELECT role FROM users WHERE id = ?";
        $stmt_role = $conn_rjllearning->prepare($sql_role);
        $stmt_role->execute([$rows['userid']]);
        $fetch_role = $stmt_role->fetch();

        $created_user_role = $fetch_role['role'];

        // Initialize the checked_by array to store names of users who checked the reminder
        $checked_by = !empty($rows['checked_by']) ? json_decode($rows['checked_by'], true) : null;
        $checked = "0"; // Default to unchecked

        if ($checked_by !== null && is_array($checked_by) && !empty($checked_by)) {
            $user_ids_to_fetch = [];

            foreach ($checked_by as $entry) {
                if (isset($entry['user_id'])) {
                    $user_ids_to_fetch[] = (int) $entry['user_id'];

                    if ($entry['user_id'] == $userid) {
                        $checked = "1"; // User has checked
                    }
                }
            }

            // Fetch user names if necessary
            $user_names = [];
            if (!empty($user_ids_to_fetch)) {
                $user_ids = array_unique($user_ids_to_fetch);
                $placeholders = implode(',', array_fill(0, count($user_ids), '?'));
                $sql_users = "SELECT id, name FROM users WHERE id IN ($placeholders)";
                $stmt_users = $conn_rjllearning->prepare($sql_users);
                $stmt_users->execute($user_ids);
                $user_results = $stmt_users->fetchAll(PDO::FETCH_ASSOC);

                foreach ($user_results as $user) {
                    $user_names[$user['id']] = $user['name'];
                }
            }

            // ✅ Ensure every entry in `checked_by` has a `name`
            foreach ($checked_by as &$entry) {
                $entry['name'] = isset($user_names[$entry['user_id']]) ? $user_names[$entry['user_id']] : "Unknown";
            }
        }

        // ✅ Ensure `$checked_by` is always an array
        $checked_by = ($checked_by !== null) ? json_encode($checked_by) : null;


        // Filter based on status and checked status
        if (($status == 1 && $checked == 1) || ($status == 0 && $checked == 0) || $status == "all") {
            $shared_users = [];
            if (!empty($rows['shared_users_specific'])) {
                $user_ids = json_decode($rows['shared_users_specific'], true);
                if (is_array($user_ids) && count($user_ids) > 0) {
                    $placeholders = str_repeat('? ,', count($user_ids) - 1) . '?';
                    $sql = "SELECT id, name, role FROM users WHERE id IN ($placeholders)";
                    $stmt = $conn_rjllearning->prepare($sql);
                    $stmt->execute($user_ids);
                    $shared_users = $stmt->fetchAll(PDO::FETCH_ASSOC);
                }
            }

            // Add the reminder data to the response
            $data[] = array(
                'id' => $rows['id'],
                'institution_id' => $rows['institution_id'],
                'branch_id' => $rows['branch_id'],
                'created_user_role' => $created_user_role,
                'title' => $rows['title'],
                'description' => $rows['description'],
                'priority' => $rows['priority'],
                'shared_with' => $rows['shared_with'],
                'shared_users_specific' => $shared_users,
                'date_time_created' => $rows['date_time_created'],
                'created_by' => $rows['created_by'],
                'reminder_date' => $rows['reminder_date'],
                'reminder_time' => $rows['reminder_time'],
                // 'checked_by' => json_decode($checked_by, true),
                'checked_by' => !empty($checked_by) ? json_decode($checked_by, true) : null,
                'checked' => $checked,
                'last_modified_by' => $rows['last_modified_by'],
                'last_modified_date' => $rows['last_modified_date'],
                'repeat_on' => $rows['repeat_on']
            );
        }
    }

    if (count($result) < 25) {
        return array('total_rows' => $totalrows, 'reminder_data' => $data, 'maxreach' => 1);
    } else {
        return array('total_rows' => $totalrows, 'reminder_data' => $data, 'maxreach' => 0);
    }
}

function fetch_specific_reminder($conn_rjllearning, $conn_rjllearning_specific, $reminder_id, $userid)
{
    // Prepare the SQL query to fetch the reminder
    $sql = "SELECT * FROM reminders WHERE id = ? AND userid = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$reminder_id, $userid]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $array_return = [];
    if (count($result) == 1) {
        foreach ($result as $res) {

            if ($res['branch_id'] == 'all') {
                $branch_info = array('branch_id' => 'all', 'name' => ucwords('all'));
            } else {
                $branch_info = getBranchInfo($conn_rjllearning_specific, $res['institution_id'], $res['branch_id']);
            }
            // Check for shared users if specific
            $shared_users = [];
            if (!empty($res['shared_users_specific'])) {
                $user_ids = json_decode($res['shared_users_specific'], true);
                if (is_array($user_ids) && count($user_ids) > 0) {
                    $placeholders = str_repeat('? ,', count($user_ids) - 1) . '?';
                    $sql = "SELECT id, name, role FROM users WHERE id IN ($placeholders)";
                    $stmt = $conn_rjllearning->prepare($sql);
                    $stmt->execute($user_ids);
                    $shared_users = $stmt->fetchAll(PDO::FETCH_ASSOC);
                }
            }

            $sql_role = "SELECT role FROM users WHERE id = ?";
            $stmt_role = $conn_rjllearning->prepare($sql_role);
            $stmt_role->execute([$res['userid']]);
            $fetch_role = $stmt_role->fetch();

            $created_user_role = $fetch_role['role'];

            $checked_by = $res['checked_by'] !== null ? json_decode($res['checked_by'], true) : null;
            $reminder_info = array(
                "reminder_id" => $res['id'],
                "institution_id" => $res['institution_id'],
                "branch_id" => $branch_info,
                "userid" => $res['userid'],
                "created_user_role" => $created_user_role,
                "title" => $res['title'],
                "description" => $res['description'],
                "priority" => $res['priority'],
                "shared_with" => $res['shared_with'],
                "shared_users_specific" => $shared_users,
                "date_time_created" => $res['date_time_created'],
                "created_by" => $res['created_by'],
                "reminder_date" => $res['reminder_date'],
                "reminder_time" => $res['reminder_time'],
                "checked_by" => $checked_by,
                "repeat_on" => $res['repeat_on'],
                "reference" => $res['reference']
            );

            $array_return[] = $reminder_info;
        }
        return $array_return;
    } else {
        return "Reminder info not found";
    }
}

function deleteReminder($conn_rjllearning_specific, $conn_rjllearning, $userid, $role, $reminder_id)
{
    // Fetch the reminder details
    $query = "SELECT * FROM reminders WHERE id = ?";
    $stmt = $conn_rjllearning_specific->prepare($query);
    $stmt->execute([$reminder_id]);
    $reminder = $stmt->fetch();

    if (!$reminder) {
        return "Reminder not found.";
    }

    $reminder_userid = $reminder['userid'];
    $existing_reference = $reminder['reference'];

    // Fetch the institution and branch details for the reminder creator
    $creator_query = "SELECT role, institution_id, branch_id FROM users WHERE id = ?";
    $creator_stmt = $conn_rjllearning->prepare($creator_query);
    $creator_stmt->execute([$reminder_userid]);
    $creator_details = $creator_stmt->fetch();

    if (!$creator_details) {
        return "No details found.";
    }

    $creator_institution_id = $creator_details['institution_id'];
    $creator_branch_id = $creator_details['branch_id'];
    $creator_role = $creator_details['role'];

    if ($role == "principal") {
        $sql_principal = "SELECT institution_id, branch_id FROM users WHERE id = ?";
        $stmt_principal = $conn_rjllearning->prepare($sql_principal);
        $stmt_principal->execute([$userid]);
        $fetch_principal = $stmt_principal->fetch();
        $user_institution_id = $fetch_principal['institution_id'];
        $user_branch_id = $fetch_principal['branch_id'];
    }

    $canDelete = false;

    // Admin and Super Admin can delete any reminder
    if ($role == "admin" || $role == "superadmin") {
        $canDelete = true;
    }
    // Principal can delete reminders created by users in their institution and branch
    elseif (
        $role == "principal" &&
        ($creator_role == "teacher" || $userid == $reminder_userid) &&
        $user_institution_id == $creator_institution_id &&
        $user_branch_id == $creator_branch_id
    ) {
        $canDelete = true;
    } elseif ($role == "teacher" && $userid == $reminder_userid) {
        $canDelete = true;
    }

    if ($canDelete) {
        if ($existing_reference != null) {
            $deleteQuery = "DELETE FROM reminders WHERE id = ?";
            $deleteStmt = $conn_rjllearning_specific->prepare($deleteQuery);
            $deleteStmt->execute([$reminder_id]);

            // Delete reminders with matching reference
            $deleteReferenceQuery = "DELETE FROM reminders WHERE reference = ?";
            $deleteReferenceStmt = $conn_rjllearning_specific->prepare($deleteReferenceQuery);
            $deleteReferenceStmt->execute([$existing_reference]);
        } else {
            $deleteQuery = "DELETE FROM reminders WHERE id = ?";
            $deleteStmt = $conn_rjllearning_specific->prepare($deleteQuery);
            $deleteStmt->execute([$reminder_id]);
        }

        // Delete from repeat_reminders table
        $deleteRepeatQuery = "DELETE FROM repeat_reminders WHERE reminder_id = ?";
        $deleteRepeatStmt = $conn_rjllearning->prepare($deleteRepeatQuery);
        $deleteRepeatStmt->execute([$reminder_id]);

        return "Reminder and its related data deleted successfully.";
    } else {
        return "You do not have permission to delete this reminder.";
    }
}

function filterCheckedBy($conn_rjllearning, $checkedby_data, $postdata, $shared_users_specific, $existing_userid, $role)
{
    $newCheckedBy = [];

    // Check if $checkedby_data is not null
    if (is_array($checkedby_data) && !empty($checkedby_data)) {
        $userIds = array_column($checkedby_data, 'user_id');

        $placeholders = implode(',', array_fill(0, count($userIds), '?'));
        $query = "SELECT id, role FROM users WHERE id IN ($placeholders)";
        $stmt = $conn_rjllearning->prepare($query);
        $stmt->execute($userIds);
        $roles = $stmt->fetchAll();

        $role_with_userid = [];
        foreach ($roles as $user_role) {
            $role_with_userid[$user_role['id']] = $user_role['role'];
        }
    } else {
        // If $checkedby_data is null or empty, return null
        return null;
    }

    if ($postdata['shared_with'] === 'specific_users') {
        $check_roles = json_decode($shared_users_specific, true);
    } else {
        switch ($postdata['shared_with']) {
            case 'all_admins':
                $check_roles = ['admin', 'superadmin'];
                break;
            case 'all_principals':
                $check_roles = ['principal'];
                break;
            case 'all_teachers':
                $check_roles = ['teacher'];
                break;
            case 'only_me':
                $check_roles = [$role];
                break;
            default:
                $check_roles = [];
                break;
        }
    }

    foreach ($checkedby_data as $entry) {
        $userId = $entry['user_id'];
        if (
            $userId == $existing_userid ||
            (isset($role_with_userid[$userId]) && in_array($role_with_userid[$userId], $check_roles)) ||
            in_array($userId, $check_roles)
        ) {
            $newCheckedBy[] = $entry;
        }
    }

    return empty($newCheckedBy) ? null : json_encode($newCheckedBy);
}

function createEntryInRepeatReminders($conn_rjllearning, $workspace_id, $id, $repeat_on, $reminder_date)
{
    $insert_query = "INSERT INTO repeat_reminders (workspace_id, reminder_id, repeat_on, date_of_reminder) VALUES (?, ?, ?, ?)";
    $stmt_insert = $conn_rjllearning->prepare($insert_query);
    $stmt_insert->execute([$workspace_id, $id, $repeat_on, $reminder_date]);
}

function updateCheckedBy($reminder_id, $userid, $checked, $conn_rjllearning_specific)
{
    // Fetch the current `checked_by` data (if any)
    $sql = "SELECT checked_by FROM reminders WHERE id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$reminder_id]);

    if ($stmt->rowCount() > 0) {
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        // Decode the `checked_by` column or initialize it as an empty array
        $checked_by = $row['checked_by'] ? json_decode($row['checked_by'], true) : [];

        if ($checked == 1) {
            $found = false;
            foreach ($checked_by as &$entry) {
                if ($entry['user_id'] == $userid) {
                    $found = true; // User exists
                    break; // Exit the loop early
                }
            }
            if (!$found) {
                // Add a new entry if the user is not already in the array
                $checked_by[] = [
                    'user_id' => $userid,
                    'time_checked' => time() // Add timestamp for the new user
                ];
            }
        } elseif ($checked == 0) {
            // Handle removing an unchecked entry
            foreach ($checked_by as $key => $entry) {
                if ($entry['user_id'] == $userid) {
                    unset($checked_by[$key]); // Remove the user entry
                    break; // Exit the loop after removal
                }
            }
            if (empty($checked_by)) {
                $checked_by = null;
            } else {
                // Re-index the array to ensure consistent structure
                $checked_by = array_values($checked_by);
            }
        }
        // Convert the updated array back to JSON
        $checked_by_json = $checked_by !== null ? json_encode($checked_by) : null;

        $sql_update = "UPDATE reminders SET checked_by = ? WHERE id = ?";
        $stmt_update = $conn_rjllearning_specific->prepare($sql_update);
        if ($stmt_update->execute([$checked_by_json, $reminder_id])) {
            return "Reminder updated successfully.";
        } else {
            return "Error while updating `checked_by`.";
        }
    } else {
        return "Reminder not found.";
    }
}

function fetch_specific_reminders($conn_rjllearning, $conn_rjllearning_specific, $reminder_id, $userid, $institution_id, $branch_id)
{
    // Prepare the SQL query to fetch the reminder
    $sql = "SELECT * FROM reminders WHERE id = ? AND userid = ? AND institution_id = ? AND branch_id = ?";
    $stmt = $conn_rjllearning_specific->prepare($sql);
    $stmt->execute([$reminder_id, $userid, $institution_id, $branch_id]);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $array_return = [];
    if (count($result) == 1) {
        foreach ($result as $res) {
            // Check for shared users if specific
            $shared_users = [];
            if (!empty($res['shared_users_specific'])) {
                $user_ids = json_decode($res['shared_users_specific'], true);
                if (is_array($user_ids) && count($user_ids) > 0) {
                    $placeholders = str_repeat('? ,', count($user_ids) - 1) . '?';
                    $sql = "SELECT id, name, role FROM users WHERE id IN ($placeholders)";
                    $stmt = $conn_rjllearning->prepare($sql);
                    $stmt->execute($user_ids);
                    $shared_users = $stmt->fetchAll(PDO::FETCH_ASSOC);
                }
            }

            $branch_info = getBranchInfo($conn_rjllearning_specific, $res['institution_id'], $res['branch_id']);
            $checked_by = $res['checked_by'] !== null ? json_decode($res['checked_by'], true) : null;
            $reminder_info = array(
                "reminder_id" => $res['id'],
                "institution_id" => $res['institution_id'],
                "branch_id" => $branch_info,
                "userid" => $res['userid'],
                "title" => $res['title'],
                "description" => $res['description'],
                "priority" => $res['priority'],
                "shared_with" => $res['shared_with'],
                "shared_users_specific" => $shared_users,
                "date_time_created" => $res['date_time_created'],
                "created_by" => $res['created_by'],
                "reminder_date" => $res['reminder_date'],
                "reminder_time" => $res['reminder_time'],
                "checked_by" => $checked_by,
                "repeat_on" => $res['repeat_on'],
                "reference" => $res['reference']
            );

            $array_return[] = $reminder_info;
        }
        return $array_return;
    } else {
        return "Reminder info not found";
    }
}
