<?php
// File: db.php
// Logika koneksi database ke MySQL menggunakan PHPMyAdmin

date_default_timezone_set('Asia/Jakarta');

// Kredensial database
$servername = "app-nido.site";
$username = "appr3619";
$password = "@Cilo021122";
$dbname = "appr3619_blog";

// Membuat koneksi
$conn = new mysqli($servername, $username, $password, $dbname);

// Memeriksa koneksi
if ($conn->connect_error) {
    die("Koneksi gagal: " . $conn->connect_error);
}

// Fungsi untuk mendapatkan semua data postingan
function getPosts() {
    global $conn;
    $posts = [];
    $sql = "SELECT p.id, p.title, p.content, p.image, p.date, p.pin, u.username, p.user_id FROM posts p JOIN users u ON p.user_id = u.id ORDER BY p.date DESC";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $posts[] = $row;
        }
    }
    return $posts;
}

// Fungsi untuk mendapatkan postingan dengan paginasi
function getPaginatedPosts($limit, $offset) {
    global $conn;
    $posts = [];
    $sql = "SELECT p.id, p.title, p.content, p.image, p.date, p.pin, u.username, p.user_id FROM posts p JOIN users u ON p.user_id = u.id ORDER BY p.date DESC LIMIT ? OFFSET ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("ii", $limit, $offset);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                $posts[] = $row;
            }
        }
        $stmt->close();
    }
    return $posts;
}

// Fungsi untuk mendapatkan postingan milik pengguna tertentu
function getPostsByUserId($userId) {
    global $conn;
    $posts = [];
    $sql = "SELECT id, title, content, image, date, pin FROM posts WHERE user_id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
                $posts[] = $row;
            }
        }
        $stmt->close();
    }
    return $posts;
}

// Fungsi untuk menyimpan postingan baru
function savePost($post) {
    global $conn;
    $id = uniqid();
    $sql = "INSERT INTO posts (id, title, content, pin, image, date, user_id) VALUES (?, ?, ?, ?, ?, ?, ?)";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $date = date('Y-m-d H:i:s');
        $stmt->bind_param("ssssssi", $id, $post['title'], $post['content'], $post['pin'], $post['image'], $date, $post['user_id']);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }
    return false;
}

// Fungsi untuk mendapatkan satu postingan berdasarkan ID
function getPostById($id) {
    global $conn;
    $sql = "SELECT p.id, p.title, p.content, p.pin, p.image, p.date, u.full_name FROM posts p JOIN users u ON p.user_id = u.id WHERE p.id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("s", $id);
        $stmt->execute();
        $result = $stmt->get_result();
        $post = $result->fetch_assoc();
        $stmt->close();
        return $post;
    }
    return null;
}

// Fungsi untuk mendaftarkan pengguna baru
function registerUser($username, $full_name, $password) {
    global $conn;

    // Periksa apakah username sudah ada
    $check_sql = "SELECT username FROM users WHERE username = ?";
    $check_stmt = $conn->prepare($check_sql);
    $check_stmt->bind_param("s", $username);
    $check_stmt->execute();
    $check_result = $check_stmt->get_result();
    if ($check_result->num_rows > 0) {
        $check_stmt->close();
        return "Username sudah ada.";
    }
    $check_stmt->close();

    $sql = "INSERT INTO users (username, full_name, password, status, role) VALUES (?, ?, ?, 'pending', 'user')";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("sss", $username, $full_name, $password);
        $result = $stmt->execute();
        $stmt->close();
        if (!$result) {
            return "Terjadi kesalahan saat pendaftaran.";
        }
        return true;
    }
    return "Terjadi kesalahan saat pendaftaran.";
}

// Fungsi untuk mendapatkan pengguna yang belum disetujui
function getPendingUsers() {
    global $conn;
    $users = [];
    $sql = "SELECT id, username, full_name FROM users WHERE status = 'pending'";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $users[] = $row;
        }
    }
    return $users;
}

// Fungsi untuk mendapatkan semua pengguna (termasuk yang sudah disetujui)
function getAllUsers() {
    global $conn;
    $users = [];
    $sql = "SELECT id, username, full_name, role, status FROM users";
    $result = $conn->query($sql);
    if ($result && $result->num_rows > 0) {
        while($row = $result->fetch_assoc()) {
            $users[] = $row;
        }
    }
    return $users;
}

// Fungsi untuk mendapatkan jumlah total postingan per pengguna
function getPostCountByUserId($userId) {
    global $conn;
    $sql = "SELECT COUNT(*) as count FROM posts WHERE user_id = ?";
    $stmt = $conn->prepare($sql);
    $count = 0;
    if ($stmt) {
        $stmt->bind_param("i", $userId);
        $stmt->execute();
        $result = $stmt->get_result();
        $row = $result->fetch_assoc();
        $count = $row['count'];
        $stmt->close();
    }
    return $count;
}

// Fungsi untuk mereset kata sandi pengguna
function resetUserPassword($userId) {
    global $conn;
    $sql = "UPDATE users SET password = ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        // Kata sandi default
        $new_password = '123';
        $stmt->bind_param("si", $new_password, $userId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }
    return false;
}

// Fungsi untuk menyetujui pengguna dan menetapkan peran
function approveUser($userId, $role) {
    global $conn;
    $sql = "UPDATE users SET status = 'approved', role = ? WHERE id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("si", $role, $userId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }
    return false;
}

// Fungsi untuk menolak (menghapus) pengguna
function rejectUser($userId) {
    global $conn;
    $sql = "DELETE FROM users WHERE id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("i", $userId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }
    return false;
}

// Fungsi untuk menghapus pengguna dari database
function deleteUser($userId) {
    global $conn;
    // Hapus postingan yang terkait dengan pengguna ini
    $sql_posts = "DELETE FROM posts WHERE user_id = ?";
    $stmt_posts = $conn->prepare($sql_posts);
    if ($stmt_posts) {
        $stmt_posts->bind_param("i", $userId);
        $stmt_posts->execute();
        $stmt_posts->close();
    }

    // Hapus pengguna itu sendiri
    $sql_user = "DELETE FROM users WHERE id = ?";
    $stmt_user = $conn->prepare($sql_user);
    if ($stmt_user) {
        $stmt_user->bind_param("i", $userId);
        $result = $stmt_user->execute();
        $stmt_user->close();
        return $result;
    }
    return false;
}

// Fungsi untuk menghapus postingan dari database
function deletePost($postId) {
    global $conn;
    $sql = "DELETE FROM posts WHERE id = ?";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("s", $postId);
        $result = $stmt->execute();
        $stmt->close();
        return $result;
    }
    return false;
}

// Fungsi untuk memeriksa kredensial login
function checkUserLogin($username, $password) {
    global $conn;
    $sql = "SELECT id, password, role, status FROM users WHERE username = ? AND status = 'approved'";
    $stmt = $conn->prepare($sql);
    if ($stmt) {
        $stmt->bind_param("s", $username);
        $stmt->execute();
        $result = $stmt->get_result();
        $user = $result->fetch_assoc();
        $stmt->close();
        
        if ($user && $password === $user['password']) {
            return $user;
        }
    }
    return false;
}

session_start();