-- ============================================================================
-- SKEMA DATABASE MYSQL LENGKAP "WargaKu - Watugaluh Residence Jombang"
-- Support 4 Role: 'warga', 'pengurus_rt', 'satpam', 'admin'
-- Host DB: 103.150.197.80 (MySQL Port: 3306)
-- Database Name: u9218273_watugaluh_db / wargaku_watugaluh
-- ============================================================================

CREATE DATABASE IF NOT EXISTS `u9218273_watugaluh_db` 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE `u9218273_watugaluh_db`;

-- ----------------------------------------------------------------------------
-- 1. TABEL USERS (Manajemen Akun Pengguna Sesuai Level)
-- ----------------------------------------------------------------------------
DROP TABLE IF EXISTS `system_settings`;
DROP TABLE IF EXISTS `security_patrol_logs`;
DROP TABLE IF EXISTS `community_fees`;
DROP TABLE IF EXISTS `private_broadcasts`;
DROP TABLE IF EXISTS `citizen_reports`;
DROP TABLE IF EXISTS `panic_alerts`;
DROP TABLE IF EXISTS `announcements`;
DROP TABLE IF EXISTS `guest_permissions`;
DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `phone_number` VARCHAR(20) UNIQUE NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('warga', 'pengurus_rt', 'satpam', 'admin') NOT NULL DEFAULT 'warga',
    `block_number` VARCHAR(50) NOT NULL, -- Contoh: "Blok B-07", "Blok A-01", "Pos Utama Gerbang 1"
    `resident_status` ENUM('pemilik', 'kontrak', 'kosong') NOT NULL DEFAULT 'pemilik',
    `fcm_token` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_role` (`role`),
    INDEX `idx_block` (`block_number`),
    INDEX `idx_email` (`email`),
    INDEX `idx_phone` (`phone_number`)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 2. TABEL GUEST PERMISSIONS (Izin Tamu, Kunjungan & Verifikasi QR Code)
-- ----------------------------------------------------------------------------
CREATE TABLE `guest_permissions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `guest_name` VARCHAR(100) NOT NULL,
    `guest_phone` VARCHAR(20) NULL,
    `vehicle_number` VARCHAR(20) NOT NULL, -- Contoh: "S 4521 WX"
    `visit_date` DATE NOT NULL,
    `purpose_of_visit` VARCHAR(255) NOT NULL,
    `qr_code_token` VARCHAR(64) UNIQUE NOT NULL,
    `status` ENUM('menunggu', 'disetujui', 'masuk', 'keluar', 'dibatalkan') NOT NULL DEFAULT 'menunggu',
    `checked_in_at` DATETIME NULL,
    `checked_out_at` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_qr_token` (`qr_code_token`),
    INDEX `idx_guest_status` (`status`),
    INDEX `idx_visit_date` (`visit_date`)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 3. TABEL ANNOUNCEMENTS (Broadcast Pengumuman Terbuka untuk Semua Warga)
-- ----------------------------------------------------------------------------
CREATE TABLE `announcements` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `author_id` INT NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `content` TEXT NOT NULL,
    `category` ENUM('keamanan', 'kerja_bakti', 'iuran', 'darurat', 'umum') NOT NULL DEFAULT 'umum',
    `attachment_url` VARCHAR(255) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_announcement_cat` (`category`)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 4. TABEL PRIVATE BROADCASTS (Broadcast Tertuju: Per User, Per Blok, atau Per Role)
