<?php

namespace CliqThemes\ORM\Database;

use wpdb;
use RuntimeException;

/**
 * Simple wrapper around the WordPress wpdb instance that powers the ORM.
 */
class Connection
{
    protected wpdb $wpdb;

    /**
     * Create a new connection wrapper optionally using a provided wpdb instance.
     *
     * @throws RuntimeException When no global wpdb instance is available.
     */
    public function __construct(?wpdb $wpdb = null)
    {
        if ($wpdb instanceof wpdb) {
            $this->wpdb = $wpdb;
            return;
        }

        if (isset($GLOBALS['wpdb']) && $GLOBALS['wpdb'] instanceof wpdb) {
            $this->wpdb = $GLOBALS['wpdb'];
            return;
        }

        throw new RuntimeException(esc_html('The global $wpdb instance is not available.'));
    }

    /**
     * Get the underlying wpdb instance in use by the connection.
     */
    public function getWpdb(): wpdb
    {
        return $this->wpdb;
    }

    /**
     * Retrieve the WordPress table prefix for the current site.
     */
    public function getTablePrefix(): string
    {
        return $this->wpdb->prefix;
    }

    /**
     * Prefix a table name with the WordPress table prefix when required.
     */
    public function table(string $table): string
    {
        if (str_starts_with($table, $this->wpdb->prefix)) {
            return $table;
        }

        return $this->wpdb->prefix . $table;
    }

    /**
     * Execute a select statement returning results as associative arrays.
     *
     * @param array<int, mixed> $bindings
     *
     * @return array<int, array<string, mixed>>
     */
    public function select(string $sql, array $bindings = []): array
    {
        return $this->executeSelect($sql, $bindings, 'ARRAY_A');
    }

    /**
     * Execute a select statement returning results as stdClass objects.
     *
     * @param array<int, mixed> $bindings
     */
    public function selectObjects(string $sql, array $bindings = []): array
    {
        return $this->executeSelect($sql, $bindings, 'OBJECT');
    }

    /**
     * Execute a select statement and return the first column of the first row.
     *
     * @param array<int, mixed> $bindings
     */
    public function value(string $sql, array $bindings = []): mixed
    {
        $results = $this->executeSelect($sql, $bindings, 'ARRAY_N');

        if (empty($results[0][0])) {
            return null;
        }

        return $results[0][0];
    }

    /**
     * Execute a select statement using the provided output type.
     *
     * @param array<int, mixed> $bindings
     */
    protected function executeSelect(string $sql, array $bindings, string $outputType): array
    {
        // $sql is assembled by the query builder, which quotes every identifier
        // it interpolates; user values arrive as $bindings and are placed by
        // prepare(). The sniff cannot follow that across the assignment.
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = empty($bindings) ? $sql : $this->wpdb->prepare($sql, $bindings);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        return $this->wpdb->get_results($prepared, $outputType) ?: [];
    }

    /**
     * Insert a record into the given table.
     *
     * @param array<string, mixed> $data
     * @param array<int, string> $format
     *
     * @throws RuntimeException When the underlying query fails.
     */
    public function insert(string $table, array $data, array $format = []): int
    {
        $table = $this->table($table);
        $this->wpdb->insert($table, $data, $format ?: null);
        $this->guard('insert', $table);

        return (int) $this->wpdb->insert_id;
    }

    /**
     * Update the given table using the provided data and constraints.
     *
     * @param array<string, mixed> $data
     * @param array<string, mixed> $where
     * @param array<int, string> $format
     * @param array<int, string> $whereFormat
     *
     * @throws RuntimeException When the underlying query fails.
     */
    public function update(string $table, array $data, array $where, array $format = [], array $whereFormat = []): int
    {
        $table = $this->table($table);
        $this->wpdb->update($table, $data, $where, $format ?: null, $whereFormat ?: null);
        $this->guard('update', $table);

        return (int) $this->wpdb->rows_affected;
    }

    /**
     * Delete rows from the given table matching the constraints.
     *
     * @param array<string, mixed> $where
     * @param array<int, string> $whereFormat
     *
     * @throws RuntimeException When the underlying query fails.
     */
    public function delete(string $table, array $where, array $whereFormat = []): int
    {
        $table = $this->table($table);
        $this->wpdb->delete($table, $where, $whereFormat ?: null);
        $this->guard('delete', $table);

        return (int) $this->wpdb->rows_affected;
    }

    /**
     * Execute an arbitrary SQL statement and return affected rows.
     *
     * @param array<int, mixed> $bindings
     *
     * @throws RuntimeException When the underlying query fails.
     */
    public function statement(string $sql, array $bindings = []): int
    {
        // See executeSelect(): identifiers are quoted by the builder, values are
        // bound through prepare().
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = empty($bindings) ? $sql : $this->wpdb->prepare($sql, $bindings);

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $this->wpdb->query($prepared);
        $this->guard('statement');

        return (int) $this->wpdb->rows_affected;
    }

    /**
     * Begin a database transaction. Requires InnoDB tables (MyISAM silently
     * ignores transactional statements).
     */
    public function beginTransaction(): void
    {
        $this->wpdb->query('START TRANSACTION');
    }

    /**
     * Commit the active database transaction.
     */
    public function commit(): void
    {
        $this->wpdb->query('COMMIT');
    }

    /**
     * Roll back the active database transaction.
     */
    public function rollBack(): void
    {
        $this->wpdb->query('ROLLBACK');
    }

    /**
     * Run $callback inside a transaction: commit on success, roll back and
     * rethrow on any throwable. MySQL does not support nested transactions, so
     * callers must not call this re-entrantly on the same connection.
     *
     * @template T
     *
     * @param callable():T $callback
     *
     * @return T
     */
    public function transaction(callable $callback): mixed
    {
        $this->beginTransaction();

        try {
            $result = $callback();
            $this->commit();

            return $result;
        } catch (\Throwable $e) {
            $this->rollBack();

            throw $e;
        }
    }

    /**
     * Throw when the last wpdb query recorded an error. wpdb clears last_error
     * at the start of each query, so this reflects only the operation above it.
     *
     * @throws RuntimeException
     */
    protected function guard(string $operation, string $table = ''): void
    {
        if ($this->wpdb->last_error !== '') {
            $target = $table !== '' ? " on {$table}" : '';

            throw new RuntimeException(esc_html(sprintf('Database %s failed%s: %s', $operation, $target, $this->wpdb->last_error)));
        }
    }
}

