package com.cube.sql;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * SQL Parser for Cube database
 * 
 * Supports standard SQL syntax:
 * - SELECT * FROM table WHERE id = 'value'
 * - INSERT INTO table (col1, col2) VALUES ('val1', 'val2')
 * - UPDATE table SET col1='val1' WHERE id='value'
 * - DELETE FROM table WHERE id='value'
 * - CREATE TABLE table (col1 TYPE, col2 TYPE, ...)
 * - DROP TABLE table
 */
public class SQLParser {
    
    public enum SQLType {
        SELECT,
        INSERT,
        UPDATE,
        DELETE,
        CREATE_TABLE,
        DROP_TABLE,
        DESCRIBE,
        SHOW_TABLES,
        CREATE_INDEX, SHOW_INDEXES, DROP_INDEX, UNKNOWN
    }
    
    public static class ParsedSQL {
        private final SQLType type;
        private final String table;
        private final String keyspace;
        private final Map<String, String> columns;
        private final Map<String, String> whereClause;
        private final List<String> selectColumns;
        private final Map<String, String> columnDefinitions;
        private final String primaryKey;
        
        public ParsedSQL(SQLType type, String keyspace, String table) {
            this.type = type;
            this.keyspace = keyspace;
            this.table = table;
            this.columns = new LinkedHashMap<>();
            this.whereClause = new LinkedHashMap<>();
            this.selectColumns = new ArrayList<>();
            this.columnDefinitions = new LinkedHashMap<>();
            this.primaryKey = null;
        }
        
        public ParsedSQL(SQLType type, String keyspace, String table, 
                        Map<String, String> columns, Map<String, String> whereClause,
                        List<String> selectColumns, Map<String, String> columnDefinitions,
                        String primaryKey) {
            this.type = type;
            this.keyspace = keyspace;
            this.table = table;
            this.columns = columns != null ? columns : new LinkedHashMap<>();
            this.whereClause = whereClause != null ? whereClause : new LinkedHashMap<>();
            this.selectColumns = selectColumns != null ? selectColumns : new ArrayList<>();
            this.columnDefinitions = columnDefinitions != null ? columnDefinitions : new LinkedHashMap<>();
            this.primaryKey = primaryKey;
        }
        
        public SQLType getType() { return type; }
        public String getTable() { return table; }
        public String getKeyspace() { return keyspace; }
        public Map<String, String> getColumns() { return columns; }
        public Map<String, String> getWhereClause() { return whereClause; }
        public List<String> getSelectColumns() { return selectColumns; }
        public Map<String, String> getColumnDefinitions() { return columnDefinitions; }
        public String getPrimaryKey() { return primaryKey; }
        
        @Override
        public String toString() {
            return "ParsedSQL{type=" + type + ", table=" + keyspace + "." + table + "}";
        }
    }
    
    /**
     * Parse SQL statement
     */
    public static ParsedSQL parse(String sql) throws IllegalArgumentException {
        if (sql == null || sql.trim().isEmpty()) {
            throw new IllegalArgumentException("SQL statement is empty");
        }
        
        sql = sql.trim();
        if (sql.endsWith(";")) {
            sql = sql.substring(0, sql.length() - 1).trim();
        }
        
        String upperSQL = sql.toUpperCase();
        
        if (upperSQL.startsWith("SELECT")) {
            return parseSelect(sql);
        } else if (upperSQL.startsWith("INSERT")) {
            return parseInsert(sql);
        } else if (upperSQL.startsWith("UPDATE")) {
            return parseUpdate(sql);
        } else if (upperSQL.startsWith("DELETE")) {
            return parseDelete(sql);
        } else if (upperSQL.startsWith("CREATE TABLE")) {
            return parseCreateTable(sql);
        } else if (upperSQL.startsWith("DROP TABLE")) {
            return parseDropTable(sql);
        } else if (upperSQL.startsWith("DESCRIBE") || upperSQL.startsWith("DESC")) {
            return parseDescribe(sql);
        } else if (upperSQL.startsWith("SHOW TABLES")) {
            return parseShowTables(sql);
        } else {
            throw new IllegalArgumentException("Unsupported SQL statement: " + sql);
        }
    }
    
