<?php
/**
 * Database Configuration
 * GHAMECC Choir Management Platform
 */

// Include logger if not already included
if (!function_exists('logError')) {
    require_once __DIR__ . '/logger.php';
}

// Include environment configuration
require_once __DIR__ . '/env_config.php';

// Database credentials are now set in env_config.php
// $host, $username, $password, and $database variables are defined there

// Create connection
$conn = new mysqli($host, $username, $password, $database);

// Check connection
if ($conn->connect_error) {
    // Log connection error if logger is available
    if (function_exists('logError')) {
        logError("Database connection failed", ['error' => $conn->connect_error]);
    }
    die("Connection failed: " . $conn->connect_error);
}

// Set character set
$conn->set_charset("utf8mb4");

/**
 * Helper function to execute SELECT queries and return all rows
 * @param string $query SQL query
 * @param array $params Parameters for prepared statement
 * @return array Result rows
 */
function fetchRows($query, $params = []) {
    global $conn;
    
    $stmt = $conn->prepare($query);
    
    // Check if prepare was successful
    if ($stmt === false) {
        // Log prepare error if logger is available
        if (function_exists('logError')) {
            logError("SQL Prepare Error", ['error' => $conn->error, 'query' => $query]);
        } else {
            error_log("SQL Prepare Error: " . $conn->error . " in query: " . $query);
        }
        return [];
    }
    
    if (!empty($params)) {
        $types = '';
        $bindParams = [];
        
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_double($param) || is_float($param)) {
                $types .= 'd';
            } elseif (is_string($param)) {
                $types .= 's';
            } else {
                $types .= 'b';
            }
            $bindParams[] = $param;
        }
        
        $refParams = [$types];
        foreach ($bindParams as $key => $value) {
            $refParams[] = &$bindParams[$key];
        }
        
        call_user_func_array([$stmt, 'bind_param'], $refParams);
    }
    
    $executeResult = $stmt->execute();
    
    // Check if execute was successful
    if ($executeResult === false) {
        // Log execute error if logger is available
        if (function_exists('logError')) {
            logError("SQL Execute Error", ['error' => $stmt->error, 'query' => $query]);
        } else {
            error_log("SQL Execute Error: " . $stmt->error . " in query: " . $query);
        }
        $stmt->close();
        return [];
    }
    
    $result = $stmt->get_result();
    
    $rows = [];
    while ($row = $result->fetch_assoc()) {
        $rows[] = $row;
    }
    
    $stmt->close();
    return $rows;
}

/**
 * Helper function to execute SELECT queries and return a single row
 * @param string $query SQL query
 * @param array $params Parameters for prepared statement
 * @return array|null Single result row or null if no results
 */
function fetchRow($query, $params = []) {
    $rows = fetchRows($query, $params);
    return !empty($rows) ? $rows[0] : null;
}

/**
 * Helper function to fetch a single value from database
 * @param string $query SQL query
 * @param array $params Parameters for prepared statement
 * @return mixed Single value or null if no results
 */
function fetchValue($query, $params = []) {
    $row = fetchRow($query, $params);
    if ($row && is_array($row)) {
        return reset($row); // Get first value from the row
    }
    return null;
}

/**
 * Helper function to execute INSERT, UPDATE, DELETE queries
 * @param string $query SQL query
 * @param array $params Parameters for prepared statement
 * @return int|bool Number of affected rows or false on failure
 */
function executeQuery($query, $params = []) {
    global $conn;
    
    $stmt = $conn->prepare($query);
    
    // Check if prepare was successful
    if ($stmt === false) {
        // Log prepare error if logger is available
        if (function_exists('logError')) {
            logError("SQL Prepare Error", ['error' => $conn->error, 'query' => $query]);
        } else {
            error_log("SQL Prepare Error: " . $conn->error . " in query: " . $query);
        }
        return false;
    }
    
    if (!empty($params)) {
        $types = '';
        $bindParams = [];
        
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_double($param) || is_float($param)) {
                $types .= 'd';
            } elseif (is_string($param)) {
                $types .= 's';
            } else {
                $types .= 'b';
            }
            $bindParams[] = $param;
        }
        
        $refParams = [$types];
        foreach ($bindParams as $key => $value) {
            $refParams[] = &$bindParams[$key];
        }
        
        call_user_func_array([$stmt, 'bind_param'], $refParams);
    }
    
    $executeResult = $stmt->execute();
    
    // Check if execute was successful
    if ($executeResult === false) {
        // Log execute error if logger is available
        if (function_exists('logError')) {
            logError("SQL Execute Error", ['error' => $stmt->error, 'query' => $query]);
        } else {
            error_log("SQL Execute Error: " . $stmt->error . " in query: " . $query);
        }
        $stmt->close();
        return false;
    }
    
    $affectedRows = $stmt->affected_rows;
    
    $stmt->close();
    return $affectedRows;
}

/**
 * Get the ID of the last inserted record
 * @return int Last insert ID
 */
function getLastInsertId() {
    global $conn;
    return $conn->insert_id;
}

/**
 * Escape and sanitize string for safe database operations
 * @param string $value The string to escape
 * @return string Escaped string
 */
function escapeString($value) {
    global $conn;
    
    // First trim the string
    $value = trim($value);
    
    // Use mysqli real_escape_string for SQL safety
    if ($conn) {
        $value = $conn->real_escape_string($value);
    }
    
    return $value;
}