-- ----------------------------------------------------------------------------
CREATE TABLE `private_broadcasts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `sender_id` INT NOT NULL,
    `recipient_user_id` INT NULL, -- NULL jika tertuju ke semua penghuni suatu blok/role
    `target_block` VARCHAR(50) NULL, -- Contoh: "Blok B-07" atau NULL
    `target_role` ENUM('warga', 'pengurus_rt', 'satpam', 'admin') NULL, -- NULL jika per user/blok
    `title` VARCHAR(200) NOT NULL,
    `message` TEXT NOT NULL,
    `category` ENUM('pribadi', 'tagihan_iuran', 'paket_pos', 'surat_rt', 'keamanan') NOT NULL DEFAULT 'pribadi',
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`recipient_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_target_recipient` (`recipient_user_id`),
    INDEX `idx_target_block` (`target_block`),
    INDEX `idx_target_role` (`target_role`)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 5. TABEL PANIC ALERTS (Sistem Tombol Darurat SOS Real-Time)
-- ----------------------------------------------------------------------------
CREATE TABLE `panic_alerts` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `latitude` DECIMAL(10, 8) NOT NULL,
    `longitude` DECIMAL(11, 8) NOT NULL,
    `status` ENUM('aktif', 'ditangani', 'salah_tekan') NOT NULL DEFAULT 'aktif',
    `resolved_by` INT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`resolved_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_panic_status` (`status`)
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 6. TABEL CITIZEN REPORTS (Laporan/Keluhan Warga ke Pengurus RT & Pengelola)
-- ----------------------------------------------------------------------------
CREATE TABLE `citizen_reports` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT NOT NULL,
    `photo_url` VARCHAR(255) NULL,
    `status` ENUM('menunggu', 'diproses', 'selesai', 'ditolak') NOT NULL DEFAULT 'menunggu',
    `response_note` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 7. TABEL COMMUNITY FEES (Iuran Bulanan Warga & Kas RT)
-- ----------------------------------------------------------------------------
CREATE TABLE `community_fees` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `month_period` VARCHAR(20) NOT NULL, -- e.g. "September"
    `year_period` INT NOT NULL, -- e.g. 2026
    `amount` DECIMAL(12, 2) NOT NULL DEFAULT 75000.00,
    `payment_status` ENUM('lunas', 'belum_lunas') NOT NULL DEFAULT 'belum_lunas',
    `payment_method` VARCHAR(50) NULL, -- "QRIS", "Transfer Bank Jatim", "Tunai Pos Satpam"
    `paid_at` DATETIME NULL,
    `invoice_number` VARCHAR(50) UNIQUE NOT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 8. TABEL SECURITY PATROL LOGS (Log Patroli Keliling Satpam Kawasan)
-- ----------------------------------------------------------------------------
CREATE TABLE `security_patrol_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `guard_user_id` INT NOT NULL,
    `checkpoint_name` VARCHAR(100) NOT NULL, -- e.g. "Pos Pantau Blok C", "Taman Belakang"
    `scan_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `notes` VARCHAR(255) NULL,
    `latitude` DECIMAL(10, 8) NULL,
    `longitude` DECIMAL(11, 8) NULL,
    FOREIGN KEY (`guard_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------------------
