-- ============================================================================
-- 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)
-- ----------------------------------------------------------------------------

-- ----------------------------------------------------------------------------
-- 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;

-- 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 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');
