-- Database: uji_kolaborasi
-- Project: Sistem Informasi Data Warga dan Bansos
-- Queries yang sesuai dengan projek ini

-- =====================================================
-- 1. DATABASE SETUP
-- =====================================================

-- Membuat database
CREATE DATABASE IF NOT EXISTS uji_kolaborasi;
USE uji_kolaborasi;

-- =====================================================
-- 2. TABLE CREATION
-- =====================================================

-- Membuat tabel users
CREATE TABLE IF NOT EXISTS users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

-- Membuat tabel warga
CREATE TABLE IF NOT EXISTS warga (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    NIK VARCHAR(50) NOT NULL,
    Nama_lengkap VARCHAR(100) NOT NULL,
    Jenis_kelamin VARCHAR(10),
    Tempat_Lahir VARCHAR(100) NOT NULL,
    Tanggal_Lahir DATE NOT NULL,
    Alamat_lengkap VARCHAR(255) NOT NULL,
    Nomor_KK VARCHAR(50),
    Agama VARCHAR(20),
    Nomor_HP VARCHAR(20) NOT NULL
);

-- Membuat tabel bansos
CREATE TABLE IF NOT EXISTS bansos (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    warga_id INT(6) UNSIGNED NOT NULL,
    jenis_bansos VARCHAR(100) NOT NULL,
    tanggal_pemberian DATE NOT NULL,
    jumlah VARCHAR(100) NOT NULL,
    keterangan TEXT,
    FOREIGN KEY (warga_id) REFERENCES warga(id) ON DELETE CASCADE
);

-- =====================================================
-- 3. SAMPLE DATA INSERTION
-- =====================================================

-- Insert default user admin
INSERT INTO users (username, password) VALUES
('admin', MD5('admin'));

-- Insert sample data warga
INSERT INTO warga (NIK, Nama_lengkap, Jenis_kelamin, Tempat_Lahir, Tanggal_Lahir, Alamat_lengkap, Nomor_KK, Agama, Nomor_HP) VALUES
('3171234567890001', 'Ahmad Surya Pratama', 'Laki-laki', 'Jakarta', '1985-05-15', 'Jl. Sudirman No. 123, RT 001/RW 002, Jakarta Pusat', '3171123456789000', 'Islam', '081234567890'),
('3171234567890002', 'Siti Nurhaliza', 'Perempuan', 'Bandung', '1990-08-20', 'Jl. Asia Afrika No. 45, RT 002/RW 003, Bandung', '3171123456789001', 'Islam', '081987654321'),
('3171234567890003', 'Budi Santoso', 'Laki-laki', 'Surabaya', '1978-12-10', 'Jl. Malioboro No. 67, RT 003/RW 004, Yogyakarta', '3171123456789002', 'Kristen', '081345678901');

-- Insert sample data bansos
INSERT INTO bansos (warga_id, jenis_bansos, tanggal_pemberian, jumlah, keterangan) VALUES
(1, 'Bantuan Langsung Tunai (BLT)', '2025-01-15', '300000', 'BLT Bulan Januari 2025 untuk keluarga kurang mampu'),
(1, 'Bantuan Sembako', '2025-01-20', '500000', 'Paket sembako bulan Januari'),
(2, 'Bantuan Langsung Tunai (BLT)', '2025-01-15', '300000', 'BLT Bulan Januari 2025'),
(3, 'Program Keluarga Harapan (PKH)', '2025-01-10', '750000', 'Bantuan pendidikan anak'),
(3, 'Bantuan Sembako', '2025-01-25', '500000', 'Paket sembako tambahan');

-- =====================================================
-- 4. SELECT QUERIES (UNTUK MENAMPILKAN DATA)
-- =====================================================

-- Menampilkan semua data warga
SELECT * FROM warga;

-- Menampilkan data warga dengan kondisi tertentu
SELECT * FROM warga WHERE Jenis_kelamin = 'Laki-laki';
SELECT * FROM warga WHERE Agama = 'Islam';
SELECT * FROM warga WHERE Alamat_lengkap LIKE '%Jakarta%';

-- Menampilkan data users
SELECT * FROM users;

-- Menampilkan semua data bansos dengan nama warga (JOIN)
SELECT b.*, w.Nama_lengkap FROM bansos b JOIN warga w ON b.warga_id = w.id;

-- Menampilkan bansos berdasarkan jenis
SELECT b.*, w.Nama_lengkap FROM bansos b
JOIN warga w ON b.warga_id = w.id
WHERE b.jenis_bansos = 'Bantuan Langsung Tunai (BLT)';

-- Menampilkan bansos berdasarkan tanggal tertentu
SELECT b.*, w.Nama_lengkap FROM bansos b
JOIN warga w ON b.warga_id = w.id
WHERE b.tanggal_pemberian >= '2025-01-01' AND b.tanggal_pemberian <= '2025-01-31';

