-- SIDAK Database Structure Only
-- Version: 2.0
-- Created: 2026-01-22
-- Description: Database structure only (no data) for SIDAK (Sistem Data Kependudukan)

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS `data_penduduk` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `data_penduduk`;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- --------------------------------------------------------
-- Table structure for table `tb_pdd` (Penduduk)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_pdd` (
  `id_pend` int(11) NOT NULL AUTO_INCREMENT,
  `nik` varchar(20) NOT NULL,
  `nama` varchar(50) NOT NULL,
  `tempat_lh` varchar(50) NOT NULL,
  `tgl_lh` date NOT NULL,
  `jekel` enum('LK','PR') NOT NULL DEFAULT 'LK',
  `desa` varchar(50) NOT NULL,
  `rt` varchar(5) NOT NULL,
  `rw` varchar(5) NOT NULL,
  `agama` varchar(20) NOT NULL,
  `kawin` varchar(20) NOT NULL,
  `pekerjaan` varchar(50) NOT NULL,
  `foto_ktp` varchar(255) DEFAULT NULL,
  `status` enum('Ada','Meninggal','Pindah') NOT NULL DEFAULT 'Ada',
  `kecamatan` varchar(50) DEFAULT NULL,
  `kabupaten` varchar(50) DEFAULT NULL,
  `provinsi` varchar(50) DEFAULT NULL,
   `kewarganegaraan` varchar(20) DEFAULT 'WNI',
   `phone` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id_pend`),
  UNIQUE KEY `nik` (`nik`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_kk` (Kartu Keluarga)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_kk` (
  `id_kk` int(11) NOT NULL AUTO_INCREMENT,
  `no_kk` varchar(30) NOT NULL,
  `kepala` varchar(50) NOT NULL,
  `desa` varchar(50) NOT NULL,
  `rt` varchar(5) NOT NULL,
  `rw` varchar(5) NOT NULL,
  `kec` varchar(50) NOT NULL,
  `kab` varchar(50) NOT NULL,
  `prov` varchar(50) NOT NULL,
  `foto_kk` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_kk`),
  UNIQUE KEY `no_kk` (`no_kk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_anggota` (Anggota Keluarga)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_anggota` (
  `id_anggota` int(11) NOT NULL AUTO_INCREMENT,
  `id_kk` int(11) NOT NULL,
  `id_pend` int(11) NOT NULL,
  `hubungan` varchar(20) NOT NULL,
  PRIMARY KEY (`id_anggota`),
  KEY `id_kk` (`id_kk`),
  KEY `id_pend` (`id_pend`),
  CONSTRAINT `tb_anggota_ibfk_1` FOREIGN KEY (`id_kk`) REFERENCES `tb_kk` (`id_kk`) ON DELETE CASCADE,
  CONSTRAINT `tb_anggota_ibfk_2` FOREIGN KEY (`id_pend`) REFERENCES `tb_pdd` (`id_pend`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_lahir` (Kelahiran)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_lahir` (
  `id_lahir` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(50) NOT NULL,
  `tgl_lh` date NOT NULL,
  `jekel` enum('LK','PR') NOT NULL DEFAULT 'LK',
  `id_kk` int(11) NOT NULL,
  PRIMARY KEY (`id_lahir`),
  KEY `id_kk` (`id_kk`),
  CONSTRAINT `tb_lahir_ibfk_1` FOREIGN KEY (`id_kk`) REFERENCES `tb_kk` (`id_kk`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_mendu` (Kematian)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_mendu` (
  `id_mendu` int(11) NOT NULL AUTO_INCREMENT,
  `id_pdd` int(11) NOT NULL,
  `tgl_mendu` date NOT NULL,
  `sebab` varchar(50) NOT NULL,
  PRIMARY KEY (`id_mendu`),
  KEY `id_pdd` (`id_pdd`),
  CONSTRAINT `tb_mendu_ibfk_1` FOREIGN KEY (`id_pdd`) REFERENCES `tb_pdd` (`id_pend`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_datang` (Pendatang)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_datang` (
  `id_datang` int(11) NOT NULL AUTO_INCREMENT,
  `nik` varchar(20) NOT NULL,
  `nama_datang` varchar(50) NOT NULL,
  `jekel` enum('LK','PR') NOT NULL DEFAULT 'LK',
  `tgl_datang` date NOT NULL,
  `pelapor` int(11) NOT NULL,
  PRIMARY KEY (`id_datang`),
  KEY `pelapor` (`pelapor`),
  CONSTRAINT `tb_datang_ibfk_1` FOREIGN KEY (`pelapor`) REFERENCES `tb_pdd` (`id_pend`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_pindah` (Pindah)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_pindah` (
  `id_pindah` int(11) NOT NULL AUTO_INCREMENT,
  `id_pdd` int(11) NOT NULL,
  `tgl_pindah` date NOT NULL,
  `alasan` varchar(100) NOT NULL,
  PRIMARY KEY (`id_pindah`),
  KEY `id_pdd` (`id_pdd`),
  CONSTRAINT `tb_pindah_ibfk_1` FOREIGN KEY (`id_pdd`) REFERENCES `tb_pdd` (`id_pend`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_pengguna` (Pengguna Sistem)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_pengguna` (
  `id_pengguna` int(11) NOT NULL AUTO_INCREMENT,
  `nama_pengguna` varchar(50) NOT NULL,
  `username` varchar(30) NOT NULL,
  `password` varchar(100) NOT NULL,
  `level` enum('admin','kaur') NOT NULL DEFAULT 'kaur',
  PRIMARY KEY (`id_pengguna`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------
-- Table structure for table `tb_profil` (Profil Desa)
-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tb_profil` (
  `id_profil` int(11) NOT NULL PRIMARY KEY,
  `nama_desa` varchar(50) NOT NULL,
  `alamat` text NOT NULL,
  `kecamatan` varchar(50) NOT NULL,
  `kabupaten` varchar(50) NOT NULL,
  `provinsi` varchar(50) NOT NULL,
  `kepala_desa` varchar(50) NOT NULL,
  `nip_kades` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;