package infra

import (
	"database/sql"
	"fmt"
	"path/filepath"

	_ "github.com/mattn/go-sqlite3"
)

// SQLiteOpen opens (or creates) a SQLite database file with WAL mode and
// foreign key support enabled. Returns a ready-to-use *sql.DB or an error.
// Pass ":memory:" for an in-memory database.
//
// If basePath is non-empty and path is relative, the path is resolved as
// filepath.Join(basePath, path). This is useful when the path comes from a
// config file and must be interpreted relative to that file's directory rather
// than the process working directory.
func SQLiteOpen(path string, basePath string) (*sql.DB, error) {
	if path == "" {
		return nil, fmt.Errorf("sqlite_open: path must not be empty (use ':memory:' for in-memory)")
	}
	resolved := path
	if basePath != "" && path != ":memory:" && !filepath.IsAbs(path) {
		resolved = filepath.Join(basePath, path)
	}
	dsn := fmt.Sprintf("file:%s?_journal_mode=WAL&_foreign_keys=on", resolved)
	db, err := sql.Open("sqlite3", dsn)
	if err != nil {
		return nil, fmt.Errorf("sqlite_open: open %q: %w", resolved, err)
	}
	if err := db.Ping(); err != nil {
		db.Close()
		return nil, fmt.Errorf("sqlite_open: ping %q: %w", resolved, err)
	}
	return db, nil
}