package db

import (
	"database/sql"
	"fmt"
	"regexp"
	"strings"
)

// Dialect captures the small set of SQL constructs that differ between MySQL
// and SQLite. The ~95% of the server's queries are plain CRUD with "?"
// placeholders and need no dialect handling; only upserts and row locks do.
type Dialect interface {
	// Name reports "mysql" or "sqlite".
	Name() string

	// LockForUpdate returns the pessimistic row-lock clause to append to a
	// SELECT inside a transaction:
	//   mysql  → "FOR UPDATE"
	//   sqlite → "" (single-writer engine; the BEGIN IMMEDIATE transaction —
	//             enabled via _txlock=immediate in the DSN — already serializes
	//             writers, giving equivalent exclusivity).
	LockForUpdate() string

	// Upsert builds the conflict-resolution tail for an INSERT. conflictCols is
	// the unique/primary key that triggers the conflict. setExprs are assignment
	// expressions; refer to the would-be-inserted value with the sentinel
	// "EXCLUDED.<col>" (rewritten to VALUES(col) on MySQL, kept as excluded.col
	// on SQLite). With no setExprs the conflict is a no-op (insert-or-ignore).
	//
	// Example (atomic accumulate):
	//   d.Upsert([]string{"user_id","date"},
	//       "bytes_up = bytes_up + EXCLUDED.bytes_up")
	Upsert(conflictCols []string, setExprs ...string) string
}

// DialectForDB derives the Dialect from the driver backing an open *sql.DB, so
// the dialect always matches the actual connection (no global state, and store
// constructors keep their (db *sql.DB) signatures).
func DialectForDB(database *sql.DB) Dialect {
	if database == nil {
		return MySQLDialect{}
	}
	name := strings.ToLower(fmt.Sprintf("%T", database.Driver()))
	if strings.Contains(name, "sqlite") {
		return SQLiteDialect{}
	}
	return MySQLDialect{}
}

// DialectFor returns the Dialect for a driver name ("mysql"|"sqlite").
func DialectFor(driver string) Dialect {
	if Normalize(driver) == "sqlite" {
		return SQLiteDialect{}
	}
	return MySQLDialect{}
}

var excludedRef = regexp.MustCompile(`EXCLUDED\.(\w+)`)

// MySQLDialect implements Dialect for MySQL.
type MySQLDialect struct{}

func (MySQLDialect) Name() string          { return "mysql" }
func (MySQLDialect) LockForUpdate() string { return "FOR UPDATE" }

func (MySQLDialect) Upsert(conflictCols []string, setExprs ...string) string {
	if len(setExprs) == 0 {
		// No-op upsert (insert-or-ignore): assign a key column to itself.
		col := "id"
		if len(conflictCols) > 0 {
			col = conflictCols[0]
		}
		return fmt.Sprintf("ON DUPLICATE KEY UPDATE %s = %s", col, col)
	}
	rewritten := make([]string, len(setExprs))
	for i, e := range setExprs {
		rewritten[i] = excludedRef.ReplaceAllString(e, "VALUES($1)")
	}
	return "ON DUPLICATE KEY UPDATE " + strings.Join(rewritten, ", ")
}

// SQLiteDialect implements Dialect for SQLite.
type SQLiteDialect struct{}

func (SQLiteDialect) Name() string          { return "sqlite" }
func (SQLiteDialect) LockForUpdate() string { return "" }

func (SQLiteDialect) Upsert(conflictCols []string, setExprs ...string) string {
	target := "ON CONFLICT(" + strings.Join(conflictCols, ", ") + ") "
	if len(setExprs) == 0 {
		return target + "DO NOTHING"
	}
	// SQLite accepts the EXCLUDED.<col> reference verbatim.
	return target + "DO UPDATE SET " + strings.Join(setExprs, ", ")
}