<?php 
require_once("public/connection.php");
session_start();

/* ===================== HANDLE ACTIONS ===================== */

// Remove student from family
if (isset($_GET['remove_student'])) {
    $reg = $connection->real_escape_string($_GET['remove_student']);
    $family_id = intval($_GET['family_id']);
    $connection->query("UPDATE students SET family_id=NULL, family_regno=NULL WHERE reg_number='$reg'");
    $connection->query("DELETE FROM family_students WHERE reg_number='$reg' AND family_id=$family_id");
    $_SESSION['success'] = "Student removed from family successfully.";
    header("Location: family_payment?view_family=".$family_id);
    exit;
}

/* ===================== CREATE/UPDATE/DELETE FAMILY ===================== */

// Create family
if (isset($_POST['save_family'])) {
    $family_name = trim($_POST['family_name']);
    $discount = floatval($_POST['discount_percent']);

    $stmt = $connection->prepare("INSERT INTO families (family_name, discount_percent) VALUES (?, ?)");
    $stmt->bind_param("sd", $family_name, $discount);
    $stmt->execute();

    $_SESSION['success'] = "Family created successfully.";
    header("Location: family_payment");
    exit;
}

// Update family
if (isset($_POST['update_family'])) {
    $id = intval($_POST['family_id']);
    $family_name = trim($_POST['family_name']);
    $discount = floatval($_POST['discount_percent']);

    $stmt = $connection->prepare("UPDATE families SET family_name=?, discount_percent=? WHERE id=?");
    $stmt->bind_param("sdi", $family_name, $discount, $id);
    $stmt->execute();

    $_SESSION['success'] = "Family updated successfully.";
    header("Location: family_payment");
    exit;
}

// Delete family
if (isset($_GET['delete'])) {
    $id = intval($_GET['delete']);

    $connection->query("UPDATE students SET family_id=NULL, family_regno=NULL WHERE family_id=$id");
    $connection->query("DELETE FROM families WHERE id=$id");
    $connection->query("DELETE FROM family_students WHERE family_id=$id");

    $_SESSION['success'] = "Family deleted successfully.";
    header("Location: family_payment");
    exit;
}

