package stardb

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"strings"
)

// Orm maps query results to a struct or slice of structs
// Usage:
//
//	var user User
//	rows.Orm(&user)  // single row
//	var users []User
//	rows.Orm(&users) // multiple rows
func (r *StarRows) Orm(target interface{}) error {
	if !r.parsed {
		if err := r.parse(); err != nil {
			return err
		}
	}

	targetType := reflect.TypeOf(target)
	targetValue := reflect.ValueOf(target)

	if targetType.Kind() != reflect.Ptr {
		return errors.New("target must be a pointer")
	}

	targetType = targetType.Elem()
	targetValue = targetValue.Elem()

	// Handle slice/array
	if targetValue.Kind() == reflect.Slice || targetValue.Kind() == reflect.Array {
		elementType := targetType.Elem()
		result := reflect.New(targetType).Elem()

		if r.Length() == 0 {
			targetValue.Set(result)
			return nil
		}

		for i := 0; i < r.Length(); i++ {
			element := reflect.New(elementType)
			if err := r.setStructFieldsFromRow(element.Interface(), "db", i); err != nil {
				return err
			}
			result = reflect.Append(result, element.Elem())
		}

		targetValue.Set(result)
		return nil
	}

	// Handle single struct
	if r.Length() == 0 {
		return nil
	}

	return r.setStructFieldsFromRow(target, "db", 0)
}

// QueryX executes a query with named parameter binding
// Usage: QueryX(&user, "SELECT * FROM users WHERE id = ?", ":id")
func (s *StarDB) QueryX(target interface{}, query string, args ...interface{}) (*StarRows, error) {
	return s.queryX(nil, target, query, args...)
}

// QueryXContext executes a query with context and named parameter binding
func (s *StarDB) QueryXContext(ctx context.Context, target interface{}, query string, args ...interface{}) (*StarRows, error) {
	return s.queryX(ctx, target, query, args...)
}

// queryX is the internal implementation
func (s *StarDB) queryX(ctx context.Context, target interface{}, query string, args ...interface{}) (*StarRows, error) {
	fieldValues, err := getStructFieldValues(target, "db")
	if err != nil {
		return nil, err
	}

	// Replace named parameters with actual values
	processedArgs := make([]interface{}, len(args))
	for i, arg := range args {
		if str, ok := arg.(string); ok {
			if strings.HasPrefix(str, ":") {
				fieldName := str[1:]
				if val, exists := fieldValues[fieldName]; exists {
					processedArgs[i] = val
				} else {
					processedArgs[i] = ""
				}
			} else if strings.HasPrefix(str, `\:`) {
				processedArgs[i] = str[1:]
			} else {
				processedArgs[i] = arg
			}
		} else {
			processedArgs[i] = arg
		}
	}

	return s.query(ctx, query, processedArgs...)
}

// QueryXS executes queries for multiple structs
func (s *StarDB) QueryXS(targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	return s.queryXS(nil, targets, query, args...)
}

// QueryXSContext executes queries with context for multiple structs
func (s *StarDB) QueryXSContext(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	return s.queryXS(ctx, targets, query, args...)
}

// queryXS is the internal implementation
func (s *StarDB) queryXS(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	var results []*StarRows

	targetType := reflect.TypeOf(targets)
	targetValue := reflect.ValueOf(targets)

	if targetType.Kind() == reflect.Ptr {
		targetType = targetType.Elem()
		targetValue = targetValue.Elem()
	}

	if targetValue.Kind() == reflect.Slice || targetValue.Kind() == reflect.Array {
		for i := 0; i < targetValue.Len(); i++ {
			result, err := s.queryX(ctx, targetValue.Index(i).Interface(), query, args...)
			if err != nil {
				return results, err
			}
			results = append(results, result)
		}
	} else {
		result, err := s.queryX(ctx, targets, query, args...)
		if err != nil {
			return results, err
		}
		results = append(results, result)
	}

	return results, nil
}

// ExecX executes a statement with named parameter binding
func (s *StarDB) ExecX(target interface{}, query string, args ...interface{}) (sql.Result, error) {
	return s.execX(nil, target, query, args...)
}

