feat: Add database maintenance with backup/reset modes and triggers

1 files changed, 86 insertions, 21 deletions

diff --git a/internal/storage/sqlite.go b/internal/storage/sqlite.go
index 3d89cb1..db0ed52 100644
--- a/internal/storage/sqlite.go
+++ b/internal/storage/sqlite.go
@@ -7,13 +7,16 @@ import (
 	"strings"
 	"time"
 
+	"github.com/Fuwn/kaze/internal/config"
 	_ "modernc.org/sqlite"
 )
 
 // Storage handles all database operations
 type Storage struct {
 	db          *sql.DB
+	dbPath      string
 	historyDays int
+	maintenance *MaintenanceState
 }
 
 // CheckResult represents a single monitor check result
@@ -71,11 +74,40 @@ type TickData struct {
 
 // New creates a new storage instance
 func New(dbPath string, historyDays int) (*Storage, error) {
-	// Add connection parameters for better concurrency handling
-	// _txlock=immediate ensures transactions acquire locks immediately
-	// _busy_timeout=5000 waits up to 5 seconds for locks
-	// _journal_mode=WAL enables write-ahead logging for better concurrency
-	// _synchronous=NORMAL balances safety and performance
+	return NewWithMaintenance(dbPath, historyDays, config.MaintenanceConfig{})
+}
+
+// NewWithMaintenance creates a new storage instance with maintenance configuration
+func NewWithMaintenance(dbPath string, historyDays int, maintenanceCfg config.MaintenanceConfig) (*Storage, error) {
+	db, err := openDB(dbPath)
+	if err != nil {
+		return nil, err
+	}
+
+	s := &Storage{
+		db:          db,
+		dbPath:      dbPath,
+		historyDays: historyDays,
+	}
+
+	if maintenanceCfg.Mode != "" && maintenanceCfg.Mode != "never" {
+		m, err := NewMaintenanceState(dbPath, maintenanceCfg)
+		if err != nil {
+			db.Close()
+			return nil, fmt.Errorf("failed to initialize maintenance: %w", err)
+		}
+		s.maintenance = m
+	}
+
+	if err := s.migrate(); err != nil {
+		db.Close()
+		return nil, fmt.Errorf("failed to run migrations: %w", err)
+	}
+
+	return s, nil
+}
+
+func openDB(dbPath string) (*sql.DB, error) {
 	dsn := fmt.Sprintf("%s?_txlock=immediate&_busy_timeout=5000&_journal_mode=WAL&_synchronous=NORMAL", dbPath)
 
 	db, err := sql.Open("sqlite", dsn)
@@ -83,36 +115,22 @@ func New(dbPath string, historyDays int) (*Storage, error) {
 		return nil, fmt.Errorf("failed to open database: %w", err)
 	}
 
-	// CRITICAL: Set max open connections to 1 for SQLite
-	// SQLite only supports one writer at a time, so we serialize all writes
 	db.SetMaxOpenConns(1)
 	db.SetMaxIdleConns(1)
-	db.SetConnMaxLifetime(0) // Don't close idle connections
+	db.SetConnMaxLifetime(0)
 
-	// Verify WAL mode is enabled
 	var journalMode string
 	if err := db.QueryRow("PRAGMA journal_mode").Scan(&journalMode); err != nil {
 		db.Close()
 		return nil, fmt.Errorf("failed to check journal mode: %w", err)
 	}
 
-	// Enable foreign keys (must be done per-connection, but with 1 conn it's fine)
 	if _, err := db.Exec("PRAGMA foreign_keys=ON"); err != nil {
 		db.Close()
 		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
 	}
 
-	s := &Storage{
-		db:          db,
-		historyDays: historyDays,
-	}
-
-	if err := s.migrate(); err != nil {
-		db.Close()
-		return nil, fmt.Errorf("failed to run migrations: %w", err)
-	}
-
-	return s, nil
+	return db, nil
 }
 
 // migrate creates the database schema
@@ -769,3 +787,50 @@ func (s *Storage) ResetMonitorData(ctx context.Context, monitorName string) erro
 func (s *Storage) Close() error {
 	return s.db.Close()
 }
+
+func (s *Storage) RecordCheck() {
+	if s.maintenance != nil {
+		s.maintenance.IncrementChecks()
+	}
+}
+
+func (s *Storage) CheckMaintenance() (bool, error) {
+	if s.maintenance == nil {
+		return false, nil
+	}
+
+	if !s.maintenance.ShouldRun() {
+		return false, nil
+	}
+
+	if err := s.db.Close(); err != nil {
+		return false, fmt.Errorf("failed to close database before maintenance: %w", err)
+	}
+
+	if err := s.maintenance.Execute(); err != nil {
+		db, openErr := openDB(s.dbPath)
+		if openErr == nil {
+			s.db = db
+		}
+		return false, fmt.Errorf("maintenance failed: %w", err)
+	}
+
+	db, err := openDB(s.dbPath)
+	if err != nil {
+		return true, fmt.Errorf("failed to reopen database after maintenance: %w", err)
+	}
+	s.db = db
+
+	if err := s.migrate(); err != nil {
+		return true, fmt.Errorf("failed to migrate after maintenance: %w", err)
+	}
+
+	return true, nil
+}
+
+func (s *Storage) GetMaintenanceMode() string {
+	if s.maintenance == nil {
+		return "never"
+	}
+	return s.maintenance.cfg.Mode
+}