timesheet and unified reports

1 files changed, 562 insertions, 0 deletions

diff --git a/internal/reports/unified_test.go b/internal/reports/unified_test.go
new file mode 100644
index 0000000..64d0b3f
--- /dev/null
+++ b/internal/reports/unified_test.go
@@ -0,0 +1,562 @@
+package reports
+
+import (
+	"database/sql"
+	"testing"
+	"time"
+
+	"punchcard/internal/queries"
+)
+
+
+func TestGenerateUnifiedReportData(t *testing.T) {
+	tests := []struct {
+		name            string
+		entries         interface{}
+		clientID        int64
+		clientName      string
+		projectName     string
+		contractor      queries.Contractor
+		invoiceNumber   int64
+		dateRange       DateRange
+		timezone        *time.Location
+		wantEntries     int
+		wantHours       float64
+		wantTotalAmount float64
+		wantError       bool
+	}{
+		{
+			name: "client entries with UTC timezone",
+			entries: []queries.GetInvoiceDataByClientRow{
+				{
+					TimeEntryID:     1,
+					StartTime:       mustParseTime("2025-07-10T14:55:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-10T18:05:00Z"), Valid: true},
+					Description:     sql.NullString{String: "GL closing", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+					ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+					DurationSeconds: 11400, // 3:10
+					EntryBillableRate:   sql.NullInt64{Int64: 150, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 150, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 150, Valid: true},
+					RateSource:          "entry",
+				},
+				{
+					TimeEntryID:     2,
+					StartTime:       mustParseTime("2025-07-10T18:42:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-10T20:04:00Z"), Valid: true},
+					Description:     sql.NullString{String: "GL closing", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+					ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+					DurationSeconds: 4920, // 1:22
+					EntryBillableRate:   sql.NullInt64{Int64: 150, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 150, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 150, Valid: true},
+					RateSource:          "entry",
+				},
+			},
+			clientID:   1,
+			clientName: "Test Client",
+			contractor: queries.Contractor{
+				Name:  "Travis Parker",
+				Label: "Software Development",
+				Email: "travis@example.com",
+			},
+			invoiceNumber:   123,
+			dateRange: DateRange{
+				Start: mustParseTime("2025-07-01T00:00:00Z"),
+				End:   mustParseTime("2025-07-31T23:59:59Z"),
+			},
+			timezone:        time.UTC,
+			wantEntries:     1, // Both entries have same rate so grouped together
+			wantHours:       4.5333, // 16320 seconds / 3600
+			wantTotalAmount: 6.80,   // 4.5333 * 1.50
+		},
+		{
+			name: "project entries with local timezone",
+			entries: []queries.GetInvoiceDataByProjectRow{
+				{
+					TimeEntryID:     3,
+					StartTime:       mustParseTime("2025-07-11T13:55:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-11T18:35:00Z"), Valid: true},
+					Description:     sql.NullString{String: "Development work", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       1,
+					ProjectName:     "Test Project",
+					DurationSeconds:     16800, // 4:40
+					EntryBillableRate:   sql.NullInt64{Int64: 125, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 125, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 125, Valid: true},
+					RateSource:          "entry",
+				},
+			},
+			clientID:    1,
+			clientName:  "Test Client",
+			projectName: "Test Project",
+			contractor: queries.Contractor{
+				Name:  "Travis Parker",
+				Label: "Software Development",
+				Email: "travis@example.com",
+			},
+			invoiceNumber: 124,
+			dateRange: DateRange{
+				Start: mustParseTime("2025-07-01T00:00:00Z"),
+				End:   mustParseTime("2025-07-31T23:59:59Z"),
+			},
+			timezone:        time.Local,
+			wantEntries:     1,
+			wantHours:       4.6667, // 16800 seconds / 3600
+			wantTotalAmount: 5.83,   // 4.6667 * 1.25
+		},
+		{
+			name: "entries with different timezone",
+			entries: []queries.GetInvoiceDataByClientRow{
+				{
+					TimeEntryID:     4,
+					StartTime:       mustParseTime("2025-07-15T00:09:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-15T00:13:00Z"), Valid: true},
+					Description:     sql.NullString{String: "Quick fix", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+					ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+					DurationSeconds: 240, // 4 minutes
+					EntryBillableRate:   sql.NullInt64{Int64: 200, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 200, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 200, Valid: true},
+					RateSource:          "entry",
+				},
+			},
+			clientID:   1,
+			clientName: "Test Client",
+			contractor: queries.Contractor{
+				Name:  "Travis Parker",
+				Label: "Software Development",
+				Email: "travis@example.com",
+			},
+			invoiceNumber: 125,
+			dateRange: DateRange{
+				Start: mustParseTime("2025-07-01T00:00:00Z"),
+				End:   mustParseTime("2025-07-31T23:59:59Z"),
+			},
+			timezone:        mustLoadLocation("America/New_York"),
+			wantEntries:     1,
+			wantHours:       0.0667, // 240 seconds / 3600
+			wantTotalAmount: 0.13,   // 0.0667 * 2.00
+		},
+		{
+			name:      "unsupported entry type",
+			entries:   "invalid",
+			wantError: true,
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			result, err := GenerateUnifiedReportData(
+				tt.entries,
+				tt.clientID,
+				tt.clientName,
+				tt.projectName,
+				tt.contractor,
+				tt.invoiceNumber,
+				tt.dateRange,
+				tt.timezone,
+			)
+
+			if tt.wantError {
+				if err == nil {
+					t.Errorf("GenerateUnifiedReportData() expected error but got none")
+				}
+				return
+			}
+
+			if err != nil {
+				t.Errorf("GenerateUnifiedReportData() error = %v", err)
+				return
+			}
+
+			if result == nil {
+				t.Errorf("GenerateUnifiedReportData() returned nil result")
+				return
+			}
+
+			// Test invoice data
+			if result.InvoiceData == nil {
+				t.Errorf("GenerateUnifiedReportData() invoice data is nil")
+				return
+			}
+
+			if len(result.InvoiceData.LineItems) != tt.wantEntries {
+				t.Errorf("GenerateUnifiedReportData() invoice entries count = %d, want %d", len(result.InvoiceData.LineItems), tt.wantEntries)
+			}
+
+			// Check invoice total hours (with tolerance for floating point precision)
+			if abs(result.InvoiceData.TotalHours-tt.wantHours) > 0.001 {
+				t.Errorf("GenerateUnifiedReportData() invoice total hours = %f, want %f", result.InvoiceData.TotalHours, tt.wantHours)
+			}
+
+			// Check invoice total amount (with tolerance for floating point precision)
+			if abs(result.InvoiceData.TotalAmount-tt.wantTotalAmount) > 0.01 {
+				t.Errorf("GenerateUnifiedReportData() invoice total amount = %f, want %f", result.InvoiceData.TotalAmount, tt.wantTotalAmount)
+			}
+
+			// Check invoice basic fields
+			if result.InvoiceData.ClientName != tt.clientName {
+				t.Errorf("GenerateUnifiedReportData() invoice client name = %s, want %s", result.InvoiceData.ClientName, tt.clientName)
+			}
+			if result.InvoiceData.ProjectName != tt.projectName {
+				t.Errorf("GenerateUnifiedReportData() invoice project name = %s, want %s", result.InvoiceData.ProjectName, tt.projectName)
+			}
+			if result.InvoiceData.InvoiceNumber != tt.invoiceNumber {
+				t.Errorf("GenerateUnifiedReportData() invoice number = %d, want %d", result.InvoiceData.InvoiceNumber, tt.invoiceNumber)
+			}
+
+			// Test timesheet data
+			if result.TimesheetData == nil {
+				t.Errorf("GenerateUnifiedReportData() timesheet data is nil")
+				return
+			}
+
+			// For timesheet, we expect individual entries, not grouped like invoice
+			expectedTimesheetEntries := tt.wantEntries
+			if tt.name == "client entries with UTC timezone" {
+				expectedTimesheetEntries = 2 // Individual timesheet entries
+			}
+			if len(result.TimesheetData.Entries) != expectedTimesheetEntries {
+				t.Errorf("GenerateUnifiedReportData() timesheet entries count = %d, want %d", len(result.TimesheetData.Entries), expectedTimesheetEntries)
+			}
+
+			// Check timesheet total hours (with tolerance for floating point precision)
+			if abs(result.TimesheetData.TotalHours-tt.wantHours) > 0.001 {
+				t.Errorf("GenerateUnifiedReportData() timesheet total hours = %f, want %f", result.TimesheetData.TotalHours, tt.wantHours)
+			}
+
+			// Check timesheet basic fields
+			if result.TimesheetData.ClientID != tt.clientID {
+				t.Errorf("GenerateUnifiedReportData() timesheet client ID = %d, want %d", result.TimesheetData.ClientID, tt.clientID)
+			}
+			if result.TimesheetData.ClientName != tt.clientName {
+				t.Errorf("GenerateUnifiedReportData() timesheet client name = %s, want %s", result.TimesheetData.ClientName, tt.clientName)
+			}
+			if result.TimesheetData.ProjectName != tt.projectName {
+				t.Errorf("GenerateUnifiedReportData() timesheet project name = %s, want %s", result.TimesheetData.ProjectName, tt.projectName)
+			}
+
+			// Check timezone handling
+			if tt.timezone != nil {
+				expectedTimezone := tt.timezone.String()
+				if expectedTimezone == "Local" {
+					zone, _ := time.Now().Zone()
+					expectedTimezone = zone
+				}
+				if result.TimesheetData.Timezone != expectedTimezone {
+					t.Errorf("GenerateUnifiedReportData() timesheet timezone = %s, want %s", result.TimesheetData.Timezone, expectedTimezone)
+				}
+			}
+
+			// Check contractor data consistency between invoice and timesheet
+			if result.InvoiceData.ContractorName != result.TimesheetData.ContractorName {
+				t.Errorf("GenerateUnifiedReportData() contractor name mismatch: invoice=%s, timesheet=%s", result.InvoiceData.ContractorName, result.TimesheetData.ContractorName)
+			}
+			if result.InvoiceData.ContractorLabel != result.TimesheetData.ContractorLabel {
+				t.Errorf("GenerateUnifiedReportData() contractor label mismatch: invoice=%s, timesheet=%s", result.InvoiceData.ContractorLabel, result.TimesheetData.ContractorLabel)
+			}
+			if result.InvoiceData.ContractorEmail != result.TimesheetData.ContractorEmail {
+				t.Errorf("GenerateUnifiedReportData() contractor email mismatch: invoice=%s, timesheet=%s", result.InvoiceData.ContractorEmail, result.TimesheetData.ContractorEmail)
+			}
+		})
+	}
+}
+
+func TestUnifiedReportDataConsistency(t *testing.T) {
+	// Test that unified report produces consistent data between invoice and timesheet components
+	entries := []queries.GetInvoiceDataByClientRow{
+		{
+			TimeEntryID:     1,
+			StartTime:       mustParseTime("2025-07-10T14:55:00Z"),
+			EndTime:         sql.NullTime{Time: mustParseTime("2025-07-10T18:05:00Z"), Valid: true},
+			Description:     sql.NullString{String: "Development work", Valid: true},
+			ClientID:        1,
+			ClientName:      "Test Client",
+			ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+			ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+			DurationSeconds:     11400, // 3:10
+			EntryBillableRate:   sql.NullInt64{Int64: 150, Valid: true},
+			ClientBillableRate:  sql.NullInt64{Int64: 150, Valid: true},
+			ProjectBillableRate: sql.NullInt64{Int64: 150, Valid: true},
+			RateSource:          "entry",
+		},
+		{
+			TimeEntryID:     2,
+			StartTime:       mustParseTime("2025-07-10T18:42:00Z"),
+			EndTime:         sql.NullTime{Time: mustParseTime("2025-07-10T20:04:00Z"), Valid: true},
+			Description:     sql.NullString{String: "Code review", Valid: true},
+			ClientID:        1,
+			ClientName:      "Test Client",
+			ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+			ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+			DurationSeconds:     4920, // 1:22
+			EntryBillableRate:   sql.NullInt64{Int64: 150, Valid: true},
+			ClientBillableRate:  sql.NullInt64{Int64: 150, Valid: true},
+			ProjectBillableRate: sql.NullInt64{Int64: 150, Valid: true},
+			RateSource:          "entry",
+		},
+	}
+
+	contractor := queries.Contractor{
+		Name:  "Travis Parker",
+		Label: "Software Development",
+		Email: "travis@example.com",
+	}
+
+	dateRange := DateRange{
+		Start: mustParseTime("2025-07-01T00:00:00Z"),
+		End:   mustParseTime("2025-07-31T23:59:59Z"),
+	}
+
+	result, err := GenerateUnifiedReportData(
+		entries,
+		1,
+		"Test Client",
+		"Test Project",
+		contractor,
+		123,
+		dateRange,
+		time.UTC,
+	)
+
+	if err != nil {
+		t.Fatalf("GenerateUnifiedReportData() error = %v", err)
+	}
+
+	// Note: Invoice entries are grouped by rate, timesheet entries remain individual
+	// So we don't expect the counts to be equal - invoice will have 1 grouped item, timesheet will have 2 individual entries
+	if len(result.InvoiceData.LineItems) != 1 {
+		t.Errorf("Invoice line items count = %d, want 1 (grouped)", len(result.InvoiceData.LineItems))
+	}
+	if len(result.TimesheetData.Entries) != 2 {
+		t.Errorf("Timesheet entries count = %d, want 2 (individual)", len(result.TimesheetData.Entries))
+	}
+
+	// Verify total hours consistency
+	if abs(result.InvoiceData.TotalHours-result.TimesheetData.TotalHours) > 0.001 {
+		t.Errorf("Total hours mismatch: invoice=%f, timesheet=%f", result.InvoiceData.TotalHours, result.TimesheetData.TotalHours)
+	}
+
+	// Verify date range consistency
+	if !result.InvoiceData.DateRange.Start.Equal(result.TimesheetData.DateRange.Start) {
+		t.Errorf("Date range start mismatch: invoice=%v, timesheet=%v", result.InvoiceData.DateRange.Start, result.TimesheetData.DateRange.Start)
+	}
+	if !result.InvoiceData.DateRange.End.Equal(result.TimesheetData.DateRange.End) {
+		t.Errorf("Date range end mismatch: invoice=%v, timesheet=%v", result.InvoiceData.DateRange.End, result.TimesheetData.DateRange.End)
+	}
+
+	// Verify client information consistency
+	if result.InvoiceData.ClientName != result.TimesheetData.ClientName {
+		t.Errorf("Client name mismatch: invoice=%s, timesheet=%s", result.InvoiceData.ClientName, result.TimesheetData.ClientName)
+	}
+
+	// Verify project information consistency
+	if result.InvoiceData.ProjectName != result.TimesheetData.ProjectName {
+		t.Errorf("Project name mismatch: invoice=%s, timesheet=%s", result.InvoiceData.ProjectName, result.TimesheetData.ProjectName)
+	}
+
+	// Verify generation date consistency (should be the same day, allowing for small time differences)
+	invoiceGenTime := result.InvoiceData.GeneratedDate
+	timesheetGenTime := result.TimesheetData.GeneratedDate
+	if invoiceGenTime.Format("2006-01-02") != timesheetGenTime.Format("2006-01-02") {
+		t.Errorf("Generation date mismatch: invoice=%s, timesheet=%s", invoiceGenTime.Format("2006-01-02"), timesheetGenTime.Format("2006-01-02"))
+	}
+}
+
+func TestUnifiedReportEntryTypeConversion(t *testing.T) {
+	tests := []struct {
+		name        string
+		entries     interface{}
+		expectError bool
+	}{
+		{
+			name: "client entries conversion",
+			entries: []queries.GetInvoiceDataByClientRow{
+				{
+					TimeEntryID:     1,
+					StartTime:       mustParseTime("2025-07-10T14:55:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-10T18:05:00Z"), Valid: true},
+					Description:     sql.NullString{String: "Work", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       sql.NullInt64{Int64: 1, Valid: true},
+					ProjectName:     sql.NullString{String: "Test Project", Valid: true},
+					DurationSeconds: 11400,
+					EntryBillableRate:   sql.NullInt64{Int64: 150, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 150, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 150, Valid: true},
+					RateSource:          "entry",
+				},
+			},
+			expectError: false,
+		},
+		{
+			name: "project entries conversion",
+			entries: []queries.GetInvoiceDataByProjectRow{
+				{
+					TimeEntryID:     2,
+					StartTime:       mustParseTime("2025-07-11T13:55:00Z"),
+					EndTime:         sql.NullTime{Time: mustParseTime("2025-07-11T18:35:00Z"), Valid: true},
+					Description:     sql.NullString{String: "Work", Valid: true},
+					ClientID:        1,
+					ClientName:      "Test Client",
+					ProjectID:       1,
+					ProjectName:     "Test Project",
+					DurationSeconds:     16800,
+					EntryBillableRate:   sql.NullInt64{Int64: 125, Valid: true},
+					ClientBillableRate:  sql.NullInt64{Int64: 125, Valid: true},
+					ProjectBillableRate: sql.NullInt64{Int64: 125, Valid: true},
+					RateSource:          "entry",
+				},
+			},
+			expectError: false,
+		},
+		{
+			name: "unsupported entry type",
+			entries: []struct {
+				ID   int
+				Name string
+			}{
+				{ID: 1, Name: "Invalid"},
+			},
+			expectError: true,
+		},
+		{
+			name:        "nil entries",
+			entries:     nil,
+			expectError: true,
+		},
+		{
+			name:        "string entries",
+			entries:     "invalid",
+			expectError: true,
+		},
+	}
+
+	contractor := queries.Contractor{
+		Name:  "Travis Parker",
+		Label: "Software Development",
+		Email: "travis@example.com",
+	}
+
+	dateRange := DateRange{
+		Start: mustParseTime("2025-07-01T00:00:00Z"),
+		End:   mustParseTime("2025-07-31T23:59:59Z"),
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			result, err := GenerateUnifiedReportData(
+				tt.entries,
+				1,
+				"Test Client",
+				"Test Project",
+				contractor,
+				123,
+				dateRange,
+				time.UTC,
+			)
+
+			if tt.expectError {
+				if err == nil {
+					t.Errorf("GenerateUnifiedReportData() expected error but got none")
+				}
+				if result != nil {
+					t.Errorf("GenerateUnifiedReportData() expected nil result on error, got %v", result)
+				}
+			} else {
+				if err != nil {
+					t.Errorf("GenerateUnifiedReportData() unexpected error = %v", err)
+				}
+				if result == nil {
+					t.Errorf("GenerateUnifiedReportData() expected result but got nil")
+				}
+			}
+		})
+	}
+}
+
+func TestUnifiedReportEmptyEntries(t *testing.T) {
+	contractor := queries.Contractor{
+		Name:  "Travis Parker",
+		Label: "Software Development",
+		Email: "travis@example.com",
+	}
+
+	dateRange := DateRange{
+		Start: mustParseTime("2025-07-01T00:00:00Z"),
+		End:   mustParseTime("2025-07-31T23:59:59Z"),
+	}
+
+	tests := []struct {
+		name    string
+		entries interface{}
+	}{
+		{
+			name:    "empty client entries",
+			entries: []queries.GetInvoiceDataByClientRow{},
+		},
+		{
+			name:    "empty project entries",
+			entries: []queries.GetInvoiceDataByProjectRow{},
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			result, err := GenerateUnifiedReportData(
+				tt.entries,
+				1,
+				"Test Client",
+				"Test Project",
+				contractor,
+				123,
+				dateRange,
+				time.UTC,
+			)
+
+			if err != nil {
+				t.Errorf("GenerateUnifiedReportData() error = %v", err)
+				return
+			}
+
+			if result == nil {
+				t.Errorf("GenerateUnifiedReportData() returned nil result")
+				return
+			}
+
+			// Both invoice and timesheet should have zero entries
+			if len(result.InvoiceData.LineItems) != 0 {
+				t.Errorf("GenerateUnifiedReportData() invoice entries count = %d, want 0", len(result.InvoiceData.LineItems))
+			}
+			if len(result.TimesheetData.Entries) != 0 {
+				t.Errorf("GenerateUnifiedReportData() timesheet entries count = %d, want 0", len(result.TimesheetData.Entries))
+			}
+
+			// Both should have zero total hours
+			if result.InvoiceData.TotalHours != 0 {
+				t.Errorf("GenerateUnifiedReportData() invoice total hours = %f, want 0", result.InvoiceData.TotalHours)
+			}
+			if result.TimesheetData.TotalHours != 0 {
+				t.Errorf("GenerateUnifiedReportData() timesheet total hours = %f, want 0", result.TimesheetData.TotalHours)
+			}
+
+			// Invoice should have zero total amount
+			if result.InvoiceData.TotalAmount != 0 {
+				t.Errorf("GenerateUnifiedReportData() invoice total amount = %f, want 0", result.InvoiceData.TotalAmount)
+			}
+		})
+	}
+}
\ No newline at end of file