# Database Structure - Sistem Informasi Desa Cemara

**Platform:** Laravel 11 + MySQL (via XAMPP/Laragon)
**Database Name:** `desa`
**Connection:** `127.0.0.1:3306` root tanpa password

---

## DAFTAR TABEL (14 Tabel)

| No | Nama Tabel | Deskripsi | Singleton | Soft Delete |
|----|-----------|-----------|-----------|-------------|
| 1 | `users` | Akun admin panel (Filament) | - | - |
| 2 | `password_reset_tokens` | Token & OTP reset password | - | - |
| 3 | `sessions` | Sesi login admin | - | - |
| 4 | `homebanners` | Banner slider homepage | - | - |
| 5 | `village_profiles` | Profil desa (single row) | Yes | - |
| 6 | `village_potentials` | Potensi & produk desa | - | - |
| 7 | `news_categories` | Kategori berita/artikel | - | - |
| 8 | `authors` | Penulis berita | - | - |
| 9 | `news` | Berita/artikel desa | - | - |
| 10 | `banners` | Banner berita unggulan | - | - |
| 11 | `dokumen_desas` | Dokumen layanan desa (download) | - | - |
| 12 | `pengaduans` | Pengaduan masyarakat | - | Yes |
| 13 | `contact_messages` | Pesan dari form kontak | - | - |
| 14 | `jenis_surats` | Master jenis surat layanan | - | - |
| 15 | `pengajuan_surats` | Pengajuan surat warga | - | Yes |
| 16 | `cache` | Cache Laravel | - | - |
| 17 | `cache_locks` | Lock cache | - | - |
| 18 | `jobs` | Queue job | - | - |

---

## STRUKTUR KOLOM PER TABEL

### 1. `users`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|------------|
| id | bigint unsigned | PK, Auto Increment | |
| name | varchar(255) | NOT NULL | Nama admin |
| email | varchar(255) | UNIQUE, NOT NULL | Email login |
| is_admin | tinyint(1) | DEFAULT 0 | Flag admin Filament |
| email_verified_at | timestamp | NULLABLE | |
| password | varchar(255) | NOT NULL | Bcrypt |
| remember_token | varchar(100) | NULLABLE | |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 2. `password_reset_tokens`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| email | varchar(255) | PRIMARY KEY | Email admin |
| token | varchar(255) | NOT NULL | Token reset |
| otp | varchar(6) | NULLABLE | Kode OTP 6 digit |
| created_at | timestamp | NULLABLE | |

### 3. `sessions`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | varchar(255) | PRIMARY KEY | Session ID |
| user_id | bigint unsigned | NULLABLE, INDEX | FK ke users |
| ip_address | varchar(45) | NULLABLE | |
| user_agent | text | NULLABLE | |
| payload | longtext | NOT NULL | |
| last_activity | int | INDEX | |

