<?php
/**
 * RUHONDO BEACH RESORT HMS + POS System
 * Database Connection and Helper Functions
 * Developer: JOACHIM'S INNOTECH
 */

// Prevent direct access
if (!defined('BASE_PATH')) {
    exit('Direct access not permitted');
}

/**
 * Get PDO database connection (Singleton pattern)
 */
function get_db_connection() {
    static $pdo = null;
    
    if ($pdo === null) {
        try {
            $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . DB_CHARSET . " COLLATE " . DB_CHARSET . "_unicode_ci",
                PDO::MYSQL_ATTR_FOUND_ROWS => true,
                PDO::ATTR_PERSISTENT => false
            ];
            
            $pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
            
            // Set timezone for connection
            $pdo->exec("SET time_zone = '" . date('P') . "'");
            
        } catch (PDOException $e) {
            // Log error
            if (function_exists('log_message')) {
                log_message("Database connection failed: " . $e->getMessage(), 'ERROR');
            } else {
                error_log("Database connection failed: " . $e->getMessage());
            }
            
            // Show user-friendly message
            die("Database connection failed. Please check your configuration and try again.");
        }
    }
    
    return $pdo;
}

/**
 * Begin transaction
 */
function begin_transaction() {
    return get_db_connection()->beginTransaction();
}

/**
 * Commit transaction
 */
function commit_transaction() {
    return get_db_connection()->commit();
}

/**
 * Rollback transaction
 */
function rollback_transaction() {
    return get_db_connection()->rollBack();
}

/**
 * Execute query with parameters and return statement
 */
function db_query($sql, $params = []) {
    try {
        $pdo = get_db_connection();
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    } catch (PDOException $e) {
        if (function_exists('log_message')) {
            log_message("Query failed: " . $e->getMessage() . " SQL: " . $sql, 'ERROR');
        }
        throw $e;
    }
}

/**
 * Fetch single row
 */
function db_fetch_one($sql, $params = []) {
    $stmt = db_query($sql, $params);
    return $stmt->fetch();
}

/**
 * Fetch all rows
 */
function db_fetch_all($sql, $params = []) {
    $stmt = db_query($sql, $params);
    return $stmt->fetchAll();
}

/**
 * Fetch single value
 */
function db_fetch_value($sql, $params = []) {
    $stmt = db_query($sql, $params);
    return $stmt->fetchColumn();
}

/**
 * Insert record and return last insert ID
 */
function db_insert($table, $data) {
    $pdo = get_db_connection();
    
    // Build column list and placeholders
    $columns = implode(', ', array_keys($data));
    $placeholders = ':' . implode(', :', array_keys($data));
    
    $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";
    
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($data);
        return $pdo->lastInsertId();
    } catch (PDOException $e) {
        if (function_exists('log_message')) {
            log_message("Insert failed: " . $e->getMessage() . " SQL: " . $sql, 'ERROR');
        }
        throw $e;
    }
}

/**
 * Update record - FIXED VERSION
 * Handles both named and positional parameters correctly
 */
function db_update($table, $data, $where, $where_params = []) {
    $pdo = get_db_connection();
    
    // Build SET clause with unique parameter names to avoid conflicts
    $set_parts = [];
    $params = [];
    
    foreach ($data as $column => $value) {
        $param_name = 'set_' . $column . '_' . uniqid();
        $set_parts[] = "{$column} = :{$param_name}";
        $params[$param_name] = $value;
    }
    $set_clause = implode(', ', $set_parts);
    
    // Handle WHERE clause parameters
    $where_clause = $where;
    
    if (!empty($where_params)) {
        // Check if where clause uses positional (?) or named parameters
        if (strpos($where, '?') !== false) {
            // Convert positional parameters to named parameters
            $parts = explode('?', $where);
            $where_clause = '';
            foreach ($parts as $index => $part) {
                $where_clause .= $part;
                if ($index < count($parts) - 1) {
                    $param_name = 'where_' . $index . '_' . uniqid();
                    $where_clause .= ':' . $param_name;
                    if (isset($where_params[$index])) {
                        $params[$param_name] = $where_params[$index];
                    }
                }
            }
        } else {
            // Already using named parameters, just merge them
            foreach ($where_params as $key => $value) {
                // Make sure where parameters don't conflict with set parameters
                $new_key = 'where_' . $key;
                $where_clause = str_replace(':' . $key, ':' . $new_key, $where_clause);
                $params[$new_key] = $value;
            }
        }
    }
    
    $sql = "UPDATE {$table} SET {$set_clause} WHERE {$where_clause}";
    
    try {
        // For debugging - uncomment if needed
        // error_log("SQL: " . $sql);
        // error_log("Params: " . print_r($params, true));
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    } catch (PDOException $e) {
        if (function_exists('log_message')) {
            log_message("Update failed: " . $e->getMessage() . " SQL: " . $sql, 'ERROR');
        }
        error_log("Update failed: " . $e->getMessage() . " SQL: " . $sql);
        error_log("Params: " . print_r($params, true));
        throw $e;
    }
}