// ExecXContext executes a statement with context and named parameter binding
func (s *StarDB) ExecXContext(ctx context.Context, target interface{}, query string, args ...interface{}) (sql.Result, error) {
	return s.execX(ctx, target, query, args...)
}

// execX is the internal implementation
func (s *StarDB) execX(ctx context.Context, target interface{}, query string, args ...interface{}) (sql.Result, error) {
	fieldValues, err := getStructFieldValues(target, "db")
	if err != nil {
		return nil, err
	}

	// Replace named parameters with actual values
	processedArgs := make([]interface{}, len(args))
	for i, arg := range args {
		if str, ok := arg.(string); ok {
			if strings.HasPrefix(str, ":") {
				fieldName := str[1:]
				if val, exists := fieldValues[fieldName]; exists {
					processedArgs[i] = val
				} else {
					processedArgs[i] = ""
				}
			} else if strings.HasPrefix(str, `\:`) {
				processedArgs[i] = str[1:]
			} else {
				processedArgs[i] = arg
			}
		} else {
			processedArgs[i] = arg
		}
	}

	return s.exec(ctx, query, processedArgs...)
}

// ExecXS executes statements for multiple structs
func (s *StarDB) ExecXS(targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	return s.execXS(nil, targets, query, args...)
}

// ExecXSContext executes statements with context for multiple structs
func (s *StarDB) ExecXSContext(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	return s.execXS(ctx, targets, query, args...)
}

// execXS is the internal implementation
func (s *StarDB) execXS(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	var results []sql.Result

	targetType := reflect.TypeOf(targets)
	targetValue := reflect.ValueOf(targets)

	if targetType.Kind() == reflect.Ptr {
		targetType = targetType.Elem()
		targetValue = targetValue.Elem()
	}

	if targetValue.Kind() == reflect.Slice || targetValue.Kind() == reflect.Array {
		for i := 0; i < targetValue.Len(); i++ {
			result, err := s.execX(ctx, targetValue.Index(i).Interface(), query, args...)
			if err != nil {
				return results, err
			}
			results = append(results, result)
		}
	} else {
		result, err := s.execX(ctx, targets, query, args...)
		if err != nil {
			return results, err
		}
		results = append(results, result)
	}

	return results, nil
}

// Insert inserts a struct into the database
// Usage: Insert(&user, "users", "id") // id is auto-increment
func (s *StarDB) Insert(target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	return s.insert(nil, target, tableName, autoIncrementFields...)
}

// InsertContext inserts a struct with context
func (s *StarDB) InsertContext(ctx context.Context, target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	return s.insert(ctx, target, tableName, autoIncrementFields...)
}

// insert is the internal implementation
func (s *StarDB) insert(ctx context.Context, target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	query, params, err := buildInsertSQL(target, tableName, autoIncrementFields...)
	if err != nil {
		return nil, err
	}

	args := []interface{}{}
	for _, param := range params {
		args = append(args, param)
	}

	return s.execX(ctx, target, query, args...)
}

// Update updates a struct in the database
// Usage: Update(&user, "users", "id") // id is primary key
func (s *StarDB) Update(target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	return s.update(nil, target, tableName, primaryKeys...)
}

// UpdateContext updates a struct with context
func (s *StarDB) UpdateContext(ctx context.Context, target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	return s.update(ctx, target, tableName, primaryKeys...)
}

// update is the internal implementation
func (s *StarDB) update(ctx context.Context, target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	query, params, err := buildUpdateSQL(target, tableName, primaryKeys...)
	if err != nil {
		return nil, err
	}

	args := []interface{}{}
	for _, param := range params {
		args = append(args, param)
	}

	return s.execX(ctx, target, query, args...)
}