    /**
     * Parse SELECT statement
     * Examples:
     * - SELECT * FROM users.profiles WHERE id = 'user-1'
     * - SELECT name, email FROM users.profiles WHERE id = 'user-1'
     * - SELECT * FROM profiles WHERE id = 'user-1' (default keyspace)
     */
    private static ParsedSQL parseSelect(String sql) {
        // Pattern: SELECT columns FROM [keyspace.]table WHERE conditions
        Pattern pattern = Pattern.compile(
            "SELECT\\s+(.+?)\\s+FROM\\s+([\\w.]+)(?:\\s+WHERE\\s+(.+))?",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid SELECT syntax: " + sql);
        }
        
        String columnsStr = matcher.group(1).trim();
        String tableRef = matcher.group(2).trim();
        String whereStr = matcher.group(3);
        
        // Parse table reference
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        // Parse columns
        List<String> selectColumns = new ArrayList<>();
        if ("*".equals(columnsStr)) {
            selectColumns.add("*");
        } else {
            for (String col : columnsStr.split(",")) {
                selectColumns.add(col.trim());
            }
        }
        
        // Parse WHERE clause
        Map<String, String> whereClause = parseWhereClause(whereStr);
        
        return new ParsedSQL(SQLType.SELECT, keyspace, table, null, whereClause, 
                           selectColumns, null, null);
    }
    
