feat: SQLite schema migrations, repository interfaces, and concrete SQLite implementations with :memory: table-driven tests (task l9)

1 files changed, 58 insertions, 0 deletions

diff --git a/internal/repository/session.go b/internal/repository/session.go
new file mode 100644
index 0000000..a1f3dca
--- /dev/null
+++ b/internal/repository/session.go
@@ -0,0 +1,58 @@
+package repository
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"time"
+
+	"github.com/paul/kiss-media-player/internal/model"
+)
+
+// CreateSession inserts a new session.
+func (s *SQLite) CreateSession(ctx context.Context, session *model.Session) error {
+	_, err := s.db.ExecContext(ctx,
+		`INSERT INTO sessions (id, user_id, expires_at, created_at) VALUES (?, ?, ?, ?)`,
+		session.ID, session.UserID, session.ExpiresAt, session.CreatedAt,
+	)
+	if err != nil {
+		return fmt.Errorf("insert session: %w", err)
+	}
+	return nil
+}
+
+// GetSessionByID retrieves a session by ID.
+func (s *SQLite) GetSessionByID(ctx context.Context, id string) (*model.Session, error) {
+	row := s.db.QueryRowContext(ctx,
+		`SELECT id, user_id, expires_at, created_at FROM sessions WHERE id = ?`, id)
+	var sess model.Session
+	if err := row.Scan(&sess.ID, &sess.UserID, &sess.ExpiresAt, &sess.CreatedAt); err != nil {
+		return nil, err
+	}
+	return &sess, nil
+}
+
+// DeleteSession removes a session by ID.
+func (s *SQLite) DeleteSession(ctx context.Context, id string) error {
+	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE id = ?`, id)
+	if err != nil {
+		return fmt.Errorf("delete session: %w", err)
+	}
+	return nil
+}
+
+// DeleteExpiredSessions removes all sessions with expires_at older than now.
+func (s *SQLite) DeleteExpiredSessions(ctx context.Context, now time.Time) error {
+	_, err := s.db.ExecContext(ctx, `DELETE FROM sessions WHERE expires_at < ?`, now)
+	if err != nil {
+		return fmt.Errorf("delete expired sessions: %w", err)
+	}
+	return nil
+}
+
+func sqlNullTime(t *time.Time) sql.NullTime {
+	if t == nil {
+		return sql.NullTime{}
+	}
+	return sql.NullTime{Time: *t, Valid: true}
+}