/home/techb158/workloadmatch.com/api/routes
Edit: /home/techb158/workloadmatch.com/api/routes/chat.php (9934B)
prepare($sql);
if ($params) $stmt->bind_param($types, ...$params);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$sql = "SELECT cm.* FROM chat_messages cm $whereClause ORDER BY cm.created_at DESC LIMIT ? OFFSET ?";
$stmt = $mysqli->prepare($sql);
$bindParams = array_merge($params, [$perPage, $offset]);
$bindTypes = $types . 'ii';
if ($bindParams) $stmt->bind_param($bindTypes, ...$bindParams);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
// Resolve sender/receiver names
foreach ($items as &$msg) {
$msg['sender_name'] = resolveUserName($msg['sender_id'], $msg['sender_type'], $mysqli);
$msg['receiver_name'] = resolveUserName($msg['receiver_id'], $msg['receiver_type'], $mysqli);
}
Response::paginated(array_reverse($items), $total, $page, $perPage);
}
function getConversationMessages(string $otherUserId, mysqli $mysqli): void
{
Auth::requireLogin();
$userId = Auth::getUserId();
$userType = Auth::getUserType();
[$page, $perPage, $offset] = getPagination();
$total = 0;
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM chat_messages WHERE ((sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?))");
$stmt->bind_param('iiii', $userId, $otherUserId, $otherUserId, $userId);
$stmt->execute();
$stmt->bind_result($total);
$stmt->fetch();
$stmt->close();
$stmt = $mysqli->prepare("SELECT * FROM chat_messages WHERE ((sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?)) ORDER BY created_at ASC LIMIT ? OFFSET ?");
$stmt->bind_param('iiiiii', $userId, $otherUserId, $otherUserId, $userId, $perPage, $offset);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($items as &$msg) {
$msg['sender_name'] = resolveUserName($msg['sender_id'], $msg['sender_type'], $mysqli);
$msg['receiver_name'] = resolveUserName($msg['receiver_id'], $msg['receiver_type'], $mysqli);
}
// Mark messages as read
$stmt = $mysqli->prepare("UPDATE chat_messages SET is_read = 1 WHERE sender_id = ? AND receiver_id = ? AND is_read = 0");
$stmt->bind_param('ii', $otherUserId, $userId);
$stmt->execute();
$stmt->close();
Response::paginated($items, $total, $page, $perPage);
}
function sendMessage(?array $body, mysqli $mysqli): void
{
Auth::requireLogin();
$senderId = Auth::getUserId();
$senderType = Auth::getUserType();
// Map user type to short type for chat
$typeMap = [
'manager_profile' => 'manager',
'teacher_profile' => 'teacher',
'master_profile' => 'master',
'admin_profile' => 'admin',
];
$senderTypeShort = $typeMap[$senderType] ?? $senderType;
$receiverId = $body['receiver_id'] ?? null;
$receiverType = $body['receiver_type'] ?? '';
$message = trim($body['message'] ?? '');
if (!$receiverId || !$receiverType || !$message) {
Response::validationError(['receiver_id', 'receiver_type', 'message' => 'Required']);
}
// Map receiver type
$typeMapReverse = [
'manager' => 'manager_profile',
'teacher' => 'teacher_profile',
'master' => 'master_profile',
'admin' => 'admin_profile',
];
$receiverTypeFull = $typeMapReverse[$receiverType] ?? $typeMapReverse[$receiverType] ?? $receiverType;
$receiverTypeShort = array_search($receiverTypeFull, $typeMap) ?: $receiverType;
$stmt = $mysqli->prepare("INSERT INTO chat_messages (sender_id, sender_type, receiver_id, receiver_type, message, is_read, created_at) VALUES (?, ?, ?, ?, ?, 0, NOW())");
$stmt->bind_param('ssiss', $senderId, $senderTypeShort, $receiverId, $receiverTypeShort, $message);
$stmt->execute();
$newId = $stmt->insert_id;
$stmt->close();
$stmt = $mysqli->prepare("SELECT * FROM chat_messages WHERE msg_id = ?");
$stmt->bind_param('i', $newId);
$stmt->execute();
$result = $stmt->get_result();
$item = $result->fetch_assoc();
$stmt->close();
$item['sender_name'] = resolveUserName($item['sender_id'], $item['sender_type'], $mysqli);
$item['receiver_name'] = resolveUserName($item['receiver_id'], $item['receiver_type'], $mysqli);
Response::created($item);
}
function getUnreadCount(mysqli $mysqli): void
{
Auth::requireLogin();
$userId = Auth::getUserId();
$userType = Auth::getUserType();
$typeMap = [
'manager_profile' => 'manager',
'teacher_profile' => 'teacher',
];
$typeShort = $typeMap[$userType] ?? '';
$stmt = $mysqli->prepare("SELECT COUNT(*) FROM chat_messages WHERE receiver_id = ? AND receiver_type = ? AND is_read = 0");
$stmt->bind_param('is', $userId, $typeShort);
$stmt->execute();
$stmt->bind_result($count);
$stmt->fetch();
$stmt->close();
Response::success(['unread_count' => (int)$count]);
}
function markAsRead(?string $msgId, ?array $body, mysqli $mysqli): void
{
Auth::requireLogin();
$userId = Auth::getUserId();
$stmt = $mysqli->prepare("UPDATE chat_messages SET is_read = 1 WHERE receiver_id = ? AND (msg_id = ? OR ? = '__all__')");
$all = $msgId ?? '__all__';
$stmt->bind_param('iis', $userId, $msgId, $all);
$stmt->execute();
$affected = $mysqli->affected_rows;
$stmt->close();
Response::success(null, "Marked $affected message(s) as read");
}
function listConversations(mysqli $mysqli): void
{
Auth::requireLogin();
$userId = Auth::getUserId();
$userType = Auth::getUserType();
$typeMap = [
'manager_profile' => 'manager',
'teacher_profile' => 'teacher',
];
$typeShort = $typeMap[$userType] ?? '';
// Get distinct conversation partners
$stmt = $mysqli->prepare("
SELECT
CASE WHEN sender_id = ? THEN receiver_id ELSE sender_id END AS partner_id,
CASE WHEN sender_id = ? THEN receiver_type ELSE sender_type END AS partner_type,
MAX(created_at) AS last_message_time,
(SELECT message FROM chat_messages WHERE (sender_id = ? AND receiver_id = ?) OR (sender_id = ? AND receiver_id = ?) ORDER BY created_at DESC LIMIT 1) AS last_message,
(SELECT COUNT(*) FROM chat_messages WHERE receiver_id = ? AND sender_id = partner_id AND is_read = 0) AS unread_count
FROM chat_messages
WHERE sender_id = ? OR receiver_id = ?
GROUP BY partner_id, partner_type
ORDER BY last_message_time DESC
");
$stmt->bind_param('iiiiiiiii', $userId, $userId, $userId, $userId, $userId, $userId, $userId, $userId, $userId);
$stmt->execute();
$result = $stmt->get_result();
$items = $result->fetch_all(MYSQLI_ASSOC);
$stmt->close();
foreach ($items as &$item) {
$item['partner_name'] = resolveUserName($item['partner_id'], $item['partner_type'], $mysqli);
}
Response::success($items);
}
function resolveUserName($userId, $type, mysqli $mysqli): string
{
$typeMap = [
'manager' => ['manager_profile', 'Manager_ID', 'First_Name', 'Last_Name'],
'teacher' => ['teacher_profile', 'Teacher_ID', 'First_Name', 'Last_Name'],
'master' => ['master_profile', 'Master_ID', 'User_Name', null],
'admin' => ['admin_profile', 'Admin_ID', 'User_Name', null],
];
$t = $typeMap[$type] ?? null;
if (!$t) return 'Unknown';
[$table, $idCol, $fnCol, $lnCol] = $t;
if ($lnCol) {
$stmt = $GLOBALS['mysqli']->prepare("SELECT $fnCol, $lnCol FROM $table WHERE $idCol = ? LIMIT 1");
$stmt->bind_param('i', $userId);
$stmt->execute();
$stmt->bind_result($fn, $ln);
$stmt->fetch();
$stmt->close();
return $fn && $ln ? "$fn $ln" : ($fn ?: 'Unknown');
}
$stmt = $GLOBALS['mysqli']->prepare("SELECT $fnCol FROM $table WHERE $idCol = ? LIMIT 1");
$stmt->bind_param('i', $userId);
$stmt->execute();
$stmt->bind_result($name);
$stmt->fetch();
$stmt->close();
return $name ?: 'Unknown';
}