# Tutorial Hari 6: CRUD Fasilitas (MySQL Spatial)

## 🎯 Tujuan
- Memahami MySQL Spatial functions
- Melanjutkan library spatial.php (bagian fasilitas)
- Membuat fungsi CRUD fasilitas
- Memahami konversi koordinat

---

## 📚 Teori: MySQL Spatial

### Apa itu MySQL Spatial?

MySQL Spatial adalah fitur untuk menyimpan dan mengolah data geografis langsung di database. Lebih efisien daripada menyimpan koordinat sebagai text biasa.

### Tipe Data Spatial

| Tipe | Kegunaan | Contoh |
|------|----------|--------|
| **POINT** | Satu titik | Lokasi fasilitas |
| **LINESTRING** | Garis/path | Jalan, sungai |
| **POLYGON** | Area tertutup | Batas desa |
| **GEOMETRY** | Semua tipe | Kolom fleksibel |

### Fungsi Spatial MySQL

| Fungsi | Kegunaan | Contoh |
|--------|----------|--------|
| `ST_PointFromText()` | Buat POINT dari text | `ST_PointFromText('POINT(106.8 -6.2)')` |
| `ST_X()` | Ambil longitude | `ST_X(koordinat)` → 106.8 |
| `ST_Y()` | Ambil latitude | `ST_Y(koordinat)` → -6.2 |
| `ST_AsText()` | Konversi ke text | `POINT(106.8 -6.2)` |
| `ST_Distance()` | Hitung jarak | Meter antara 2 titik |

### WKT (Well-Known Text)

WKT adalah format text untuk data spatial:

| Tipe | Format WKT |
|------|------------|
| Point | `POINT(longitude latitude)` |
| Line | `LINESTRING(lng1 lat1, lng2 lat2, ...)` |
| Polygon | `POLYGON((lng1 lat1, lng2 lat2, ..., lng1 lat1))` |

> ⚠️ **PENTING:** Format WKT adalah `POINT(longitude latitude)` - kebalikan dari biasanya!

---

## 📚 Teori: Kategori Fasilitas

Aplikasi kita mendukung 6 kategori fasilitas:

| Key | Label | Icon | Warna | Contoh |
|-----|-------|------|-------|--------|
| `pendidikan` | Pendidikan | fa-school | #3b82f6 | SD, SMP, SMA, TK |
| `kesehatan` | Kesehatan | fa-hospital | #ef4444 | Puskesmas, Klinik |
| `ibadah` | Ibadah | fa-place-of-worship | #22c55e | Masjid, Gereja |
| `pemerintahan` | Pemerintahan | fa-landmark | #f59e0b | Kantor Desa |
| `umkm` | UMKM | fa-store | #ec4899 | Toko, Warung |
| `wisata` | Wisata | fa-mountain-sun | #8b5cf6 | Air Terjun |

---

## 1. Tambahkan ke spatial.php

Buka file `lib/spatial.php` dan **tambahkan** kode berikut di bawah fungsi peta:

