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"},
	}

	for _, entry := range entries {
		// Insert entries directly with specific times for predictable testing
		if _, 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); err != nil {
			t.Fatalf("Failed to create time entry: %v", err)
		}
	}

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