# SQL References & Troubleshooting

## SQL Queries untuk Troubleshooting

### Lihat Semua Database
```sql
SHOW DATABASES;
```

### Lihat Struktur Tabel
```sql
DESCRIBE pegawai;
DESCRIBE jabatan_fungsional;
DESCRIBE pegawai_files;
DESCRIBE users;
```

### Reset Admin Password
```sql
UPDATE users SET password = '$2y$12$BQjE9x5vkNTfhGhCjU.dWOJj3yXm3uXPwJPB7wYlJC9Q3q7OU8Oyy' 
WHERE email = 'admin@sukabumi.gov.id';
-- Password: admin123456 (hashed dengan bcrypt)
```

### Update Admin Email
```sql
UPDATE users SET email = 'new-admin@sukabumi.gov.id' 
WHERE email = 'admin@sukabumi.gov.id';
```

### Lihat Semua Pegawai
```sql
SELECT id, nip, nama_lengkap, email, status_validasi, created_at 
FROM pegawai 
ORDER BY created_at DESC;
```

### Lihat Pegawai Pending
```sql
SELECT id, nip, nama_lengkap, email, created_at 
FROM pegawai 
WHERE status_validasi = 'pending' 
ORDER BY created_at DESC;
```

### Lihat Pegawai Approved
```sql
SELECT id, nip, nama_lengkap, jabatan_fungsional_id, created_at 
FROM pegawai 
WHERE status_validasi = 'disetujui' 
ORDER BY created_at DESC;
```

### Lihat Pegawai Rejected
```sql
SELECT id, nip, nama_lengkap, catatan_penolakan, created_at 
FROM pegawai 
WHERE status_validasi = 'ditolak' 
ORDER BY created_at DESC;
```

### Statistik Pegawai per Jabatan
```sql
SELECT 
  jf.id,
  jf.nama_jabatan, 
  jf.kategori,
  COUNT(p.id) as jumlah_pegawai
FROM jabatan_fungsional jf
LEFT JOIN pegawai p ON jf.id = p.jabatan_fungsional_id
GROUP BY jf.id, jf.nama_jabatan, jf.kategori
ORDER BY jumlah_pegawai DESC;
```

### Statistik Status Pegawai
```sql
SELECT 
  status_validasi,
  COUNT(*) as jumlah
FROM pegawai
GROUP BY status_validasi;
```

### Pegawai per Unit Kerja
```sql
SELECT 
  unit_kerja,
  COUNT(*) as jumlah_pegawai,
  SUM(CASE WHEN status_validasi = 'disetujui' THEN 1 ELSE 0 END) as approved,
  SUM(CASE WHEN status_validasi = 'pending' THEN 1 ELSE 0 END) as pending,
  SUM(CASE WHEN status_validasi = 'ditolak' THEN 1 ELSE 0 END) as rejected
FROM pegawai
GROUP BY unit_kerja;
```

### Lihat File Upload Pegawai
```sql
SELECT 
  p.nip,
  p.nama_lengkap,
  pf.nama_file,
  pf.tipe_file,
  pf.path,
  pf.created_at
FROM pegawai_files pf
JOIN pegawai p ON pf.pegawai_id = p.id
ORDER BY pf.created_at DESC;
```

### Hapus Pegawai Berdasarkan Status
```sql
-- DELETE PENDING PEGAWAI (Hati-hati!)
DELETE FROM pegawai 
WHERE status_validasi = 'ditolak' 
AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
```

### Backup Database ke SQL File
```bash
mysqldump -u root -p skripsi_pegawai > backup_2025_05_12.sql
```

### Restore Database dari SQL File
```bash
mysql -u root -p skripsi_pegawai < backup_2025_05_12.sql
```

### Export Data Pegawai ke CSV (via Laravel)
```bash
php artisan tinker
>>> $pegawai = \App\Models\Pegawai::all();
>>> \Illuminate\Support\Facades\File::put('pegawai_export.csv', (new \Maatwebsite\Excel\Excel)->raw($pegawai)->download('csv'));
```

## Tips & Tricks

### 1. Clear All Cache
```bash
php artisan cache:clear
php artisan config:clear
php artisan view:clear
php artisan route:clear
php artisan optimize:clear
```

### 2. Check Database Connection
```bash
php artisan tinker
>>> \DB::connection()->getPdo();
```

