package systemtables

import (
	"context"
	"fmt"

	"github.com/everything-on-mysql/cassandra-on-mysql/pkg/cql/keyspace"
	"github.com/everything-on-mysql/cassandra-on-mysql/pkg/cql/parser"
	"github.com/everything-on-mysql/cassandra-on-mysql/pkg/cql/types"
)

// SystemTableExecutor executes queries against system tables
type SystemTableExecutor struct {
	registry       *Registry
	schemaProvider SchemaProvider
}

// NewSystemTableExecutor creates a new system table executor
func NewSystemTableExecutor(registry *Registry) *SystemTableExecutor {
	return &SystemTableExecutor{
		registry: registry,
	}
}

// SetSchemaProvider installs a schema provider for MySQL-backed system_schema tables.
func (e *SystemTableExecutor) SetSchemaProvider(provider SchemaProvider) {
	if e == nil {
		return
	}
	e.schemaProvider = provider
}

// LookupTable exposes the system table definition for describe-style queries.
func (e *SystemTableExecutor) LookupTable(name string) (*SystemTable, bool) {
	if e == nil || e.registry == nil {
		return nil, false
	}
	return e.registry.Lookup(name)
}

// IsSystemTableQuery checks if a SELECT query targets system tables
func IsSystemTableQuery(stmt *parser.SelectStatement) bool {
	if stmt == nil || stmt.From == nil {
		return false
	}
	if stmt.From.Schema == "" {
		return false
	}
	return keyspace.IsSystem(stmt.From.Schema)
}

