<?php
/**
 * Database Helper
 * Handles Transaction Wrapping, Row Locking, and Atomic Concurrency Operations
 */

require_once __DIR__ . '/../../database/Database.php';

class DB {
    private static int $transactionDepth = 0;

    /**
     * Execute a callback within an atomic Database Transaction
     * Automatically commits on success, rolls back on any exception/error
     */
    public static function transaction(callable $callback) {
        $pdo = Database::getInstance();

        if (self::$transactionDepth === 0) {
            $pdo->beginTransaction();
        } else {
            $pdo->exec("SAVEPOINT TRANS_SP_" . self::$transactionDepth);
        }
        self::$transactionDepth++;

        try {
            $result = $callback($pdo);

            self::$transactionDepth--;
            if (self::$transactionDepth === 0) {
                $pdo->commit();
            } else {
                $pdo->exec("RELEASE SAVEPOINT TRANS_SP_" . (self::$transactionDepth + 1));
            }

            return $result;
        } catch (Throwable $e) {
            self::$transactionDepth--;
            if (self::$transactionDepth === 0) {
                if ($pdo->inTransaction()) {
                    $pdo->rollBack();
                }
            } else {
                $pdo->exec("ROLLBACK TO SAVEPOINT TRANS_SP_" . (self::$transactionDepth + 1));
            }

            throw $e;
        }
    }

    /**
     * Execute a query with Row-Level Lock for Concurrency Protection
     */
    public static function selectForUpdate(string $sql, array $params = []): array {
        $pdo = Database::getInstance();
        
        // Append FOR UPDATE if not present
        if (stripos($sql, 'FOR UPDATE') === false) {
            $sql .= ' FOR UPDATE';
        }

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll() ?: [];
    }

    /**
     * Single Row Lock
     */
    public static function lockRow(string $table, int $id, string $primaryKey = 'id'): ?array {
        $pdo = Database::getInstance();
        $sql = "SELECT * FROM `{$table}` WHERE `{$primaryKey}` = :id FOR UPDATE";
        $stmt = $pdo->prepare($sql);
        $stmt->execute([':id' => $id]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    /**
     * Atomic safe decrement (e.g. Stock decrement with stock >= qty protection)
     * Returns true if decremented, false if condition failed (e.g. out of stock)
     */
    public static function atomicDecrement(string $table, string $column, float $amount, string $where, array $params = [], bool $preventNegative = true): bool {
        $pdo = Database::getInstance();
        $condition = $where;
        if ($preventNegative) {
            $condition .= " AND `{$column}` >= :_min_sub_amt";
            $params[':_min_sub_amt'] = $amount;
        }

        $sql = "UPDATE `{$table}` SET `{$column}` = `{$column}` - :_sub_amt WHERE {$condition}";
        $params[':_sub_amt'] = $amount;

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }

    /**
     * Atomic safe increment
     */
    public static function atomicIncrement(string $table, string $column, float $amount, string $where, array $params = []): bool {
        $pdo = Database::getInstance();
        $sql = "UPDATE `{$table}` SET `{$column}` = `{$column}` + :_add_amt WHERE {$where}";
        $params[':_add_amt'] = $amount;

        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount() > 0;
    }
}