<?php
// query/reports_query.php
session_start();
include '../../connection.php';
header('Content-Type: application/json; charset=utf-8');

// Helper: return JSON then exit
function json_exit($payload) {
    echo json_encode($payload);
    exit;
}

// If this is an approve request
if (isset($_POST['approve_report'])) {
    $report_id = intval($_POST['report_id'] ?? 0);
    if ($report_id <= 0) {
        json_exit(['success' => false, 'message' => 'Invalid report ID']);
    }

    // Find UniqueID from tbl_reports
    $stmt = $conn->prepare("SELECT UniqueID FROM tbl_reports WHERE report_id = ?");
    $stmt->bind_param('i', $report_id);
    $stmt->execute();
    $res = $stmt->get_result();
    if (!$row = $res->fetch_assoc()) {
        json_exit(['success' => false, 'message' => 'Report not found']);
    }
    $uniqueID = $row['UniqueID'];
    $stmt->close();

    // Update tbl_transaction.status to 'Settled' (matches your schema values)
    $stmt2 = $conn->prepare("UPDATE tbl_transaction SET status = 'Settled' WHERE UniqueID = ?");
    $stmt2->bind_param('i', $uniqueID);
    $ok = $stmt2->execute();
    $stmt2->close();

    if ($ok) {
        json_exit(['success' => true, 'message' => 'Report approved and complaint marked as Settled.']);
    } else {
        json_exit(['success' => false, 'message' => 'Failed to update complaint status.']);
    }
}

// Otherwise, loading the reports list (AJAX)
if (isset($_POST['loading_reports_list'])) {
    // We'll return HTML directly (the page expects HTML)
    header('Content-Type: text/html; charset=utf-8');

    $kagawad = $_POST['kagawad'] ?? 'all';
    $status = $_POST['status'] ?? 'all';
    $dateFrom = $_POST['dateFrom'] ?? '';
    $dateTo = $_POST['dateTo'] ?? '';
    $search = trim($_POST['search'] ?? '');

    // Build WHERE dynamically — using real_escape_string to avoid SQL injection risk here
    $whereClauses = [];
    if ($kagawad !== 'all' && $kagawad !== '') {
        $kagawadEsc = $conn->real_escape_string($kagawad);
        $whereClauses[] = "r.user_id = '$kagawadEsc'";
    }
    if ($status !== 'all' && $status !== '') {
        $statusEsc = $conn->real_escape_string($status);
        $whereClauses[] = "t.status = '$statusEsc'";
    }
    if (!empty($dateFrom) && !empty($dateTo)) {
        // sanitize dates (basic)
        $dateFromEsc = $conn->real_escape_string($dateFrom);
        $dateToEsc = $conn->real_escape_string($dateTo);
        $whereClauses[] = "(r.date_created BETWEEN '$dateFromEsc' AND '$dateToEsc')";
    }
    if ($search !== '') {
        $s = $conn->real_escape_string($search);
        $whereClauses[] = "(r.notes LIKE '%$s%' OR c.complain_name LIKE '%$s%' OR res.first_name LIKE '%$s%' OR res.last_name LIKE '%$s%')";
    }

    $whereSQL = $whereClauses ? 'WHERE ' . implode(' AND ', $whereClauses) : '';

    $sql = "
      SELECT 
        r.report_id,
        r.user_id,
        r.UniqueID,
        r.notes,
        r.date_created AS report_created,
        t.resident_id,
        t.incident_address,
        t.description,
        t.status AS complaint_status,
        t.date_created AS date_of_transaction,
        c.complain_name,
        res.first_name AS resident_fname,
        res.middle_name AS resident_mname,
        res.last_name AS resident_lname,
        u.first_name AS kagawad_fname,
        u.last_name AS kagawad_lname
      FROM tbl_reports r
      LEFT JOIN tbl_transaction t ON r.UniqueID = t.UniqueID
      LEFT JOIN tbl_complaint_types c ON t.complaint_type_id = c.complaint_type_id
      LEFT JOIN tbl_resident res ON t.resident_id = res.resident_id
      LEFT JOIN tbl_user u ON r.user_id = u.user_id
      $whereSQL
      ORDER BY r.date_created DESC
    ";

    $res = $conn->query($sql);
    if (!$res) {
        echo "<div class='alert alert-danger'>Query error.</div>";
        exit;
    }

    if ($res->num_rows === 0) {
        echo "<div class='alert alert-warning text-center'>No reports found for the selected filters.</div>";
        exit;
    }

    // Render each row as HTML (same structure as UI expects)
    while ($row = $res->fetch_assoc()) {
        $residentName = htmlspecialchars(trim($row['resident_fname'] . ' ' . $row['resident_mname'] . ' ' . $row['resident_lname']));
        $kagawadName = htmlspecialchars(trim($row['kagawad_fname'] . ' ' . $row['kagawad_lname']));
        $complainName = htmlspecialchars($row['complain_name'] ?? 'No Nature');
        $notes = htmlspecialchars($row['notes']);
        $reportDate = !empty($row['report_created']) ? date('F d, Y', strtotime($row['report_created'])) : '';
        $status = htmlspecialchars($row['complaint_status'] ?? 'Pending');

        // status class mapping
        $statusLower = strtolower($status);
        $statusClass = 'status-pending';
        if (strpos($statusLower, 'accept') !== false || strpos($statusLower, 'settled') !== false || strpos($statusLower, 'resolved') !== false) $statusClass = 'status-resolved';
        elseif (strpos($statusLower, 'ongoing') !== false || strpos($statusLower, 'in-progress') !== false) $statusClass = 'status-in-progress';
        elseif (strpos($statusLower, 'urgent') !== false) $statusClass = 'status-urgent';

        // Build avatar URLs (ui-avatars)
        $kagawadAvatar = 'https://ui-avatars.com/api/?name=' . urlencode($kagawadName) . '&background=3498db&color=fff';
        $residentAvatar = 'https://ui-avatars.com/api/?name=' . urlencode($residentName) . '&background=e74c3c&color=fff';

        // Output HTML block
        echo '
        <div class="report-item">
            <div class="row">
                <div class="col-md-1 text-center">
                    <img src="'. $kagawadAvatar .'" class="kagawad-avatar" alt="Kagawad Avatar">
                </div>
                <div class="col-md-8">
                    <div class="d-flex align-items-center mb-2">
                        <h6 class="mb-0 mr-2">'. $complainName .'</h6>
                        <span class="status-badge '. $statusClass .'">'. $status .'</span>
                    </div>
                    <p class="mb-2">'. $notes .'</p>
                    <div class="d-flex align-items-center">
                        <img src="'. $residentAvatar .'" class="resident-avatar mr-2" alt="Resident Avatar">
                        <small class="text-muted mr-3"><strong>Resident:</strong> '. $residentName .'</small>
                        <small class="text-muted mr-3"><i class="far fa-clock mr-1"></i> '. $reportDate .'</small>
                    </div>
                </div>
                <div class="col-md-3 text-right">
                    <a class="btn btn-sm btn-primary action-btn" href="pages/form_two.php?page='. intval($row['report_id']) .'" target="_blank">
                        <i class="fas fa-eye mr-1"></i> View Full Report
                    </a>
                    <button class="btn btn-sm btn-success action-btn approve-report" data-id="'. intval($row['report_id']) .'">
                        <i class="fas fa-check mr-1"></i> Approve
                    </button>
                </div>
            </div>
        </div>';
    }

    exit;
}

// default fallback
echo json_encode(['success' => false, 'message' => 'No valid action specified.']);
exit;
