<link rel="icon" href="logo.jpg" type="image/jpg">
<?php
// File: households.php
// The main page for adding and managing households.
require_once "db.php";
check_login();

$page_title = "Household Management";
$error_message = '';
$success_message = '';

// Handle form submission for adding a new household
if ($_SERVER["REQUEST_METHOD"] == "POST" && isset($_POST['add_household'])) {
    $household_no = trim($_POST['household_no']);
    $purok = trim($_POST['purok']);

    if (empty($household_no) || empty($purok)) {
        $error_message = "Household No. and Purok are required.";
    } else {
        // **UPDATED LOGIC**: Check for duplicate household number WITHIN THE SAME PUROK.
        $stmt_check = $conn->prepare("SELECT id FROM households WHERE household_no = ? AND purok = ?");
        $stmt_check->bind_param("ss", $household_no, $purok);
        $stmt_check->execute();
        $stmt_check->store_result();

        if ($stmt_check->num_rows > 0) {
            $error_message = "Error: A household with this number already exists in " . htmlspecialchars($purok) . ".";
        } else {
            $stmt_add = $conn->prepare("INSERT INTO households (household_no, purok) VALUES (?, ?)");
            $stmt_add->bind_param("ss", $household_no, $purok);
            if ($stmt_add->execute()) {
                $success_message = "New household added successfully!";
            } else {
                $error_message = "Error adding household: " . $stmt_add->error;
            }
            $stmt_add->close();
        }
        $stmt_check->close();
    }
}

include 'header.php';

// Handle search
$search_query = $_GET['search'] ?? '';
$sql_condition = '';
if (!empty($search_query)) {
    $sql_condition = "WHERE h.household_no LIKE ? OR h.purok LIKE ? OR r.last_name LIKE ?";
}

// Fetch households from the database
$sql = "
    SELECT h.id, h.household_no, h.purok, 
           r.first_name, r.last_name, r.suffix
    FROM households h
    LEFT JOIN residents r ON h.id = r.household_id AND r.relation_to_head = 'Head'
    $sql_condition
    ORDER BY h.purok, h.household_no ASC
";

$stmt = $conn->prepare($sql);
if (!empty($search_query)) {
    $search_term = "%" . $search_query . "%";
    $stmt->bind_param("sss", $search_term, $search_term, $search_term);
}
$stmt->execute();
$result = $stmt->get_result();

?>

<div class="main-content">
    <div class="page-header">
        <h1>Household Management</h1>
        <p>Add new households and manage their members.</p>
    </div>

    <!-- Add Household Form -->
    <div class="card form-container">
        <h2 class="card-title">Add New Household</h2>
        <?php if($error_message): ?><div class="alert alert-danger"><?php echo $error_message; ?></div><?php endif; ?>
        <?php if($success_message): ?><div class="alert alert-success"><?php echo $success_message; ?></div><?php endif; ?>

        <form action="households.php" method="post">
            <div class="form-grid">
                <div class="form-group">
                    <label for="household_no">Household No.</label>
                    <input type="text" id="household_no" name="household_no" class="form-control" required>
                </div>
                <div class="form-group">
                    <label for="purok">Purok</label>
                    <select id="purok" name="purok" class="form-control" required>
                        <option value="">-- Select Purok --</option>
                        <option value="Purok 1">Purok 1</option>
                        <option value="Purok 2">Purok 2</option>
                        <option value="Purok 3">Purok 3</option>
                        <option value="Purok 4">Purok 4</option>
                        <option value="Purok 5">Purok 5</option>
                        <option value="Purok 6">Purok 6</option>
                        <option value="Purok 7">Purok 7</option>
                    </select>
                </div>
            </div>
            <div class="form-actions">
                <button type="submit" name="add_household" class="btn btn-primary">Save Household</button>
            </div>
        </form>
    </div>

    <!-- Household List -->
    <div class="card">
        <div class="report-header">
            <h2 class="card-title">Household List</h2>
            <form action="households.php" method="get" class="search-form">
                <input type="text" name="search" placeholder="Search by Household No, Purok, or Head..." value="<?php echo htmlspecialchars($search_query); ?>">
                <button type="submit" class="btn btn-primary btn-sm">Search</button>
            </form>
        </div>

        <div class="table-responsive">
            <table>
                <thead>
                    <tr>
                        <th>Household No.</th>
                        <th>Purok</th>
                        <th>Head of Family</th>
                        <th>Actions</th>
                    </tr>
                </thead>
                <tbody>
                    <?php if ($result->num_rows > 0): ?>
                        <?php while($row = $result->fetch_assoc()): ?>
                            <tr>
                                <td><?php echo htmlspecialchars($row['household_no']); ?></td>
                                <td><?php echo htmlspecialchars($row['purok']); ?></td>
                                <td>
                                    <?php 
                                        if (!empty($row['last_name'])) {
                                            echo htmlspecialchars($row['last_name'] . ", " . $row['first_name'] . " " . $row['suffix']);
                                        } else {
                                            echo "<span style='color: #999;'>No head assigned</span>";
                                        }
                                    ?>
                                </td>
                                <td>
                                    <a href="manage_members.php?household_id=<?php echo $row['id']; ?>" class="btn btn-success btn-sm">
                                        <i class="fas fa-users"></i> Manage Members
                                    </a>
                                </td>
                            </tr>
                        <?php endwhile; ?>
                    <?php else: ?>
                        <tr>
                            <td colspan="4">No households found. Start by adding one above.</td>
                        </tr>
                    <?php endif; ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

<?php 
$stmt->close();
include 'footer.php'; 
?>

