package coredb

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"regexp"
	"strconv"
	"strings"
)

var positionalPlaceholderPattern = regexp.MustCompile(`\$(\d+)`)

func (db *tidbAdminDB) QueryRow(ctx context.Context, query string, args ...any) Row {
	if db == nil || db.db == nil {
		return errorRow{err: fmt.Errorf("tidb db is nil")}
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return errorRow{err: err}
	}
	return sqlRow{row: db.db.QueryRowContext(ctx, reboundQuery, reboundArgs...)}
}

func (db *tidbAdminDB) Query(ctx context.Context, query string, args ...any) (Rows, error) {
	if db == nil || db.db == nil {
		return nil, fmt.Errorf("tidb db is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	rows, err := db.db.QueryContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	return &sqlRowsAdapter{rows: rows}, nil
}

func (db *tidbAdminDB) Exec(ctx context.Context, query string, args ...any) (Result, error) {
	if db == nil || db.db == nil {
		return nil, fmt.Errorf("tidb db is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	result, err := db.db.ExecContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return nil, err
	}
	return commandTagFromRowsAffected(rowsAffected), nil
}

func (db *tidbAdminDB) BeginTx(ctx context.Context, options TxOptions) (Tx, error) {
	if db == nil || db.db == nil {
		return nil, fmt.Errorf("tidb db is nil")
	}

	sqlOptions, err := sqlTxOptionsFromContract(options)
	if err != nil {
		return nil, err
	}
	tx, err := db.db.BeginTx(ctx, sqlOptions)
	if err != nil {
		return nil, err
	}
	return &sqlTxAdapter{tx: tx}, nil
}

func (tx *sqlTxAdapter) Database() string {
	return "tidb"
}

type sqlTxAdapter struct {
	tx *sql.Tx
}

type sqlConnAdapter struct {
	conn *sql.Conn
}

func (conn *sqlConnAdapter) Database() string {
	return "tidb"
}

func (conn *sqlConnAdapter) Exec(ctx context.Context, query string, args ...any) (Result, error) {
	if conn == nil || conn.conn == nil {
		return nil, fmt.Errorf("tidb conn is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	result, err := conn.conn.ExecContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return nil, err
	}
	return commandTagFromRowsAffected(rowsAffected), nil
}

func (conn *sqlConnAdapter) Query(ctx context.Context, query string, args ...any) (Rows, error) {
	if conn == nil || conn.conn == nil {
		return nil, fmt.Errorf("tidb conn is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	rows, err := conn.conn.QueryContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	return &sqlRowsAdapter{rows: rows}, nil
}

func (conn *sqlConnAdapter) QueryRow(ctx context.Context, query string, args ...any) Row {
	if conn == nil || conn.conn == nil {
		return errorRow{err: fmt.Errorf("tidb conn is nil")}
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return errorRow{err: err}
	}
	return sqlRow{row: conn.conn.QueryRowContext(ctx, reboundQuery, reboundArgs...)}
}

func (conn *sqlConnAdapter) BeginTx(ctx context.Context, options TxOptions) (Tx, error) {
	if conn == nil || conn.conn == nil {
		return nil, fmt.Errorf("tidb conn is nil")
	}

	sqlOptions, err := sqlTxOptionsFromContract(options)
	if err != nil {
		return nil, err
	}
	tx, err := conn.conn.BeginTx(ctx, sqlOptions)
	if err != nil {
		return nil, err
	}
	return &sqlTxAdapter{tx: tx}, nil
}

func (tx *sqlTxAdapter) Exec(ctx context.Context, query string, args ...any) (Result, error) {
	if tx == nil || tx.tx == nil {
		return nil, fmt.Errorf("tidb tx is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	result, err := tx.tx.ExecContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return nil, err
	}
	return commandTagFromRowsAffected(rowsAffected), nil
}

func (tx *sqlTxAdapter) Query(ctx context.Context, query string, args ...any) (Rows, error) {
	if tx == nil || tx.tx == nil {
		return nil, fmt.Errorf("tidb tx is nil")
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return nil, err
	}
	rows, err := tx.tx.QueryContext(ctx, reboundQuery, reboundArgs...)
	if err != nil {
		return nil, err
	}
	return &sqlRowsAdapter{rows: rows}, nil
}

func (tx *sqlTxAdapter) QueryRow(ctx context.Context, query string, args ...any) Row {
	if tx == nil || tx.tx == nil {
		return errorRow{err: fmt.Errorf("tidb tx is nil")}
	}

	reboundQuery, reboundArgs, err := rebindPositionalQuery(query, args)
	if err != nil {
		return errorRow{err: err}
	}
	return sqlRow{row: tx.tx.QueryRowContext(ctx, reboundQuery, reboundArgs...)}
}

func (tx *sqlTxAdapter) Commit(context.Context) error {
	if tx == nil || tx.tx == nil {
		return fmt.Errorf("tidb tx is nil")
	}
	return tx.tx.Commit()
}

func (tx *sqlTxAdapter) Rollback(context.Context) error {
	if tx == nil || tx.tx == nil {
		return fmt.Errorf("tidb tx is nil")
	}
	return tx.tx.Rollback()
}

type sqlRow struct {
	row *sql.Row
}

func (row sqlRow) Scan(dest ...any) error {
	if row.row == nil {
		return sql.ErrNoRows
	}
	return row.row.Scan(dest...)
}

type errorRow struct {
	err error
}

func (row errorRow) Scan(dest ...any) error {
	if row.err == nil {
		return sql.ErrNoRows
	}
	return row.err
}

type sqlRowsAdapter struct {
	rows     *sql.Rows
	closeErr error
}

func (rows *sqlRowsAdapter) Close() {
	if rows != nil && rows.rows != nil {
		if err := rows.rows.Close(); err != nil && rows.closeErr == nil {
			rows.closeErr = err
		}
	}
}

func (rows *sqlRowsAdapter) Err() error {
	if rows == nil || rows.rows == nil {
		return nil
	}
	return errors.Join(rows.rows.Err(), rows.closeErr)
}

func (rows *sqlRowsAdapter) Next() bool {
	if rows == nil || rows.rows == nil {
		return false
	}
	return rows.rows.Next()
}

func (rows *sqlRowsAdapter) Scan(dest ...any) error {
	if rows == nil || rows.rows == nil {
		return sql.ErrNoRows
	}
	return rows.rows.Scan(dest...)
}

func rebindPositionalQuery(query string, args []any) (string, []any, error) {
	matches := positionalPlaceholderPattern.FindAllStringSubmatchIndex(query, -1)
	if len(matches) == 0 {
		return query, args, nil
	}

	var builder strings.Builder
	reboundArgs := make([]any, 0, len(matches))
	lastIndex := 0
	highestPosition := 0
	for _, match := range matches {
		builder.WriteString(query[lastIndex:match[0]])
		builder.WriteString("?")

		position, err := strconv.Atoi(query[match[2]:match[3]])
		if err != nil {
			return "", nil, fmt.Errorf("invalid query placeholder %q", query[match[0]:match[1]])
		}
		if position <= 0 || position > len(args) {
			return "", nil, fmt.Errorf("query placeholder $%d has no argument", position)
		}
		if position > highestPosition {
			highestPosition = position
		}
		reboundArgs = append(reboundArgs, args[position-1])
		lastIndex = match[1]
	}
	if highestPosition < len(args) {
		return "", nil, fmt.Errorf("query has %d positional arguments but only references $%d", len(args), highestPosition)
	}
	builder.WriteString(query[lastIndex:])
	return builder.String(), reboundArgs, nil
}

func sqlTxOptionsFromContract(options TxOptions) (*sql.TxOptions, error) {
	sqlOptions := &sql.TxOptions{}
	switch options.IsoLevel {
	case "":
	case ReadCommitted:
		sqlOptions.Isolation = sql.LevelReadCommitted
	case RepeatableRead:
		sqlOptions.Isolation = sql.LevelRepeatableRead
	case Serializable:
		sqlOptions.Isolation = sql.LevelSerializable
	case ReadUncommitted:
		sqlOptions.Isolation = sql.LevelReadUncommitted
	default:
		return nil, fmt.Errorf("unsupported transaction isolation level %q", options.IsoLevel)
	}

	switch options.AccessMode {
	case "":
	case ReadWrite:
		sqlOptions.ReadOnly = false
	case ReadOnly:
		sqlOptions.ReadOnly = true
	default:
		return nil, fmt.Errorf("unsupported transaction access mode %q", options.AccessMode)
	}

	return sqlOptions, nil
}

func commandTagFromRowsAffected(rowsAffected int64) Result {
	return rowsAffectedResult{rowsAffected: rowsAffected}
}