-- Database Schema for GP Ansor Banyumas Member Database
-- Run this SQL in your MySQL database

-- Create database
CREATE DATABASE IF NOT EXISTS `ansor_banyumas` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `ansor_banyumas`;

-- Users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL UNIQUE,
    `password` VARCHAR(255) NOT NULL,
    `role` ENUM('admin', 'admin_rayon', 'admin_cabang', 'anggota') DEFAULT 'anggota',
    `is_active` TINYINT(1) DEFAULT 1,
    `last_login` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_username` (`username`),
    INDEX `idx_email` (`email`),
    INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Anggota table
CREATE TABLE IF NOT EXISTS `anggota` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `no_anggota` VARCHAR(20) NOT NULL UNIQUE,
    `no_kta` VARCHAR(20) NULL UNIQUE,
    `nik` VARCHAR(16) NOT NULL UNIQUE,
    `no_ktp` VARCHAR(16) NULL,
    `nama_anggota` VARCHAR(100) NOT NULL,
    `nama_panggilan` VARCHAR(50) NULL,
    `tempat_lahir` VARCHAR(50) NULL,
    `tanggal_lahir` DATE NULL,
    `jenis_kelamin` ENUM('L', 'P') NOT NULL,
    `agama` ENUM('Islam', 'Kristen', 'Katholik', 'Hindu', 'Buddha', 'Khonghucu') DEFAULT 'Islam',
    `status_perkawinan` ENUM('Belum Kawin', 'Kawin', 'Cerai Hidup', 'Cerai Mati') DEFAULT 'Belum Kawin',
    `pekerjaan` VARCHAR(100) NULL,
    `pendidikan` ENUM('Tidak Sekolah', 'SD', 'SMP', 'SMA/SMK', 'D1/D2/D3', 'S1', 'S2', 'S3') DEFAULT 'SMA/SMK',
    `alamat` TEXT NULL,
    `rt` VARCHAR(3) NULL,
    `rw` VARCHAR(3) NULL,
    `desa_kelurahan` VARCHAR(50) NULL,
    `kecamatan` VARCHAR(50) NULL,
    `kabupaten` VARCHAR(50) DEFAULT 'Banyumas',
    `provinsi` VARCHAR(50) DEFAULT 'Jawa Tengah',
    `kode_pos` VARCHAR(5) NULL,
    `no_telepon` VARCHAR(15) NULL,
    `no_whatsapp` VARCHAR(15) NULL,
    `email` VARCHAR(100) NULL,
    `rayon` VARCHAR(50) NULL,
    `cabang` VARCHAR(50) DEFAULT 'PC Ansor Banyumas',
    `tanggal_daftar` DATE NOT NULL DEFAULT (CURDATE()),
    `tanggal_kta_terbit` DATE NULL,
    `tanggal_kta_berakhir` DATE NULL,
    `status` ENUM('aktif', 'nonaktif', 'pindah', 'meninggal') DEFAULT 'aktif',
    `foto` VARCHAR(255) NULL,
    `foto_ktp` VARCHAR(255) NULL,
    `foto_kk` VARCHAR(255) NULL,
    `nama_ayah` VARCHAR(100) NULL,
    `nama_ibu` VARCHAR(100) NULL,
    `pekerjaan_ayah` VARCHAR(100) NULL,
    `pekerjaan_ibu` VARCHAR(100) NULL,
    `no_telepon_ortu` VARCHAR(15) NULL,
    `keterangan` TEXT NULL,
    `status_verifikasi` ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    `verified_by` INT NULL,
    `verified_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_no_anggota` (`no_anggota`),
    INDEX `idx_no_kta` (`no_kta`),
    INDEX `idx_nik` (`nik`),
    INDEX `idx_nama` (`nama_anggota`),
    INDEX `idx_kecamatan` (`kecamatan`),
    INDEX `idx_rayon` (`rayon`),
    INDEX `idx_status` (`status`),
    INDEX `idx_status_verifikasi` (`status_verifikasi`),
    INDEX `idx_tanggal_daftar` (`tanggal_daftar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rayon table
CREATE TABLE IF NOT EXISTS `rayon` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `nama_rayon` VARCHAR(50) NOT NULL UNIQUE,
    `kecamatan` VARCHAR(50) NOT NULL,
    `ketua_rayon` VARCHAR(100) NULL,
    `sekretaris_rayon` VARCHAR(100) NULL,
    `alamat_sekretariat` TEXT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cabang table
