--
-- Host: localhost    Database: arn_system
-- ------------------------------------------------------


--
-- Table structure for table `absensis`
--

CREATE TABLE `absensis` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `jadwal_id` bigint(20) unsigned NOT NULL,
  `pertemuan_id` bigint(20) unsigned DEFAULT NULL,
  `qr_id` bigint(20) unsigned DEFAULT NULL,
  `waktu_absen` datetime NOT NULL,
  `tanggal_absen` date DEFAULT NULL,
  `status` enum('hadir','izin','alpha','sakit') NOT NULL DEFAULT 'alpha',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `absensi_unique_per_hari` (`user_id`,`jadwal_id`,`tanggal_absen`),
  KEY `absensis_jadwal_id_foreign` (`jadwal_id`),
  KEY `absensis_qr_id_foreign` (`qr_id`),
  KEY `absensis_tanggal_absen_index` (`tanggal_absen`),
  KEY `absensis_pertemuan_id_foreign` (`pertemuan_id`),
  CONSTRAINT `absensis_jadwal_id_foreign` FOREIGN KEY (`jadwal_id`) REFERENCES `jadwals` (`id`) ON DELETE CASCADE,
  CONSTRAINT `absensis_pertemuan_id_foreign` FOREIGN KEY (`pertemuan_id`) REFERENCES `pertemuan` (`id`) ON DELETE CASCADE,
  CONSTRAINT `absensis_qr_id_foreign` FOREIGN KEY (`qr_id`) REFERENCES `qr_codes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `absensis_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=579 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `absensis`
--

