-- ========================================
-- S.P.M Dashboard - Sektor Pembangunan Murid
-- Database Schema
-- ========================================

CREATE DATABASE IF NOT EXISTS `sepmu_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `sepmu_db`;

-- ----------------------------------------
-- Table: sektor
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `sektor` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama_sektor` VARCHAR(100) NOT NULL,
  `kod` VARCHAR(20) DEFAULT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `sektor` (`nama_sektor`, `kod`) VALUES
('Sektor Pembangunan Murid', 'SPM');

-- ----------------------------------------
-- Table: unit
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `unit` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `sektor_id` INT UNSIGNED NOT NULL,
  `nama_unit` VARCHAR(100) NOT NULL,
  `kod` VARCHAR(20) DEFAULT NULL,
  `warna` VARCHAR(20) DEFAULT '#dc3545',
  `ikon` VARCHAR(50) DEFAULT 'fas fa-building',
  `deskripsi` TEXT,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`sektor_id`) REFERENCES `sektor`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO `unit` (`sektor_id`, `nama_unit`, `kod`, `warna`, `ikon`, `deskripsi`) VALUES
(1, 'Unit Pembangunan Bakat Murid', 'UPBM', '#0d6efd', 'fas fa-star', 'Unit yang menguruskan pembangunan bakat dan potensi murid'),
(1, 'Unit Hal Ehwal Murid', 'UHEM', '#dc3545', 'fas fa-users', 'Unit yang menguruskan hal ehwal dan kebajikan murid'),
(1, 'Unit Pusat Kokurikulum', 'UPK', '#198754', 'fas fa-trophy', 'Unit yang menguruskan aktiviti kokurikulum sekolah');

-- ----------------------------------------
-- Table: jawatan
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `jawatan` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `nama_jawatan` VARCHAR(100) NOT NULL,
  `gred` VARCHAR(20) DEFAULT NULL,
  `urutan` INT DEFAULT 0,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `jawatan` (`nama_jawatan`, `gred`, `urutan`) VALUES
('Timbalan Pengarah Pendidikan', 'JUSA C', 1),
('Ketua Penolong Pengarah', 'S52', 2),
('Penolong Pengarah', 'S48', 3),
('Ketua Pembantu Tadbir', 'N22', 4),
('Pembantu Tadbir', 'N19', 5);

-- ----------------------------------------
-- Table: pegawai
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `pegawai` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED NOT NULL,
  `sektor_id` INT UNSIGNED NOT NULL DEFAULT 1,
  `jawatan_id` INT UNSIGNED NOT NULL,
  `nama` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) UNIQUE,
  `no_telefon` VARCHAR(20),
  `no_telefon_pejabat` VARCHAR(20),
  `gambar` VARCHAR(255) DEFAULT 'default.png',
  `bio` TEXT,
  `tugas_utama` TEXT,
  `pencapaian` TEXT,
  `facebook` VARCHAR(255),
  `twitter` VARCHAR(255),
  `linkedin` VARCHAR(255),
  `instagram` VARCHAR(255),
  `status` TINYINT(1) DEFAULT 1 COMMENT '1=Aktif, 0=Tidak Aktif',
  `urutan` INT DEFAULT 99,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`sektor_id`) REFERENCES `sektor`(`id`),
  FOREIGN KEY (`jawatan_id`) REFERENCES `jawatan`(`id`)
) ENGINE=InnoDB;

