<?php
declare(strict_types=1);
require_once __DIR__ . '/../../authentication/api/connect.php';
$user=require_auth(); $role=(string)$user['role']; $id=(int)$user['id']; $db=db();
$hod=null; $student=null;
if($role==='hod'){ $q=$db->prepare('SELECT department_id,campus_id FROM hod_assignments WHERE user_id=?'); $q->execute([$id]); $hod=$q->fetch(); if(!$hod)reply(403,['status'=>'error','message'=>'HOD is not assigned to a department and campus.']); }
if($role==='student'){ $q=$db->prepare('SELECT id,department_id,campus_id FROM students WHERE user_id=?'); $q->execute([$id]); $student=$q->fetch(); if(!$student)reply(403,['status'=>'error','message'=>'Student profile is unavailable.']); }
$module=(string)($_GET['module']??'');if(in_array($module,['group-management-overview','group-project-assignment','my-group'],true))reply(410,['message'=>'This feature has been removed.']); if(!preg_match('/^[a-z0-9-]{1,80}$/',$module)) reply(400,['status'=>'error','message'=>'Invalid module.']);
if(($_SERVER['REQUEST_METHOD']??'GET')==='POST'){
  $data=post_data(250000); $action=(string)($data['action']??'');if($action==='create_group'||array_key_exists('group_id',$data))reply(410,['message'=>'Group actions have been removed. Choose an individual student.']);
  if($role==='staff'&&in_array($action,['create_project','create_guide','save_grade','verify_logbook','mark_attendance'],true)){
    $hod=['department_id'=>(int)($data['department_id']??0),'campus_id'=>(int)($data['campus_id']??0)];
    foreach(['departments'=>'department_id','campuses'=>'campus_id'] as $table=>$field){$q=$db->prepare('SELECT id FROM '.$table.' WHERE id=? AND is_active=1');$q->execute([$hod[$field]]);if(!$q->fetch())reply(422,['message'=>'Choose the department and campus for this action.']);}
  }
  if(in_array($action,['create_admin','create_account'],true)){
    if($role!=='staff'||$module!=='user-accounts-security-logs')reply(403,['status'=>'error','message'=>'Only staff can create administrator or staff accounts from account management.']);
    require_once __DIR__.'/../lib/admin_accounts.php';
    $accountRole=$action==='create_admin'?'admin':(string)($data['account_role']??'');
    try{$created=create_management_account($db,$data,$accountRole);}catch(InvalidArgumentException $e){reply(422,['status'=>'error','message'=>$e->getMessage()]);}catch(DomainException $e){reply(409,['status'=>'error','message'=>$e->getMessage()]);}
    audit_event($id,$role,$accountRole,$created,'create_account',ucfirst($accountRole).' account created');
    reply(201,['status'=>'success','message'=>ucfirst($accountRole).' account created. They can sign in with their email and the password you assigned.']);
  } elseif($action==='save_logbook' && $role==='student'){
    $date=(string)($data['activity_date']??''); $activity=trim((string)($data['practical_activity']??'')); if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)||$activity==='')reply(422,['status'=>'error','message'=>'Enter a valid date and practical activity.']);
    $db->prepare('INSERT INTO logbook_entries(student_id,activity_date,practical_activity) VALUES(?,?,?) ON DUPLICATE KEY UPDATE practical_activity=VALUES(practical_activity),verified_by=NULL,verified_at=NULL')->execute([(int)$student['id'],$date,$activity]);
  } elseif($action==='create_department' && in_array($role,['admin','staff'],true)){
    $name=trim((string)($data['department_name']??'')); $code=trim((string)($data['department_code']??'')); if($name==='')reply(422,['status'=>'error','message'=>'Department name is required.']);
    $db->prepare('INSERT INTO departments(department_code,department_name) VALUES(?,?)')->execute([$code?:null,$name]);
  } elseif($action==='create_project' && in_array($role,['hod','staff'],true)){
    $title=trim((string)($data['title']??''));$description=trim((string)($data['description']??''));$due=(string)($data['due_at']??'');if($title===''||$description==='')reply(422,['status'=>'error','message'=>'Project title and description are required.']);
    if((int)($data['student_id']??0)<1)reply(422,['message'=>'Choose a student.']);
    $check=$db->prepare('SELECT id FROM students WHERE id=? AND department_id=? AND campus_id=?');$check->execute([(int)$data['student_id'],(int)$hod['department_id'],(int)$hod['campus_id']]);if(!$check->fetch())reply(404,['message'=>'Student not found in the selected department and campus.']);
    $db->prepare('INSERT INTO practical_projects(title,description,department_id,campus_id,created_by) VALUES(?,?,?,?,?)')->execute([$title,$description,(int)$hod['department_id'],(int)$hod['campus_id'],$id]);$pid=$db->lastInsertId();
    $db->prepare('INSERT INTO project_assignments(project_id,student_id,assigned_by,due_at) VALUES(?,?,?,?)')->execute([$pid,(int)$data['student_id'],$id,$due?:null]);
    $q=$db->prepare('INSERT INTO notifications(student_id,kind,title,message,created_by) SELECT s.id,\'assignment\',?,?,? FROM students s WHERE s.department_id=? AND s.campus_id=? AND (? IS NULL OR s.id=?)');$only=empty($data['student_id'])?null:(int)$data['student_id'];$q->execute([$title,'A new practical project has been assigned.',$id,(int)$hod['department_id'],(int)$hod['campus_id'],$only,$only]);
  } elseif($action==='create_guide' && in_array($role,['hod','staff'],true)){
    $title=trim((string)($data['title']??''));$explanation=trim((string)($data['explanation']??''));$type=(string)($data['guide_type']??'system_development');$stage=trim((string)($data['development_stage']??''));if($title===''||$explanation===''||!in_array($type,['software_installation','system_development'],true))reply(422,['status'=>'error','message'=>'Complete the guide title, type and explanation.']);if($stage!==''&&!in_array($stage,['requirements','analysis','design','coding','testing','deployment'],true))reply(422,['status'=>'error','message'=>'Select a valid development stage.']);
    $db->prepare('INSERT INTO practical_guides(department_id,campus_id,guide_type,title,software_name,download_link,installation_steps,installation_check,development_stage,explanation,created_by) VALUES(?,?,?,?,?,?,?,?,?,?,?)')->execute([(int)$hod['department_id'],(int)$hod['campus_id'],$type,$title,$data['software_name']??null,$data['download_link']??null,$data['installation_steps']??null,$data['installation_check']??null,$stage?:null,$explanation,$id]);
  } elseif($action==='save_grade' && in_array($role,['hod','staff'],true)){
    $assignment=(int)($data['assignment_id']??0);$grade=(float)($data['grade']??-1);$comment=trim((string)($data['comments']??''));if($assignment<1||$grade<0)reply(422,['status'=>'error','message'=>'Choose an assignment and enter a valid grade.']);
    $q=$db->prepare('SELECT a.id FROM project_assignments a JOIN practical_projects p ON p.id=a.project_id WHERE a.student_id IS NOT NULL AND a.id=? AND p.department_id=? AND p.campus_id=?');$q->execute([$assignment,(int)$hod['department_id'],(int)$hod['campus_id']]);if(!$q->fetch())reply(404,['status'=>'error','message'=>'Assignment is outside your department and campus.']);
    $db->prepare('INSERT INTO grades_feedback(assignment_id,grade,comments,graded_by) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE grade=VALUES(grade),comments=VALUES(comments),graded_by=VALUES(graded_by),graded_at=UTC_TIMESTAMP()')->execute([$assignment,$grade,$comment?:null,$id]);
  } elseif($action==='verify_logbook' && in_array($role,['hod','staff'],true)){
    $entry=(int)($data['entry_id']??0);$comment=trim((string)($data['verification_comment']??''));$q=$db->prepare('UPDATE logbook_entries l JOIN students s ON s.id=l.student_id SET l.verified_by=?,l.verified_at=UTC_TIMESTAMP(),l.verification_comment=? WHERE l.id=? AND s.department_id=? AND s.campus_id=?');$q->execute([$id,$comment?:null,$entry,(int)$hod['department_id'],(int)$hod['campus_id']]);if(!$q->rowCount())reply(404,['status'=>'error','message'=>'Logbook entry not found in your assigned department and campus.']);
  } elseif($action==='announce' && in_array($role,['admin','staff','hod'],true)){
    $title=trim((string)($data['title']??''));$message=trim((string)($data['message']??''));if($title===''||$message==='')reply(422,['status'=>'error','message'=>'Enter a title and message.']);
    $dept=$role==='hod'?(int)$hod['department_id']:null;$camp=$role==='hod'?(int)$hod['campus_id']:null;$db->prepare('INSERT INTO notifications(department_id,campus_id,kind,title,message,created_by) VALUES(?,?,?,?,?,?)')->execute([$dept,$camp,'announcement',$title,$message,$id]);
  } elseif($action==='send_direct_message' && in_array($role,['admin','staff','hod','student'],true)){
    $recipient=(int)($data['recipient_user_id']??0);$text=trim((string)($data['message_text']??''));if($recipient<1||$recipient===$id||$text==='')reply(422,['status'=>'error','message'=>'Choose a recipient and enter a message.']);
    if($role==='hod'){$check=$db->prepare('SELECT 1 FROM users u JOIN students s ON s.user_id=u.id WHERE u.id=? AND s.department_id=? AND s.campus_id=?');$check->execute([$recipient,(int)$hod['department_id'],(int)$hod['campus_id']]);if(!$check->fetch())reply(404,['status'=>'error','message'=>'Student is outside your HOD scope.']);}
    elseif($role==='student'){$check=$db->prepare("SELECT 1 FROM users u WHERE u.id=? AND (u.role IN ('staff','admin') OR (u.role='hod' AND EXISTS(SELECT 1 FROM students s JOIN hod_assignments h ON h.department_id=s.department_id AND h.campus_id=s.campus_id WHERE s.user_id=? AND h.user_id=u.id)))");$check->execute([$recipient,$id]);if(!$check->fetch())reply(404,['message'=>'Choose your HOD or a management account.']);}
    else{$check=$db->prepare('SELECT id FROM users WHERE id=? AND is_active=1');$check->execute([$recipient]);if(!$check->fetch())reply(404,['status'=>'error','message'=>'Recipient account was not found.']);}
    $check=$db->prepare('SELECT c.id FROM conversations c JOIN conversation_members a ON a.conversation_id=c.id AND a.user_id=? JOIN conversation_members b ON b.conversation_id=c.id AND b.user_id=? WHERE c.conversation_type=\'direct\' LIMIT 1');$check->execute([$id,$recipient]);$conv=$check->fetch();if($conv)$conversation=(int)$conv['id'];else{$db->prepare("INSERT INTO conversations(conversation_type) VALUES('direct')")->execute();$conversation=$db->lastInsertId();$db->prepare('INSERT INTO conversation_members(conversation_id,user_id) VALUES(?,?),(?,?)')->execute([$conversation,$id,$conversation,$recipient]);}$db->prepare('INSERT INTO messages(conversation_id,sender_user_id,message_text) VALUES(?,?,?)')->execute([$conversation,$id,substr($text,0,5000)]);
  } elseif($action==='create_request' && in_array($role,['student','hod','staff'],true)){
    $type=trim((string)($data['request_type']??''));$details=trim((string)($data['request_details']??''));if($type===''||$details==='')reply(422,['status'=>'error','message'=>'Complete request type and details.']);$dept=$role==='hod'?(int)$hod['department_id']:($student?(int)$student['department_id']:null);$camp=$role==='hod'?(int)$hod['campus_id']:($student?(int)$student['campus_id']:null);$db->prepare('INSERT INTO system_requests(requested_by,department_id,campus_id,request_type,request_details) VALUES(?,?,?,?,?)')->execute([$id,$dept,$camp,$type,$details]);
  } elseif($action==='review_request' && in_array($role,['admin','staff'],true)){
    $requestId=(int)($data['request_id']??0);$decision=(string)($data['request_status']??'');$comment=trim((string)($data['review_comment']??''));if($requestId<1||!in_array($decision,['approved','rejected'],true))reply(422,['status'=>'error','message'=>'Choose a request and approval decision.']);$updated=$db->prepare('UPDATE system_requests SET request_status=?,reviewed_by=?,reviewed_at=UTC_TIMESTAMP(),review_comment=? WHERE id=? AND request_status=\'pending\'');$updated->execute([$decision,$id,$comment?:null,$requestId]);if(!$updated->rowCount())reply(404,['status'=>'error','message'=>'Pending request not found.']);
  } elseif($action==='change_password'){
    $current=(string)($data['current_password']??'');$next=(string)($data['new_password']??'');if(strlen($next)<12||strlen($next)>128)reply(422,['status'=>'error','message'=>'New password must be 12 to 128 characters.']);$q=$db->prepare('SELECT password_hash FROM users WHERE id=?');$q->execute([$id]);$hash=$q->fetchColumn();if(!is_string($hash)||!password_verify($current,$hash))reply(403,['status'=>'error','message'=>'Current password is incorrect.']);$newHash=password_hash($next,PASSWORD_DEFAULT);$db->prepare('UPDATE users SET password_hash=? WHERE id=?')->execute([$newHash,$id]);start_auth_session();session_regenerate_id(true);$_SESSION['password_fingerprint']=hash('sha256',$newHash);
  } elseif($action==='mark_attendance' && in_array($role,['hod','staff'],true)){
    $date=(string)($data['session_date']??date('Y-m-d'));$studentId=(int)($data['student_id']??0);$status=(string)($data['attendance_status']??'present');if($studentId<1||!in_array($status,['present','absent','deferred'],true))reply(422,['status'=>'error','message'=>'Select a student and attendance status.']);$sessionQuery=$db->prepare('SELECT id FROM practical_attendance_sessions WHERE department_id=? AND campus_id=? AND session_date=? ORDER BY id DESC LIMIT 1');$sessionQuery->execute([(int)$hod['department_id'],(int)$hod['campus_id'],$date]);$sessionRow=$sessionQuery->fetch();if($sessionRow)$session=(int)$sessionRow['id'];else{$db->prepare('INSERT INTO practical_attendance_sessions(department_id,campus_id,session_date,created_by) VALUES(?,?,?,?)')->execute([(int)$hod['department_id'],(int)$hod['campus_id'],$date,$id]);$session=$db->lastInsertId();}$q=$db->prepare('INSERT INTO practical_attendance(session_id,student_id,attendance_status,marked_by,mark_method) SELECT ?,id,?,?,\'manual\' FROM students WHERE id=? AND department_id=? AND campus_id=? ON DUPLICATE KEY UPDATE attendance_status=VALUES(attendance_status),marked_by=VALUES(marked_by),marked_at=UTC_TIMESTAMP()');$q->execute([$session,$status,$id,$studentId,(int)$hod['department_id'],(int)$hod['campus_id']]);if(!$q->rowCount())reply(404,['status'=>'error','message'=>'Student is outside your department and campus.']);
  } else reply(403,['status'=>'error','message'=>'This action is unavailable for your role.']);
  audit_event($id,$role,'module',null,$action,'Completed '.$action);reply(200,['status'=>'success','message'=>'Saved successfully.']);
}
$specs=[
'system-overview'=>['System Overview','Overview of SLPS data across campuses.', ['admin','staff']], 'manage-all-students'=>['Manage All Students','Search and filter registered students.', ['admin','staff']], 'department-management'=>['Department Management','Manage departments.',['admin','staff']], 'past-papers-oversight'=>['Past Papers Oversight','Past papers available in SLPS.',['admin','staff']], 'practical-guides-oversight'=>['Practical Guides Oversight','Guides published for practical learning.',['admin','staff']], 'all-submissions-monitor'=>['All Submissions Monitor','Monitor submitted project files.',['admin','staff']], 'analytics-across-campuses'=>['Analytics Across Campuses','Review assignment and grade outcomes.',['admin','staff']], 'file-management'=>['File Management','File records stored in SLPS.',['admin','staff']], 'request-approval-system'=>['Request & Approval System','Review requests from students and staff.',['admin','staff']], 'user-accounts-security-logs'=>['User Accounts & Security Logs','Accounts and recent security activity.',['admin','staff']], 'messaging'=>['Messaging','Notifications and announcements.',['admin','staff']], 'system-settings-backup'=>['System Settings & Backup','System settings and backup records.',['admin','staff']],
'hod-overview'=>['HOD Dashboard Overview','Department and campus practical learning.',['hod']], 'hod-dashboard-overview'=>['HOD Dashboard Overview','Department and campus practical learning.',['hod']], 'manage-students'=>['Manage Students','Students in your assigned department and campus.',['hod']], 'individual-project-assignment'=>['Individual Project Assignment','Assign practical work to a student.',['hod']], 'zipped-project-submissions'=>['Zipped Project Submissions','View project submission records.',['hod']], 'practical-development-guide-management'=>['Practical Development Guide Management','Create software and development guides.',['hod']], 'past-papers-upload'=>['Past Papers Upload','Browse past papers for your department.',['hod']], 'grade-feedback-center'=>['Grade & Feedback Center','Grade assignments and provide comments.',['hod']], 'student-logbook-verification'=>['Student Logbook Verification','Review student practical logbooks.',['hod']], 'practical-attendance'=>['Practical Attendance','Record practical attendance.',['hod']], 'notifications-center'=>['Notifications Center','Send department announcements.',['hod']], 'analytics-performance'=>['Analytics & Performance','Review practical performance.',['hod']], 'hod-file-manager'=>['File Manager','Department file records.',['hod']], 'file-manager'=>['File Manager','Files for your role and department scope.',['hod','student']], 'hod-messaging'=>['Messaging Module','Direct messages with students.',['hod']], 'messaging-module'=>['Messaging Module','Direct messages with students.',['hod']], 'hod-profile-security'=>['Profile & Security','Account and login history.',['hod']], 'profile-security'=>['Profile & Security','Account and login history.',['hod']], 'hod-ai-assistant'=>['AI Assistant','Guidance from your SLPS assistant.',['hod']], 'ai-assistant'=>['AI Assistant','Guidance from your SLPS assistant.',['hod']],
'student-dashboard'=>['My Dashboard','Your assignments, grades and deadlines.',['student']], 'my-dashboard'=>['My Dashboard','Your assignments, grades and deadlines.',['student']], 'my-assignments'=>['My Assignments','Practical projects assigned to you.',['student']], 'submit-project'=>['Submit Project','Upload a ZIP project submission.',['student']], 'practical-guides'=>['Practical Guides','Guides for your department.',['student']], 'past-papers-library'=>['Past Papers Library','Past papers for your department.',['student']], 'software-installation-lab'=>['Software Installation Lab','Software installation instructions.',['student']], 'e-logbook'=>['E-Logbook','Record daily practical activity.',['student']], 'my-grades-feedback'=>['My Grades & Feedback','Your assignment marks and comments.',['student']], 'attendance-history'=>['Attendance History','Your practical attendance records.',['student']], 'student-notifications'=>['Notifications','Assignments, grades and announcements.',['student']], 'notifications'=>['Notifications','Assignments, grades and announcements.',['student']], 'student-file-manager'=>['File Manager','Your submitted file records.',['student']], 'internal-messaging'=>['Internal Messaging','Direct messages with your HOD and university staff.',['student']], 'profile-management'=>['Profile Management','Your registration details.',['student']], 'ai-learning-assistant'=>['AI Learning Assistant','Practical learning help.',['student']], 'calendar-deadlines'=>['Calendar & Deadlines','Upcoming practical project deadlines.',['student']]
];
if(!isset($specs[$module]))reply(404,['status'=>'error','message'=>'Module not found.']);[$title,$description,$roles]=$specs[$module];if(!in_array($role,$roles,true)&&!($role==='staff'&&!in_array($module,['my-dashboard','student-dashboard','my-assignments','submit-project','e-logbook','my-grades-feedback','attendance-history','calendar-deadlines','profile-management'],true)))reply(403,['status'=>'error','message'=>'This module is not available for your role.']);
$params=[];$where='';if($role==='hod'){$where=' WHERE s.department_id=? AND s.campus_id=?';$params=[(int)$hod['department_id'],(int)$hod['campus_id']];}
$queries=[
'departments'=>["SELECT id,department_name,department_code,CASE WHEN is_active=1 THEN 'Active' ELSE 'Inactive' END AS status,created_at FROM departments ORDER BY department_name",[]],
'students'=>['SELECT s.id student_id,s.student_number,u.full_name,u.email,s.identity_number,s.year_of_study,s.status,d.department_name,c.campus_name FROM students s JOIN users u ON u.id=s.user_id JOIN departments d ON d.id=s.department_id JOIN campuses c ON c.id=s.campus_id'.($role==='hod'?' WHERE s.department_id=? AND s.campus_id=?':'').' ORDER BY u.full_name LIMIT 300',$params],
'papers'=>['SELECT p.course_code,p.exam_year,p.semester,d.department_name,c.course_name,p.paper_file_id,p.solution_file_id FROM past_papers p JOIN departments d ON d.id=p.department_id JOIN courses c ON c.id=p.course_id'.($role==='student'?' WHERE p.department_id=?':($role==='hod'?' WHERE p.department_id=?':'')).' ORDER BY p.exam_year DESC LIMIT 300',$role==='student'?[(int)$student['department_id']]:($role==='hod'?[(int)$hod['department_id']]:[])],
'guides'=>['SELECT g.title,g.guide_type,g.software_name,g.development_stage,g.explanation,d.department_name FROM practical_guides g JOIN departments d ON d.id=g.department_id'.(in_array($role,['student','hod'],true)?' WHERE g.department_id=?'.($role==='hod'?' AND g.campus_id=?':''): '').' ORDER BY g.created_at DESC LIMIT 300', $role==='student'?[(int)$student['department_id']]:($role==='hod'?[(int)$hod['department_id'],(int)$hod['campus_id']]:[])],
'submissions'=>['SELECT u.full_name,p.title,a.due_at,ps.submitted_at,ps.validation_state,fa.id zip_file_id,fa.file_name FROM project_submissions ps JOIN students s ON s.id=ps.submitted_by JOIN users u ON u.id=s.user_id JOIN project_assignments a ON a.id=ps.assignment_id AND a.student_id IS NOT NULL JOIN practical_projects p ON p.id=a.project_id JOIN file_assets fa ON fa.id=ps.zip_file_id'.($role==='hod'?' WHERE p.department_id=? AND p.campus_id=?':'').' ORDER BY ps.submitted_at DESC LIMIT 300',$params],
'requests'=>['SELECT r.id,u.full_name,r.request_type,r.request_details,r.request_status,r.created_at FROM system_requests r JOIN users u ON u.id=r.requested_by ORDER BY r.created_at DESC LIMIT 300',[]],
'notifications'=>['SELECT title,message,kind,created_at FROM notifications'.($role==='student'?' WHERE student_id=? OR recipient_user_id=? OR (department_id=? AND (campus_id IS NULL OR campus_id=?))':($role==='hod'?' WHERE department_id=? AND (campus_id IS NULL OR campus_id=?)':'' )).' ORDER BY created_at DESC LIMIT 300',$role==='student'?[(int)$student['id'],$id,(int)$student['department_id'],(int)$student['campus_id']]:($role==='hod'?[(int)$hod['department_id'],(int)$hod['campus_id']]:[])],
'logbook'=>['SELECT l.id entry_id,u.full_name,l.activity_date,l.practical_activity,l.verified_at,l.verification_comment FROM logbook_entries l JOIN students s ON s.id=l.student_id JOIN users u ON u.id=s.user_id'.($role==='student'?' WHERE s.id=?':($role==='hod'?' WHERE s.department_id=? AND s.campus_id=?':'')).' ORDER BY l.activity_date DESC LIMIT 300',$role==='student'?[(int)$student['id']]:$params],
'assignments'=>['SELECT a.id assignment_id,p.title,p.description,a.due_at,g.grade,g.comments FROM project_assignments a JOIN practical_projects p ON p.id=a.project_id LEFT JOIN grades_feedback g ON g.assignment_id=a.id'.($role==='student'?' WHERE a.student_id=?':($role==='hod'?' WHERE a.student_id IS NOT NULL AND p.department_id=? AND p.campus_id=?':' WHERE a.student_id IS NOT NULL')).' ORDER BY a.due_at LIMIT 300',$role==='student'?[(int)$student['id']]:$params],
'attendance'=>['SELECT a.attendance_status,a.mark_method,a.marked_at,s.session_date FROM practical_attendance a JOIN practical_attendance_sessions s ON s.id=a.session_id'.($role==='student'?' WHERE a.student_id=?':($role==='hod'?' WHERE s.department_id=? AND s.campus_id=?':'')).' ORDER BY s.session_date DESC LIMIT 300',$role==='student'?[(int)$student['id']]:$params],
'chat'=>['SELECT m.sent_at,m.message_text,u.full_name sender,c.id conversation_id FROM messages m JOIN conversations c ON c.id=m.conversation_id JOIN conversation_members cm ON cm.conversation_id=c.id JOIN users u ON u.id=m.sender_user_id WHERE cm.user_id=? ORDER BY m.sent_at DESC LIMIT 300',[$id]],
'audit'=>['SELECT actor_role,entity,action,summary,created_at FROM system_audit_log ORDER BY created_at DESC LIMIT 300',[]],
'accounts'=>['SELECT username,full_name,email,role,is_active,last_login FROM users ORDER BY created_at DESC LIMIT 300',[]]
];
$queries['profile']=$role==='hod'?['SELECT u.full_name,u.email,u.username,d.department_name,c.campus_name FROM users u JOIN hod_assignments h ON h.user_id=u.id JOIN departments d ON d.id=h.department_id JOIN campuses c ON c.id=h.campus_id WHERE u.id=?',[$id]]:['SELECT u.full_name,u.email,u.username,s.identity_number,s.student_number,s.year_of_study,d.department_name,c.campus_name FROM users u JOIN students s ON s.user_id=u.id JOIN departments d ON d.id=s.department_id JOIN campuses c ON c.id=s.campus_id WHERE u.id=?',[$id]];
$queries['myfiles']=['SELECT f.file_name,f.media_type,f.file_size_bytes,f.created_at,p.title FROM file_assets f JOIN project_submissions ps ON ps.zip_file_id=f.id JOIN project_assignments a ON a.id=ps.assignment_id AND a.student_id IS NOT NULL JOIN practical_projects p ON p.id=a.project_id JOIN students s ON s.id=ps.submitted_by WHERE s.id=? ORDER BY f.created_at DESC LIMIT 300',[(int)($student['id']??0)]];
$queries['zipfiles']=['SELECT e.id extracted_file_id,e.relative_path,e.media_type,e.file_size_bytes,u.full_name,p.title,ps.submitted_at FROM extracted_submission_files e JOIN project_submissions ps ON ps.id=e.submission_id JOIN students s ON s.id=ps.submitted_by JOIN users u ON u.id=s.user_id JOIN project_assignments a ON a.id=ps.assignment_id AND a.student_id IS NOT NULL JOIN practical_projects p ON p.id=a.project_id'.($role==='hod'?' WHERE p.department_id=? AND p.campus_id=?':($role==='student'?' WHERE s.id=?':'')).' ORDER BY ps.submitted_at DESC,e.relative_path LIMIT 1000',$role==='student'?[(int)$student['id']]:($role==='hod'?$params:[])];
// Keep legacy messaging/notification views aligned with the same recipient rules.
require_once __DIR__.'/../lib/notices.php';
[$noticeWhere,$noticeArgs]=notice_visibility($user,notice_user_scope($user));
$queries['notifications']=['SELECT n.title,n.message,n.kind,n.created_at FROM notifications n LEFT JOIN notice_publications p ON p.notification_id=n.id WHERE '.$noticeWhere.' ORDER BY n.id DESC LIMIT 300',$noticeArgs];
$queries['files']=['SELECT f.id download_file_id,f.file_name,f.media_type,f.file_size_bytes,f.file_category,f.created_at,d.department_name,c.campus_name,COALESCE(NULLIF(u.full_name,\'\'),u.username) uploaded_by FROM file_assets f LEFT JOIN departments d ON d.id=f.department_id LEFT JOIN campuses c ON c.id=f.campus_id LEFT JOIN users u ON u.id=f.uploaded_by'.($role==='hod'?' WHERE (f.department_id=? AND (f.campus_id IS NULL OR f.campus_id=?))':($role==='student'?' WHERE ((f.file_category=\'department_file\' AND f.department_id=? AND (f.campus_id IS NULL OR f.campus_id=?)) OR (f.file_category IN (\'past_paper\',\'solution\') AND f.department_id=?) OR f.uploaded_by=?)':'')).' ORDER BY f.created_at DESC LIMIT 300',$role==='hod'?$params:($role==='student'?[(int)$student['department_id'],(int)$student['campus_id'],(int)$student['department_id'],$id]:[])];
$queries['analytics']=['SELECT p.title,COUNT(DISTINCT a.id) assignments,COUNT(DISTINCT g.id) graded,SUM(CASE WHEN g.grade>=50 THEN 1 ELSE 0 END) passed,SUM(CASE WHEN g.grade<50 THEN 1 ELSE 0 END) failed FROM practical_projects p JOIN project_assignments a ON a.project_id=p.id AND a.student_id IS NOT NULL LEFT JOIN grades_feedback g ON g.assignment_id=a.id'.($role==='hod'?' WHERE p.department_id=? AND p.campus_id=?':'').' GROUP BY p.id ORDER BY p.created_at DESC LIMIT 300',$params];
$queries['system']=['SELECT (SELECT COUNT(*) FROM users) users,(SELECT COUNT(*) FROM students) students,(SELECT COUNT(*) FROM campuses WHERE is_active=1) campuses,(SELECT COUNT(*) FROM departments WHERE is_active=1) departments,(SELECT COUNT(*) FROM project_submissions) submissions',[]];
$queries['settings']=['SELECT setting_key,setting_value,updated_at FROM system_settings ORDER BY setting_key',[]];
$queries['backups']=['SELECT backup_storage_key,backup_status,created_at FROM system_backups ORDER BY created_at DESC LIMIT 100',[]];
$key=match(true){$module==='user-accounts-security-logs'=>'accounts',$module==='department-management'=>'departments',$module==='system-overview'=>'system',str_contains($module,'analytics')=>'analytics',str_contains($module,'settings')=>'settings',str_contains($module,'backup')=>'backups',in_array($module,['manage-all-students','manage-students'],true)=>'students',str_contains($module,'paper')=>'papers',str_contains($module,'guide')=>'guides',$module==='zipped-project-submissions'=>'zipfiles',str_contains($module,'submission')=>'submissions',str_contains($module,'request')=>'requests',in_array($module,['internal-messaging','hod-messaging','messaging-module'],true)=>'chat',str_contains($module,'messag')||str_contains($module,'notification')=>'notifications',str_contains($module,'logbook')=>'logbook',str_contains($module,'grade')||str_contains($module,'assignment')||str_contains($module,'deadline')||str_contains($module,'calendar')||str_contains($module,'dashboard')=>'assignments',str_contains($module,'attendance')=>'attendance',in_array($module,['profile-security','hod-profile-security','profile-management'],true)=>'profile',str_contains($module,'security')=>'audit',str_contains($module,'account')=>'accounts',str_contains($module,'profile')=>'profile',str_contains($module,'file-management')||str_contains($module,'file-manager')=>'files',default=>null};
$rows=[];if($key&&isset($queries[$key])){$q=$db->prepare($queries[$key][0]);$q->execute($queries[$key][1]);$rows=$q->fetchAll();}
$options=[];
if($role==='staff'&&$module==='user-accounts-security-logs'){
 $options['campuses']=$db->query('SELECT id,campus_name FROM campuses WHERE is_active=1 ORDER BY campus_name')->fetchAll();
 $options['departments']=$db->query('SELECT id,department_name FROM departments WHERE is_active=1 ORDER BY department_name')->fetchAll();
}
if($role==='hod'&&in_array($module,['individual-project-assignment','practical-attendance'],true)){
  $sql='SELECT s.id,s.student_number,u.full_name,s.year_of_study FROM students s JOIN users u ON u.id=s.user_id WHERE s.department_id=? AND s.campus_id=? AND s.status=\'active\' ORDER BY u.full_name LIMIT 500';
  $q=$db->prepare($sql);$q->execute([(int)$hod['department_id'],(int)$hod['campus_id']]);$options['students']=$q->fetchAll();
}
if($role==='hod'&&$module==='grade-feedback-center'){
  $q=$db->prepare('SELECT a.id,p.title,COALESCE(NULLIF(u.full_name,\'\'),s.student_number) assignee,a.due_at FROM project_assignments a JOIN practical_projects p ON p.id=a.project_id LEFT JOIN students s ON s.id=a.student_id LEFT JOIN users u ON u.id=s.user_id WHERE a.student_id IS NOT NULL AND p.department_id=? AND p.campus_id=? ORDER BY p.title,a.due_at LIMIT 500');$q->execute([(int)$hod['department_id'],(int)$hod['campus_id']]);$options['assignments']=$q->fetchAll();
}
if($role==='hod'&&$module==='student-logbook-verification'){
  $q=$db->prepare('SELECT l.id,u.full_name student_name,l.activity_date,LEFT(l.practical_activity,90) activity_preview FROM logbook_entries l JOIN students s ON s.id=l.student_id JOIN users u ON u.id=s.user_id WHERE s.department_id=? AND s.campus_id=? AND l.verified_at IS NULL ORDER BY l.activity_date DESC,u.full_name LIMIT 500');$q->execute([(int)$hod['department_id'],(int)$hod['campus_id']]);$options['logbooks']=$q->fetchAll();
}
if(in_array($role,['admin','staff'],true)&&$module==='request-approval-system'){
  $q=$db->query("SELECT r.id,r.request_type,LEFT(r.request_details,100) request_summary,COALESCE(NULLIF(u.full_name,''),u.username) requester_name,r.created_at FROM system_requests r JOIN users u ON u.id=r.requested_by WHERE r.request_status='pending' ORDER BY r.created_at LIMIT 500");$options['requests']=$q->fetchAll();
}
if($role==='student'&&$module==='submit-project'){
  $q=$db->prepare('SELECT DISTINCT a.id,p.title,a.due_at FROM project_assignments a JOIN practical_projects p ON p.id=a.project_id WHERE a.student_id=? AND p.department_id=? AND p.campus_id=? ORDER BY a.due_at LIMIT 300');$q->execute([(int)$student['id'],(int)$student['department_id'],(int)$student['campus_id']]);$options['assignments']=$q->fetchAll();
}
if(in_array($role,['admin','staff'],true)&&in_array($module,['file-management','file-manager'],true)){
  $q=$db->query('SELECT id,department_name FROM departments WHERE is_active=1 ORDER BY department_name');
  $options['departments']=$q->fetchAll();
}
if($role==='staff'){
 $options['campuses']=$db->query('SELECT id,campus_name FROM campuses WHERE is_active=1 ORDER BY campus_name')->fetchAll();
 $options['departments']=$db->query('SELECT id,department_name FROM departments WHERE is_active=1 ORDER BY department_name')->fetchAll();
 if(in_array($module,['individual-project-assignment','practical-attendance'],true)){
  $sql="SELECT s.id,s.student_number,u.full_name,s.year_of_study FROM students s JOIN users u ON u.id=s.user_id WHERE s.status='active'";
  $options['students']=$db->query($sql.' ORDER BY u.full_name LIMIT 500')->fetchAll();
 }
 if($module==='grade-feedback-center')$options['assignments']=$db->query("SELECT a.id,p.title,COALESCE(u.full_name,s.student_number,'Student') assignee,a.due_at FROM project_assignments a JOIN practical_projects p ON p.id=a.project_id LEFT JOIN students s ON s.id=a.student_id LEFT JOIN users u ON u.id=s.user_id WHERE a.student_id IS NOT NULL ORDER BY p.title LIMIT 500")->fetchAll();
 if($module==='student-logbook-verification')$options['logbooks']=$db->query('SELECT l.id,u.full_name student_name,l.activity_date,LEFT(l.practical_activity,90) activity_preview FROM logbook_entries l JOIN students s ON s.id=l.student_id JOIN users u ON u.id=s.user_id WHERE l.verified_at IS NULL ORDER BY l.activity_date DESC LIMIT 500')->fetchAll();
}
reply(200,['status'=>'success','module'=>$module,'title'=>$title,'description'=>$description,'rows'=>$rows,'options'=>$options,'role'=>$role]);