### 4. `homebanners`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| title | varchar(255) | NOT NULL | Judul banner |
| image | varchar(255) | NOT NULL | Path gambar |
| order | int | DEFAULT 0 | Urutan tampil |
| is_active | tinyint(1) | DEFAULT 1 | Status aktif |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 5. `village_profiles`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| visi | text | NULLABLE | Visi desa |
| misi | text | NULLABLE | Misi desa (markdown list) |
| sejarah | text | NULLABLE | Sejarah desa |
| sejarah_image | varchar(255) | NULLABLE | Gambar sejarah |
| sejarah_asal_usul | text | NULLABLE | Asal usul desa |
| logo_kabupaten | varchar(255) | NULLABLE | Logo kabupaten |
| luas_wilayah | varchar(255) | NULLABLE | |
| jumlah_penduduk | varchar(255) | NULLABLE | |
| jumlah_kk | varchar(255) | NULLABLE | |
| jumlah_rt | varchar(255) | NULLABLE | |
| jumlah_dusun | varchar(255) | NULLABLE | |
| batas_utara | varchar(255) | NULLABLE | |
| batas_timur | varchar(255) | NULLABLE | |
| batas_selatan | varchar(255) | NULLABLE | |
| batas_barat | varchar(255) | NULLABLE | |
| kades_name | varchar(255) | NULLABLE | Kepala desa |
| kades_image | varchar(255) | NULLABLE | |
| sekdes_name | varchar(255) | NULLABLE | Sekretaris desa |
| sekdes_image | varchar(255) | NULLABLE | |
| kaur_keuangan_name | varchar(255) | NULLABLE | |
| kaur_keuangan_image | varchar(255) | NULLABLE | |
| kaur_perencanaan_name | varchar(255) | NULLABLE | |
| kaur_perencanaan_image | varchar(255) | NULLABLE | |
| kaur_tu_name | varchar(255) | NULLABLE | |
| kaur_tu_image | varchar(255) | NULLABLE | |
| kasi_pemerintahan_name | varchar(255) | NULLABLE | |
| kasi_pemerintahan_image | varchar(255) | NULLABLE | |
| kasi_kesejahteraan_name | varchar(255) | NULLABLE | |
| kasi_kesejahteraan_image | varchar(255) | NULLABLE | |
| kasi_pelayanan_name | varchar(255) | NULLABLE | |
| kasi_pelayanan_image | varchar(255) | NULLABLE | |
| ketua_bpd_name | varchar(255) | NULLABLE | |
| ketua_bpd_image | varchar(255) | NULLABLE | |
| kadus_1_name | varchar(255) | NULLABLE | Kadus 1 |
| kadus_1_image | varchar(255) | NULLABLE | |
| kadus_2_name | varchar(255) | NULLABLE | Kadus 2 |
| kadus_2_image | varchar(255) | NULLABLE | |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 6. `village_potentials`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| title | varchar(255) | NOT NULL | Nama potensi |
| image | varchar(255) | NULLABLE | Gambar |
| description | text | NULLABLE | Deskripsi |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 7. `news_categories`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| title | varchar(255) | NOT NULL | Nama kategori |
| slug | varchar(255) | UNIQUE, NOT NULL | Slug URL |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 8. `authors`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| name | varchar(255) | NOT NULL | Nama penulis |
| username | varchar(255) | UNIQUE, NOT NULL | |
| avatar | varchar(255) | NOT NULL | Path foto |
| bio | longtext | NOT NULL | Biografi |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 9. `news`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| author_id | bigint unsigned | FK → authors.id | Penulis |
| news_category_id | bigint unsigned | FK → news_categories.id | Kategori |
| title | varchar(255) | NOT NULL | Judul berita |
| slug | varchar(255) | UNIQUE, NOT NULL | Slug URL |
| thumbnail | varchar(255) | NOT NULL | Gambar cover |
| content | longtext | NOT NULL | Isi berita |
| is_featured | tinyint(1) | DEFAULT 0 | Berita unggulan |
| views | bigint unsigned | DEFAULT 0 | Counter dilihat |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 10. `banners`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| news_id | bigint unsigned | FK → news.id | Berita terkait |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 11. `dokumen_desas`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| nama | varchar(255) | NOT NULL | Judul dokumen |
| file | varchar(255) | NOT NULL | Path file |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 12. `pengaduans`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| nama_lengkap | varchar(255) | NOT NULL | Nama pelapor |
| nik | text | NOT NULL | NIK (dienkripsi) |
| email | varchar(255) | NULLABLE | |
| telepon | varchar(20) | NOT NULL | |
| kategori | varchar(255) | NOT NULL | Kategori aduan |
| isi_pengaduan | text | NOT NULL | |
| lampiran | varchar(255) | NULLABLE | File (dienkripsi) |
| nomor_tiket | varchar(20) | UNIQUE, INDEX | Format: TKT-XXXXXXXX |
| status | enum('Pending','Diproses','Selesai') | DEFAULT 'Pending', INDEX | |
| created_at | timestamp | INDEX | |
| updated_at | timestamp | NULLABLE | |
| deleted_at | timestamp | NULLABLE | Soft delete |

### 13. `contact_messages`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|-----------|
| id | bigint unsigned | PK, Auto Increment | |
| nama | varchar(255) | NOT NULL | |
| email | varchar(255) | NOT NULL | |
| subjek | varchar(255) | NULLABLE | |
| pesan | text | NOT NULL | |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 14. `jenis_surats`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|------------|
| id | bigint unsigned | PK, Auto Increment | |
| nama | varchar(255) | UNIQUE, NOT NULL | Nama jenis surat (contoh: Pengajuan Surat Keterangan Domisili) |
| created_at | timestamp | NULLABLE | |
| updated_at | timestamp | NULLABLE | |

### 15. `pengajuan_surats`

| Kolom | Tipe | Constraint | Keterangan |
|-------|------|-----------|------------|
| id | bigint unsigned | PK, Auto Increment | |
| nama_lengkap | varchar(255) | NOT NULL | Nama pemohon |
| nik | text | NOT NULL | NIK (dienkripsi) |
| email | varchar(255) | NULLABLE | |
| telepon | varchar(20) | NOT NULL | |
| jenis_surat | varchar(255) | NOT NULL | Nama jenis surat (FK ke jenis_surats.nama) |
| keterangan | text | NOT NULL | Keperluan/keterangan pengajuan |
| lampiran | varchar(255) | NULLABLE | File pendukung |
| nomor_tiket | varchar(20) | UNIQUE, INDEX | Format: TKT-XXXXXXXX |
| status | enum('Pending','Diproses','Selesai') | DEFAULT 'Pending', INDEX | |
| deleted_at | timestamp | NULLABLE | Soft delete |
| created_at | timestamp | INDEX | |
| updated_at | timestamp | NULLABLE | |

