<?php session_start();require_once'../config/database.php';
function h($s){return htmlspecialchars((string)$s,ENT_QUOTES,'UTF-8');}
if(!isset($_SESSION['user_id'])||!in_array($_SESSION['user_role']??'',['admin','teacher'],true)){header('Location: ../login.php');exit();}
$sn=$_GET['session']??'';$ci=$_GET['class_id']??'';$gn=$_GET['group_name']??'';$si=$_GET['subject_id']??'';$et=$_GET['exam_type']??'';$ac=$_GET['action']??'view';
try{$ses=$pdo->query("SELECT session_name FROM academic_sessions WHERE status='active' ORDER BY session_name DESC")->fetchAll();$cls=$pdo->query("SELECT id,name,section FROM classes WHERE status='active' ORDER BY name,section")->fetchAll();$grps=$pdo->query("SELECT group_name,group_code FROM student_groups WHERE status='active' ORDER BY group_name")->fetchAll();}catch(Exception$e){$em=$e->getMessage();$ses=$cls=$grps=[];}
try{$ets=$pdo->query("SELECT DISTINCT exam_type FROM results ORDER BY exam_type")->fetchAll(PDO::FETCH_COLUMN);}catch(Exception$e){$ets=[];}
$csb=[];if(!empty($ci)){$s=$pdo->prepare("SELECT s.id,s.name,s.code FROM class_subjects cs JOIN subjects s ON cs.subject_id=s.id WHERE cs.class_id=? AND s.status='active' ORDER BY s.name");$s->execute([$ci]);$csb=$s->fetchAll();}else{$csb=$pdo->query("SELECT id,name,code FROM subjects WHERE status='active' ORDER BY name")->fetchAll();}
$ws=["s.status='active'"];$ps=[];if($sn!==''){$ws[]='s.session=?';$ps[]=$sn;}if($ci!==''){$ws[]='s.class_id=?';$ps[]=$ci;}if($gn!==''){if(in_array($gn,['General','সাধারণ'],true)){$ws[]="COALESCE(s.group_name,'')IN(?,?,?)";$ps[]='General';$ps[]='সাধারণ';$ps[]='';}else{$ws[]="COALESCE(s.group_name,'')=?";$ps[]=$gn;}}
$wsi=implode(' AND ',$ws);$st=[];
if($ac==='download'&&$si!==''&&$ci!==''&&$sn!==''){header('Content-Type: text/csv; charset=UTF-8');header('Content-Disposition: attachment; filename="filtered_'.date('Ymd_His').'.csv"');$o=fopen('php://output','w');fwrite($o,"\xEF\xBB\xBF");fputcsv($o,['Roll','Student ID','Name','Session','Class','Section','Group','Subject','Code']);
$sql="SELECT s.roll_number,s.student_id,CONCAT(s.first_name,' ',s.last_name)AS name,s.session,c.name AS class_name,c.section,COALESCE(s.group_name,'')AS group_name,sub.name AS subject_name,sub.code AS subject_code FROM students s JOIN classes c ON s.class_id=c.id JOIN student_subjects ss ON ss.student_id=s.id AND ss.subject_id=? JOIN subjects sub ON sub.id=ss.subject_id WHERE $wsi ORDER BY s.roll_number";
$s=$pdo->prepare($sql);$s->execute(array_merge([$si],$ps));while($r=$s->fetch())fputcsv($o,[$r['roll_number'],$r['student_id'],$r['name'],$r['session'],$r['class_name'],$r['section'],$r['group_name'],$r['subject_name'],$r['subject_code']]);fclose($o);exit();}
if($ac==='view'&&$si!==''&&$ci!==''&&$sn!==''){$sql="SELECT s.id,s.roll_number,s.student_id,s.first_name,s.last_name,s.session,c.name AS class_name,c.section,COALESCE(s.group_name,'')AS group_name,sub.name AS subject_name,sub.code AS subject_code FROM students s JOIN classes c ON s.class_id=c.id JOIN student_subjects ss ON ss.student_id=s.id AND ss.subject_id=? JOIN subjects sub ON sub.id=ss.subject_id WHERE $wsi ORDER BY s.roll_number";
$s=$pdo->prepare($sql);$s->execute(array_merge([$si],$ps));$st=$s->fetchAll();}
?><!DOCTYPE html><html lang="bn"><head><meta charset="UTF-8"><meta name="viewport" content="width=device-width,initial-scale=1.0"><title>শিক্ষার্থী ফিল্টার</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet"><link href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.0.0/css/all.min.css" rel="stylesheet"><link href="https://fonts.googleapis.com/css2?family=Hind+Siliguri:wght@400;600;700&display=swap" rel="stylesheet">
<style>:root{--p:#6C63FF;--s:#A855F7}body{font-family:'Hind Siliguri',sans-serif;background:linear-gradient(135deg,#0F0C29,#302B63,#24243E);min-height:100vh;font-size:.85rem;color:#fff}
.navbar{background:linear-gradient(135deg,#1a1a3e,#2d1b69)!important;padding:5px 0!important;border-bottom:1px solid rgba(168,85,247,.2)}
.fc{background:rgba(255,255,255,.06);backdrop-filter:blur(16px);border-radius:14px;box-shadow:0 8px 32px rgba(0,0,0,.25);border:1px solid rgba(255,255,255,.08);overflow:hidden;margin-bottom:14px}
.fc .ch{background:linear-gradient(135deg,rgba(108,99,255,.35),rgba(168,85,247,.25));color:#fff;border:none;padding:7px 12px;font-weight:700;font-size:.88rem;backdrop-filter:blur(4px)}
.fc .cb{padding:10px 12px}.fl{font-weight:600;color:#fff;margin-bottom:2px;font-size:.78rem}
.fc-s{background:#1e1b4b;border:1px solid rgba(255,255,255,.12);border-radius:8px;padding:5px 9px;font-size:.82rem;transition:.3s;width:100%;color:#fff}
.fc-s:focus{border-color:var(--p);box-shadow:0 0 0 3px rgba(108,99,255,.2);outline:none}.fc-s option{background:#1e1b4b;color:#fff}
.btn-p{background:linear-gradient(135deg,var(--p),var(--s));border:none;border-radius:8px;padding:5px 16px;font-weight:700;font-size:.82rem;box-shadow:0 4px 14px rgba(108,99,255,.3);color:#fff}.btn-p:hover{transform:translateY(-1px);box-shadow:0 6px 20px rgba(108,99,255,.4);color:#fff}
.btn-g{background:rgba(52,211,153,.15);border:1px solid rgba(52,211,153,.3);border-radius:8px;padding:5px 16px;font-weight:600;font-size:.82rem;color:#fff}.btn-g:hover{background:rgba(52,211,153,.25);color:#fff}
.btn-o{background:transparent;border:1px solid rgba(255,255,255,.15);border-radius:8px;padding:5px 16px;font-weight:600;font-size:.82rem;color:#fff}.btn-o:hover{background:rgba(255,255,255,.08);color:#fff}
.form-check-input{background:rgba(255,255,255,.1);border:1px solid rgba(255,255,255,.2)}.form-check-input:checked{background:var(--p);border-color:var(--p)}
.text-muted{color:rgba(255,255,255,.55)!important}.alert{backdrop-filter:blur(8px);border:none;border-radius:8px;color:#fff}.alert-danger{background:rgba(239,68,68,.12)}.alert-info{background:rgba(96,165,250,.12)}
.table{color:#fff!important;margin-bottom:0}.table th{background:linear-gradient(135deg,rgba(108,99,255,.4),rgba(168,85,247,.3));color:#fff!important;font-weight:600;font-size:.75rem;padding:6px 8px!important;border:none}
.table td{padding:5px 8px!important;vertical-align:middle;font-size:.8rem;border-color:rgba(255,255,255,.06);color:#fff!important}.table-hover tbody tr:hover{background:rgba(255,255,255,.04)}.table-striped>tbody>tr:nth-of-type(odd){background:rgba(255,255,255,.04)}.table-striped>tbody>tr:nth-of-type(even){background:rgba(255,255,255,.01)}
.tf{max-height:520px;overflow-y:auto}.badge{font-weight:500;font-size:.7rem;padding:2px 8px;background:var(--p);color:#fff}
body,.card,.card-body,.card-header,.btn,.form-label,.form-select,.form-control,.form-check,.nav-link,.list-group-item,.table,.table td,.table th,.table tr,td,th,tr,.btn i,a,a:hover,i,span,small,p,h1,h2,h3,h4,h5,h6,div,label,select,option,input,textarea,strong,b,em{color:#fff!important}
@media print{@page{size:A4;margin:6mm}.no-print{display:none!important}body{background:none!important}.container-fluid,.fc,.cb,.table,.table td,.table th,.tf,div,span,small,p,label,strong,b,em,a{color:#000!important}.fc{background:#fff!important;border:none!important;box-shadow:none!important}.table th{background:#e0e0e0!important}}
</style></head><body>
<nav class="navbar navbar-expand navbar-dark"><div class="container-fluid px-3"><a class="navbar-brand fw-bold" style="font-size:.9rem" href="../index.php"><i class="fas fa-graduation-cap"></i> শিক্ষার্থী ফিল্টার</a>
<div class="navbar-nav ms-auto flex-row gap-1"><a class="nav-link text-white py-0 px-2" href="../index.php"><i class="fas fa-home"></i></a><a class="nav-link text-white py-0 px-2" href="../logout.php"><i class="fas fa-sign-out-alt"></i></a></div></div></nav>
<div class="container py-2">
<?php if(isset($em)&&$em):?><div class="alert alert-danger py-1 small"><?=h($em)?></div><?php endif;?>
<div class="fc"><div class="ch"><i class="fas fa-filter me-1"></i>সেশন, ক্লাস, গ্রুপ ও বিষয় ভিত্তিক ফিল্টার</div><div class="cb">
<form method="GET" class="row g-2 align-items-end">
<div class="col-md-3"><label class="fl">সেশন *</label><select class="form-select fc-s" name="session" required><option value="">নির্বাচন</option><?php foreach($ses as$s):?><option value="<?=h($s['session_name'])?>" <?=$sn===$s['session_name']?'selected':''?>><?=h($s['session_name'])?></option><?php endforeach;?></select></div>
<div class="col-md-3"><label class="fl">ক্লাস *</label><select class="form-select fc-s" name="class_id" required><option value="">নির্বাচন</option><?php foreach($cls as$c):?><option value="<?=$c['id']?>" <?=$ci==$c['id']?'selected':''?>><?=h($c['name'].' - '.$c['section'])?></option><?php endforeach;?></select></div>
<div class="col-md-3"><label class="fl">গ্রুপ</label><select class="form-select fc-s" name="group_name"><option value="">সব</option><?php foreach($grps as$g):?><option value="<?=h($g['group_name'])?>" <?=$gn===$g['group_name']?'selected':''?>><?=h($g['group_name'].' ('.$g['group_code'].')')?></option><?php endforeach;?></select></div>
<div class="col-md-3"><label class="fl">বিষয় *</label><select class="form-select fc-s" name="subject_id" required><option value="">নির্বাচন</option><?php foreach($csb as$sb):?><option value="<?=$sb['id']?>" <?=$si==$sb['id']?'selected':''?>><?=h($sb['name'].' ('.$sb['code'].')')?></option><?php endforeach;?></select></div>
<div class="col-md-3"><label class="fl">পরীক্ষার ধরন</label><select class="form-select fc-s" name="exam_type"><option value="">নির্বাচন</option><?php foreach($ets as$e):?><option value="<?=h($e)?>" <?=$et===$e?'selected':''?>><?=h(ucfirst($e))?></option><?php endforeach;?></select></div>
<div class="col-md-12 d-flex flex-wrap gap-2">
<button type="submit" name="action" value="view" class="btn btn-p"><i class="fas fa-search"></i> দেখুন</button>
<button type="submit" name="action" value="download" class="btn btn-g" <?=!$si||!$ci||!$sn?'disabled':''?>><i class="fas fa-download"></i> ডাউনলোড</button></div>
<div class="col-md-12 d-flex flex-wrap gap-2"><?php $qs=http_build_query(['session'=>$sn,'class_id'=>$ci,'group_name'=>$gn,'subject_id'=>$si]);?>
<a class="btn btn-o" target="_blank" href="../exams/seat_card_grid.php?<?=$qs?>"><i class="fas fa-id-card"></i> সীটকার্ড (গ্রিড)</a>
<a class="btn btn-o" target="_blank" href="../exams/seat_card.php?<?=$qs?>"><i class="fas fa-chair"></i> সীটকার্ড</a>
<a class="btn btn-o" target="_blank" href="subject_enrollment_tools.php?<?=http_build_query(['session'=>$sn,'class_id'=>$ci,'group_name'=>'','subject_id'=>2,'action'=>'seatcards_photo'])?>"><i class="fas fa-id-badge"></i> সীটকার্ড (ছবি)</a>
<a class="btn btn-o" target="_blank" href="../reports/tabulation_sheet.php?<?=http_build_query(['session'=>$sn,'class_id'=>$ci,'group_name'=>$gn,'exam_type'=>$et])?>"><i class="fas fa-table"></i> টেবুলেশন</a>
<?php if(!empty($st)):$f=$st[0];?><a class="btn btn-o" target="_blank" href="../reports/marksheet.php?student_id=<?=(int)$f['id']?>&session=<?=urlencode($sn)?>&exam_type=<?=urlencode($et)?>"><i class="fas fa-certificate"></i> মার্কশীট</a><?php endif;?></div>
</form></div></div>

<?php if($ac==='view'&&!empty($st)):?><div class="fc mt-2"><div class="ch d-flex justify-content-between"><span><i class="fas fa-users"></i> ফিল্টার রেজাল্ট</span><span class="small">শিক্ষার্থী: <?=count($st)?></span></div>
<div class="tf"><table class="table table-striped table-hover"><thead><tr><th>রোল</th><th>আইডি</th><th>নাম</th><th>সেশন</th><th>ক্লাস</th><th>গ্রুপ</th><th>বিষয়</th><th>একশন</th></tr></thead>
<tbody><?php foreach($st as$s):?><tr><td><?=h($s['roll_number'])?></td><td><?=h($s['student_id'])?></td><td><?=h(trim($s['first_name'].' '.$s['last_name']))?></td><td><?=h($s['session'])?></td><td><?=h($s['class_name'].'-'.$s['section'])?></td><td><?=h($s['group_name'])?></td><td><?=h($s['subject_name'].' ('.$s['subject_code'].')')?></td>
<td><a class="btn btn-sm btn-o py-0" href="../students/view.php?id=<?=(int)$s['id']?>"><i class="fas fa-eye"></i></a><a class="btn btn-sm btn-o py-0" href="../students/edit.php?id=<?=(int)$s['id']?>"><i class="fas fa-edit"></i></a></td></tr><?php endforeach;?></tbody></table></div></div>
<?php elseif($ac==='view'&&$si&&$ci&&$sn):?><div class="alert alert-info py-1 small mt-2">কোনো শিক্ষার্থী পাওয়া যায়নি</div><?php endif;?>
</div>
<script>document.addEventListener('DOMContentLoaded',function(){
document.querySelector('select[name="class_id"]')?.addEventListener('change',function(){const f=this.closest('form');if(f){const s=f.querySelector('select[name="subject_id"]');if(s)s.selectedIndex=0;f.requestSubmit()}});
document.querySelector('select[name="session"]')?.addEventListener('change',function(){const f=this.closest('form');if(f){const s=f.querySelector('select[name="subject_id"]');if(s)s.selectedIndex=0;f.requestSubmit()}})});
</script></body></html>