-- Database schema for BellOn

CREATE TABLE `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `name` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `jurusan` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `kode_jurusan` VARCHAR(20) NOT NULL UNIQUE,
  `nama_jurusan` VARCHAR(100) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `kelas` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `jurusan_id` INT NOT NULL,
  `nama_kelas` VARCHAR(50) NOT NULL,
  `tingkat` VARCHAR(50) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`jurusan_id`) REFERENCES `jurusan`(`id`)
) ENGINE=InnoDB;

CREATE TABLE `users` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `role_id` INT NOT NULL,
  `kelas_id` INT NULL,
  `failed_login_attempts` INT DEFAULT 0,
  `lockout_until` DATETIME NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`role_id`) REFERENCES `roles`(`id`),
  FOREIGN KEY (`kelas_id`) REFERENCES `kelas`(`id`)
) ENGINE=InnoDB;

CREATE TABLE `qr_codes` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `qr_string` VARCHAR(100) NOT NULL UNIQUE,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `radius` INT DEFAULT 20,
  `room_id` INT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `attendance_logs` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `name_users` BIGINT NOT NULL,
  `status` ENUM('Hadir', 'Terlambat', 'Izin', 'Sakit', 'Dinas', 'Alpha') NOT NULL,
  `scan_time` DATETIME NOT NULL,
  `latitude` DECIMAL(10,8) NOT NULL,
  `longitude` DECIMAL(11,8) NOT NULL,
  `gps_accuracy` DECIMAL(5,2) NOT NULL,
  `device_info` VARCHAR(255),
  `browser_info` VARCHAR(100),
  `user_agent` TEXT,
  `ip_address` VARCHAR(45),
  `room_id` INT NULL,
  FOREIGN KEY (`name_users`) REFERENCES `users`(`id`),
  INDEX (`scan_time`),
  INDEX (`status`)
) ENGINE=InnoDB;
 
CREATE TABLE `leave_requests` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `name_users` BIGINT NOT NULL,
  `category` ENUM('Izin', 'Sakit', 'Dinas') NOT NULL,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `reason` TEXT NOT NULL,
  `proof_image` VARCHAR(255) NOT NULL,
  `approval_status` ENUM('Pending', 'Approved', 'Rejected') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`name_users`) REFERENCES `users`(`id`)
) ENGINE=InnoDB;
 
CREATE TABLE `audit_logs` (
  `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
  `name_users` BIGINT NULL,
  `action` VARCHAR(100) NOT NULL,
  `ip_address` VARCHAR(45),
  `user_agent` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`name_users`) REFERENCES `users`(`id`)
) ENGINE=InnoDB;

-- Initial Seed
INSERT INTO `roles` (`slug`, `name`) VALUES 
('super_admin', 'Super Admin'), ('admin', 'Admin'), ('operator', 'Operator'), 
('petugas_absen', 'Petugas Absen'), ('wali_kelas', 'Wali Kelas'), 
('guru', 'Guru'), ('karyawan', 'Karyawan'), ('siswa', 'Siswa');

-- Password for admin is 'password' (password_hash equivalent)
INSERT INTO `users` (`username`, `password_hash`, `name`, `role_id`) VALUES 
('admin123', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator Utama', 1);