<?php
/**
 * Database Connection Manager
 *
 * @package ZenoChatbot\Framework\Database
 */

namespace ZenoChatbot\Framework\Database;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Database Connection Manager for WordPress
 */
class Connection
{
    /**
     * WordPress database instance
     *
     * @var \wpdb
     */
    protected $db;

    /**
     * Transaction level
     *
     * @var int
     */
    protected $transactionLevel = 0;

    /**
     * Create a new connection instance
     */
    public function __construct()
    {
        global $wpdb;
        $this->db = $wpdb;
    }

    /**
     * Get the WordPress database instance
     *
     * @return \wpdb
     */
    public function getDb()
    {
        return $this->db;
    }

    /**
     * Create a new query builder instance
     *
     * @param string $table
     * @return QueryBuilder
     */
    public function table($table)
    {
        return new QueryBuilder($table);
    }

    /**
     * Execute a raw SQL query
     *
     * @param string $sql
     * @param array $bindings
     * @return mixed
     */
    public function query($sql, array $bindings = [])
    {
        if (!empty($bindings)) {
            $sql = $this->db->prepare($sql, $bindings);
        }

        return $this->db->query($sql);
    }

    /**
     * Execute a SELECT query and return results
     *
     * @param string $sql
     * @param array $bindings
     * @return array
     */
    public function select($sql, array $bindings = [])
    {
        if (!empty($bindings)) {
            $sql = $this->db->prepare($sql, $bindings);
        }

        return $this->db->get_results($sql, ARRAY_A) ?: [];
    }

    /**
     * Execute a SELECT query and return first result
     *
     * @param string $sql
     * @param array $bindings
     * @return array|null
     */
    public function selectOne($sql, array $bindings = [])
    {
        if (!empty($bindings)) {
            $sql = $this->db->prepare($sql, $bindings);
        }

        return $this->db->get_row($sql, ARRAY_A);
    }

    /**
     * Execute a SELECT query and return single value
     *
     * @param string $sql
     * @param array $bindings
     * @return mixed
     */
    public function selectValue($sql, array $bindings = [])
    {
        if (!empty($bindings)) {
            $sql = $this->db->prepare($sql, $bindings);
        }

        return $this->db->get_var($sql);
    }

    /**
     * Insert a record
     *
     * @param string $table
     * @param array $data
     * @return int|false
     */
    public function insert($table, array $data)
    {
        $table = $this->db->prefix . $table;
        $result = $this->db->insert($table, $data);

        if ($result === false) {
            return false;
        }

        return $this->db->insert_id;
    }

    /**
     * Update records
     *
     * @param string $table
     * @param array $data
     * @param array $where
     * @return int|false
     */
    public function update($table, array $data, array $where)
    {
        $table = $this->db->prefix . $table;
        return $this->db->update($table, $data, $where);
    }

    /**
     * Delete records
     *
     * @param string $table
     * @param array $where
     * @return int|false
     */
    public function delete($table, array $where)
    {
        $table = $this->db->prefix . $table;
        return $this->db->delete($table, $where);
    }

    /**
     * Get the last insert ID
     *
     * @return int
     */
    public function lastInsertId()
    {
        return $this->db->insert_id;
    }

    /**
     * Get the number of affected rows
     *
     * @return int
     */
    public function affectedRows()
    {
        return $this->db->rows_affected;
    }

    /**
     * Get the last error
     *
     * @return string
     */
    public function lastError()
    {
        return $this->db->last_error;
    }

    /**
     * Check if table exists
     *
     * @param string $table
     * @return bool
     */
    public function tableExists($table)
    {
        $table = $this->db->prefix . $table;
        $result = $this->db->get_var($this->db->prepare(
            "SHOW TABLES LIKE %s",
            $table
        ));

        return $result === $table;
    }

    /**
     * Get table columns
     *
     * @param string $table
     * @return array
     */
    public function getColumns($table)
    {
        $table = $this->db->prefix . $table;
        $columns = $this->db->get_results("DESCRIBE {$table}", ARRAY_A);

        return array_column($columns, 'Field');
    }

    /**
     * Begin a database transaction
     *
     * @return bool
     */
    public function beginTransaction()
    {
        if ($this->transactionLevel === 0) {
            $result = $this->db->query('START TRANSACTION');
            if ($result !== false) {
                $this->transactionLevel++;
                return true;
            }
            return false;
        }

        // Nested transaction using savepoints
        $savepoint = 'sp_' . $this->transactionLevel;
        $result = $this->db->query("SAVEPOINT {$savepoint}");
        if ($result !== false) {
            $this->transactionLevel++;
            return true;
        }

        return false;
    }

    /**
     * Commit the database transaction
     *
     * @return bool
     */
    public function commit()
    {
        if ($this->transactionLevel === 0) {
            return false;
        }

        if ($this->transactionLevel === 1) {
            $result = $this->db->query('COMMIT');
            if ($result !== false) {
                $this->transactionLevel = 0;
                return true;
            }
            return false;
        }

        // Release savepoint for nested transaction
        $savepoint = 'sp_' . ($this->transactionLevel - 1);
        $result = $this->db->query("RELEASE SAVEPOINT {$savepoint}");
        if ($result !== false) {
            $this->transactionLevel--;
            return true;
        }

        return false;
    }

    /**
     * Rollback the database transaction
     *
     * @return bool
     */
    public function rollback()
    {
        if ($this->transactionLevel === 0) {
            return false;
        }

        if ($this->transactionLevel === 1) {
            $result = $this->db->query('ROLLBACK');
            if ($result !== false) {
                $this->transactionLevel = 0;
                return true;
            }
            return false;
        }

        // Rollback to savepoint for nested transaction
        $savepoint = 'sp_' . ($this->transactionLevel - 1);
        $result = $this->db->query("ROLLBACK TO SAVEPOINT {$savepoint}");
        if ($result !== false) {
            $this->transactionLevel--;
            return true;
        }

        return false;
    }

    /**
     * Execute a callback within a transaction
     *
     * @param callable $callback
     * @return mixed
     * @throws \Exception
     */
    public function transaction(callable $callback)
    {
        $this->beginTransaction();

        try {
            $result = $callback($this);
            $this->commit();
            return $result;
        } catch (\Exception $e) {
            $this->rollback();
            throw $e;
        }
    }

    /**
     * Get current transaction level
     *
     * @return int
     */
    public function getTransactionLevel()
    {
        return $this->transactionLevel;
    }

    /**
     * Check if currently in a transaction
     *
     * @return bool
     */
    public function inTransaction()
    {
        return $this->transactionLevel > 0;
    }

    /**
     * Escape a value for use in SQL
     *
     * @param mixed $value
     * @return string
     */
    public function escape($value)
    {
        return $this->db->_real_escape($value);
    }

    /**
     * Get the table prefix
     *
     * @return string
     */
    public function getPrefix()
    {
        return $this->db->prefix;
    }
}
