-- Database: absensi_rsud
-- RSUD Dr. Albert H. Torey Attendance System

CREATE DATABASE IF NOT EXISTS absensi_rsud;
USE absensi_rsud;

-- Table: users (Multi-role system)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nip VARCHAR(20) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin_hrd', 'kepala_ruangan', 'dokter', 'perawat', 'pegawai_non_medis') NOT NULL,
    unit_kerja VARCHAR(100) NOT NULL,
    jabatan VARCHAR(100) NOT NULL,
    foto_profil VARCHAR(255),
    face_encoding TEXT,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table: shifts
CREATE TABLE shifts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_shift VARCHAR(50) NOT NULL,
    jam_mulai TIME NOT NULL,
    jam_selesai TIME NOT NULL,
    deskripsi TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: jadwal_shift
CREATE TABLE jadwal_shift (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    shift_id INT NOT NULL,
    tanggal DATE NOT NULL,
    status ENUM('scheduled', 'completed', 'missed') DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_date (user_id, tanggal)
);

-- Table: ruangan
CREATE TABLE ruangan (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nama_ruangan VARCHAR(100) NOT NULL,
    kode_ruangan VARCHAR(20) UNIQUE NOT NULL,
    qr_code VARCHAR(255) UNIQUE NOT NULL,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    radius_meter INT DEFAULT 50,
    status ENUM('aktif', 'nonaktif') DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table: absensi
CREATE TABLE absensi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tanggal DATE NOT NULL,
    shift_id INT NOT NULL,
    ruangan_id INT,
    check_in TIME,
    check_out TIME,
    latitude_in DECIMAL(10, 8),
    longitude_in DECIMAL(11, 8),
    latitude_out DECIMAL(10, 8),
    longitude_out DECIMAL(11, 8),
    foto_check_in VARCHAR(255),
    foto_check_out VARCHAR(255),
    qr_code_scanned VARCHAR(255),
    status ENUM('hadir', 'terlambat', 'alpha', 'izin', 'sakit') DEFAULT 'hadir',
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (shift_id) REFERENCES shifts(id) ON DELETE CASCADE,
    FOREIGN KEY (ruangan_id) REFERENCES ruangan(id) ON DELETE SET NULL,
    UNIQUE KEY unique_user_date_shift (user_id, tanggal, shift_id)
);

-- Table: izin_cuti
CREATE TABLE izin_cuti (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    jenis ENUM('cuti_tahunan', 'cuti_sakit', 'izin_darurat', 'izin_khusus') NOT NULL,
    tanggal_mulai DATE NOT NULL,
    tanggal_selesai DATE NOT NULL,
    jumlah_hari INT NOT NULL,
    alasan TEXT NOT NULL,
    dokumen_pendukung VARCHAR(255),
    status ENUM('pending', 'approved_kepala', 'approved_hrd', 'rejected') DEFAULT 'pending',
    approved_by_kepala INT,
    approved_by_hrd INT,
    tanggal_approval_kepala TIMESTAMP NULL,
    tanggal_approval_hrd TIMESTAMP NULL,
    keterangan_approval TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (approved_by_kepala) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (approved_by_hrd) REFERENCES users(id) ON DELETE SET NULL
);

-- Table: notifikasi
CREATE TABLE notifikasi (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    judul VARCHAR(255) NOT NULL,
    pesan TEXT NOT NULL,
    jenis ENUM('shift_reminder', 'late_warning', 'approval_request', 'system') NOT NULL,
    status ENUM('unread', 'read') DEFAULT 'unread',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table: log_aktivitas
CREATE TABLE log_aktivitas (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    aktivitas VARCHAR(255) NOT NULL,
    detail TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert default shifts
INSERT INTO shifts (nama_shift, jam_mulai, jam_selesai, deskripsi) VALUES
('Pagi', '07:00:00', '14:00:00', 'Shift pagi (07:00 - 14:00)'),
('Siang', '14:00:00', '21:00:00', 'Shift siang (14:00 - 21:00)'),
('Malam', '21:00:00', '07:00:00', 'Shift malam (21:00 - 07:00)'),
('On-Call', '00:00:00', '23:59:59', 'Shift on-call 24 jam');

-- Insert default rooms
INSERT INTO ruangan (nama_ruangan, kode_ruangan, qr_code, latitude, longitude) VALUES
('IGD', 'IGD001', 'QR_IGD_001', -7.250445, 112.768845),
('ICU', 'ICU001', 'QR_ICU_001', -7.250445, 112.768845),
('Poli Umum', 'POLI001', 'QR_POLI_001', -7.250445, 112.768845),
('Poli Anak', 'POLI002', 'QR_POLI_002', -7.250445, 112.768845),
('Poli Kandungan', 'POLI003', 'QR_POLI_003', -7.250445, 112.768845),
('Rawat Inap 1', 'RI001', 'QR_RI_001', -7.250445, 112.768845),
('Rawat Inap 2', 'RI002', 'QR_RI_002', -7.250445, 112.768845),
('Farmasi', 'FARM001', 'QR_FARM_001', -7.250445, 112.768845),
('Laboratorium', 'LAB001', 'QR_LAB_001', -7.250445, 112.768845),
('Radiologi', 'RAD001', 'QR_RAD_001', -7.250445, 112.768845);

-- Insert default admin user
INSERT INTO users (nip, nama, email, password, role, unit_kerja, jabatan) VALUES
('ADM001', 'Admin HRD', 'admin@rsud-torey.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin_hrd', 'HRD', 'Administrator HRD');

-- Table: location_settings
CREATE TABLE location_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_name VARCHAR(100) NOT NULL UNIQUE,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    radius_meter INT NOT NULL DEFAULT 500,
    is_active BOOLEAN DEFAULT TRUE,
    description TEXT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Insert default hospital location
INSERT INTO location_settings (setting_name, latitude, longitude, radius_meter, description, created_by) VALUES
('RSUD Dr. Albert H. Torey - Main', -7.250445, 112.768845, 500, 'Lokasi utama rumah sakit dengan radius 500 meter', 1);

-- Create indexes for better performance
CREATE INDEX idx_absensi_user_tanggal ON absensi(user_id, tanggal);
CREATE INDEX idx_absensi_tanggal ON absensi(tanggal);
CREATE INDEX idx_jadwal_shift_user_tanggal ON jadwal_shift(user_id, tanggal);
CREATE INDEX idx_izin_cuti_status ON izin_cuti(status);
CREATE INDEX idx_notifikasi_user_status ON notifikasi(user_id, status);