hako/internal/database/connections.go

package database

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

	"git.nakama.town/fmartingr/gotoolkit/model"
)

// Connections holds read and write database connections and implements model.DB
type Connections struct {
	Read  *sql.DB
	Write *sql.DB
}

// Ensure Connections implements model.DB interface
var _ model.DB = (*Connections)(nil)

// NewConnections creates read and write database connections
func NewConnections(readURL, writeURL string) (*Connections, error) {
	readDB, err := connectDatabase(readURL, true)
	if err != nil {
		return nil, fmt.Errorf("failed to connect to read database: %w", err)
	}

	writeDB, err := connectDatabase(writeURL, false)
	if err != nil {
		_ = readDB.Close()
		return nil, fmt.Errorf("failed to connect to write database: %w", err)
	}

	// Configure SQLite for better performance
	if err := configureSQLite(readDB, true); err != nil {
		_ = readDB.Close()
		_ = writeDB.Close()
		return nil, fmt.Errorf("failed to configure read database: %w", err)
	}

	if err := configureSQLite(writeDB, false); err != nil {
		_ = readDB.Close()
		_ = writeDB.Close()
		return nil, fmt.Errorf("failed to configure write database: %w", err)
	}

	return &Connections{
		Read:  readDB,
		Write: writeDB,
	}, nil
}

// Close closes all database connections
func (c *Connections) Close() error {
	var errs []error
	if c.Read != nil {
		if err := c.Read.Close(); err != nil {
			errs = append(errs, fmt.Errorf("failed to close read connection: %w", err))
		}
	}
	if c.Write != nil {
		if err := c.Write.Close(); err != nil {
			errs = append(errs, fmt.Errorf("failed to close write connection: %w", err))
		}
	}
	if len(errs) > 0 {
		return fmt.Errorf("errors closing connections: %v", errs)
	}
	return nil
}

// connectDatabase connects to the database using the connection string
func connectDatabase(dbURL string, readOnly bool) (*sql.DB, error) {
	// Parse the database URL
	// Format: scheme:path (e.g., sqlite:./hako.db)
	// For SQLite, we need to extract the path after the colon
	var dsn string
	if len(dbURL) > 7 && dbURL[:7] == "sqlite:" {
		dsn = dbURL[7:]
	} else {
		return nil, fmt.Errorf("unsupported database scheme in URL: %s", dbURL)
	}

	// Add read-only mode for read connections
	if readOnly {
		// Check if DSN already has query parameters
		if strings.Contains(dsn, "?") {
			dsn += "&mode=ro"
		} else {
			dsn += "?mode=ro"
		}
	}

	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	return db, nil
}

// configureSQLite configures SQLite connection for optimal performance
func configureSQLite(db *sql.DB, readOnly bool) error {
	if readOnly {
		// For read-only connections, we can use more aggressive settings
		_, err := db.Exec("PRAGMA journal_mode=WAL")
		if err != nil {
			return fmt.Errorf("failed to set WAL mode: %w", err)
		}
		_, err = db.Exec("PRAGMA synchronous=NORMAL")
		if err != nil {
			return fmt.Errorf("failed to set synchronous mode: %w", err)
		}
		_, err = db.Exec("PRAGMA cache_size=-64000") // 64MB cache
		if err != nil {
			return fmt.Errorf("failed to set cache size: %w", err)
		}
	} else {
		// For write connections, use WAL mode for better concurrency
		_, err := db.Exec("PRAGMA journal_mode=WAL")
		if err != nil {
			return fmt.Errorf("failed to set WAL mode: %w", err)
		}
		_, err = db.Exec("PRAGMA synchronous=NORMAL")
		if err != nil {
			return fmt.Errorf("failed to set synchronous mode: %w", err)
		}
		_, err = db.Exec("PRAGMA cache_size=-64000") // 64MB cache
		if err != nil {
			return fmt.Errorf("failed to set cache size: %w", err)
		}
		_, err = db.Exec("PRAGMA foreign_keys=ON")
		if err != nil {
			return fmt.Errorf("failed to enable foreign keys: %w", err)
		}
		// Set connection pool settings for write connection
		db.SetMaxOpenConns(1) // SQLite works best with single writer
		db.SetMaxIdleConns(1)
	}

	// Set connection pool settings for read connection
	if readOnly {
		db.SetMaxOpenConns(10) // Multiple readers allowed
		db.SetMaxIdleConns(5)
	}

	return nil
}

// WriterDB returns the write database connection
func (c *Connections) WriterDB() *sql.DB {
	return c.Write
}

// ReaderDB returns the read database connection
func (c *Connections) ReaderDB() *sql.DB {
	return c.Read
}

// GetDatabaseSchemaVersion returns the current database schema version
func (c *Connections) GetDatabaseSchemaVersion(ctx context.Context) (string, error) {
	var version string
	err := c.Read.QueryRowContext(ctx, "SELECT version FROM schema_version ORDER BY id DESC LIMIT 1").Scan(&version)
	if err != nil {
		if err == sql.ErrNoRows {
			return "", nil // No version means fresh database
		}
		return "", fmt.Errorf("failed to get schema version: %w", err)
	}
	return version, nil
}

// SetDatabaseSchemaVersion sets the database schema version
func (c *Connections) SetDatabaseSchemaVersion(ctx context.Context, version string) error {
	_, err := c.Write.ExecContext(ctx, "INSERT INTO schema_version (version) VALUES (?)", version)
	if err != nil {
		return fmt.Errorf("failed to set schema version: %w", err)
	}
	return nil
}