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

namespace ZenoChatbot\Framework\Database\ORM;

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

/**
 * ORM Database Connection (Uses $wpdb directly)
 */
class Connection
{
    /**
     * WordPress database instance
     *
     * @var \wpdb
     */
    protected $wpdb;

    /**
     * Database type (mysql or sqlite)
     *
     * @var string
     */
    protected $type;

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

    /**
     * Detect database type
     *
     * @return string
     */
    protected function detectDatabaseType()
    {
        // Check if using SQLite
        if (defined('DB_TYPE') && strtolower(DB_TYPE) === 'sqlite') {
            return 'sqlite';
        }

        // Check WordPress database type
        if (isset($this->wpdb->dbh) && is_object($this->wpdb->dbh)) {
            // WordPress uses mysqli, not PDO
            if ($this->wpdb->dbh instanceof \mysqli) {
                // For mysqli, we can check the server info
                $serverInfo = $this->wpdb->dbh->server_info;
                if (stripos($serverInfo, 'sqlite') !== false) {
                    return 'sqlite';
                }
                return 'mysql';
            }
            // If it's PDO (rare case)
            if ($this->wpdb->dbh instanceof \PDO) {
                $driver = $this->wpdb->dbh->getAttribute(\PDO::ATTR_DRIVER_NAME);
                return $driver === 'sqlite' ? 'sqlite' : 'mysql';
            }
        }

        // Default to MySQL (WordPress standard)
        return 'mysql';
    }

    /**
     * Get database type
     *
     * @return string
     */
    public function getType()
    {
        return $this->type;
    }

    /**
     * Check if database is MySQL
     *
     * @return bool
     */
    public function isMysql()
    {
        return $this->type === 'mysql';
    }

    /**
     * Check if database is SQLite
     *
     * @return bool
     */
    public function isSqlite()
    {
        return $this->type === 'sqlite';
    }

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

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

    /**
     * Execute a select query and return single row
     *
     * @param string $sql
     * @param array $bindings
     * @return array|null
     */
    public function selectOne($sql, array $bindings = [])
    {
        if (!empty($bindings)) {
            $sql = $this->wpdb->prepare($sql, $bindings);
        }
        
        $result = $this->wpdb->get_row($sql, ARRAY_A);
        return $result ?: null;
    }

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

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

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

    /**
     * Check if table exists
     *
     * @param string $table
     * @return bool
     */
    public function tableExists($table)
    {
        $table = $this->wpdb->prefix . $table;
        
        if ($this->isSqlite()) {
            $result = $this->wpdb->get_var($this->wpdb->prepare(
                "SELECT name FROM sqlite_master WHERE type='table' AND name=%s",
                $table
            ));
        } else {
            $result = $this->wpdb->get_var($this->wpdb->prepare(
                "SHOW TABLES LIKE %s",
                $table
            ));
        }
        
        return $result === $table;
    }

    /**
     * Get table columns
     *
     * @param string $table
     * @return array
     */
    public function getColumns($table)
    {
        $table = $this->wpdb->prefix . $table;
        
        if ($this->isSqlite()) {
            $columns = $this->wpdb->get_results("PRAGMA table_info({$table})", ARRAY_A);
            return array_column($columns, 'name');
        } else {
            $columns = $this->wpdb->get_results("DESCRIBE {$table}", ARRAY_A);
            return array_column($columns, 'Field');
        }
    }

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

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

    /**
     * Get charset collate
     *
     * @return string
     */
    public function getCharsetCollate()
    {
        // SQLite doesn't use charset collate
        if ($this->isSqlite()) {
            return '';
        }
        
        return $this->wpdb->get_charset_collate();
    }

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

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

    /**
     * Get affected rows
     *
     * @return int
     */
    public function getAffectedRows()
    {
        return $this->wpdb->rows_affected;
    }

    /**
     * Prepare SQL statement
     *
     * @param string $query
     * @param mixed ...$args
     * @return string
     */
    public function prepare($query, ...$args)
    {
        return $this->wpdb->prepare($query, ...$args);
    }

    /**
     * Check if connection is valid
     *
     * @return bool
     */
    public function isConnected()
    {
        return $this->wpdb instanceof \wpdb;
    }

    /**
     * Get database version
     *
     * @return string
     */
    public function getVersion()
    {
        if ($this->isSqlite()) {
            return $this->wpdb->get_var("SELECT sqlite_version()");
        }
        
        return $this->wpdb->db_version();
    }

    /**
     * Begin transaction
     *
     * @return bool
     */
    public function beginTransaction()
    {
        return $this->wpdb->query('START TRANSACTION') !== false;
    }

    /**
     * Commit transaction
     *
     * @return bool
     */
    public function commit()
    {
        return $this->wpdb->query('COMMIT') !== false;
    }

    /**
     * Rollback transaction
     *
     * @return bool
     */
    public function rollback()
    {
        return $this->wpdb->query('ROLLBACK') !== false;
    }

    /**
     * Execute 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;
        }
    }
}
