-- =========================================================
-- Skema Database: Sistem Manajemen Surat Tugas
-- Import file ini ke MySQL/MariaDB jika Anda tidak ingin
-- menjalankan migration CodeIgniter (php spark migrate).
-- =========================================================

CREATE DATABASE IF NOT EXISTS `surat_tugas_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `surat_tugas_db`;

-- Master data seksi
CREATE TABLE `seksi` (
  `id_seksi` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_seksi` VARCHAR(150) NOT NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id_seksi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Master data pegawai
CREATE TABLE `pegawai` (
  `id_pegawai` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama` VARCHAR(150) NOT NULL,
  `nip` VARCHAR(30) NOT NULL,
  `jabatan` VARCHAR(200) NOT NULL,
  `seksi_id` INT UNSIGNED NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id_pegawai`),
  KEY `seksi_id` (`seksi_id`),
  CONSTRAINT `fk_pegawai_seksi` FOREIGN KEY (`seksi_id`) REFERENCES `seksi` (`id_seksi`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pengaturan umum: kop surat & data camat (single row settings)
CREATE TABLE `pengaturan` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nama_kabupaten` VARCHAR(150) NULL,
  `nama_kecamatan` VARCHAR(150) NULL,
  `alamat_kantor` VARCHAR(255) NULL,
  `telepon` VARCHAR(50) NULL,
  `website` VARCHAR(150) NULL,
  `email` VARCHAR(150) NULL,
  `nama_camat` VARCHAR(150) NULL,
  `nip_camat` VARCHAR(30) NULL,
  `pangkat_camat` VARCHAR(150) NULL,
  `kode_nomor_surat` VARCHAR(100) NULL COMMENT 'mis. 800.1.11.1',
  `kode_klasifikasi` VARCHAR(50) NULL COMMENT 'mis. Sekret',
  `logo` VARCHAR(255) NULL,
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Surat tugas
CREATE TABLE `surat_tugas` (
  `id_surat_tugas` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nomor` VARCHAR(150) NOT NULL,
  `dasar` TEXT NULL,
  `untuk` TEXT NULL COMMENT 'uraian tugas / keperluan',
  `hari` VARCHAR(50) NULL,
  `waktu` VARCHAR(100) NULL,
  `tanggal` DATE NULL COMMENT 'tanggal pelaksanaan tugas',
  `tempat` VARCHAR(255) NULL COMMENT 'tempat pelaksanaan tugas',
  `tempat_ttd` VARCHAR(150) NULL COMMENT 'dikeluarkan di',
  `tanggal_ttd` DATE NULL COMMENT 'tanggal surat ditandatangani',
  `status` VARCHAR(20) NOT NULL DEFAULT 'aktif',
  `created_at` DATETIME NULL,
  `updated_at` DATETIME NULL,
  PRIMARY KEY (`id_surat_tugas`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pivot pegawai yang ditugaskan dalam 1 surat tugas (mendukung banyak pegawai)
CREATE TABLE `surat_tugas_pegawai` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `surat_tugas_id` INT UNSIGNED NOT NULL,
  `pegawai_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  KEY `surat_tugas_id` (`surat_tugas_id`),
  KEY `pegawai_id` (`pegawai_id`),
  CONSTRAINT `fk_stp_surat` FOREIGN KEY (`surat_tugas_id`) REFERENCES `surat_tugas` (`id_surat_tugas`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_stp_pegawai` FOREIGN KEY (`pegawai_id`) REFERENCES `pegawai` (`id_pegawai`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data pengaturan default (contoh, silakan diedit lewat menu Pengaturan)
INSERT INTO `pengaturan`
(`nama_kabupaten`, `nama_kecamatan`, `alamat_kantor`, `telepon`, `website`, `email`,
 `nama_camat`, `nip_camat`, `pangkat_camat`, `kode_nomor_surat`, `kode_klasifikasi`, `created_at`, `updated_at`)
VALUES
('PEMERINTAH KABUPATEN BOGOR', 'KECAMATAN TANJUNGSARI',
 'Jl. Kecamatan Nomor 1 Tanjungsari - Bogor 16840', '(021) Fax.(021)8996083',
 'https://kecamatantanjungsari.bogorkab.go.id/', 'tanjungsaribogorkab@gmail.com',
 'TOTOK SUPRIYADI, S.AP.,MM', '197311101992011001', 'Pembina Tk. I',
 '800.1.11.1', 'Sekret', NOW(), NOW());