<?php
/**
 * SQLite Migration Grammar Class
 * 
 * @package ZenoChatbot\Framework\Database\Migration\Grammar
 */

namespace ZenoChatbot\Framework\Database\Migration\Grammar;

use ZenoChatbot\Framework\Database\Migration\Sql;

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

/**
 * SQLite Migration Grammar Class
 */
class SqliteGrammar extends Grammar
{
    /**
     * Compile a create table statement
     *
     * @param string $table
     * @param array $columns
     * @param array $indexes
     * @return Sql
     */
    public function compileCreateTable($table, array $columns, array $indexes = [])
    {
        $tableName = $this->getTableName($table);
        
        // Build column definitions
        $columnDefinitions = [];
        foreach ($columns as $name => $definition) {
            // Convert MySQL types to SQLite equivalents
            $sqliteDefinition = $this->convertMysqlToSqlite($definition);
            $columnDefinitions[] = $this->wrapIdentifier($name) . " {$sqliteDefinition}";
        }
        
        // Build index definitions (inline with table creation)
        $indexDefinitions = [];
        foreach ($indexes as $index) {
            $indexDef = $this->buildIndexDefinition($index);
            if (!empty($indexDef)) {
                $indexDefinitions[] = $indexDef;
            }
        }
        
        // Combine all definitions
        $allDefinitions = array_merge($columnDefinitions, $indexDefinitions);
        
        // Build the CREATE TABLE statement
        $sql = "CREATE TABLE {$tableName} (\n";
        $sql .= "  " . implode(",\n  ", $allDefinitions) . "\n";
        $sql .= ");";
        
        // Create separate index statements for complex indexes
        $separateIndexes = $this->buildSeparateIndexes($table, $indexes);
        if (!empty($separateIndexes)) {
            $sql .= "\n\n" . implode("\n", $separateIndexes);
        }
        
        return Sql::create($sql, [
            'table' => $table,
            'full_table_name' => $tableName
        ]);
    }

    /**
     * Compile a drop table statement
     *
     * @param string $table
     * @return Sql
     */
    public function compileDropTable($table)
    {
        $tableName = $this->getTableName($table);
        $sql = "DROP TABLE IF EXISTS {$tableName};";
        
        return Sql::drop($sql, [
            'table' => $table,
            'full_table_name' => $tableName
        ]);
    }

    /**
     * Compile an alter table statement
     *
     * @param string $table
     * @param array $operations
     * @return Sql
     */
    public function compileAlterTable($table, array $operations)
    {
        $tableName = $this->getTableName($table);
        $statements = [];
        
        foreach ($operations as $operation => $definition) {
            switch ($operation) {
                case 'add_column':
                    foreach ($definition as $column => $columnDef) {
                        $sqliteDefinition = $this->convertMysqlToSqlite($columnDef);
                        $statements[] = "ALTER TABLE {$tableName} ADD COLUMN {$this->wrapIdentifier($column)} {$sqliteDefinition};";
                    }
                    break;
                    
                case 'drop_column':
                    // SQLite doesn't support DROP COLUMN directly
                    $statements[] = "-- SQLite does not support DROP COLUMN. Table recreation required.";
                    break;
                    
                case 'rename_column':
                    // SQLite 3.25.0+ supports RENAME COLUMN
                    foreach ($definition as $oldColumn => $newColumn) {
                        $statements[] = "ALTER TABLE {$tableName} RENAME COLUMN {$this->wrapIdentifier($oldColumn)} TO {$this->wrapIdentifier($newColumn)};";
                    }
                    break;
                    
                case 'add_index':
                    foreach ($definition as $index) {
                        $indexSql = $this->buildSeparateIndex($table, $index);
                        if (!empty($indexSql)) {
                            $statements[] = $indexSql;
                        }
                    }
                    break;
                    
                case 'drop_index':
                    $indexes = is_array($definition) ? $definition : [$definition];
                    foreach ($indexes as $indexName) {
                        $statements[] = "DROP INDEX IF EXISTS {$this->wrapIdentifier($indexName)};";
                    }
                    break;
            }
        }
        
        if (empty($statements)) {
            return Sql::raw('', ['table' => $table]);
        }
        
        $sql = implode("\n", $statements);
        
        return Sql::alter($sql, [
            'table' => $table,
            'full_table_name' => $tableName
        ]);
    }

    /**
     * Compile a rename table statement
     *
     * @param string $from
     * @param string $to
     * @return Sql
     */
    public function compileRenameTable($from, $to)
    {
        $fromTable = $this->getTableName($from);
        $toTable = $this->getTableName($to);
        $sql = "ALTER TABLE {$fromTable} RENAME TO {$toTable};";
        
        return Sql::raw($sql, [
            'from_table' => $from,
            'to_table' => $to,
            'from_full_name' => $fromTable,
            'to_full_name' => $toTable
        ]);
    }

    /**
     * Compile a truncate table statement
     *
     * @param string $table
     * @return Sql
     */
    public function compileTruncateTable($table)
    {
        $tableName = $this->getTableName($table);
        // SQLite doesn't have TRUNCATE, use DELETE
        $sql = "DELETE FROM {$tableName};";
        
        return Sql::raw($sql, [
            'table' => $table,
            'full_table_name' => $tableName
        ]);
    }

