package infra

import (
	"database/sql"
	"fmt"
	"strconv"
)

// DBQuery executes a SELECT query and returns the results as a slice of maps.
// Each map key is the column name; values are converted to native Go types:
// int64, float64, bool, string, []byte, or nil for NULLs.
func DBQuery(db *sql.DB, query string, args ...any) ([]map[string]any, error) {
	rows, err := db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("db_query: %w", err)
	}
	defer rows.Close()

	cols, err := rows.Columns()
	if err != nil {
		return nil, fmt.Errorf("db_query: columns: %w", err)
	}
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, fmt.Errorf("db_query: column types: %w", err)
	}

	var results []map[string]any
	for rows.Next() {
		// Use RawBytes so we can inspect the raw value before converting.
		raw := make([]sql.RawBytes, len(cols))
		ptrs := make([]any, len(cols))
		for i := range raw {
			ptrs[i] = &raw[i]
		}
		if err := rows.Scan(ptrs...); err != nil {
			return nil, fmt.Errorf("db_query: scan: %w", err)
		}

		row := make(map[string]any, len(cols))
		for i, col := range cols {
			if raw[i] == nil {
				row[col] = nil
				continue
			}
			row[col] = convertRaw(raw[i], colTypes[i].DatabaseTypeName())
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("db_query: rows: %w", err)
	}
	return results, nil
}

// convertRaw attempts to convert a raw SQL byte slice into a native Go type
// based on the database column type name hint.
func convertRaw(b sql.RawBytes, dbType string) any {
	s := string(b)
	switch dbType {
	case "INTEGER", "INT", "BIGINT", "SMALLINT", "TINYINT", "INT2", "INT4", "INT8":
		if v, err := strconv.ParseInt(s, 10, 64); err == nil {
			return v
		}
	case "REAL", "FLOAT", "DOUBLE", "NUMERIC", "DECIMAL":
		if v, err := strconv.ParseFloat(s, 64); err == nil {
			return v
		}
	case "BOOLEAN", "BOOL":
		if v, err := strconv.ParseBool(s); err == nil {
			return v
		}
	case "BLOB":
		cp := make([]byte, len(b))
		copy(cp, b)
		return cp
	}
	return s
}