loads of testing

1 files changed, 441 insertions, 0 deletions

diff --git a/internal/database/queries_test.go b/internal/database/queries_test.go
new file mode 100644
index 0000000..435f8b0
--- /dev/null
+++ b/internal/database/queries_test.go
@@ -0,0 +1,441 @@
+package database
+
+import (
+	"context"
+	"database/sql"
+	"fmt"
+	"testing"
+	"time"
+
+	"git.tjp.lol/punchcard/internal/queries"
+	_ "modernc.org/sqlite"
+)
+
+// setupTestDB creates an in-memory SQLite database for testing
+func setupTestDB(t *testing.T) (*queries.Queries, *sql.DB, func()) {
+	db, err := sql.Open("sqlite", ":memory:")
+	if err != nil {
+		t.Fatalf("Failed to open in-memory sqlite db: %v", err)
+	}
+
+	// Initialize with the full schema
+	if err := InitializeDB(db); err != nil {
+		t.Fatalf("Failed to initialize database: %v", err)
+	}
+
+	q := queries.New(db)
+
+	cleanup := func() {
+		if err := db.Close(); err != nil {
+			t.Logf("error closing database: %v", err)
+		}
+	}
+
+	return q, db, cleanup
+}
+
+func TestSQLQueryTimezoneConsistency(t *testing.T) {
+	// Test that SQL queries handle timezone boundaries correctly
+	tests := []struct {
+		name      string
+		setupData func(*sql.DB, int64) error
+		queryTest func(*queries.Queries, int64) error
+	}{
+		{
+			name: "today summary query handles timezone boundaries",
+			setupData: func(db *sql.DB, clientID int64) error {
+				now := time.Now()
+
+				// Insert entries for today, yesterday, and tomorrow in various timezone scenarios
+				entries := []struct {
+					start, end string
+					desc       string
+				}{
+					// Entry clearly in "today" regardless of timezone
+					{now.Add(-2 * time.Hour).Format("2006-01-02 15:04:05"), now.Add(-1 * time.Hour).Format("2006-01-02 15:04:05"), "today_clear"},
+
+					// Entry at the edge of today/yesterday boundary
+					{now.Format("2006-01-02") + " 00:30:00", now.Format("2006-01-02") + " 01:30:00", "today_early"},
+
+					// Entry at the edge of today/tomorrow boundary
+					{now.Format("2006-01-02") + " 22:30:00", now.Format("2006-01-02") + " 23:30:00", "today_late"},
+
+					// Entry from yesterday
+					{now.AddDate(0, 0, -1).Format("2006-01-02") + " 12:00:00", now.AddDate(0, 0, -1).Format("2006-01-02") + " 13:00:00", "yesterday"},
+				}
+
+				for _, entry := range entries {
+					_, err := db.Exec(`
+						INSERT INTO time_entry (start_time, end_time, client_id, description) 
+						VALUES (?, ?, ?, ?)
+					`, entry.start, entry.end, clientID, entry.desc)
+					if err != nil {
+						return err
+					}
+				}
+				return nil
+			},
+			queryTest: func(q *queries.Queries, clientID int64) error {
+				// Test GetTodaySummary - returns total seconds as int64
+				totalSeconds, err := q.GetTodaySummary(context.Background())
+				if err != nil {
+					return err
+				}
+
+				// We should get some seconds from "today" entries
+				// The exact amount depends on the test data, could be 0 if no entries match today
+				if totalSeconds < 0 {
+					t.Errorf("Expected non-negative total seconds, got %d", totalSeconds)
+				}
+
+				t.Logf("GetTodaySummary returned %d total seconds", totalSeconds)
+				return nil
+			},
+		},
+		{
+			name: "week summary query handles week boundaries",
+			setupData: func(db *sql.DB, clientID int64) error {
+				now := time.Now()
+
+				// Calculate this week's Monday
+				weekday := int(now.Weekday())
+				if weekday == 0 {
+					weekday = 7 // Sunday = 7
+				}
+				thisMonday := now.AddDate(0, 0, -(weekday - 1))
+
+				entries := []struct {
+					start, end string
+					desc       string
+				}{
+					// Entry clearly in this week
+					{thisMonday.AddDate(0, 0, 1).Format("2006-01-02") + " 10:00:00", thisMonday.AddDate(0, 0, 1).Format("2006-01-02") + " 11:00:00", "this_week"},
+
+					// Entry from last week
+					{thisMonday.AddDate(0, 0, -2).Format("2006-01-02") + " 10:00:00", thisMonday.AddDate(0, 0, -2).Format("2006-01-02") + " 11:00:00", "last_week"},
+
+					// Entry at the week boundary (this Monday)
+					{thisMonday.Format("2006-01-02") + " 00:30:00", thisMonday.Format("2006-01-02") + " 01:30:00", "week_boundary"},
+				}
+
+				for _, entry := range entries {
+					_, err := db.Exec(`
+						INSERT INTO time_entry (start_time, end_time, client_id, description) 
+						VALUES (?, ?, ?, ?)
+					`, entry.start, entry.end, clientID, entry.desc)
+					if err != nil {
+						return err
+					}
+				}
+				return nil
+			},
+			queryTest: func(q *queries.Queries, clientID int64) error {
+				// Test GetWeekSummaryByProject
+				summaryRows, err := q.GetWeekSummaryByProject(context.Background())
+				if err != nil {
+					return err
+				}
+
+				// Should get entries from this week
+				if len(summaryRows) == 0 {
+					t.Logf("Warning: GetWeekSummaryByProject returned no entries")
+				}
+
+				for _, s := range summaryRows {
+					if s.TotalSeconds <= 0 {
+						t.Errorf("Expected positive duration, got %d", s.TotalSeconds)
+					}
+				}
+
+				return nil
+			},
+		},
+		{
+			name: "mixed timezone modifiers work correctly",
+			setupData: func(db *sql.DB, clientID int64) error {
+				// Test the mixed 'localtime' and 'utc' usage in queries
+				now := time.Now()
+
+				// Insert entries that test the boundary conditions of mixed timezone usage
+				_, err := db.Exec(`
+					INSERT INTO time_entry (start_time, end_time, client_id, description) VALUES 
+					(?, ?, ?, 'boundary_test_1'),
+					(?, ?, ?, 'boundary_test_2')
+				`,
+					now.Format("2006-01-02 15:04:05"), now.Add(time.Hour).Format("2006-01-02 15:04:05"), clientID,
+					now.Add(-24*time.Hour).Format("2006-01-02 15:04:05"), now.Add(-23*time.Hour).Format("2006-01-02 15:04:05"), clientID,
+				)
+				return err
+			},
+			queryTest: func(q *queries.Queries, clientID int64) error {
+				// Test both today and week summaries to ensure consistent timezone handling
+				todayTotal, err := q.GetTodaySummary(context.Background())
+				if err != nil {
+					return err
+				}
+
+				weekSummaryRows, err := q.GetWeekSummaryByProject(context.Background())
+				if err != nil {
+					return err
+				}
+
+				// Calculate week total from summary rows
+				var weekTotal int64
+				for _, s := range weekSummaryRows {
+					weekTotal += s.TotalSeconds
+				}
+
+				// Week total should be >= today total (assuming both have entries)
+				if weekTotal > 0 && todayTotal > weekTotal {
+					t.Errorf("Week total (%d) should be >= today total (%d)", weekTotal, todayTotal)
+				}
+
+				t.Logf("Today total: %d seconds, Week total: %d seconds", todayTotal, weekTotal)
+				return nil
+			},
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, db, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			// Create test client
+			client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+				Name: "TestClient",
+			})
+			if err != nil {
+				t.Fatalf("Failed to create client: %v", err)
+			}
+
+			// Setup test data
+			if err := tt.setupData(db, client.ID); err != nil {
+				t.Fatalf("Failed to setup test data: %v", err)
+			}
+
+			// Run the query test
+			if err := tt.queryTest(q, client.ID); err != nil {
+				t.Fatalf("Query test failed: %v", err)
+			}
+		})
+	}
+}
+
+func TestTimezoneQueryBoundaryEdgeCases(t *testing.T) {
+	// Test specific edge cases around timezone boundaries
+	q, db, cleanup := setupTestDB(t)
+	defer cleanup()
+
+	// Create test client
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name: "EdgeCaseClient",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	// Test case: entry that starts in one day (UTC) but is in another day (local)
+	// This tests the edge case where localtime and UTC disagree about the date
+	now := time.Now()
+
+	// Find a time where local and UTC dates might differ
+	// For example, if it's currently 2 AM local but 8 AM UTC (in a UTC+6 timezone)
+	testTime := time.Date(now.Year(), now.Month(), now.Day(), 1, 0, 0, 0, time.Local) // 1 AM local
+	utcTestTime := testTime.UTC()
+
+	// Insert entry at this boundary time
+	_, err = db.Exec(`
+		INSERT INTO time_entry (start_time, end_time, client_id, description) 
+		VALUES (?, ?, ?, 'boundary_edge_case')
+	`, testTime.Format("2006-01-02 15:04:05"), testTime.Add(time.Hour).Format("2006-01-02 15:04:05"), client.ID)
+	if err != nil {
+		t.Fatalf("Failed to insert test entry: %v", err)
+	}
+
+	t.Logf("Test entry: Local time %s, UTC time %s", testTime.Format("2006-01-02 15:04:05"), utcTestTime.Format("2006-01-02 15:04:05"))
+
+	// Test today summary
+	todaySummary, err := q.GetTodaySummary(context.Background())
+	if err != nil {
+		// GetTodaySummary might fail if there are no entries for today (returns NULL)
+		t.Logf("GetTodaySummary failed (likely no entries for today): %v", err)
+		return
+	}
+
+	// The entry should appear in today's summary if the query uses localtime correctly
+	// Note: GetTodaySummary returns total seconds, so we just check if it's positive
+	found := todaySummary > 0
+
+	// Log result for manual verification - exact behavior depends on current timezone
+	t.Logf("Today summary %s entry for boundary case (local: %s, UTC: %s, total seconds: %d)",
+		map[bool]string{true: "found", false: "did not find"}[found],
+		testTime.Format("2006-01-02"), utcTestTime.Format("2006-01-02"), todaySummary)
+}
+
+func TestSQLiteTimezoneFunction(t *testing.T) {
+	// Test that SQLite's timezone functions work as expected
+	_, db, cleanup := setupTestDB(t)
+	defer cleanup()
+
+	tests := []struct {
+		name     string
+		query    string
+		expected string // Expected format or pattern
+	}{
+		{
+			name:     "datetime now UTC",
+			query:    "SELECT datetime('now', 'utc')",
+			expected: "UTC timestamp format",
+		},
+		{
+			name:     "datetime now localtime",
+			query:    "SELECT datetime('now', 'localtime')",
+			expected: "Local timestamp format",
+		},
+		{
+			name:     "date conversion from UTC to localtime",
+			query:    "SELECT date('2024-08-22 12:00:00', 'localtime')",
+			expected: "Date format",
+		},
+		{
+			name:     "weekday calculation",
+			query:    "SELECT date('now', 'localtime', 'weekday 0', '-6 days')",
+			expected: "Monday of this week",
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			var result string
+			err := db.QueryRow(tt.query).Scan(&result)
+			if err != nil {
+				t.Fatalf("Query failed: %v", err)
+			}
+
+			// Verify result is not empty and has reasonable format
+			if result == "" {
+				t.Errorf("Expected non-empty result for %s", tt.name)
+			}
+
+			t.Logf("%s result: %s", tt.name, result)
+
+			// Basic format validation
+			switch tt.name {
+			case "datetime now UTC", "datetime now localtime":
+				// Should be YYYY-MM-DD HH:MM:SS format
+				if _, err := time.Parse("2006-01-02 15:04:05", result); err != nil {
+					t.Errorf("Result %s not in expected datetime format: %v", result, err)
+				}
+			case "date conversion from UTC to localtime":
+				// Should be YYYY-MM-DD format
+				if _, err := time.Parse("2006-01-02", result); err != nil {
+					t.Errorf("Result %s not in expected date format: %v", result, err)
+				}
+			case "weekday calculation":
+				// Should be a date, and should be a Monday
+				date, err := time.Parse("2006-01-02", result)
+				if err != nil {
+					t.Errorf("Result %s not in expected date format: %v", result, err)
+				} else if date.Weekday() != time.Monday {
+					t.Errorf("Expected Monday, got %v for result %s", date.Weekday(), result)
+				}
+			}
+		})
+	}
+}
+
+func TestQueryResultTimezoneConsistency(t *testing.T) {
+	// Test that queries return timestamps in a consistent format
+	q, db, cleanup := setupTestDB(t)
+	defer cleanup()
+
+	// Create test data
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name: "ConsistencyTestClient",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	// Insert entry with known UTC time
+	utcTime := "2024-08-22 14:30:00"
+	_, err = db.Exec(`
+		INSERT INTO time_entry (start_time, end_time, client_id, description) 
+		VALUES (?, ?, ?, 'consistency_test')
+	`, utcTime, "2024-08-22 16:30:00", client.ID)
+	if err != nil {
+		t.Fatalf("Failed to insert test entry: %v", err)
+	}
+
+	// Test different query methods return consistent timestamp formats
+	tests := []struct {
+		name      string
+		queryFunc func() (time.Time, error)
+	}{
+		{
+			name: "GetMostRecentTimeEntry",
+			queryFunc: func() (time.Time, error) {
+				entry, err := q.GetMostRecentTimeEntry(context.Background())
+				if err != nil {
+					return time.Time{}, err
+				}
+				return entry.StartTime, nil
+			},
+		},
+		{
+			name: "Direct SQL query",
+			queryFunc: func() (time.Time, error) {
+				var startTime string
+				err := db.QueryRow("SELECT start_time FROM time_entry ORDER BY id DESC LIMIT 1").Scan(&startTime)
+				if err != nil {
+					return time.Time{}, err
+				}
+				// Parse as stored format - try different formats since SQLite might return different formats
+				formats := []string{
+					"2006-01-02 15:04:05",  // Standard format
+					"2006-01-02T15:04:05Z", // ISO format with Z
+					"2006-01-02T15:04:05",  // ISO format without Z
+					time.RFC3339,           // RFC3339 format
+				}
+				for _, format := range formats {
+					if t, err := time.Parse(format, startTime); err == nil {
+						return t, nil
+					}
+				}
+				return time.Time{}, fmt.Errorf("unable to parse time %q with any known format", startTime)
+			},
+		},
+	}
+
+	var timestamps []time.Time
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			timestamp, err := tt.queryFunc()
+			if err != nil {
+				t.Fatalf("Query failed: %v", err)
+			}
+
+			timestamps = append(timestamps, timestamp)
+
+			// Verify timestamp is not zero
+			if timestamp.IsZero() {
+				t.Errorf("Got zero timestamp from %s", tt.name)
+			}
+
+			t.Logf("%s returned: %s (location: %v)", tt.name, timestamp.Format("2006-01-02 15:04:05"), timestamp.Location())
+		})
+	}
+
+	// Verify all queries returned equivalent times (accounting for timezone differences)
+	if len(timestamps) >= 2 {
+		first := timestamps[0].UTC()
+		for i, ts := range timestamps[1:] {
+			second := ts.UTC()
+			if !first.Equal(second) {
+				t.Errorf("Timestamp inconsistency: query 0 returned %v, query %d returned %v (both in UTC)", first, i+1, second)
+			}
+		}
+	}
+}
+