<?php

include "../config/db.php";
include "header.php";

/* GET SCHEME ID */

$scheme_id = intval($_GET['id'] ?? 0);

if($scheme_id == 0){
echo "Invalid scheme";
exit;
}


/* FETCH SCHEME */

$q = mysqli_query($conn,"
SELECT s.*, c.caste_name, e.education_name
FROM schemes s
LEFT JOIN caste_master c ON s.category_id=c.id
LEFT JOIN education_master e ON s.education_id=e.id
WHERE s.id='$scheme_id'
LIMIT 1
");

$scheme = mysqli_fetch_assoc($q);

if(!$scheme){
echo "Scheme not found";
exit;
}


/* ELIGIBLE MEMBERS FILTER */

$where = "WHERE 1";


/* Gender */

if(!empty($scheme['gender']) && $scheme['gender'] != 'Any'){
$where .= " AND m.gender='".mysqli_real_escape_string($conn,$scheme['gender'])."'";
}


/* Age */

if($scheme['age_min'] > 0 && $scheme['age_max'] > 0){
$where .= " AND m.age BETWEEN ".intval($scheme['age_min'])." AND ".intval($scheme['age_max']);
}


/* Education */

if(!empty($scheme['education_id'])){
$where .= " AND m.education_id >= ".intval($scheme['education_id']);
}


/* Family Type */

if(!empty($scheme['family_type']) && $scheme['family_type'] != 'Any'){
$where .= " AND f.family_type='".mysqli_real_escape_string($conn,$scheme['family_type'])."'";
}


/* Caste */

if(!empty($scheme['caste_name'])){
$where .= " AND f.caste='".mysqli_real_escape_string($conn,$scheme['caste_name'])."'";
}


/* GET ELIGIBLE MEMBERS */

$members = mysqli_query($conn,"
SELECT 
m.id,
m.name,
m.gender,
f.family_head
FROM family_members m
LEFT JOIN families f ON m.family_id=f.id
$where
ORDER BY m.name
");

?>

<div class="container">

<h2><?php echo htmlspecialchars($scheme['scheme_name']); ?></h2>

<?php if(!empty($scheme['image'])){ ?>

<img src="/digital_panchayat/assets/schemes/<?php echo htmlspecialchars($scheme['image']); ?>" 
style="max-width:300px;margin-bottom:15px;">

<?php } ?>

<p><b>Category:</b>
<?php echo ($scheme['category_id']==0) ? "All" : htmlspecialchars($scheme['caste_name']); ?>
</p>

<p><b>Department:</b>
<?php echo htmlspecialchars($scheme['department']); ?>
</p>

<p><b>Gender:</b>
<?php echo htmlspecialchars($scheme['gender']); ?>
</p>

<p><b>Education:</b>
<?php echo (!empty($scheme['education_name'])) ? htmlspecialchars($scheme['education_name']) : "Any"; ?>
</p>

<p><b>Family Type:</b>
<?php echo htmlspecialchars($scheme['family_type']); ?>
</p>

<p><b>Age Range:</b>
<?php echo $scheme['age_min']; ?> - <?php echo $scheme['age_max']; ?>
</p>

<hr>

<p>
<?php echo nl2br(htmlspecialchars($scheme['description'])); ?>
</p>

<hr>

<h3>Eligible Members</h3>

<div style="overflow-x:auto;">

<table style="width:100%;border-collapse:collapse;min-width:400px;">

<tr style="background:#2c3e50;color:white">

<th style="padding:8px;border:1px solid #ddd;">Name</th>
<th style="padding:8px;border:1px solid #ddd;">Gender</th>
<th style="padding:8px;border:1px solid #ddd;">Family Head</th>

</tr>

<?php

if($members && mysqli_num_rows($members) > 0){

while($row=mysqli_fetch_assoc($members)){
?>

<tr>

<td style="padding:8px;border:1px solid #ddd;"><?php echo htmlspecialchars($row['name']); ?></td>

<td style="padding:8px;border:1px solid #ddd;"><?php echo htmlspecialchars($row['gender']); ?></td>

<td style="padding:8px;border:1px solid #ddd;"><?php echo htmlspecialchars($row['family_head']); ?></td>

</tr>

<?php
}

}else{

echo "<tr><td colspan='3' align='center' style='padding:10px;'>No Eligible Members Found</td></tr>";

}

?>

</table>

</div>

</div>

<?php include "footer.php"; ?>