urit/internal/store/sqlite_test.go

package store

import (
	"os"
	"path/filepath"
	"testing"
	"time"

	"github.com/urit/urit/internal/models"
)

// testStore creates a temporary SQLite store for testing.
// The database is removed after the test completes.
func testStore(t *testing.T) *SQLiteStore {
	t.Helper()
	dir := t.TempDir()
	path := filepath.Join(dir, "test.db")

	s, err := OpenSQLite(path)
	if err != nil {
		t.Fatalf("OpenSQLite: %v", err)
	}
	t.Cleanup(func() { s.Close() })

	return s
}

// --- User tests ---

func TestUserCRUD(t *testing.T) {
	s := testStore(t)

	// Create
	user := &models.User{
		Name:         "TestUser",
		PasswordHash: "$2a$10$fakehash",
		Comments:     "Test account",
		Active:       true,
		SecStatus:    2,
		SecBoard:     2,
		SecLibrary:   2,
		SecBulletin:  2,
		TimeLimit:    3600,
		LastOn:       timePtr(time.Now()),
	}

	if err := s.CreateUser(user); err != nil {
		t.Fatalf("CreateUser: %v", err)
	}
	if user.ID == 0 {
		t.Fatal("CreateUser did not set ID")
	}

	// Read by ID
	got, err := s.GetUser(user.ID)
	if err != nil {
		t.Fatalf("GetUser: %v", err)
	}
	if got == nil {
		t.Fatal("GetUser returned nil")
	}
	if got.Name != "TestUser" {
		t.Errorf("Name = %q, want %q", got.Name, "TestUser")
	}
	if got.SecStatus != 2 {
		t.Errorf("SecStatus = %d, want 2", got.SecStatus)
	}

	// Read by name (case-insensitive)
	got, err = s.GetUserByName("testuser")
	if err != nil {
		t.Fatalf("GetUserByName: %v", err)
	}
	if got == nil {
		t.Fatal("GetUserByName returned nil for case-insensitive match")
	}
	if got.ID != user.ID {
		t.Errorf("ID = %d, want %d", got.ID, user.ID)
	}

	// Read nonexistent
	got, err = s.GetUser(9999)
	if err != nil {
		t.Fatalf("GetUser(9999): %v", err)
	}
	if got != nil {
		t.Error("GetUser(9999) should return nil")
	}

	// Update
	user.SecStatus = 255
	user.MessagesPosted = 42
	if err := s.UpdateUser(user); err != nil {
		t.Fatalf("UpdateUser: %v", err)
	}
	got, _ = s.GetUser(user.ID)
	if got.SecStatus != 255 {
		t.Errorf("SecStatus after update = %d, want 255", got.SecStatus)
	}
	if got.MessagesPosted != 42 {
		t.Errorf("MessagesPosted = %d, want 42", got.MessagesPosted)
	}

	// List
	users, err := s.ListUsers(0, 100)
	if err != nil {
		t.Fatalf("ListUsers: %v", err)
	}
	if len(users) != 1 {
		t.Errorf("ListUsers returned %d users, want 1", len(users))
	}

	// Count
	count, err := s.CountUsers()
	if err != nil {
		t.Fatalf("CountUsers: %v", err)
	}
	if count != 1 {
		t.Errorf("CountUsers = %d, want 1", count)
	}

	// Delete (soft)
	if err := s.DeleteUser(user.ID); err != nil {
		t.Fatalf("DeleteUser: %v", err)
	}
	count, _ = s.CountUsers()
	if count != 0 {
		t.Errorf("CountUsers after delete = %d, want 0", count)
	}
	// Verify the record still exists but is inactive
	got, _ = s.GetUser(user.ID)
	if got == nil {
		t.Fatal("Soft-deleted user should still be readable by ID")
	}
	if got.Active {
		t.Error("Soft-deleted user should have Active=false")
	}
}

func TestUserDuplicateName(t *testing.T) {
	s := testStore(t)

	user1 := &models.User{Name: "Sysop", Active: true}
	if err := s.CreateUser(user1); err != nil {
		t.Fatalf("CreateUser 1: %v", err)
	}

	user2 := &models.User{Name: "Sysop", Active: true}
	err := s.CreateUser(user2)
	if err == nil {
		t.Fatal("Expected error for duplicate name, got nil")
	}
}

