<?php
/**
 * SQL Container Class
 * 
 * @package ZenoChatbot\Framework\Database\Migration
 */

namespace ZenoChatbot\Framework\Database\Migration;

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

/**
 * SQL Container Class
 * 
 * Simple container to hold SQL statements returned by migration methods
 */
class Sql
{
    /**
     * The SQL statement
     *
     * @var string
     */
    protected $sql;

    /**
     * Whether this SQL should use dbDelta
     *
     * @var bool
     */
    protected $useDbDelta;

    /**
     * Additional options for the SQL execution
     *
     * @var array
     */
    protected $options;

    /**
     * Create a new SQL instance
     *
     * @param string $sql
     * @param bool $useDbDelta
     * @param array $options
     */
    public function __construct($sql, $useDbDelta = true, array $options = [])
    {
        $this->sql = $sql;
        $this->useDbDelta = $useDbDelta; // Default to true for migrations
        $this->options = $options;
    }

    /**
     * Get the SQL statement
     *
     * @return string
     */
    public function getSql()
    {
        return $this->sql;
    }

    /**
     * Check if this SQL should use dbDelta
     *
     * @return bool
     */
    public function shouldUseDbDelta()
    {
        return $this->useDbDelta;
    }

    /**
     * Get the options
     *
     * @return array
     */
    public function getOptions()
    {
        return $this->options;
    }

    /**
     * Get a specific option
     *
     * @param string $key
     * @param mixed $default
     * @return mixed
     */
    public function getOption($key, $default = null)
    {
        return $this->options[$key] ?? $default;
    }

    /**
     * Set an option
     *
     * @param string $key
     * @param mixed $value
     * @return $this
     */
    public function setOption($key, $value)
    {
        $this->options[$key] = $value;
        return $this;
    }

    /**
     * Convert to string
     *
     * @return string
     */
    public function __toString()
    {
        return $this->sql;
    }

    /**
     * Create a new SQL instance for CREATE TABLE (uses dbDelta)
     *
     * @param string $sql
     * @param array $options
     * @return static
     */
    public static function create($sql, array $options = [])
    {
        return new static($sql, true, $options);
    }

    /**
     * Create a new SQL instance for DROP TABLE (uses dbDelta)
     *
     * @param string $sql
     * @param array $options
     * @return static
     */
    public static function drop($sql, array $options = [])
    {
        return new static($sql, true, $options);
    }

    /**
     * Create a new SQL instance for ALTER TABLE (uses dbDelta)
     *
     * @param string $sql
     * @param array $options
     * @return static
     */
    public static function alter($sql, array $options = [])
    {
        return new static($sql, true, $options);
    }

    /**
     * Create a new SQL instance for raw SQL (uses dbDelta)
     *
     * @param string $sql
     * @param array $options
     * @return static
     */
    public static function raw($sql, array $options = [])
    {
        return new static($sql, true, $options);
    }

    /**
     * Check if the SQL is empty
     *
     * @return bool
     */
    public function isEmpty()
    {
        return empty(trim($this->sql));
    }

    /**
     * Check if the SQL is not empty
     *
     * @return bool
     */
    public function isNotEmpty()
    {
        return !$this->isEmpty();
    }

    /**
     * Get SQL type based on the first word
     *
     * @return string
     */
    public function getType()
    {
        $sql = trim(strtoupper($this->sql));
        
        if (strpos($sql, 'CREATE') === 0) {
            return 'CREATE';
        } elseif (strpos($sql, 'DROP') === 0) {
            return 'DROP';
        } elseif (strpos($sql, 'ALTER') === 0) {
            return 'ALTER';
        } elseif (strpos($sql, 'INSERT') === 0) {
            return 'INSERT';
        } elseif (strpos($sql, 'UPDATE') === 0) {
            return 'UPDATE';
        } elseif (strpos($sql, 'DELETE') === 0) {
            return 'DELETE';
        } elseif (strpos($sql, 'SELECT') === 0) {
            return 'SELECT';
        }
        
        return 'UNKNOWN';
    }

    /**
     * Check if this is a CREATE statement
     *
     * @return bool
     */
    public function isCreate()
    {
        return $this->getType() === 'CREATE';
    }

    /**
     * Check if this is a DROP statement
     *
     * @return bool
     */
    public function isDrop()
    {
        return $this->getType() === 'DROP';
    }

    /**
     * Check if this is an ALTER statement
     *
     * @return bool
     */
    public function isAlter()
    {
        return $this->getType() === 'ALTER';
    }

    /**
     * Add a comment to the SQL
     *
     * @param string $comment
     * @return $this
     */
    public function comment($comment)
    {
        $this->sql = "-- {$comment}\n" . $this->sql;
        return $this;
    }

    /**
     * Get formatted SQL for logging
     *
     * @return string
     */
    public function getFormattedSql()
    {
        $type = $this->getType();
        $dbDelta = $this->useDbDelta ? ' (dbDelta)' : ' (direct)';
        
        return "[{$type}{$dbDelta}] " . $this->sql;
    }
}
