-- =====================================================================
-- SIMSURAT - Sistem Informasi Manajemen Surat
-- DDL database (dihasilkan dari migration Laravel, MySQL 8.4)
--
-- Cara yang dianjurkan tetap: php artisan migrate --seed
-- File ini disediakan sebagai referensi / instalasi manual tanpa artisan.
-- Data referensi awal (status, role, permission) ada di database/seeders.
-- =====================================================================

CREATE DATABASE IF NOT EXISTS db_simsurat
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE db_simsurat;

SET FOREIGN_KEY_CHECKS = 0;
CREATE TABLE IF NOT EXISTS `migrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_aksi_disposisi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_aksi` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_aksi` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_aksi_disposisi_kode_aksi_unique` (`kode_aksi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_bidang_unit` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_bidang` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_bidang` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_bidang_unit_kode_bidang_unique` (`kode_bidang`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_derajat_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_derajat` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_derajat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keterangan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_derajat_surat_kode_derajat_unique` (`kode_derajat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_format_nomor` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_format` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_format` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `format_nomor` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `panjang_nomor` tinyint unsigned NOT NULL DEFAULT '3' COMMENT 'Jumlah digit dengan nol di depan',
  `reset_periode` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'TAHUNAN' COMMENT 'TAHUNAN | BULANAN | TIDAK',
  `nomor_terakhir` int unsigned NOT NULL DEFAULT '0',
  `periode_terakhir` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'YYYY atau YYYY-MM',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_format_nomor_kode_format_unique` (`kode_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_jabatan` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_jabatan` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_jabatan` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `level` tinyint unsigned NOT NULL DEFAULT '9' COMMENT '1 = pimpinan tertinggi',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_jabatan_kode_jabatan_unique` (`kode_jabatan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_jenis_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_jenis` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_jenis` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_jenis_surat_kode_jenis_unique` (`kode_jenis`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_jenis_tindak_lanjut` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_tindak_lanjut` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_tindak_lanjut` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_jenis_tindak_lanjut_kode_tindak_lanjut_unique` (`kode_tindak_lanjut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_jenis_unit` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_jenis_unit` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_jenis_unit` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_jenis_unit_kode_jenis_unit_unique` (`kode_jenis_unit`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_kategori_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_kategori` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_kategori` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_kategori_surat_kode_kategori_unique` (`kode_kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_klasifikasi_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_klasifikasi` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_klasifikasi` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_klasifikasi_naskah_kode` (`kode_klasifikasi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_status_disposisi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary',
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_status_disposisi_kode_status_unique` (`kode_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_status_distribusi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary',
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_status_distribusi_kode_status_unique` (`kode_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_status_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `jenis_transaksi` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | SURAT_KELUAR',
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary' COMMENT 'Kelas warna badge Bootstrap',
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_status_surat_jenis_transaksi_kode_status_unique` (`jenis_transaksi`,`kode_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_status_tindak_lanjut` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_status` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `warna` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'secondary',
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_status_tindak_lanjut_kode_status_unique` (`kode_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_ref_unit` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_unit` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_unit` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_jenis_unit` bigint unsigned NOT NULL,
  `id_unit_induk` bigint unsigned DEFAULT NULL,
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_ref_unit_kode_unit_unique` (`kode_unit`),
  KEY `tb_ref_unit_id_jenis_unit_foreign` (`id_jenis_unit`),
  KEY `tb_ref_unit_id_unit_induk_foreign` (`id_unit_induk`),
  KEY `tb_ref_unit_aktif_nama_unit_index` (`aktif`,`nama_unit`),
  CONSTRAINT `tb_ref_unit_id_jenis_unit_foreign` FOREIGN KEY (`id_jenis_unit`) REFERENCES `tb_ref_jenis_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_ref_unit_id_unit_induk_foreign` FOREIGN KEY (`id_unit_induk`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_cache` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `value` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `tb_sys_cache_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_cache_lock` (
  `key` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `owner` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expiration` bigint NOT NULL,
  PRIMARY KEY (`key`),
  KEY `tb_sys_cache_lock_expiration_index` (`expiration`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_failed_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int NOT NULL,
  `pending_jobs` int NOT NULL,
  `failed_jobs` int NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci,
  `cancelled_at` int DEFAULT NULL,
  `created_at` int NOT NULL,
  `finished_at` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_jobs` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint unsigned NOT NULL,
  `reserved_at` int unsigned DEFAULT NULL,
  `available_at` int unsigned NOT NULL,
  `created_at` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_sys_jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_log_aktivitas` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_user` bigint unsigned DEFAULT NULL,
  `aktivitas` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `modul` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data_id` bigint unsigned DEFAULT NULL,
  `data_tambahan` json DEFAULT NULL COMMENT 'Nilai lama/baru atau konteks tambahan',
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tb_sys_log_aktivitas_modul_data_id_index` (`modul`,`data_id`),
  KEY `tb_sys_log_aktivitas_id_user_created_at_index` (`id_user`,`created_at`),
  KEY `tb_sys_log_aktivitas_created_at_index` (`created_at`),
  CONSTRAINT `tb_sys_log_aktivitas_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_tb_sys_log_aktivitas_tolak_update` BEFORE UPDATE ON `tb_sys_log_aktivitas` FOR EACH ROW SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'Data tb_sys_log_aktivitas bersifat append-only dan tidak dapat diubah atau dihapus.' */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_tb_sys_log_aktivitas_tolak_delete` BEFORE DELETE ON `tb_sys_log_aktivitas` FOR EACH ROW SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'Data tb_sys_log_aktivitas bersifat append-only dan tidak dapat diubah atau dihapus.' */;;
DELIMITER ;
CREATE TABLE IF NOT EXISTS `tb_sys_menu` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_induk` bigint unsigned DEFAULT NULL,
  `jenis` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'header | grup | menu',
  `judul` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ikon` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `route` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `urutan` smallint unsigned NOT NULL DEFAULT '0',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `terkunci` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Item sistem yang tidak boleh dihapus/disembunyikan dari Super Admin',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_sys_menu_id_induk_urutan_index` (`id_induk`,`urutan`),
  CONSTRAINT `tb_sys_menu_id_induk_foreign` FOREIGN KEY (`id_induk`) REFERENCES `tb_sys_menu` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_menu_role` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_menu` bigint unsigned NOT NULL,
  `id_role` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_menu_role_id_menu_id_role_unique` (`id_menu`,`id_role`),
  KEY `tb_sys_menu_role_id_role_foreign` (`id_role`),
  CONSTRAINT `tb_sys_menu_role_id_menu_foreign` FOREIGN KEY (`id_menu`) REFERENCES `tb_sys_menu` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tb_sys_menu_role_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_notifikasi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_user` bigint unsigned NOT NULL,
  `judul` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pesan` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `jenis_notifikasi` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | DISPOSISI | DISTRIBUSI | TINDAK_LANJUT | PENGINGAT',
  `url_tujuan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dibaca` tinyint(1) NOT NULL DEFAULT '0',
  `tanggal_dibaca` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tb_sys_notifikasi_id_user_dibaca_id_index` (`id_user`,`dibaca`,`id`),
  CONSTRAINT `tb_sys_notifikasi_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_pengaturan` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kunci` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nilai` text COLLATE utf8mb4_unicode_ci,
  `tipe` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'string' COMMENT 'string | integer | boolean | json',
  `grup` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'umum',
  `keterangan` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_pengaturan_kunci_unique` (`kunci`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_pengguna` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `username` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_unit` bigint unsigned NOT NULL,
  `id_jabatan` bigint unsigned DEFAULT NULL,
  `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `foto` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notifikasi_suara` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Preferensi suara notifikasi',
  `aktif` tinyint(1) NOT NULL DEFAULT '1',
  `wajib_ganti_password` tinyint(1) NOT NULL DEFAULT '0',
  `password_diubah_at` timestamp NULL DEFAULT NULL,
  `terakhir_login` timestamp NULL DEFAULT NULL,
  `terakhir_login_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_pengguna_username_unique` (`username`),
  UNIQUE KEY `tb_sys_pengguna_email_unique` (`email`),
  KEY `tb_sys_pengguna_id_jabatan_foreign` (`id_jabatan`),
  KEY `tb_sys_pengguna_id_unit_aktif_index` (`id_unit`,`aktif`),
  CONSTRAINT `tb_sys_pengguna_id_jabatan_foreign` FOREIGN KEY (`id_jabatan`) REFERENCES `tb_ref_jabatan` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_sys_pengguna_id_unit_foreign` FOREIGN KEY (`id_unit`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_pengguna_role` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_pengguna` bigint unsigned NOT NULL,
  `id_role` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_pengguna_role_id_pengguna_id_role_unique` (`id_pengguna`,`id_role`),
  KEY `tb_sys_pengguna_role_id_role_foreign` (`id_role`),
  CONSTRAINT `tb_sys_pengguna_role_id_pengguna_foreign` FOREIGN KEY (`id_pengguna`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tb_sys_pengguna_role_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_permission` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_permission` varchar(80) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Format: modul.aksi',
  `nama_permission` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `modul` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_permission_kode_permission_unique` (`kode_permission`),
  KEY `tb_sys_permission_modul_index` (`modul`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_reset_password` (
  `email` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_role` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `kode_role` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nama_role` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_role_kode_role_unique` (`kode_role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_role_permission` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_role` bigint unsigned NOT NULL,
  `id_permission` bigint unsigned NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_role_permission_id_role_id_permission_unique` (`id_role`,`id_permission`),
  KEY `tb_sys_role_permission_id_permission_foreign` (`id_permission`),
  CONSTRAINT `tb_sys_role_permission_id_permission_foreign` FOREIGN KEY (`id_permission`) REFERENCES `tb_sys_permission` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tb_sys_role_permission_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_role_widget` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_role` bigint unsigned NOT NULL,
  `kode_widget` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_sys_role_widget_id_role_kode_widget_unique` (`id_role`,`kode_widget`),
  CONSTRAINT `tb_sys_role_widget_id_role_foreign` FOREIGN KEY (`id_role`) REFERENCES `tb_sys_role` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_sys_sesi` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_sys_sesi_user_id_index` (`user_id`),
  KEY `tb_sys_sesi_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_disposisi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_surat_masuk` bigint unsigned NOT NULL,
  `id_penerima_induk` bigint unsigned DEFAULT NULL COMMENT 'Baris penerima yang meneruskan disposisi ini',
  `nomor_disposisi` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_user_pemberi` bigint unsigned NOT NULL,
  `tanggal_disposisi` datetime NOT NULL,
  `instruksi` text COLLATE utf8mb4_unicode_ci COMMENT 'Instruksi umum untuk seluruh penerima',
  `tanggal_batas` date DEFAULT NULL,
  `id_status` bigint unsigned NOT NULL,
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_disposisi_nomor_disposisi_unique` (`nomor_disposisi`),
  KEY `tb_trx_disposisi_id_user_pemberi_foreign` (`id_user_pemberi`),
  KEY `tb_trx_disposisi_id_status_foreign` (`id_status`),
  KEY `tb_trx_disposisi_id_surat_masuk_tanggal_disposisi_index` (`id_surat_masuk`,`tanggal_disposisi`),
  KEY `tb_trx_disposisi_tanggal_batas_index` (`tanggal_batas`),
  KEY `tb_trx_disposisi_id_penerima_induk_foreign` (`id_penerima_induk`),
  CONSTRAINT `tb_trx_disposisi_id_penerima_induk_foreign` FOREIGN KEY (`id_penerima_induk`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_disposisi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_id_user_pemberi_foreign` FOREIGN KEY (`id_user_pemberi`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_disposisi_aksi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_disposisi` bigint unsigned NOT NULL,
  `id_aksi_disposisi` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_disposisi_aksi_id_disposisi_id_aksi_disposisi_unique` (`id_disposisi`,`id_aksi_disposisi`),
  KEY `tb_trx_disposisi_aksi_id_aksi_disposisi_foreign` (`id_aksi_disposisi`),
  CONSTRAINT `tb_trx_disposisi_aksi_id_aksi_disposisi_foreign` FOREIGN KEY (`id_aksi_disposisi`) REFERENCES `tb_ref_aksi_disposisi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_aksi_id_disposisi_foreign` FOREIGN KEY (`id_disposisi`) REFERENCES `tb_trx_disposisi` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_disposisi_penerima` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_disposisi` bigint unsigned NOT NULL,
  `id_user_penerima` bigint unsigned DEFAULT NULL,
  `id_unit_penerima` bigint unsigned NOT NULL,
  `id_user_diterima` bigint unsigned DEFAULT NULL,
  `id_status` bigint unsigned NOT NULL,
  `instruksi` text COLLATE utf8mb4_unicode_ci COMMENT 'Instruksi khusus penerima; kosong = instruksi umum disposisi',
  `tanggal_batas` date DEFAULT NULL COMMENT 'Batas waktu khusus penerima ini',
  `tanggal_diterima` datetime DEFAULT NULL,
  `tanggal_dibaca` datetime DEFAULT NULL,
  `tanggal_mulai_proses` datetime DEFAULT NULL,
  `tanggal_selesai` datetime DEFAULT NULL,
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_trx_disposisi_penerima_id_disposisi_foreign` (`id_disposisi`),
  KEY `tb_trx_disposisi_penerima_id_user_diterima_foreign` (`id_user_diterima`),
  KEY `tb_trx_disposisi_penerima_id_status_foreign` (`id_status`),
  KEY `tb_trx_disposisi_penerima_id_user_penerima_id_status_index` (`id_user_penerima`,`id_status`),
  KEY `tb_trx_disposisi_penerima_id_unit_penerima_id_status_index` (`id_unit_penerima`,`id_status`),
  KEY `tb_trx_disposisi_penerima_tanggal_batas_index` (`tanggal_batas`),
  CONSTRAINT `tb_trx_disposisi_penerima_id_disposisi_foreign` FOREIGN KEY (`id_disposisi`) REFERENCES `tb_trx_disposisi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_penerima_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_disposisi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_penerima_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_penerima_id_user_diterima_foreign` FOREIGN KEY (`id_user_diterima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_penerima_id_user_penerima_foreign` FOREIGN KEY (`id_user_penerima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_disposisi_penerima_aksi` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_disposisi_penerima` bigint unsigned NOT NULL,
  `id_aksi_disposisi` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_penerima_aksi` (`id_disposisi_penerima`,`id_aksi_disposisi`),
  KEY `tb_trx_disposisi_penerima_aksi_id_aksi_disposisi_foreign` (`id_aksi_disposisi`),
  CONSTRAINT `tb_trx_disposisi_penerima_aksi_id_aksi_disposisi_foreign` FOREIGN KEY (`id_aksi_disposisi`) REFERENCES `tb_ref_aksi_disposisi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_disposisi_penerima_aksi_id_disposisi_penerima_foreign` FOREIGN KEY (`id_disposisi_penerima`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_distribusi_surat_keluar` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_surat_keluar` bigint unsigned NOT NULL,
  `id_user_penerima` bigint unsigned DEFAULT NULL,
  `id_unit_penerima` bigint unsigned NOT NULL,
  `id_user_pengirim` bigint unsigned NOT NULL,
  `id_user_diterima` bigint unsigned DEFAULT NULL,
  `tanggal_distribusi` datetime NOT NULL,
  `tanggal_dibaca` datetime DEFAULT NULL,
  `tanggal_diterima` datetime DEFAULT NULL COMMENT 'Konfirmasi penerimaan',
  `id_status` bigint unsigned NOT NULL,
  `id_surat_masuk` bigint unsigned DEFAULT NULL COMMENT 'Naskah masuk internal di unit tujuan',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_trx_distribusi_surat_keluar_id_user_pengirim_foreign` (`id_user_pengirim`),
  KEY `tb_trx_distribusi_surat_keluar_id_user_diterima_foreign` (`id_user_diterima`),
  KEY `tb_trx_distribusi_surat_keluar_id_status_foreign` (`id_status`),
  KEY `tb_trx_distribusi_surat_keluar_id_user_penerima_id_status_index` (`id_user_penerima`,`id_status`),
  KEY `tb_trx_distribusi_surat_keluar_id_unit_penerima_id_status_index` (`id_unit_penerima`,`id_status`),
  KEY `tb_trx_distribusi_surat_keluar_id_surat_keluar_index` (`id_surat_keluar`),
  KEY `tb_trx_distribusi_surat_keluar_id_surat_masuk_foreign` (`id_surat_masuk`),
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_distribusi` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_diterima_foreign` FOREIGN KEY (`id_user_diterima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_penerima_foreign` FOREIGN KEY (`id_user_penerima`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_distribusi_surat_keluar_id_user_pengirim_foreign` FOREIGN KEY (`id_user_pengirim`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_pengirim` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama_pengirim` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `instansi` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alamat` text COLLATE utf8mb4_unicode_ci,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_trx_pengirim_nama_pengirim_index` (`nama_pengirim`),
  KEY `tb_trx_pengirim_instansi_index` (`instansi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_riwayat_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `jenis_transaksi` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SURAT_MASUK | SURAT_KELUAR',
  `id_surat_masuk` bigint unsigned DEFAULT NULL,
  `id_surat_keluar` bigint unsigned DEFAULT NULL,
  `status_sebelumnya` bigint unsigned DEFAULT NULL,
  `status_baru` bigint unsigned DEFAULT NULL,
  `aktivitas` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Kode aktivitas, mis. DIAGENDAKAN, DISPOSISI_DIBUAT',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `referensi_tipe` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referensi_id` bigint unsigned DEFAULT NULL,
  `id_user` bigint unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tb_trx_riwayat_surat_status_sebelumnya_foreign` (`status_sebelumnya`),
  KEY `tb_trx_riwayat_surat_status_baru_foreign` (`status_baru`),
  KEY `tb_trx_riwayat_surat_id_user_foreign` (`id_user`),
  KEY `tb_trx_riwayat_surat_id_surat_masuk_created_at_index` (`id_surat_masuk`,`created_at`),
  KEY `tb_trx_riwayat_surat_id_surat_keluar_created_at_index` (`id_surat_keluar`,`created_at`),
  KEY `tb_trx_riwayat_surat_aktivitas_index` (`aktivitas`),
  CONSTRAINT `tb_trx_riwayat_surat_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_riwayat_surat_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_riwayat_surat_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_riwayat_surat_status_baru_foreign` FOREIGN KEY (`status_baru`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_riwayat_surat_status_sebelumnya_foreign` FOREIGN KEY (`status_sebelumnya`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `chk_riwayat_jenis_transaksi` CHECK ((((`jenis_transaksi` = _utf8mb4'SURAT_MASUK') and (`id_surat_masuk` is not null) and (`id_surat_keluar` is null)) or ((`jenis_transaksi` = _utf8mb4'SURAT_KELUAR') and (`id_surat_keluar` is not null) and (`id_surat_masuk` is null))))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_tb_trx_riwayat_surat_tolak_update` BEFORE UPDATE ON `tb_trx_riwayat_surat` FOR EACH ROW SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'Data tb_trx_riwayat_surat bersifat append-only dan tidak dapat diubah atau dihapus.' */;;
DELIMITER ;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_tb_trx_riwayat_surat_tolak_delete` BEFORE DELETE ON `tb_trx_riwayat_surat` FOR EACH ROW SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'Data tb_trx_riwayat_surat bersifat append-only dan tidak dapat diubah atau dihapus.' */;;
DELIMITER ;
CREATE TABLE IF NOT EXISTS `tb_trx_surat_keluar` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tahun_agenda` smallint unsigned DEFAULT NULL,
  `urutan_agenda` int unsigned DEFAULT NULL,
  `nomor_agenda` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `nomor_surat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_kategori_surat` bigint unsigned NOT NULL,
  `tanggal_surat` date NOT NULL,
  `tanggal_agenda` datetime DEFAULT NULL,
  `id_tujuan` bigint unsigned NOT NULL,
  `perihal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ringkasan` text COLLATE utf8mb4_unicode_ci,
  `lampiran` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_penandatangan` bigint unsigned NOT NULL,
  `id_unit_pengirim` bigint unsigned NOT NULL,
  `id_status` bigint unsigned NOT NULL,
  `id_user_input` bigint unsigned NOT NULL,
  `tanggal_selesai` datetime DEFAULT NULL,
  `tanggal_arsip` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `id_klasifikasi` bigint unsigned NOT NULL,
  `id_bidang_unit` bigint unsigned NOT NULL,
  `id_derajat` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_surat_keluar_nomor_surat_unique` (`nomor_surat`),
  UNIQUE KEY `uq_surat_keluar_agenda` (`tahun_agenda`,`urutan_agenda`),
  UNIQUE KEY `tb_trx_surat_keluar_nomor_agenda_unique` (`nomor_agenda`),
  KEY `tb_trx_surat_keluar_id_tujuan_foreign` (`id_tujuan`),
  KEY `tb_trx_surat_keluar_id_penandatangan_foreign` (`id_penandatangan`),
  KEY `tb_trx_surat_keluar_id_unit_pengirim_foreign` (`id_unit_pengirim`),
  KEY `tb_trx_surat_keluar_id_user_input_foreign` (`id_user_input`),
  KEY `tb_trx_surat_keluar_tanggal_surat_index` (`tanggal_surat`),
  KEY `tb_trx_surat_keluar_id_status_tanggal_surat_index` (`id_status`,`tanggal_surat`),
  KEY `tb_trx_surat_keluar_perihal_index` (`perihal`),
  KEY `tb_trx_surat_keluar_id_kategori_surat_foreign` (`id_kategori_surat`),
  KEY `tb_trx_surat_keluar_id_klasifikasi_foreign` (`id_klasifikasi`),
  KEY `tb_trx_surat_keluar_id_bidang_unit_foreign` (`id_bidang_unit`),
  KEY `tb_trx_surat_keluar_id_derajat_foreign` (`id_derajat`),
  CONSTRAINT `tb_trx_surat_keluar_id_bidang_unit_foreign` FOREIGN KEY (`id_bidang_unit`) REFERENCES `tb_ref_bidang_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_derajat_foreign` FOREIGN KEY (`id_derajat`) REFERENCES `tb_ref_derajat_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_kategori_surat_foreign` FOREIGN KEY (`id_kategori_surat`) REFERENCES `tb_ref_kategori_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_klasifikasi_foreign` FOREIGN KEY (`id_klasifikasi`) REFERENCES `tb_ref_klasifikasi_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_penandatangan_foreign` FOREIGN KEY (`id_penandatangan`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_tujuan_foreign` FOREIGN KEY (`id_tujuan`) REFERENCES `tb_trx_tujuan_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_unit_pengirim_foreign` FOREIGN KEY (`id_unit_pengirim`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_id_user_input_foreign` FOREIGN KEY (`id_user_input`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_surat_keluar_file` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_surat_keluar` bigint unsigned NOT NULL,
  `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ukuran_file` bigint unsigned NOT NULL,
  `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `versi` smallint unsigned NOT NULL DEFAULT '1',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `id_user_upload` bigint unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_surat_keluar_file_id_surat_keluar_versi_unique` (`id_surat_keluar`,`versi`),
  UNIQUE KEY `tb_trx_surat_keluar_file_path_file_unique` (`path_file`),
  KEY `tb_trx_surat_keluar_file_id_user_upload_foreign` (`id_user_upload`),
  CONSTRAINT `tb_trx_surat_keluar_file_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_keluar_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_surat_masuk` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tahun_agenda` smallint unsigned DEFAULT NULL,
  `urutan_agenda` int unsigned DEFAULT NULL,
  `nomor_agenda` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `buku_agenda` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PUSAT' COMMENT 'PUSAT = sekretariat yayasan; U{id_unit} = buku agenda unit tujuan',
  `nomor_surat` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_kategori_surat` bigint unsigned NOT NULL,
  `tanggal_surat` date NOT NULL,
  `tanggal_terima` date NOT NULL,
  `id_pengirim` bigint unsigned NOT NULL,
  `perihal` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ringkasan` text COLLATE utf8mb4_unicode_ci,
  `lampiran` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `id_status` bigint unsigned NOT NULL,
  `id_unit_penerima` bigint unsigned NOT NULL,
  `id_surat_keluar` bigint unsigned DEFAULT NULL COMMENT 'Diisi bila naskah masuk ini berasal dari naskah keluar internal',
  `id_user_tujuan` bigint unsigned DEFAULT NULL COMMENT 'Pimpinan unit tujuan naskah internal',
  `id_user_input` bigint unsigned NOT NULL,
  `tanggal_agenda` datetime DEFAULT NULL,
  `tanggal_selesai` datetime DEFAULT NULL,
  `tanggal_arsip` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `id_klasifikasi` bigint unsigned NOT NULL,
  `id_bidang_unit` bigint unsigned NOT NULL,
  `id_derajat` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_surat_masuk_pengirim_nomor_buku` (`id_pengirim`,`nomor_surat`,`buku_agenda`),
  UNIQUE KEY `uq_surat_masuk_agenda` (`tahun_agenda`,`urutan_agenda`),
  UNIQUE KEY `uq_surat_masuk_buku_nomor_agenda` (`buku_agenda`,`nomor_agenda`),
  UNIQUE KEY `uq_surat_masuk_keluar_unit` (`id_surat_keluar`,`id_unit_penerima`),
  KEY `tb_trx_surat_masuk_id_unit_penerima_foreign` (`id_unit_penerima`),
  KEY `tb_trx_surat_masuk_id_user_input_foreign` (`id_user_input`),
  KEY `tb_trx_surat_masuk_nomor_surat_index` (`nomor_surat`),
  KEY `tb_trx_surat_masuk_tanggal_surat_index` (`tanggal_surat`),
  KEY `tb_trx_surat_masuk_tanggal_terima_index` (`tanggal_terima`),
  KEY `tb_trx_surat_masuk_id_status_tanggal_terima_index` (`id_status`,`tanggal_terima`),
  KEY `tb_trx_surat_masuk_perihal_index` (`perihal`),
  KEY `tb_trx_surat_masuk_id_kategori_surat_foreign` (`id_kategori_surat`),
  KEY `tb_trx_surat_masuk_id_klasifikasi_foreign` (`id_klasifikasi`),
  KEY `tb_trx_surat_masuk_id_bidang_unit_foreign` (`id_bidang_unit`),
  KEY `tb_trx_surat_masuk_id_derajat_foreign` (`id_derajat`),
  KEY `tb_trx_surat_masuk_id_user_tujuan_foreign` (`id_user_tujuan`),
  CONSTRAINT `tb_trx_surat_masuk_id_bidang_unit_foreign` FOREIGN KEY (`id_bidang_unit`) REFERENCES `tb_ref_bidang_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_derajat_foreign` FOREIGN KEY (`id_derajat`) REFERENCES `tb_ref_derajat_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_kategori_surat_foreign` FOREIGN KEY (`id_kategori_surat`) REFERENCES `tb_ref_kategori_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_klasifikasi_foreign` FOREIGN KEY (`id_klasifikasi`) REFERENCES `tb_ref_klasifikasi_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_pengirim_foreign` FOREIGN KEY (`id_pengirim`) REFERENCES `tb_trx_pengirim` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_surat` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_surat_keluar_foreign` FOREIGN KEY (`id_surat_keluar`) REFERENCES `tb_trx_surat_keluar` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_unit_penerima_foreign` FOREIGN KEY (`id_unit_penerima`) REFERENCES `tb_ref_unit` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_user_input_foreign` FOREIGN KEY (`id_user_input`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_id_user_tujuan_foreign` FOREIGN KEY (`id_user_tujuan`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_surat_masuk_file` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_surat_masuk` bigint unsigned NOT NULL,
  `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Nama asli file, hanya untuk ditampilkan',
  `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Path UUID di storage private',
  `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ukuran_file` bigint unsigned NOT NULL COMMENT 'Byte',
  `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'SHA-256 untuk verifikasi integritas',
  `versi` smallint unsigned NOT NULL DEFAULT '1',
  `keterangan` text COLLATE utf8mb4_unicode_ci,
  `id_user_upload` bigint unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_surat_masuk_file_id_surat_masuk_versi_unique` (`id_surat_masuk`,`versi`),
  UNIQUE KEY `tb_trx_surat_masuk_file_path_file_unique` (`path_file`),
  KEY `tb_trx_surat_masuk_file_id_user_upload_foreign` (`id_user_upload`),
  CONSTRAINT `tb_trx_surat_masuk_file_id_surat_masuk_foreign` FOREIGN KEY (`id_surat_masuk`) REFERENCES `tb_trx_surat_masuk` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_surat_masuk_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_tindak_lanjut` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_disposisi_penerima` bigint unsigned NOT NULL,
  `id_jenis_tindak_lanjut` bigint unsigned DEFAULT NULL,
  `tanggal_tindak_lanjut` datetime NOT NULL,
  `uraian_tindak_lanjut` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `hasil_tindak_lanjut` text COLLATE utf8mb4_unicode_ci,
  `id_user` bigint unsigned NOT NULL,
  `id_status` bigint unsigned NOT NULL,
  `id_user_verifikator` bigint unsigned DEFAULT NULL,
  `tanggal_verifikasi` datetime DEFAULT NULL,
  `catatan_verifikasi` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_trx_tindak_lanjut_id_jenis_tindak_lanjut_foreign` (`id_jenis_tindak_lanjut`),
  KEY `tb_trx_tindak_lanjut_id_user_foreign` (`id_user`),
  KEY `tb_trx_tindak_lanjut_id_user_verifikator_foreign` (`id_user_verifikator`),
  KEY `idx_tl_penerima_tanggal` (`id_disposisi_penerima`,`tanggal_tindak_lanjut`),
  KEY `tb_trx_tindak_lanjut_id_status_index` (`id_status`),
  CONSTRAINT `tb_trx_tindak_lanjut_id_disposisi_penerima_foreign` FOREIGN KEY (`id_disposisi_penerima`) REFERENCES `tb_trx_disposisi_penerima` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_tindak_lanjut_id_jenis_tindak_lanjut_foreign` FOREIGN KEY (`id_jenis_tindak_lanjut`) REFERENCES `tb_ref_jenis_tindak_lanjut` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_tindak_lanjut_id_status_foreign` FOREIGN KEY (`id_status`) REFERENCES `tb_ref_status_tindak_lanjut` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_tindak_lanjut_id_user_foreign` FOREIGN KEY (`id_user`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_tindak_lanjut_id_user_verifikator_foreign` FOREIGN KEY (`id_user_verifikator`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_tindak_lanjut_file` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `id_tindak_lanjut` bigint unsigned NOT NULL,
  `nama_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `path_file` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `tipe_file` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ukuran_file` bigint unsigned NOT NULL,
  `hash_file` char(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_user_upload` bigint unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tb_trx_tindak_lanjut_file_path_file_unique` (`path_file`),
  KEY `tb_trx_tindak_lanjut_file_id_tindak_lanjut_foreign` (`id_tindak_lanjut`),
  KEY `tb_trx_tindak_lanjut_file_id_user_upload_foreign` (`id_user_upload`),
  CONSTRAINT `tb_trx_tindak_lanjut_file_id_tindak_lanjut_foreign` FOREIGN KEY (`id_tindak_lanjut`) REFERENCES `tb_trx_tindak_lanjut` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `tb_trx_tindak_lanjut_file_id_user_upload_foreign` FOREIGN KEY (`id_user_upload`) REFERENCES `tb_sys_pengguna` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `tb_trx_tujuan_surat` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `nama_tujuan` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `instansi` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alamat` text COLLATE utf8mb4_unicode_ci,
  `email` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `telepon` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_trx_tujuan_surat_nama_tujuan_index` (`nama_tujuan`),
  KEY `tb_trx_tujuan_surat_instansi_index` (`instansi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



SET FOREIGN_KEY_CHECKS = 1;