package coredb

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/go-sql-driver/mysql"

	"github.com/tidbcloud/AgentTeam/internal/migrations"
	"github.com/tidbcloud/AgentTeam/internal/tidb"
)

type AdminDB interface {
	Ping(context.Context) error
	Close()
}

type MigrationState struct {
	CurrentVersion int64
	TargetVersion  int64
	Pending        bool
}

type Admin interface {
	Database() string
	Open(context.Context, string, time.Duration) (AdminDB, error)
	ApplyMigrations(context.Context, AdminDB) error
	InspectMigrationState(context.Context, AdminDB) (MigrationState, error)
	VerifySchema(context.Context, AdminDB) error
	RequireSchemaCurrent(context.Context, AdminDB) error
	RunStartupPreflight(context.Context, AdminDB) error
	ClassifyRetryable(error) bool
}

type SQLOpener func(driverName string, dataSourceName string) (*sql.DB, error)

type AdminOption func(*tidbAdmin)

// NewAdmin returns the TiDB admin implementation for core storage.
func NewAdmin() Admin {
	return NewAdminWithOptions()
}

func NewAdminWithOptions(options ...AdminOption) Admin {
	admin := tidbAdmin{}
	for _, option := range options {
		if option != nil {
			option(&admin)
		}
	}
	return admin
}

func WithSQLOpener(opener SQLOpener) AdminOption {
	return func(admin *tidbAdmin) {
		if opener != nil {
			admin.openSQL = opener
		}
	}
}

func IsNotFound(err error) bool {
	return errors.Is(err, sql.ErrNoRows)
}

func IsUniqueViolation(err error) bool {
	var mysqlError *mysql.MySQLError
	return errors.As(err, &mysqlError) && mysqlError.Number == 1062
}

func IsForeignKeyViolation(err error) bool {
	var mysqlError *mysql.MySQLError
	if !errors.As(err, &mysqlError) {
		return false
	}
	return mysqlError.Number == 1451 || mysqlError.Number == 1452
}

func IsRetryableTransactionError(err error) bool {
	var mysqlError *mysql.MySQLError
	if !errors.As(err, &mysqlError) {
		return false
	}
	switch mysqlError.Number {
	case 1205, 1213, 9007:
		return true
	default:
		return false
	}
}

func UniqueViolationConstraint(err error) (string, bool) {
	var mysqlError *mysql.MySQLError
	if !errors.As(err, &mysqlError) || mysqlError.Number != 1062 {
		return "", false
	}

	message := strings.TrimSpace(mysqlError.Message)
	const marker = " for key "
	index := strings.LastIndex(message, marker)
	if index == -1 {
		return "", false
	}
	constraint := strings.Trim(strings.TrimSpace(message[index+len(marker):]), "`'")
	if constraint == "" {
		return "", false
	}
	if qualifier := strings.LastIndex(constraint, "."); qualifier >= 0 {
		constraint = constraint[qualifier+1:]
	}
	constraint = strings.Trim(strings.TrimSpace(constraint), "`'")
	if constraint == "" {
		return "", false
	}
	return constraint, true
}

type tidbAdminDB struct {
	db *sql.DB
}

func WrapSQLDB(db *sql.DB) DB {
	if db == nil {
		return nil
	}
	return newSQLXDB(db)
}

func RawSQLDB(db DB) *sql.DB {
	switch typed := db.(type) {
	case *sqlxDB:
		return typed.sqlDB()
	case *tidbAdminDB:
		if typed == nil {
			return nil
		}
		return typed.db
	default:
		return nil
	}
}

func (db *tidbAdminDB) Database() string {
	return "tidb"
}

func (db *tidbAdminDB) Ping(ctx context.Context) error {
	if db == nil || db.db == nil {
		return fmt.Errorf("tidb db is nil")
	}
	return db.db.PingContext(ctx)
}

func (db *tidbAdminDB) Close() {
	if db != nil && db.db != nil {
		_ = db.db.Close()
	}
}

func requireTiDB(db AdminDB) (*sql.DB, error) {
	switch typed := db.(type) {
	case *sqlxDB:
		if sqlDB := typed.sqlDB(); sqlDB != nil {
			return sqlDB, nil
		}
	case *tidbAdminDB:
		if typed != nil && typed.db != nil {
			return typed.db, nil
		}
	}
	return nil, fmt.Errorf("tidb admin requires a tidb sql db")
}

type tidbAdmin struct {
	openSQL SQLOpener
}

func (tidbAdmin) Database() string {
	return "tidb"
}

func (admin tidbAdmin) Open(ctx context.Context, dsn string, connectTimeout time.Duration) (AdminDB, error) {
	options := []tidb.AdminOption{}
	if admin.openSQL != nil {
		options = append(options, tidb.WithSQLOpener(func(driverName string, dataSourceName string) (*sql.DB, error) {
			return admin.openSQL(driverName, dataSourceName)
		}))
	}
	db, err := tidb.NewAdmin(options...).Open(ctx, dsn, connectTimeout)
	if err != nil {
		return nil, err
	}
	return newSQLXDB(db), nil
}

func (tidbAdmin) ApplyMigrations(ctx context.Context, db AdminDB) error {
	sqlDB, err := requireTiDB(db)
	if err != nil {
		return err
	}
	return tidb.NewAdmin().ApplyMigrations(ctx, sqlDB)
}

func (tidbAdmin) ApplyMigrationsWithReport(ctx context.Context, db AdminDB) (migrations.ApplyReport, error) {
	sqlDB, err := requireTiDB(db)
	if err != nil {
		return migrations.ApplyReport{}, err
	}
	return tidb.ApplyMigrationsWithReport(ctx, sqlDB)
}

func (tidbAdmin) InspectMigrationState(ctx context.Context, db AdminDB) (MigrationState, error) {
	sqlDB, err := requireTiDB(db)
	if err != nil {
		return MigrationState{}, err
	}

	state, err := tidb.NewAdmin().InspectMigrationState(ctx, sqlDB)
	if err != nil {
		return MigrationState{}, err
	}
	return MigrationState{
		CurrentVersion: state.CurrentVersion,
		TargetVersion:  state.TargetVersion,
		Pending:        state.Pending,
	}, nil
}

func (tidbAdmin) VerifySchema(ctx context.Context, db AdminDB) error {
	sqlDB, err := requireTiDB(db)
	if err != nil {
		return err
	}
	return tidb.NewAdmin().VerifySchema(ctx, sqlDB)
}

func (tidbAdmin) RequireSchemaCurrent(ctx context.Context, db AdminDB) error {
	if err := (tidbAdmin{}).VerifySchema(ctx, db); err != nil {
		return err
	}

	state, err := (tidbAdmin{}).InspectMigrationState(ctx, db)
	if err != nil {
		return err
	}
	if state.Pending {
		return fmt.Errorf(
			"pending core storage migrations: current=%d target=%d",
			state.CurrentVersion,
			state.TargetVersion,
		)
	}
	return nil
}

func (tidbAdmin) RunStartupPreflight(ctx context.Context, db AdminDB) error {
	sqlDB, err := requireTiDB(db)
	if err != nil {
		return err
	}
	return tidb.NewAdmin().RunStartupPreflight(ctx, sqlDB)
}

func (tidbAdmin) ClassifyRetryable(err error) bool {
	return IsRetryableTransactionError(err)
}