```php
// ==========================================
// CRUD FASILITAS
// ==========================================

/**
 * Ambil semua fasilitas untuk peta tertentu
 * 
 * ST_X() = ambil longitude dari POINT
 * ST_Y() = ambil latitude dari POINT
 * 
 * @param int $petaId - ID peta
 * @return array - Daftar fasilitas
 */
function getFasilitasByPeta($petaId) {
    try {
        $stmt = db()->prepare("
            SELECT id, nama, jenis, 
                   ST_X(koordinat) as lng,
                   ST_Y(koordinat) as lat, 
                   alamat, keterangan, ikon, warna, 
                   created_at, updated_at
            FROM fasilitas_desa 
            WHERE peta_id = ?
            ORDER BY jenis, nama
        ");
        $stmt->execute([$petaId]);
        return $stmt->fetchAll();
        
    } catch (Exception $e) {
        return [];
    }
}

/**
 * Ambil fasilitas dalam format GeoJSON
 * Format ini langsung bisa dipakai Leaflet.js
 * 
 * @param int $petaId - ID peta
 * @return array - GeoJSON FeatureCollection
 */
function getFasilitasGeoJSON($petaId) {
    $fasilitas = getFasilitasByPeta($petaId);
    $features = [];
    
    foreach ($fasilitas as $fas) {
        $features[] = [
            'type' => 'Feature',
            'properties' => [
                'id' => $fas['id'],
                'nama' => $fas['nama'],
                'jenis' => $fas['jenis'],
                'alamat' => $fas['alamat'],
                'keterangan' => $fas['keterangan'],
                'ikon' => $fas['ikon'],
                'warna' => $fas['warna']
            ],
            'geometry' => [
                'type' => 'Point',
                'coordinates' => [(float)$fas['lng'], (float)$fas['lat']]
            ]
        ];
    }
    
    return [
        'type' => 'FeatureCollection',
        'features' => $features
    ];
}

/**
 * Tambah fasilitas baru
 * 
 * @param int $petaId - ID peta
 * @param array $data - ['nama', 'jenis', 'lat', 'lng', 'alamat', 'keterangan']
 * @return array - Status
 */
function addFasilitas($petaId, $data) {
    try {
        // Validasi data wajib
        if (empty($data['nama']) || empty($data['jenis']) || 
            !isset($data['lat']) || !isset($data['lng'])) {
            return ['success' => false, 'message' => 'Data tidak lengkap'];
        }
        
        // Buat WKT POINT dari koordinat
        // Format: POINT(longitude latitude)
        $point = "POINT({$data['lng']} {$data['lat']})";
        
        $stmt = db()->prepare("
            INSERT INTO fasilitas_desa 
                (peta_id, nama, jenis, koordinat, alamat, keterangan, ikon, warna)
            VALUES 
                (?, ?, ?, ST_PointFromText(?), ?, ?, ?, ?)
        ");
        
        $stmt->execute([
            $petaId,
            $data['nama'],
            $data['jenis'],
            $point,
            $data['alamat'] ?? '',
            $data['keterangan'] ?? '',
            $data['ikon'] ?? 'fa-map-marker',
            $data['warna'] ?? '#3388ff'
        ]);
        
        return [
            'success' => true,
            'fasilitas_id' => db()->lastInsertId(),
            'message' => 'Fasilitas berhasil ditambahkan'
        ];
        
    } catch (Exception $e) {
        return [
            'success' => false, 
            'message' => 'Gagal menambah fasilitas: ' . $e->getMessage()
        ];
    }
}

/**
 * Update fasilitas
 * 
 * @param int $fasilitasId - ID fasilitas
 * @param int $petaId - ID peta (verifikasi)
 * @param array $data - Data yang mau diupdate
 * @return array - Status
 */
function updateFasilitas($fasilitasId, $petaId, $data) {
    try {
        $fields = [];
        $params = [];
        
        // Update nama
        if (isset($data['nama'])) {
            $fields[] = "nama = ?";
            $params[] = $data['nama'];
        }
        
        // Update jenis
        if (isset($data['jenis'])) {
            $fields[] = "jenis = ?";
            $params[] = $data['jenis'];
        }
        
        // Update koordinat (jika keduanya ada)
        if (isset($data['lat']) && isset($data['lng'])) {
            $fields[] = "koordinat = ST_PointFromText(?)";
            $params[] = "POINT({$data['lng']} {$data['lat']})";
        }
        
        // Update alamat
        if (isset($data['alamat'])) {
            $fields[] = "alamat = ?";
            $params[] = $data['alamat'];
        }
        
        // Update keterangan
        if (isset($data['keterangan'])) {
            $fields[] = "keterangan = ?";
            $params[] = $data['keterangan'];
        }
        
        if (empty($fields)) {
            return ['success' => false, 'message' => 'Tidak ada data yang diupdate'];
        }
        
        $params[] = $fasilitasId;
        $params[] = $petaId;
        
        $sql = "UPDATE fasilitas_desa SET " . implode(', ', $fields) . 
               " WHERE id = ? AND peta_id = ?";
        
        $stmt = db()->prepare($sql);
        $stmt->execute($params);
        
        return ['success' => true, 'message' => 'Fasilitas berhasil diupdate'];
        
    } catch (Exception $e) {
        return ['success' => false, 'message' => 'Gagal update fasilitas'];
    }
}

/**
 * Hapus fasilitas
 * 
 * @param int $fasilitasId - ID fasilitas
 * @param int $petaId - ID peta (verifikasi)
 * @return array - Status
 */
function deleteFasilitas($fasilitasId, $petaId) {
    try {
        $stmt = db()->prepare("DELETE FROM fasilitas_desa WHERE id = ? AND peta_id = ?");
        $stmt->execute([$fasilitasId, $petaId]);
        
        return ['success' => true, 'message' => 'Fasilitas berhasil dihapus'];
        
    } catch (Exception $e) {
        return ['success' => false, 'message' => 'Gagal menghapus fasilitas'];
    }
}

/**
 * Hitung jumlah fasilitas per jenis
 * 
 * @param int $petaId - ID peta
 * @return array - ['pendidikan' => 5, 'kesehatan' => 3, ...]
 */
function getFasilitasCountByType($petaId) {
    try {
        $stmt = db()->prepare("
            SELECT jenis, COUNT(*) as jumlah 
            FROM fasilitas_desa 
            WHERE peta_id = ? 
            GROUP BY jenis
        ");
        $stmt->execute([$petaId]);
        
        $counts = [];
        while ($row = $stmt->fetch()) {
            $counts[$row['jenis']] = $row['jumlah'];
        }
        return $counts;
        
    } catch (Exception $e) {
        return [];
    }
}
```

### Daftar Fungsi Fasilitas

| Fungsi | Operasi | Return |
|--------|---------|--------|
| `getFasilitasByPeta()` | READ | array fasilitas |
| `getFasilitasGeoJSON()` | READ | GeoJSON FeatureCollection |
| `addFasilitas()` | CREATE | array status |
| `updateFasilitas()` | UPDATE | array status |
| `deleteFasilitas()` | DELETE | array status |
| `getFasilitasCountByType()` | COUNT | array jumlah per jenis |

