83 lines
3.6 KiB
SQL
Executable File
83 lines
3.6 KiB
SQL
Executable File
-- =====================================================
|
|
-- MIGRATION SCRIPT: Pengajuan Izin/Sakit Feature
|
|
-- Run this script on your MySQL database server
|
|
-- =====================================================
|
|
|
|
-- 1. Add phone column to staff_users table (for Guru BK WhatsApp notification)
|
|
ALTER TABLE staff_users
|
|
ADD COLUMN IF NOT EXISTS phone VARCHAR(20) AFTER role;
|
|
|
|
-- Alternative syntax if your MySQL version doesn't support IF NOT EXISTS:
|
|
-- First check if column exists, if not add it
|
|
-- SET @exist := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'staff_users' AND COLUMN_NAME = 'phone');
|
|
-- SET @sql := IF(@exist = 0, 'ALTER TABLE staff_users ADD COLUMN phone VARCHAR(20) AFTER role', 'SELECT "Column already exists"');
|
|
-- PREPARE stmt FROM @sql;
|
|
-- EXECUTE stmt;
|
|
|
|
-- 2. Update attendance status ENUM to include SAKIT and IZIN
|
|
ALTER TABLE attendance
|
|
MODIFY COLUMN status ENUM('PRESENT', 'LATE', 'REGISTRATION', 'ALFA', 'SAKIT', 'IZIN') DEFAULT 'PRESENT';
|
|
|
|
-- 3. Create leave_requests table (Pengajuan Izin/Sakit dari Siswa)
|
|
CREATE TABLE IF NOT EXISTS leave_requests (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
student_id VARCHAR(36) NOT NULL COMMENT 'ID siswa yang mengajukan',
|
|
student_name VARCHAR(255) NOT NULL COMMENT 'Nama siswa',
|
|
student_nis VARCHAR(50) COMMENT 'NIS siswa',
|
|
student_class VARCHAR(100) COMMENT 'Kelas siswa',
|
|
request_type ENUM('SAKIT', 'IZIN') NOT NULL COMMENT 'Jenis pengajuan',
|
|
request_date VARCHAR(20) NOT NULL COMMENT 'Tanggal izin (YYYY-MM-DD)',
|
|
reason TEXT NOT NULL COMMENT 'Alasan pengajuan',
|
|
photo_evidence LONGTEXT COMMENT 'Bukti foto (base64 atau URL)',
|
|
status ENUM('PENDING', 'APPROVED', 'REJECTED') DEFAULT 'PENDING' COMMENT 'Status pengajuan',
|
|
reviewed_by VARCHAR(36) COMMENT 'ID Guru BK yang mereview',
|
|
reviewed_by_name VARCHAR(255) COMMENT 'Nama Guru BK yang mereview',
|
|
reviewed_at TIMESTAMP NULL COMMENT 'Waktu review',
|
|
rejection_reason TEXT COMMENT 'Alasan penolakan (wajib jika status REJECTED)',
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Waktu pengajuan dibuat',
|
|
INDEX idx_student (student_id),
|
|
INDEX idx_status (status),
|
|
INDEX idx_date (request_date),
|
|
INDEX idx_created (created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 4. Add Fonnte Token setting if not exists
|
|
INSERT IGNORE INTO settings (setting_key, setting_value)
|
|
VALUES ('FONNTE_TOKEN', '');
|
|
|
|
-- =====================================================
|
|
-- VERIFICATION QUERIES (Run to check if migration success)
|
|
-- =====================================================
|
|
|
|
-- Check staff_users has phone column:
|
|
-- DESCRIBE staff_users;
|
|
|
|
-- Check leave_requests table exists:
|
|
-- SHOW TABLES LIKE 'leave_requests';
|
|
|
|
-- Check attendance status enum:
|
|
-- SHOW COLUMNS FROM attendance LIKE 'status';
|
|
|
|
-- =====================================================
|
|
-- SAMPLE DATA FOR TESTING (Optional)
|
|
-- =====================================================
|
|
|
|
-- Create a sample Guru BK account with phone (change password hash as needed)
|
|
-- INSERT INTO staff_users (id, username, password, name, role, phone, is_active)
|
|
-- VALUES (
|
|
-- UUID(),
|
|
-- 'gurubk1',
|
|
-- SHA2('password123', 256), -- Password: password123
|
|
-- 'Guru BK Test',
|
|
-- 'GURU_BK',
|
|
-- '628123456789', -- WhatsApp number
|
|
-- TRUE
|
|
-- );
|
|
|
|
-- =====================================================
|
|
-- NOTES:
|
|
-- - Nomor WhatsApp harus format: 628xxxxxxxxxx (tanpa + atau 0)
|
|
-- - Fonnte Token didapat dari dashboard fonnte.com
|
|
-- - Guru BK bisa lebih dari 1, semua akan menerima notifikasi WA
|
|
-- =====================================================
|