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
}