---

## 📚 Teori: Konversi Koordinat

### Dari/Ke Database

```php
// SIMPAN: PHP → MySQL
$lat = -6.2;
$lng = 106.8;
$wkt = "POINT($lng $lat)";  // POINT(106.8 -6.2)

// Query
ST_PointFromText('POINT(106.8 -6.2)')

// ---

// BACA: MySQL → PHP
SELECT ST_X(koordinat) as lng, ST_Y(koordinat) as lat FROM fasilitas
// Hasil: lng = 106.8, lat = -6.2
```

### Dari/Ke GeoJSON (untuk Leaflet)

```php
// PHP → GeoJSON
'coordinates' => [$fas['lng'], $fas['lat']]  // [106.8, -6.2]

// GeoJSON → PHP (dari Leaflet)
$coords = $geojson['geometry']['coordinates'];
$lng = $coords[0];  // 106.8
$lat = $coords[1];  // -6.2
```

---

## 2. Test Fungsi Fasilitas

Update file `test.php`:

```php
<?php
/**
 * Test Spatial Functions - Fasilitas
 */

require_once 'config/database.php';
require_once 'lib/spatial.php';

echo "<h2>🧪 Test CRUD Fasilitas</h2>";

$userId = 1;

// Buat peta dulu
$result = createPeta($userId, 'Test Peta Fasilitas', 'Untuk test');
$petaId = $result['peta_id'];
echo "<p>✅ Peta dibuat dengan ID: $petaId</p>";

// ===== TEST 1: Add Fasilitas =====
echo "<h3>Test 1: addFasilitas()</h3>";

$testFasilitas = [
    ['nama' => 'SDN 01 Test', 'jenis' => 'pendidikan', 'lat' => -6.2, 'lng' => 106.8],
    ['nama' => 'Puskesmas Test', 'jenis' => 'kesehatan', 'lat' => -6.21, 'lng' => 106.81],
    ['nama' => 'Masjid Al-Ikhlas', 'jenis' => 'ibadah', 'lat' => -6.22, 'lng' => 106.82],
];

foreach ($testFasilitas as $fas) {
    $result = addFasilitas($petaId, $fas);
    if ($result['success']) {
        echo "<p style='color: green;'>✅ {$fas['nama']} - ID: {$result['fasilitas_id']}</p>";
    } else {
        echo "<p style='color: red;'>❌ {$result['message']}</p>";
    }
}

// ===== TEST 2: Get Fasilitas =====
echo "<h3>Test 2: getFasilitasByPeta()</h3>";
$fasilitas = getFasilitasByPeta($petaId);
echo "<p>Total: <strong>" . count($fasilitas) . "</strong> fasilitas</p>";

echo "<table border='1' cellpadding='10'>";
echo "<tr><th>ID</th><th>Nama</th><th>Jenis</th><th>Koordinat</th></tr>";
foreach ($fasilitas as $fas) {
    echo "<tr>";
    echo "<td>{$fas['id']}</td>";
    echo "<td>{$fas['nama']}</td>";
    echo "<td>{$fas['jenis']}</td>";
    echo "<td>{$fas['lat']}, {$fas['lng']}</td>";
    echo "</tr>";
}
echo "</table>";

// ===== TEST 3: Get GeoJSON =====
echo "<h3>Test 3: getFasilitasGeoJSON()</h3>";
$geojson = getFasilitasGeoJSON($petaId);
echo "<pre style='background: #f5f5f5; padding: 1rem; max-height: 200px; overflow: auto;'>";
echo json_encode($geojson, JSON_PRETTY_PRINT);
echo "</pre>";

// ===== TEST 4: Count by Type =====
echo "<h3>Test 4: getFasilitasCountByType()</h3>";
$counts = getFasilitasCountByType($petaId);
echo "<table border='1' cellpadding='10'>";
echo "<tr><th>Jenis</th><th>Jumlah</th></tr>";
foreach ($counts as $jenis => $jumlah) {
    echo "<tr><td>$jenis</td><td><strong>$jumlah</strong></td></tr>";
}
echo "</table>";

// ===== CLEANUP =====
echo "<h3>Cleanup</h3>";
deletePeta($petaId, $userId);
echo "<p>✅ Peta test dihapus</p>";

echo "<hr>";
echo "<p style='color: green;'>✅ Semua test CRUD Fasilitas selesai!</p>";
```

Jalankan: `http://localhost/pemetaan-desa/test.php`

---

## 📁 Struktur Akhir Hari 6

```
📁 pemetaan-desa/
├── 📁 lib/
│   ├── 📄 auth.php
│   ├── 📄 helpers.php
│   └── 📄 spatial.php    ← UPDATED (+ fasilitas)
└── 📄 test.php           ← UPDATED
```

---

**← [Hari 5: CRUD Peta](hari5.md) | [Hari 7: CSS & Layout](hari7.md) →**