/* ===================== ASSIGN STUDENTS ===================== */
if (isset($_POST['assign_family'])) {
    $family_id = intval($_POST['family_id']);
    $reg_numbers = $_POST['reg_number'] ?? [];

    if(!empty($reg_numbers)){
        // Check if any student already has a family_regno
        $existing = $connection->query("SELECT family_regno FROM students WHERE family_id=$family_id LIMIT 1")->fetch_assoc();
        $family_regno = $existing['family_regno'] ?? "FAM".time();

        foreach ($reg_numbers as $reg) {
            $reg = trim($reg);
            $stmt = $connection->prepare("
                UPDATE students 
                SET family_id=?, family_regno=? 
                WHERE reg_number=?
            ");
            $stmt->bind_param("iss", $family_id, $family_regno, $reg);
            $stmt->execute();
            $stmt->close();

            // Insert into family_students if not exists
            $check = $connection->prepare("SELECT id FROM family_students WHERE reg_number = ? AND family_id = ?");
            $check->bind_param("si", $reg, $family_id);
            $check->execute();
            $res = $check->get_result();

            if ($res->num_rows === 0) {
                $insert = $connection->prepare(
                    "INSERT INTO family_students (family_id, reg_number, created_at, updated_at) VALUES (?, ?, NOW(), NOW())"
                );
                $insert->bind_param("is", $family_id, $reg);
                $insert->execute();
                $insert->close();
            }
            $check->close();
        }
        $_SESSION['success'] = "Students assigned successfully. Family RegNo: ".$family_regno;
    }

    header("Location: family_payment");
    exit;
}

/* ===================== FETCH DATA ===================== */
$famRes = $connection->query("SELECT * FROM families ORDER BY family_name");
$classRes = $connection->query("SELECT DISTINCT class FROM students ORDER BY class");
$filterClass = $_GET['class'] ?? '';

$stuSql = "SELECT reg_number, firstname, lastname, class, family_id FROM students";
if ($filterClass !== '') $stuSql .= " WHERE class='" . $connection->real_escape_string($filterClass) . "'";
$stuSql .= " ORDER BY lastname ASC";
$stuRes = $connection->query($stuSql);

$editFamily = isset($_GET['edit']) ? $connection->query("SELECT * FROM families WHERE id=".intval($_GET['edit']))->fetch_assoc() : null;

$assignedFamilyId = $_GET['view_family'] ?? '';
$assignedStudents = [];
if ($assignedFamilyId) {
    $assignedStudentsRes = $connection->query(
        "SELECT reg_number, firstname, lastname, class, family_regno 
         FROM students 
         WHERE family_id=".intval($assignedFamilyId)." 
         ORDER BY lastname"
    );
    while($row = $assignedStudentsRes->fetch_assoc()) $assignedStudents[] = $row;
}
?>

<div class="container-fluid py-3">

<?php if (!empty($_SESSION['success'])): ?>
<div class="alert alert-success alert-dismissible fade show" id="successAlert">
  <strong>Success!</strong> <?= $_SESSION['success'] ?>
  <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
</div>
<?php unset($_SESSION['success']); endif; ?>

<div class="row g-3">

<!-- FAMILY FORM -->
<div class="col-md-5">
  <div class="card shadow-sm">
    <div class="card-header bg-primary text-white"><h5 class="mb-0"><?= $editFamily ? 'Edit Family' : 'Create Family' ?></h5></div>
    <div class="card-body">
      <form method="post">
        <input type="hidden" name="family_id" value="<?= $editFamily['id'] ?? '' ?>">
        <div class="mb-3">
          <label class="form-label">Family Name</label>
          <input type="text" name="family_name" class="form-control" value="<?= htmlspecialchars($editFamily['family_name'] ?? '') ?>" required>
        </div>
        <div class="mb-3">
          <label class="form-label">Discount (%)</label>
          <input type="number" step="0.01" name="discount_percent" class="form-control" value="<?= $editFamily['discount_percent'] ?? 0 ?>">
        </div>
        <button class="btn btn-success w-100" name="<?= $editFamily ? 'update_family' : 'save_family' ?>"><?= $editFamily ? 'Update Family' : 'Create Family' ?></button>
      </form>
    </div>
  </div>
</div>

<!-- FAMILY LIST -->
<div class="col-md-7">
  <div class="card shadow-sm">
    <div class="card-header bg-info text-white">
      <h5 class="mb-0">Families</h5>
    </div>
    <div class="card-body p-0">
      <!-- Scrollable wrapper -->
      <div class="families-scroll">
        <table class="table table-hover table-bordered align-middle mb-0">
          <thead class="table-light">
            <tr>
              <th>Family</th>
              <th>Discount</th>
              <th class="text-center">Actions</th>
            </tr>
          </thead>
          <tbody>
            <?php while ($f = $famRes->fetch_assoc()): ?>
            <tr>
              <td><?= htmlspecialchars($f['family_name']) ?></td>
              <td><span class="badge bg-success"><?= $f['discount_percent'] ?>%</span></td>
              <td class="text-center">
                <a href="?edit=<?= $f['id'] ?>" class="btn btn-sm btn-warning">Edit</a>
                <a href="?delete=<?= $f['id'] ?>" class="btn btn-sm btn-danger" onclick="return confirm('Delete this family?')">Delete</a>
                <a href="?view_family=<?= $f['id'] ?>" class="btn btn-sm btn-info">View Students</a>
              </td>
            </tr>
            <?php endwhile; ?>
          </tbody>
        </table>
      </div>
    </div>
  </div>
  </div>
</div>

<!-- ===================== CSS ===================== -->
<style>
/* Scrollable families table */
.families-scroll {
  max-height: 280px; /* Adjust so ~5 rows fit */
  overflow-y: auto;
}

/* Optional: show a nice thin scrollbar */
.families-scroll::-webkit-scrollbar {
  width: 8px;
}

.families-scroll::-webkit-scrollbar-thumb {
  background-color: rgba(0,0,0,0.3);
  border-radius: 4px;
}

.families-scroll::-webkit-scrollbar-track {
  background-color: rgba(0,0,0,0.05);
}
</style>

<!-- ASSIGN STUDENTS -->
<div class="card shadow-sm mt-4">
  <div class="card-header bg-secondary text-white"><h5 class="mb-0">Assign Students to Family</h5></div>
  <div class="card-body">
    <form method="post">
      <div class="row g-3 mb-3">
        <div class="col-md-4">
          <select name="family_id" class="form-control" required>
            <option value="">-- Select Family --</option>
            <?php
            $famRes2 = $connection->query("SELECT * FROM families");
            while ($f = $famRes2->fetch_assoc()):
            ?>
            <option value="<?= $f['id'] ?>"><?= $f['family_name'] ?> (<?= $f['discount_percent'] ?>%)</option>
            <?php endwhile; ?>
          </select>
        </div>
        <div class="col-md-4">
          <select name="class" class="form-control" onchange="window.location='?class='+this.value;">
            <option value="">-- Filter by Class --</option>
            <?php
            $classRes2 = $connection->query("SELECT DISTINCT class FROM students ORDER BY class");
            while ($c = $classRes2->fetch_assoc()):
            ?>
            <option value="<?= $c['class'] ?>" <?= $filterClass == $c['class'] ? 'selected' : '' ?>><?= $c['class'] ?></option>
            <?php endwhile; ?>
          </select>
        </div>
      </div>

      <div class="table-responsive">
        <table class="table table-bordered table-hover align-middle">
          <thead class="table-light">
            <tr>
              <th>Select</th>
              <th>Reg No</th>
              <th>Name</th>
              <th>Class</th>
            </tr>
          </thead>
          <tbody>
            <?php while ($s = $stuRes->fetch_assoc()): ?>
            <tr>
              <td><input type="checkbox" name="reg_number[]" value="<?= $s['reg_number'] ?>"></td>
              <td><?= $s['reg_number'] ?></td>
              <td><?= $s['lastname'].' '.$s['firstname'] ?></td>
              <td><?= $s['class'] ?></td>
            </tr>
            <?php endwhile; ?>
          </tbody>
        </table>
      </div>
      <button name="assign_family" class="btn btn-primary mt-2 w-100">Assign Selected Students</button>
    </form>
  </div>
</div>

<!-- VIEW ASSIGNED STUDENTS -->
<?php if($assignedFamilyId): ?>
<div class="card shadow-sm mt-4">
  <div class="card-header bg-dark text-white"><h5 class="mb-0">Students in Family: <?= htmlspecialchars($connection->query("SELECT family_name FROM families WHERE id=".intval($assignedFamilyId))->fetch_assoc()['family_name']) ?></h5></div>
  <div class="card-body table-responsive">
    <?php if(count($assignedStudents) > 0): ?>
    <table class="table table-bordered table-hover align-middle">
      <thead class="table-light">
        <tr>
          <th>Reg No</th>
          <th>Name</th>
          <th>Class</th>
          <th>Family Reg No</th>
          <th>Action</th>
        </tr>
      </thead>
      <tbody>
        <?php foreach($assignedStudents as $s): ?>
        <tr>
          <td><?= $s['reg_number'] ?></td>
          <td><?= $s['lastname'].' '.$s['firstname'] ?></td>
          <td><?= $s['class'] ?></td>
          <td>
            <div class="d-flex align-items-center">
              <span class="badge bg-primary me-2" id="regno-<?= $s['reg_number'] ?>"><?= $s['family_regno'] ?></span>
              <button class="btn btn-outline-light btn-sm" onclick="copyToClipboard('regno-<?= $s['reg_number'] ?>')">Copy</button>
            </div>
          </td>
          <td>
            <a href="?remove_student=<?= $s['reg_number'] ?>&family_id=<?= $assignedFamilyId ?>" class="btn btn-sm btn-danger" onclick="return confirm('Remove student from family?')">Remove</a>
          </td>
        </tr>
        <?php endforeach; ?>
      </tbody>
    </table>
    <?php else: ?>
    <div class="alert alert-info">No students assigned to this family yet.</div>
    <?php endif; ?>
  </div>
</div>
<?php endif; ?>

<script>
window.addEventListener('DOMContentLoaded', () => {
  const alertBox = document.getElementById('successAlert');
  if(alertBox){
    setTimeout(()=>{alertBox.classList.remove('show'); alertBox.classList.add('hide');}, 3000);
  }
});
function copyToClipboard(id){
  const text = document.getElementById(id).innerText;
  navigator.clipboard.writeText(text).then(()=> alert('Copied: '+text));
}
</script>