<?php 
include '../../connection.php';


if (isset($_POST['loading_resolved_complaints'])) {
    header("Content-Type: application/json");

    $query = "
        SELECT 
            COUNT(*) AS total,
            ct.complain_name
        FROM tbl_transaction t
        LEFT JOIN tbl_complaint_types ct 
            ON t.complaint_type_id = ct.complaint_type_id
        WHERE t.status = 'settled' -- ✅ Only count resolved complaints
        GROUP BY ct.complain_name
        ORDER BY ct.complain_name
    ";

    $query_result = $conn->query($query);

    $complaint_total = [];
    $complaint_name = [];

    while ($row = $query_result->fetch_assoc()) {
        $complaint_total[] = (int)$row['total'];
        $complaint_name[] = $row['complain_name']; // ✅ Keep it as string
    }

    echo json_encode([
        "complaint_total" => $complaint_total,
        "complaint_name" => $complaint_name
    ]);
    exit;
}


if (isset($_POST['loading_status_breakdown'])) {
    header("Content-Type: application/json");

    $query = "
        SELECT 
            COUNT(*) AS total_number_per_status, 
            status 
        FROM tbl_transaction  
        GROUP BY status 
        ORDER BY status
    ";
    $query_result = $conn->query($query);

    $status_label = [];
    $total_label = [];

    while ($row = $query_result->fetch_assoc()) {
        $status_label[] = $row['status'];
        $total_label[] = (int)$row['total_number_per_status'];
    }

    echo json_encode([
        "status_label" => $status_label,
        "total_label" => $total_label
    ]);
    exit;
}


if (isset($_POST['loading_transactions_status'])) {
  

    $status = $_POST['status'];

    // ✅ Use prepared statement to prevent SQL injection
    $stmt = $conn->prepare("
        SELECT 
            t.UniqueID,
            t.resident_id,
            t.description,
            t.status,
            t.date_created,
            r.first_name,
            r.middle_name,
            r.last_name
        FROM tbl_transaction t
        LEFT JOIN tbl_resident r ON t.resident_id = r.resident_id
        WHERE t.status = ?
        ORDER BY t.date_created DESC
    ");
    $stmt->bind_param("s", $status);
    $stmt->execute();
    $result = $stmt->get_result();

    // ✅ Start table
    echo '
    <table class="table table-striped table-hover">
        <thead>
            <tr>
                <th>#</th>
                <th>Resident Name</th>
                <th>Description</th>
                <th>Status</th>
                <th>Date Created</th>
            </tr>
        </thead>
        <tbody>
    ';

if ($result && $result->num_rows > 0) {
    $count = 1;
    while ($row = $result->fetch_assoc()) {
        // Safely build the full name
        $fullname = htmlspecialchars($row['last_name'] . ', ' . $row['first_name'] . ' ' . $row['middle_name']);

        // Normalize status for consistent matching
        $status = strtolower(trim($row['status'] ?? ''));

        // Display badge based on status
        switch ($status) {
            case 'pending':
                $statusBadge = '<span class="badge bg-warning text-dark">Pending</span>';
                break;
            case 'resolved':
                $statusBadge = '<span class="badge bg-success">Resolved</span>';
                break;
            case 'rejected':
                $statusBadge = '<span class="badge bg-danger">Rejected</span>';
                break;
            default:
                $statusBadge = '<span class="badge bg-secondary">' . htmlspecialchars(ucfirst($status)) . '</span>';
        }

        echo '
        <tr>
            <td>' . $count++ . '</td>
            <td>' . $fullname . '</td>
            <td>' . htmlspecialchars($row['description']) . '</td>
            <td>' . $statusBadge . '</td>
            <td>' . date("F d, Y h:i A", strtotime($row['date_created'])) . '</td>
        </tr>';
    }
} else {
    echo '
    <tr>
        <td colspan="5" class="text-center text-muted py-3">
            No transactions found for this status.
        </td>
    </tr>';
}

    echo '
        </tbody>
    </table>';

    $stmt->close();
}

// ✅ Load Barangay Officials
if (isset($_POST['loading_all_officials'])) {
    $query = "
        SELECT 
            os.organization_id,
            os.user_id,
            os.position,
            os.status,
            os.date_created,
            u.first_name,
            u.middle_name,
            u.last_name,
            u.contact
        FROM tbl_organizational_structure os
        LEFT JOIN tbl_user u ON os.user_id = u.user_id
    ";
    $result = $conn->query($query);

    echo '<table class="table table-hover">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Contact</th>
                </tr>
            </thead>
            <tbody>';
    
    while ($row = $result->fetch_assoc()) {
        $fullname = "{$row['last_name']}, {$row['first_name']} {$row['middle_name']}";
        echo '
            <tr>
                <td>
                    <div class="user-profile">
                        <img src="https://ui-avatars.com/api/?name='.urlencode($fullname).'&background=3498db&color=fff" alt="'.$fullname.'">
                        <span>'.$fullname.'</span>
                    </div>
                </td>
                <td>Barangay '.$row['position'].'</td>
                <td>'.$row['contact'].'</td>
            </tr>
        ';
    }

    echo '</tbody></table>';
    exit();
}

// ✅ Load Most Complaints Per Month
if (isset($_POST['most_complaints_per_month'])) {
    header("Content-Type: application/json; charset=UTF-8");

    $sql = "
  SELECT 
    DATE_FORMAT(t.incident_date, '%M %Y') AS month_label,
    c.complain_name,
    COUNT(t.UniqueID) AS total_complaints
FROM tbl_transaction t
LEFT JOIN tbl_complaint_types c 
    ON t.complaint_type_id = c.complaint_type_id

GROUP BY DATE_FORMAT(t.incident_date, '%Y-%m'), c.complain_name
ORDER BY STR_TO_DATE(month_label, '%M %Y'), total_complaints DESC;
    ";

    $result = $conn->query($sql);
    $data = [];

    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $row['total_complaints'] = (int)$row['total_complaints'];
            $data[] = $row;
        }
    }

    echo json_encode($data);
    exit();
}
?>
