<?php
$host = 'localhost';
$user = 'root';
$pass = '';
$db   = 'rtwl';

$conn = new mysqli($host, $user, $pass, $db);

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

// 1. Master Jabatan
$sql1 = "CREATE TABLE IF NOT EXISTS master_jabatan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_jabatan VARCHAR(100) NOT NULL,
    kategori ENUM('pengurus', 'seksi') DEFAULT 'pengurus',
    urutan INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)";

// 2. Struktur Organisasi (Data Pengurus)
$sql2 = "CREATE TABLE IF NOT EXISTS struktur_organisasi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    jabatan_id INT NOT NULL,
    warga_id INT DEFAULT NULL,
    nama_manual VARCHAR(255) DEFAULT NULL,
    nik_manual VARCHAR(20) DEFAULT NULL,
    foto VARCHAR(255) DEFAULT NULL,
    urutan INT DEFAULT 0,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (jabatan_id) REFERENCES master_jabatan(id) ON DELETE CASCADE
)";

if ($conn->query($sql1) === TRUE) {
    echo "Table master_jabatan created successfully\n";
} else {
    echo "Error creating table master_jabatan: " . $conn->error . "\n";
}

if ($conn->query($sql2) === TRUE) {
    echo "Table struktur_organisasi created successfully\n";
} else {
    echo "Error creating table struktur_organisasi: " . $conn->error . "\n";
}

// Insert default data for master_jabatan
$check = $conn->query("SELECT COUNT(*) as total FROM master_jabatan")->fetch_assoc();
if ($check['total'] == 0) {
    $ins = "INSERT INTO master_jabatan (nama_jabatan, kategori, urutan) VALUES 
    ('Ketua RT', 'pengurus', 1),
    ('Sekretaris', 'pengurus', 2),
    ('Bendahara', 'pengurus', 3),
    ('Seksi Keamanan', 'seksi', 4),
    ('Seksi Kebersihan', 'seksi', 5),
    ('Seksi Kerohanian', 'seksi', 6)";
    $conn->query($ins);
    echo "Default master_jabatan inserted\n";
}

$conn->close();
