path: root/internal/database/queries_test.go

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)
			}
		}
	}
}