// --- Board and Message tests ---

func TestBoardAndMessages(t *testing.T) {
	s := testStore(t)

	// Create board
	board := &models.Board{
		Name:      "General",
		ReadLow:   0,
		ReadHigh:  255,
		WriteLow:  1,
		WriteHigh: 255,
		MaxPosts:  100,
	}
	if err := s.CreateBoard(board); err != nil {
		t.Fatalf("CreateBoard: %v", err)
	}
	if board.ID == 0 {
		t.Fatal("CreateBoard did not set ID")
	}

	// List boards
	boards, err := s.ListBoards()
	if err != nil {
		t.Fatalf("ListBoards: %v", err)
	}
	if len(boards) != 1 {
		t.Errorf("ListBoards returned %d, want 1", len(boards))
	}

	// Create messages
	msg1 := &models.Message{
		BoardID:  board.ID,
		Title:    "First Post",
		Author:   "Sysop",
		AuthorID: 1,
		Body:     "Welcome to the BBS!",
	}
	if err := s.CreateMessage(msg1); err != nil {
		t.Fatalf("CreateMessage 1: %v", err)
	}
	if msg1.Number != 1 {
		t.Errorf("First message number = %d, want 1", msg1.Number)
	}

	msg2 := &models.Message{
		BoardID:  board.ID,
		Title:    "Reply",
		Author:   "User",
		AuthorID: 2,
		Body:     "Thanks!",
		ReplyTo:  msg1.ID,
	}
	if err := s.CreateMessage(msg2); err != nil {
		t.Fatalf("CreateMessage 2: %v", err)
	}
	if msg2.Number != 2 {
		t.Errorf("Second message number = %d, want 2", msg2.Number)
	}

	// Verify board post_count updated
	got, _ := s.GetBoard(board.ID)
	if got.PostCount != 2 {
		t.Errorf("Board PostCount = %d, want 2", got.PostCount)
	}

	// List messages
	msgs, err := s.ListMessages(board.ID, 0, 50)
	if err != nil {
		t.Fatalf("ListMessages: %v", err)
	}
	if len(msgs) != 2 {
		t.Errorf("ListMessages returned %d, want 2", len(msgs))
	}
	if msgs[0].Title != "First Post" {
		t.Errorf("First msg title = %q", msgs[0].Title)
	}
	if msgs[1].ReplyTo != msg1.ID {
		t.Errorf("Reply message ReplyTo = %d, want %d", msgs[1].ReplyTo, msg1.ID)
	}

	// Count messages
	count, _ := s.CountMessages(board.ID)
	if count != 2 {
		t.Errorf("CountMessages = %d, want 2", count)
	}

	// Delete message
	if err := s.DeleteMessage(msg1.ID); err != nil {
		t.Fatalf("DeleteMessage: %v", err)
	}
	count, _ = s.CountMessages(board.ID)
	if count != 1 {
		t.Errorf("CountMessages after delete = %d, want 1", count)
	}

	// Delete board (cascades to messages)
	if err := s.DeleteBoard(board.ID); err != nil {
		t.Fatalf("DeleteBoard: %v", err)
	}
	count, _ = s.CountMessages(board.ID)
	if count != 0 {
		t.Errorf("Messages should be cascade-deleted with board")
	}
}

// --- Mail tests ---

