-- --------------------------------------------------------
-- Database schema for berita-kelas
-- Generated from production database (nuogwcpc_beritakelas)
-- --------------------------------------------------------

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
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 */;

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

CREATE TABLE `users` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(255) NOT NULL DEFAULT 'mahasiswa',
  `remember_token` varchar(100) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `password_reset_tokens`
-- --------------------------------------------------------

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `sessions`
-- --------------------------------------------------------

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `cache`
-- --------------------------------------------------------

CREATE TABLE `cache` (
  `key` varchar(255) NOT NULL,
  `value` mediumtext NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `cache_locks`
-- --------------------------------------------------------

CREATE TABLE `cache_locks` (
  `key` varchar(255) NOT NULL,
  `owner` varchar(255) NOT NULL,
  `expiration` int(11) NOT NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `jobs`
-- --------------------------------------------------------

CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `job_batches`
-- --------------------------------------------------------

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `failed_jobs`
-- --------------------------------------------------------

CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `kelas`
-- --------------------------------------------------------

CREATE TABLE `kelas` (
  `id` varchar(30) NOT NULL,
  `nama` varchar(80) NOT NULL,
  `lantai` int(3) NOT NULL,
  `kode_ruangan` varchar(10) NOT NULL,
  `tipe_ruang` enum('tetap','tidak tetap') NOT NULL DEFAULT 'tidak tetap',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `personal`
-- --------------------------------------------------------

CREATE TABLE `personal` (
  `id` varchar(10) NOT NULL,
  `nama` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `barang_grup`
-- --------------------------------------------------------

CREATE TABLE `barang_grup` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nama` varchar(255) NOT NULL,
  `merk` varchar(255) NOT NULL,
  `kondisi` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nama` (`nama`,`merk`,`kondisi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `barang`
-- --------------------------------------------------------

CREATE TABLE `barang` (
  `id` varchar(50) NOT NULL,
  `kodefikasi` varchar(20) DEFAULT NULL,
  `nama` varchar(255) NOT NULL,
  `merk` varchar(255) NOT NULL,
  `barang_grup_id` bigint(20) DEFAULT NULL,
  `kondisi` enum('Baik','Rusak Ringan','Rusak Berat') NOT NULL,
  `status` enum('Baru','Pindahan','Pemusnahan','Hilang','Pindah Ruangan','Pindah Unit','Pindah Personal') NOT NULL,
  `tahun` year(4) NOT NULL,
  `berita_acara` varchar(255) DEFAULT NULL,
  `assigned_type` enum('App\\Models\\Kelas','App\\Models\\Personal') NOT NULL,
  `assigned_id` varchar(10) NOT NULL,
  `kelas_id` varchar(30) DEFAULT NULL,
  `personal_id` varchar(10) DEFAULT NULL,
  `keterangan` varchar(255) DEFAULT NULL,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_assigned_type_id` (`assigned_type`,`assigned_id`),
  KEY `barang_kelas_id_index` (`kelas_id`),
  KEY `barang_personal_id_index` (`personal_id`),
  KEY `barang_barang_grup_id_foreign` (`barang_grup_id`),
  CONSTRAINT `barang_barang_grup_id_foreign` FOREIGN KEY (`barang_grup_id`) REFERENCES `barang_grup` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_kelas_id_foreign` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_personal_id_foreign` FOREIGN KEY (`personal_id`) REFERENCES `personal` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `barang_assignments` (relation/pivot table)
-- --------------------------------------------------------

CREATE TABLE `barang_assignments` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `barang_id` varchar(50) NOT NULL,
  `kelas_id` varchar(30) DEFAULT NULL,
  `personal_id` varchar(10) DEFAULT NULL,
  `assigned_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `released_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `barang_assignments_kelas_personal_barang_index` (`kelas_id`,`personal_id`,`barang_id`),
  KEY `barang_assignments_barang_id_foreign` (`barang_id`),
  KEY `barang_assignments_kelas_id_foreign` (`kelas_id`),
  KEY `barang_assignments_personal_id_foreign` (`personal_id`),
  CONSTRAINT `barang_assignments_barang_id_foreign` FOREIGN KEY (`barang_id`) REFERENCES `barang` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_assignments_kelas_id_foreign` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barang_assignments_personal_id_foreign` FOREIGN KEY (`personal_id`) REFERENCES `personal` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `berita`
-- --------------------------------------------------------

CREATE TABLE `berita` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `barang_grup_id` bigint(20) DEFAULT NULL,
  `barang_id` varchar(20) DEFAULT NULL,
  `berita` varchar(255) NOT NULL,
  `kelas_id` varchar(30) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `status` enum('Diselesaikan','Dijawab','Proses') NOT NULL DEFAULT 'Proses',
  `image` varchar(255) DEFAULT NULL,
  `jawaban` varchar(255) DEFAULT NULL,
  `jawaban_image` varchar(255) DEFAULT NULL,
  `kondisi_terakhir` enum('Baik','Rusak Berat','Rusak Ringan') NOT NULL,
  `tanggal_action` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `berita_barang_id_foreign` (`barang_id`),
  KEY `berita_kelas_id_foreign` (`kelas_id`),
  KEY `berita_barang_grup_id_foreign` (`barang_grup_id`),
  CONSTRAINT `berita_barang_grup_id_foreign` FOREIGN KEY (`barang_grup_id`) REFERENCES `barang_grup` (`id`) ON DELETE CASCADE,
  CONSTRAINT `berita_barang_id_foreign` FOREIGN KEY (`barang_id`) REFERENCES `barang` (`id`) ON DELETE CASCADE,
  CONSTRAINT `berita_kelas_id_foreign` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `data_personil`
-- --------------------------------------------------------

CREATE TABLE `data_personil` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nis` varchar(30) DEFAULT NULL,
  `nip` varchar(30) DEFAULT NULL,
  `nidn` varchar(30) DEFAULT NULL,
  `jabatan` enum('dosen','tendik') NOT NULL,
  `nama` varchar(255) DEFAULT NULL,
  `no_hp` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `mata_kuliah`
-- --------------------------------------------------------

CREATE TABLE `mata_kuliah` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kode_mk` varchar(255) DEFAULT NULL,
  `nama` varchar(255) NOT NULL,
  `prodi` varchar(255) NOT NULL,
  `sks` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `jadwal_ruangan`
-- --------------------------------------------------------

CREATE TABLE `jadwal_ruangan` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `hari` varchar(255) NOT NULL,
  `jam_masuk` time NOT NULL,
  `jam_keluar` time NOT NULL,
  `dosen` varchar(255) DEFAULT NULL,
  `dosen_id` bigint(20) UNSIGNED DEFAULT NULL,
  `mata_kuliah` varchar(255) NOT NULL,
  `mata_kuliah_id` bigint(20) UNSIGNED DEFAULT NULL,
  `kelas_id` varchar(30) NOT NULL,
  `kolom` tinyint(4) DEFAULT NULL,
  `kelas` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jadwal_ruangan_kelas_id_foreign` (`kelas_id`),
  KEY `jadwal_ruangan_mata_kuliah_id_foreign` (`mata_kuliah_id`),
  KEY `jadwal_ruangan_dosen_id_foreign` (`dosen_id`),
  CONSTRAINT `jadwal_ruangan_kelas_id_foreign` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE,
  CONSTRAINT `jadwal_ruangan_mata_kuliah_id_foreign` FOREIGN KEY (`mata_kuliah_id`) REFERENCES `mata_kuliah` (`id`) ON DELETE SET NULL,
  CONSTRAINT `jadwal_ruangan_dosen_id_foreign` FOREIGN KEY (`dosen_id`) REFERENCES `data_personil` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `reservasi_ruangan`
-- --------------------------------------------------------

CREATE TABLE `reservasi_ruangan` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_pic` varchar(255) NOT NULL,
  `kontak` varchar(255) NOT NULL,
  `nama_acara` varchar(255) NOT NULL,
  `tanggal_acara` datetime NOT NULL,
  `jam_mulai` time NOT NULL,
  `jam_selesai` time NOT NULL,
  `kelas_id` varchar(30) NOT NULL,
  `status` enum('0','1','-1') NOT NULL DEFAULT '0',
  `keterangan` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `reservasi_ruangan_kelas_id_foreign` (`kelas_id`),
  CONSTRAINT `reservasi_ruangan_kelas_id_foreign` FOREIGN KEY (`kelas_id`) REFERENCES `kelas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- View: barang_mhs
-- --------------------------------------------------------

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `barang_mhs` AS
SELECT
  `bg`.`id`         AS `barang_grup_id`,
  `b`.`assigned_id` AS `kelas_id`,
  `b`.`nama`        AS `nama`,
  `b`.`merk`        AS `merk`,
  `b`.`kondisi`     AS `kondisi`,
  COUNT(*)          AS `jumlah`
FROM (`barang` `b` JOIN `barang_grup` `bg` ON(`b`.`barang_grup_id` = `bg`.`id`))
WHERE `b`.`assigned_type` = 'App\\Models\\Kelas'
  AND `b`.`status` NOT IN ('Pemusnahan','Hilang','Pindah Unit')
GROUP BY `bg`.`id`, `b`.`assigned_id`, `b`.`nama`, `b`.`merk`, `b`.`kondisi`;

-- --------------------------------------------------------
-- View: jadwal_gabungan
-- --------------------------------------------------------

CREATE ALGORITHM=UNDEFINED SQL SECURITY INVOKER VIEW `jadwal_gabungan` AS
SELECT
  `jadwal_ruangan`.`jam_masuk`      AS `jam_masuk`,
  `jadwal_ruangan`.`jam_keluar`     AS `jam_keluar`,
  `jadwal_ruangan`.`hari`           AS `hari`,
  `jadwal_ruangan`.`dosen`          AS `dosen`,
  `jadwal_ruangan`.`dosen_id`       AS `dosen_id`,
  `jadwal_ruangan`.`kelas_id`       AS `kelas_id`,
  `jadwal_ruangan`.`kelas`          AS `kelas`,
  `jadwal_ruangan`.`mata_kuliah`    AS `mata_kuliah`,
  'tetap'                           AS `tipe_jadwal`,
  NULL                              AS `status`
FROM `jadwal_ruangan`
UNION ALL
SELECT
  `reservasi_ruangan`.`jam_mulai`     AS `jam_masuk`,
  `reservasi_ruangan`.`jam_selesai`   AS `jam_keluar`,
  CAST(`reservasi_ruangan`.`tanggal_acara` AS CHAR CHARSET utf8mb4) COLLATE utf8mb4_unicode_ci AS `hari`,
  `reservasi_ruangan`.`nama_acara`    COLLATE utf8mb4_unicode_ci AS `dosen`,
  NULL                                AS `dosen_id`,
  `reservasi_ruangan`.`kelas_id`      COLLATE utf8mb4_unicode_ci AS `kelas_id`,
  ''                                  COLLATE utf8mb4_unicode_ci AS `kelas`,
  ''                                  COLLATE utf8mb4_unicode_ci AS `mata_kuliah`,
  'tidak tetap'                       COLLATE utf8mb4_unicode_ci AS `tipe_jadwal`,
  `reservasi_ruangan`.`status`        AS `status`
FROM `reservasi_ruangan`;

COMMIT;