Support for archiving clients and projects.HEADmain

1 files changed, 612 insertions, 0 deletions

diff --git a/internal/reports/archive_reports_test.go b/internal/reports/archive_reports_test.go
new file mode 100644
index 0000000..d300031
--- /dev/null
+++ b/internal/reports/archive_reports_test.go
@@ -0,0 +1,612 @@
+package reports
+
+import (
+	"context"
+	"database/sql"
+	"testing"
+	"time"
+
+	"git.tjp.lol/punchcard/internal/database"
+	"git.tjp.lol/punchcard/internal/queries"
+)
+
+func setupTestDB(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 in-memory sqlite db: %v", err)
+	}
+	q := queries.New(db)
+
+	cleanup := func() {
+		if err := q.DBTX().(*sql.DB).Close(); err != nil {
+			t.Logf("error closing database: %v", err)
+		}
+	}
+
+	return q, cleanup
+}
+
+func TestGetInvoiceDataByClientWithArchivedClient(t *testing.T) {
+	tests := []struct {
+		name              string
+		setupData         func(*queries.Queries) (clientID int64)
+		expectEntries     int
+		expectTotalAmount float64
+	}{
+		{
+			name: "archived client time entries appear in invoice",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name:         "ArchivedClient",
+					BillableRate: sql.NullInt64{Int64: 10000, Valid: true},
+				})
+
+				startTime := time.Now().UTC().Add(-2 * time.Hour)
+				endTime := time.Now().UTC()
+
+				_, err := q.DBTX().(*sql.DB).Exec(
+					"INSERT INTO time_entry (client_id, start_time, end_time) VALUES (?, ?, ?)",
+					client.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+				)
+				if err != nil {
+					t.Fatalf("Failed to create time entry: %v", err)
+				}
+
+				_ = q.ArchiveClient(context.Background(), client.ID)
+
+				return client.ID
+			},
+			expectEntries:     1,
+			expectTotalAmount: 200.0,
+		},
+		{
+			name: "multiple time entries for archived client all appear",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name:         "MultiEntryClient",
+					BillableRate: sql.NullInt64{Int64: 15000, Valid: true},
+				})
+
+				for i := 0; i < 3; i++ {
+					startTime := time.Now().UTC().Add(-time.Duration(i+2) * time.Hour)
+					endTime := time.Now().UTC().Add(-time.Duration(i+1) * time.Hour)
+
+					_, err := q.DBTX().(*sql.DB).Exec(
+						"INSERT INTO time_entry (client_id, start_time, end_time) VALUES (?, ?, ?)",
+						client.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+					)
+					if err != nil {
+						t.Fatalf("Failed to create time entry: %v", err)
+					}
+				}
+
+				_ = q.ArchiveClient(context.Background(), client.ID)
+
+				return client.ID
+			},
+			expectEntries:     3,
+			expectTotalAmount: 450.0,
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			clientID := tt.setupData(q)
+
+			startTime := time.Now().UTC().Add(-7 * 24 * time.Hour)
+			endTime := time.Now().UTC()
+
+			entries, err := q.GetInvoiceDataByClient(context.Background(), queries.GetInvoiceDataByClientParams{
+				ClientID:  clientID,
+				StartTime: startTime,
+				EndTime:   endTime,
+			})
+
+			if err != nil {
+				t.Fatalf("Failed to get invoice data: %v", err)
+			}
+
+			if len(entries) != tt.expectEntries {
+				t.Errorf("Expected %d entries, got %d", tt.expectEntries, len(entries))
+			}
+
+			if len(entries) > 0 {
+				contractor := queries.Contractor{
+					Name:  "Test Contractor",
+					Label: "Contractor",
+					Email: "test@example.com",
+				}
+
+				invoiceData, err := GenerateInvoiceData(
+					entries,
+					clientID,
+					"TestClient",
+					"",
+					contractor,
+					1,
+					DateRange{Start: startTime, End: endTime},
+				)
+
+				if err != nil {
+					t.Fatalf("Failed to generate invoice data: %v", err)
+				}
+
+				if invoiceData.TotalAmount != tt.expectTotalAmount {
+					t.Errorf("Expected total amount %.2f, got %.2f", tt.expectTotalAmount, invoiceData.TotalAmount)
+				}
+			}
+		})
+	}
+}
+
+func TestGetInvoiceDataByProjectWithArchivedProject(t *testing.T) {
+	tests := []struct {
+		name              string
+		setupData         func(*queries.Queries) (projectID int64)
+		expectEntries     int
+		expectTotalAmount float64
+	}{
+		{
+			name: "archived project time entries appear in invoice",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "TestClient",
+				})
+				project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{
+					Name:         "ArchivedProject",
+					ClientID:     client.ID,
+					BillableRate: sql.NullInt64{Int64: 12500, Valid: true},
+				})
+
+				baseTime := time.Now().UTC()
+				startTime := baseTime.Add(-2 * time.Hour)
+				endTime := baseTime
+
+				_, err := q.DBTX().(*sql.DB).Exec(
+					"INSERT INTO time_entry (client_id, project_id, start_time, end_time) VALUES (?, ?, ?, ?)",
+					client.ID, project.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+				)
+				if err != nil {
+					t.Fatalf("Failed to create time entry: %v", err)
+				}
+
+				_ = q.ArchiveProject(context.Background(), project.ID)
+
+				return project.ID
+			},
+			expectEntries:     1,
+			expectTotalAmount: 250.0,
+		},
+		{
+			name: "multiple time entries for archived project all appear",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "TestClient",
+				})
+				project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{
+					Name:         "MultiEntryProject",
+					ClientID:     client.ID,
+					BillableRate: sql.NullInt64{Int64: 20000, Valid: true},
+				})
+
+				baseTime := time.Now().UTC()
+				for i := 0; i < 2; i++ {
+					startTime := baseTime.Add(-time.Duration(i+2) * time.Hour)
+					endTime := baseTime.Add(-time.Duration(i+1) * time.Hour)
+
+					_, err := q.DBTX().(*sql.DB).Exec(
+						"INSERT INTO time_entry (client_id, project_id, start_time, end_time) VALUES (?, ?, ?, ?)",
+						client.ID, project.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+					)
+					if err != nil {
+						t.Fatalf("Failed to create time entry: %v", err)
+					}
+				}
+
+				_ = q.ArchiveProject(context.Background(), project.ID)
+
+				return project.ID
+			},
+			expectEntries:     2,
+			expectTotalAmount: 396.66,
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			projectID := tt.setupData(q)
+
+			startTime := time.Now().UTC().Add(-7 * 24 * time.Hour)
+			endTime := time.Now().UTC()
+
+			entries, err := q.GetInvoiceDataByProject(context.Background(), queries.GetInvoiceDataByProjectParams{
+				ProjectID: projectID,
+				StartTime: startTime,
+				EndTime:   endTime,
+			})
+
+			if err != nil {
+				t.Fatalf("Failed to get invoice data: %v", err)
+			}
+
+			if len(entries) != tt.expectEntries {
+				t.Errorf("Expected %d entries, got %d", tt.expectEntries, len(entries))
+			}
+
+			if len(entries) > 0 {
+				contractor := queries.Contractor{
+					Name:  "Test Contractor",
+					Label: "Contractor",
+					Email: "test@example.com",
+				}
+
+				invoiceData, err := GenerateInvoiceData(
+					entries,
+					entries[0].ClientID,
+					"TestClient",
+					"ArchivedProject",
+					contractor,
+					1,
+					DateRange{Start: startTime, End: endTime},
+				)
+
+				if err != nil {
+					t.Fatalf("Failed to generate invoice data: %v", err)
+				}
+
+				if invoiceData.TotalAmount != tt.expectTotalAmount {
+					t.Errorf("Expected total amount %.2f, got %.2f", tt.expectTotalAmount, invoiceData.TotalAmount)
+				}
+			}
+		})
+	}
+}
+
+func TestGetTimesheetDataByClientWithArchivedClient(t *testing.T) {
+	tests := []struct {
+		name           string
+		setupData      func(*queries.Queries) (clientID int64)
+		expectEntries  int
+		expectTotalHrs float64
+	}{
+		{
+			name: "archived client time entries appear in timesheet",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "ArchivedTimesheetClient",
+				})
+
+				startTime := time.Now().UTC().Add(-2 * time.Hour)
+				endTime := time.Now().UTC()
+
+				_, err := q.DBTX().(*sql.DB).Exec(
+					"INSERT INTO time_entry (client_id, start_time, end_time) VALUES (?, ?, ?)",
+					client.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+				)
+				if err != nil {
+					t.Fatalf("Failed to create time entry: %v", err)
+				}
+
+				_ = q.ArchiveClient(context.Background(), client.ID)
+
+				return client.ID
+			},
+			expectEntries:  1,
+			expectTotalHrs: 2.0,
+		},
+		{
+			name: "multiple time entries for archived client all appear in timesheet",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "MultiEntryTimesheetClient",
+				})
+
+				for i := 0; i < 3; i++ {
+					startTime := time.Now().UTC().Add(-time.Duration(i+2) * time.Hour)
+					endTime := time.Now().UTC().Add(-time.Duration(i+1) * time.Hour)
+
+					_, err := q.DBTX().(*sql.DB).Exec(
+						"INSERT INTO time_entry (client_id, start_time, end_time) VALUES (?, ?, ?)",
+						client.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+					)
+					if err != nil {
+						t.Fatalf("Failed to create time entry: %v", err)
+					}
+				}
+
+				_ = q.ArchiveClient(context.Background(), client.ID)
+
+				return client.ID
+			},
+			expectEntries:  3,
+			expectTotalHrs: 3.0,
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			clientID := tt.setupData(q)
+
+			startTime := time.Now().UTC().Add(-7 * 24 * time.Hour)
+			endTime := time.Now().UTC()
+
+			entries, err := q.GetTimesheetDataByClient(context.Background(), queries.GetTimesheetDataByClientParams{
+				ClientID:  clientID,
+				StartTime: startTime,
+				EndTime:   endTime,
+			})
+
+			if err != nil {
+				t.Fatalf("Failed to get timesheet data: %v", err)
+			}
+
+			if len(entries) != tt.expectEntries {
+				t.Errorf("Expected %d entries, got %d", tt.expectEntries, len(entries))
+			}
+
+			if len(entries) > 0 {
+				contractor := queries.Contractor{
+					Name:  "Test Contractor",
+					Label: "Contractor",
+					Email: "test@example.com",
+				}
+
+				timesheetData, err := GenerateTimesheetData(
+					entries,
+					clientID,
+					"TestClient",
+					"",
+					contractor,
+					DateRange{Start: startTime, End: endTime},
+					time.UTC,
+				)
+
+				if err != nil {
+					t.Fatalf("Failed to generate timesheet data: %v", err)
+				}
+
+				if timesheetData.TotalHours != tt.expectTotalHrs {
+					t.Errorf("Expected total hours %.2f, got %.2f", tt.expectTotalHrs, timesheetData.TotalHours)
+				}
+			}
+		})
+	}
+}
+
+func TestGetTimesheetDataByProjectWithArchivedProject(t *testing.T) {
+	tests := []struct {
+		name           string
+		setupData      func(*queries.Queries) (projectID int64)
+		expectEntries  int
+		expectTotalHrs float64
+	}{
+		{
+			name: "archived project time entries appear in timesheet",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "TestClient",
+				})
+				project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{
+					Name:     "ArchivedTimesheetProject",
+					ClientID: client.ID,
+				})
+
+				startTime := time.Now().UTC().Add(-2 * time.Hour)
+				endTime := time.Now().UTC()
+
+				_, err := q.DBTX().(*sql.DB).Exec(
+					"INSERT INTO time_entry (client_id, project_id, start_time, end_time) VALUES (?, ?, ?, ?)",
+					client.ID, project.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+				)
+				if err != nil {
+					t.Fatalf("Failed to create time entry: %v", err)
+				}
+
+				_ = q.ArchiveProject(context.Background(), project.ID)
+
+				return project.ID
+			},
+			expectEntries:  1,
+			expectTotalHrs: 2.0,
+		},
+		{
+			name: "multiple time entries for archived project all appear in timesheet",
+			setupData: func(q *queries.Queries) int64 {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name: "TestClient",
+				})
+				project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{
+					Name:     "MultiEntryTimesheetProject",
+					ClientID: client.ID,
+				})
+
+				for i := 0; i < 2; i++ {
+					startTime := time.Now().UTC().Add(-time.Duration(i+2) * time.Hour)
+					endTime := time.Now().UTC().Add(-time.Duration(i+1) * time.Hour)
+
+					_, err := q.DBTX().(*sql.DB).Exec(
+						"INSERT INTO time_entry (client_id, project_id, start_time, end_time) VALUES (?, ?, ?, ?)",
+						client.ID, project.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+					)
+					if err != nil {
+						t.Fatalf("Failed to create time entry: %v", err)
+					}
+				}
+
+				_ = q.ArchiveProject(context.Background(), project.ID)
+
+				return project.ID
+			},
+			expectEntries:  2,
+			expectTotalHrs: 2.0,
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			projectID := tt.setupData(q)
+
+			startTime := time.Now().UTC().Add(-7 * 24 * time.Hour)
+			endTime := time.Now().UTC()
+
+			entries, err := q.GetTimesheetDataByProject(context.Background(), queries.GetTimesheetDataByProjectParams{
+				ProjectID: projectID,
+				StartTime: startTime,
+				EndTime:   endTime,
+			})
+
+			if err != nil {
+				t.Fatalf("Failed to get timesheet data: %v", err)
+			}
+
+			t.Logf("Got %d entries, expected %d", len(entries), tt.expectEntries)
+			if len(entries) != tt.expectEntries {
+				t.Errorf("Expected %d entries, got %d", tt.expectEntries, len(entries))
+				return
+			}
+
+			if len(entries) > 0 {
+				contractor := queries.Contractor{
+					Name:  "Test Contractor",
+					Label: "Contractor",
+					Email: "test@example.com",
+				}
+
+				timesheetData, err := GenerateTimesheetData(
+					entries,
+					entries[0].ClientID,
+					"TestClient",
+					"ArchivedTimesheetProject",
+					contractor,
+					DateRange{Start: startTime, End: endTime},
+					time.UTC,
+				)
+
+				if err != nil {
+					t.Fatalf("Failed to generate timesheet data: %v", err)
+				}
+
+				// Allow for small floating point rounding errors
+				diff := timesheetData.TotalHours - tt.expectTotalHrs
+				if diff < 0 {
+					diff = -diff
+				}
+				if diff > 0.01 {
+					t.Errorf("Expected total hours %.2f, got %.2f", tt.expectTotalHrs, timesheetData.TotalHours)
+					return
+				}
+			}
+		})
+	}
+}
+
+func TestArchivedClientProjectRevenueIntegrity(t *testing.T) {
+	tests := []struct {
+		name          string
+		setupData     func(*queries.Queries) (clientID, projectID int64)
+		verifyInvoice func(*testing.T, []queries.GetInvoiceDataByClientRow)
+	}{
+		{
+			name: "archived client and project both appear in combined invoice",
+			setupData: func(q *queries.Queries) (int64, int64) {
+				client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{
+					Name:         "FullyArchivedClient",
+					BillableRate: sql.NullInt64{Int64: 10000, Valid: true},
+				})
+				project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{
+					Name:     "FullyArchivedProject",
+					ClientID: client.ID,
+				})
+
+				for i := 0; i < 2; i++ {
+					startTime := time.Now().UTC().Add(-time.Duration(i+2) * time.Hour)
+					endTime := time.Now().UTC().Add(-time.Duration(i+1) * time.Hour)
+
+					if i == 1 {
+						_, err := q.DBTX().(*sql.DB).Exec(
+							"INSERT INTO time_entry (client_id, project_id, start_time, end_time) VALUES (?, ?, ?, ?)",
+							client.ID, project.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+						)
+						if err != nil {
+							t.Fatalf("Failed to create time entry: %v", err)
+						}
+					} else {
+						_, err := q.DBTX().(*sql.DB).Exec(
+							"INSERT INTO time_entry (client_id, start_time, end_time) VALUES (?, ?, ?)",
+							client.ID, startTime.Format("2006-01-02 15:04:05"), endTime.Format("2006-01-02 15:04:05"),
+						)
+						if err != nil {
+							t.Fatalf("Failed to create time entry: %v", err)
+						}
+					}
+				}
+
+				_ = q.ArchiveClient(context.Background(), client.ID)
+				_ = q.ArchiveProject(context.Background(), project.ID)
+
+				return client.ID, project.ID
+			},
+			verifyInvoice: func(t *testing.T, entries []queries.GetInvoiceDataByClientRow) {
+				if len(entries) != 2 {
+					t.Errorf("Expected 2 entries, got %d", len(entries))
+				}
+
+				hasProjectEntry := false
+				hasClientEntry := false
+				for _, entry := range entries {
+					if entry.ProjectID.Valid {
+						hasProjectEntry = true
+					} else {
+						hasClientEntry = true
+					}
+				}
+
+				if !hasProjectEntry {
+					t.Errorf("Expected at least one project entry")
+				}
+				if !hasClientEntry {
+					t.Errorf("Expected at least one client-only entry")
+				}
+			},
+		},
+	}
+
+	for _, tt := range tests {
+		t.Run(tt.name, func(t *testing.T) {
+			q, cleanup := setupTestDB(t)
+			defer cleanup()
+
+			clientID, _ := tt.setupData(q)
+
+			startTime := time.Now().UTC().Add(-7 * 24 * time.Hour)
+			endTime := time.Now().UTC()
+
+			entries, err := q.GetInvoiceDataByClient(context.Background(), queries.GetInvoiceDataByClientParams{
+				ClientID:  clientID,
+				StartTime: startTime,
+				EndTime:   endTime,
+			})
+
+			if err != nil {
+				t.Fatalf("Failed to get invoice data: %v", err)
+			}
+
+			tt.verifyInvoice(t, entries)
+		})
+	}
+}