<?php
include 'db.php'; 

$searchKeyword = isset($_GET['search']) ? strtolower($_GET['search']) : '';

// First, check which columns exist in the table
$columnsQuery = "SHOW COLUMNS FROM Report";
$columnsResult = $conn->query($columnsQuery);
$existingColumns = [];
while ($column = $columnsResult->fetch_assoc()) {
    $existingColumns[] = $column['Field'];
}

// Check if new datetime columns exist
$hasWaktuTemuan = in_array('waktu_temuan', $existingColumns);
$hasWaktuUpdateManual = in_array('waktu_update_manual', $existingColumns);
$hasTimestampUpdate = in_array('timestamp_update', $existingColumns);

// Build the base query
$query = "SELECT * FROM Report";

if (!empty($searchKeyword)) {
    $searchConditions = [
        "LOWER(id) LIKE ?",
        "LOWER(status) LIKE ?", 
        "LOWER(timestamp) LIKE ?",
        "LOWER(division) LIKE ?",
        "LOWER(block) LIKE ?",
        "LOWER(kj) LIKE ?",
        "LOWER(classification) LIKE ?",
        "LOWER(damageType) LIKE ?",
        "LOWER(mitigationPlan) LIKE ?",
        "LOWER(notes) LIKE ?"
    ];
    
    // Add datetime columns to search if they exist
    if ($hasWaktuTemuan) {
        $searchConditions[] = "LOWER(waktu_temuan) LIKE ?";
    }
    if ($hasWaktuUpdateManual) {
        $searchConditions[] = "LOWER(waktu_update_manual) LIKE ?";
    }
    if ($hasTimestampUpdate) {
        $searchConditions[] = "LOWER(timestamp_update) LIKE ?";
    }
    
    $query .= " WHERE " . implode(" OR ", $searchConditions);
    $query .= " ORDER BY timestamp DESC";
    
    $stmt = $conn->prepare($query);
    if (!$stmt) {
        die("Prepare failed: " . $conn->error);
    }
    
    $searchPattern = '%' . $searchKeyword . '%';
    $paramCount = count($searchConditions);
    $params = array_fill(0, $paramCount, $searchPattern);
    $types = str_repeat('s', $paramCount);
    
    $stmt->bind_param($types, ...$params);
    $stmt->execute();
    $result = $stmt->get_result();
} else {
    $query .= " ORDER BY timestamp DESC";
    $result = $conn->query($query);
}

if (!$result) {
    die("Query failed: " . $conn->error);
}

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        // Format timestamp
        $formattedDate = date('d M Y, H:i', strtotime($row['timestamp']));
        
        // Format waktu temuan if exists
        $formattedWaktuTemuan = '-';
        if ($hasWaktuTemuan && !empty($row['waktu_temuan']) && $row['waktu_temuan'] !== '0000-00-00 00:00:00') {
            $formattedWaktuTemuan = date('d M Y, H:i', strtotime($row['waktu_temuan']));
        }
        // Format waktu perlakuan
        $formattedWaktuPerlakuan = '-';
        if (isset($row['waktu_perlakuan']) && !empty($row['waktu_perlakuan']) && $row['waktu_perlakuan'] !== '0000-00-00 00:00:00') {
            $formattedWaktuPerlakuan = date('d M Y, H:i', strtotime($row['waktu_perlakuan']));
        }
        // Format waktu verifikasi
        $formattedWaktuVerifikasi = '-';
        if (isset($row['waktu_verifikasi']) && !empty($row['waktu_verifikasi']) && $row['waktu_verifikasi'] !== '0000-00-00 00:00:00') {
            $formattedWaktuVerifikasi = date('d M Y, H:i', strtotime($row['waktu_verifikasi']));
        }
        // Format waktu update manual
        $formattedUpdateDate = '-';
        if ($hasWaktuUpdateManual && !empty($row['waktu_update_manual']) && $row['waktu_update_manual'] !== '0000-00-00 00:00:00') {
            $formattedUpdateDate = date('d M Y, H:i', strtotime($row['waktu_update_manual']));
        }
        
        // Status badge styling
        switch ($row['status']) {
            case 'Belum Ditangani':
                $badgeClass = 'badge-danger';
                break;
            case 'Sedang Ditangani':
                $badgeClass = 'badge-warning';
                break;
            case 'Selesai':
                $badgeClass = 'badge-success';
                break;
            default:
                $badgeClass = 'badge-secondary'; 
                break;
        }
        
        echo "<tr>";
        echo "<td>" . htmlspecialchars($row['id']) . "</td>";
        echo "<td>
                <div class='d-flex'>
                    <a href='?active=photo&id=" . htmlspecialchars($row['id']) . "' class='btn btn-primary mr-2' title='Lihat Foto'>
                        <i class='fas fa-eye'></i>
                    </a>
                    <a href='?active=edit&id=" . htmlspecialchars($row['id']) . "' class='btn btn-warning mr-2' title='Edit'>
                        <i class='fas fa-edit'></i>
                    </a>
                    <a href='delete_report.php?id=" . htmlspecialchars($row['id']) . "' class='cursor-pointer btn btn-danger' title='Hapus' onclick='return confirm(\"Apakah Anda yakin ingin menghapus data ini?\");'>
                        <i class='fas fa-trash'></i>
                    </a>
                </div>
              </td>";
        echo "<td><span class='badge badge-pill " . htmlspecialchars($badgeClass) . "'>" . htmlspecialchars($row['status']) . "</span></td>";
        echo "<td>" . htmlspecialchars($formattedWaktuTemuan) . "</td>";
        echo "<td>" . htmlspecialchars($formattedWaktuPerlakuan) . "</td>";
        echo "<td>" . htmlspecialchars($formattedWaktuVerifikasi) . "</td>";
        echo "<td>" . htmlspecialchars($formattedUpdateDate) . "</td>";
        echo "<td>" . htmlspecialchars($row['division']) . "</td>";
        echo "<td>" . htmlspecialchars($row['block']) . "</td>";
        echo "<td>" . htmlspecialchars($row['kj']) . "</td>";
        echo "<td>" . htmlspecialchars($row['classification']) . "</td>";
        echo "<td>" . htmlspecialchars($row['damageType']) . "</td>";
        echo "<td>" . htmlspecialchars($row['mitigationPlan']) . "</td>";
        echo "<td>" . htmlspecialchars($row['notes']) . "</td>";
        echo "</tr>";
    }
} else {
    echo "<tr><td colspan='13'>Tidak ada data ditemukan</td></tr>";
}

$conn->close();
?>