INSERT INTO `absensis` VALUES (528,2,15,376,NULL,'2026-04-13 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(529,2,15,378,NULL,'2026-04-20 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(530,2,15,380,NULL,'2026-04-27 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(531,2,16,375,NULL,'2026-04-07 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(532,2,16,377,NULL,'2026-04-14 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(533,2,16,379,NULL,'2026-04-21 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(534,2,16,381,NULL,'2026-04-28 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(535,6,15,376,NULL,'2026-04-13 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(536,6,15,378,NULL,'2026-04-20 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(537,6,15,380,NULL,'2026-04-27 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(538,6,16,375,NULL,'2026-04-07 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(539,6,16,377,NULL,'2026-04-14 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(540,6,16,379,NULL,'2026-04-21 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(541,6,16,381,NULL,'2026-04-28 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(542,7,15,376,NULL,'2026-04-13 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(543,7,15,378,NULL,'2026-04-20 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(544,7,15,380,NULL,'2026-04-27 08:51:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(545,7,16,375,NULL,'2026-04-07 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(546,7,16,377,NULL,'2026-04-14 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(547,7,16,379,NULL,'2026-04-21 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(548,7,16,381,NULL,'2026-04-28 09:40:00',NULL,'alpha','2026-05-03 01:51:48','2026-05-03 01:51:48'),(549,2,15,391,3,'2026-05-03 08:53:05',NULL,'hadir','2026-05-03 01:53:05','2026-05-03 01:53:05'),(550,6,15,391,NULL,'2026-05-03 08:51:00',NULL,'alpha','2026-05-03 01:53:27','2026-05-03 01:53:27'),(551,7,15,391,NULL,'2026-05-03 08:51:00',NULL,'alpha','2026-05-03 01:53:27','2026-05-03 01:53:27'),(570,2,17,408,NULL,'2026-04-13 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(571,2,17,409,NULL,'2026-04-20 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(572,2,17,410,NULL,'2026-04-27 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(573,6,17,408,NULL,'2026-04-13 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(574,6,17,409,NULL,'2026-04-20 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(575,6,17,410,NULL,'2026-04-27 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(576,7,17,408,NULL,'2026-04-13 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(577,7,17,409,NULL,'2026-04-20 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02'),(578,7,17,410,NULL,'2026-04-27 11:00:00',NULL,'alpha','2026-05-03 02:07:02','2026-05-03 02:07:02');

--
-- Table structure for table `dosens`
--

CREATE TABLE `dosens` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `nip` varchar(20) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `prodi` varchar(60) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `dosens_nip_unique` (`nip`),
  KEY `dosens_user_id_foreign` (`user_id`),
  CONSTRAINT `dosens_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `dosens`
--

INSERT INTO `dosens` VALUES (1,3,'199011192019031014','Debi Yandra Niska, S.Kom., M.Kom.','Ilmu Komputer','2026-04-24 12:34:31','2026-05-02 08:37:17');

--
-- Table structure for table `jadwals`
--

CREATE TABLE `jadwals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `matkul_id` bigint(20) unsigned NOT NULL,
  `hari` varchar(10) NOT NULL,
  `jam_mulai` time NOT NULL,
  `jam_selesai` time NOT NULL,
  `ruangan` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jadwals_matkul_id_foreign` (`matkul_id`),
  CONSTRAINT `jadwals_matkul_id_foreign` FOREIGN KEY (`matkul_id`) REFERENCES `mata_kuliahs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `jadwals`
--

INSERT INTO `jadwals` VALUES (15,1,'Minggu','08:51:00','10:00:00','online','2026-05-02 17:03:08','2026-05-03 01:51:48'),(16,1,'Selasa','09:40:00','12:10:00','77.02.1','2026-05-02 17:05:05','2026-05-02 17:05:05'),(17,8,'Minggu','11:00:00','12:59:00','Online','2026-05-03 01:58:16','2026-05-03 01:58:16');

--
-- Table structure for table `mahasiswas`
--

CREATE TABLE `mahasiswas` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `nim` varchar(15) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `angkatan` varchar(4) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mahasiswas_nim_unique` (`nim`),
  KEY `mahasiswas_user_id_foreign` (`user_id`),
  CONSTRAINT `mahasiswas_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `mahasiswas`
--

INSERT INTO `mahasiswas` VALUES (1,2,'4243250029','Angelica Barus','2024','2026-04-24 12:33:28','2026-05-02 03:31:36'),(3,6,'4243250055','Gus Rosauli Pandiangan',NULL,'2026-05-01 16:09:02','2026-05-01 16:09:02'),(4,7,'4241250007','Nuriana Sipahutar',NULL,'2026-05-02 03:31:22','2026-05-02 03:31:22');

--
-- Table structure for table `mata_kuliahs`
--

CREATE TABLE `mata_kuliahs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `dosen_id` bigint(20) unsigned DEFAULT NULL,
  `kode` varchar(10) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `sks` tinyint(4) NOT NULL,
  `semester` int(11) DEFAULT NULL,
  `tanggal_mulai` date DEFAULT NULL,
  `tanggal_selesai` date DEFAULT NULL,
  `is_aktif` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `mata_kuliahs_kode_unique` (`kode`),
  KEY `mata_kuliahs_dosen_id_foreign` (`dosen_id`),
  CONSTRAINT `mata_kuliahs_dosen_id_foreign` FOREIGN KEY (`dosen_id`) REFERENCES `dosens` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `mata_kuliahs`
--

INSERT INTO `mata_kuliahs` VALUES (1,1,'RPL','Rekayasa Perangkat Lunak',3,4,'2026-04-06','2026-06-06',1,'2026-04-24 12:35:01','2026-05-03 01:48:39'),(8,1,'ORKOM','Organisasi dan Arsitektur komputer',3,4,'2026-04-06','2026-05-02',0,'2026-05-02 17:02:22','2026-05-03 02:07:02');

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` VALUES (1,'2024_01_01_000001_create_users_table',1),(2,'2024_01_01_000002_create_mahasiswas_table',1),(3,'2024_01_01_000003_create_dosens_table',1),(4,'2024_01_01_000004_create_mata_kuliahs_table',1),(5,'2024_01_01_000005_create_jadwals_table',1),(6,'2024_01_01_000006_create_qr_codes_table',1),(7,'2024_01_01_000007_create_absensis_table',1),(8,'2024_01_01_000008_create_tugass_table',1),(9,'2024_01_01_000009_create_pengumpulan_tugass_table',1),(10,'2024_01_02_000001_add_semester_period_to_mata_kuliahs',1),(11,'2024_01_02_000002_add_komentar_to_pengumpulan_tugass',1),(12,'2026_03_29_095715_make_qr_id_nullable_in_absensis_table',1),(13,'2026_04_21_235702_create_mahasiswa_matakuliah_table',1),(14,'2026_04_22_061045_add_tanggal_absen_to_absensis',1),(15,'2026_04_24_115318_update_semester_to_integer',1),(16,'2026_04_24_121144_make_prodi_nullable_in_dosens',1),(17,'2026_04_24_123236_make_dosen_id_nullable_in_mata_kuliahs',1),(18,'2026_04_24_195018_create_pertemuan_table',2),(19,'2026_04_24_201544_add_pertemuan_id_to_absensis',3);

--
-- Table structure for table `pengumpulan_tugass`
--

CREATE TABLE `pengumpulan_tugass` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `tugas_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `file_jawaban` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`file_jawaban`)),
  `komentar_mahasiswa` text DEFAULT NULL,
  `waktu_kumpul` datetime NOT NULL,
  `nilai` double DEFAULT NULL,
  `catatan_dosen` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pengumpulan_tugass_tugas_id_user_id_unique` (`tugas_id`,`user_id`),
  KEY `pengumpulan_tugass_user_id_foreign` (`user_id`),
  CONSTRAINT `pengumpulan_tugass_tugas_id_foreign` FOREIGN KEY (`tugas_id`) REFERENCES `tugass` (`id`) ON DELETE CASCADE,
  CONSTRAINT `pengumpulan_tugass_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pengumpulan_tugass`
--


--
-- Table structure for table `pertemuan`
--

CREATE TABLE `pertemuan` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `jadwal_id` bigint(20) unsigned NOT NULL,
  `pertemuan_ke` int(11) NOT NULL,
  `tanggal` date NOT NULL,
  `jam_mulai` time NOT NULL,
  `jam_selesai` time NOT NULL,
  `ruangan` varchar(50) DEFAULT NULL,
  `status` enum('akan_datang','berlangsung','selesai') NOT NULL DEFAULT 'akan_datang',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pertemuan_jadwal_id_tanggal_index` (`jadwal_id`,`tanggal`),
  CONSTRAINT `pertemuan_jadwal_id_foreign` FOREIGN KEY (`jadwal_id`) REFERENCES `jadwals` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `pertemuan`
--

INSERT INTO `pertemuan` VALUES (375,16,1,'2026-04-07','09:40:00','12:10:00','77.02.1','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(376,15,2,'2026-04-13','08:51:00','10:00:00','online','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(377,16,3,'2026-04-14','09:40:00','12:10:00','77.02.1','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(378,15,4,'2026-04-20','08:51:00','10:00:00','online','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(379,16,5,'2026-04-21','09:40:00','12:10:00','77.02.1','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(380,15,6,'2026-04-27','08:51:00','10:00:00','online','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(381,16,7,'2026-04-28','09:40:00','12:10:00','77.02.1','selesai','2026-05-03 01:51:48','2026-05-03 01:51:48'),(382,15,8,'2026-05-04','08:51:00','10:00:00','online','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(383,16,9,'2026-05-05','09:40:00','12:10:00','77.02.1','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(384,15,10,'2026-05-11','08:51:00','10:00:00','online','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(385,16,11,'2026-05-12','09:40:00','12:10:00','77.02.1','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(386,15,12,'2026-05-18','08:51:00','10:00:00','online','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(387,16,13,'2026-05-19','09:40:00','12:10:00','77.02.1','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(388,15,14,'2026-05-25','08:51:00','10:00:00','online','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(389,16,15,'2026-05-26','09:40:00','12:10:00','77.02.1','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(390,15,16,'2026-06-01','08:51:00','10:00:00','online','akan_datang','2026-05-03 01:51:48','2026-05-03 01:51:48'),(391,15,9,'2026-05-03','08:51:00','10:00:00','online','berlangsung','2026-05-03 01:53:05','2026-05-03 01:53:05'),(408,17,1,'2026-04-13','11:00:00','12:59:00','Online','selesai','2026-05-03 02:07:02','2026-05-03 02:07:02'),(409,17,2,'2026-04-20','11:00:00','12:59:00','Online','selesai','2026-05-03 02:07:02','2026-05-03 02:07:02'),(410,17,3,'2026-04-27','11:00:00','12:59:00','Online','selesai','2026-05-03 02:07:02','2026-05-03 02:07:02');

--
-- Table structure for table `tugass`
--

CREATE TABLE `tugass` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `matkul_id` bigint(20) unsigned NOT NULL,
  `judul` varchar(200) NOT NULL,
  `deskripsi` text DEFAULT NULL,
  `deadline` datetime NOT NULL,
  `file_soal` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`file_soal`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tugass_matkul_id_foreign` (`matkul_id`),
  CONSTRAINT `tugass_matkul_id_foreign` FOREIGN KEY (`matkul_id`) REFERENCES `mata_kuliahs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tugass`
--