-- 9. TABEL SYSTEM SETTINGS (Pusat Konfigurasi Aplikasi Admin & Sinkronisasi Warga)
-- ----------------------------------------------------------------------------
CREATE TABLE `system_settings` (
    `setting_key` VARCHAR(100) PRIMARY KEY,
    `setting_value` TEXT NOT NULL,
    `setting_group` VARCHAR(50) NOT NULL DEFAULT 'general',
    `description` VARCHAR(255) NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;


-- ============================================================================
-- SEED DATA CONTOH LENGKAP SEMUA DATA DALAM APLIKASI
-- Default Password: 'warga123' (Bcrypt Hash: $2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi)
-- ============================================================================

-- 1. SEED DATA PENGGUNA (Users 4 Level)
INSERT INTO `users` (`id`, `name`, `email`, `phone_number`, `password_hash`, `role`, `block_number`, `resident_status`) VALUES
(1, 'Budi Santoso', 'budi.watugaluh@gmail.com', '081234567890', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'warga', 'Blok B-07', 'pemilik'),
(2, 'Bambang Wijaya, S.T.', 'rt03.watugaluh@gmail.com', '081398765432', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'pengurus_rt', 'Blok A-01', 'pemilik'),
(3, 'Joko Susilo', 'pos.satpam@watugaluh.id', '085712345678', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'satpam', 'Pos Utama Gerbang 1', 'pemilik'),
(4, 'Admin Watugaluh Residence', 'admin@watugaluh.id', '081122334455', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'Kantor Pengelola', 'pemilik'),
(5, 'Hendra Setiawan', 'hendra.setiawan@gmail.com', '082199887711', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'warga', 'Blok C-12', 'kontrak'),
(6, 'Siti Rahmawati', 'siti.rahma@gmail.com', '087755443322', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'warga', 'Blok B-15', 'pemilik'),
(7, 'Agus Prasetyo', 'agus.prasetyo@watugaluh.id', '085698712345', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'satpam', 'Pos Utama Gerbang 2', 'pemilik'),
(8, 'Drs. H. Sugiono', 'sugiono.rw@watugaluh.id', '081233445566', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'pengurus_rt', 'Blok A-05', 'pemilik');

-- 2. SEED DATA IZIN TAMU (Guest Permissions & QR Passes)
INSERT INTO `guest_permissions` (`id`, `user_id`, `guest_name`, `guest_phone`, `vehicle_number`, `visit_date`, `purpose_of_visit`, `qr_code_token`, `status`, `checked_in_at`, `checked_out_at`) VALUES
(1, 1, 'Ahmad Maulana', '081299887766', 'S 4521 WX', CURDATE(), 'Silaturahmi Keluarga & Urusan Pekerjaan', 'WTG-2026-GH92K1', 'menunggu', NULL, NULL),
(2, 1, 'Kurir J&T Express', '085811223344', 'S 3912 YZ', CURDATE(), 'Pengantaran Paket Material Renovasi', 'WTG-2026-JK81L0', 'masuk', NOW(), NULL),
(3, 5, 'Dr. Irwan Santika', '081344556677', 'L 1823 AA', CURDATE(), 'Konsultasi Desain Rumah', 'WTG-2026-AB77P9', 'disetujui', NULL, NULL),
(4, 6, 'Teknisi PLN Jombang', '082233114455', 'S 9812 WQ', CURDATE(), 'Pengecekan Meteran Listrik Blok B', 'WTG-2026-PLN881', 'masuk', NOW(), NULL);

-- 3. SEED DATA PENGUMUMAN PUBLIK (Announcements)
INSERT INTO `announcements` (`id`, `author_id`, `title`, `content`, `category`, `created_at`) VALUES
(1, 2, 'Kerja Bakti Massal & Penebangan Ranting Pohon', 'Diberitahukan kepada seluruh warga Watugaluh Residence, hari Minggu besok pukul 06.30 WIB akan diadakan kerja bakti pembersihan saluran air dan pemangkasan ranting pohon antisipasi musim hujan. Dimohon partisipasi tiap KK.', 'kerja_bakti', '2026-08-28 08:00:00'),
(2, 3, 'Pemberlakuan Sistem One-Gate QR Pass Tamu', 'Mulai 1 September 2026, seluruh tamu/kurir wajib menunjukkan QR Code yang dibuat oleh warga melalui aplikasi WargaKu sebelum portal pos satpam dibuka oleh petugas jaga.', 'keamanan', '2026-08-27 19:30:00'),
(3, 2, 'Pembayaran Iuran Kas & Kebersihan Bulan September', 'Tagihan iuran keamanan dan kebersihan sebesar Rp 75.000 dapat ditransfer via VA Bank Jatim / QRIS atau diserahkan ke pos satpam sebelum tanggal 10 tiap bulannya.', 'iuran', '2026-08-25 14:00:00');

-- 4. SEED DATA BROADCAST PRIVATE / KHUSUS (Private Broadcasts to specific User / Block / Role)
INSERT INTO `private_broadcasts` (`id`, `sender_id`, `recipient_user_id`, `target_block`, `target_role`, `title`, `message`, `category`, `is_read`, `created_at`) VALUES
(1, 3, 1, 'Blok B-07', NULL, 'Paket Barang Tiba di Pos Satpam', 'Halo Bpk. Budi Santoso (Blok B-07), ada paket kiriman J&T ukuran sedang telah dititipkan di Pos Utama Gerbang 1 oleh kurir. Mohon diambil sebelum pukul 22.00 WIB.', 'paket_pos', 0, '2026-08-28 10:20:00'),
(2, 2, 1, 'Blok B-07', NULL, 'Konfirmasi Surat Pengantar RT', 'Surat keterangan domisili / pengantar KTP atas nama Bpk. Budi Santoso telah ditandatangani Ketua RT. Berkas fisik dapat diambil di rumah Pak RT (Blok A-01).', 'surat_rt', 0, '2026-08-28 09:15:00'),
(3, 2, NULL, 'Blok B-07', 'warga', 'Pengingat Iuran Kebersihan Blok B', 'Kepada warga penghuni Blok B, tagihan iuran pemeliharaan lingkungan bulan berjalan telah terbit. Terima kasih bagi yang sudah melunasi.', 'tagihan_iuran', 0, '2026-08-27 11:00:00'),
(4, 4, NULL, NULL, 'satpam', 'Instruksi Khusus: Pengetatan Patroli Malam', 'Kepada regu jaga satpam malam, mohon tingkatkan frekuensi keliling patroli di area portal belakang dan taman samping mulai pukul 01.00 - 04.00 WIB.', 'keamanan', 1, '2026-08-26 21:00:00');

-- 5. SEED DATA PANIC ALERTS (Tombol Darurat SOS)
INSERT INTO `panic_alerts` (`id`, `user_id`, `latitude`, `longitude`, `status`, `resolved_by`, `created_at`) VALUES
(1, 5, -7.55012300, 112.23145600, 'ditangani', 3, '2026-08-26 23:45:10');

-- 6. SEED DATA COMMUNITY FEES
INSERT INTO `community_fees` (`id`, `user_id`, `month_period`, `year_period`, `amount`, `payment_status`, `payment_method`, `paid_at`, `invoice_number`) VALUES
(1, 1, 'Agustus', 2026, 75000.00, 'lunas', 'QRIS Mandiri', '2026-08-05 10:14:00', 'INV-WTG-202608-001'),
(2, 1, 'September', 2026, 75000.00, 'belum_lunas', NULL, NULL, 'INV-WTG-202609-001'),
(3, 5, 'Agustus', 2026, 75000.00, 'lunas', 'Transfer Bank Jatim', '2026-08-08 16:30:00', 'INV-WTG-202608-005');

-- 7. SEED DATA PATROLI SATPAM
INSERT INTO `security_patrol_logs` (`id`, `guard_user_id`, `checkpoint_name`, `scan_time`, `notes`, `latitude`, `longitude`) VALUES
(1, 3, 'Pos Gerbang Utama', NOW() - INTERVAL 2 HOUR, 'Kondisi portal tertutup aman, lampu penerangan menyala terang.', -7.54980000, 112.23090000),
(2, 3, 'Checkpoint Blok B & C', NOW() - INTERVAL 1 HOUR, 'Situasi kondusif, kendaraan terparkir rapi di carport masing-masing.', -7.55020000, 112.23170000);

-- 8. SEED DATA SYSTEM SETTINGS (Pusat Konfigurasi Cloud MySQL)
INSERT INTO `system_settings` (`setting_key`, `setting_value`, `setting_group`, `description`) VALUES
('residence_name', 'Watugaluh Residence Jombang', 'general', 'Nama resmi kawasan perumahan'),
('rt_rw_unit', 'RT 03 / RW 02 Desa Watugaluh, Kec. Diwek, Kab. Jombang', 'general', 'Unit wilayah administrasi RT/RW'),
('pos_satpam_phone', '085712345678', 'emergency', 'Nomor darurat / WhatsApp Pos Satpam Utama'),
('emergency_ambulance_phone', '119 / 0811333119', 'emergency', 'Hotline Ambulans & Medis Siaga'),
('emergency_police_phone', '110 (Polsek Diwek)', 'emergency', 'Hotline Polsek Diwek Jombang'),
('monthly_dues_amount', '75000', 'finance', 'Nominal iuran keamanan & kebersihan bulanan (Rp)'),
('payment_bank_info', 'Bank Jatim: 0182-9988-11 a.n Paguyuban Watugaluh', 'finance', 'Nomor rekening penampung kas warga'),
('qris_merchant_name', 'KAS RT03 WATUGALUH', 'finance', 'Label Merchant QRIS Resmi'),
('portal_open_hours', '05:00 - 23:00 WIB', 'security', 'Jadwal operasional buka portal otomatis pos satpam'),
('auto_approve_guest_qr', '1', 'security', 'Aktifkan QR Code pass tamu instan'),
('allow_warga_panic_button', '1', 'security', 'Izinkan warga memicu tombol darurat SOS'),
('broadcast_running_text', 'Selamat datang di Kawasan Watugaluh Residence Jombang. Jaga kerukunan, ketertiban & kebersihan lingkungan bersama.', 'general', 'Pesan berjalan di beranda warga'),
('api_base_url', 'https://watugaluh-residence.jombang.go.id/', 'network', 'Base URL API Endpoint Cloud Hosting'),
('database_name', 'u9218273_watugaluh_db', 'network', 'Nama Database MySQL Server'),
('database_host', '103.150.197.80', 'network', 'IP / Hostname MySQL Server'),
('database_port', '3306', 'network', 'Port Koneksi MySQL'),
('admin_contact', '081122334455', 'general', 'Nomor Pengelola / Admin Hotline');


-- ============================================================================
-- CONTOH QUERY SQL SIAP PAKAI (COPY-PASTE / CRUD & OPERASIONAL SERVER)
-- ============================================================================

-- ----------------------------------------------------------------------------
-- CONTOH 1: SQL TAMBAH USER BARU (Role: Warga)
-- ----------------------------------------------------------------------------
-- Password 'warga123' di-hash menggunakan PHP password_hash('warga123', PASSWORD_BCRYPT)
INSERT INTO `users` (
    `name`, 
    `email`, 
    `phone_number`, 
    `password_hash`, 
    `role`, 
    `block_number`, 
    `resident_status`
) VALUES (
    'Rahmat Hidayat', 
    'rahmat.hidayat@gmail.com', 
    '081298877665', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
    'warga', 
    'Blok D-03', 
    'pemilik'
);

-- ----------------------------------------------------------------------------
-- CONTOH 2: SQL TAMBAH USER PENGURUS RT / RW BARU
-- ----------------------------------------------------------------------------
INSERT INTO `users` (
    `name`, 
    `email`, 
    `phone_number`, 
    `password_hash`, 
    `role`, 
    `block_number`, 
    `resident_status`
) VALUES (
    'H. M. Yusuf, S.Sos.', 
    'yusuf.rt04@watugaluh.id', 
    '081377889900', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
    'pengurus_rt', 
    'Blok D-01', 
    'pemilik'
);

-- ----------------------------------------------------------------------------
-- CONTOH 3: SQL TAMBAH USER SATPAM (Petugas Keamanan Kawasan)
-- ----------------------------------------------------------------------------
INSERT INTO `users` (
    `name`, 
    `email`, 
    `phone_number`, 
    `password_hash`, 
    `role`, 
    `block_number`, 
    `resident_status`
) VALUES (
    'Slamet Supriyadi', 
    'slamet.satpam@watugaluh.id', 
    '085877665544', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
    'satpam', 
    'Pos Gerbang Selatan', 
    'pemilik'
);

-- ----------------------------------------------------------------------------
-- CONTOH 4: SQL TAMBAH USER ADMINISTRATOR APLIKASI
-- ----------------------------------------------------------------------------
INSERT INTO `users` (
    `name`, 
    `email`, 
    `phone_number`, 
    `password_hash`, 
    `role`, 
    `block_number`, 
    `resident_status`
) VALUES (
    'Master Administrator Watugaluh', 
    'superadmin@watugaluh.id', 
    '081199887700', 
    '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 
    'admin', 
    'Ruang IT Server', 
    'pemilik'
);

-- ----------------------------------------------------------------------------
-- CONTOH 5: SQL TAMBAH IZIN TAMU DENGAN TOKEN QR CODE
-- ----------------------------------------------------------------------------
INSERT INTO `guest_permissions` (
    `user_id`, 
    `guest_name`, 
    `guest_phone`, 
    `vehicle_number`, 
    `visit_date`, 
    `purpose_of_visit`, 
    `qr_code_token`, 
    `status`
) VALUES (
    1, -- User ID Bpk. Budi Santoso
    'Muhammad Rizky (Gojek Food)', 
    '081234112233', 
    'S 5512 XY', 
    CURDATE(), 
    'Pengantaran Makanan', 
    CONCAT('WTG-2026-', UPPER(SUBSTRING(MD5(RAND()), 1, 6))), 
    'menunggu'
);

-- ----------------------------------------------------------------------------
-- CONTOH 6: SQL KIRIM BROADCAST PRIVATE KE USER SPESIFIK / BLOK SPESIFIK
-- ----------------------------------------------------------------------------
-- (A) Kirim ke user tertentu (contoh: paket tiba di pos satpam)
INSERT INTO `private_broadcasts` (
    `sender_id`, 
    `recipient_user_id`, 
    `target_block`, 
    `title`, 
    `message`, 
    `category`
) VALUES (
    3, -- ID Satpam
    1, -- ID Warga (Budi Santoso)
    'Blok B-07', 
    'Pemberitahuan Paket Dokumen Penting', 
    'Dokumen pos kilat dari Bank telah diterima di pos jaga atas nama Budi Santoso.', 
    'paket_pos'
);

-- (B) Kirim ke seluruh penghuni blok tertentu (contoh: pemadaman air sementara di Blok C)
INSERT INTO `private_broadcasts` (
    `sender_id`, 
    `recipient_user_id`, 
    `target_block`, 
    `target_role`, 
    `title`, 
    `message`, 
    `category`
) VALUES (
    2, -- ID Pengurus RT
    NULL, 
    'Blok C-12', 
    'warga', 
    'Perbaikan Pipa Air Blok C', 
    'Pemberitahuan pengalihan saluran air selama 1 jam karena ada pergantian kran utama.', 
    'pribadi'
);

-- ----------------------------------------------------------------------------
-- CONTOH 7: SQL TRIGGER TOMBOL DARURAT SOS (PANIC ALERT)
-- ----------------------------------------------------------------------------
INSERT INTO `panic_alerts` (`user_id`, `latitude`, `longitude`, `status`)
VALUES (1, -7.55034500, 112.23198000, 'aktif');

-- ----------------------------------------------------------------------------
-- CONTOH 8: SQL VERIFIKASI QR CODE TAMU OLEH SATPAM (UPDATE SCAN MASUK)
-- ----------------------------------------------------------------------------
UPDATE `guest_permissions` 
SET 
    `status` = 'masuk', 
    `checked_in_at` = NOW() 
WHERE `qr_code_token` = 'WTG-2026-GH92K1';

-- ----------------------------------------------------------------------------
-- CONTOH 9: QUERY AMBIL BROADCAST PRIVAT KHUSUS USER (USER ID 1 / BLOK B-07 / WARGA)
-- Digunakan oleh API untuk mengirim pesan broadcast terfilter ke user
-- ----------------------------------------------------------------------------
SELECT 
    pb.*,
    u_sender.name AS sender_name,
    u_sender.role AS sender_role
FROM `private_broadcasts` pb
JOIN `users` u_sender ON pb.sender_id = u_sender.id
WHERE 
    pb.recipient_user_id = 1 
    OR pb.target_block = 'Blok B-07' 
    OR pb.target_role = 'warga' 
    OR (pb.recipient_user_id IS NULL AND pb.target_block IS NULL AND pb.target_role IS NULL)
ORDER BY pb.created_at DESC;

-- ----------------------------------------------------------------------------
-- CONTOH 10: QUERY AUTENTIKASI LOGIN ONLINE VIA HOSTING SERVER
-- ----------------------------------------------------------------------------
SELECT 
    `id`, 
    `name`, 
    `email`, 
    `phone_number`, 
    `password_hash`, 
    `role`, 
    `block_number`, 
    `resident_status`, 
    `fcm_token`
FROM `users` 
WHERE `email` = 'budi.watugaluh@gmail.com' OR `phone_number` = '081234567890'
LIMIT 1;