// buildInsertSQL builds an INSERT SQL statement
func buildInsertSQL(target interface{}, tableName string, autoIncrementFields ...string) (string, []string, error) {
	fieldNames, err := getStructFieldNames(target, "db")
	if err != nil {
		return "", []string{}, err
	}

	var columns []string
	var placeholders []string
	var params []string

	for _, fieldName := range fieldNames {
		// Skip auto-increment fields
		isAutoIncrement := false
		for _, autoField := range autoIncrementFields {
			if fieldName == autoField {
				isAutoIncrement = true
				break
			}
		}
		if isAutoIncrement {
			continue
		}

		columns = append(columns, fieldName)
		placeholders = append(placeholders, "?")
		params = append(params, ":"+fieldName)
	}

	query := fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s)",
		tableName,
		strings.Join(columns, ", "),
		strings.Join(placeholders, ", "))

	return query, params, nil
}

// buildUpdateSQL builds an UPDATE SQL statement
func buildUpdateSQL(target interface{}, tableName string, primaryKeys ...string) (string, []string, error) {
	fieldNames, err := getStructFieldNames(target, "db")
	if err != nil {
		return "", []string{}, err
	}

	var setClauses []string
	var params []string

	// Build SET clause
	for _, fieldName := range fieldNames {
		setClauses = append(setClauses, fmt.Sprintf("%s = ?", fieldName))
		params = append(params, ":"+fieldName)
	}

	// Build WHERE clause
	var whereClauses []string
	for _, pk := range primaryKeys {
		whereClauses = append(whereClauses, fmt.Sprintf("%s = ?", pk))
		params = append(params, ":"+pk)
	}

	query := fmt.Sprintf("UPDATE %s SET %s WHERE %s",
		tableName,
		strings.Join(setClauses, ", "),
		strings.Join(whereClauses, " AND "))

	return query, params, nil
}

// Transaction ORM methods

// QueryX executes a query with named parameter binding in a transaction
func (t *StarTx) QueryX(target interface{}, query string, args ...interface{}) (*StarRows, error) {
	return t.queryX(nil, target, query, args...)
}

// QueryXContext executes a query with context in a transaction
func (t *StarTx) QueryXContext(ctx context.Context, target interface{}, query string, args ...interface{}) (*StarRows, error) {
	return t.queryX(ctx, target, query, args...)
}

// queryX is the internal implementation
func (t *StarTx) queryX(ctx context.Context, target interface{}, query string, args ...interface{}) (*StarRows, error) {
	fieldValues, err := getStructFieldValues(target, "db")
	if err != nil {
		return nil, err
	}

	processedArgs := make([]interface{}, len(args))
	for i, arg := range args {
		if str, ok := arg.(string); ok {
			if strings.HasPrefix(str, ":") {
				fieldName := str[1:]
				if val, exists := fieldValues[fieldName]; exists {
					processedArgs[i] = val
				} else {
					processedArgs[i] = ""
				}
			} else if strings.HasPrefix(str, `\:`) {
				processedArgs[i] = str[1:]
			} else {
				processedArgs[i] = arg
			}
		} else {
			processedArgs[i] = arg
		}
	}

	return t.query(ctx, query, processedArgs...)
}

// QueryXS executes queries for multiple structs in a transaction
func (t *StarTx) QueryXS(targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	return t.queryXS(nil, targets, query, args...)
}

// QueryXSContext executes queries with context for multiple structs in a transaction
func (t *StarTx) QueryXSContext(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	return t.queryXS(ctx, targets, query, args...)
}

// queryXS is the internal implementation
func (t *StarTx) queryXS(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]*StarRows, error) {
	var results []*StarRows

	targetType := reflect.TypeOf(targets)
	targetValue := reflect.ValueOf(targets)

	if targetType.Kind() == reflect.Ptr {
		targetType = targetType.Elem()
		targetValue = targetValue.Elem()
	}

	if targetValue.Kind() == reflect.Slice || targetValue.Kind() == reflect.Array {
		for i := 0; i < targetValue.Len(); i++ {
			result, err := t.queryX(ctx, targetValue.Index(i).Interface(), query, args...)
			if err != nil {
				return results, err
			}
			results = append(results, result)
		}
	} else {
		result, err := t.queryX(ctx, targets, query, args...)
		if err != nil {
			return results, err
		}
		results = append(results, result)
	}

	return results, nil
}

