-- =====================================================================
-- DATABASE: Integrated Website and Academic Management System
-- PROGRAM STUDI MAGISTER PENDIDIKAN (M.Pd.)
-- UNIVERSITAS LINGGABUANA PGRI SUKABUMI (UNLIP)
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- =====================================================================
SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+07:00";
-- ---------------------------------------------------------------------
-- 1. USER DAN AUTHENTICATION
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `login_logs`;
DROP TABLE IF EXISTS `password_resets`;
DROP TABLE IF EXISTS `user_sessions`;
DROP TABLE IF EXISTS `user_roles`;
DROP TABLE IF EXISTS `role_permissions`;
DROP TABLE IF EXISTS `permissions`;
DROP TABLE IF EXISTS `roles`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`email` VARCHAR(150) NOT NULL UNIQUE,
`username` VARCHAR(50) NULL UNIQUE,
`phone` VARCHAR(25) NULL,
`password` VARCHAR(255) NOT NULL,
`status` ENUM('active', 'inactive', 'pending', 'suspended') NOT NULL DEFAULT 'active',
`avatar` VARCHAR(255) NULL,
`remember_token` VARCHAR(100) NULL,
`last_login_at` DATETIME NULL,
`last_login_ip` VARCHAR(45) NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_users_email` (`email`),
INDEX `idx_users_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `roles` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`slug` VARCHAR(50) NOT NULL UNIQUE,
`description` TEXT NULL,
`is_system` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `permissions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`slug` VARCHAR(80) NOT NULL UNIQUE,
`group_name` VARCHAR(50) NOT NULL,
`description` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_perm_slug` (`slug`),
INDEX `idx_perm_group` (`group_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `role_permissions` (
`role_id` INT UNSIGNED NOT NULL,
`permission_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`role_id`, `permission_id`),
CONSTRAINT `fk_rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user_roles` (
`user_id` BIGINT UNSIGNED NOT NULL,
`role_id` INT UNSIGNED NOT NULL,
PRIMARY KEY (`user_id`, `role_id`),
CONSTRAINT `fk_ur_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ur_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `user_sessions` (
`id` VARCHAR(128) NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`ip_address` VARCHAR(45) NULL,
`user_agent` TEXT NULL,
`payload` LONGTEXT NOT NULL,
`last_activity` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_sessions_user` (`user_id`),
CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `password_resets` (
`email` VARCHAR(150) NOT NULL,
`token` VARCHAR(255) NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX `idx_pwd_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `login_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NULL,
`ip_address` VARCHAR(45) NOT NULL,
`user_agent` TEXT NULL,
`status` ENUM('success', 'failed', 'blocked') NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_log_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 2. CMS (CONTENT MANAGEMENT SYSTEM)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `cms_announcement_recipients`;
DROP TABLE IF EXISTS `cms_announcements`;
DROP TABLE IF EXISTS `cms_news`;
DROP TABLE IF EXISTS `cms_categories`;
DROP TABLE IF EXISTS `cms_banners`;
DROP TABLE IF EXISTS `cms_menus`;
DROP TABLE IF EXISTS `cms_pages`;
DROP TABLE IF EXISTS `cms_media`;
DROP TABLE IF EXISTS `cms_settings`;
CREATE TABLE `cms_settings` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`key` VARCHAR(100) NOT NULL UNIQUE,
`value` LONGTEXT NULL,
`group` VARCHAR(50) NOT NULL DEFAULT 'general',
`type` VARCHAR(20) NOT NULL DEFAULT 'text',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_settings_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_categories` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`slug` VARCHAR(120) NOT NULL UNIQUE,
`type` ENUM('news', 'announcement', 'publication', 'general') NOT NULL DEFAULT 'news',
`description` TEXT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_pages` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(200) NOT NULL,
`slug` VARCHAR(220) NOT NULL UNIQUE,
`content` LONGTEXT NOT NULL,
`featured_image` VARCHAR(255) NULL,
`meta_title` VARCHAR(255) NULL,
`meta_description` TEXT NULL,
`status` ENUM('draft', 'published', 'scheduled', 'archived') NOT NULL DEFAULT 'published',
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_page_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_menus` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`parent_id` INT UNSIGNED NULL,
`title` VARCHAR(100) NOT NULL,
`url` VARCHAR(255) NOT NULL,
`target` VARCHAR(20) NOT NULL DEFAULT '_self',
`order_index` INT NOT NULL DEFAULT 0,
`position` ENUM('header', 'footer', 'sidebar') NOT NULL DEFAULT 'header',
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
INDEX `idx_menu_pos` (`position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_banners` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(200) NOT NULL,
`subtitle` TEXT NULL,
`image_url` VARCHAR(255) NOT NULL,
`cta_text` VARCHAR(50) NULL,
`cta_url` VARCHAR(255) NULL,
`order_index` INT NOT NULL DEFAULT 0,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_news` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`category_id` INT UNSIGNED NULL,
`title` VARCHAR(255) NOT NULL,
`slug` VARCHAR(255) NOT NULL UNIQUE,
`summary` TEXT NULL,
`content` LONGTEXT NOT NULL,
`featured_image` VARCHAR(255) NULL,
`views_count` INT UNSIGNED NOT NULL DEFAULT 0,
`status` ENUM('draft', 'published', 'scheduled', 'archived') NOT NULL DEFAULT 'published',
`published_at` DATETIME NULL,
`author_id` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_news_slug` (`slug`),
INDEX `idx_news_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_announcements` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`category_id` INT UNSIGNED NULL,
`title` VARCHAR(255) NOT NULL,
`slug` VARCHAR(255) NOT NULL UNIQUE,
`content` LONGTEXT NOT NULL,
`attachment_file` VARCHAR(255) NULL,
`featured_image` VARCHAR(255) NULL,
`target_audience` ENUM('all_active', 'cohort', 'semester', 'camaba', 'lecturers', 'custom') NOT NULL DEFAULT 'all_active',
`target_cohort` VARCHAR(10) NULL,
`target_semester` INT NULL,
`send_email` TINYINT(1) NOT NULL DEFAULT 0,
`send_whatsapp` TINYINT(1) NOT NULL DEFAULT 0,
`notification_status` ENUM('none', 'queued', 'sent', 'partial', 'failed') NOT NULL DEFAULT 'none',
`published_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expired_at` DATETIME NULL,
`status` ENUM('draft', 'published', 'scheduled', 'archived') NOT NULL DEFAULT 'published',
`author_id` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_ann_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_announcement_recipients` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`announcement_id` BIGINT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NULL,
`email` VARCHAR(150) NULL,
`phone` VARCHAR(25) NULL,
`email_sent` TINYINT(1) NOT NULL DEFAULT 0,
`whatsapp_sent` TINYINT(1) NOT NULL DEFAULT 0,
`read_at` DATETIME NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ar_ann` FOREIGN KEY (`announcement_id`) REFERENCES `cms_announcements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cms_media` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`filename` VARCHAR(255) NOT NULL,
`original_name` VARCHAR(255) NOT NULL,
`file_path` VARCHAR(255) NOT NULL,
`file_size` INT UNSIGNED NOT NULL,
`mime_type` VARCHAR(100) NOT NULL,
`uploaded_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 3. PROFIL PROGRAM STUDI
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `organizations`;
DROP TABLE IF EXISTS `accreditations`;
DROP TABLE IF EXISTS `study_program_missions`;
DROP TABLE IF EXISTS `study_program_visions`;
DROP TABLE IF EXISTS `study_program_profiles`;
DROP TABLE IF EXISTS `study_programs`;
CREATE TABLE `study_programs` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`code` VARCHAR(20) NOT NULL UNIQUE,
`name` VARCHAR(150) NOT NULL,
`level` VARCHAR(20) NOT NULL DEFAULT 'S2',
`degree_title` VARCHAR(50) NOT NULL DEFAULT 'M.Pd.',
`status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `study_program_profiles` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`history` LONGTEXT NULL,
`objectives` LONGTEXT NULL,
`targets` LONGTEXT NULL,
`strategy` LONGTEXT NULL,
`graduate_profile` LONGTEXT NULL,
`advantages` LONGTEXT NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_spp_sp` FOREIGN KEY (`study_program_id`) REFERENCES `study_programs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `study_program_visions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`vision_text` TEXT NOT NULL,
`effective_year` VARCHAR(10) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_spv_sp` FOREIGN KEY (`study_program_id`) REFERENCES `study_programs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `study_program_missions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`order_index` INT NOT NULL DEFAULT 1,
`mission_text` TEXT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_spm_sp` FOREIGN KEY (`study_program_id`) REFERENCES `study_programs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `accreditations` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`agency` VARCHAR(100) NOT NULL DEFAULT 'LAMDIK',
`rating` VARCHAR(50) NOT NULL,
`sk_number` VARCHAR(100) NOT NULL,
`valid_from` DATE NOT NULL,
`valid_until` DATE NOT NULL,
`certificate_file` VARCHAR(255) NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `organizations` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`position_title` VARCHAR(100) NOT NULL,
`person_name` VARCHAR(150) NOT NULL,
`academic_title` VARCHAR(100) NULL,
`photo` VARCHAR(255) NULL,
`order_index` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 4. DOSEN (LECTURERS)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `lecturer_profiles`;
DROP TABLE IF EXISTS `lecturer_research`;
DROP TABLE IF EXISTS `lecturer_publications`;
DROP TABLE IF EXISTS `lecturer_expertises`;
DROP TABLE IF EXISTS `lecturer_educations`;
DROP TABLE IF EXISTS `lecturers`;
CREATE TABLE `lecturers` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NULL,
`nidn` VARCHAR(30) NOT NULL UNIQUE,
`nip` VARCHAR(30) NULL,
`full_name` VARCHAR(150) NOT NULL,
`front_degree` VARCHAR(30) NULL,
`back_degree` VARCHAR(50) NULL,
`academic_position` VARCHAR(80) NOT NULL DEFAULT 'Lektor',
`highest_degree` ENUM('S2', 'S3') NOT NULL DEFAULT 'S3',
`is_professor` TINYINT(1) NOT NULL DEFAULT 0,
`is_doctor` TINYINT(1) NOT NULL DEFAULT 1,
`email` VARCHAR(150) NOT NULL,
`phone` VARCHAR(25) NULL,
`photo` VARCHAR(255) NULL,
`scholar_url` VARCHAR(255) NULL,
`sinta_id` VARCHAR(50) NULL,
`sinta_url` VARCHAR(255) NULL,
`scopus_id` VARCHAR(50) NULL,
`scopus_url` VARCHAR(255) NULL,
`status` ENUM('active', 'leave', 'retired', 'inactive') NOT NULL DEFAULT 'active',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_lecturer_nidn` (`nidn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `lecturer_educations` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`degree_level` ENUM('S1', 'S2', 'S3') NOT NULL,
`institution_name` VARCHAR(150) NOT NULL,
`major` VARCHAR(150) NOT NULL,
`graduation_year` INT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_le_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `lecturer_expertises` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`expertise_name` VARCHAR(150) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_lx_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `lecturer_publications` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`title` VARCHAR(255) NOT NULL,
`journal_name` VARCHAR(200) NOT NULL,
`year` INT NOT NULL,
`indexing` VARCHAR(50) NULL,
`doi_or_url` VARCHAR(255) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_lp_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `lecturer_research` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`title` VARCHAR(255) NOT NULL,
`funding_source` VARCHAR(150) NULL,
`year` INT NOT NULL,
`status` ENUM('completed', 'ongoing') NOT NULL DEFAULT 'completed',
PRIMARY KEY (`id`),
CONSTRAINT `fk_lr_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `lecturer_profiles` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`lecturer_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`bio` LONGTEXT NULL,
`teaching_philosophy` TEXT NULL,
`consultation_hours` VARCHAR(150) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_lprof_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 5. KURIKULUM DAN MATA KULIAH
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `course_schedules`;
DROP TABLE IF EXISTS `course_learning_outcomes`;
DROP TABLE IF EXISTS `learning_outcomes`;
DROP TABLE IF EXISTS `curriculum_courses`;
DROP TABLE IF EXISTS `courses`;
DROP TABLE IF EXISTS `curricula`;
CREATE TABLE `curricula` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_program_id` INT UNSIGNED NOT NULL,
`code` VARCHAR(50) NOT NULL UNIQUE,
`name` VARCHAR(150) NOT NULL,
`year_start` INT NOT NULL,
`year_end` INT NULL,
`total_sks` INT NOT NULL DEFAULT 36,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `courses` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`code` VARCHAR(30) NOT NULL UNIQUE,
`name` VARCHAR(150) NOT NULL,
`name_en` VARCHAR(150) NULL,
`sks` INT NOT NULL DEFAULT 3,
`theory_sks` INT NOT NULL DEFAULT 3,
`practice_sks` INT NOT NULL DEFAULT 0,
`type` ENUM('wajib', 'pilihan', 'tesis') NOT NULL DEFAULT 'wajib',
`semester_default` INT NOT NULL DEFAULT 1,
`description` TEXT NULL,
`syllabus_file` VARCHAR(255) NULL,
PRIMARY KEY (`id`),
INDEX `idx_course_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `curriculum_courses` (
`curriculum_id` INT UNSIGNED NOT NULL,
`course_id` INT UNSIGNED NOT NULL,
`semester` INT NOT NULL,
PRIMARY KEY (`curriculum_id`, `course_id`),
CONSTRAINT `fk_cc_curr` FOREIGN KEY (`curriculum_id`) REFERENCES `curricula` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_cc_crs` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `learning_outcomes` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`curriculum_id` INT UNSIGNED NOT NULL,
`code` VARCHAR(20) NOT NULL,
`category` ENUM('sikap', 'pengetahuan', 'keterampilan_umum', 'keterampilan_khusus') NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_lo_curr` FOREIGN KEY (`curriculum_id`) REFERENCES `curricula` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `course_learning_outcomes` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`course_id` INT UNSIGNED NOT NULL,
`learning_outcome_id` INT UNSIGNED NOT NULL,
`code` VARCHAR(30) NOT NULL,
`description` TEXT NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_clo_crs` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_clo_lo` FOREIGN KEY (`learning_outcome_id`) REFERENCES `learning_outcomes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `course_schedules` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`academic_year_id` INT UNSIGNED NOT NULL,
`course_id` INT UNSIGNED NOT NULL,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`room_name` VARCHAR(100) NOT NULL,
`day` ENUM('Senin', 'Selasa', 'Rabu', 'Kamis', 'Jumat', 'Sabtu', 'Minggu') NOT NULL,
`start_time` TIME NOT NULL,
`end_time` TIME NOT NULL,
`class_name` VARCHAR(50) NOT NULL DEFAULT 'Reguler A',
`online_link` VARCHAR(255) NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 6. PMB (PENERIMAAN MAHASISWA BARU ONLINE)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `pmb_re_registration`;
DROP TABLE IF EXISTS `pmb_results`;
DROP TABLE IF EXISTS `pmb_scores`;
DROP TABLE IF EXISTS `pmb_interviews`;
DROP TABLE IF EXISTS `pmb_exam_schedules`;
DROP TABLE IF EXISTS `pmb_exams`;
DROP TABLE IF EXISTS `pmb_payments`;
DROP TABLE IF EXISTS `pmb_invoices`;
DROP TABLE IF EXISTS `pmb_verifications`;
DROP TABLE IF EXISTS `pmb_documents`;
DROP TABLE IF EXISTS `pmb_registrations`;
DROP TABLE IF EXISTS `pmb_periods`;
CREATE TABLE `pmb_periods` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(150) NOT NULL,
`academic_year` VARCHAR(20) NOT NULL,
`wave_number` INT NOT NULL DEFAULT 1,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL,
`exam_date` DATE NOT NULL,
`announcement_date` DATE NOT NULL,
`re_registration_deadline` DATE NOT NULL,
`registration_fee` DECIMAL(12,2) NOT NULL DEFAULT 500000.00,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_registrations` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`period_id` INT UNSIGNED NOT NULL,
`user_id` BIGINT UNSIGNED NOT NULL,
`registration_number` VARCHAR(50) NOT NULL UNIQUE,
-- Step 1: Personal Data
`nik` VARCHAR(20) NOT NULL,
`full_name` VARCHAR(150) NOT NULL,
`birth_place` VARCHAR(100) NOT NULL,
`birth_date` DATE NOT NULL,
`gender` ENUM('L', 'P') NOT NULL,
`religion` VARCHAR(30) NOT NULL,
`address` TEXT NOT NULL,
`city` VARCHAR(100) NOT NULL,
`province` VARCHAR(100) NOT NULL,
`postal_code` VARCHAR(10) NULL,
`phone` VARCHAR(25) NOT NULL,
-- Step 2: S1 Education
`prev_university` VARCHAR(150) NOT NULL,
`prev_major` VARCHAR(150) NOT NULL,
`prev_entry_year` INT NOT NULL,
`prev_graduate_year` INT NOT NULL,
`prev_gpa` DECIMAL(3,2) NOT NULL,
`prev_certificate_no` VARCHAR(100) NOT NULL,
`prev_accreditation` VARCHAR(20) NULL,
-- Step 3: Employment
`job_status` ENUM('pns', 'guru_tetap', 'guru_honorer', 'dosen', 'swasta', 'wiraswasta', 'belum_bekerja') NOT NULL,
`company_name` VARCHAR(150) NULL,
`job_title` VARCHAR(100) NULL,
`company_address` TEXT NULL,
`work_duration_years` INT NULL DEFAULT 0,
-- Step 4: Study Option & Research
`class_type` ENUM('reguler_a', 'reguler_b_eksekutif', 'blended_learning') NOT NULL DEFAULT 'reguler_b_eksekutif',
`thesis_plan_title` TEXT NULL,
`step_completed` INT NOT NULL DEFAULT 1,
`status` ENUM('draft', 'submitted', 'pending', 'verified', 'revision', 'rejected') NOT NULL DEFAULT 'draft',
`submitted_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_pmb_regno` (`registration_number`),
INDEX `idx_pmb_user` (`user_id`),
CONSTRAINT `fk_pmb_period` FOREIGN KEY (`period_id`) REFERENCES `pmb_periods` (`id`) ON DELETE RESTRICT,
CONSTRAINT `fk_pmb_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_documents` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`document_type` ENUM('ktp', 'kk', 'ijazah_s1', 'transkrip_s1', 'pasfoto', 'surat_izin_belajar', 'rekomendasi') NOT NULL,
`file_path` VARCHAR(255) NOT NULL,
`file_name` VARCHAR(255) NOT NULL,
`file_size` INT UNSIGNED NOT NULL,
`verification_status` ENUM('pending', 'approved', 'revision', 'rejected') NOT NULL DEFAULT 'pending',
`notes` TEXT NULL,
`uploaded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pdoc_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_verifications` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`verifier_id` BIGINT UNSIGNED NOT NULL,
`status` ENUM('approved', 'revision', 'rejected') NOT NULL,
`notes` TEXT NULL,
`verified_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pver_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_invoices` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`invoice_number` VARCHAR(50) NOT NULL UNIQUE,
`amount` DECIMAL(12,2) NOT NULL,
`due_date` DATE NOT NULL,
`status` ENUM('unpaid', 'paid', 'expired', 'under_review') NOT NULL DEFAULT 'unpaid',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pinv_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_payments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`invoice_id` BIGINT UNSIGNED NOT NULL,
`proof_file` VARCHAR(255) NOT NULL,
`bank_name` VARCHAR(100) NOT NULL,
`account_number` VARCHAR(50) NOT NULL,
`account_name` VARCHAR(150) NOT NULL,
`amount_paid` DECIMAL(12,2) NOT NULL,
`payment_date` DATE NOT NULL,
`status` ENUM('pending', 'verified', 'rejected') NOT NULL DEFAULT 'pending',
`verifier_id` BIGINT UNSIGNED NULL,
`verified_at` DATETIME NULL,
`notes` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ppay_inv` FOREIGN KEY (`invoice_id`) REFERENCES `pmb_invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_exams` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(150) NOT NULL,
`exam_type` ENUM('tpa', 'bahasa_inggris', 'pedagogik') NOT NULL,
`duration_minutes` INT NOT NULL DEFAULT 60,
`total_questions` INT NOT NULL DEFAULT 20,
`passing_score` INT NOT NULL DEFAULT 65,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_exam_schedules` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`exam_id` INT UNSIGNED NOT NULL,
`exam_date` DATETIME NOT NULL,
`exam_link` VARCHAR(255) NULL,
`is_completed` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pes_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_interviews` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`examiner_id` BIGINT UNSIGNED NOT NULL,
`schedule_date` DATETIME NOT NULL,
`room_or_link` VARCHAR(255) NOT NULL,
`score` DECIMAL(5,2) NULL,
`notes` TEXT NULL,
`status` ENUM('scheduled', 'completed', 'cancelled') NOT NULL DEFAULT 'scheduled',
PRIMARY KEY (`id`),
CONSTRAINT `fk_piv_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_scores` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL,
`tpa_score` DECIMAL(5,2) NOT NULL DEFAULT 0,
`interview_score` DECIMAL(5,2) NOT NULL DEFAULT 0,
`portfolio_score` DECIMAL(5,2) NOT NULL DEFAULT 0,
`final_score` DECIMAL(5,2) NOT NULL DEFAULT 0,
`graded_by` BIGINT UNSIGNED NULL,
`graded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_psc_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_results` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`decision` ENUM('lulus', 'tidak_lulus', 'cadangan') NOT NULL,
`letter_number` VARCHAR(100) NOT NULL UNIQUE,
`letter_date` DATE NOT NULL,
`notes` TEXT NULL,
`qr_code_token` VARCHAR(100) NOT NULL UNIQUE,
`published_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pres_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `pmb_re_registration` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`registration_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`is_confirmed` TINYINT(1) NOT NULL DEFAULT 0,
`payment_status` ENUM('unpaid', 'pending_verification', 'verified') NOT NULL DEFAULT 'unpaid',
`invoice_number` VARCHAR(50) NULL,
`payment_proof` VARCHAR(255) NULL,
`assigned_nim` VARCHAR(30) NULL UNIQUE,
`converted_to_student_id` BIGINT UNSIGNED NULL,
`converted_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_prr_reg` FOREIGN KEY (`registration_id`) REFERENCES `pmb_registrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 7. DATA MAHASISWA
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `student_academic_histories`;
DROP TABLE IF EXISTS `student_documents`;
DROP TABLE IF EXISTS `student_status_histories`;
DROP TABLE IF EXISTS `student_emergency_contacts`;
DROP TABLE IF EXISTS `student_addresses`;
DROP TABLE IF EXISTS `student_profiles`;
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`study_program_id` INT UNSIGNED NOT NULL DEFAULT 1,
`nim` VARCHAR(30) NOT NULL UNIQUE,
`full_name` VARCHAR(150) NOT NULL,
`cohort` VARCHAR(10) NOT NULL, -- e.g. 2026/2027
`current_semester` INT NOT NULL DEFAULT 1,
`academic_status` ENUM('aktif', 'cuti', 'lulus', 'drop_out', 'non_aktif') NOT NULL DEFAULT 'aktif',
`gpa` DECIMAL(3,2) NOT NULL DEFAULT 0.00,
`credits_completed` INT NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_stud_nim` (`nim`),
CONSTRAINT `fk_stud_usr` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_profiles` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`nik` VARCHAR(20) NOT NULL,
`birth_place` VARCHAR(100) NOT NULL,
`birth_date` DATE NOT NULL,
`gender` ENUM('L', 'P') NOT NULL,
`religion` VARCHAR(30) NOT NULL,
`prev_university` VARCHAR(150) NULL,
`prev_major` VARCHAR(150) NULL,
`current_occupation` VARCHAR(150) NULL,
`company_name` VARCHAR(150) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_sprof_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_addresses` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`street_address` TEXT NOT NULL,
`village` VARCHAR(100) NULL,
`district` VARCHAR(100) NULL,
`city` VARCHAR(100) NOT NULL,
`province` VARCHAR(100) NOT NULL,
`postal_code` VARCHAR(10) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_saddr_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_emergency_contacts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`contact_name` VARCHAR(150) NOT NULL,
`relationship` VARCHAR(50) NOT NULL,
`phone` VARCHAR(25) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_semg_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_status_histories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`semester` INT NOT NULL,
`academic_year` VARCHAR(20) NOT NULL,
`status` ENUM('aktif', 'cuti', 'non_aktif') NOT NULL,
`notes` TEXT NULL,
`recorded_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ssh_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_documents` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`doc_type` VARCHAR(50) NOT NULL,
`file_path` VARCHAR(255) NOT NULL,
`file_name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_sdoc_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `student_academic_histories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`semester` INT NOT NULL,
`ips` DECIMAL(3,2) NOT NULL DEFAULT 0.00,
`ipk` DECIMAL(3,2) NOT NULL DEFAULT 0.00,
`sks_semester` INT NOT NULL DEFAULT 0,
`sks_total` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
CONSTRAINT `fk_sah_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 8. AKADEMIK (PERKULIAHAN, PRESENSI, NILAI)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `academic_calendars`;
DROP TABLE IF EXISTS `grade_histories`;
DROP TABLE IF EXISTS `grade_components`;
DROP TABLE IF EXISTS `grades`;
DROP TABLE IF EXISTS `attendance`;
DROP TABLE IF EXISTS `study_plan_courses`;
DROP TABLE IF EXISTS `study_plans`;
DROP TABLE IF EXISTS `course_enrollments`;
DROP TABLE IF EXISTS `semesters`;
DROP TABLE IF EXISTS `academic_years`;
CREATE TABLE `academic_years` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL, -- e.g. 2026/2027
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `semesters` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`academic_year_id` INT UNSIGNED NOT NULL,
`name` VARCHAR(50) NOT NULL, -- e.g. Ganjil 2026/2027
`type` ENUM('ganjil', 'genap', 'pendek') NOT NULL,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL,
`krs_start_date` DATE NOT NULL,
`krs_end_date` DATE NOT NULL,
`grade_submission_deadline` DATE NOT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
CONSTRAINT `fk_sem_ay` FOREIGN KEY (`academic_year_id`) REFERENCES `academic_years` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `study_plans` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`semester_id` INT UNSIGNED NOT NULL,
`total_sks` INT NOT NULL DEFAULT 0,
`status` ENUM('draft', 'submitted', 'approved', 'rejected') NOT NULL DEFAULT 'draft',
`approved_by` BIGINT UNSIGNED NULL,
`approved_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_sp_stud_sem` (`student_id`, `semester_id`),
CONSTRAINT `fk_sp_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_sp_sem` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `study_plan_courses` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`study_plan_id` BIGINT UNSIGNED NOT NULL,
`course_id` INT UNSIGNED NOT NULL,
`course_schedule_id` BIGINT UNSIGNED NULL,
`sks` INT NOT NULL DEFAULT 3,
PRIMARY KEY (`id`),
CONSTRAINT `fk_spc_sp` FOREIGN KEY (`study_plan_id`) REFERENCES `study_plans` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_spc_crs` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `course_enrollments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`course_id` INT UNSIGNED NOT NULL,
`semester_id` INT UNSIGNED NOT NULL,
`schedule_id` BIGINT UNSIGNED NULL,
`status` ENUM('enrolled', 'completed', 'dropped') NOT NULL DEFAULT 'enrolled',
PRIMARY KEY (`id`),
CONSTRAINT `fk_ce_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ce_crs` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ce_sem` FOREIGN KEY (`semester_id`) REFERENCES `semesters` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `attendance` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`schedule_id` BIGINT UNSIGNED NOT NULL,
`student_id` BIGINT UNSIGNED NOT NULL,
`meeting_number` INT NOT NULL, -- 1 to 16
`date` DATE NOT NULL,
`status` ENUM('hadir', 'izin', 'sakit', 'alpa') NOT NULL DEFAULT 'hadir',
`notes` VARCHAR(255) NULL,
PRIMARY KEY (`id`),
INDEX `idx_att_sched` (`schedule_id`),
CONSTRAINT `fk_att_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `grade_components` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`course_id` INT UNSIGNED NOT NULL,
`component_name` VARCHAR(50) NOT NULL, -- e.g. Tugas, UTS, UAS, Proyek
`weight_percentage` INT NOT NULL, -- e.g. 20, 30, 40, 10
PRIMARY KEY (`id`),
CONSTRAINT `fk_gc_crs` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `grades` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`enrollment_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`score_presence` DECIMAL(5,2) NOT NULL DEFAULT 100.00,
`score_assignment` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
`score_uts` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
`score_uas` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
`score_final` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
`letter_grade` VARCHAR(3) NOT NULL DEFAULT 'E',
`weight` DECIMAL(3,2) NOT NULL DEFAULT 0.00,
`is_locked` TINYINT(1) NOT NULL DEFAULT 0,
`finalized_by` BIGINT UNSIGNED NULL,
`finalized_at` DATETIME NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_gr_enr` FOREIGN KEY (`enrollment_id`) REFERENCES `course_enrollments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `grade_histories` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`grade_id` BIGINT UNSIGNED NOT NULL,
`changed_by` BIGINT UNSIGNED NOT NULL,
`old_score_final` DECIMAL(5,2) NOT NULL,
`new_score_final` DECIMAL(5,2) NOT NULL,
`old_letter_grade` VARCHAR(3) NOT NULL,
`new_letter_grade` VARCHAR(3) NOT NULL,
`reason` TEXT NOT NULL,
`changed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_gh_gr` FOREIGN KEY (`grade_id`) REFERENCES `grades` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `academic_calendars` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`semester_id` INT UNSIGNED NOT NULL,
`title` VARCHAR(150) NOT NULL,
`event_type` VARCHAR(50) NOT NULL,
`start_date` DATE NOT NULL,
`end_date` DATE NULL,
`description` TEXT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 9. TESIS
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `thesis_final_documents`;
DROP TABLE IF EXISTS `thesis_revisions`;
DROP TABLE IF EXISTS `thesis_assessments`;
DROP TABLE IF EXISTS `thesis_examinations`;
DROP TABLE IF EXISTS `thesis_seminars`;
DROP TABLE IF EXISTS `thesis_guidance`;
DROP TABLE IF EXISTS `thesis_supervisors`;
DROP TABLE IF EXISTS `thesis_proposals`;
DROP TABLE IF EXISTS `thesis_topics`;
CREATE TABLE `thesis_topics` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`title` VARCHAR(255) NOT NULL,
`focus_area` VARCHAR(150) NOT NULL,
`background_summary` TEXT NOT NULL,
`status` ENUM('submitted', 'approved', 'revision', 'rejected') NOT NULL DEFAULT 'submitted',
`reviewed_by` BIGINT UNSIGNED NULL,
`review_notes` TEXT NULL,
`submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tt_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_proposals` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`proposal_file` VARCHAR(255) NOT NULL,
`status` ENUM('draft', 'submitted', 'reviewing', 'approved_for_seminar', 'revision', 'rejected') NOT NULL DEFAULT 'submitted',
`submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tp_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_supervisors` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`lecturer_id` BIGINT UNSIGNED NOT NULL,
`supervisor_type` ENUM('pembimbing_1', 'pembimbing_2') NOT NULL,
`sk_number` VARCHAR(100) NULL,
`assigned_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_ts_top_type` (`topic_id`, `supervisor_type`),
CONSTRAINT `fk_ts_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_ts_lec` FOREIGN KEY (`lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_guidance` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`supervisor_id` BIGINT UNSIGNED NOT NULL,
`guidance_date` DATE NOT NULL,
`chapter_discussed` VARCHAR(100) NOT NULL,
`notes_from_student` TEXT NULL,
`notes_from_supervisor` TEXT NULL,
`document_draft` VARCHAR(255) NULL,
`status` ENUM('pending', 'approved', 'revision_needed') NOT NULL DEFAULT 'pending',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tg_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_tg_sup` FOREIGN KEY (`supervisor_id`) REFERENCES `thesis_supervisors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_seminars` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`seminar_type` ENUM('proposal', 'hasil') NOT NULL,
`scheduled_at` DATETIME NOT NULL,
`room_or_link` VARCHAR(255) NOT NULL,
`status` ENUM('scheduled', 'passed', 'revision', 'failed') NOT NULL DEFAULT 'scheduled',
`final_score` DECIMAL(5,2) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tsem_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_examinations` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`examination_date` DATETIME NOT NULL,
`room_or_link` VARCHAR(255) NOT NULL,
`status` ENUM('scheduled', 'passed', 'revision', 'failed') NOT NULL DEFAULT 'scheduled',
`final_score` DECIMAL(5,2) NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tex_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_assessments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`examination_id` BIGINT UNSIGNED NOT NULL,
`examiner_id` BIGINT UNSIGNED NOT NULL,
`score_content` DECIMAL(5,2) NOT NULL,
`score_methodology` DECIMAL(5,2) NOT NULL,
`score_defense` DECIMAL(5,2) NOT NULL,
`total_score` DECIMAL(5,2) NOT NULL,
`notes` TEXT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ta_ex` FOREIGN KEY (`examination_id`) REFERENCES `thesis_examinations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_revisions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL,
`revision_notes` TEXT NOT NULL,
`revised_document` VARCHAR(255) NULL,
`status` ENUM('submitted', 'approved', 'rejected') NOT NULL DEFAULT 'submitted',
`reviewed_by` BIGINT UNSIGNED NULL,
`reviewed_at` DATETIME NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_trev_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `thesis_final_documents` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`topic_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`full_thesis_pdf` VARCHAR(255) NOT NULL,
`article_pdf` VARCHAR(255) NULL,
`approval_sheet_pdf` VARCHAR(255) NOT NULL,
`is_validated` TINYINT(1) NOT NULL DEFAULT 0,
`validated_by` BIGINT UNSIGNED NULL,
`validated_at` DATETIME NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_tfd_top` FOREIGN KEY (`topic_id`) REFERENCES `thesis_topics` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 10. KEUANGAN DAN TAGIHAN
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `financial_transactions`;
DROP TABLE IF EXISTS `installments`;
DROP TABLE IF EXISTS `payment_receipts`;
DROP TABLE IF EXISTS `payment_confirmations`;
DROP TABLE IF EXISTS `payments`;
DROP TABLE IF EXISTS `invoice_items`;
DROP TABLE IF EXISTS `invoices`;
DROP TABLE IF EXISTS `fee_structures`;
DROP TABLE IF EXISTS `fee_types`;
CREATE TABLE `fee_types` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`code` VARCHAR(50) NOT NULL UNIQUE,
`name` VARCHAR(150) NOT NULL,
`description` TEXT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `fee_structures` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`fee_type_id` INT UNSIGNED NOT NULL,
`cohort` VARCHAR(10) NOT NULL, -- e.g. 2026/2027
`amount` DECIMAL(12,2) NOT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
CONSTRAINT `fk_fs_ft` FOREIGN KEY (`fee_type_id`) REFERENCES `fee_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `invoices` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL,
`invoice_number` VARCHAR(50) NOT NULL UNIQUE,
`semester_id` INT UNSIGNED NULL,
`fee_type_id` INT UNSIGNED NOT NULL,
`title` VARCHAR(200) NOT NULL,
`total_amount` DECIMAL(12,2) NOT NULL,
`paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
`due_date` DATE NOT NULL,
`status` ENUM('draft', 'unpaid', 'partially_paid', 'paid', 'overdue', 'cancelled', 'under_review') NOT NULL DEFAULT 'unpaid',
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_inv_num` (`invoice_number`),
INDEX `idx_inv_stud` (`student_id`),
CONSTRAINT `fk_inv_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_inv_ft` FOREIGN KEY (`fee_type_id`) REFERENCES `fee_types` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `invoice_items` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`invoice_id` BIGINT UNSIGNED NOT NULL,
`description` VARCHAR(255) NOT NULL,
`amount` DECIMAL(12,2) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ii_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `payments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`invoice_id` BIGINT UNSIGNED NOT NULL,
`payment_number` VARCHAR(50) NOT NULL UNIQUE,
`amount` DECIMAL(12,2) NOT NULL,
`payment_method` VARCHAR(50) NOT NULL DEFAULT 'bank_transfer',
`proof_file` VARCHAR(255) NULL,
`bank_name` VARCHAR(100) NULL,
`account_number` VARCHAR(50) NULL,
`account_name` VARCHAR(150) NULL,
`paid_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` ENUM('pending', 'verified', 'rejected') NOT NULL DEFAULT 'pending',
`verified_by` BIGINT UNSIGNED NULL,
`verified_at` DATETIME NULL,
`rejection_reason` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pay_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `payment_receipts` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`payment_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`receipt_number` VARCHAR(50) NOT NULL UNIQUE,
`issued_date` DATE NOT NULL,
`issued_by` BIGINT UNSIGNED NOT NULL,
`total_paid` DECIMAL(12,2) NOT NULL,
`pdf_path` VARCHAR(255) NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_pr_pay` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `installments` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`invoice_id` BIGINT UNSIGNED NOT NULL,
`installment_number` INT NOT NULL,
`amount` DECIMAL(12,2) NOT NULL,
`due_date` DATE NOT NULL,
`status` ENUM('unpaid', 'paid') NOT NULL DEFAULT 'unpaid',
PRIMARY KEY (`id`),
CONSTRAINT `fk_inst_inv` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `financial_transactions` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`transaction_number` VARCHAR(50) NOT NULL UNIQUE,
`type` ENUM('inflow', 'outflow') NOT NULL DEFAULT 'inflow',
`category` VARCHAR(100) NOT NULL,
`amount` DECIMAL(12,2) NOT NULL,
`description` TEXT NOT NULL,
`transaction_date` DATE NOT NULL,
`recorded_by` BIGINT UNSIGNED NOT NULL,
`reference_id` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 11. PENELITIAN DAN ALUMNI
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `alumni_tracer_studies`;
DROP TABLE IF EXISTS `alumni_careers`;
DROP TABLE IF EXISTS `alumni`;
DROP TABLE IF EXISTS `cooperation_partners`;
DROP TABLE IF EXISTS `community_services`;
DROP TABLE IF EXISTS `publications`;
DROP TABLE IF EXISTS `research_members`;
DROP TABLE IF EXISTS `research_projects`;
CREATE TABLE `research_projects` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`lead_lecturer_id` BIGINT UNSIGNED NOT NULL,
`academic_year` VARCHAR(20) NOT NULL,
`funding_source` VARCHAR(150) NOT NULL,
`budget` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
`status` ENUM('proposed', 'approved', 'ongoing', 'completed') NOT NULL DEFAULT 'completed',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_rp_lead` FOREIGN KEY (`lead_lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `research_members` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`research_id` BIGINT UNSIGNED NOT NULL,
`lecturer_id` BIGINT UNSIGNED NULL,
`student_id` BIGINT UNSIGNED NULL,
`role` VARCHAR(50) NOT NULL DEFAULT 'Anggota',
PRIMARY KEY (`id`),
CONSTRAINT `fk_rm_rp` FOREIGN KEY (`research_id`) REFERENCES `research_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `publications` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`authors` TEXT NOT NULL,
`type` ENUM('journal', 'proceeding', 'book', 'hki') NOT NULL DEFAULT 'journal',
`journal_or_publisher` VARCHAR(200) NOT NULL,
`publication_year` INT NOT NULL,
`volume` VARCHAR(20) NULL,
`issue` VARCHAR(20) NULL,
`pages` VARCHAR(50) NULL,
`doi` VARCHAR(100) NULL,
`url` VARCHAR(255) NULL,
`is_featured` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `community_services` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`partner_name` VARCHAR(150) NOT NULL,
`location` VARCHAR(150) NOT NULL,
`year` INT NOT NULL,
`lead_lecturer_id` BIGINT UNSIGNED NOT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_cs_lead` FOREIGN KEY (`lead_lecturer_id`) REFERENCES `lecturers` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `cooperation_partners` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`partner_name` VARCHAR(150) NOT NULL,
`cooperation_type` VARCHAR(100) NOT NULL,
`start_date` DATE NOT NULL,
`end_date` DATE NOT NULL,
`document_mou` VARCHAR(255) NULL,
`status` ENUM('active', 'expired') NOT NULL DEFAULT 'active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `alumni` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`student_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`graduation_date` DATE NOT NULL,
`diploma_number` VARCHAR(100) NOT NULL UNIQUE,
`final_gpa` DECIMAL(3,2) NOT NULL,
`predicate` VARCHAR(50) NOT NULL DEFAULT 'Dengan Pujian',
`thesis_title` TEXT NOT NULL,
`current_occupation` VARCHAR(150) NULL,
`company_name` VARCHAR(150) NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_al_stud` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `alumni_careers` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`alumni_id` BIGINT UNSIGNED NOT NULL,
`company_name` VARCHAR(150) NOT NULL,
`position` VARCHAR(100) NOT NULL,
`start_year` INT NOT NULL,
`end_year` INT NULL,
`is_current` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ac_al` FOREIGN KEY (`alumni_id`) REFERENCES `alumni` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `alumni_tracer_studies` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`alumni_id` BIGINT UNSIGNED NOT NULL,
`survey_year` INT NOT NULL,
`time_to_first_job_months` INT NOT NULL DEFAULT 0,
`job_relevance` ENUM('sangat_erat', 'erat', 'cukup_erat', 'kurang_erat', 'tidak_erat') NOT NULL DEFAULT 'sangat_erat',
`income_range` VARCHAR(100) NULL,
`submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
CONSTRAINT `fk_ats_al` FOREIGN KEY (`alumni_id`) REFERENCES `alumni` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 12. NOTIFICATION CENTER
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `notification_webhooks`;
DROP TABLE IF EXISTS `notification_settings`;
DROP TABLE IF EXISTS `notification_events`;
DROP TABLE IF EXISTS `notification_queue`;
DROP TABLE IF EXISTS `notification_preferences`;
DROP TABLE IF EXISTS `notification_logs`;
DROP TABLE IF EXISTS `notification_templates`;
CREATE TABLE `notification_templates` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`event_key` VARCHAR(80) NOT NULL UNIQUE,
`title` VARCHAR(150) NOT NULL,
`description` TEXT NULL,
`email_subject` VARCHAR(255) NOT NULL,
`email_body` LONGTEXT NOT NULL,
`whatsapp_body` TEXT NOT NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_nt_event` (`event_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `notification_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NULL,
`channel` ENUM('email', 'whatsapp', 'in_app') NOT NULL,
`recipient` VARCHAR(150) NOT NULL,
`subject` VARCHAR(255) NULL,
`content` LONGTEXT NOT NULL,
`event_type` VARCHAR(80) NOT NULL,
`status` ENUM('pending', 'sent', 'delivered', 'read', 'failed') NOT NULL DEFAULT 'pending',
`error_message` TEXT NULL,
`sent_at` DATETIME NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_nl_status` (`status`),
INDEX `idx_nl_channel` (`channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `notification_preferences` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NOT NULL UNIQUE,
`email_enabled` TINYINT(1) NOT NULL DEFAULT 1,
`whatsapp_enabled` TINYINT(1) NOT NULL DEFAULT 1,
`billing_alerts` TINYINT(1) NOT NULL DEFAULT 1,
`academic_alerts` TINYINT(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`id`),
CONSTRAINT `fk_np_usr` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `notification_queue` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`channel` ENUM('email', 'whatsapp') NOT NULL,
`recipient` VARCHAR(150) NOT NULL,
`subject` VARCHAR(255) NULL,
`payload` LONGTEXT NOT NULL,
`event_key` VARCHAR(80) NOT NULL,
`attempts` INT NOT NULL DEFAULT 0,
`max_attempts` INT NOT NULL DEFAULT 3,
`status` ENUM('pending', 'processing', 'completed', 'failed') NOT NULL DEFAULT 'pending',
`last_attempt_at` DATETIME NULL,
`error_reason` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_nq_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- 13. SISTEM (AUDIT LOG, SYSTEM SETTINGS)
-- ---------------------------------------------------------------------
DROP TABLE IF EXISTS `application_logs`;
DROP TABLE IF EXISTS `error_logs`;
DROP TABLE IF EXISTS `backup_logs`;
DROP TABLE IF EXISTS `system_settings`;
DROP TABLE IF EXISTS `audit_logs`;
CREATE TABLE `audit_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`user_id` BIGINT UNSIGNED NULL,
`action` VARCHAR(100) NOT NULL,
`table_name` VARCHAR(100) NOT NULL,
`record_id` BIGINT UNSIGNED NULL,
`old_values` LONGTEXT NULL,
`new_values` LONGTEXT NULL,
`ip_address` VARCHAR(45) NOT NULL,
`user_agent` TEXT NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `idx_al_act` (`action`),
INDEX `idx_al_tab` (`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `system_settings` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`group_name` VARCHAR(50) NOT NULL DEFAULT 'general',
`setting_key` VARCHAR(100) NOT NULL UNIQUE,
`setting_value` LONGTEXT NULL,
`description` VARCHAR(255) NULL,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `backup_logs` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`filename` VARCHAR(255) NOT NULL,
`filesize_bytes` BIGINT UNSIGNED NOT NULL,
`status` ENUM('success', 'failed') NOT NULL,
`created_by` BIGINT UNSIGNED NULL,
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ---------------------------------------------------------------------
-- SEED DATA (DATA AWAL RESMI & SISTEM)
-- ---------------------------------------------------------------------
-- Roles
INSERT INTO `roles` (`id`, `name`, `slug`, `description`, `is_system`) VALUES
(1, 'Super Admin', 'super_admin', 'Administrator tertinggi sistem dengan akses absolut ke semua modul', 1),
(2, 'Admin Pengelola', 'admin_pengelola', 'Pengelola administrasi sistem operasional pascasarjana', 1),
(3, 'Admin CMS', 'admin_cms', 'Pengelola konten website publik, berita, banner, dan halaman informasi', 1),
(4, 'Admin PMB', 'admin_pmb', 'Pengelola seleksi dan operasional penerimaan mahasiswa baru', 1),
(5, 'Verifikator PMB', 'verifikator_pmb', 'Pemeriksa dan pemverifikasi berkas pendaftaran calon mahasiswa', 1),
(6, 'Penguji Seleksi', 'penguji_seleksi', 'Dosen penguji tes wawancara dan tes masuk PMB', 1),
(7, 'Bagian Keuangan', 'keuangan', 'Pengelola tagihan, tarif, kuitansi, cicilan, dan verifikasi pembayaran', 1),
(8, 'Ketua Program Studi', 'kaprodi', 'Pimpinan pengelola program studi, kurikulum, CPL, mutu dan monitoring', 1),
(9, 'Sekretaris Program Studi', 'sekprodi', 'Sekretaris administrasi dan koordinasi akademik program studi', 1),
(10, 'Dosen', 'dosen', 'Pengajar matakuliah, pembimbing tesis, dan penguji sidang', 1),
(11, 'Mahasiswa', 'mahasiswa', 'Mahasiswa aktif program magister pendidikan', 1),
(12, 'Pimpinan', 'pimpinan', 'Rektorat dan pimpinan universitas untuk monitoring eksekutif', 1),
(13, 'Alumni', 'alumni', 'Lulusan Magister Pendidikan untuk jejaring dan tracer study', 1);
-- Permissions
INSERT INTO `permissions` (`name`, `slug`, `group_name`, `description`) VALUES
('Kelola User', 'users.manage', 'user', 'Akses penuh membuat, mengedit, dan menghapus user'),
('Kelola CMS', 'cms.manage', 'cms', 'Akses mengedit halaman, berita, banner, dan pengaturan website'),
('Kelola PMB', 'pmb.manage', 'pmb', 'Akses mengelola pendaftar, gelombang, verifikasi dan seleksi PMB'),
('Kelola Keuangan', 'finance.manage', 'finance', 'Akses mengelola tagihan, tarif, pembayaran, dan laporan keuangan'),
('Kelola Akademik', 'academic.manage', 'academic', 'Akses mengelola jadwal, kurikulum, KRS, KHS, dan perkuliahan'),
('Input Nilai', 'grades.input', 'academic', 'Akses menginput dan memfinalisasi nilai perkuliahan'),
('Bimbingan Tesis', 'thesis.advise', 'thesis', 'Akses bimbingan tesis mahasiswa dan persetujuan ujian'),
('Lihat Dashboard Eksekutif', 'executive.view', 'monitoring', 'Akses laporan statistik dan monitoring pimpinan');
-- Link Super Admin permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;
-- Initial Super Admin User: admin@unlip.ac.id / AdminUNLIP2026!
-- Hash generated via password_hash('AdminUNLIP2026!', PASSWORD_BCRYPT)
INSERT INTO `users` (`id`, `name`, `email`, `username`, `phone`, `password`, `status`, `created_at`) VALUES
(1, 'Administrator Pascasarjana UNLIP', 'admin@unlip.ac.id', 'admin', '081234567890', '$2y$10$JsoKgZN3IXPVmbf/ZkJnWO06RldvYtOGNN/gsL4ko7ncA9rtQJ8mO', 'active', NOW());
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES (1, 1);
-- Program Studi Magister Pendidikan
INSERT INTO `study_programs` (`id`, `code`, `name`, `level`, `degree_title`, `status`) VALUES
(1, '86108', 'Magister Pendidikan', 'S2', 'M.Pd.', 'active');
INSERT INTO `study_program_profiles` (`id`, `study_program_id`, `history`, `objectives`, `targets`, `strategy`, `graduate_profile`, `advantages`) VALUES
(1, 1,
'Program Studi Magister Pendidikan (M.Pd.) Universitas Linggabuana PGRI Sukabumi (UNLIP) didirikan untuk menjawab tuntutan peningkatan kualifikasi pendidik, pimpinan institusi pendidikan, dan peneliti pendidikan yang unggul, berkarakter, dan adaptif terhadap perkembangan teknologi digital.',
'1. Menghasilkan magister pendidikan yang menguasai keilmuan pendidikan mutakhir.\n2. Mengembangkan inovasi pembelajaran berbasis riset dan kearifan lokal.\n3. Menghasilkan publikasi ilmiah bereputasi nasional dan internasional.\n4. Mendorong transformasi tata kelola pendidikan yang visioner dan berintegritas.',
'1. 100% lulusan menyelesaikan studi tepat waktu (4 semester).\n2. 90% lulusan menghasilkan publikasi ilmiah pada jurnal nasional terakreditasi SINTA atau internasional.\n3. Tingkat kepuasan pengguna lulusan mencapai minimal 90% kategori Sangat Baik.',
'1. Penguatan kurikulum berbasis Outcome-Based Education (OBE).\n2. Kolaborasi riset bersama perguruan tinggi mitra nasional dan internasional.\n3. Penerapan blended learning adaptif untuk profesional dan praktisi pendidikan.',
'Lulusan dirancang menjadi:\n1. Pendidik Profesional & Dosen.\n2. Peneliti dan Pengembang Kurikulum Inovatif.\n3. Manajer & Pemimpin Lembaga Pendidikan (Kepala Sekolah, Pengawas, Konsultan Pendidikan).\n4. Penulis & Pengembang Media Pembelajaran Digital.',
'1. Dosen berkualifikasi Doktor (S3) dan Guru Besar (Profesor) dengan reputasi publikasi tinggi.\n2. Waktu perkuliahan fleksibel (Kelas Reguler & Eksekutif Akhir Pekan).\n3. Pembimbingan tesis terstruktur dengan target publikasi terarah.\n4. Biaya pendidikan terjangkau dengan skema cicilan resmi.');
INSERT INTO `study_program_visions` (`id`, `study_program_id`, `vision_text`, `effective_year`) VALUES
(1, 1, 'Menjadi program studi Magister Pendidikan yang unggul, inovatif, dan berdaya saing global berlandaskan nilai-nilai kearifan lokal dan keprofesionalan pendidik pada tahun 2030.', '2025-2030');
INSERT INTO `study_program_missions` (`id`, `study_program_id`, `order_index`, `mission_text`) VALUES
(1, 1, 1, 'Menyelenggarakan pendidikan magister yang bermutu tinggi berbasis kurikulum terstandar nasional dan berorientasi masa depan.'),
(2, 1, 2, 'Mengembangkan riset pendidikan mutakhir yang berkontribusi nyata pada pemecahan problematika pembelajaran di masyarakat.'),
(3, 1, 3, 'Melaksanakan pengabdian kepada masyarakat berbasis hasil inovasi keilmuan pendidikan.'),
(4, 1, 4, 'Menjalin jejaring kemitraan strategis dengan institusi pendidikan, pemerintah daerah, dan lembaga riset bereputasi.');
-- Akreditasi
INSERT INTO `accreditations` (`id`, `study_program_id`, `agency`, `rating`, `sk_number`, `valid_from`, `valid_until`, `is_active`) VALUES
(1, 1, 'LAMDIK', 'Baik Sekali', 'SK-LAMDIK/045/BAN-PT/2024', '2024-03-01', '2029-03-01', 1);
-- Kurikulum Standar S2 Magister Pendidikan (36 SKS)
INSERT INTO `curricula` (`id`, `study_program_id`, `code`, `name`, `year_start`, `year_end`, `total_sks`, `is_active`) VALUES
(1, 1, 'KUR-MPD-2024', 'Kurikulum Magister Pendidikan Berbasis OBE 2024', 2024, 2029, 36, 1);
-- Mata Kuliah
INSERT INTO `courses` (`id`, `code`, `name`, `name_en`, `sks`, `type`, `semester_default`, `description`) VALUES
(1, 'MPD701', 'Landasan Filosofis dan Teori Pendidikan', 'Philosophical Foundations and Educational Theory', 3, 'wajib', 1, 'Mengkaji hakikat filosofis, ontologis, epistemologis dan aksiologis dalam paradigma pendidikan.'),
(2, 'MPD702', 'Metodologi Penelitian Pendidikan Kuantitatif dan Kualitatif', 'Educational Research Methodology', 3, 'wajib', 1, 'Konsep dan implementasi metode riset pendidikan, mixed-method, dan analisis data empiris.'),
(3, 'MPD703', 'Statistika Terapan Pendidikan', 'Applied Statistics in Education', 3, 'wajib', 1, 'Penerapan uji statistik parametrik dan non-parametrik serta penggunaan software olah data (SPSS/R).'),
(4, 'MPD704', 'Inovasi dan Desain Kurikulum Pendidikan', 'Curriculum Innovation and Design', 3, 'wajib', 1, 'Analisis perancangan kurikulum modern, OBE, dan adaptasi Kurikulum Merdeka di sekolah.'),
(5, 'MPD705', 'Teori Belajar dan Pembelajaran Mutakhir', 'Contemporary Learning Theories', 3, 'wajib', 2, 'Eksplorasi konstruktivisme, konektivisme digital, dan neurosains dalam pembelajaran abad 21.'),
(6, 'MPD706', 'Evaluasi dan Asesmen Hasil Pembelajaran', 'Educational Assessment and Evaluation', 3, 'wajib', 2, 'Pengembangan instrumen tes, asesmen autentik, HOTS, dan evaluasi program pendidikan.'),
(7, 'MPD707', 'Kepemimpinan dan Manajemen Pendidikan', 'Educational Leadership and Management', 3, 'wajib', 2, 'Manajemen mutu terpadu, supervisi klinis, dan kepemimpinan transformasional di satuan pendidikan.'),
(8, 'MPD708', 'Teknologi dan Transformasi Digital Pembelajaran', 'Educational Technology and Digital Transformation', 3, 'wajib', 2, 'Desain media interaktif, e-learning, AI dalam pembelajaran, dan flipped classroom.'),
(9, 'MPD709', 'Seminar Proposal Penelitian Tesis', 'Thesis Proposal Seminar', 3, 'tesis', 3, 'Penyusunan dan presentasi proposal riset tesis di hadapan dewan penguji.'),
(10, 'MPD710', 'Publikasi Ilmiah dan Penulisan Artikel Jurnal', 'Scientific Publication and Journal Writing', 3, 'wajib', 3, 'Praktik penulisan artikel hasil riset untuk disubmit ke jurnal nasional SINTA/internasional.'),
(11, 'MPD711', 'Tesis (Riset dan Ujian Akhir)', 'Master Thesis', 6, 'tesis', 4, 'Pelaksanaan riset mandiri, pembimbingan, dan ujian sidang terbuka/tertutup tesis magister.');
-- Relasi Kurikulum & Mata Kuliah
INSERT INTO `curriculum_courses` (`curriculum_id`, `course_id`, `semester`) VALUES
(1, 1, 1), (1, 2, 1), (1, 3, 1), (1, 4, 1),
(1, 5, 2), (1, 6, 2), (1, 7, 2), (1, 8, 2),
(1, 9, 3), (1, 10, 3),
(1, 11, 4);
-- Tahun Akademik & Semester Aktif
INSERT INTO `academic_years` (`id`, `name`, `is_active`) VALUES
(1, '2026/2027', 1);
INSERT INTO `semesters` (`id`, `academic_year_id`, `name`, `type`, `start_date`, `end_date`, `krs_start_date`, `krs_end_date`, `grade_submission_deadline`, `is_active`) VALUES
(1, 1, 'Ganjil 2026/2027', 'ganjil', '2026-09-01', '2027-01-31', '2026-08-15', '2026-08-31', '2027-02-10', 1);
-- Periode PMB S2 Aktif
INSERT INTO `pmb_periods` (`id`, `name`, `academic_year`, `wave_number`, `start_date`, `end_date`, `exam_date`, `announcement_date`, `re_registration_deadline`, `registration_fee`, `is_active`) VALUES
(1, 'PMB S2 Magister Pendidikan Gelombang 1 TA 2026/2027', '2026/2027', 1, '2026-03-01', '2026-07-31', '2026-08-05', '2026-08-10', '2026-08-25', 500000.00, 1);
-- Jenis Tagihan Keuangan
INSERT INTO `fee_types` (`id`, `code`, `name`, `description`) VALUES
(1, 'PMB_REG', 'Biaya Pendaftaran PMB', 'Biaya formulir dan administrasi tes seleksi calon mahasiswa'),
(2, 'DAFTAR_ULANG', 'Biaya Registrasi Ulang', 'Biaya awal registrasi mahasiswa baru dan penerbitan NIM'),
(3, 'SPP_SEMESTER', 'SPP Semester', 'Sumbangan Pembinaan Pendidikan per semester'),
(4, 'BIAYA_SKS', 'Biaya Perkuliahan SKS', 'Biaya per SKS yang ditempuh mahasiswa'),
(5, 'SEMINAR_PROPOSAL', 'Biaya Ujian Seminar Proposal', 'Biaya dewan penguji seminar proposal tesis'),
(6, 'SIDANG_TESIS', 'Biaya Ujian Sidang Tesis', 'Biaya dewan penguji sidang akhir tesis magister'),
(7, 'WISUDA', 'Biaya Wisuda dan Ijazah', 'Biaya wisuda, legalisir, dan cetak ijazah magister');
-- Template Notifikasi Standar
INSERT INTO `notification_templates` (`id`, `event_key`, `title`, `description`, `email_subject`, `email_body`, `whatsapp_body`, `is_active`) VALUES
(1, 'pmb_registered', 'Pendaftaran Akun PMB Berhasil', 'Dikirim saat calon mahasiswa berhasil mendaftar akun PMB',
'Konfirmasi Akun PMB Magister Pendidikan UNLIP - {{nama_mahasiswa}}',
'Halo {{nama_mahasiswa}},
Terima kasih telah mendaftar pada Program Studi Magister Pendidikan (M.Pd.) Universitas Linggabuana PGRI Sukabumi (UNLIP).
Nomor Registrasi Anda: {{nomor_registrasi}}
Email: {{email}}
Silakan login ke portal PMB untuk melengkapi formulir 4 tahap dan mengunggah dokumen persyaratan.
Masuk ke Portal PMB
',
'Halo {{nama_mahasiswa}}, akun pendaftaran S2 Magister Pendidikan UNLIP Anda telah aktif. Nomor Registrasi: {{nomor_registrasi}}. Silakan lanjutkan pengisian formulir di: {{link_portal}}', 1),
(2, 'invoice_created', 'Penerbitan Tagihan Pembayaran', 'Dikirim saat invoice/tagihan baru diterbitkan',
'Pemberitahuan Tagihan Pembayaran: {{nomor_tagihan}} - Magister Pendidikan UNLIP',
'Halo {{nama_mahasiswa}} (NIM: {{nim}}),
Tagihan baru telah diterbitkan untuk Anda dengan rincian:
- Nomor Tagihan: {{nomor_tagihan}}
- Jenis Tagihan: {{jenis_tagihan}}
- Jumlah: {{jumlah_tagihan}}
- Jatuh Tempo: {{jatuh_tempo}}
Harap melakukan pembayaran sebelum tanggal jatuh tempo melalui rekening resmi UNLIP dan mengunggah bukti pada portal.
Lihat Rincian Tagihan
',
'Pemberitahuan Tagihan UNLIP: Halo {{nama_mahasiswa}}, tagihan {{jenis_tagihan}} sebesar {{jumlah_tagihan}} telah terbit. Jatuh tempo: {{jatuh_tempo}}. Info pembayaran: {{link_portal}}', 1),
(3, 'payment_verified', 'Verifikasi Pembayaran Berhasil', 'Dikirim saat bagian keuangan memverifikasi pembayaran',
'Kuitansi & Konfirmasi Pembayaran Tagihan {{nomor_tagihan}} - UNLIP',
'Halo {{nama_mahasiswa}},
Pembayaran Anda untuk tagihan {{nomor_tagihan}} sebesar {{jumlah_tagihan}} telah berhasil diverifikasi oleh Bagian Keuangan UNLIP.
Kuitansi resmi digital dapat diunduh melalui portal akademik Anda.
Unduh Kuitansi Digital
',
'Halo {{nama_mahasiswa}}, pembayaran Anda sebesar {{jumlah_tagihan}} untuk tagihan {{nomor_tagihan}} telah DIVERIFIKASI. Kuitansi dapat diunduh di {{link_portal}}', 1),
(4, 'announcement_published', 'Pengumuman Akademik Baru', 'Dikirim saat pengumuman diterbitkan untuk mahasiswa',
'[PENGUMUMAN] {{judul_pengumuman}} - Magister Pendidikan UNLIP',
'Halo Civitas Akademika Magister Pendidikan UNLIP,
Terdapat pengumuman resmi terbaru:
{{judul_pengumuman}}
{{ringkasan_pengumuman}}
Baca Pengumuman Selengkapnya
',
'PENGUMUMAN UNLIP: "{{judul_pengumuman}}". Baca selengkapnya di portal: {{link_portal}}', 1),
(5, 'pmb_passed', 'Pengumuman Kelulusan Seleksi PMB S2', 'Dikirim saat calon mahasiswa dinyatakan LULUS seleksi',
'Selamat! Anda Dinyatakan LULUS Seleksi Magister Pendidikan UNLIP',
'Yth. {{nama_mahasiswa}},
Selamat! Berdasarkan hasil evaluasi seleksi masuk Pascasarjana Universitas Linggabuana PGRI Sukabumi TA 2026/2027, Anda dinyatakan:
LULUS SELEKSI
Surat Keterangan Lulus (SKL) resmi bernomor {{nomor_surat}} dapat diunduh di portal PMB. Silakan segera melakukan proses Registrasi Ulang sebelum {{batas_daftar_ulang}}.
Unduh SKL & Registrasi Ulang
',
'Selamat {{nama_mahasiswa}}! Anda dinyatakan LULUS seleksi S2 Magister Pendidikan UNLIP. Unduh SKL resmi dan lakukan registrasi ulang di: {{link_portal}}', 1);
-- CMS Kategori Berita & Pengumuman
INSERT INTO `cms_categories` (`id`, `name`, `slug`, `type`, `description`) VALUES
(1, 'Berita Kampus', 'berita-kampus', 'news', 'Kabar dan kegiatan terkini lingkungan pascasarjana UNLIP'),
(2, 'Pengumuman Akademik', 'pengumuman-akademik', 'announcement', 'Pengumuman resmi perkuliahan, KRS, dan kalender akademik'),
(3, 'Informasi PMB', 'informasi-pmb', 'announcement', 'Jadwal, panduan, dan pengumuman penerimaan mahasiswa baru S2'),
(4, 'Seminar & Workshop', 'seminar-workshop', 'news', 'Agenda call for papers, konferensi, dan workshop keilmuan pendidikan'),
(5, 'Penelitian & Publikasi', 'penelitian-publikasi', 'general', 'Hasil riset dosen, tesis mahasiswa, dan jurnal ilmiah');
-- Banner Utama Website
INSERT INTO `cms_banners` (`id`, `title`, `subtitle`, `image_url`, `cta_text`, `cta_url`, `order_index`, `is_active`) VALUES
(1, 'Membentuk Pemimpin Pendidikan Masa Depan yang Unggul & Berkarakter', 'Program Studi Magister Pendidikan (M.Pd.) Universitas Linggabuana PGRI Sukabumi mengintegrasikan riset inovatif dan kepemimpinan transformasional.', 'assets/images/banner-hero-1.jpg', 'Daftar S2 Sekarang', 'pmb/register', 1, 1),
(2, 'Perkuliahan Fleksibel & Terjangkau untuk Pendidik Profesional', 'Tersedia Kelas Eksekutif Akhir Pekan dan Blended Learning didukung dosen berkualifikasi Guru Besar dan Doktor terkemuka.', 'assets/images/banner-hero-2.jpg', 'Lihat Kurikulum', 'kurikulum', 2, 1);
-- Pengaturan CMS Default
INSERT INTO `cms_settings` (`key`, `value`, `group`, `type`) VALUES
('site_name', 'Magister Pendidikan UNLIP', 'general', 'text'),
('site_tagline', 'Unggul, Inovatif, dan Berkarakter Pendidikan', 'general', 'text'),
('university_name', 'Universitas Linggabuana PGRI Sukabumi', 'general', 'text'),
('university_short', 'UNLIP', 'general', 'text'),
('contact_email', 'pasca@unlip.ac.id', 'contact', 'text'),
('contact_phone', '(0266) 226163', 'contact', 'text'),
('contact_whatsapp', '+62 812-3456-7890', 'contact', 'text'),
('contact_address', 'Jl. Pramuka II No. 10, Cikondang, Kec. Citamiang, Kota Sukabumi, Jawa Barat 43142', 'contact', 'textarea'),
('stats_students', '240', 'statistics', 'number'),
('stats_alumni', '850', 'statistics', 'number'),
('stats_lecturers', '18', 'statistics', 'number'),
('stats_professors_doctors', '14', 'statistics', 'number'),
('stats_publications', '320', 'statistics', 'number'),
('stats_partners', '45', 'statistics', 'number'),
('stats_accreditation', 'Baik Sekali (LAMDIK)', 'statistics', 'text'),
('pmb_registration_active', '1', 'pmb', 'boolean'),
('pmb_brochure_url', 'uploads/brosur-magister-pendidikan-unlip.pdf', 'pmb', 'text');
-- Menu Navigasi Header
INSERT INTO `cms_menus` (`id`, `parent_id`, `title`, `url`, `target`, `order_index`, `position`, `is_active`) VALUES
(1, NULL, 'Beranda', '/', '_self', 1, 'header', 1),
(2, NULL, 'Profil', '/profil', '_self', 2, 'header', 1),
(3, NULL, 'Visi & Misi', '/visi-misi', '_self', 3, 'header', 1),
(4, NULL, 'Kurikulum', '/kurikulum', '_self', 4, 'header', 1),
(5, NULL, 'Profil Dosen', '/dosen', '_self', 5, 'header', 1),
(6, NULL, 'Penelitian & Publikasi', '/penelitian', '_self', 6, 'header', 1),
(7, NULL, 'Berita & Pengumuman', '/berita', '_self', 7, 'header', 1),
(8, NULL, 'PMB S2', '/pmb-info', '_self', 8, 'header', 1),
(9, NULL, 'Kontak', '/kontak', '_self', 9, 'header', 1);
-- Data Awal Mahasiswa (Demo & Portal Mahasiswa)
INSERT INTO `users` (`id`, `name`, `email`, `username`, `phone`, `password`, `status`, `created_at`) VALUES
(2, 'Ahmad Fauzi, S.Pd.', 'mahasiswa@unlip.ac.id', 'mhs_ahmad', '081234567891', '$2y$10$JsoKgZN3IXPVmbf/ZkJnWO06RldvYtOGNN/gsL4ko7ncA9rtQJ8mO', 'active', NOW())
ON DUPLICATE KEY UPDATE `email` = VALUES(`email`);
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(2, 11)
ON DUPLICATE KEY UPDATE `role_id` = VALUES(`role_id`);
INSERT INTO `students` (`id`, `user_id`, `study_program_id`, `nim`, `full_name`, `cohort`, `current_semester`, `academic_status`, `gpa`, `credits_completed`, `created_at`) VALUES
(1, 2, 1, '2026861001', 'Ahmad Fauzi, S.Pd.', '2026/2027', 1, 'aktif', 3.82, 14, NOW())
ON DUPLICATE KEY UPDATE `nim` = VALUES(`nim`);
INSERT INTO `student_profiles` (`student_id`, `nik`, `birth_place`, `birth_date`, `gender`, `religion`, `prev_university`, `prev_major`, `current_occupation`, `company_name`) VALUES
(1, '3272010101950001', 'Sukabumi', '1995-05-12', 'L', 'Islam', 'Universitas Linggabuana PGRI Sukabumi', 'Pendidikan Bahasa Indonesia (S1)', 'Guru Tetap', 'SMK PGRI 1 Sukabumi')
ON DUPLICATE KEY UPDATE `nik` = VALUES(`nik`);
INSERT INTO `student_addresses` (`student_id`, `street_address`, `city`, `province`, `postal_code`) VALUES
(1, 'Jl. Pramuka No. 12', 'Kota Sukabumi', 'Jawa Barat', '43142')
ON DUPLICATE KEY UPDATE `city` = VALUES(`city`);
INSERT INTO `course_enrollments` (`id`, `student_id`, `course_id`, `semester_id`, `status`) VALUES
(1, 1, 1, 1, 'enrolled'),
(2, 1, 2, 1, 'enrolled'),
(3, 1, 3, 1, 'enrolled'),
(4, 1, 4, 1, 'enrolled')
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);
INSERT INTO `invoices` (`id`, `student_id`, `invoice_number`, `semester_id`, `fee_type_id`, `title`, `total_amount`, `paid_amount`, `due_date`, `status`, `created_at`) VALUES
(1, 1, 'INV-SPP-20260901-0001', 1, 3, 'SPP Magister Pendidikan Semester 1', 4500000.00, 4500000.00, DATE_ADD(NOW(), INTERVAL 30 DAY), 'paid', NOW())
ON DUPLICATE KEY UPDATE `status` = VALUES(`status`);
SET FOREIGN_KEY_CHECKS = 1;