-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Nov 21, 2026 at 06:12 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `absenkerja_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `attendances`
--

CREATE TABLE `attendances` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(120) NOT NULL,
  `employee_nip` varchar(50) NOT NULL,
  `type` enum('MASUK','KELUAR','KEMBALI','PULANG') NOT NULL,
  `date_str` varchar(20) NOT NULL,
  `time_str` varchar(10) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `distance_meters` int(11) NOT NULL,
  `is_within_radius` tinyint(1) NOT NULL DEFAULT 1,
  `photo_path` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` varchar(30) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `attendances`
--

INSERT INTO `attendances` (`id`, `employee_id`, `employee_name`, `employee_nip`, `type`, `date_str`, `time_str`, `latitude`, `longitude`, `distance_meters`, `is_within_radius`, `photo_path`, `notes`, `status`, `created_at`) VALUES
(4, 3, 'Rizky Pratama', '19950412001', 'MASUK', '2026-11-21', '20:30', 5.2112287, 97.0656033, 304, 1, 'uploads/presensi/1795267801_c00d19f9-b9ea-4ff3-a108-e04f7ce1fa6e6694943738480852822.jpg', '', 'TERLAMBAT', '2026-11-21 13:30:01'),
(5, 3, 'Rizky Pratama', '19950412001', 'KELUAR', '2026-11-21', '21:34', 5.2112367, 97.0656007, 304, 1, 'uploads/presensi/1795271651_cc1cb0ee-42af-47d3-8439-334f9ea408067085225492966624672.jpg', 'sedang tugas diluar', 'TERCATAT', '2026-11-21 14:34:11'),
(6, 3, 'Rizky Pratama', '19950412001', 'KELUAR', '2026-11-21', '23:25', 5.2112391, 97.0656064, 304, 1, 'uploads/presensi/1795278303_178875d1-72b5-4f64-b0a8-ea0b7656dbd43651655063768717013.jpg', 'sedang diluar tugas lapangan', 'TERCATAT', '2026-11-21 16:25:03'),
(7, 3, 'Rizky Pratama', '19950412001', 'PULANG', '2026-11-21', '23:26', 5.2112404, 97.065605, 304, 1, 'uploads/presensi/1795278399_f2a2724d-6344-42d4-926d-7f6dc5ce2a242121180257190082134.jpg', '', 'TERCATAT', '2026-11-21 16:26:39');

-- --------------------------------------------------------

--
-- Table structure for table `leave_requests`
--

CREATE TABLE `leave_requests` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `employee_name` varchar(120) NOT NULL,
  `employee_nip` varchar(50) NOT NULL,
  `category` enum('CUTI','IZIN','SAKIT','LEMBUR','TUGAS_LUAR') NOT NULL,
  `start_date` varchar(20) NOT NULL,
  `end_date` varchar(20) NOT NULL,
  `reason` text NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `attachment_name` varchar(180) DEFAULT NULL,
  `attachment_mime` varchar(120) DEFAULT NULL,
  `status` enum('MENUNGGU','DISETUJUI','DITOLAK') NOT NULL DEFAULT 'MENUNGGU',
  `admin_note` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` datetime DEFAULT NULL,
  `deleted_by_role` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leave_requests`
--