---

## RELASI (Foreign Keys)

| FK pada Tabel | Kolom | Referensi | Type |
|--------------|-------|-----------|------|
| news | author_id | authors.id | 1 ── N |
| news | news_category_id | news_categories.id | 1 ── N |
| banners | news_id | news.id | 1 ── 1 |
| sessions | user_id | users.id | 1 ── N |
| pengajuan_surats | jenis_surat | jenis_surats.nama | 1 ── N |

---

## SKEMA SQL LENGKAP

```sql
-- Buat database
CREATE DATABASE IF NOT EXISTS desa CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE desa;

-- 1. users
CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `is_admin` tinyint(1) NOT NULL DEFAULT '0',
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. password_reset_tokens
CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `otp` varchar(6) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. sessions
CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text,
  `payload` longtext NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. homebanners
CREATE TABLE `homebanners` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `image` varchar(255) NOT NULL,
  `order` int NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. village_profiles
CREATE TABLE `village_profiles` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `visi` text,
  `misi` text,
  `sejarah` text,
  `sejarah_image` varchar(255) DEFAULT NULL,
  `sejarah_asal_usul` text,
  `logo_kabupaten` varchar(255) DEFAULT NULL,
  `luas_wilayah` varchar(255) DEFAULT NULL,
  `jumlah_penduduk` varchar(255) DEFAULT NULL,
  `jumlah_kk` varchar(255) DEFAULT NULL,
  `jumlah_rt` varchar(255) DEFAULT NULL,
  `jumlah_dusun` varchar(255) DEFAULT NULL,
  `batas_utara` varchar(255) DEFAULT NULL,
  `batas_timur` varchar(255) DEFAULT NULL,
  `batas_selatan` varchar(255) DEFAULT NULL,
  `batas_barat` varchar(255) DEFAULT NULL,
  `kades_name` varchar(255) DEFAULT NULL,
  `kades_image` varchar(255) DEFAULT NULL,
  `sekdes_name` varchar(255) DEFAULT NULL,
  `sekdes_image` varchar(255) DEFAULT NULL,
  `kaur_keuangan_name` varchar(255) DEFAULT NULL,
  `kaur_keuangan_image` varchar(255) DEFAULT NULL,
  `kaur_perencanaan_name` varchar(255) DEFAULT NULL,
  `kaur_perencanaan_image` varchar(255) DEFAULT NULL,
  `kaur_tu_name` varchar(255) DEFAULT NULL,
  `kaur_tu_image` varchar(255) DEFAULT NULL,
  `kasi_pemerintahan_name` varchar(255) DEFAULT NULL,
  `kasi_pemerintahan_image` varchar(255) DEFAULT NULL,
  `kasi_kesejahteraan_name` varchar(255) DEFAULT NULL,
  `kasi_kesejahteraan_image` varchar(255) DEFAULT NULL,
  `kasi_pelayanan_name` varchar(255) DEFAULT NULL,
  `kasi_pelayanan_image` varchar(255) DEFAULT NULL,
  `ketua_bpd_name` varchar(255) DEFAULT NULL,
  `ketua_bpd_image` varchar(255) DEFAULT NULL,
  `kadus_1_name` varchar(255) DEFAULT NULL,
  `kadus_1_image` varchar(255) DEFAULT NULL,
  `kadus_2_name` varchar(255) DEFAULT NULL,
  `kadus_2_image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. village_potentials