-- ----------------------------------------
-- Table: users (Login System)
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pegawai_id` INT UNSIGNED DEFAULT NULL,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `email` VARCHAR(150) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('superadmin','admin_unit','pegawai') DEFAULT 'pegawai',
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `token_reset` VARCHAR(100) DEFAULT NULL,
  `token_expiry` DATETIME DEFAULT NULL,
  `last_login` DATETIME DEFAULT NULL,
  `remember_token` VARCHAR(100) DEFAULT NULL,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`pegawai_id`) REFERENCES `pegawai`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Default admin account (password: Admin@1234)
INSERT INTO `users` (`username`, `email`, `password`, `role`) VALUES
('superadmin', 'admin@sepmu.gov.my', '$2y$12$LZneoJlUPjVMhqsyAkpS3.9ydJuBqVB4nN37IUTGXHg.xByHrCNgi', 'superadmin');

-- ----------------------------------------
-- Table: takwim (Calendar Events)
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `takwim` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `pegawai_id` INT UNSIGNED DEFAULT NULL,
  `tajuk` VARCHAR(200) NOT NULL,
  `deskripsi` TEXT,
  `tarikh_mula` DATE NOT NULL,
  `tarikh_tamat` DATE,
  `masa_mula` TIME,
  `masa_tamat` TIME,
  `tempat` VARCHAR(200),
  `warna` VARCHAR(20) DEFAULT '#0d6efd',
  `semua_hari` TINYINT(1) DEFAULT 0,
  `status` TINYINT(1) DEFAULT 1,
  `created_by` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`pegawai_id`) REFERENCES `pegawai`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------------------
-- Table: links (Unit Dashboard Links)
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `links` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED NOT NULL,
  `nama` VARCHAR(100) NOT NULL,
  `url` VARCHAR(500) NOT NULL,
  `ikon` VARCHAR(50) DEFAULT 'fas fa-link',
  `warna` VARCHAR(20) DEFAULT '#0d6efd',
  `deskripsi` VARCHAR(255),
  `urutan` INT DEFAULT 99,
  `status` TINYINT(1) DEFAULT 1,
  `created_by` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Sample links
INSERT INTO `links` (`unit_id`, `nama`, `url`, `ikon`, `warna`) VALUES
(1, 'Dashboard Bakat', '#', 'fas fa-star', '#0d6efd'),
(1, 'Dashboard SPBT', '#', 'fas fa-book', '#6610f2'),
(2, 'Dashboard Sahsiah', '#', 'fas fa-shield-alt', '#dc3545'),
(2, 'Dashboard Kaunseling', '#', 'fas fa-heart', '#e91e63'),
(2, 'Dashboard 3K', '#', 'fas fa-check-circle', '#fd7e14'),
(3, 'Dashboard Asrama', '#', 'fas fa-home', '#198754'),
(3, 'Dashboard Kokurikulum', '#', 'fas fa-trophy', '#20c997');

-- ----------------------------------------
-- Table: informasi (Officer Information Posts)
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `informasi` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `pegawai_id` INT UNSIGNED NOT NULL,
  `tajuk` VARCHAR(200) NOT NULL,
  `kandungan` LONGTEXT,
  `jenis` ENUM('umum','pengumuman','pencapaian','aktiviti','dokumen') DEFAULT 'umum',
  `lampiran` VARCHAR(255),
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`pegawai_id`) REFERENCES `pegawai`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------
-- Table: gallery
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `gallery` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `tajuk` VARCHAR(200),
  `fail` VARCHAR(255) NOT NULL,
  `jenis` ENUM('gambar','video') DEFAULT 'gambar',
  `urutan` INT DEFAULT 99,
  `status` TINYINT(1) DEFAULT 1,
  `created_by` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------------------
-- Table: announcements
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED DEFAULT NULL,
  `tajuk` VARCHAR(200) NOT NULL,
  `kandungan` TEXT NOT NULL,
  `jenis` ENUM('info','warning','success','danger') DEFAULT 'info',
  `tarikh_mula` DATE,
  `tarikh_tamat` DATE,
  `status` TINYINT(1) DEFAULT 1,
  `created_by` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Sample announcement
INSERT INTO `announcements` (`tajuk`, `kandungan`, `jenis`) VALUES
('Selamat Datang ke S.P.M Dashboard', 'Portal rasmi Sektor Pembangunan Murid kini aktif. Sila log masuk untuk akses penuh.', 'info');

-- ----------------------------------------
-- Table: activity_log
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `activity_log` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `aksi` VARCHAR(100),
  `modul` VARCHAR(50),
  `rekod_id` INT UNSIGNED DEFAULT NULL,
  `deskripsi` TEXT,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(255),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------
-- Table: success_stories
-- ----------------------------------------
CREATE TABLE IF NOT EXISTS `success_stories` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `unit_id` INT UNSIGNED,
  `pegawai_id` INT UNSIGNED DEFAULT NULL,
  `tajuk` VARCHAR(200) NOT NULL,
  `kandungan` LONGTEXT,
  `gambar` VARCHAR(255),
  `tarikh` DATE,
  `status` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`unit_id`) REFERENCES `unit`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`pegawai_id`) REFERENCES `pegawai`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;