INSERT INTO `leave_requests` (`id`, `employee_id`, `employee_name`, `employee_nip`, `category`, `start_date`, `end_date`, `reason`, `attachment_path`, `attachment_name`, `attachment_mime`, `status`, `admin_note`, `created_at`, `deleted_at`, `deleted_by_role`) VALUES
(3, 3, 'Rizky Pratama', '19950412001', 'IZIN', '2026-11-21', '2026-11-21', '', 'uploads/dokumen/1795267851_LAPORAN_kelompok_1.docx', 'LAPORAN kelompok 1.docx', 'application/octet-stream', 'DITOLAK', 'Ditolak Admin', '2026-11-21 13:30:51', NULL, NULL),
(4, 3, 'Rizky Pratama', '19950412001', 'IZIN', '2026-11-21', '2026-11-21', '', 'uploads/dokumen/1795271466_Armayani_230180011_KRS.pdf', 'Armayani_230180011_KRS.pdf', 'application/octet-stream', 'DISETUJUI', 'Disetujui Admin', '2026-11-21 14:31:06', NULL, NULL),
(5, 3, 'Rizky Pratama', '19950412001', 'SAKIT', '2026-11-21', '2026-11-22', 'izin sakit', 'uploads/dokumen/1795271709_230180011_Armayani_Uts_Keamanan_Informasi.pdf', '230180011_Armayani_Uts Keamanan Informasi.pdf', 'application/octet-stream', 'DITOLAK', 'Ditolak Admin', '2026-11-21 14:35:09', NULL, NULL),
(6, 3, 'Rizky Pratama', '19950412001', 'SAKIT', '2026-11-21', '2026-11-22', 'sedang sakit, mohon izin', 'uploads/dokumen/1795276139_Armayani_230180011_KRS.pdf', 'Armayani_230180011_KRS.pdf', 'application/octet-stream', 'DITOLAK', 'Diubah menjadi Ditolak Admin', '2026-11-21 15:48:59', NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `target_user_id` int(11) NOT NULL DEFAULT 0,
  `target_role` enum('ADMIN','PEGAWAI') NOT NULL,
  `title` varchar(150) NOT NULL,
  `message` text NOT NULL,
  `category` varchar(40) NOT NULL DEFAULT 'SISTEM',
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `target_user_id`, `target_role`, `title`, `message`, `category`, `is_read`, `created_at`) VALUES
(1, 0, 'ADMIN', 'Pengajuan IZIN Baru', 'Rizky Pratama mengajukan IZIN (2026-11-19).', 'PENGAJUAN', 0, '2026-11-19 07:02:14'),
(2, 2, 'PEGAWAI', 'Pengajuan IZIN DITOLAK', 'Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-19 07:04:56'),
(3, 2, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Diubah menjadi Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-19 07:52:45'),
(4, 2, 'PEGAWAI', 'Pengajuan IZIN MENUNGGU', 'Pengajuan Anda telah MENUNGGU.', 'PERSETUJUAN', 0, '2026-11-19 07:52:47'),
(5, 0, 'ADMIN', 'Presensi KELUAR - Rizky Pratama', 'Rizky Pratama absen KELUAR pukul 15:01 (19540m).', 'PRESENSI', 0, '2026-11-19 08:01:05'),
(6, 2, 'PEGAWAI', 'Pengajuan IZIN DITOLAK', 'Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-19 08:22:50'),
(7, 2, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Diubah menjadi Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-19 08:22:52'),
(8, 2, 'PEGAWAI', 'Pengajuan IZIN MENUNGGU', 'Pengajuan Anda telah MENUNGGU.', 'PERSETUJUAN', 0, '2026-11-19 08:22:59'),
(9, 2, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-19 08:23:08'),
(10, 2, 'PEGAWAI', 'Pengajuan IZIN MENUNGGU', 'Pengajuan Anda telah MENUNGGU.', 'PERSETUJUAN', 0, '2026-11-19 08:23:57'),
(11, 2, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-19 08:24:02'),
(12, 2, 'PEGAWAI', 'Pengajuan IZIN DITOLAK', 'Diubah menjadi Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-19 08:24:20'),
(13, 2, 'PEGAWAI', 'Pengajuan IZIN MENUNGGU', 'Pengajuan Anda telah MENUNGGU.', 'PERSETUJUAN', 0, '2026-11-19 08:24:27'),
(14, 2, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-19 09:02:45'),
(15, 0, 'ADMIN', 'Presensi MASUK - Rizky Pratama', 'Rizky Pratama absen MASUK pukul 19:50 (303m).', 'PRESENSI', 0, '2026-11-21 12:50:27'),
(16, 0, 'ADMIN', 'Presensi MASUK - Rizky Pratama', 'Rizky Pratama absen MASUK pukul 19:50 (303m).', 'PRESENSI', 0, '2026-11-21 12:50:57'),
(17, 0, 'ADMIN', 'Pengajuan IZIN Baru', 'Rizky Pratama mengajukan IZIN (2026-11-21).', 'PENGAJUAN', 0, '2026-11-21 12:56:19'),
(18, 2, 'PEGAWAI', 'Pengajuan IZIN DITOLAK', 'Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-21 13:00:00'),
(19, 0, 'ADMIN', 'Presensi MASUK - Rizky Pratama', 'Rizky Pratama absen MASUK pukul 20:30 (304m).', 'PRESENSI', 0, '2026-11-21 13:30:01'),
(20, 0, 'ADMIN', 'Pengajuan IZIN Baru', 'Rizky Pratama mengajukan IZIN (2026-11-21).', 'PENGAJUAN', 0, '2026-11-21 13:30:51'),
(21, 3, 'PEGAWAI', 'Pengajuan IZIN DITOLAK', 'Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-21 13:33:49'),
(22, 0, 'ADMIN', 'Pengajuan IZIN Baru', 'Rizky Pratama mengajukan IZIN (2026-11-21).', 'PENGAJUAN', 0, '2026-11-21 14:31:06'),
(23, 3, 'PEGAWAI', 'Pengajuan IZIN DISETUJUI', 'Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-21 14:31:59'),
(24, 0, 'ADMIN', 'Presensi KELUAR - Rizky Pratama', 'Rizky Pratama absen KELUAR pukul 21:34 WIB (TERCATAT, 304m).', 'PRESENSI', 0, '2026-11-21 14:34:11'),
(25, 3, 'PEGAWAI', 'Presensi KELUAR Tercatat', 'Presensi KELUAR pukul 21:34 WIB berstatus TERCATAT (304m).', 'PRESENSI', 0, '2026-11-21 14:34:11'),
(26, 0, 'ADMIN', 'Pengajuan SAKIT Baru', 'Rizky Pratama mengajukan SAKIT (2026-11-21).', 'PENGAJUAN', 0, '2026-11-21 14:35:09'),
(27, 3, 'PEGAWAI', 'Pengajuan SAKIT DITOLAK', 'Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-21 14:37:40'),
(28, 0, 'ADMIN', 'Pengajuan SAKIT Baru', 'Rizky Pratama mengajukan SAKIT (2026-11-21).', 'PENGAJUAN', 0, '2026-11-21 15:48:59'),
(29, 3, 'PEGAWAI', 'Pengajuan SAKIT DISETUJUI', 'Disetujui Admin', 'PERSETUJUAN', 0, '2026-11-21 15:50:08'),
(30, 3, 'PEGAWAI', 'Pengajuan SAKIT DITOLAK', 'Diubah menjadi Ditolak Admin', 'PERSETUJUAN', 0, '2026-11-21 15:50:27'),
(31, 0, 'ADMIN', 'Presensi KELUAR - Rizky Pratama', 'Rizky Pratama absen KELUAR pukul 23:25 WIB (TERCATAT, 304m).', 'PRESENSI', 0, '2026-11-21 16:25:03'),
(32, 3, 'PEGAWAI', 'Presensi KELUAR Tercatat', 'Presensi KELUAR pukul 23:25 WIB berstatus TERCATAT (304m).', 'PRESENSI', 0, '2026-11-21 16:25:03'),
(33, 0, 'ADMIN', 'Presensi PULANG - Rizky Pratama', 'Rizky Pratama absen PULANG pukul 23:26 WIB (TERCATAT, 304m).', 'PRESENSI', 0, '2026-11-21 16:26:39'),
(34, 3, 'PEGAWAI', 'Presensi PULANG Tercatat', 'Presensi PULANG pukul 23:26 WIB berstatus TERCATAT (304m).', 'PRESENSI', 0, '2026-11-21 16:26:39');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `nip` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(120) NOT NULL,
  `role` enum('ADMIN','PEGAWAI') NOT NULL DEFAULT 'PEGAWAI',
  `position` varchar(100) DEFAULT '',
  `division` varchar(100) DEFAULT '',
  `phone` varchar(30) DEFAULT '',
  `photo_path` varchar(255) DEFAULT NULL,
  `shift_name` varchar(80) DEFAULT 'Reguler (08:00 - 17:00)',
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `nip`, `username`, `password_hash`, `full_name`, `role`, `position`, `division`, `phone`, `photo_path`, `shift_name`, `is_active`, `created_at`) VALUES
(1, 'PLN-ADM-001', 'admin', '$2y$10$ZP9Bnq5uXfA2.lZd/CUb/uInLkJFJZ..cdUqr9ayHYSl0MgCPEJSW', 'Admin ULP Gandapura', 'ADMIN', 'Supervisor SDM & Operasi', 'ULP Gandapura', '081167001001', 'uploads/profil/1795072948_1000034475.jpg', 'Reguler (08:00 - 17:00)', 1, '2026-11-19 06:15:37'),
(3, '19950412001', 'rizky', '$2y$10$nro/EuFxu9KC/v5AO7cyjO/MekvaIZ1ZOWVXYGnRZKkOUyzw1At5.', 'Rizky Pratama', 'PEGAWAI', 'Teknisi Jaringan Distribusi', 'Teknik & Pemeliharaan', '082277112233', NULL, 'Reguler (08:00 - 17:00)', 1, '2026-11-21 13:26:43');

-- --------------------------------------------------------

--
-- Table structure for table `work_settings`
--

CREATE TABLE `work_settings` (
  `id` int(11) NOT NULL DEFAULT 1,
  `office_name` varchar(120) NOT NULL DEFAULT 'PLN ULP Gandapura',
  `office_lat` double NOT NULL DEFAULT 5.2118,
  `office_lng` double NOT NULL DEFAULT 96.8856,
  `radius_meters` int(11) NOT NULL DEFAULT 5000,
  `work_start_time` varchar(12) NOT NULL DEFAULT '08:00 AM',
  `break_out_time` varchar(12) NOT NULL DEFAULT '12:00 PM',
  `break_in_time` varchar(12) NOT NULL DEFAULT '01:00 PM',
  `work_end_time` varchar(12) NOT NULL DEFAULT '05:00 PM',
  `late_tolerance_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `late_tolerance_minutes` int(11) NOT NULL DEFAULT 5,
  `reminder_minutes` int(11) NOT NULL DEFAULT 15
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `work_settings`
--

INSERT INTO `work_settings` (`id`, `office_name`, `office_lat`, `office_lng`, `radius_meters`, `work_start_time`, `break_out_time`, `break_in_time`, `work_end_time`, `late_tolerance_enabled`, `late_tolerance_minutes`, `reminder_minutes`) VALUES
(1, 'PLN ULP Gandapura', 5.2120301361155, 97.0682306, 3000, '08:00 AM', '12:00 PM', '01:00 PM', '05:00 PM', 0, 5, 15);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `attendances`
--
ALTER TABLE `attendances`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `nip` (`nip`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `work_settings`
--
ALTER TABLE `work_settings`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `attendances`
--
ALTER TABLE `attendances`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `leave_requests`
--
ALTER TABLE `leave_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `attendances`
--
ALTER TABLE `attendances`
  ADD CONSTRAINT `attendances_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD CONSTRAINT `leave_requests_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