CREATE TABLE `village_potentials` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `description` text,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. news_categories
CREATE TABLE `news_categories` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `news_categories_slug_unique` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. authors
CREATE TABLE `authors` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `avatar` varchar(255) NOT NULL,
  `bio` longtext NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `authors_username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. news
CREATE TABLE `news` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `author_id` bigint unsigned NOT NULL,
  `news_category_id` bigint unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `thumbnail` varchar(255) NOT NULL,
  `content` longtext NOT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT '0',
  `views` bigint unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `news_slug_unique` (`slug`),
  KEY `news_author_id_foreign` (`author_id`),
  KEY `news_news_category_id_foreign` (`news_category_id`),
  CONSTRAINT `news_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `authors` (`id`),
  CONSTRAINT `news_news_category_id_foreign` FOREIGN KEY (`news_category_id`) REFERENCES `news_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. banners
CREATE TABLE `banners` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `news_id` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `banners_news_id_foreign` (`news_id`),
  CONSTRAINT `banners_news_id_foreign` FOREIGN KEY (`news_id`) REFERENCES `news` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. dokumen_desas
CREATE TABLE `dokumen_desas` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `file` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. pengaduans
CREATE TABLE `pengaduans` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama_lengkap` varchar(255) NOT NULL,
  `nik` text NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `telepon` varchar(20) NOT NULL,
  `kategori` varchar(255) NOT NULL,
  `isi_pengaduan` text NOT NULL,
  `lampiran` varchar(255) DEFAULT NULL,
  `nomor_tiket` varchar(20) NOT NULL,
  `status` enum('Pending','Diproses','Selesai') NOT NULL DEFAULT 'Pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pengaduans_nomor_tiket_unique` (`nomor_tiket`),
  KEY `pengaduans_status_index` (`status`),
  KEY `pengaduans_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. jenis_surats
CREATE TABLE `jenis_surats` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `jenis_surats_nama_unique` (`nama`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. pengajuan_surats
CREATE TABLE `pengajuan_surats` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama_lengkap` varchar(255) NOT NULL,
  `nik` text NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `telepon` varchar(20) NOT NULL,
  `jenis_surat` varchar(255) NOT NULL,
  `keterangan` text NOT NULL,
  `lampiran` varchar(255) DEFAULT NULL,
  `nomor_tiket` varchar(20) NOT NULL,
  `status` enum('Pending','Diproses','Selesai') NOT NULL DEFAULT 'Pending',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pengajuan_surats_nomor_tiket_unique` (`nomor_tiket`),
  KEY `pengajuan_surats_status_index` (`status`),
  KEY `pengajuan_surats_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. contact_messages
CREATE TABLE `contact_messages` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `subjek` varchar(255) DEFAULT NULL,
  `pesan` text NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16-18. cache, cache_locks, jobs (built-in Laravel)
CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
```

---

## CARA MENJALANKAN MIGRASI

Project sudah memiliki **semua migration file** di `database/migrations/`. Cukup jalankan:

```bash
# 1. Pastikan MySQL aktif di XAMPP/Laragon
# 2. Buat database 'desa' di phpMyAdmin atau CLI

# 3. Jalankan migrasi (dari folder project)
cd sisteminformasi-desa-laravel-blade-main
php artisan migrate

# 4. Seed data awal
php artisan db:seed

# 5. (Opsional) Seed data spesifik
php artisan db:seed --class=VillageProfileSeeder
php artisan db:seed --class=HomeBannerSeeder
```

---

## DATA AWAL (Seeder)

### User Admin Default
- **Email:** `test@example.com`
- **Password:** `password` (default Laravel factory)
- **Role:** `is_admin = true`

### Profil Desa
Seeder `VillageProfileSeeder` mengisi data desa contoh **"Desa Cemara"** dengan struktur organisasi desa lengkap (Kades, Sekdes, KAUR, KASI, KADUS, dll).

---

## FITUR KHUSUS

### Enkripsi NIK
- `pengaduans.nik` & `pengajuan_surats.nik` → disimpan sebagai `text` (dienkripsi via `Crypt::encrypt()`)

### Lampiran Terenkripsi
- `pengaduans.lampiran` → file dienkripsi sebelum disimpan di `storage/app/lampiran_pengaduan/`
- `pengajuan_surats.lampiran` → file dienkripsi sebelum disimpan di `storage/app/lampiran_pengaduan/`
- Download lampiran didekripsi via `PengaduanController@downloadSecureLampiran`

### Nomor Tiket Otomatis
- Format: `TKT-XXXXXXXX` (8 karakter alfanumerik random)
- Generated di `PengaduanController@store` (pengaduan) & `PengajuanSuratController@store` (pengajuan surat)

### Soft Delete
- Tabel `pengaduans` & `pengajuan_surats` menggunakan `SoftDeletes` trait Laravel
- Data tidak dihapus permanen, hanya di-flag `deleted_at`

### Master Data Jenis Surat
- Tabel `jenis_surats` menyimpan daftar jenis surat yang tersedia untuk dipilih warga
- Data awal di-seed via migration mencakup 13 jenis surat (Domisili, Usaha, Kematian, SKTM, dll.)
- Relasi ke `pengajuan_surats` melalui kolom `jenis_surat` (string lookup ke `jenis_surats.nama`)

### Singleton Profiles
- `village_profiles` hanya memiliki **1 row** (updateOrCreate id=1)
- Tidak perlu multiple records karena hanya profil 1 desa