-- ============================================================
--  Sistem Informasi Pendaftaran Mahasiswa Baru (PMB)
--  MySQL Schema — XAMPP compatible
--  Database: sistemin_sistem_informasi_pendaftaran_mahasiswa_baru
-- ============================================================

-- Pastikan database yang dipakai adalah sistemin_sistem_informasi_pendaftaran_mahasiswa_baru
-- Jika database belum dibuat, buat terlebih dahulu di phpMyAdmin:
-- CREATE DATABASE sistemin_sistem_informasi_pendaftaran_mahasiswa_baru CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `sistemin_sistem_informasi_pendaftaran_mahasiswa_baru`;

-- ============================================================
--  Table: users
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('student','petugas','admin','super_admin') NOT NULL DEFAULT 'student',
  `nim` VARCHAR(20) DEFAULT 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`),
  UNIQUE KEY `uk_users_email` (`email`),
  KEY `idx_users_role` (`role`),
  KEY `idx_users_nim` (`nim`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS `profiles` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `asal_sekolah` VARCHAR(255) DEFAULT NULL,
  `jurusan` VARCHAR(100) DEFAULT NULL,
  `status_pendaftaran` ENUM('pending','processing','accepted','rejected') NOT NULL DEFAULT 'pending',
  `angkatan` INT DEFAULT NULL,
  `tempat_lahir` VARCHAR(255) DEFAULT NULL,
  `tanggal_lahir` DATE DEFAULT NULL,
  `jenis_kelamin` VARCHAR(20) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_profiles_user_id` (`user_id`),
  KEY `idx_profiles_status` (`status_pendaftaran`),
  KEY `idx_profiles_jurusan` (`jurusan`),
  CONSTRAINT `fk_profiles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: program_studi
-- ============================================================
CREATE TABLE IF NOT EXISTS `program_studi` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `kode` VARCHAR(20) DEFAULT NULL,
  `nama` VARCHAR(255) NOT NULL,
  `akreditasi` VARCHAR(50) DEFAULT NULL,
  `kuota` INT NOT NULL DEFAULT 0,
  `biaya` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `deskripsi` TEXT DEFAULT NULL,
  `icon` VARCHAR(100) DEFAULT NULL,
  `foto` VARCHAR(255) DEFAULT NULL,
  `urut` 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`),
  UNIQUE KEY `uk_program_studi_nama` (`nama`),
  KEY `idx_program_studi_urut` (`urut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: requirements
-- ============================================================
CREATE TABLE IF NOT EXISTS `requirements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` VARCHAR(255) NOT NULL,
  `deskripsi` TEXT DEFAULT NULL,
  `icon` VARCHAR(100) DEFAULT NULL,
  `kategori` VARCHAR(100) DEFAULT NULL,
  `urut` 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`),
  KEY `idx_requirements_urut` (`urut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: education_history
-- ============================================================
CREATE TABLE IF NOT EXISTS `education_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `jenjang` VARCHAR(50) DEFAULT NULL,
  `nama_sekolah` VARCHAR(255) DEFAULT NULL,
  `tahun_lulus` INT DEFAULT NULL,
  `nilai_ujian` FLOAT DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_edu_user` (`user_id`),
  CONSTRAINT `fk_education_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: documents
-- ============================================================
CREATE TABLE IF NOT EXISTS `documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `document_type` ENUM('ktp','ijazah','kk','foto','sehat','skl','raport') NOT NULL,
  `original_name` VARCHAR(255) DEFAULT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_size` INT DEFAULT NULL,
  `mime_type` VARCHAR(100) DEFAULT NULL,
  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,
  `admin_note` TEXT DEFAULT NULL,
  `verified_by` INT UNSIGNED DEFAULT NULL,
  `verified_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_doc_user_type` (`user_id`,`document_type`),
  KEY `idx_doc_verified` (`is_verified`),
  CONSTRAINT `fk_doc_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_doc_verifier` FOREIGN KEY (`verified_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: payments
-- ============================================================
CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL DEFAULT 2500000.00,
  `payment_status` ENUM('pending','paid','failed') NOT NULL DEFAULT 'pending',
  `payment_method` VARCHAR(50) DEFAULT NULL,
  `transaction_id` VARCHAR(100) DEFAULT NULL,
  `paid_at` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pay_user` (`user_id`),
  KEY `idx_pay_status` (`payment_status`),
  CONSTRAINT `fk_pay_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: registration_status
-- ============================================================
CREATE TABLE IF NOT EXISTS `registration_status` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `status` ENUM('draft','submitted','under_review','approved','rejected') NOT NULL DEFAULT 'draft',
  `last_step_completed` INT NOT NULL DEFAULT 0,
  `admin_comment` TEXT DEFAULT NULL,
  `updated_by` INT UNSIGNED DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_reg_user` (`user_id`),
  KEY `idx_reg_status` (`status`),
  CONSTRAINT `fk_reg_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_reg_updater` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: password_resets
-- ============================================================
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `token` VARCHAR(255) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `used` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reset_email` (`email`),
  KEY `idx_reset_token` (`token`),
  KEY `idx_reset_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: activities
-- ============================================================
CREATE TABLE IF NOT EXISTS `activities` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `description` TEXT NOT NULL,
  `status` VARCHAR(20) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_act_user` (`user_id`),
  KEY `idx_act_created` (`created_at`),
  CONSTRAINT `fk_act_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: announcements
-- ============================================================
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `body` TEXT NOT NULL,
  `author` VARCHAR(100) DEFAULT NULL,
  `pinned` TINYINT(1) NOT NULL DEFAULT 0,
  `published` TINYINT(1) NOT NULL DEFAULT 1,
  `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ann_pinned` (`pinned`),
  KEY `idx_ann_published` (`published`),
  KEY `idx_ann_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: messages
-- ============================================================
CREATE TABLE IF NOT EXISTS `messages` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `admin_id` INT UNSIGNED DEFAULT NULL,
  `message` TEXT NOT NULL,
  `sender_type` ENUM('student','admin') NOT NULL DEFAULT 'student',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_user` (`user_id`),
  KEY `idx_msg_created` (`created_at`),
  CONSTRAINT `fk_msg_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: notifications
-- ============================================================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_notif_user_read` (`user_id`,`is_read`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Table: website_settings
-- ============================================================
CREATE TABLE IF NOT EXISTS `website_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(100) NOT NULL,
  `value` TEXT DEFAULT NULL,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_settings_key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
--  Seed Data
-- ============================================================

-- Admin user (password: admin12345)
INSERT INTO `users` (`email`, `password`, `role`, `is_active`, `created_at`)
VALUES ('admin@pmb.ac.id', '$2y$10$r/xS/wtnFJNsFXF9nUrer.tXmFXbwlyRXDZHYFfBgP8YQAVYQbybu', 'super_admin', 1, NOW())
ON DUPLICATE KEY UPDATE `role` = 'super_admin', `is_active` = 1;

-- Default program studi
INSERT INTO `program_studi` (`kode`, `nama`, `akreditasi`, `kuota`, `biaya`, `deskripsi`, `icon`, `urut`) VALUES
('TI', 'Teknik Informatika', 'A', 120, 2500000.00, 'Program studi Teknik Informatika fokus pada pengembangan perangkat lunak dan sistem informasi.', 'fa-laptop-code', 1),
('SI', 'Sistem Informasi', 'A', 100, 2500000.00, 'Program studi Sistem Informasi menggabungkan ilmu komputer dan manajemen.', 'fa-database', 2),
('M', 'Manajemen', 'B', 80, 2000000.00, 'Program studi Manajemen mempersiapkan lulusan untuk dunia bisnis dan manajemen.', 'fa-chart-line', 3),
('A', 'Akuntansi', 'B', 80, 2000000.00, 'Program studi Akuntansi mempersiapkan lulusan untuk profesi akuntan dan keuangan.', 'fa-calculator', 4)
ON DUPLICATE KEY UPDATE `nama` = VALUES(`nama`);

-- Default requirements
INSERT INTO `requirements` (`nama`, `deskripsi`, `icon`, `kategori`, `urut`) VALUES
('Ijazah', 'Scan ijazah SMA/SMK yang sudah dilegalisir', 'fa-graduation-cap', 'dokumen', 1),
('KTP', 'Scan KTP pemohon yang masih berlaku', 'fa-id-card', 'identitas', 2),
('Kartu Keluarga', 'Scan KK pemohon', 'fa-file-alt', 'identitas', 3),
('Pas Foto', 'Foto terbaru berwarna 3x4', 'fa-camera', 'dokumen', 4),
('Surat Keterangan Sehat', 'Surat keterangan sehat dari dokter', 'fa-stethoscope', 'kesehatan', 5),
('Surat Keterangan Lulus', 'SKL dari sekolah asal', 'fa-file-signature', 'dokumen', 6)
ON DUPLICATE KEY UPDATE `nama` = VALUES(`nama`);

-- Default website settings
INSERT INTO `website_settings` (`key`, `value`) VALUES
('site_name', 'PMB 2026'),
('tagline', 'Sistem Informasi Pendaftaran Mahasiswa Baru'),
('address', 'Jl. Contoh No. 123, Kota Contoh'),
('phone', '(021) 1234-5678'),
('email', 'info@pmb.ac.id'),
('maps', ''),
('maps_embed', ''),
('soc_fb', ''),
('soc_ig', ''),
('soc_yt', ''),
('soc_x', ''),
('seo_title', 'PMB 2026 - Pendaftaran Mahasiswa Baru'),
('seo_desc', 'Sistem Informasi Pendaftaran Mahasiswa Baru Online'),
('seo_keywords', 'PMB, pendaftaran, mahasiswa baru, universitas'),
('logo', '')
ON DUPLICATE KEY UPDATE `value` = VALUES(`value`);