-- Menampilkan statistik bansos per warga
SELECT w.Nama_lengkap, COUNT(b.id) as jumlah_bansos, SUM(CAST(REPLACE(jumlah, '.', '') AS UNSIGNED)) as total_bantuan
FROM warga w
LEFT JOIN bansos b ON w.id = b.warga_id
GROUP BY w.id, w.Nama_lengkap
ORDER BY total_bantuan DESC;

-- Menampilkan data warga untuk dropdown (seperti di add_bansos.php)
SELECT id, Nama_lengkap FROM warga;

-- =====================================================
-- 5. INSERT QUERIES (UNTUK MENAMBAH DATA)
-- =====================================================

-- Menambah data warga baru
INSERT INTO warga (NIK, Nama_lengkap, Jenis_kelamin, Tempat_Lahir, Tanggal_Lahir, Alamat_lengkap, Nomor_KK, Agama, Nomor_HP)
VALUES ('3171234567890004', 'Dewi Sartika', 'Perempuan', 'Semarang', '1992-03-25', 'Jl. Pahlawan No. 89, RT 005/RW 006, Semarang', '3171123456789003', 'Islam', '081556667788');

-- Menambah data bansos baru
INSERT INTO bansos (warga_id, jenis_bansos, tanggal_pemberian, jumlah, keterangan)
VALUES (4, 'Bantuan Langsung Tunai (BLT)', '2025-02-01', '300000', 'BLT Bulan Februari 2025');

-- =====================================================
-- 6. UPDATE QUERIES (UNTUK MENGEDIT DATA)
-- =====================================================

-- Update data warga
UPDATE warga
SET Nama_lengkap = 'Ahmad Surya Pratama, S.T.',
    Nomor_HP = '081234567891'
WHERE id = 1;

-- Update data bansos
UPDATE bansos
SET jumlah = '350000',
    keterangan = 'BLT Bulan Januari 2025 (penyesuaian)'
WHERE id = 1 AND jenis_bansos = 'Bantuan Langsung Tunai (BLT)';

-- =====================================================
-- 7. DELETE QUERIES (UNTUK MENGHAPUS DATA)
-- =====================================================

-- Hapus data bansos tertentu
DELETE FROM bansos WHERE id = 5;

-- Hapus data warga (akan otomatis menghapus bansos terkait karena CASCADE)
DELETE FROM warga WHERE id = 4;

-- Hapus semua data bansos (untuk reset testing)
DELETE FROM bansos;

-- Hapus semua data warga (untuk reset testing)
DELETE FROM warga;

-- =====================================================
-- 8. ADVANCED QUERIES
-- =====================================================

-- Menampilkan laporan bansos bulanan
SELECT
    DATE_FORMAT(tanggal_pemberian, '%Y-%m') as bulan,
    jenis_bansos,
    COUNT(*) as jumlah_penerima,
    SUM(CAST(REPLACE(jumlah, '.', '') AS UNSIGNED)) as total_bantuan
FROM bansos
GROUP BY DATE_FORMAT(tanggal_pemberian, '%Y-%m'), jenis_bansos
ORDER BY bulan DESC, jenis_bansos;

-- Mencari warga yang belum pernah menerima bansos
SELECT w.*
FROM warga w
LEFT JOIN bansos b ON w.id = b.warga_id
WHERE b.id IS NULL;

-- Menampilkan warga dengan bansos terbanyak
SELECT w.Nama_lengkap, COUNT(b.id) as jumlah_bansos
FROM warga w
JOIN bansos b ON w.id = b.warga_id
GROUP BY w.id, w.Nama_lengkap
HAVING COUNT(b.id) > 1
ORDER BY jumlah_bansos DESC;

-- Backup data (SELECT all untuk export)
SELECT 'warga' as tabel, CONCAT_WS(';', id, NIK, Nama_lengkap, Jenis_kelamin, Tempat_Lahir, Tanggal_Lahir, Alamat_lengkap, Nomor_KK, Agama, Nomor_HP) as data FROM warga
UNION ALL
SELECT 'bansos' as tabel, CONCAT_WS(';', b.id, b.warga_id, w.Nama_lengkap, b.jenis_bansos, b.tanggal_pemberian, b.jumlah, b.keterangan) as data
FROM bansos b JOIN warga w ON b.warga_id = w.id;

-- =====================================================
-- 9. MAINTENANCE QUERIES
-- =====================================================

-- Menambah index untuk performa yang lebih baik
CREATE INDEX idx_warga_nik ON warga(NIK);
CREATE INDEX idx_bansos_warga_id ON bansos(warga_id);
CREATE INDEX idx_bansos_tanggal ON bansos(tanggal_pemberian);

-- Menampilkan struktur tabel untuk verifikasi
DESCRIBE warga;
DESCRIBE bansos;
DESCRIBE users;

-- Menampilkan foreign key constraints
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'uji_kolaborasi'
AND REFERENCED_TABLE_NAME IS NOT NULL;
