<?php
/**
 * MySQL 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;
}

/**
 * MySQL Migration Grammar Class
 */
class MySqlGrammar 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);
        $charsetCollate = $this->getCharsetCollate();
        
        $primaryKeyColumn = null;

        // Build column definitions
        $columnDefinitions = [];
        foreach ($columns as $name => $definition) {
            // Check if PRIMARY KEY is in the definition (case-insensitive)
            if (preg_match('/\bPRIMARY\s+KEY\b/i', $definition)) {
                // Remove PRIMARY KEY from definition
                $definition = preg_replace('/\bPRIMARY\s+KEY\b/i', '', $definition);
                $definition = trim($definition);
                $primaryKeyColumn = $name;
            }
            
            $columnDefinitions[] = $this->wrapIdentifier($name) . " {$definition}";
        }
        
        // Build index definitions
        $indexDefinitions = [];

        // Add extracted primary key if found
        if ($primaryKeyColumn) {
             // Check if PRIMARY KEY is already in indexes to avoid duplication
             $hasPrimaryKey = false;
             foreach ($indexes as $index) {
                 if (is_string($index) && stripos($index, 'PRIMARY KEY') !== false) {
                     $hasPrimaryKey = true;
                     break;
                 }
                 if (is_array($index) && isset($index['type']) && strtoupper($index['type']) === 'PRIMARY KEY') {
                     $hasPrimaryKey = true;
                     break;
                 }
             }
             
             if (!$hasPrimaryKey) {
                 // dbDelta requires two spaces between PRIMARY KEY and (column)
                 $indexDefinitions[] = "PRIMARY KEY  (" . $this->wrapIdentifier($primaryKeyColumn) . ")";
             }
        }

        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 .= ") {$charsetCollate};";
        
        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);
        $alterStatements = [];
        
        foreach ($operations as $operation => $definition) {
            switch ($operation) {
                case 'add_column':
                    foreach ($definition as $column => $columnDef) {
                        $alterStatements[] = "ADD COLUMN {$this->wrapIdentifier($column)} {$columnDef}";
                    }
                    break;
                    
                case 'drop_column':
                    $columns = is_array($definition) ? $definition : [$definition];
                    foreach ($columns as $column) {
                        $alterStatements[] = "DROP COLUMN {$this->wrapIdentifier($column)}";
                    }
                    break;
                    
                case 'modify_column':
                    foreach ($definition as $column => $columnDef) {
                        $alterStatements[] = "MODIFY COLUMN {$this->wrapIdentifier($column)} {$columnDef}";
                    }
                    break;
                    
                case 'add_index':
                    foreach ($definition as $index) {
                        $indexDef = $this->buildIndexDefinition($index);
                        if (!empty($indexDef)) {
                            $alterStatements[] = "ADD {$indexDef}";
                        }
                    }
                    break;
                    
                case 'drop_index':
                    $indexes = is_array($definition) ? $definition : [$definition];
                    foreach ($indexes as $indexName) {
                        $alterStatements[] = "DROP INDEX {$this->wrapIdentifier($indexName)}";
                    }
                    break;
            }
        }
        
        if (empty($alterStatements)) {
            return Sql::raw('', ['table' => $table]);
        }
        
        $sql = "ALTER TABLE {$tableName}\n  " . implode(",\n  ", $alterStatements) . ";";
        
        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 = "RENAME TABLE {$fromTable} 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);
        $sql = "TRUNCATE TABLE {$tableName};";
        
        return Sql::raw($sql, [
            'table' => $table,
            'full_table_name' => $tableName
        ]);
    }

    /**
     * Build MySQL-specific index definition
     *
     * @param mixed $index
     * @return string
     */
    protected function buildIndexDefinition($index)
    {
        if (is_string($index)) {
            return $index;
        }
        
        if (is_array($index)) {
            if (isset($index['type']) && isset($index['name']) && isset($index['columns'])) {
                $columns = is_array($index['columns']) ? implode(', ', array_map([$this, 'wrapIdentifier'], $index['columns'])) : $index['columns'];
                $indexName = $this->wrapIdentifier($index['name']);
                
                // Handle different index types
                switch (strtoupper($index['type'])) {
                    case 'PRIMARY':
                    case 'PRIMARY KEY':
                        return "PRIMARY KEY ({$columns})";
                    case 'UNIQUE':
                    case 'UNIQUE KEY':
                        return "UNIQUE KEY {$indexName} ({$columns})";
                    case 'INDEX':
                    case 'KEY':
                        return "KEY {$indexName} ({$columns})";
                    case 'FULLTEXT':
                        return "FULLTEXT KEY {$indexName} ({$columns})";
                    case 'SPATIAL':
                        return "SPATIAL KEY {$indexName} ({$columns})";
                    default:
                        return "{$index['type']} {$indexName} ({$columns})";
                }
            }
        }
        
        return '';
    }
}
