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