db.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"io"
	"os"
	"path/filepath"
	"time"

	_ "modernc.org/sqlite"
)

type DB struct {
	conn   *sql.DB
	dbPath string
}

func NewDB() (*DB, error) {
	home, err := os.UserHomeDir()
	if err != nil {
		return nil, fmt.Errorf("cannot find home dir: %w", err)
	}
	dir := filepath.Join(home, ".local", "share", "trueblocks", "ceiling")
	if err := os.MkdirAll(dir, 0755); err != nil {
		return nil, fmt.Errorf("cannot create data dir: %w", err)
	}

	dbPath := filepath.Join(dir, "ceiling.db")
	conn, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("cannot open database: %w", err)
	}

	db := &DB{conn: conn, dbPath: dbPath}
	if err := db.migrate(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("migration failed: %w", err)
	}
	return db, nil
}

func (db *DB) Close() error {
	return db.conn.Close()
}

func (db *DB) migrate() error {
	_, err := db.conn.Exec(`
		CREATE TABLE IF NOT EXISTS app_state (
			key   TEXT PRIMARY KEY,
			value TEXT NOT NULL
		)
	`)
	if err != nil {
		return err
	}

	_, err = db.conn.Exec(`
		CREATE TABLE IF NOT EXISTS projects (
			id   INTEGER PRIMARY KEY AUTOINCREMENT,
			name TEXT NOT NULL
		)
	`)
	if err != nil {
		return err
	}

	var count int
	err = db.conn.QueryRow(`SELECT COUNT(*) FROM projects`).Scan(&count)
	if err != nil {
		return err
	}
	if count == 0 {
		_, err = db.conn.Exec(`INSERT INTO projects (name) VALUES (?)`, "Back Basement")
		if err != nil {
			return err
		}

		var raw string
		err = db.conn.QueryRow(`SELECT value FROM app_state WHERE key = ?`, "segments").Scan(&raw)
		if err == nil && raw != "" {
			_, _ = db.conn.Exec(
				`INSERT INTO app_state (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
				"segments_1", raw,
			)
		}
	}

	return nil
}

type WindowState struct {
	X      int `json:"x"`
	Y      int `json:"y"`
	Width  int `json:"width"`
	Height int `json:"height"`
}

func (db *DB) SaveState(key string, value interface{}) error {
	data, err := json.Marshal(value)
	if err != nil {
		return err
	}
	_, err = db.conn.Exec(
		`INSERT INTO app_state (key, value) VALUES (?, ?)
		 ON CONFLICT(key) DO UPDATE SET value = excluded.value`,
		key, string(data),
	)
	return err
}

func (db *DB) LoadState(key string, dest interface{}) error {
	var raw string
	err := db.conn.QueryRow(`SELECT value FROM app_state WHERE key = ?`, key).Scan(&raw)
	if err != nil {
		return err
	}
	return json.Unmarshal([]byte(raw), dest)
}

func (db *DB) Backup() (string, error) {
	if _, err := db.conn.Exec(`PRAGMA wal_checkpoint(TRUNCATE)`); err != nil {
		return "", fmt.Errorf("WAL checkpoint failed: %w", err)
	}

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

	backupDir := filepath.Join(filepath.Dir(db.dbPath), "backups")
	if err := os.MkdirAll(backupDir, 0755); err != nil {
		return "", fmt.Errorf("cannot create backup dir: %w", err)
	}

	stamp := time.Now().Format("2006-01-02_15-04-05")
	backupPath := filepath.Join(backupDir, fmt.Sprintf("ceiling_%s.db", stamp))

	if err := copyFile(db.dbPath, backupPath); err != nil {
		return "", fmt.Errorf("copy failed: %w", err)
	}

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

	return backupPath, nil
}

func copyFile(src, dst string) error {
	in, err := os.Open(src)
	if err != nil {
		return err
	}
	defer in.Close()

	out, err := os.Create(dst)
	if err != nil {
		return err
	}
	defer out.Close()

	if _, err := io.Copy(out, in); err != nil {
		return err
	}
	return out.Sync()
}