    /**
     * Parse INSERT statement
     * Example: INSERT INTO users.profiles (id, name, email) VALUES ('user-1', 'Alice', 'alice@example.com')
     */
    private static ParsedSQL parseInsert(String sql) {
        // Pattern: INSERT INTO [keyspace.]table (columns) VALUES (values)
        Pattern pattern = Pattern.compile(
            "INSERT\\s+INTO\\s+([\\w.]+)\\s*\\(([^)]+)\\)\\s*VALUES\\s*\\(([^)]+)\\)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid INSERT syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        String columnsStr = matcher.group(2).trim();
        String valuesStr = matcher.group(3).trim();
        
        // Parse table reference
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        // Parse columns and values
        String[] columns = columnsStr.split(",");
        String[] values = parseValues(valuesStr);
        
        if (columns.length != values.length) {
            throw new IllegalArgumentException("Column count doesn't match value count");
        }
        
        Map<String, String> columnValues = new LinkedHashMap<>();
        for (int i = 0; i < columns.length; i++) {
            columnValues.put(columns[i].trim(), values[i]);
        }
        
        return new ParsedSQL(SQLType.INSERT, keyspace, table, columnValues, null, 
                           null, null, null);
    }
    
    /**
     * Parse UPDATE statement
     * Example: UPDATE users.profiles SET name='Alice Johnson', age='31' WHERE id='user-1'
     */
    private static ParsedSQL parseUpdate(String sql) {
        // Pattern: UPDATE [keyspace.]table SET assignments WHERE conditions
        Pattern pattern = Pattern.compile(
            "UPDATE\\s+([\\w.]+)\\s+SET\\s+(.+?)\\s+WHERE\\s+(.+)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid UPDATE syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        String setStr = matcher.group(2).trim();
        String whereStr = matcher.group(3).trim();
        
        // Parse table reference
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        // Parse SET clause
        Map<String, String> columns = parseSetClause(setStr);
        
        // Parse WHERE clause
        Map<String, String> whereClause = parseWhereClause(whereStr);
        
        return new ParsedSQL(SQLType.UPDATE, keyspace, table, columns, whereClause, 
                           null, null, null);
    }
    
    /**
     * Parse DELETE statement
     * Example: DELETE FROM users.profiles WHERE id='user-1'
     */
    private static ParsedSQL parseDelete(String sql) {
        // Pattern: DELETE FROM [keyspace.]table WHERE conditions
        Pattern pattern = Pattern.compile(
            "DELETE\\s+FROM\\s+([\\w.]+)\\s+WHERE\\s+(.+)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid DELETE syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        String whereStr = matcher.group(2).trim();
        
        // Parse table reference
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        // Parse WHERE clause
        Map<String, String> whereClause = parseWhereClause(whereStr);
        
        return new ParsedSQL(SQLType.DELETE, keyspace, table, null, whereClause, 
                           null, null, null);
    }
    
    /**
     * Parse CREATE TABLE statement
     * Example: CREATE TABLE users.profiles (id TEXT PRIMARY KEY, name TEXT, email TEXT)
     */
    private static ParsedSQL parseCreateTable(String sql) {
        // Pattern: CREATE TABLE [keyspace.]table (column_definitions)
        Pattern pattern = Pattern.compile(
            "CREATE\\s+TABLE\\s+([\\w.]+)\\s*\\(([^)]+)\\)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid CREATE TABLE syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        String columnsStr = matcher.group(2).trim();
        
        // Parse table reference
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        // Parse column definitions
        Map<String, String> columnDefs = new LinkedHashMap<>();
        String primaryKey = null;
        
        String[] columns = columnsStr.split(",");
        for (String col : columns) {
            col = col.trim();
            
            // Check for PRIMARY KEY constraint
            if (col.toUpperCase().contains("PRIMARY KEY")) {
                String[] colParts = col.split("\\s+");
                String colName = colParts[0];
                String colType = colParts[1];
                columnDefs.put(colName, colType);
                primaryKey = colName;
            } else {
                String[] colParts = col.split("\\s+", 2);
                if (colParts.length == 2) {
                    columnDefs.put(colParts[0], colParts[1]);
                }
            }
        }
        
        // If no PRIMARY KEY specified, first column is primary key
        if (primaryKey == null && !columnDefs.isEmpty()) {
            primaryKey = columnDefs.keySet().iterator().next();
        }
        
        return new ParsedSQL(SQLType.CREATE_TABLE, keyspace, table, null, null, 
                           null, columnDefs, primaryKey);
    }
    
    /**
     * Parse DROP TABLE statement
     * Example: DROP TABLE users.profiles
     */
    private static ParsedSQL parseDropTable(String sql) {
        Pattern pattern = Pattern.compile(
            "DROP\\s+TABLE\\s+([\\w.]+)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid DROP TABLE syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        return new ParsedSQL(SQLType.DROP_TABLE, keyspace, table, null, null, 
                           null, null, null);
    }
    
    /**
     * Parse DESCRIBE statement
     * Example: DESCRIBE users.profiles
     */
    private static ParsedSQL parseDescribe(String sql) {
        Pattern pattern = Pattern.compile(
            "(?:DESCRIBE|DESC)\\s+([\\w.]+)",
            Pattern.CASE_INSENSITIVE
        );
        
        Matcher matcher = pattern.matcher(sql);
        if (!matcher.find()) {
            throw new IllegalArgumentException("Invalid DESCRIBE syntax: " + sql);
        }
        
        String tableRef = matcher.group(1).trim();
        
        String[] parts = tableRef.split("\\.");
        String keyspace = parts.length > 1 ? parts[0] : "default";
        String table = parts.length > 1 ? parts[1] : parts[0];
        
        return new ParsedSQL(SQLType.DESCRIBE, keyspace, table, null, null, 
                           null, null, null);
    }
    
    /**
     * Parse SHOW TABLES statement
     */
    private static ParsedSQL parseShowTables(String sql) {
        return new ParsedSQL(SQLType.SHOW_TABLES, "default", null, null, null, 
                           null, null, null);
    }
    
    /**
     * Parse WHERE clause
     * Example: id='user-1' AND status='active'
     */
    private static Map<String, String> parseWhereClause(String whereStr) {
        Map<String, String> where = new LinkedHashMap<>();
        
        if (whereStr == null || whereStr.trim().isEmpty()) {
            return where;
        }
        
        // Split by AND (simple implementation)
        String[] conditions = whereStr.split("\\s+AND\\s+", -1);
        
        for (String condition : conditions) {
            condition = condition.trim();
            
            // Parse: column = 'value' or column='value'
            Pattern pattern = Pattern.compile("([\\w]+)\\s*=\\s*['\"]?([^'\"]+)['\"]?");
            Matcher matcher = pattern.matcher(condition);
            
            if (matcher.find()) {
                where.put(matcher.group(1).trim(), matcher.group(2).trim());
            }
        }
        
        return where;
    }
    
    /**
     * Parse SET clause
     * Example: name='Alice', age='31'
     */
    private static Map<String, String> parseSetClause(String setStr) {
        Map<String, String> sets = new LinkedHashMap<>();
        
        String[] assignments = setStr.split(",");
        for (String assignment : assignments) {
            assignment = assignment.trim();
            
            Pattern pattern = Pattern.compile("([\\w]+)\\s*=\\s*['\"]?([^'\"]+)['\"]?");
            Matcher matcher = pattern.matcher(assignment);
            
            if (matcher.find()) {
                sets.put(matcher.group(1).trim(), matcher.group(2).trim());
            }
        }
        
        return sets;
    }
    
    /**
     * Parse VALUES clause, handling quoted strings
     */
    private static String[] parseValues(String valuesStr) {
        List<String> values = new ArrayList<>();
        
        StringBuilder current = new StringBuilder();
        boolean inQuotes = false;
        char quoteChar = '\0';
        
        for (int i = 0; i < valuesStr.length(); i++) {
            char c = valuesStr.charAt(i);
            
            if ((c == '\'' || c == '"') && (i == 0 || valuesStr.charAt(i - 1) != '\\')) {
                if (!inQuotes) {
                    inQuotes = true;
                    quoteChar = c;
                } else if (c == quoteChar) {
                    inQuotes = false;
                    quoteChar = '\0';
                } else {
                    current.append(c);
                }
            } else if (c == ',' && !inQuotes) {
                values.add(current.toString().trim());
                current = new StringBuilder();
            } else if (!Character.isWhitespace(c) || inQuotes) {
                current.append(c);
            }
        }
        
        if (current.length() > 0) {
            values.add(current.toString().trim());
        }
        
        return values.toArray(new String[0]);
    }
}