1 files changed, 68 insertions, 0 deletions
diff --git a/internal/database/db.go b/internal/database/db.go
new file mode 100644
index 0000000..f699d14
--- /dev/null
+++ b/internal/database/db.go
@@ -0,0 +1,68 @@
+package database
+
+import (
+	"database/sql"
+	_ "embed"
+	"fmt"
+	"os"
+	"path/filepath"
+
+	"punchcard/internal/queries"
+
+	_ "modernc.org/sqlite"
+)
+
+//go:embed schema.sql
+var schema string
+
+func GetDB() (*queries.Queries, error) {
+	dataDir := os.Getenv("XDG_DATA_HOME")
+	if dataDir == "" {
+		homeDir, err := os.UserHomeDir()
+		if err != nil {
+			return nil, fmt.Errorf("failed to get user home directory: %w", err)
+		}
+		dataDir = filepath.Join(homeDir, ".local", "share")
+	}
+
+	punchcardDir := filepath.Join(dataDir, "punchcard")
+	if err := os.MkdirAll(punchcardDir, 0o755); err != nil {
+		return nil, fmt.Errorf("failed to create punchcard directory at %s: %w", punchcardDir, err)
+	}
+
+	dbPath := filepath.Join(punchcardDir, "punchcard.db")
+	db, err := sql.Open("sqlite", dbPath)
+	if err != nil {
+		return nil, fmt.Errorf("failed to open database at %s: %w", dbPath, err)
+	}
+
+	if err := InitializeDB(db); err != nil {
+		return nil, err
+	}
+
+	return queries.New(db), nil
+}
+
+func InitializeDB(db *sql.DB) error {
+	if _, err := db.Exec(schema); err != nil {
+		return fmt.Errorf("failed to execute schema: %w", err)
+	}
+
+	pragmas := []string{
+		"PRAGMA foreign_keys = ON;",
+		"PRAGMA journal_mode = WAL;",
+		"PRAGMA synchronous = NORMAL;",
+		"PRAGMA cache_size = -64000;",
+		"PRAGMA temp_store = MEMORY;",
+		"PRAGMA mmap_size = 67108864;",
+		"PRAGMA optimize;",
+	}
+
+	for _, pragma := range pragmas {
+		if _, err := db.Exec(pragma); err != nil {
+			return fmt.Errorf("failed to execute pragma %s: %w", pragma, err)
+		}
+	}
+
+	return nil
+}