package database

import (
	"context"
	"crypto/tls"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"os"
	"strconv"
	"strings"
	"sync"
	"time"

	"github.com/go-sql-driver/mysql"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	_ "github.com/nexuscrm/kernel/pkg/telemetry" // Register mysql-tracing driver
)

// TiDBConnection represents a TiDB database connection
// Note: sql.DB is already thread-safe and manages its own connection pool.
// We do NOT wrap it with additional mutexes as that causes deadlocks under
// high concurrency (writers waiting for connections block readers).
type TiDBConnection struct {
	db                    *sql.DB
	independentDBOpenSpec *dbOpenSpec
}

type dbOpenSpec struct {
	driverName string
	dsn        string
}

type PoolOptions struct {
	MaxOpenConns    int
	MaxIdleConns    int
	ConnMaxLifetime time.Duration
	ConnMaxIdleTime time.Duration
	// InterpolateParams asks go-sql-driver/mysql to encode supported typed
	// parameters into one COM_QUERY request. This removes the PREPARE, EXECUTE,
	// and CLOSE round trips used by the default path. The driver rejects unsafe
	// collations and falls back to prepared statements for unsupported shapes.
	InterpolateParams bool
	// NetworkTimeout bounds each dial, read and write on this pool's physical
	// connections, including COMMIT/ROLLBACK which lack a context in the driver.
	// Zero preserves the driver's defaults. This is not a transaction deadline.
	NetworkTimeout time.Duration
}

var (
	instance *TiDBConnection
	once     sync.Once
	initErr  error
	tlsOnce  sync.Once // Ensure TLS config is registered only once
)

// ErrIndependentDBUnavailable means a TiDBConnection was wrapped around an
// existing sql.DB without the immutable driver/DSN authority needed to create
// a matching independent pool.
var ErrIndependentDBUnavailable = errors.New("independent database pool is unavailable")

const (
	defaultDBMaxOpenConns = 100

	dbMaxOpenConnsEnv = "NEXUSCRM_DB_MAX_OPEN_CONNS"
	dbMaxIdleConnsEnv = "NEXUSCRM_DB_MAX_IDLE_CONNS"
)

// GetInstance returns the singleton TiDB connection
func GetInstance() (*TiDBConnection, error) {
	once.Do(func() {
		instance, initErr = NewConnection()
	})
	return instance, initErr
}

// NewConnection creates a new TiDB connection using environment variables
func NewConnection() (*TiDBConnection, error) {
	return NewConnectionContext(context.Background(), DefaultPoolOptions())
}

// NewConnectionContext creates one TiDB connection with an explicitly bounded
// first readiness probe while preserving the immutable driver/DSN authority
// required by OpenIndependentDB. Long-running process assembly must use this
// path so startup cancellation applies to the first network access, not only a
// redundant probe after construction.
func NewConnectionContext(ctx context.Context, options PoolOptions) (*TiDBConnection, error) {
	if ctx == nil {
		return nil, errors.New("database connection context is required")
	}
	openSpec := dbOpenSpecFromEnv()
	db, err := openSpec.open(options)
	if err != nil {
		return nil, err
	}

	if err := db.PingContext(ctx); err != nil {
		_ = db.Close()
		return nil, apperrors.NewInternalError("failed to ping database", err)
	}

	return newTiDBConnection(db, openSpec), nil
}

func DefaultPoolOptions() PoolOptions {
	maxOpen := parsePositiveIntEnv(os.Getenv, dbMaxOpenConnsEnv, defaultDBMaxOpenConns)
	maxIdle := parsePositiveIntEnv(os.Getenv, dbMaxIdleConnsEnv, maxOpen)
	if maxIdle > maxOpen {
		maxIdle = maxOpen
	}
	return PoolOptions{
		MaxOpenConns:    maxOpen,
		MaxIdleConns:    maxIdle,
		ConnMaxLifetime: 5 * time.Minute,
		ConnMaxIdleTime: 3 * time.Minute,
	}
}

func OpenDBFromEnv(options PoolOptions) (*sql.DB, error) {
	return dbOpenSpecFromEnv().open(options)
}

func dbOpenSpecFromEnv() dbOpenSpec {
	host := os.Getenv("TIDB_HOST")
	port := os.Getenv("TIDB_PORT")
	user := os.Getenv("TIDB_USER")
	password := os.Getenv("TIDB_PASSWORD")
	database := os.Getenv("TIDB_DATABASE")

	if port == "" {
		port = "4000"
	}

	if database == "" {
		database = "nexuscrm"
	}

	// Determine TLS configuration based on host. The non-loopback heuristic
	// is a stand-in for "this is TiDB Cloud"; container-network and LAN hosts
	// (e.g. a sibling `tidb` service in docker compose) do not support TLS.
	// TIDB_TLS_DISABLE=1 forces the connection to skip TLS for those cases.
	tlsParam := ""
	tlsDisabled := os.Getenv("TIDB_TLS_DISABLE") == "1" || strings.EqualFold(os.Getenv("TIDB_TLS_DISABLE"), "true")
	if !tlsDisabled && host != "" && host != "127.0.0.1" && host != "localhost" {
		// Remote host (e.g., TiDB Cloud) - register TLS config with ServerName
		// Use sync.Once to prevent panic on duplicate registration (e.g., in tests)
		tlsOnce.Do(func() {
			if err := mysql.RegisterTLSConfig("tidb", &tls.Config{
				MinVersion: tls.VersionTLS12,
				ServerName: host, // Required for TLS verification
			}); err != nil {
				// Just log as we can't return error from sync.Once
				log.Printf("Failed to register TLS config: %v\n", err)
			}
		})
		tlsParam = "&tls=tidb"
	}
	// For localhost, no TLS is used
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?charset=utf8mb4&parseTime=True&loc=Local%s",
		user, password, host, port, database, tlsParam)
	return dbOpenSpec{driverName: "mysql-tracing", dsn: dsn}
}