func TestMail(t *testing.T) {
	s := testStore(t)

	mail := &models.Mail{
		Title:     "Hello",
		Author:    "Alice",
		FromID:    1,
		ToID:      2,
		Recipient: "Bob",
		Body:      "How's it going?",
	}
	if err := s.CreateMail(mail); err != nil {
		t.Fatalf("CreateMail: %v", err)
	}

	// Count unread
	count, _ := s.CountUnreadMail(2)
	if count != 1 {
		t.Errorf("Unread mail for Bob = %d, want 1", count)
	}

	// List mail
	mails, err := s.ListMailFor(2)
	if err != nil {
		t.Fatalf("ListMailFor: %v", err)
	}
	if len(mails) != 1 {
		t.Fatalf("ListMailFor returned %d, want 1", len(mails))
	}
	if mails[0].Read {
		t.Error("New mail should be unread")
	}

	// Mark read
	if err := s.MarkMailRead(mail.ID); err != nil {
		t.Fatalf("MarkMailRead: %v", err)
	}
	count, _ = s.CountUnreadMail(2)
	if count != 0 {
		t.Errorf("Unread after marking = %d, want 0", count)
	}

	// Delete
	if err := s.DeleteMail(mail.ID); err != nil {
		t.Fatalf("DeleteMail: %v", err)
	}
	got, _ := s.GetMail(mail.ID)
	if got != nil {
		t.Error("Deleted mail should be nil")
	}
}

// --- Library tests ---

func TestLibraryAndFiles(t *testing.T) {
	s := testStore(t)

	lib := &models.Library{
		Name:         "Downloads",
		FilePath:     "/data/files/downloads",
		UploadLow:    2,
		UploadHigh:   255,
		DownloadLow:  0,
		DownloadHigh: 255,
		MaxFiles:     50,
	}
	if err := s.CreateLibrary(lib); err != nil {
		t.Fatalf("CreateLibrary: %v", err)
	}

	// Create file entry
	file := &models.LibraryFile{
		LibraryID:   lib.ID,
		Filename:    "readme.txt",
		Description: "Read this first",
		UploaderID:  1,
		Uploader:    "Sysop",
		FileSize:    1024,
	}
	if err := s.CreateLibraryFile(file); err != nil {
		t.Fatalf("CreateLibraryFile: %v", err)
	}

	// Verify library file_count updated
	got, _ := s.GetLibrary(lib.ID)
	if got.FileCount != 1 {
		t.Errorf("Library FileCount = %d, want 1", got.FileCount)
	}

	// List files
	files, err := s.ListLibraryFiles(lib.ID, 0, 50)
	if err != nil {
		t.Fatalf("ListLibraryFiles: %v", err)
	}
	if len(files) != 1 {
		t.Errorf("ListLibraryFiles returned %d, want 1", len(files))
	}

	// Delete file
	if err := s.DeleteLibraryFile(file.ID); err != nil {
		t.Fatalf("DeleteLibraryFile: %v", err)
	}
	got, _ = s.GetLibrary(lib.ID)
	if got.FileCount != 0 {
		t.Errorf("FileCount after delete = %d, want 0", got.FileCount)
	}
}

// --- Bulletin tests ---

func TestBulletins(t *testing.T) {
	s := testStore(t)

	b := &models.Bulletin{
		Name:     "System Rules",
		FilePath: "./screens/rules.ans",
		ReadLow:  0,
		ReadHigh: 255,
	}
	if err := s.CreateBulletin(b); err != nil {
		t.Fatalf("CreateBulletin: %v", err)
	}

	bulletins, err := s.ListBulletins()
	if err != nil {
		t.Fatalf("ListBulletins: %v", err)
	}
	if len(bulletins) != 1 {
		t.Errorf("ListBulletins returned %d, want 1", len(bulletins))
	}
	if bulletins[0].Name != "System Rules" {
		t.Errorf("Name = %q, want %q", bulletins[0].Name, "System Rules")
	}

	if err := s.DeleteBulletin(b.ID); err != nil {
		t.Fatalf("DeleteBulletin: %v", err)
	}
	bulletins, _ = s.ListBulletins()
	if len(bulletins) != 0 {
		t.Error("Bulletins should be empty after delete")
	}
}

// --- Schema test ---

func TestOpenSQLiteCreatesDirectory(t *testing.T) {
	dir := t.TempDir()
	path := filepath.Join(dir, "subdir", "deep", "test.db")

	s, err := OpenSQLite(path)
	if err != nil {
		t.Fatalf("OpenSQLite with nested path: %v", err)
	}
	s.Close()

	if _, err := os.Stat(path); os.IsNotExist(err) {
		t.Fatal("Database file was not created")
	}
}

func timePtr(t time.Time) *time.Time { return &t }