<?php
/**
 * Database Connection
 * LandSales Pro - Professional Land Sales Management
 *
 * This file handles database connection using config settings
 */

// Load configuration if not already loaded
if (!defined('DB_HOST')) {
    if (!defined('APP_RUNNING')) {
        define('APP_RUNNING', true);
    }
    require_once __DIR__ . '/config.php';
}

// Create connection
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

// Check connection
if ($conn->connect_error) {
    if (IS_PRODUCTION) {
        // Log error but show generic message in production
        error_log("Database connection failed: " . $conn->connect_error);
        die("Connection error. Please try again later.");
    } else {
        die("Could not connect to MySQL: " . $conn->connect_error . "<br><br>Make sure you have:<br>1. Started MySQL in XAMPP<br>2. Created the 'landsales' database<br>3. Run the database/schema.sql file");
    }
}

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

/**
 * Execute a prepared statement and return result
 *
 * @param string $sql SQL query with placeholders
 * @param string $types Parameter types (s=string, i=integer, d=double, b=blob)
 * @param array $params Parameters to bind
 * @return mysqli_result|bool
 */
function db_query($sql, $types = '', $params = []) {
    global $conn;

    if (empty($types) || empty($params)) {
        return $conn->query($sql);
    }

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        if (!IS_PRODUCTION) {
            die("Query preparation failed: " . $conn->error);
        }
        return false;
    }

    $stmt->bind_param($types, ...$params);
    $stmt->execute();

    return $stmt->get_result();
}

/**
 * Execute a prepared statement and return affected rows
 *
 * @param string $sql SQL query with placeholders
 * @param string $types Parameter types
 * @param array $params Parameters to bind
 * @return int|false Affected rows or false on failure
 */
function db_execute($sql, $types = '', $params = []) {
    global $conn;

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        if (!IS_PRODUCTION) {
            die("Query preparation failed: " . $conn->error);
        }
        return false;
    }

    if (!empty($types) && !empty($params)) {
        $stmt->bind_param($types, ...$params);
    }

    if ($stmt->execute()) {
        return $stmt->affected_rows;
    }

    return false;
}

/**
 * Insert a record and return the insert ID
 *
 * @param string $table Table name
 * @param array $data Associative array of column => value
 * @return int|false Insert ID or false on failure
 */
function db_insert($table, $data) {
    global $conn;

    $columns = implode(', ', array_keys($data));
    $placeholders = implode(', ', array_fill(0, count($data), '?'));
    $types = str_repeat('s', count($data));
    $values = array_values($data);

    $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return false;
    }

    $stmt->bind_param($types, ...$values);

    if ($stmt->execute()) {
        return $conn->insert_id;
    }

    return false;
}

/**
 * Update records in a table
 *
 * @param string $table Table name
 * @param array $data Associative array of column => value
 * @param string $where WHERE clause (without WHERE keyword)
 * @param string $whereTypes Parameter types for WHERE
 * @param array $whereParams Parameters for WHERE clause
 * @return int|false Affected rows or false on failure
 */
function db_update($table, $data, $where, $whereTypes = '', $whereParams = []) {
    global $conn;

    $setParts = [];
    foreach (array_keys($data) as $column) {
        $setParts[] = "{$column} = ?";
    }
    $setClause = implode(', ', $setParts);

    $types = str_repeat('s', count($data)) . $whereTypes;
    $params = array_merge(array_values($data), $whereParams);

    $sql = "UPDATE {$table} SET {$setClause} WHERE {$where}";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return false;
    }

    $stmt->bind_param($types, ...$params);

    if ($stmt->execute()) {
        return $stmt->affected_rows;
    }

    return false;
}

/**
 * Fetch a single row
 *
 * @param string $sql SQL query
 * @param string $types Parameter types
 * @param array $params Parameters to bind
 * @return array|null
 */
function db_fetch_one($sql, $types = '', $params = []) {
    $result = db_query($sql, $types, $params);
    if ($result && $result->num_rows > 0) {
        return $result->fetch_assoc();
    }
    return null;
}

/**
 * Fetch all rows
 *
 * @param string $sql SQL query
 * @param string $types Parameter types
 * @param array $params Parameters to bind
 * @return array
 */
function db_fetch_all($sql, $types = '', $params = []) {
    $result = db_query($sql, $types, $params);
    if ($result) {
        return $result->fetch_all(MYSQLI_ASSOC);
    }
    return [];
}

/**
 * Escape a string for safe use in queries
 *
 * @param string $str String to escape
 * @return string
 */
function db_escape($str) {
    global $conn;
    return $conn->real_escape_string($str);
}

/**
 * Get the last insert ID
 *
 * @return int
 */
function db_last_id() {
    global $conn;
    return $conn->insert_id;
}

/**
 * Get the last error
 *
 * @return string
 */
function db_error() {
    global $conn;
    return $conn->error;
}

/**
 * Begin a transaction
 */
function db_begin() {
    global $conn;
    $conn->begin_transaction();
}

/**
 * Commit a transaction
 */
function db_commit() {
    global $conn;
    $conn->commit();
}

/**
 * Rollback a transaction
 */
function db_rollback() {
    global $conn;
    $conn->rollback();
}