CREATE TABLE IF NOT EXISTS `cabang` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `nama_cabang` VARCHAR(50) NOT NULL UNIQUE,
    `ketua_cabang` VARCHAR(100) NULL,
    `sekretaris_cabang` VARCHAR(100) NULL,
    `alamat_sekretariat` TEXT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activity log table
CREATE TABLE IF NOT EXISTS `activity_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `action` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Settings table
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `setting_key` VARCHAR(50) NOT NULL UNIQUE,
    `setting_value` TEXT NULL,
    `description` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: admin123)
INSERT INTO `users` (`username`, `email`, `password`, `role`, `is_active`) VALUES
('admin', 'admin@ansorbanyumas.or.id', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 1)
ON DUPLICATE KEY UPDATE `username` = `username`;

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`, `description`) VALUES
('site_name', 'GP Ansor Banyumas', 'Nama website'),
('site_description', 'Database Anggota GP Ansor Kabupaten Banyumas', 'Deskripsi website'),
('site_email', 'info@ansorbanyumas.or.id', 'Email kontak'),
('site_phone', '0281-xxxxxxx', 'Nomor telepon'),
('site_address', 'Jl. Jenderal Sudirman No. 1, Purwokerto, Banyumas', 'Alamat sekretariat'),
('kta_validity_years', '5', 'Masa berlaku KTA (tahun)'),
('registration_open', '1', 'Pendaftaran dibuka (1=ya, 0=tidak)'),
('auto_approve', '0', 'Auto approve pendaftaran (1=ya, 0=tidak)')
ON DUPLICATE KEY UPDATE `setting_key` = `setting_key`;

-- Insert sample rayon data
INSERT INTO `rayon` (`nama_rayon`, `kecamatan`) VALUES
('Rayon Ajibarang', 'Ajibarang'),
('Rayon Banyumas', 'Banyumas'),
('Rayon Cilongok', 'Cilongok'),
('Rayon Gumelar', 'Gumelar'),
('Rayon Jatilawang', 'Jatilawang'),
('Rayon Kalibagor', 'Kalibagor'),
('Rayon Karanglewas', 'Karanglewas'),
('Rayon Kedungbanteng', 'Kedungbanteng'),
('Rayon Kembaran', 'Kembaran'),
('Rayon Kemranjen', 'Kemranjen'),
('Rayon Lumbir', 'Lumbir'),
('Rayon Patikraja', 'Patikraja'),
('Rayon Pekuncen', 'Pekuncen'),
('Rayon Purwojati', 'Purwojati'),
('Rayon Rawalo', 'Rawalo'),
('Rayon Somba Opu', 'Somba Opu'),
('Rayon Somagede', 'Somagede'),
('Rayon Sumpiuh', 'Sumpiuh'),
('Rayon Wangon', 'Wangon')
ON DUPLICATE KEY UPDATE `nama_rayon` = `nama_rayon`;

-- Insert sample cabang data
INSERT INTO `cabang` (`nama_cabang`) VALUES
('PC Ansor Banyumas')
ON DUPLICATE KEY UPDATE `nama_cabang` = `nama_cabang`;

-- Create view for member statistics
CREATE OR REPLACE VIEW `v_member_stats` AS
SELECT 
    COUNT(*) as total_anggota,
    SUM(CASE WHEN status = 'aktif' THEN 1 ELSE 0 END) as aktif,
    SUM(CASE WHEN status = 'nonaktif' THEN 1 ELSE 0 END) as nonaktif,
    SUM(CASE WHEN status = 'pindah' THEN 1 ELSE 0 END) as pindah,
    SUM(CASE WHEN status = 'meninggal' THEN 1 ELSE 0 END) as meninggal,
    SUM(CASE WHEN jenis_kelamin = 'L' THEN 1 ELSE 0 END) as laki_laki,
    SUM(CASE WHEN jenis_kelamin = 'P' THEN 1 ELSE 0 END) as perempuan,
    SUM(CASE WHEN YEAR(tanggal_daftar) = YEAR(CURDATE()) THEN 1 ELSE 0 END) as tahun_ini
FROM `anggota`;

-- Create view for members with rayon info
CREATE OR REPLACE VIEW `v_anggota_lengkap` AS
SELECT 
    a.*,
    u.username,
    u.email as user_email,
    u.role,
    r.ketua_rayon,
    r.sekretaris_rayon,
    c.nama_cabang,
    c.ketua_cabang
FROM `anggota` a
LEFT JOIN `users` u ON a.user_id = u.id
LEFT JOIN `rayon` r ON a.rayon = r.nama_rayon
LEFT JOIN `cabang` c ON a.cabang = c.nama_cabang;