<?php
/**
 * WebGIS Poverty Mapping - Database Setup Script
 * Version: 1.0
 * Description: Initialize database schema and required tables
 */

// Error reporting
error_reporting(E_ALL);
ini_set('display_errors', 1);

// Buat koneksi tanpa database dulu
$conn = new mysqli("localhost", "root", "");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "=== WebGIS Poverty Mapping - Database Setup ===<br><br>";

// ========== 1. CREATE DATABASE ==========
$sql_db = "CREATE DATABASE IF NOT EXISTS webgis_unified CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
if ($conn->query($sql_db) === TRUE) {
    echo "✓ Database 'webgis_unified' ready<br>";
} else {
    die("✗ Error creating database: " . $conn->error . "<br>");
}

$conn->select_db("webgis_unified");

// ========== 2. CREATE MAIN DATA TABLE ==========
$sql_table = "CREATE TABLE IF NOT EXISTS data_unified (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipe ENUM('point','worship','poor_house','jalan','parsil') NOT NULL DEFAULT 'point',
    
    -- Atribut Umum
    nama VARCHAR(200) NULL,
    jenis VARCHAR(100) NULL,
    alamat VARCHAR(255) NULL,
    kontak VARCHAR(100) NULL,
    no_wa VARCHAR(50) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'aktif',
    
    -- Atribut Rumah Ibadah
    kegiatan VARCHAR(255) NULL,
    kapasitas INT NULL,
    radius_meter INT NULL,
    
    -- Atribut Rumah Miskin
    nama_kepala_keluarga VARCHAR(200) NULL,
    jumlah_anggota INT NULL,
    kondisi_rumah VARCHAR(100) NULL,
    sumber_penghasilan VARCHAR(255) NULL,
    kebutuhan_prioritas VARCHAR(255) NULL,
    status_bantuan VARCHAR(100) NULL,
    
    -- Koordinat Geografis
    latitude DOUBLE NULL,
    longitude DOUBLE NULL,
    
    -- Untuk Jalan & Parsil
    panjang DECIMAL(10,2) NULL,
    luas DECIMAL(10,2) NULL,
    geom LONGTEXT NULL,
    
    -- Audit Trail
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by VARCHAR(50) NULL,
    updated_by VARCHAR(50) NULL,
    
    -- Indexes
    KEY idx_tipe (tipe),
    KEY idx_status (status),
    KEY idx_created_at (created_at),
    KEY idx_koordinat (latitude, longitude),
    FULLTEXT KEY ft_nama (nama),
    FULLTEXT KEY ft_alamat (alamat)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_table) === TRUE) {
    echo "✓ Table 'data_unified' created<br>";
} else {
    echo "✗ Error creating table: " . $conn->error . "<br>";
}

// ========== 3. CREATE AUDIT LOG TABLE ==========
$sql_audit = "CREATE TABLE IF NOT EXISTS audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    record_id INT NOT NULL,
    action ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    user_name VARCHAR(50) NULL,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    KEY idx_table (table_name),
    KEY idx_record (record_id),
    KEY idx_action (action),
    KEY idx_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_audit) === TRUE) {
    echo "✓ Table 'audit_log' created<br>";
} else {
    echo "✗ Error creating audit_log: " . $conn->error . "<br>";
}

// ========== 3.1 CREATE ACTIVITY LOG TABLE (for API logging) ==========
$sql_activity = "CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(50) NOT NULL,
    details JSON NULL,
    user_name VARCHAR(50) NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    KEY idx_action (action),
    KEY idx_user (user_name),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_activity) === TRUE) {
    echo "✓ Table 'activity_log' created<br>";
} else {
    echo "✗ Error creating activity_log: " . $conn->error . "<br>";
}

// ========== 4. CREATE REFERENCE TABLES ==========

// House Condition Reference
$sql_kondisi = "CREATE TABLE IF NOT EXISTS ref_kondisi_rumah (
    id INT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    deskripsi TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_kondisi) === TRUE) {
    echo "✓ Table 'ref_kondisi_rumah' created<br>";
    
    // Insert reference data
    $insert_kondisi = "INSERT IGNORE INTO ref_kondisi_rumah (id, kode, nama, deskripsi) VALUES
        (1, 'SANGAT_BURUK', 'Sangat Buruk', 'Rumah dalam kondisi sangat buruk/rawan roboh'),
        (2, 'BURUK', 'Buruk', 'Rumah banyak kerusakan'),
        (3, 'SEDANG', 'Sedang', 'Rumah dengan kerusakan ringan'),
        (4, 'BAIK', 'Baik', 'Rumah dalam kondisi baik')";
    $conn->query($insert_kondisi);
}

// Assistance Status Reference
$sql_bantuan = "CREATE TABLE IF NOT EXISTS ref_status_bantuan (
    id INT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    deskripsi TEXT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_bantuan) === TRUE) {
    echo "✓ Table 'ref_status_bantuan' created<br>";
    
    // Insert reference data
    $insert_bantuan = "INSERT IGNORE INTO ref_status_bantuan (id, kode, nama, deskripsi) VALUES
        (1, 'BELUM', 'Belum Menerima', 'Belum menerima bantuan apapun'),
        (2, 'PENERIMA_PKH', 'Penerima PKH', 'Penerima Program Keluarga Harapan'),
        (3, 'PENERIMA_BLT', 'Penerima BLT', 'Penerima Bantuan Langsung Tunai'),
        (4, 'PENERIMA_BANTSOS', 'Penerima Bantuan Sosial', 'Penerima bantuan sosial lainnya')";
    $conn->query($insert_bantuan);
}

