<?php
/**
 * DB Security Template - Best Practices for Rev. Dr. Sally Julie Armah Memorial Website
 * 
 * This file demonstrates how to implement:
 * 1. Secure Database Connections (PDO)
 * 2. Prepared Statements (SQL Injection Prevention)
 * 3. Secure Password Hashing
 */

// --- 1. Secure Connection (PDO) ---
function get_db_connection() {
    $host = 'localhost';
    $db   = 'memorial_db';
    $user = 'secure_user';
    $pass = 'secure_password';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,       // Fetch as associative array
        PDO::ATTR_EMULATE_PREPARES   => false,                  // Use real prepared statements
    ];

    try {
         return new PDO($dsn, $user, $pass, $options);
    } catch (\PDOException $e) {
         // In production, log error to file, do NOT show to user
         error_log($e->getMessage());
         die("A database error occurred. Please try again later.");
    }
}

// --- 2. Prepared Statements (SQL Injection Prevention) ---
/**
 * Example: Fetching a tribute by ID securely
 */
function get_tribute_by_id($id) {
    $pdo = get_db_connection();
    
    // NEVER concatenate user input like: "SELECT * FROM tributes WHERE id = " . $id
    // ALWAYS use placeholders (?) or named placeholders (:id)
    $stmt = $pdo->prepare("SELECT * FROM tributes WHERE id = ? AND status = 'approved'");
    $stmt->execute([$id]);
    return $stmt->fetch();
}

/**
 * Example: Saving a new tribute securely
 */
function save_tribute($name, $message) {
    $pdo = get_db_connection();
    
    // Sanitize input if needed, but prepared statements handle the escaping
    $stmt = $pdo->prepare("INSERT INTO tributes (name, message, created_at) VALUES (?, ?, NOW())");
    return $stmt->execute([$name, $message]);
}

// --- 3. Secure Password Storage ---
/**
 * Example: Hashing a password for registration
 */
function register_user($username, $password) {
    $pdo = get_db_connection();
    
    // Use Bcrypt (default) or Argon2 (if available)
    $hashed_password = password_hash($password, PASSWORD_DEFAULT);
    
    $stmt = $pdo->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
    return $stmt->execute([$username, $hashed_password]);
}

/**
 * Example: Verifying a password for login
 */
function login_user($username, $password) {
    $pdo = get_db_connection();
    
    $stmt = $pdo->prepare("SELECT password FROM users WHERE username = ?");
    $stmt->execute([$username]);
    $user = $stmt->fetch();
    
    if ($user && password_verify($password, $user['password'])) {
        // Password is correct
        return true;
    }
    return false;
}
