// Package database provides SQLite database initialization with WAL mode,
// optimized PRAGMA settings for HDD I/O on resource-constrained VPS,
// and schema management with version tracking.
package database

import (
	"database/sql"
	"fmt"

	_ "modernc.org/sqlite"
)

// Config holds database configuration options.
type Config struct {
	// Path is the filesystem path to the SQLite database file.
	Path string

	// MaxOpenConns is the maximum number of open connections.
	// Default is 1 for single-writer pattern (exclusive connection ownership).
	MaxOpenConns int
}

// DefaultConfig returns a Config with sensible defaults.
func DefaultConfig(path string) Config {
	return Config{
		Path:         path,
		MaxOpenConns: 1, // Single-writer pattern requires exclusive connection
	}
}

// Open opens a SQLite database with WAL mode and optimized PRAGMA settings
// for HDD I/O on resource-constrained VPS (1GB RAM, 25GB HDD).
//
// The connection string enables:
//   - WAL journal mode for concurrent reads during writes
//   - 5 second busy timeout for lock contention
//
// PRAGMA settings applied:
//   - synchronous = NORMAL (safe in WAL, faster than FULL)
//   - cache_size = -64000 (64MB cache)
//   - temp_store = MEMORY (temp tables in memory)
//   - mmap_size = 30000000 (30MB memory-mapped I/O)
//   - wal_autocheckpoint = 1000 (checkpoint every 1000 pages)
//
// Returns error if WAL mode cannot be enabled or any PRAGMA fails.
func Open(cfg Config) (*sql.DB, error) {
	if cfg.Path == "" {
		return nil, fmt.Errorf("database path is required")
	}

	if cfg.MaxOpenConns <= 0 {
		cfg.MaxOpenConns = 1
	}

	// Connection string with WAL mode and busy timeout
	// modernc.org/sqlite uses URI-style connection strings
	connStr := fmt.Sprintf("%s?_journal_mode=WAL&_busy_timeout=5000", cfg.Path)

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

	// Set connection pool limits for single-writer pattern
	db.SetMaxOpenConns(cfg.MaxOpenConns)
	db.SetMaxIdleConns(cfg.MaxOpenConns)

	// Enable WAL mode first (must be done before other settings)
	// Note: modernc.org/sqlite does not parse connection string parameters,
	// so we must set journal_mode explicitly via PRAGMA
	_, err = db.Exec("PRAGMA journal_mode = WAL")
	if err != nil {
		db.Close()
		return nil, fmt.Errorf("set journal_mode WAL: %w", err)
	}

	// Apply PRAGMA settings in order
	pragmas := []struct {
		name  string
		value string
	}{
		{"synchronous", "NORMAL"},        // Safe in WAL, faster than FULL
		{"cache_size", "-64000"},         // 64MB cache (negative = KB)
		{"temp_store", "MEMORY"},         // Temp tables in memory
		{"mmap_size", "30000000"},        // 30MB memory-mapped I/O
		{"wal_autocheckpoint", "1000"},   // Checkpoint every 1000 pages
	}

	for _, p := range pragmas {
		_, err := db.Exec(fmt.Sprintf("PRAGMA %s = %s", p.name, p.value))
		if err != nil {
			db.Close()
			return nil, fmt.Errorf("set PRAGMA %s: %w", p.name, err)
		}
	}

	// Verify WAL mode is enabled
	var journalMode string
	err = db.QueryRow("PRAGMA journal_mode").Scan(&journalMode)
	if err != nil {
		db.Close()
		return nil, fmt.Errorf("verify journal_mode: %w", err)
	}

	if journalMode != "wal" {
		db.Close()
		return nil, fmt.Errorf("WAL mode not enabled: got %q", journalMode)
	}

	return db, nil
}