loads of testing

1 files changed, 503 insertions, 0 deletions

diff --git a/internal/integration/timezone_test.go b/internal/integration/timezone_test.go
new file mode 100644
index 0000000..695fb04
--- /dev/null
+++ b/internal/integration/timezone_test.go
@@ -0,0 +1,503 @@
+package integration
+
+import (
+	"context"
+	"database/sql"
+	"testing"
+	"time"
+
+	"git.tjp.lol/punchcard/internal/database"
+	"git.tjp.lol/punchcard/internal/queries"
+	"git.tjp.lol/punchcard/internal/reports"
+	_ "modernc.org/sqlite"
+)
+
+// setupIntegrationDB creates a full database setup for integration testing
+func setupIntegrationDB(t *testing.T) (*queries.Queries, func()) {
+	db, err := sql.Open("sqlite", ":memory:")
+	if err != nil {
+		t.Fatalf("Failed to open in-memory sqlite db: %v", err)
+	}
+
+	if err := database.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, cleanup
+}
+
+func TestEndToEndTimezoneWorkflow(t *testing.T) {
+	// Integration test: complete workflow from punch in/out to report generation
+	// with timezone consistency verification
+
+	q, cleanup := setupIntegrationDB(t)
+	defer cleanup()
+
+	// Setup: Create client and contractor
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name:  "E2E Test Client",
+		Email: sql.NullString{String: "test@client.com", Valid: true},
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	contractor, err := q.CreateContractor(context.Background(), queries.CreateContractorParams{
+		Name:  "E2E Contractor",
+		Label: "Software Development",
+		Email: "contractor@example.com",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create contractor: %v", err)
+	}
+
+	// Step 1: Create time entries with specific times for predictable testing
+	// Use a known time range to make verification easier
+	baseTime := time.Date(2024, time.August, 22, 14, 0, 0, 0, time.UTC)
+
+	// Create time entries that span different timezone scenarios
+	entries := []struct {
+		start, end time.Time
+		desc       string
+	}{
+		{baseTime, baseTime.Add(2 * time.Hour), "Morning work"},
+		{baseTime.Add(4 * time.Hour), baseTime.Add(6 * time.Hour), "Afternoon work"},
+		{baseTime.AddDate(0, 0, 1), baseTime.AddDate(0, 0, 1).Add(3 * time.Hour), "Next day work"},
+	}
+
+	var entryIDs []int64
+	for _, entry := range entries {
+		// Insert entries directly with specific times for predictable testing
+		result, err := q.DBTX().(*sql.DB).Exec(`
+			INSERT INTO time_entry (start_time, end_time, description, client_id) 
+			VALUES (?, ?, ?, ?)
+		`, entry.start.Format("2006-01-02 15:04:05"), entry.end.Format("2006-01-02 15:04:05"), entry.desc, client.ID)
+		if err != nil {
+			t.Fatalf("Failed to create time entry: %v", err)
+		}
+
+		entryID, err := result.LastInsertId()
+		if err != nil {
+			t.Fatalf("Failed to get entry ID: %v", err)
+		}
+
+		entryIDs = append(entryIDs, entryID)
+	}
+
+	// Step 2: Test report generation in different timezones
+	testTimezones := []*time.Location{
+		time.UTC,
+		time.Local,
+	}
+
+	// Try to load some interesting timezones for testing
+	extraTimezones := []string{
+		"America/New_York",
+		"America/Los_Angeles",
+		"Asia/Tokyo",
+		"Europe/London",
+	}
+
+	for _, tzName := range extraTimezones {
+		if tz, err := time.LoadLocation(tzName); err == nil {
+			testTimezones = append(testTimezones, tz)
+		} else {
+			t.Logf("Skipping timezone %s: %v", tzName, err)
+		}
+	}
+
+	for _, tz := range testTimezones {
+		t.Run("timezone_"+tz.String(), func(t *testing.T) {
+			// Test timesheet generation
+			dateRange := reports.DateRange{
+				Start: baseTime.AddDate(0, 0, -1), // Day before to capture all entries
+				End:   baseTime.AddDate(0, 0, 2),  // Day after to capture all entries
+			}
+
+			// Generate timesheet data
+			timesheetEntries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
+				ClientID:  client.ID,
+				StartTime: dateRange.Start,
+				EndTime:   dateRange.End,
+			})
+			if err != nil {
+				t.Fatalf("Failed to get timesheet data: %v", err)
+			}
+
+			if len(timesheetEntries) == 0 {
+				t.Fatalf("No timesheet entries found - date filtering may be incorrect")
+			}
+
+			timesheetData, err := reports.GenerateTimesheetData(
+				timesheetEntries,
+				client.ID,
+				client.Name,
+				"",
+				contractor,
+				dateRange,
+				tz,
+			)
+			if err != nil {
+				t.Fatalf("Failed to generate timesheet data: %v", err)
+			}
+
+			// Verify timezone consistency
+			if len(timesheetData.Entries) != len(entries) {
+				t.Errorf("Expected %d entries, got %d in timezone %s", len(entries), len(timesheetData.Entries), tz)
+			}
+
+			// Verify that total hours calculation is consistent regardless of timezone
+			expectedTotalHours := 7.0 // 2 + 2 + 3 hours from our test data
+			if timesheetData.TotalHours != expectedTotalHours {
+				t.Errorf("Expected total hours %v, got %v in timezone %s", expectedTotalHours, timesheetData.TotalHours, tz)
+			}
+
+			// Verify timezone field is set correctly
+			expectedTimezoneStr := tz.String()
+			if expectedTimezoneStr == "Local" {
+				zone, _ := time.Now().Zone()
+				expectedTimezoneStr = zone
+			}
+
+			if timesheetData.Timezone != expectedTimezoneStr {
+				t.Errorf("Expected timezone %s, got %s", expectedTimezoneStr, timesheetData.Timezone)
+			}
+
+			// Verify individual entry times are converted to the target timezone
+			for i, entry := range timesheetData.Entries {
+				// Verify date is in the target timezone context
+				originalStart := entries[i].start
+				expectedDate := originalStart.In(tz).Format("2006-01-02")
+
+				if entry.Date != expectedDate {
+					t.Errorf("Entry %d: expected date %s, got %s (timezone %s)", i, expectedDate, entry.Date, tz)
+				}
+
+				t.Logf("Timezone %s: Entry %d on %s (%s-%s) = %v hours",
+					tz, i, entry.Date, entry.StartTime, entry.EndTime, entry.Hours)
+			}
+		})
+	}
+}
+
+func TestCrossTImezoneReportConsistency(t *testing.T) {
+	// Test that the same data produces consistent reports across different timezones
+	// (with appropriate timezone conversions for display)
+
+	q, cleanup := setupIntegrationDB(t)
+	defer cleanup()
+
+	// Setup test data
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name: "Consistency Test Client",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	contractor, err := q.CreateContractor(context.Background(), queries.CreateContractorParams{
+		Name:  "Test Contractor",
+		Label: "Development",
+		Email: "test@example.com",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create contractor: %v", err)
+	}
+
+	// Create a time entry that spans multiple hours for clear testing
+	startTime := time.Date(2024, time.August, 22, 14, 30, 0, 0, time.UTC) // 2:30 PM UTC
+	endTime := startTime.Add(3*time.Hour + 30*time.Minute)                // 6:00 PM UTC
+
+	_, err = q.DBTX().(*sql.DB).Exec(`
+		INSERT INTO time_entry (start_time, end_time, client_id, description) 
+		VALUES (?, ?, ?, 'Cross-timezone test work')
+	`, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"), client.ID)
+	if err != nil {
+		t.Fatalf("Failed to create test entry: %v", err)
+	}
+
+	// Test in multiple timezones
+	timezones := []*time.Location{time.UTC}
+	tzNames := []string{"America/New_York", "Asia/Tokyo", "Australia/Sydney"}
+
+	for _, tzName := range tzNames {
+		if tz, err := time.LoadLocation(tzName); err == nil {
+			timezones = append(timezones, tz)
+		}
+	}
+
+	dateRange := reports.DateRange{
+		Start: startTime.AddDate(0, 0, -1),
+		End:   startTime.AddDate(0, 0, 1),
+	}
+
+	var timesheetDatas []*reports.TimesheetData
+	for _, tz := range timezones {
+		// Get entries for this timezone
+		entries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
+			ClientID:  client.ID,
+			StartTime: dateRange.Start,
+			EndTime:   dateRange.End,
+		})
+		if err != nil {
+			t.Fatalf("Failed to get entries for timezone %s: %v", tz, err)
+		}
+
+		// Generate timesheet data
+		data, err := reports.GenerateTimesheetData(entries, client.ID, client.Name, "", contractor, dateRange, tz)
+		if err != nil {
+			t.Fatalf("Failed to generate timesheet data for timezone %s: %v", tz, err)
+		}
+
+		timesheetDatas = append(timesheetDatas, data)
+	}
+
+	// Verify consistency across timezones
+	if len(timesheetDatas) < 2 {
+		t.Skip("Need at least 2 timezones for consistency testing")
+	}
+
+	firstData := timesheetDatas[0]
+	for i, data := range timesheetDatas[1:] {
+		// Total hours should be the same regardless of timezone
+		if data.TotalHours != firstData.TotalHours {
+			t.Errorf("Timezone %d: total hours %v != baseline %v", i+1, data.TotalHours, firstData.TotalHours)
+		}
+
+		// Should have same number of entries
+		if len(data.Entries) != len(firstData.Entries) {
+			t.Errorf("Timezone %d: entry count %d != baseline %d", i+1, len(data.Entries), len(firstData.Entries))
+		}
+
+		// Each entry should have same duration (hours)
+		for j, entry := range data.Entries {
+			if j < len(firstData.Entries) {
+				if entry.Hours != firstData.Entries[j].Hours {
+					t.Errorf("Timezone %d, entry %d: hours %v != baseline %v", i+1, j, entry.Hours, firstData.Entries[j].Hours)
+				}
+			}
+		}
+
+		t.Logf("Timezone %s: %d entries, %.2f total hours", data.Timezone, len(data.Entries), data.TotalHours)
+	}
+}
+
+func TestTimezoneFilteringEdgeCases(t *testing.T) {
+	// Test edge cases where timezone differences could affect filtering
+	q, cleanup := setupIntegrationDB(t)
+	defer cleanup()
+
+	// Create test client
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name: "Edge Case Client",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	// Test scenario: entry that occurs on different calendar dates in different timezones
+	// e.g., 11 PM Pacific on July 31 = 6 AM UTC on August 1
+
+	// Create entry at midnight UTC (edge of day boundary)
+	edgeTime := time.Date(2024, time.August, 1, 0, 30, 0, 0, time.UTC) // 12:30 AM UTC on Aug 1
+
+	_, err = q.DBTX().(*sql.DB).Exec(`
+		INSERT INTO time_entry (start_time, end_time, client_id, description) 
+		VALUES (?, ?, ?, 'Timezone edge case work')
+	`, edgeTime.Format("2006-01-02 15:04:05"), edgeTime.Add(time.Hour).Format("2006-01-02 15:04:05"), client.ID)
+	if err != nil {
+		t.Fatalf("Failed to create edge case entry: %v", err)
+	}
+
+	// Test filtering with different timezone contexts
+	testCases := []struct {
+		name       string
+		timezone   string
+		expectFind bool // Whether we expect to find the entry in July or August reports
+		monthYear  string
+	}{
+		{"UTC August", "UTC", true, "august 2024"},
+		{"UTC July", "UTC", false, "july 2024"},
+		// Note: The actual filtering is done at database level using UTC dates,
+		// not timezone-aware dates. So the entry at UTC 2024-08-01 00:30:00
+		// will appear in August reports regardless of target timezone
+		{"Pacific July", "America/Los_Angeles", false, "july 2024"},    // UTC date is August, not July
+		{"Pacific August", "America/Los_Angeles", true, "august 2024"}, // UTC date is August
+	}
+
+	for _, tc := range testCases {
+		t.Run(tc.name, func(t *testing.T) {
+			var tz *time.Location
+			if tc.timezone == "UTC" {
+				tz = time.UTC
+			} else {
+				var err error
+				tz, err = time.LoadLocation(tc.timezone)
+				if err != nil {
+					t.Skipf("Timezone %s not available: %v", tc.timezone, err)
+				}
+			}
+
+			// Parse the month range
+			dateRange, err := reports.ParseDateRange(tc.monthYear)
+			if err != nil {
+				t.Fatalf("Failed to parse date range %s: %v", tc.monthYear, err)
+			}
+
+			// Get entries for this month and timezone
+			entries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
+				ClientID:  client.ID,
+				StartTime: dateRange.Start,
+				EndTime:   dateRange.End,
+			})
+			if err != nil {
+				t.Fatalf("Failed to get entries: %v", err)
+			}
+
+			found := len(entries) > 0
+
+			if found != tc.expectFind {
+				t.Errorf("Expected to find entry: %v, actually found: %v (timezone: %s, month: %s)",
+					tc.expectFind, found, tc.timezone, tc.monthYear)
+
+				// Log the actual time in both timezones for debugging
+				utcTime := edgeTime
+				localTime := edgeTime.In(tz)
+				t.Logf("Entry time: UTC=%s, %s=%s",
+					utcTime.Format("2006-01-02 15:04:05"),
+					tc.timezone,
+					localTime.Format("2006-01-02 15:04:05"))
+			}
+		})
+	}
+}
+
+func TestReportGenerationTimezoneAccuracy(t *testing.T) {
+	// Verify that report generation maintains accuracy across timezone conversions
+	q, cleanup := setupIntegrationDB(t)
+	defer cleanup()
+
+	// Setup comprehensive test data
+	client, err := q.CreateClient(context.Background(), queries.CreateClientParams{
+		Name:         "Accuracy Test Client",
+		BillableRate: sql.NullInt64{Int64: 15000, Valid: true}, // $150.00/hour
+	})
+	if err != nil {
+		t.Fatalf("Failed to create client: %v", err)
+	}
+
+	contractor, err := q.CreateContractor(context.Background(), queries.CreateContractorParams{
+		Name:  "Accuracy Contractor",
+		Label: "Testing",
+		Email: "accuracy@test.com",
+	})
+	if err != nil {
+		t.Fatalf("Failed to create contractor: %v", err)
+	}
+
+	// Create entries with precise timing for accurate verification
+	preciseEntries := []struct {
+		start    time.Time
+		duration time.Duration
+		desc     string
+	}{
+		{time.Date(2024, time.August, 22, 9, 0, 0, 0, time.UTC), 2*time.Hour + 30*time.Minute, "Morning session"},
+		{time.Date(2024, time.August, 22, 13, 15, 0, 0, time.UTC), 1*time.Hour + 45*time.Minute, "Afternoon session"},
+		{time.Date(2024, time.August, 23, 10, 30, 0, 0, time.UTC), 3 * time.Hour, "Next day session"},
+	}
+
+	var totalExpectedSeconds int64
+	for _, entry := range preciseEntries {
+		endTime := entry.start.Add(entry.duration)
+		totalExpectedSeconds += int64(entry.duration.Seconds())
+
+		_, err = q.DBTX().(*sql.DB).Exec(`
+			INSERT INTO time_entry (start_time, end_time, client_id, description) 
+			VALUES (?, ?, ?, ?)
+		`, entry.start.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"), client.ID, entry.desc)
+		if err != nil {
+			t.Fatalf("Failed to create precision entry: %v", err)
+		}
+	}
+
+	expectedTotalHours := float64(totalExpectedSeconds) / 3600.0
+
+	// Test accuracy across different timezones
+	testTimezones := []string{"UTC", "America/New_York", "Asia/Tokyo"}
+
+	for _, tzName := range testTimezones {
+		t.Run("accuracy_"+tzName, func(t *testing.T) {
+			var tz *time.Location
+			if tzName == "UTC" {
+				tz = time.UTC
+			} else {
+				var err error
+				tz, err = time.LoadLocation(tzName)
+				if err != nil {
+					t.Skipf("Timezone %s not available: %v", tzName, err)
+				}
+			}
+
+			// Generate timesheet
+			dateRange := reports.DateRange{
+				Start: time.Date(2024, time.August, 22, 0, 0, 0, 0, time.UTC),
+				End:   time.Date(2024, time.August, 23, 23, 59, 59, 999999999, time.UTC),
+			}
+
+			entries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
+				ClientID:  client.ID,
+				StartTime: dateRange.Start,
+				EndTime:   dateRange.End,
+			})
+			if err != nil {
+				t.Fatalf("Failed to get entries: %v", err)
+			}
+
+			timesheetData, err := reports.GenerateTimesheetData(entries, client.ID, client.Name, "", contractor, dateRange, tz)
+			if err != nil {
+				t.Fatalf("Failed to generate timesheet: %v", err)
+			}
+
+			// Verify total hours accuracy (within small tolerance for rounding)
+			tolerance := 0.001
+			if abs(timesheetData.TotalHours-expectedTotalHours) > tolerance {
+				t.Errorf("Total hours accuracy error in timezone %s: expected %.6f, got %.6f (diff: %.6f)",
+					tzName, expectedTotalHours, timesheetData.TotalHours, abs(timesheetData.TotalHours-expectedTotalHours))
+			}
+
+			// Verify individual entry accuracy
+			if len(timesheetData.Entries) != len(preciseEntries) {
+				t.Errorf("Entry count mismatch in timezone %s: expected %d, got %d", tzName, len(preciseEntries), len(timesheetData.Entries))
+			}
+
+			for i, entry := range timesheetData.Entries {
+				if i < len(preciseEntries) {
+					expectedHours := preciseEntries[i].duration.Hours()
+					if abs(entry.Hours-expectedHours) > tolerance {
+						t.Errorf("Entry %d hours accuracy error in timezone %s: expected %.6f, got %.6f",
+							i, tzName, expectedHours, entry.Hours)
+					}
+				}
+			}
+
+			t.Logf("Timezone %s: %.6f hours total (expected %.6f)", tzName, timesheetData.TotalHours, expectedTotalHours)
+		})
+	}
+}
+
+// Helper function for floating point comparison
+func abs(x float64) float64 {
+	if x < 0 {
+		return -x
+	}
+	return x
+}
+