-- ==========================================================
-- SITEGAR - Sistem Informasi Demografis & Keuangan RW
-- Database Schema for MySQL / phpMyAdmin
-- ==========================================================

START TRANSACTION;

-- --------------------------------------------------------
-- 2. Tabel Warga
-- --------------------------------------------------------
CREATE TABLE `warga` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nik` varchar(16) NOT NULL UNIQUE,
  `nama` varchar(255) NOT NULL,
  `jenis_kelamin` enum('L','P') NOT NULL,
  `tempat_lahir` varchar(255) NOT NULL,
  `tanggal_lahir` date NOT NULL,
  `alamat` text NOT NULL,
  `rt` varchar(3) NOT NULL,
  `rw` varchar(3) NOT NULL DEFAULT '8',
  `status_warga` enum('tetap','kontrak','pendatang') NOT NULL,
  `status_pernikahan` enum('belum_menikah','menikah','cerai') NOT NULL,
  `pekerjaan` varchar(255) DEFAULT NULL,
  `no_hp` varchar(20) DEFAULT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- 3. Tabel Kartu Keluarga
-- --------------------------------------------------------
CREATE TABLE `kartu_keluarga` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `no_kk` varchar(16) NOT NULL UNIQUE,
  `kepala_keluarga` bigint(20) UNSIGNED NOT NULL,
  `alamat` text NOT NULL,
  `rt` varchar(3) NOT NULL,
  `rw` varchar(3) NOT NULL DEFAULT '8',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`kepala_keluarga`) REFERENCES `warga` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- 4. Tabel Anggota KK
-- --------------------------------------------------------
CREATE TABLE `anggota_kk` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `kartu_keluarga_id` bigint(20) UNSIGNED NOT NULL,
  `warga_id` bigint(20) UNSIGNED NOT NULL,
  `hubungan_keluarga` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`kartu_keluarga_id`) REFERENCES `kartu_keluarga` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`warga_id`) REFERENCES `warga` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------
-- 5. Tabel Transaksi (Keuangan)
-- --------------------------------------------------------
CREATE TABLE `transaksi` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tanggal` date NOT NULL,
  `judul` varchar(255) NOT NULL,
  `kategori` varchar(255) NOT NULL,
  `jenis` enum('pemasukan','pengeluaran') NOT NULL,
  `jumlah` decimal(15,2) NOT NULL,
  `rt` varchar(3) DEFAULT NULL,
  `pencatat` varchar(255) DEFAULT NULL,
  `keterangan` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;