// Income Source Reference
$sql_penghasilan = "CREATE TABLE IF NOT EXISTS ref_sumber_penghasilan (
    id INT PRIMARY KEY,
    kode VARCHAR(20) NOT NULL UNIQUE,
    nama VARCHAR(100) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_penghasilan) === TRUE) {
    echo "✓ Table 'ref_sumber_penghasilan' created<br>";
    
    // Insert reference data
    $insert_penghasilan = "INSERT IGNORE INTO ref_sumber_penghasilan (id, kode, nama) VALUES
        (1, 'PETANI', 'Petani'),
        (2, 'BURUH', 'Buruh'),
        (3, 'PEDAGANG', 'Pedagang'),
        (4, 'PNS', 'PNS'),
        (5, 'SWASTA', 'Pegawai Swasta'),
        (6, 'USAHA_KECIL', 'Usaha Kecil'),
        (7, 'PENSIUN', 'Pensiun'),
        (8, 'TIDAK_BEKERJA', 'Tidak Bekerja')";
    $conn->query($insert_penghasilan);
}

// ========== 5. CREATE STATISTICS TABLE ==========
$sql_stats = "CREATE TABLE IF NOT EXISTS data_statistics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL UNIQUE,
    total_records INT DEFAULT 0,
    total_worship INT DEFAULT 0,
    total_poor_house INT DEFAULT 0,
    total_points INT DEFAULT 0,
    total_roads INT DEFAULT 0,
    total_parcels INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    KEY idx_stat_date (stat_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_stats) === TRUE) {
    echo "✓ Table 'data_statistics' created<br>";
}

// ========== 6. CREATE ADMIN USER TABLE ==========
$sql_users = "CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NULL,
    role ENUM('admin', 'user', 'viewer') DEFAULT 'user',
    is_active TINYINT DEFAULT 1,
    last_login DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    KEY idx_username (username),
    KEY idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

if ($conn->query($sql_users) === TRUE) {
    echo "✓ Table 'users' created<br>";
}

// ========== 7. CREATE TRIGGERS (For Audit Trail) ==========

// Trigger for INSERT
$trigger_insert = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_insert 
AFTER INSERT ON data_unified 
FOR EACH ROW 
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, new_values, user_name, changed_at)
    VALUES ('data_unified', NEW.id, 'INSERT', 
            JSON_OBJECT('tipe', NEW.tipe, 'nama', NEW.nama, 'alamat', NEW.alamat),
            IFNULL(NEW.created_by, 'system'), NOW());
END";

if ($conn->query($trigger_insert) === TRUE) {
    echo "✓ Trigger 'trg_data_unified_insert' created<br>";
} else {
    echo "  Trigger insert: " . $conn->error . "<br>";
}

// Trigger for UPDATE
$trigger_update = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_update 
AFTER UPDATE ON data_unified 
FOR EACH ROW 
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_values, new_values, user_name, changed_at)
    VALUES ('data_unified', NEW.id, 'UPDATE',
            JSON_OBJECT('nama', OLD.nama, 'alamat', OLD.alamat, 'status', OLD.status),
            JSON_OBJECT('nama', NEW.nama, 'alamat', NEW.alamat, 'status', NEW.status),
            IFNULL(NEW.updated_by, 'system'), NOW());
END";

if ($conn->query($trigger_update) === TRUE) {
    echo "✓ Trigger 'trg_data_unified_update' created<br>";
} else {
    echo "  Trigger update: " . $conn->error . "<br>";
}

// Trigger for DELETE
$trigger_delete = "CREATE TRIGGER IF NOT EXISTS trg_data_unified_delete 
BEFORE DELETE ON data_unified 
FOR EACH ROW 
BEGIN
    INSERT INTO audit_log (table_name, record_id, action, old_values, changed_at)
    VALUES ('data_unified', OLD.id, 'DELETE',
            JSON_OBJECT('tipe', OLD.tipe, 'nama', OLD.nama, 'alamat', OLD.alamat), 
            NOW());
END";

if ($conn->query($trigger_delete) === TRUE) {
    echo "✓ Trigger 'trg_data_unified_delete' created<br>";
} else {
    echo "  Trigger delete: " . $conn->error . "<br>";
}

// ========== 8. CREATE VIEWS ==========

$view_summary = "CREATE OR REPLACE VIEW v_data_summary AS
SELECT 
    tipe,
    COUNT(*) as count,
    status
FROM data_unified
GROUP BY tipe, status";

if ($conn->query($view_summary) === TRUE) {
    echo "✓ View 'v_data_summary' created<br>";
}

$view_poor_analysis = "CREATE OR REPLACE VIEW v_poor_house_analysis AS
SELECT 
    nama_kepala_keluarga,
    alamat,
    kondisi_rumah,
    jumlah_anggota,
    kebutuhan_prioritas,
    status_bantuan,
    latitude,
    longitude
FROM data_unified
WHERE tipe = 'poor_house' AND status = 'aktif'
ORDER BY jumlah_anggota DESC";

if ($conn->query($view_poor_analysis) === TRUE) {
    echo "✓ View 'v_poor_house_analysis' created<br>";
}

// ========== COMPLETION MESSAGE ==========
echo "<br>=== Setup Selesai ===" . date('Y-m-d H:i:s') . "<br>";
echo "Database: webgis_unified<br>";
echo "Tables: 9 (data_unified, audit_log, ref_kondisi_rumah, ref_status_bantuan, ref_sumber_penghasilan, data_statistics, users, + 2 views)<br>";
echo "Triggers: 3 (INSERT, UPDATE, DELETE)<br>";
echo "Ready for production use!<br>";

$conn->close();
?>