// Execute executes a CQL query against system tables
func (e *SystemTableExecutor) Execute(ctx context.Context, stmt *parser.SelectStatement, user, password string) (*types.QueryResult, error) {
	if stmt == nil || stmt.From == nil {
		return nil, fmt.Errorf("invalid select statement")
	}

	tableName := stmt.From.String()

	// Lookup the system table
	table, ok := e.registry.Lookup(tableName)
	if !ok {
		return nil, fmt.Errorf("system table not found: %s", tableName)
	}

	// Build query options from statement
	opts := QueryOptions{
		WhereClause: stmt.Where,
	}

	// Extract LIMIT if present
	if stmt.Options.Limit != nil {
		if lit, ok := stmt.Options.Limit.(*parser.IntegerLiteral); ok {
			limit := lit.Value
			opts.Limit = &limit
		}
	}
	if stmt.Options.Offset != nil {
		if lit, ok := stmt.Options.Offset.(*parser.IntegerLiteral); ok {
			offset := lit.Value
			opts.Offset = &offset
		}
	}

	if e.registry != nil && e.registry.state != nil {
		if err := e.registry.state.EnsureSchemaVersion(ctx, user, password); err != nil {
			return nil, err
		}
	}

	// Get data from provider
	rows, err := table.Provider(ctx, opts)
	if err != nil {
		return nil, fmt.Errorf("failed to get data: %w", err)
	}

	if e.schemaProvider != nil {
		switch tableName {
		case "system_schema.keyspaces":
			extra, err := e.schemaProvider.Keyspaces(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.tables":
			extra, err := e.schemaProvider.Tables(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.columns":
			extra, err := e.schemaProvider.Columns(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.indexes":
			extra, err := e.schemaProvider.Indexes(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.types":
			extra, err := e.schemaProvider.Types(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.views":
			extra, err := e.schemaProvider.Views(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.triggers":
			extra, err := e.schemaProvider.Triggers(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.functions":
			extra, err := e.schemaProvider.Functions(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_schema.aggregates":
			extra, err := e.schemaProvider.Aggregates(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		case "system_auth.roles":
			extra, err := e.schemaProvider.Users(ctx, table.Columns, user, password)
			if err != nil {
				return nil, err
			}
			rows = append(rows, extra...)
		}
	}

	// Apply WHERE filtering if present
	if stmt.Where != nil {
		filter := NewFilterEngine(stmt.Where)
		filtered := make([]types.Row, 0)
		for _, row := range rows {
			match, err := filter.Evaluate(row, table.Columns)
			if err != nil {
				return nil, fmt.Errorf("filter evaluation error: %w", err)
			}
			if match {
				filtered = append(filtered, row)
			}
		}
		rows = filtered
	}

	// Apply OFFSET if present
	if opts.Offset != nil && *opts.Offset > 0 {
		if int64(len(rows)) <= *opts.Offset {
			rows = nil
		} else {
			rows = rows[*opts.Offset:]
		}
	}

	// Apply LIMIT if present
	if opts.Limit != nil && int64(len(rows)) > *opts.Limit {
		rows = rows[:*opts.Limit]
	}

	// Handle column projection (SELECT specific columns)
	if len(stmt.Columns) > 0 {
		// Check if it's SELECT *
		if len(stmt.Columns) == 1 {
			if _, ok := stmt.Columns[0].(*parser.StarExpr); ok {
				// SELECT * - return all columns
				return &types.QueryResult{
					Columns: table.Columns,
					Rows:    rows,
				}, nil
			}
		}

		// Project specific columns
		return e.projectColumns(stmt, rows, table.Columns)
	}

	return &types.QueryResult{
		Columns: table.Columns,
		Rows:    rows,
	}, nil
}

// projectColumns projects only selected columns from rows
func (e *SystemTableExecutor) projectColumns(stmt *parser.SelectStatement, rows []types.Row, allColumns []types.ColumnInfo) (*types.QueryResult, error) {
	// Build column index map
	colIndexMap := make(map[string]int)
	for i, col := range allColumns {
		colIndexMap[col.Name] = i
	}

	// Determine which columns to keep and their new names
	var keepIndices []int
	var resultColumns []types.ColumnInfo

	for _, expr := range stmt.Columns {
		switch v := expr.(type) {
		case *parser.ColumnExpr:
			// Simple column reference
			if idx, ok := colIndexMap[v.Name]; ok {
				keepIndices = append(keepIndices, idx)
				resultColumns = append(resultColumns, allColumns[idx])
			} else {
				return nil, fmt.Errorf("column not found: %s", v.Name)
			}
		case *parser.AliasedExpr:
			if colExpr, ok := v.Expr.(*parser.ColumnExpr); ok {
				if idx, ok := colIndexMap[colExpr.Name]; ok {
					keepIndices = append(keepIndices, idx)
					resultColumns = append(resultColumns, types.ColumnInfo{
						Keyspace: allColumns[idx].Keyspace,
						Table:    allColumns[idx].Table,
						Name:     v.Alias,
						Type:     allColumns[idx].Type,
					})
				} else {
					return nil, fmt.Errorf("column not found: %s", colExpr.Name)
				}
			} else {
				return nil, fmt.Errorf("unsupported aliased expression type: %T", v.Expr)
			}
		case *parser.BinaryExpr:
			// Handle aliased columns like "col AS alias"
			// For now, we'll just try to extract the base column
			if colExpr, ok := v.Left.(*parser.ColumnExpr); ok {
				if idx, ok := colIndexMap[colExpr.Name]; ok {
					keepIndices = append(keepIndices, idx)
					// Use right side as alias if it's a string literal or identifier
					alias := colExpr.Name
					if lit, ok := v.Right.(*parser.StringLiteral); ok {
						alias = lit.Value
					}
					resultColumns = append(resultColumns, types.ColumnInfo{
						Keyspace: allColumns[idx].Keyspace,
						Table:    allColumns[idx].Table,
						Name:     alias,
						Type:     allColumns[idx].Type,
					})
				}
			}
		default:
			// For other expression types, try to find column references
			// This is a simplified approach
			return nil, fmt.Errorf("unsupported column expression type: %T", expr)
		}
	}

	// Project rows
	var projectedRows []types.Row
	for _, row := range rows {
		newValues := make([]interface{}, len(keepIndices))
		for i, idx := range keepIndices {
			if idx < len(row.Values) {
				newValues[i] = row.Values[idx]
			}
		}
		projectedRows = append(projectedRows, types.Row{Values: newValues})
	}

	return &types.QueryResult{
		Columns: resultColumns,
		Rows:    projectedRows,
	}, nil
}

// ExecuteRaw executes a raw system table query without parsing
// Useful for internal queries
func (e *SystemTableExecutor) ExecuteRaw(ctx context.Context, tableName string, opts QueryOptions) (*types.QueryResult, error) {
	table, ok := e.registry.Lookup(tableName)
	if !ok {
		return nil, fmt.Errorf("system table not found: %s", tableName)
	}

	rows, err := table.Provider(ctx, opts)
	if err != nil {
		return nil, fmt.Errorf("failed to get data: %w", err)
	}

	return &types.QueryResult{
		Columns: table.Columns,
		Rows:    rows,
	}, nil
}