<?php

namespace Licenzo\Framework\Database;

class WPDBConnection extends Connection
{
    public function getQueryGrammar()
    {
        global $wpdb;
        $dbType = $this->detectDatabaseType($wpdb);

        $grammar = null;
        switch ($dbType) {
            case 'mariadb':
                $grammar = new Query\Grammars\MariaDbGrammar();
                break;
            case 'sqlite':
                $grammar = new Query\Grammars\SQLiteGrammar();
                break;
            default:
                $grammar = new Query\Grammars\MySqlGrammar();
                break;
        }

        // Set the WordPress table prefix on the grammar
        $grammar->setTablePrefix($wpdb->prefix);
        $grammar->setConnection($this);

        return $grammar;

    }



    public function getPostProcessor()
    {
        global $wpdb;

        // Detect database type from WordPress
        $dbType = $this->detectDatabaseType($wpdb);

        switch ($dbType) {
            case 'mariadb':
                return new Query\Processors\MariaDbProcessor();
            case 'sqlite':
                return new Query\Processors\SQLiteProcessor();
            default:
                return new Query\Processors\MySqlProcessor();
        }
    }

    /**
     * Detect the database type from WordPress $wpdb instance
     *
     * @param \wpdb $wpdb
     * @return string
     */
    protected function detectDatabaseType($wpdb)
    {
        // Check if we can get the database version
        if (method_exists($wpdb, 'db_version')) {
            $version = $wpdb->db_version();

            // Check for MariaDB
            if (stripos($version, 'mariadb') !== false) {
                return 'mariadb';
            }

            // Check for MySQL
            if (stripos($version, 'mysql') !== false || is_numeric(substr($version, 0, 1))) {
                return 'mysql';
            }
        }

        // Alternative method: Check the database server info
        if (isset($wpdb->dbh) && $wpdb->dbh instanceof \mysqli) {
            $serverInfo = mysqli_get_server_info($wpdb->dbh);

            if (stripos($serverInfo, 'mariadb') !== false) {
                return 'mariadb';
            }

            if (stripos($serverInfo, 'mysql') !== false) {
                return 'mysql';
            }
        }

        // Check for SQLite (less common in WordPress but possible)
        if (defined('DB_ENGINE') && strtolower(DB_ENGINE) === 'sqlite') {
            return 'sqlite';
        }

        // Default to MySQL since it's the most common for WordPress
        return 'mysql';
    }

    public function getName()
    {
        global $wpdb;

        // WordPress typically uses the database name from wp-config.php
        return defined('DB_NAME') ? DB_NAME : $wpdb->dbname;
    }

    public function getDatabaseName()
    {
        return $this->getName();
    }
    public function select($query, $bindings = [], $useReadPdo = true)
    {
        global $wpdb;

        if (!empty($bindings)) {
            // WordPress prepare expects %s, %d format, not ? placeholders
            $query = str_replace('?', '%s', $query);
            $query = $wpdb->prepare($query, ...$bindings);
        }

        $results = $wpdb->get_results($query, ARRAY_A);

        if ($wpdb->last_error) {
            throw new \Exception('Database error: ' . $wpdb->last_error);
        }

        return $results ?: [];
    }

    public function statement($query, $bindings = [])
    {
        global $wpdb;

        if (!empty($bindings)) {
            $query = str_replace('?', '%s', $query);
            $query = $wpdb->prepare($query, ...$bindings);
        }

        $result = $wpdb->query($query);

        if ($wpdb->last_error) {
            throw new \Exception('Database error: ' . $wpdb->last_error);
        }

        return $result;
    }

    /**
     * Update records using raw SQL (Laravel-style)
     *
     * @param string $sql
     * @param array $bindings
     * @return int
     */
    public function update($sql,  $bindings = [], $where = [])
    {
        return $this->affectingStatement($sql, $bindings);
    }

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

    public function affectingStatement($query, $bindings = [])
    {
        global $wpdb;

        if (!empty($bindings)) {
            $query = str_replace('?', '%s', $query);
            $query = $wpdb->prepare($query, ...$bindings);
        }

        $result = $wpdb->query($query);

        if ($wpdb->last_error) {
            throw new \Exception('Database error: ' . $wpdb->last_error);
        }

        return $result;
    }

    public function getPdo()
    {
        return $this;
    }

    /**
     * Run an insert statement against the database.
     *
     * @param string $query
     * @param array $bindings
     * @return bool
     */
    public function insert($query, $bindings = [])
    {
        return $this->statement($query, $bindings);
    }

    /**
     * Delete records using raw SQL
     *
     * @param string $sql
     * @param array $bindings
     * @return int
     * @throws \Exception
     */
    public function delete($sql, array $bindings = [])
    {
        return $this->affectingStatement($sql, $bindings);
    }

    /**
     * Get the database server version
     *
     * @return string
     */
    public function getServerVersion()
    {
        global $wpdb;

        // Try WordPress's db_version method first
        if (method_exists($wpdb, 'db_version')) {
            return $wpdb->db_version();
        }

        // Fallback: query directly via mysqli
        if (isset($wpdb->dbh) && $wpdb->dbh instanceof \mysqli) {
            return mysqli_get_server_info($wpdb->dbh);
        }

        // Last resort: query the database
        try {
            $version = $wpdb->get_var('SELECT VERSION()');
            if ($version) {
                return $version;
            }
        } catch (\Exception $e) {
            // Ignore and fall through to default
        }

        // Default to a safe modern version (MySQL 8.0)
        return '8.0.0';
    }

    /**
     * Determine if the connection is to a MariaDB database
     *
     * @return bool
     */
    public function isMaria()
    {
        $version = $this->getServerVersion();
        return stripos($version, 'mariadb') !== false;
    }

}