/**
 * Alternative simplified update function using only positional parameters
 * Use this if you prefer simplicity over flexibility
 */
function db_update_simple($table, $data, $where, $where_params = []) {
    $pdo = get_db_connection();
    
    // Build SET clause with positional parameters
    $set_parts = [];
    $params = [];
    
    foreach ($data as $column => $value) {
        $set_parts[] = "{$column} = ?";
        $params[] = $value;
    }
    $set_clause = implode(', ', $set_parts);
    
    // Add where parameters
    foreach ($where_params as $param) {
        $params[] = $param;
    }
    
    $sql = "UPDATE {$table} SET {$set_clause} WHERE {$where}";
    
    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    } catch (PDOException $e) {
        if (function_exists('log_message')) {
            log_message("Update failed: " . $e->getMessage() . " SQL: " . $sql, 'ERROR');
        }
        throw $e;
    }
}

/**
 * Delete record
 */
function db_delete($table, $where, $params = []) {
    $sql = "DELETE FROM {$table} WHERE {$where}";
    
    try {
        $stmt = db_query($sql, $params);
        return $stmt->rowCount();
    } catch (PDOException $e) {
        if (function_exists('log_message')) {
            log_message("Delete failed: " . $e->getMessage() . " SQL: " . $sql, 'ERROR');
        }
        throw $e;
    }
}

/**
 * Check if record exists
 */
function db_exists($table, $where, $params = []) {
    $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
    return db_fetch_value($sql, $params) > 0;
}

/**
 * Get count of records
 */
function db_count($table, $where = '1', $params = []) {
    $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
    return db_fetch_value($sql, $params);
}

/**
 * Paginate results
 */
function db_paginate($table, $columns = '*', $where = '1', $params = [], $order_by = 'created_at DESC', $page = 1, $per_page = 25) {
    $offset = ($page - 1) * $per_page;
    
    // Get total count
    $count_sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}";
    $total = db_fetch_value($count_sql, $params);
    
    // Get paginated data
    $data_sql = "SELECT {$columns} FROM {$table} WHERE {$where} ORDER BY {$order_by} LIMIT {$per_page} OFFSET {$offset}";
    $data = db_fetch_all($data_sql, $params);
    
    // Calculate pagination info
    $total_pages = ceil($total / $per_page);
    $has_previous = $page > 1;
    $has_next = $page < $total_pages;
    
    return [
        'data' => $data,
        'total' => $total,
        'per_page' => $per_page,
        'current_page' => $page,
        'total_pages' => $total_pages,
        'has_previous' => $has_previous,
        'has_next' => $has_next,
        'previous_page' => $has_previous ? $page - 1 : null,
        'next_page' => $has_next ? $page + 1 : null,
        'from' => $offset + 1,
        'to' => min($offset + $per_page, $total)
    ];
}

// ... (rest of your functions remain the same - build_where_clause, get_next_code, 
// get_room_availability, calculate_room_rate, generate_booking_code, 
// generate_invoice_number, generate_receipt_number, generate_transaction_code,
// get_dashboard_stats, search_guests, get_recent_activities, 
// test_db_connection, get_db_size, backup_database)

// [KEEP ALL YOUR EXISTING FUNCTIONS BELOW THIS LINE - they don't need changes]
// ...