<?php
/**
 * Database connection class using PDO
 */
class Database {
    private $host = 'localhost';
    private $db_name = 'my_database';
    private $username = 'db_user';
    private $password = 'db_password';
    private $conn;
    private $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ];
    
    /**
     * Get database connection
     * @return PDO
     */
    public function getConnection() {
        $this->conn = null;
        
        try {
            $dsn = "mysql:host={$this->host};dbname={$this->db_name};charset=utf8mb4";
            $this->conn = new PDO($dsn, $this->username, $this->password, $this->options);
        } catch(PDOException $e) {
            // Log the error
            error_log("Database Connection Error: " . $e->getMessage());
            // You might want to throw the exception again or handle it differently
            throw new Exception("Database connection failed: " . $e->getMessage());
        }
        
        return $this->conn;
    }
    
    /**
     * Execute a query with parameters
     * @param string $query SQL query
     * @param array $params Parameters for prepared statement
     * @return PDOStatement
     */
    public function query($query, $params = []) {
        try {
            $stmt = $this->conn->prepare($query);
            $stmt->execute($params);
            return $stmt;
        } catch(PDOException $e) {
            error_log("Query Error: " . $e->getMessage());
            throw new Exception("Query failed: " . $e->getMessage());
        }
    }
    
    /**
     * Get a single record
     * @param string $query SQL query
     * @param array $params Parameters for prepared statement
     * @return array|false Single record or false if not found
     */
    public function getRecord($query, $params = []) {
        $stmt = $this->query($query, $params);
        return $stmt->fetch();
    }
    
    /**
     * Get multiple records
     * @param string $query SQL query
     * @param array $params Parameters for prepared statement
     * @return array Array of records
     */
    public function getRecords($query, $params = []) {
        $stmt = $this->query($query, $params);
        return $stmt->fetchAll();
    }
    
    /**
     * Insert a record and return the last insert ID
     * @param string $table Table name
     * @param array $data Associative array of column => value
     * @return int|string Last insert ID
     */
    public function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        
        $query = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
        $this->query($query, array_values($data));
        
        return $this->conn->lastInsertId();
    }
    
    /**
     * Update records
     * @param string $table Table name
     * @param array $data Associative array of column => value
     * @param string $where WHERE clause
     * @param array $params Parameters for WHERE clause
     * @return int Number of affected rows
     */
    public function update($table, $data, $where, $params = []) {
        $set = [];
        foreach (array_keys($data) as $column) {
            $set[] = "{$column} = ?";
        }
        $set = implode(', ', $set);
        
        $query = "UPDATE {$table} SET {$set} WHERE {$where}";
        $stmt = $this->query($query, array_merge(array_values($data), $params));
        
        return $stmt->rowCount();
    }
    
    /**
     * Delete records
     * @param string $table Table name
     * @param string $where WHERE clause
     * @param array $params Parameters for WHERE clause
     * @return int Number of affected rows
     */
    public function delete($table, $where, $params = []) {
        $query = "DELETE FROM {$table} WHERE {$where}";
        $stmt = $this->query($query, $params);
        
        return $stmt->rowCount();
    }
}

// Example usage:
try {
    // Create database instance
    $database = new Database();
    $db = $database->getConnection();
    
    // Get a single user
    $user = $database->getRecord("SELECT * FROM users WHERE id = ?", [1]);
    
    // Get all active users
    $activeUsers = $database->getRecords(
        "SELECT * FROM users WHERE status = ? ORDER BY name",
        ['active']
    );
    
    // Insert a new user
    $userId = $database->insert('users', [
        'name' => 'John Doe',
        'email' => 'john@example.com',
        'status' => 'active'
    ]);
    
    // Update a user
    $affected = $database->update(
        'users',
        ['status' => 'inactive'],
        'id = ?',
        [5]
    );
    
    // Delete a user
    $deleted = $database->delete('users', 'id = ?', [6]);
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}