### 3. Rebuild Storage Link
```bash
php artisan storage:link --force
```

### 4. Reset database (Fresh)
```bash
php artisan migrate:fresh --seed
```

### 5. Create New Admin User
```bash
php artisan tinker
>>> \App\Models\User::create([
    'name' => 'New Admin',
    'email' => 'newadmin@example.com',
    'password' => \Illuminate\Support\Facades\Hash::make('password'),
    'role' => 'admin',
    'email_verified_at' => now()
]);
```

### 6. Check File Permissions
```bash
# Linux/Mac
chmod -R 775 storage
chmod -R 775 bootstrap/cache

# Windows (if needed)
icacls "C:\laragon\www\Skripsi-M\storage" /grant Users:F /T
```

### 7. Monitor Laravel Log
```bash
tail -f storage/logs/laravel.log
```

### 8. Generate Sample Data
```bash
php artisan tinker
>>> for($i=1; $i<=50; $i++) {
    \App\Models\Pegawai::create([
        'nip' => '198' . rand(10000000,99999999),
        'nama_lengkap' => 'Pegawai ' . $i,
        'tempat_lahir' => 'Sukabumi',
        'tanggal_lahir' => now()->subYears(rand(25, 55)),
        'jenis_kelamin' => rand(0,1) ? 'Laki-laki' : 'Perempuan',
        'alamat' => 'Jl. Test No. ' . $i,
        'no_hp' => '0812' . rand(10000000,99999999),
        'email' => 'pegawai' . $i . '@test.com',
        'unit_kerja' => 'Dinas Pendidikan',
        'pendidikan_terakhir' => 'S1',
        'jabatan_fungsional_id' => rand(1, 8),
        'tanggal_mulai_jabatan' => now()->subYears(rand(1, 10)),
        'status_validasi' => array_rand(['pending', 'disetujui', 'ditolak'])
    ]);
}
```

## Database Optimization

### Add Indexes
```sql
-- Index untuk pencarian
ALTER TABLE pegawai ADD INDEX idx_nip (nip);
ALTER TABLE pegawai ADD INDEX idx_email (email);
ALTER TABLE pegawai ADD INDEX idx_status (status_validasi);
ALTER TABLE pegawai ADD INDEX idx_jabatan (jabatan_fungsional_id);
ALTER TABLE pegawai ADD INDEX idx_created (created_at);
```

### Check Index Usage
```sql
SELECT * FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_NAME = 'pegawai';
```

## Monitoring

### Check Database Size
```sql
SELECT 
  SUM(ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2)) AS "Database Size (MB)"
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'skripsi_pegawai';
```

### Check Table Sizes
```sql
SELECT 
  TABLE_NAME,
  ROUND(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS TABLE_SIZE_MB
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'skripsi_pegawai'
ORDER BY TABLE_SIZE_MB DESC;
```

## Performance Tips

### 1. Enable Query Log
Edit `.env`:
```env
DB_DEBUG=true
```

### 2. Use Eager Loading
```php
// Bad
$pegawai = Pegawai::all();
foreach($pegawai as $p) {
    echo $p->jabatan->nama_jabatan; // N+1 Query
}

// Good
$pegawai = Pegawai::with('jabatan')->get();
```

### 3. Limit Results
```php
// Instead of
$pegawai = Pegawai::all();

// Use
$pegawai = Pegawai::paginate(15);
$pegawai = Pegawai::limit(100)->get();
```

### 4. Index Frequently Used Columns
```php
// In migration
$table->index('status_validasi');
$table->index('created_at');
```

## Security Best Practices

### 1. Validate All Inputs
```php
$validated = $request->validate([
    'email' => 'required|email|unique:users',
    'password' => 'required|min:8',
]);
```

### 2. Use HTTPS in Production
```env
APP_URL=https://yourdomain.com
```

### 3. Hide Sensitive Data
```php
protected $hidden = ['password', 'remember_token'];
```

### 4. Prevent SQL Injection
```php
// Bad
DB::raw("SELECT * FROM pegawai WHERE status = '$status'");

// Good
Pegawai::where('status_validasi', $status)->get();
```

### 5. Rate Limiting
```php
// In routes
Route::post('/login', [AuthController::class, 'login'])->throttle('60,1');
```

---

**Last Updated:** 12 Mei 2025