    /**
     * Convert MySQL column definition to SQLite
     *
     * @param string $definition
     * @return string
     */
    protected function convertMysqlToSqlite($definition)
    {
        // Convert common MySQL types to SQLite
        $definition = preg_replace('/BIGINT\(\d+\)\s+UNSIGNED/i', 'INTEGER', $definition);
        $definition = preg_replace('/INT\(\d+\)\s+UNSIGNED/i', 'INTEGER', $definition);
        $definition = preg_replace('/BIGINT\(\d+\)/i', 'INTEGER', $definition);
        $definition = preg_replace('/INT\(\d+\)/i', 'INTEGER', $definition);
        $definition = preg_replace('/TINYINT\(\d+\)/i', 'INTEGER', $definition);
        $definition = preg_replace('/SMALLINT\(\d+\)/i', 'INTEGER', $definition);
        $definition = preg_replace('/MEDIUMINT\(\d+\)/i', 'INTEGER', $definition);
        
        $definition = preg_replace('/VARCHAR\(\d+\)/i', 'TEXT', $definition);
        $definition = preg_replace('/CHAR\(\d+\)/i', 'TEXT', $definition);
        $definition = preg_replace('/LONGTEXT/i', 'TEXT', $definition);
        $definition = preg_replace('/MEDIUMTEXT/i', 'TEXT', $definition);
        $definition = preg_replace('/TINYTEXT/i', 'TEXT', $definition);
        
        $definition = preg_replace('/DATETIME/i', 'TEXT', $definition);
        $definition = preg_replace('/TIMESTAMP/i', 'TEXT', $definition);
        $definition = preg_replace('/DATE/i', 'TEXT', $definition);
        $definition = preg_replace('/TIME/i', 'TEXT', $definition);
        
        // Handle AUTO_INCREMENT
        $definition = preg_replace('/AUTO_INCREMENT/i', '', $definition);
        
        // Handle DEFAULT CURRENT_TIMESTAMP
        $definition = preg_replace('/DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP/i', "DEFAULT (datetime('now'))", $definition);
        $definition = preg_replace('/DEFAULT CURRENT_TIMESTAMP/i', "DEFAULT (datetime('now'))", $definition);
        
        return trim($definition);
    }

    /**
     * Build SQLite-specific index definition
     *
     * @param mixed $index
     * @return string
     */
    protected function buildIndexDefinition($index)
    {
        if (is_string($index)) {
            return '';
        }
        
        if (is_array($index)) {
            if (isset($index['type']) && isset($index['columns'])) {
                $type = strtoupper($index['type']);
                
                // Handle PRIMARY KEY inline
                if ($type === 'PRIMARY' || $type === 'PRIMARY KEY') {
                    $columns = is_array($index['columns']) ? implode(', ', array_map([$this, 'wrapIdentifier'], $index['columns'])) : $index['columns'];
                    return "PRIMARY KEY ({$columns})";
                }
                
                // Handle UNIQUE inline
                if ($type === 'UNIQUE' || $type === 'UNIQUE KEY') {
                    $columns = is_array($index['columns']) ? implode(', ', array_map([$this, 'wrapIdentifier'], $index['columns'])) : $index['columns'];
                    return "UNIQUE ({$columns})";
                }
            }
        }
        
        return '';
    }

    /**
     * Build separate index statements
     *
     * @param string $table
     * @param array $indexes
     * @return array
     */
    protected function buildSeparateIndexes($table, array $indexes)
    {
        $statements = [];
        
        foreach ($indexes as $index) {
            $statement = $this->buildSeparateIndex($table, $index);
            if (!empty($statement)) {
                $statements[] = $statement;
            }
        }
        
        return $statements;
    }

    /**
     * Build a separate index statement
     *
     * @param string $table
     * @param mixed $index
     * @return string
     */
    protected function buildSeparateIndex($table, $index)
    {
        $tableName = $this->getTableName($table);
        
        if (is_string($index)) {
            // Parse string index definition
            if (preg_match('/(?:INDEX|KEY)\s+`?(\w+)`?\s+\(([^)]+)\)/i', $index, $matches)) {
                $indexName = $matches[1];
                $columns = $matches[2];
                return "CREATE INDEX {$this->wrapIdentifier($indexName)} ON {$tableName} ({$columns});";
            }
            return '';
        }
        
        if (is_array($index) && isset($index['type']) && isset($index['name']) && isset($index['columns'])) {
            $type = strtoupper($index['type']);
            $indexName = $this->wrapIdentifier($index['name']);
            $columns = is_array($index['columns']) ? implode(', ', array_map([$this, 'wrapIdentifier'], $index['columns'])) : $index['columns'];
            
            // Skip PRIMARY KEY and UNIQUE as they're handled inline
            if ($type === 'PRIMARY' || $type === 'PRIMARY KEY' || $type === 'UNIQUE' || $type === 'UNIQUE KEY') {
                return '';
            }
            
            // Create regular index
            if ($type === 'INDEX' || $type === 'KEY') {
                return "CREATE INDEX {$indexName} ON {$tableName} ({$columns});";
            }
        }
        
        return '';
    }

    /**
     * Wrap identifier for SQLite (uses double quotes)
     *
     * @param string $identifier
     * @return string
     */
    protected function wrapIdentifier($identifier)
    {
        return "\"{$identifier}\"";
    }
}
