add initial stuff

9 files changed, 491 insertions, 0 deletions

diff --git a/internal/db/connect.go b/internal/db/connect.go
new file mode 100644
index 000000000..50a30d0bc
--- /dev/null
+++ b/internal/db/connect.go
@@ -0,0 +1,93 @@
+package db
+
+import (
+	"database/sql"
+	"fmt"
+	"os"
+	"path/filepath"
+
+	"github.com/golang-migrate/migrate/v4"
+	"github.com/golang-migrate/migrate/v4/source/iofs"
+
+	"github.com/golang-migrate/migrate/v4/database/sqlite3"
+	_ "github.com/mattn/go-sqlite3"
+
+	"github.com/kujtimiihoxha/termai/internal/logging"
+	"github.com/spf13/viper"
+)
+
+var log = logging.Get()
+
+func Connect() (*sql.DB, error) {
+	dataDir := viper.GetString("data.dir")
+	if dataDir == "" {
+		return nil, fmt.Errorf("data.dir is not set")
+	}
+	if err := os.MkdirAll(dataDir, 0o700); err != nil {
+		return nil, fmt.Errorf("failed to create data directory: %w", err)
+	}
+	dbPath := filepath.Join(dataDir, "termai.db")
+	// Open the SQLite database
+	db, err := sql.Open("sqlite3", dbPath)
+	if err != nil {
+		return nil, fmt.Errorf("failed to open database: %w", err)
+	}
+
+	// Verify connection
+	if err = db.Ping(); err != nil {
+		db.Close()
+		return nil, fmt.Errorf("failed to connect to database: %w", err)
+	}
+
+	// Set pragmas for better performance
+	pragmas := []string{
+		"PRAGMA foreign_keys = ON;",
+		"PRAGMA journal_mode = WAL;",
+		"PRAGMA page_size = 4096;",
+		"PRAGMA cache_size = -8000;",
+		"PRAGMA synchronous = NORMAL;",
+	}
+
+	for _, pragma := range pragmas {
+		if _, err = db.Exec(pragma); err != nil {
+			log.Warn("Failed to set pragma", pragma, err)
+		} else {
+			log.Warn("Set pragma", "pragma", pragma)
+		}
+	}
+
+	// Initialize schema from embedded file
+	d, err := iofs.New(FS, "migrations")
+	if err != nil {
+		log.Error("Failed to open embedded migrations", "error", err)
+		db.Close()
+		return nil, fmt.Errorf("failed to open embedded migrations: %w", err)
+	}
+
+	driver, err := sqlite3.WithInstance(db, &sqlite3.Config{})
+	if err != nil {
+		log.Error("Failed to create SQLite driver", "error", err)
+		db.Close()
+		return nil, fmt.Errorf("failed to create SQLite driver: %w", err)
+	}
+
+	m, err := migrate.NewWithInstance("iofs", d, "ql", driver)
+	if err != nil {
+		log.Error("Failed to create migration instance", "error", err)
+		db.Close()
+		return nil, fmt.Errorf("failed to create migration instance: %w", err)
+	}
+
+	err = m.Up()
+	if err != nil && err != migrate.ErrNoChange {
+		log.Error("Migration failed", "error", err)
+		db.Close()
+		return nil, fmt.Errorf("failed to apply schema: %w", err)
+	} else if err == migrate.ErrNoChange {
+		log.Info("No schema changes to apply")
+	} else {
+		log.Info("Schema migration applied successfully")
+	}
+
+	return db, nil
+}
diff --git a/internal/db/db.go b/internal/db/db.go
new file mode 100644
index 000000000..b97eb7108
--- /dev/null
+++ b/internal/db/db.go
@@ -0,0 +1,128 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+
+package db
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+func Prepare(ctx context.Context, db DBTX) (*Queries, error) {
+	q := Queries{db: db}
+	var err error
+	if q.createSessionStmt, err = db.PrepareContext(ctx, createSession); err != nil {
+		return nil, fmt.Errorf("error preparing query CreateSession: %w", err)
+	}
+	if q.deleteSessionStmt, err = db.PrepareContext(ctx, deleteSession); err != nil {
+		return nil, fmt.Errorf("error preparing query DeleteSession: %w", err)
+	}
+	if q.getSessionByIDStmt, err = db.PrepareContext(ctx, getSessionByID); err != nil {
+		return nil, fmt.Errorf("error preparing query GetSessionByID: %w", err)
+	}
+	if q.listSessionsStmt, err = db.PrepareContext(ctx, listSessions); err != nil {
+		return nil, fmt.Errorf("error preparing query ListSessions: %w", err)
+	}
+	if q.updateSessionStmt, err = db.PrepareContext(ctx, updateSession); err != nil {
+		return nil, fmt.Errorf("error preparing query UpdateSession: %w", err)
+	}
+	return &q, nil
+}
+
+func (q *Queries) Close() error {
+	var err error
+	if q.createSessionStmt != nil {
+		if cerr := q.createSessionStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing createSessionStmt: %w", cerr)
+		}
+	}
+	if q.deleteSessionStmt != nil {
+		if cerr := q.deleteSessionStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing deleteSessionStmt: %w", cerr)
+		}
+	}
+	if q.getSessionByIDStmt != nil {
+		if cerr := q.getSessionByIDStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing getSessionByIDStmt: %w", cerr)
+		}
+	}
+	if q.listSessionsStmt != nil {
+		if cerr := q.listSessionsStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing listSessionsStmt: %w", cerr)
+		}
+	}
+	if q.updateSessionStmt != nil {
+		if cerr := q.updateSessionStmt.Close(); cerr != nil {
+			err = fmt.Errorf("error closing updateSessionStmt: %w", cerr)
+		}
+	}
+	return err
+}
+
+func (q *Queries) exec(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) (sql.Result, error) {
+	switch {
+	case stmt != nil && q.tx != nil:
+		return q.tx.StmtContext(ctx, stmt).ExecContext(ctx, args...)
+	case stmt != nil:
+		return stmt.ExecContext(ctx, args...)
+	default:
+		return q.db.ExecContext(ctx, query, args...)
+	}
+}
+
+func (q *Queries) query(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) (*sql.Rows, error) {
+	switch {
+	case stmt != nil && q.tx != nil:
+		return q.tx.StmtContext(ctx, stmt).QueryContext(ctx, args...)
+	case stmt != nil:
+		return stmt.QueryContext(ctx, args...)
+	default:
+		return q.db.QueryContext(ctx, query, args...)
+	}
+}
+
+func (q *Queries) queryRow(ctx context.Context, stmt *sql.Stmt, query string, args ...interface{}) *sql.Row {
+	switch {
+	case stmt != nil && q.tx != nil:
+		return q.tx.StmtContext(ctx, stmt).QueryRowContext(ctx, args...)
+	case stmt != nil:
+		return stmt.QueryRowContext(ctx, args...)
+	default:
+		return q.db.QueryRowContext(ctx, query, args...)
+	}
+}
+
+type Queries struct {
+	db                 DBTX
+	tx                 *sql.Tx
+	createSessionStmt  *sql.Stmt
+	deleteSessionStmt  *sql.Stmt
+	getSessionByIDStmt *sql.Stmt
+	listSessionsStmt   *sql.Stmt
+	updateSessionStmt  *sql.Stmt
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db:                 tx,
+		tx:                 tx,
+		createSessionStmt:  q.createSessionStmt,
+		deleteSessionStmt:  q.deleteSessionStmt,
+		getSessionByIDStmt: q.getSessionByIDStmt,
+		listSessionsStmt:   q.listSessionsStmt,
+		updateSessionStmt:  q.updateSessionStmt,
+	}
+}
diff --git a/internal/db/embed.go b/internal/db/embed.go
new file mode 100644
index 000000000..4afa6eafb
--- /dev/null
+++ b/internal/db/embed.go
@@ -0,0 +1,6 @@
+package db
+
+import "embed"
+
+//go:embed migrations/*.sql
+var FS embed.FS
diff --git a/internal/db/migrations/000001_initial.down.sql b/internal/db/migrations/000001_initial.down.sql
new file mode 100644
index 000000000..7324ddb6a
--- /dev/null
+++ b/internal/db/migrations/000001_initial.down.sql
@@ -0,0 +1,4 @@
+-- sqlfluff:dialect:sqlite
+DROP TRIGGER IF EXISTS update_sessions_updated_at;
+
+DROP TABLE IF EXISTS sessions;
diff --git a/internal/db/migrations/000001_initial.up.sql b/internal/db/migrations/000001_initial.up.sql
new file mode 100644
index 000000000..115b6145b
--- /dev/null
+++ b/internal/db/migrations/000001_initial.up.sql
@@ -0,0 +1,17 @@
+-- sqlfluff:dialect:sqlite
+CREATE TABLE IF NOT EXISTS sessions (
+    id TEXT PRIMARY KEY,
+    title TEXT NOT NULL,
+    message_count INTEGER NOT NULL DEFAULT 0 CHECK (message_count >= 0),
+    tokens INTEGER NOT NULL DEFAULT 0 CHECK (tokens >= 0),
+    cost REAL NOT NULL DEFAULT 0.0 CHECK (cost >= 0.0),
+    updated_at INTEGER NOT NULL,  -- Unix timestamp in milliseconds
+    created_at INTEGER NOT NULL   -- Unix timestamp in milliseconds
+);
+
+CREATE TRIGGER IF NOT EXISTS update_sessions_updated_at
+AFTER UPDATE ON sessions
+BEGIN
+UPDATE sessions SET updated_at = strftime('%s', 'now')
+WHERE id = new.id;
+END;
diff --git a/internal/db/models.go b/internal/db/models.go
new file mode 100644
index 000000000..834aaf4dc
--- /dev/null
+++ b/internal/db/models.go
@@ -0,0 +1,15 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+
+package db
+
+type Session struct {
+	ID           string  `json:"id"`
+	Title        string  `json:"title"`
+	MessageCount int64   `json:"message_count"`
+	Tokens       int64   `json:"tokens"`
+	Cost         float64 `json:"cost"`
+	UpdatedAt    int64   `json:"updated_at"`
+	CreatedAt    int64   `json:"created_at"`
+}
diff --git a/internal/db/querier.go b/internal/db/querier.go
new file mode 100644
index 000000000..ac2374cf8
--- /dev/null
+++ b/internal/db/querier.go
@@ -0,0 +1,20 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+
+package db
+
+import (
+	"context"
+)
+
+type Querier interface {
+	// sqlfluff:dialect:sqlite
+	CreateSession(ctx context.Context, arg CreateSessionParams) (Session, error)
+	DeleteSession(ctx context.Context, id string) error
+	GetSessionByID(ctx context.Context, id string) (Session, error)
+	ListSessions(ctx context.Context) ([]Session, error)
+	UpdateSession(ctx context.Context, arg UpdateSessionParams) (Session, error)
+}
+
+var _ Querier = (*Queries)(nil)
diff --git a/internal/db/sessions.sql.go b/internal/db/sessions.sql.go
new file mode 100644
index 000000000..3e0b6eea2
--- /dev/null
+++ b/internal/db/sessions.sql.go
@@ -0,0 +1,165 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.27.0
+// source: sessions.sql
+
+package db
+
+import (
+	"context"
+)
+
+const createSession = `-- name: CreateSession :one
+INSERT INTO sessions (
+    id,
+    title,
+    message_count,
+    tokens,
+    cost,
+    updated_at,
+    created_at
+) VALUES (
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    strftime('%s', 'now'),
+    strftime('%s', 'now')
+) RETURNING id, title, message_count, tokens, cost, updated_at, created_at
+`
+
+type CreateSessionParams struct {
+	ID           string  `json:"id"`
+	Title        string  `json:"title"`
+	MessageCount int64   `json:"message_count"`
+	Tokens       int64   `json:"tokens"`
+	Cost         float64 `json:"cost"`
+}
+
+// sqlfluff:dialect:sqlite
+func (q *Queries) CreateSession(ctx context.Context, arg CreateSessionParams) (Session, error) {
+	row := q.queryRow(ctx, q.createSessionStmt, createSession,
+		arg.ID,
+		arg.Title,
+		arg.MessageCount,
+		arg.Tokens,
+		arg.Cost,
+	)
+	var i Session
+	err := row.Scan(
+		&i.ID,
+		&i.Title,
+		&i.MessageCount,
+		&i.Tokens,
+		&i.Cost,
+		&i.UpdatedAt,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const deleteSession = `-- name: DeleteSession :exec
+DELETE FROM sessions
+WHERE id = ?
+`
+
+func (q *Queries) DeleteSession(ctx context.Context, id string) error {
+	_, err := q.exec(ctx, q.deleteSessionStmt, deleteSession, id)
+	return err
+}
+
+const getSessionByID = `-- name: GetSessionByID :one
+SELECT id, title, message_count, tokens, cost, updated_at, created_at
+FROM sessions
+WHERE id = ? LIMIT 1
+`
+
+func (q *Queries) GetSessionByID(ctx context.Context, id string) (Session, error) {
+	row := q.queryRow(ctx, q.getSessionByIDStmt, getSessionByID, id)
+	var i Session
+	err := row.Scan(
+		&i.ID,
+		&i.Title,
+		&i.MessageCount,
+		&i.Tokens,
+		&i.Cost,
+		&i.UpdatedAt,
+		&i.CreatedAt,
+	)
+	return i, err
+}
+
+const listSessions = `-- name: ListSessions :many
+SELECT id, title, message_count, tokens, cost, updated_at, created_at
+FROM sessions
+ORDER BY created_at DESC
+`
+
+func (q *Queries) ListSessions(ctx context.Context) ([]Session, error) {
+	rows, err := q.query(ctx, q.listSessionsStmt, listSessions)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []Session{}
+	for rows.Next() {
+		var i Session
+		if err := rows.Scan(
+			&i.ID,
+			&i.Title,
+			&i.MessageCount,
+			&i.Tokens,
+			&i.Cost,
+			&i.UpdatedAt,
+			&i.CreatedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const updateSession = `-- name: UpdateSession :one
+UPDATE sessions
+SET
+    title = ?,
+    tokens = ?,
+    cost = ?
+WHERE id = ?
+RETURNING id, title, message_count, tokens, cost, updated_at, created_at
+`
+
+type UpdateSessionParams struct {
+	Title  string  `json:"title"`
+	Tokens int64   `json:"tokens"`
+	Cost   float64 `json:"cost"`
+	ID     string  `json:"id"`
+}
+
+func (q *Queries) UpdateSession(ctx context.Context, arg UpdateSessionParams) (Session, error) {
+	row := q.queryRow(ctx, q.updateSessionStmt, updateSession,
+		arg.Title,
+		arg.Tokens,
+		arg.Cost,
+		arg.ID,
+	)
+	var i Session
+	err := row.Scan(
+		&i.ID,
+		&i.Title,
+		&i.MessageCount,
+		&i.Tokens,
+		&i.Cost,
+		&i.UpdatedAt,
+		&i.CreatedAt,
+	)
+	return i, err
+}
diff --git a/internal/db/sql/sessions.sql b/internal/db/sql/sessions.sql
new file mode 100644
index 000000000..e90e5e328
--- /dev/null
+++ b/internal/db/sql/sessions.sql
@@ -0,0 +1,43 @@
+-- sqlfluff:dialect:sqlite
+-- name: CreateSession :one
+INSERT INTO sessions (
+    id,
+    title,
+    message_count,
+    tokens,
+    cost,
+    updated_at,
+    created_at
+) VALUES (
+    ?,
+    ?,
+    ?,
+    ?,
+    ?,
+    strftime('%s', 'now'),
+    strftime('%s', 'now')
+) RETURNING *;
+
+-- name: GetSessionByID :one
+SELECT *
+FROM sessions
+WHERE id = ? LIMIT 1;
+
+-- name: ListSessions :many
+SELECT *
+FROM sessions
+ORDER BY created_at DESC;
+
+-- name: UpdateSession :one
+UPDATE sessions
+SET
+    title = ?,
+    tokens = ?,
+    cost = ?
+WHERE id = ?
+RETURNING *;
+
+
+-- name: DeleteSession :exec
+DELETE FROM sessions
+WHERE id = ?;