// ExecX executes a statement with named parameter binding in a transaction
func (t *StarTx) ExecX(target interface{}, query string, args ...interface{}) (sql.Result, error) {
	return t.execX(nil, target, query, args...)
}

// ExecXContext executes a statement with context in a transaction
func (t *StarTx) ExecXContext(ctx context.Context, target interface{}, query string, args ...interface{}) (sql.Result, error) {
	return t.execX(ctx, target, query, args...)
}

// execX is the internal implementation
func (t *StarTx) execX(ctx context.Context, target interface{}, query string, args ...interface{}) (sql.Result, error) {
	fieldValues, err := getStructFieldValues(target, "db")
	if err != nil {
		return nil, err
	}

	processedArgs := make([]interface{}, len(args))
	for i, arg := range args {
		if str, ok := arg.(string); ok {
			if strings.HasPrefix(str, ":") {
				fieldName := str[1:]
				if val, exists := fieldValues[fieldName]; exists {
					processedArgs[i] = val
				} else {
					processedArgs[i] = ""
				}
			} else if strings.HasPrefix(str, `\:`) {
				processedArgs[i] = str[1:]
			} else {
				processedArgs[i] = arg
			}
		} else {
			processedArgs[i] = arg
		}
	}

	return t.exec(ctx, query, processedArgs...)
}

// ExecXS executes statements for multiple structs in a transaction
func (t *StarTx) ExecXS(targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	return t.execXS(nil, targets, query, args...)
}

// ExecXSContext executes statements with context for multiple structs in a transaction
func (t *StarTx) ExecXSContext(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	return t.execXS(ctx, targets, query, args...)
}

// execXS is the internal implementation
func (t *StarTx) execXS(ctx context.Context, targets interface{}, query string, args ...interface{}) ([]sql.Result, error) {
	var results []sql.Result

	targetType := reflect.TypeOf(targets)
	targetValue := reflect.ValueOf(targets)

	if targetType.Kind() == reflect.Ptr {
		targetType = targetType.Elem()
		targetValue = targetValue.Elem()
	}

	if targetValue.Kind() == reflect.Slice || targetValue.Kind() == reflect.Array {
		for i := 0; i < targetValue.Len(); i++ {
			result, err := t.execX(ctx, targetValue.Index(i).Interface(), query, args...)
			if err != nil {
				return results, err
			}
			results = append(results, result)
		}
	} else {
		result, err := t.execX(ctx, targets, query, args...)
		if err != nil {
			return results, err
		}
		results = append(results, result)
	}

	return results, nil
}

// Insert inserts a struct in a transaction
func (t *StarTx) Insert(target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	return t.insert(nil, target, tableName, autoIncrementFields...)
}

// InsertContext inserts a struct with context in a transaction
func (t *StarTx) InsertContext(ctx context.Context, target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	return t.insert(ctx, target, tableName, autoIncrementFields...)
}

// insert is the internal implementation
func (t *StarTx) insert(ctx context.Context, target interface{}, tableName string, autoIncrementFields ...string) (sql.Result, error) {
	query, params, err := buildInsertSQL(target, tableName, autoIncrementFields...)
	if err != nil {
		return nil, err
	}

	args := []interface{}{}
	for _, param := range params {
		args = append(args, param)
	}

	return t.execX(ctx, target, query, args...)
}

// Update updates a struct in a transaction
func (t *StarTx) Update(target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	return t.update(nil, target, tableName, primaryKeys...)
}

// UpdateContext updates a struct with context in a transaction
func (t *StarTx) UpdateContext(ctx context.Context, target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	return t.update(ctx, target, tableName, primaryKeys...)
}

// update is the internal implementation
func (t *StarTx) update(ctx context.Context, target interface{}, tableName string, primaryKeys ...string) (sql.Result, error) {
	query, params, err := buildUpdateSQL(target, tableName, primaryKeys...)
	if err != nil {
		return nil, err
	}

	args := []interface{}{}
	for _, param := range params {
		args = append(args, param)
	}

	return t.execX(ctx, target, query, args...)
}