func (s dbOpenSpec) open(options PoolOptions) (*sql.DB, error) {
	dsn, err := s.dsnForOptions(options)
	if err != nil {
		return nil, err
	}
	// Use our custom TracingDriver ("mysql-tracing") which wraps "mysql"
	// This injects trace_id/traceparent into SQL comments for DB-layer observability
	db, err := sql.Open(s.driverName, dsn)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to open database", err)
	}

	applyPoolOptions(db, options)

	return db, nil
}

func (s dbOpenSpec) dsnForOptions(options PoolOptions) (string, error) {
	if options.NetworkTimeout <= 0 && !options.InterpolateParams {
		return s.dsn, nil
	}
	config, err := mysql.ParseDSN(s.dsn)
	if err != nil {
		return "", apperrors.NewInternalError("invalid database connection configuration", err)
	}
	if options.NetworkTimeout > 0 {
		config.Timeout = options.NetworkTimeout
		config.ReadTimeout = options.NetworkTimeout
		config.WriteTimeout = options.NetworkTimeout
	}
	if options.InterpolateParams {
		config.InterpolateParams = true
	}
	return config.FormatDSN(), nil
}

func newTiDBConnection(db *sql.DB, openSpec dbOpenSpec) *TiDBConnection {
	return &TiDBConnection{db: db, independentDBOpenSpec: &openSpec}
}

func applyPoolOptions(db *sql.DB, options PoolOptions) {
	if options.MaxOpenConns <= 0 {
		options.MaxOpenConns = defaultDBMaxOpenConns
	}
	if options.MaxIdleConns <= 0 {
		options.MaxIdleConns = options.MaxOpenConns
	}
	if options.MaxIdleConns > options.MaxOpenConns {
		options.MaxIdleConns = options.MaxOpenConns
	}
	if options.ConnMaxLifetime <= 0 {
		options.ConnMaxLifetime = 5 * time.Minute
	}
	if options.ConnMaxIdleTime <= 0 {
		options.ConnMaxIdleTime = 3 * time.Minute
	}

	db.SetMaxOpenConns(options.MaxOpenConns)
	db.SetMaxIdleConns(options.MaxIdleConns)
	db.SetConnMaxLifetime(options.ConnMaxLifetime)
	db.SetConnMaxIdleTime(options.ConnMaxIdleTime)
}

func parsePositiveIntEnv(getenv func(string) string, name string, fallback int) int {
	if getenv == nil {
		getenv = os.Getenv
	}
	value := strings.TrimSpace(getenv(name))
	if value == "" {
		return fallback
	}
	parsed, err := strconv.Atoi(value)
	if err != nil || parsed <= 0 {
		return fallback
	}
	return parsed
}

// NewConnectionFromDB wraps an existing *sql.DB in TiDBConnection.
// Useful for tests or adapters that already own connection construction.
func NewConnectionFromDB(db *sql.DB) *TiDBConnection {
	return &TiDBConnection{db: db}
}

// OpenIndependentDB returns a newly constructed empty connection pool using
// the immutable driver/DSN authority captured by NewConnection and the caller's
// pool limits (including NetworkTimeout). Its
// first query therefore runs on a physical session independent of the primary
// pool while retaining sql.DB's native bad-connection retry behavior. The
// caller owns and must close the returned pool.
//
// Connections wrapped through NewConnectionFromDB do not own connection
// configuration and cannot safely manufacture a matching independent pool.
func (c *TiDBConnection) OpenIndependentDB(options PoolOptions) (*sql.DB, error) {
	if c == nil || c.independentDBOpenSpec == nil {
		return nil, ErrIndependentDBUnavailable
	}
	return c.independentDBOpenSpec.open(options)
}

// Query executes a SELECT query and returns rows
// sql.DB handles connection pooling and concurrency internally
func (c *TiDBConnection) Query(query string, args ...interface{}) (*sql.Rows, error) {
	return c.db.Query(query, args...)
}

// QueryContext executes a SELECT query with context
func (c *TiDBConnection) QueryContext(ctx context.Context, query string, args ...interface{}) (*sql.Rows, error) {
	return c.db.QueryContext(ctx, query, args...)
}

// QueryRow executes a SELECT query that returns at most one row
func (c *TiDBConnection) QueryRow(query string, args ...interface{}) *sql.Row {
	return c.db.QueryRow(query, args...)
}

// QueryRowContext executes a SELECT query with context that returns at most one row
func (c *TiDBConnection) QueryRowContext(ctx context.Context, query string, args ...interface{}) *sql.Row {
	return c.db.QueryRowContext(ctx, query, args...)
}

// Exec executes an INSERT, UPDATE, or DELETE query
func (c *TiDBConnection) Exec(query string, args ...interface{}) (sql.Result, error) {
	return c.db.Exec(query, args...)
}

// ExecContext executes an INSERT, UPDATE, or DELETE query with context
func (c *TiDBConnection) ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error) {
	return c.db.ExecContext(ctx, query, args...)
}

// Begin starts a new transaction
func (c *TiDBConnection) Begin() (*sql.Tx, error) {
	return c.db.Begin()
}

// BeginTx starts a new transaction with context
func (c *TiDBConnection) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) {
	return c.db.BeginTx(ctx, opts)
}

// DB returns the underlying *sql.DB connection
// This is useful for operations that need direct access to sql.DB
func (c *TiDBConnection) DB() *sql.DB {
	return c.db
}

// Close closes the database connection
func (c *TiDBConnection) Close() error {
	return c.db.Close()
}