-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `absensi`
--

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

--
-- Table structure for table `absensi`
--

CREATE TABLE `absensi` (
  `id` int(11) NOT NULL,
  `karyawan_id` int(11) DEFAULT NULL,
  `tanggal` date NOT NULL,
  `waktu_masuk` time DEFAULT NULL,
  `waktu_keluar` time DEFAULT NULL,
  `kode_absen_id` int(11) DEFAULT NULL,
  `status` enum('hadir','sakit','izin','alpa') DEFAULT 'hadir',
  `keterangan` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `absensi`
--

INSERT INTO `absensi` (`id`, `karyawan_id`, `tanggal`, `waktu_masuk`, `waktu_keluar`, `kode_absen_id`, `status`, `keterangan`, `created_at`, `updated_at`) VALUES
(1, 1, '2025-05-20', '04:04:46', NULL, 3, 'hadir', NULL, '2025-05-20 02:04:46', '2025-05-20 02:04:46'),
(2, 2, '2025-05-20', '04:12:14', NULL, 3, 'hadir', NULL, '2025-05-20 02:12:14', '2025-05-20 02:12:14'),
(3, 4, '2025-05-20', '04:18:00', NULL, 3, 'hadir', '', '2025-05-20 02:18:31', '2025-05-20 02:35:53'),
(4, 5, '2025-05-20', '04:42:20', NULL, 5, 'hadir', NULL, '2025-05-20 02:42:20', '2025-05-20 02:42:20'),
(5, 6, '2025-05-20', '06:01:12', NULL, 5, 'hadir', NULL, '2025-05-20 04:01:12', '2025-05-20 04:01:12');

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

--
-- Table structure for table `divisi`
--

CREATE TABLE `divisi` (
  `id` int(11) NOT NULL,
  `nama_divisi` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `divisi`
--

INSERT INTO `divisi` (`id`, `nama_divisi`, `created_at`, `updated_at`) VALUES
(1, 'IT', '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(2, 'HRD', '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(3, 'Finance', '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(4, 'Operations', '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(5, 'Marketing', '2025-05-14 14:36:03', '2025-05-14 14:36:03');

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

--
-- Table structure for table `gaji`
--

CREATE TABLE `gaji` (
  `id` int(11) NOT NULL,
  `karyawan_id` int(11) DEFAULT NULL,
  `bulan` int(11) NOT NULL,
  `tahun` int(11) NOT NULL,
  `gaji_pokok` decimal(12,2) DEFAULT 0.00,
  `gaji_harian` decimal(12,2) DEFAULT 0.00,
  `tunjangan` decimal(12,2) DEFAULT 0.00,
  `potongan_bpjs` decimal(12,2) DEFAULT 0.00,
  `potongan_pajak` decimal(12,2) DEFAULT 0.00,
  `potongan_absensi` decimal(12,2) DEFAULT 0.00,
  `lembur` decimal(12,2) DEFAULT 0.00,
  `total_gaji` decimal(12,2) DEFAULT 0.00,
  `status` enum('draft','approved','paid') DEFAULT 'draft',
  `tanggal_bayar` date DEFAULT NULL,
  `catatan` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `gaji`
--

INSERT INTO `gaji` (`id`, `karyawan_id`, `bulan`, `tahun`, `gaji_pokok`, `gaji_harian`, `tunjangan`, `potongan_bpjs`, `potongan_pajak`, `potongan_absensi`, `lembur`, `total_gaji`, `status`, `tanggal_bayar`, `catatan`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 2025, 3000000.00, 0.00, 1000000.00, 50000.00, 30000.00, 0.00, 0.00, 3920000.00, 'paid', '2025-05-20', 'gaji dibayar ya semangat', '2025-05-20 02:09:13', '2025-05-20 02:09:42'),
(2, 4, 5, 2025, 3000000.00, 0.00, 2000000.00, 120000.00, 100000.00, 0.00, 0.00, 4780000.00, 'approved', '2025-05-20', 'gaji dibayar', '2025-05-20 02:19:53', '2025-05-20 02:19:53');

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

--
-- Table structure for table `jabatan`
--

CREATE TABLE `jabatan` (
  `id` int(11) NOT NULL,
  `nama_jabatan` varchar(100) NOT NULL,
  `divisi_id` int(11) DEFAULT NULL,
  `gaji_pokok` decimal(12,2) DEFAULT 0.00,
  `tunjangan` decimal(12,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `jabatan`
--

INSERT INTO `jabatan` (`id`, `nama_jabatan`, `divisi_id`, `gaji_pokok`, `tunjangan`, `created_at`, `updated_at`) VALUES
(1, 'Direktur', 1, 15000000.00, 5000000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(2, 'Supervisor IT', 1, 10000000.00, 3000000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(3, 'Staff IT', 1, 7000000.00, 1500000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(4, 'Supervisor HRD', 2, 9000000.00, 2500000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(5, 'Staff HRD', 2, 6000000.00, 1200000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(6, 'Admin', 2, 5000000.00, 1000000.00, '2025-05-14 14:36:03', '2025-05-14 14:36:03'),
(7, 'Teknisi', 4, 3.00, 1.00, '2025-05-20 02:39:26', '2025-05-20 04:26:35');

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

--
-- Table structure for table `karyawan`
--

CREATE TABLE `karyawan` (
  `id` int(11) NOT NULL,
  `nik` varchar(20) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `nama` varchar(150) NOT NULL,
  `jabatan_id` int(11) DEFAULT NULL,
  `divisi_id` int(11) DEFAULT NULL,
  `alamat` text DEFAULT NULL,
  `no_telp` varchar(15) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `tanggal_gabung` date DEFAULT NULL,
  `supervisor_id` int(11) DEFAULT NULL,
  `status` enum('aktif','nonaktif') DEFAULT 'aktif',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `karyawan`
--

INSERT INTO `karyawan` (`id`, `nik`, `user_id`, `nama`, `jabatan_id`, `divisi_id`, `alamat`, `no_telp`, `email`, `tanggal_gabung`, `supervisor_id`, `status`, `created_at`, `updated_at`) VALUES
(1, '221', 5, 'Andi', 3, 1, 'asa', '099', 'andi@gmail.com', '2025-05-20', NULL, 'aktif', '2025-05-20 02:02:25', '2025-05-20 02:02:43'),
(2, '222', 6, 'Saputra Kartini', 6, 2, 'zdsad', '48044', 'saput@gmail.com', '2025-05-20', NULL, 'aktif', '2025-05-20 02:03:18', '2025-05-20 02:03:18'),
(3, '223', 7, 'Dabit', 5, 2, 'qeqwe', '95605', 'dabit@gmail.com', '2025-05-20', NULL, 'aktif', '2025-05-20 02:07:22', '2025-05-20 02:07:22'),
(4, '224', 8, 'geraldo', 4, 2, 'akslas', '709807', 'geraldo@gmail', '2025-05-20', NULL, 'aktif', '2025-05-20 02:18:01', '2025-05-20 02:18:01'),
(5, '225', 9, 'jackson', 2, 1, 'asnaks', '18219', 'jackson@dki.com', '2025-05-20', NULL, 'aktif', '2025-05-20 02:38:08', '2025-05-20 02:38:08'),
(6, '226', 10, 'kiwil', 7, 4, 'adad', '5454', 'kiwil@dki.com', '2025-05-20', NULL, 'aktif', '2025-05-20 03:56:47', '2025-05-20 03:56:47');

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

--
-- Table structure for table `kode_absen`
--

CREATE TABLE `kode_absen` (
  `id` int(11) NOT NULL,
  `kode` varchar(10) NOT NULL,
  `tanggal` date NOT NULL,
  `karyawan_id` int(11) DEFAULT NULL,
  `status` enum('aktif','nonaktif') DEFAULT 'aktif',
  `created_by` int(11) DEFAULT NULL,
  `expired_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `kode_absen`
--

INSERT INTO `kode_absen` (`id`, `kode`, `tanggal`, `karyawan_id`, `status`, `created_by`, `expired_at`, `created_at`) VALUES
(1, '84BFMM', '2025-05-14', 1, 'aktif', 1, '2025-05-14 23:59:59', '2025-05-14 15:57:25'),
(2, 'E8VGAZ', '2025-05-19', 4, 'aktif', 1, '2025-05-19 23:59:59', '2025-05-19 14:28:06'),
(3, '7IIWVO', '2025-05-20', 3, 'aktif', 1, '2025-05-20 23:59:59', '2025-05-20 02:04:05'),
(4, 'CKC5FK', '2025-05-20', 5, 'aktif', 1, '2025-05-20 05:36:51', '2025-05-20 02:36:51'),
(5, 'BJ4PNW', '2025-05-20', 6, 'aktif', 1, '2025-05-20 23:59:59', '2025-05-20 02:37:03'),
(6, 'TP8CUV', '2025-05-20', 2, 'aktif', 1, '2025-05-20 23:59:59', '2025-05-20 03:56:09');

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

--
-- Table structure for table `notifikasi`
--

CREATE TABLE `notifikasi` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `tipe` enum('info','warning','success','danger') DEFAULT 'info',
  `judul` varchar(100) NOT NULL,
  `pesan` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `absensi`
--
ALTER TABLE `absensi`
  ADD PRIMARY KEY (`id`),
  ADD KEY `karyawan_id` (`karyawan_id`),
  ADD KEY `kode_absen_id` (`kode_absen_id`);

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

--
-- Indexes for table `gaji`
--
ALTER TABLE `gaji`
  ADD PRIMARY KEY (`id`),
  ADD KEY `karyawan_id` (`karyawan_id`);

--
-- Indexes for table `jabatan`
--
ALTER TABLE `jabatan`
  ADD PRIMARY KEY (`id`),
  ADD KEY `divisi_id` (`divisi_id`);

--
-- Indexes for table `karyawan`
--
ALTER TABLE `karyawan`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `nik` (`nik`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `jabatan_id` (`jabatan_id`),
  ADD KEY `divisi_id` (`divisi_id`),
  ADD KEY `supervisor_id` (`supervisor_id`);

--
-- Indexes for table `kode_absen`
--
ALTER TABLE `kode_absen`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `notifikasi`
--
ALTER TABLE `notifikasi`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `notifikasi`
--
ALTER TABLE `notifikasi`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `absensi`
--
ALTER TABLE `absensi`
  ADD CONSTRAINT `absensi_ibfk_1` FOREIGN KEY (`karyawan_id`) REFERENCES `karyawan` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `absensi_ibfk_2` FOREIGN KEY (`kode_absen_id`) REFERENCES `kode_absen` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `gaji`
--
ALTER TABLE `gaji`
  ADD CONSTRAINT `gaji_ibfk_1` FOREIGN KEY (`karyawan_id`) REFERENCES `karyawan` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `jabatan`
--
ALTER TABLE `jabatan`
  ADD CONSTRAINT `jabatan_ibfk_1` FOREIGN KEY (`divisi_id`) REFERENCES `divisi` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `karyawan`
--
ALTER TABLE `karyawan`
  ADD CONSTRAINT `karyawan_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `karyawan_ibfk_2` FOREIGN KEY (`jabatan_id`) REFERENCES `jabatan` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `karyawan_ibfk_3` FOREIGN KEY (`divisi_id`) REFERENCES `divisi` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `karyawan_ibfk_4` FOREIGN KEY (`supervisor_id`) REFERENCES `karyawan` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `kode_absen`
--
ALTER TABLE `kode_absen`
  ADD CONSTRAINT `kode_absen_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `kode_absen_ibfk_2` FOREIGN KEY (`karyawan_id`) REFERENCES `karyawan` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `notifikasi`
--
ALTER TABLE `notifikasi`
  ADD CONSTRAINT `notifikasi_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

