From 8be5f93f5b2d4b6f438ca84094937a0f7101c59b Mon Sep 17 00:00:00 2001 From: T Date: Sat, 2 Aug 2025 17:25:59 -0600 Subject: Initial commit of punchcard. Contains working time tracking commands, and the stub of a command to generate reports. --- .gitignore | 1 + CLAUDE.md | 105 ++++ README.md | 65 +++ TODO.md | 8 + cmd/punch/main.go | 21 + go.mod | 20 + go.sum | 33 ++ internal/commands/add.go | 19 + internal/commands/add_client.go | 90 ++++ internal/commands/add_client_test.go | 175 +++++++ internal/commands/add_project.go | 106 ++++ internal/commands/add_project_test.go | 198 +++++++ internal/commands/billable_rate_test.go | 225 ++++++++ internal/commands/import.go | 275 ++++++++++ internal/commands/import_test.go | 543 ++++++++++++++++++++ internal/commands/in.go | 236 +++++++++ internal/commands/in_test.go | 566 +++++++++++++++++++++ internal/commands/out.go | 49 ++ internal/commands/out_test.go | 124 +++++ internal/commands/report.go | 44 ++ internal/commands/root.go | 47 ++ internal/commands/status.go | 379 ++++++++++++++ internal/commands/status_test.go | 534 +++++++++++++++++++ internal/commands/test_utils.go | 48 ++ .../commands/testdata/clockify_extra_columns.csv | 3 + internal/commands/testdata/clockify_full.csv | 4 + internal/commands/testdata/clockify_invalid.csv | 2 + .../commands/testdata/clockify_missing_data.csv | 4 + .../commands/testdata/clockify_no_billable.csv | 3 + internal/commands/testdata/clockify_reordered.csv | 3 + internal/context/db.go | 23 + internal/database/db.go | 68 +++ internal/database/queries.sql | 132 +++++ internal/database/schema.sql | 28 + internal/queries/db.go | 31 ++ internal/queries/dbtx.go | 44 ++ internal/queries/models.go | 36 ++ internal/queries/queries.sql.go | 542 ++++++++++++++++++++ sqlc.yaml | 14 + 39 files changed, 4848 insertions(+) create mode 100644 .gitignore create mode 100644 CLAUDE.md create mode 100644 README.md create mode 100644 TODO.md create mode 100644 cmd/punch/main.go create mode 100644 go.mod create mode 100644 go.sum create mode 100644 internal/commands/add.go create mode 100644 internal/commands/add_client.go create mode 100644 internal/commands/add_client_test.go create mode 100644 internal/commands/add_project.go create mode 100644 internal/commands/add_project_test.go create mode 100644 internal/commands/billable_rate_test.go create mode 100644 internal/commands/import.go create mode 100644 internal/commands/import_test.go create mode 100644 internal/commands/in.go create mode 100644 internal/commands/in_test.go create mode 100644 internal/commands/out.go create mode 100644 internal/commands/out_test.go create mode 100644 internal/commands/report.go create mode 100644 internal/commands/root.go create mode 100644 internal/commands/status.go create mode 100644 internal/commands/status_test.go create mode 100644 internal/commands/test_utils.go create mode 100644 internal/commands/testdata/clockify_extra_columns.csv create mode 100644 internal/commands/testdata/clockify_full.csv create mode 100644 internal/commands/testdata/clockify_invalid.csv create mode 100644 internal/commands/testdata/clockify_missing_data.csv create mode 100644 internal/commands/testdata/clockify_no_billable.csv create mode 100644 internal/commands/testdata/clockify_reordered.csv create mode 100644 internal/context/db.go create mode 100644 internal/database/db.go create mode 100644 internal/database/queries.sql create mode 100644 internal/database/schema.sql create mode 100644 internal/queries/db.go create mode 100644 internal/queries/dbtx.go create mode 100644 internal/queries/models.go create mode 100644 internal/queries/queries.sql.go create mode 100644 sqlc.yaml diff --git a/.gitignore b/.gitignore new file mode 100644 index 0000000..039275c --- /dev/null +++ b/.gitignore @@ -0,0 +1 @@ +punch diff --git a/CLAUDE.md b/CLAUDE.md new file mode 100644 index 0000000..5026b06 --- /dev/null +++ b/CLAUDE.md @@ -0,0 +1,105 @@ +# Punchcard Development Guide + +## Project Overview + +Punchcard is a CLI time tracking tool for freelancers and consultants, built in Go with SQLite storage. + +## Architecture + +### Core Components +- **CLI Interface**: spf13/cobra for command structure +- **Database**: SQLite with sqlc for type-safe queries +- **Report Generation**: Typst templates compiled to PDF +- **Storage**: Local SQLite database file + +### Commands Structure +- `punch in` - Starts timer (inserts row with start_time, NULL end_time) +- `punch out` - Stops active timer (updates end_time), exits non-zero if no active timer +- `punch invoice` - Generates invoice PDF via Typst +- `punch timesheet` - Generates timesheet PDF via Typst + +## Database Schema + +```sql +-- Time tracking entries +CREATE TABLE time_entries ( + id INTEGER PRIMARY KEY AUTOINCREMENT, + start_time DATETIME NOT NULL, + end_time DATETIME NULL, + description TEXT, + created_at DATETIME DEFAULT CURRENT_TIMESTAMP +); + +-- Future: client/project tables for invoicing +``` + +## Technology Stack + +- **Language**: Go 1.21+ +- **CLI Framework**: github.com/spf13/cobra +- **Database**: SQLite3 +- **Query Builder**: github.com/sqlc-dev/sqlc +- **PDF Generation**: Typst (external dependency) + +## File Structure + +``` +punchcard/ +├── cmd/ +│ └── punch/ # Main CLI entry point +├── internal/ +│ ├── commands/ # Cobra command implementations +│ ├── database/ # Database connection and migrations +│ ├── models/ # Data models +│ ├── queries/ # sqlc generated queries +│ └── reports/ # Typst template handling +├── templates/ # Typst templates for PDF generation +├── go.mod +├── go.sum +├── README.md +└── CLAUDE.md +``` + +## Development Guidelines + +### Database +- Use sqlc for all database interactions +- Store timestamps in UTC +- Active timer = row with NULL end_time + +### CLI Design +- Follow cobra conventions +- Provide helpful error messages +- Exit codes: 0 = success, 1 = general error, 2 = no active timer + +### PDF Generation +- Use Typst templates in `templates/` directory +- Invoke `typst compile` subprocess for PDF generation +- Handle Typst installation check gracefully + +### Testing +- Use table-driven tests for business logic +- Test CLI commands with cobra testing utilities +- Use in-memory SQLite for test database + +## Build and Run + +```bash +# Build +go build -o punch cmd/punch/main.go + +# Run +./punch in +./punch out + +# Install locally +go install ./cmd/punch +``` + +## Dependencies + +```bash +go get github.com/spf13/cobra +go get github.com/mattn/go-sqlite3 +go get modernc.org/sqlite +``` diff --git a/README.md b/README.md new file mode 100644 index 0000000..bf723e7 --- /dev/null +++ b/README.md @@ -0,0 +1,65 @@ +# 👊 Punchcard ♣️ + +A simple time tracking CLI tool for freelancers and consultants. + +## What it does + +Punchcard helps you track your work hours and generate professional invoices and timesheets. Start and stop timers from the command line, then generate PDF reports when you're ready to bill your clients. + +## Quick Start + +```bash +# Start tracking time +punch in + +# Stop tracking time +punch out + +# Check current status +punch status + +# Generate reports +punch report invoice +punch report timesheet +``` + +## Commands + +### Time Tracking +- `punch in` - Start a timer for the current work session +- `punch out` - Stop the active timer +- `punch status` - Show current timer status and recent time entries + +### Data Management +- `punch add client ` - Add a new client to the database +- `punch add project ` - Add a new project to the database +- `punch import ` - Import time data from CSV files (supports Clockify CSV exports) + +### Reports +- `punch report invoice` - Generate a PDF invoice from tracked time +- `punch report timesheet` - Generate a PDF timesheet report + +## How it works + +When you run `punch in`, a timer starts recording to a local SQLite database. Run `punch out` to stop the timer. Your time data stays on your machine - nothing is sent to external servers. + +Reports are generated using Typst and compiled to PDF automatically. + +## Installation + +If you have Go installed: +```bash +go install git.tjp.lol/punchcard.git +``` + +Or build from source: +```bash +git clone git.tjp.lol/punchcard.git +cd punchcard +go build -o punch cmd/punch/main.go +``` + +## Requirements + +- Go 1.21+ (for building from source) +- Typst (for PDF generation) diff --git a/TODO.md b/TODO.md new file mode 100644 index 0000000..fa81a6b --- /dev/null +++ b/TODO.md @@ -0,0 +1,8 @@ +- [x] fill in `punch import` based on CSV exports from clockify +- [x] in `punch status` display the most recent time entry if there is no currently active timer +- [x] add a `billable_rate` to both client and project DB tables +- [x] get rid of `test_entry.created_at` +- [x] build any best-practice `#PRAGMA`s for working with sqlite in a local user's environment into `GetDB()` +- [ ] fill in `punch report` with typst report generation for invoices and timesheets +- [ ] brainstorm a TUI mode made with bubbletea +- [ ] implement `punch set [-c|--client ] [-p|--project ] key=value ...` - one of -c or -p is required (but never both), and keys will only be valid if they are members of a specific list, which is dependent on whether we're setting something on a client or project. on client we can set "name", "email", and "billable_rate", and on project we can set "name" and "billable_rate". diff --git a/cmd/punch/main.go b/cmd/punch/main.go new file mode 100644 index 0000000..c98b970 --- /dev/null +++ b/cmd/punch/main.go @@ -0,0 +1,21 @@ +package main + +import ( + "errors" + "fmt" + "os" + "punchcard/internal/commands" +) + +func main() { + if err := commands.Execute(); err != nil { + // Check for specific error types that need special exit codes + if errors.Is(err, commands.ErrNoActiveTimer) { + fmt.Fprintf(os.Stderr, "%s\n", err.Error()) + os.Exit(2) // Exit code 2 for no active timer as per CLAUDE.md + } + + // Default error handling + os.Exit(1) + } +} \ No newline at end of file diff --git a/go.mod b/go.mod new file mode 100644 index 0000000..4c39953 --- /dev/null +++ b/go.mod @@ -0,0 +1,20 @@ +module punchcard + +go 1.24.4 + +require ( + github.com/dustin/go-humanize v1.0.1 // indirect + github.com/google/uuid v1.6.0 // indirect + github.com/inconshreveable/mousetrap v1.1.0 // indirect + github.com/mattn/go-isatty v0.0.20 // indirect + github.com/ncruces/go-strftime v0.1.9 // indirect + github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect + github.com/spf13/cobra v1.9.1 // indirect + github.com/spf13/pflag v1.0.6 // indirect + golang.org/x/exp v0.0.0-20250620022241-b7579e27df2b // indirect + golang.org/x/sys v0.34.0 // indirect + modernc.org/libc v1.66.3 // indirect + modernc.org/mathutil v1.7.1 // indirect + modernc.org/memory v1.11.0 // indirect + modernc.org/sqlite v1.38.2 // indirect +) diff --git a/go.sum b/go.sum new file mode 100644 index 0000000..e2cdc92 --- /dev/null +++ b/go.sum @@ -0,0 +1,33 @@ +github.com/cpuguy83/go-md2man/v2 v2.0.6/go.mod h1:oOW0eioCTA6cOiMLiUPZOpcVxMig6NIQQ7OS05n1F4g= +github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY= +github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto= +github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0= +github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo= +github.com/inconshreveable/mousetrap v1.1.0 h1:wN+x4NVGpMsO7ErUn/mUI3vEoE6Jt13X2s0bqwp9tc8= +github.com/inconshreveable/mousetrap v1.1.0/go.mod h1:vpF70FUmC8bwa3OWnCshd2FqLfsEA9PFc4w1p2J65bw= +github.com/mattn/go-isatty v0.0.20 h1:xfD0iDuEKnDkl03q4limB+vH+GxLEtL/jb4xVJSWWEY= +github.com/mattn/go-isatty v0.0.20/go.mod h1:W+V8PltTTMOvKvAeJH7IuucS94S2C6jfK/D7dTCTo3Y= +github.com/ncruces/go-strftime v0.1.9 h1:bY0MQC28UADQmHmaF5dgpLmImcShSi2kHU9XLdhx/f4= +github.com/ncruces/go-strftime v0.1.9/go.mod h1:Fwc5htZGVVkseilnfgOVb9mKy6w1naJmn9CehxcKcls= +github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE= +github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo= +github.com/russross/blackfriday/v2 v2.1.0/go.mod h1:+Rmxgy9KzJVeS9/2gXHxylqXiyQDYRxCVz55jmeOWTM= +github.com/spf13/cobra v1.9.1 h1:CXSaggrXdbHK9CF+8ywj8Amf7PBRmPCOJugH954Nnlo= +github.com/spf13/cobra v1.9.1/go.mod h1:nDyEzZ8ogv936Cinf6g1RU9MRY64Ir93oCnqb9wxYW0= +github.com/spf13/pflag v1.0.6 h1:jFzHGLGAlb3ruxLB8MhbI6A8+AQX/2eW4qeyNZXNp2o= +github.com/spf13/pflag v1.0.6/go.mod h1:McXfInJRrz4CZXVZOBLb0bTZqETkiAhM9Iw0y3An2Bg= +golang.org/x/exp v0.0.0-20250620022241-b7579e27df2b h1:M2rDM6z3Fhozi9O7NWsxAkg/yqS/lQJ6PmkyIV3YP+o= +golang.org/x/exp v0.0.0-20250620022241-b7579e27df2b/go.mod h1:3//PLf8L/X+8b4vuAfHzxeRUl04Adcb341+IGKfnqS8= +golang.org/x/sys v0.6.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg= +golang.org/x/sys v0.34.0 h1:H5Y5sJ2L2JRdyv7ROF1he/lPdvFsd0mJHFw2ThKHxLA= +golang.org/x/sys v0.34.0/go.mod h1:BJP2sWEmIv4KK5OTEluFJCKSidICx8ciO85XgH3Ak8k= +gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= +gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +modernc.org/libc v1.66.3 h1:cfCbjTUcdsKyyZZfEUKfoHcP3S0Wkvz3jgSzByEWVCQ= +modernc.org/libc v1.66.3/go.mod h1:XD9zO8kt59cANKvHPXpx7yS2ELPheAey0vjIuZOhOU8= +modernc.org/mathutil v1.7.1 h1:GCZVGXdaN8gTqB1Mf/usp1Y/hSqgI2vAGGP4jZMCxOU= +modernc.org/mathutil v1.7.1/go.mod h1:4p5IwJITfppl0G4sUEDtCr4DthTaT47/N3aT6MhfgJg= +modernc.org/memory v1.11.0 h1:o4QC8aMQzmcwCK3t3Ux/ZHmwFPzE6hf2Y5LbkRs+hbI= +modernc.org/memory v1.11.0/go.mod h1:/JP4VbVC+K5sU2wZi9bHoq2MAkCnrt2r98UGeSK7Mjw= +modernc.org/sqlite v1.38.2 h1:Aclu7+tgjgcQVShZqim41Bbw9Cho0y/7WzYptXqkEek= +modernc.org/sqlite v1.38.2/go.mod h1:cPTJYSlgg3Sfg046yBShXENNtPrWrDX8bsbAQBzgQ5E= diff --git a/internal/commands/add.go b/internal/commands/add.go new file mode 100644 index 0000000..7b43f67 --- /dev/null +++ b/internal/commands/add.go @@ -0,0 +1,19 @@ +package commands + +import ( + "github.com/spf13/cobra" +) + +func NewAddCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "add", + Short: "Add new entities to the database", + Long: "Add new clients, projects, or other entities to the punchcard database.", + } + + cmd.AddCommand(NewAddClientCmd()) + cmd.AddCommand(NewAddProjectCmd()) + + return cmd +} + diff --git a/internal/commands/add_client.go b/internal/commands/add_client.go new file mode 100644 index 0000000..e35eba9 --- /dev/null +++ b/internal/commands/add_client.go @@ -0,0 +1,90 @@ +package commands + +import ( + "database/sql" + "fmt" + "regexp" + "strings" + + "punchcard/internal/context" + "punchcard/internal/queries" + + "github.com/spf13/cobra" +) + +func NewAddClientCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "client []", + Short: "Add a new client", + Long: "Add a new client to the database. Name can include email in format 'Name '", + Args: cobra.RangeArgs(1, 2), + RunE: func(cmd *cobra.Command, args []string) error { + name, email := parseNameAndEmail(args) + + billableRateFloat, _ := cmd.Flags().GetFloat64("hourly-rate") + billableRate := int64(billableRateFloat * 100) + + q := context.GetDB(cmd.Context()) + if q == nil { + return fmt.Errorf("database not available in context") + } + + var emailParam sql.NullString + if email != "" { + emailParam = sql.NullString{String: email, Valid: true} + } + + var billableRateParam sql.NullInt64 + if billableRate > 0 { + billableRateParam = sql.NullInt64{Int64: billableRate, Valid: true} + } + + client, err := q.CreateClient(cmd.Context(), queries.CreateClientParams{ + Name: name, + Email: emailParam, + BillableRate: billableRateParam, + }) + if err != nil { + return fmt.Errorf("failed to create client: %w", err) + } + + output := fmt.Sprintf("Created client: %s", client.Name) + if client.Email.Valid { + output += fmt.Sprintf(" <%s>", client.Email.String) + } + output += fmt.Sprintf(" (ID: %d)\n", client.ID) + cmd.Print(output) + + return nil + }, + } + + cmd.Flags().Float64P("hourly-rate", "r", 0, "Default hourly billable rate for this client") + + return cmd +} + +func parseNameAndEmail(args []string) (string, string) { + nameArg := args[0] + var emailArg string + if len(args) > 1 { + emailArg = args[1] + } + + if emailArg != "" { + if matches := emailAndNameRegex.FindStringSubmatch(emailArg); matches != nil { + emailArg = strings.TrimSpace(matches[2]) + } + } + + if matches := emailAndNameRegex.FindStringSubmatch(nameArg); matches != nil { + nameArg = strings.TrimSpace(matches[1]) + if emailArg == "" { + emailArg = strings.TrimSpace(matches[2]) + } + } + + return nameArg, emailArg +} + +var emailAndNameRegex = regexp.MustCompile(`^(.+?)<([^>]+@[^>]+)>$`) diff --git a/internal/commands/add_client_test.go b/internal/commands/add_client_test.go new file mode 100644 index 0000000..23c6e71 --- /dev/null +++ b/internal/commands/add_client_test.go @@ -0,0 +1,175 @@ +package commands + +import ( + "context" + "testing" + + "punchcard/internal/queries" +) + +func TestAddClientCommand(t *testing.T) { + tests := []struct { + name string + args []string + expectedOutput string + expectError bool + }{ + { + name: "add client with name only", + args: []string{"add", "client", "TestCorp"}, + expectedOutput: "Created client: TestCorp (ID: 1)\n", + expectError: false, + }, + { + name: "add client with name and email", + args: []string{"add", "client", "TechSolutions", "contact@techsolutions.com"}, + expectedOutput: "Created client: TechSolutions (ID: 1)\n", + expectError: false, + }, + { + name: "add client with embedded email in name", + args: []string{"add", "client", "StartupXYZ "}, + expectedOutput: "Created client: StartupXYZ (ID: 1)\n", + expectError: false, + }, + { + name: "add client with both embedded and separate email (prefer separate)", + args: []string{"add", "client", "GlobalInc ", "new@global.com"}, + expectedOutput: "Created client: GlobalInc (ID: 1)\n", + expectError: false, + }, + { + name: "add client with email format in email arg", + args: []string{"add", "client", "BigCorp", "Contact Person "}, + expectedOutput: "Created client: BigCorp (ID: 1)\n", + expectError: false, + }, + { + name: "add client with no arguments", + args: []string{"add", "client"}, + expectError: true, + }, + { + name: "add client with too many arguments", + args: []string{"add", "client", "name", "email", "extra"}, + expectError: true, + }, + { + name: "add client with billable rate", + args: []string{"add", "client", "BillableClient", "--hourly-rate", "150.50"}, + expectedOutput: "Created client: BillableClient (ID: 1)\n", + expectError: false, + }, + { + name: "add client with email and billable rate", + args: []string{"add", "client", "PremiumClient", "premium@example.com", "--hourly-rate", "200.75"}, + expectedOutput: "Created client: PremiumClient (ID: 1)\n", + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + // Execute command + output, err := executeCommandWithDB(t, q, tt.args...) + + // Check error expectation + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if tt.expectedOutput != "" && output != tt.expectedOutput { + t.Errorf("expected output %q, got %q", tt.expectedOutput, output) + } + }) + } +} + +func ptrval(i int64) *int64 { + return &i +} + +func TestAddClientBillableRateStorage(t *testing.T) { + tests := []struct { + name string + args []string + expectedRate *int64 // nil means NULL in database, values in cents + expectError bool + }{ + { + name: "client without billable rate stores NULL", + args: []string{"add", "client", "NoRateClient"}, + expectedRate: nil, + expectError: false, + }, + { + name: "client with zero billable rate stores NULL", + args: []string{"add", "client", "ZeroRateClient", "--hourly-rate", "0"}, + expectedRate: nil, + expectError: false, + }, + { + name: "client with billable rate stores value", + args: []string{"add", "client", "RateClient", "--hourly-rate", "125.75"}, + expectedRate: ptrval(12575), // $125.75 = 12575 cents + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + _, err := executeCommandWithDB(t, q, tt.args...) + + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + clientName := tt.args[2] // "add", "client", "" + clients, err := q.FindClient(context.Background(), queries.FindClientParams{ + ID: 0, + Name: clientName, + }) + if err != nil { + t.Fatalf("Failed to query created client: %v", err) + } + if len(clients) != 1 { + t.Fatalf("Expected 1 client, got %d", len(clients)) + } + + client := clients[0] + if tt.expectedRate == nil { + if client.BillableRate.Valid { + t.Errorf("Expected NULL billable_rate, got %d", client.BillableRate.Int64) + } + } else { + if !client.BillableRate.Valid { + t.Errorf("Expected billable_rate %d, got NULL", *tt.expectedRate) + } else if client.BillableRate.Int64 != *tt.expectedRate { + t.Errorf("Expected billable_rate %d, got %d", *tt.expectedRate, client.BillableRate.Int64) + } + } + }) + } +} diff --git a/internal/commands/add_project.go b/internal/commands/add_project.go new file mode 100644 index 0000000..6c37e2a --- /dev/null +++ b/internal/commands/add_project.go @@ -0,0 +1,106 @@ +package commands + +import ( + "context" + "database/sql" + "fmt" + "strconv" + + punchctx "punchcard/internal/context" + "punchcard/internal/queries" + + "github.com/spf13/cobra" +) + +func NewAddProjectCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "project ", + Short: "Add a new project", + Long: `Add a new project to the database. Client can be specified by ID or name using the -c/--client flag. + +Examples: + punch add project "Website Redesign" -c "Acme Corp" + punch add project "Mobile App" --client 1`, + Args: cobra.ExactArgs(1), + RunE: func(cmd *cobra.Command, args []string) error { + projectName := args[0] + + clientRef, err := cmd.Flags().GetString("client") + if err != nil { + return fmt.Errorf("failed to get client flag: %w", err) + } + if clientRef == "" { + return fmt.Errorf("client is required, use -c/--client flag") + } + + billableRateFloat, _ := cmd.Flags().GetFloat64("hourly-rate") + billableRate := int64(billableRateFloat * 100) // Convert dollars to cents + + q := punchctx.GetDB(cmd.Context()) + if q == nil { + return fmt.Errorf("database not available in context") + } + + // Find client by ID or name + client, err := findClient(cmd.Context(), q, clientRef) + if err != nil { + return fmt.Errorf("failed to find client: %w", err) + } + + // Create project + var billableRateParam sql.NullInt64 + if billableRate > 0 { + billableRateParam = sql.NullInt64{Int64: billableRate, Valid: true} + } + + project, err := q.CreateProject(cmd.Context(), queries.CreateProjectParams{ + Name: projectName, + ClientID: client.ID, + BillableRate: billableRateParam, + }) + if err != nil { + return fmt.Errorf("failed to create project: %w", err) + } + + output := fmt.Sprintf("Created project: %s for client %s (ID: %d)", project.Name, client.Name, project.ID) + cmd.Print(output + "\n") + + return nil + }, + } + + cmd.Flags().StringP("client", "c", "", "Client name or ID (required)") + cmd.Flags().Float64P("hourly-rate", "r", 0, "Default hourly billable rate for this project") + if err := cmd.MarkFlagRequired("client"); err != nil { + panic(fmt.Sprintf("Failed to mark client flag as required: %v", err)) + } + + return cmd +} + +func findClient(ctx context.Context, q *queries.Queries, clientRef string) (queries.Client, error) { + // Parse clientRef as ID if possible, otherwise use 0 + var idParam int64 + if id, err := strconv.ParseInt(clientRef, 10, 64); err == nil { + idParam = id + } + + // Search by both ID and name using UNION ALL + clients, err := q.FindClient(ctx, queries.FindClientParams{ + ID: idParam, + Name: clientRef, + }) + if err != nil { + return queries.Client{}, fmt.Errorf("database error looking up client: %w", err) + } + + // Check results + switch len(clients) { + case 0: + return queries.Client{}, fmt.Errorf("client not found: %s", clientRef) + case 1: + return clients[0], nil + default: + return queries.Client{}, fmt.Errorf("ambiguous client: %s", clientRef) + } +} diff --git a/internal/commands/add_project_test.go b/internal/commands/add_project_test.go new file mode 100644 index 0000000..c41bd5c --- /dev/null +++ b/internal/commands/add_project_test.go @@ -0,0 +1,198 @@ +package commands + +import ( + "context" + "database/sql" + "testing" + + "punchcard/internal/queries" +) + +func TestAddProjectCommand(t *testing.T) { + tests := []struct { + name string + setupClient bool + clientName string + clientEmail string + args []string + expectedOutput string + expectError bool + }{ + { + name: "add project with client name", + setupClient: true, + clientName: "TestCorp", + clientEmail: "test@testcorp.com", + args: []string{"add", "project", "Website Redesign", "-c", "TestCorp"}, + expectedOutput: "Created project: Website Redesign for client TestCorp (ID: 1)\n", + expectError: false, + }, + { + name: "add project with client ID", + setupClient: true, + clientName: "TechSolutions", + clientEmail: "contact@techsolutions.com", + args: []string{"add", "project", "Mobile App", "-c", "1"}, + expectedOutput: "Created project: Mobile App for client TechSolutions (ID: 1)\n", + expectError: false, + }, + { + name: "add project with nonexistent client name", + setupClient: false, + args: []string{"add", "project", "Test Project", "-c", "NonexistentClient"}, + expectError: true, + }, + { + name: "add project with nonexistent client ID", + setupClient: false, + args: []string{"add", "project", "Test Project", "-c", "999"}, + expectError: true, + }, + { + name: "add project with no arguments", + setupClient: false, + args: []string{"add", "project"}, + expectError: true, + }, + { + name: "add project with only name", + setupClient: false, + args: []string{"add", "project", "Test Project"}, + expectError: true, + }, + { + name: "add project with too many arguments", + setupClient: true, + clientName: "TestCorp", + args: []string{"add", "project", "name", "extra", "-c", "TestCorp"}, + expectError: true, + }, + { + name: "add project with billable rate", + setupClient: true, + clientName: "BillableClient", + clientEmail: "billing@client.com", + args: []string{"add", "project", "Premium Project", "-c", "BillableClient", "-r", "175.25"}, + expectedOutput: "Created project: Premium Project for client BillableClient (ID: 1)\n", + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + if tt.setupClient { + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: tt.clientName, + Email: sql.NullString{String: tt.clientEmail, Valid: tt.clientEmail != ""}, + BillableRate: sql.NullInt64{}, + }) + if err != nil { + t.Fatalf("Failed to setup test client: %v", err) + } + } + + output, err := executeCommandWithDB(t, q, tt.args...) + + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if output != tt.expectedOutput { + t.Errorf("Expected output %q, got %q", tt.expectedOutput, output) + } + }) + } +} + +func TestAddProjectBillableRateStorage(t *testing.T) { + tests := []struct { + name string + args []string + expectedRate *int64 // nil means NULL in database, values in cents + expectError bool + }{ + { + name: "project without billable rate stores NULL", + args: []string{"add", "project", "NoRateProject", "-c", "testclient"}, + expectedRate: nil, + expectError: false, + }, + { + name: "project with zero billable rate stores NULL", + args: []string{"add", "project", "ZeroRateProject", "-c", "testclient", "--hourly-rate", "0"}, + expectedRate: nil, + expectError: false, + }, + { + name: "project with billable rate stores value", + args: []string{"add", "project", "RateProject", "-c", "testclient", "--hourly-rate", "225.50"}, + expectedRate: func() *int64 { f := int64(22550); return &f }(), // $225.50 = 22550 cents + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "testclient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{}, + }) + if err != nil { + t.Fatalf("Failed to setup test client: %v", err) + } + + _, err = executeCommandWithDB(t, q, tt.args...) + + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + projects, err := q.FindProject(context.Background(), queries.FindProjectParams{ + ID: 1, + Name: "", + }) + if err != nil { + t.Fatalf("Failed to query created project: %v", err) + } + if len(projects) != 1 { + t.Fatalf("Expected 1 project, got %d", len(projects)) + } + + project := projects[0] + if tt.expectedRate == nil { + if project.BillableRate.Valid { + t.Errorf("Expected NULL billable_rate, got %d", project.BillableRate.Int64) + } + } else { + if !project.BillableRate.Valid { + t.Errorf("Expected billable_rate %d, got NULL", *tt.expectedRate) + } else if project.BillableRate.Int64 != *tt.expectedRate { + t.Errorf("Expected billable_rate %d, got %d", *tt.expectedRate, project.BillableRate.Int64) + } + } + }) + } +} diff --git a/internal/commands/billable_rate_test.go b/internal/commands/billable_rate_test.go new file mode 100644 index 0000000..f9ce621 --- /dev/null +++ b/internal/commands/billable_rate_test.go @@ -0,0 +1,225 @@ +package commands + +import ( + "context" + "database/sql" + "testing" + "time" + + "punchcard/internal/queries" +) + +func TestTimeEntryBillableRateCoalescing(t *testing.T) { + tests := []struct { + name string + clientRate *int64 // nil means NULL, values in cents + projectRate *int64 // nil means NULL, values in cents + explicitRate *int64 // nil means NULL, values in cents + expectedRate *int64 // nil means NULL, values in cents + expectError bool + }{ + { + name: "no rates anywhere - should be NULL", + clientRate: nil, + projectRate: nil, + explicitRate: nil, + expectedRate: nil, + expectError: false, + }, + { + name: "only client rate - should use client rate", + clientRate: func() *int64 { f := int64(10000); return &f }(), // $100.00 + projectRate: nil, + explicitRate: nil, + expectedRate: func() *int64 { f := int64(10000); return &f }(), + expectError: false, + }, + { + name: "client and project rates - should use project rate", + clientRate: func() *int64 { f := int64(10000); return &f }(), // $100.00 + projectRate: func() *int64 { f := int64(15000); return &f }(), // $150.00 + explicitRate: nil, + expectedRate: func() *int64 { f := int64(15000); return &f }(), + expectError: false, + }, + { + name: "all rates provided - should use explicit rate", + clientRate: func() *int64 { f := int64(10000); return &f }(), // $100.00 + projectRate: func() *int64 { f := int64(15000); return &f }(), // $150.00 + explicitRate: func() *int64 { f := int64(20000); return &f }(), // $200.00 + expectedRate: func() *int64 { f := int64(20000); return &f }(), + expectError: false, + }, + { + name: "explicit rate overrides even when zero", + clientRate: func() *int64 { f := int64(10000); return &f }(), // $100.00 + projectRate: func() *int64 { f := int64(15000); return &f }(), // $150.00 + explicitRate: func() *int64 { f := int64(0); return &f }(), // $0.00 + expectedRate: func() *int64 { f := int64(0); return &f }(), + expectError: false, + }, + { + name: "only project rate with no client rate - should use project rate", + clientRate: nil, + projectRate: func() *int64 { f := int64(12500); return &f }(), // $125.00 + explicitRate: nil, + expectedRate: func() *int64 { f := int64(12500); return &f }(), + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Create client with optional billable rate + var clientBillableRate sql.NullInt64 + if tt.clientRate != nil { + clientBillableRate = sql.NullInt64{Int64: *tt.clientRate, Valid: true} + } + + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + Email: sql.NullString{}, + BillableRate: clientBillableRate, + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Create project with optional billable rate + var projectBillableRate sql.NullInt64 + if tt.projectRate != nil { + projectBillableRate = sql.NullInt64{Int64: *tt.projectRate, Valid: true} + } + + project, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "TestProject", + ClientID: client.ID, + BillableRate: projectBillableRate, + }) + if err != nil { + t.Fatalf("Failed to create project: %v", err) + } + + // Create time entry with optional explicit billable rate + var explicitBillableRate sql.NullInt64 + if tt.explicitRate != nil { + explicitBillableRate = sql.NullInt64{Int64: *tt.explicitRate, Valid: true} + } + + timeEntry, err := q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Test work", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{Int64: project.ID, Valid: true}, + BillableRate: explicitBillableRate, + }) + + // Check error expectation + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + // Verify the coalesced billable rate + if tt.expectedRate == nil { + if timeEntry.BillableRate.Valid { + t.Errorf("Expected NULL billable_rate, got %d", timeEntry.BillableRate.Int64) + } + } else { + if !timeEntry.BillableRate.Valid { + t.Errorf("Expected billable_rate %d, got NULL", *tt.expectedRate) + } else if timeEntry.BillableRate.Int64 != *tt.expectedRate { + t.Errorf("Expected billable_rate %d, got %d", *tt.expectedRate, timeEntry.BillableRate.Int64) + } + } + }) + } +} + +func TestTimeEntryWithTimesCoalescing(t *testing.T) { + // Test CreateTimeEntryWithTimes also applies coalescing + q, cleanup := setupTestDB(t) + defer cleanup() + + // Create client with rate $100.00 (10000 cents) + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "RateClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 10000, Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Create project with rate $150.00 (15000 cents) + project, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "RateProject", + ClientID: client.ID, + BillableRate: sql.NullInt64{Int64: 15000, Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create project: %v", err) + } + + // Create time entry with times but no explicit rate - should use project rate + now := time.Now().UTC() + timeEntry, err := q.CreateTimeEntryWithTimes(context.Background(), queries.CreateTimeEntryWithTimesParams{ + StartTime: now, + EndTime: sql.NullTime{Time: now.Add(time.Hour), Valid: true}, + Description: sql.NullString{String: "Test work", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{Int64: project.ID, Valid: true}, + BillableRate: sql.NullInt64{}, // No explicit rate + }) + if err != nil { + t.Fatalf("Failed to create time entry: %v", err) + } + + // Should use project rate (15000 cents = $150.00) + if !timeEntry.BillableRate.Valid || timeEntry.BillableRate.Int64 != 15000 { + t.Errorf("Expected billable_rate 15000, got %v", timeEntry.BillableRate) + } +} + +func TestTimeEntryCoalescingWithoutProject(t *testing.T) { + // Test coalescing when no project is specified + q, cleanup := setupTestDB(t) + defer cleanup() + + // Create client with rate $75.50 (7550 cents) + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "NoProjectClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 7550, Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Create time entry without project - should use client rate + timeEntry, err := q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Client work", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{}, // No project + BillableRate: sql.NullInt64{}, // No explicit rate + }) + if err != nil { + t.Fatalf("Failed to create time entry: %v", err) + } + + // Should use client rate (7550 cents = $75.50) + if !timeEntry.BillableRate.Valid || timeEntry.BillableRate.Int64 != 7550 { + t.Errorf("Expected billable_rate 7550, got %v", timeEntry.BillableRate) + } +} + diff --git a/internal/commands/import.go b/internal/commands/import.go new file mode 100644 index 0000000..a767923 --- /dev/null +++ b/internal/commands/import.go @@ -0,0 +1,275 @@ +package commands + +import ( + "context" + "database/sql" + "encoding/csv" + "fmt" + "os" + "strings" + "time" + + punchctx "punchcard/internal/context" + "punchcard/internal/database" + "punchcard/internal/queries" + + "github.com/spf13/cobra" +) + +func NewImportCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "import [file]", + Short: "Import time entries from external sources", + Long: `Import time entries from various external time tracking tools and formats. Use --source to specify the format. + +For Clockify exports: +1. Go to REPORTS > DETAILED in the sidebar +2. Select your desired time range with the date range picker in the top right +3. Click Export > "Save as CSV" from the menu above the table header`, + Args: cobra.ExactArgs(1), + RunE: func(cmd *cobra.Command, args []string) error { + filepath := args[0] + + // Get flag values + source, err := cmd.Flags().GetString("source") + if err != nil { + return fmt.Errorf("failed to get source flag: %w", err) + } + + timezone, err := cmd.Flags().GetString("timezone") + if err != nil { + return fmt.Errorf("failed to get timezone flag: %w", err) + } + + // Get database from context (for tests) or create new connection + queries := punchctx.GetDB(cmd.Context()) + if queries == nil { + var err error + queries, err = database.GetDB() + if err != nil { + return fmt.Errorf("failed to connect to database: %w", err) + } + } + + // Select import function based on source + switch source { + case "clockify": + return importClockifyCSV(queries, filepath, timezone) + case "": + return fmt.Errorf("required flag \"source\" not set") + default: + return fmt.Errorf("unsupported source: %s", source) + } + }, + } + + cmd.Flags().StringP("timezone", "t", "Local", "Timezone of the CSV data (e.g., 'America/New_York', 'UTC', or 'Local')") + cmd.Flags().StringP("source", "s", "", "Source format of the import file (supported: clockify)") + cmd.MarkFlagRequired("source") + + return cmd +} + +type clockifyEntry struct { + Project string + Client string + Description string + StartDate string + StartTime string + EndDate string + EndTime string +} + +func importClockifyCSV(queries *queries.Queries, filepath, timezone string) error { + file, err := os.Open(filepath) + if err != nil { + return fmt.Errorf("failed to open file: %w", err) + } + defer file.Close() + + reader := csv.NewReader(file) + records, err := reader.ReadAll() + if err != nil { + return fmt.Errorf("failed to read CSV: %w", err) + } + + if len(records) < 2 { + return fmt.Errorf("CSV file must have at least a header and one data row") + } + + header := records[0] + + // Find column indices for the fields we actually use + columnIndices := make(map[string]int) + for i, columnName := range header { + columnIndices[columnName] = i + } + + // Check for required columns (only the ones we actually use) + requiredColumns := []string{"Project", "Client", "Start Date", "Start Time", "End Date", "End Time"} + for _, required := range requiredColumns { + if _, exists := columnIndices[required]; !exists { + return fmt.Errorf("CSV file missing required column: %s", required) + } + } + + // Optional billable columns may be present (unused for now) + + var loc *time.Location + if timezone == "Local" { + loc = time.Local + } else { + loc, err = time.LoadLocation(timezone) + if err != nil { + return fmt.Errorf("invalid timezone '%s': %w", timezone, err) + } + } + + importedCount := 0 + for i, record := range records[1:] { + if len(record) < len(header) { + fmt.Printf("Warning: Row %d has insufficient columns, skipping\n", i+2) + continue + } + + // Extract values using column indices + entry := clockifyEntry{ + Project: getColumnValue(record, columnIndices, "Project"), + Client: getColumnValue(record, columnIndices, "Client"), + Description: getColumnValue(record, columnIndices, "Description"), + StartDate: getColumnValue(record, columnIndices, "Start Date"), + StartTime: getColumnValue(record, columnIndices, "Start Time"), + EndDate: getColumnValue(record, columnIndices, "End Date"), + EndTime: getColumnValue(record, columnIndices, "End Time"), + } + + if entry.Client == "" || entry.Project == "" { + fmt.Printf("Warning: Row %d missing client or project, skipping\n", i+2) + continue + } + + if err := importSingleEntry(queries, entry, loc); err != nil { + fmt.Printf("Warning: Row %d failed to import: %v\n", i+2, err) + continue + } + + importedCount++ + } + + fmt.Printf("Successfully imported %d time entries\n", importedCount) + return nil +} + +func importSingleEntry(q *queries.Queries, entry clockifyEntry, loc *time.Location) error { + client, err := getOrCreateClient(q, entry.Client) + if err != nil { + return fmt.Errorf("failed to get or create client: %w", err) + } + + project, err := getOrCreateProject(q, entry.Project, client.ID) + if err != nil { + return fmt.Errorf("failed to get or create project: %w", err) + } + + startTime, err := parseClockifyDateTime(entry.StartDate, entry.StartTime, loc) + if err != nil { + return fmt.Errorf("failed to parse start time: %w", err) + } + + endTime, err := parseClockifyDateTime(entry.EndDate, entry.EndTime, loc) + if err != nil { + return fmt.Errorf("failed to parse end time: %w", err) + } + + var projectID sql.NullInt64 + if project != nil { + projectID = sql.NullInt64{Int64: project.ID, Valid: true} + } + + _, err = q.CreateTimeEntryWithTimes(context.Background(), queries.CreateTimeEntryWithTimesParams{ + StartTime: startTime.UTC(), + EndTime: sql.NullTime{Time: endTime.UTC(), Valid: true}, + Description: sql.NullString{String: entry.Description, Valid: entry.Description != ""}, + ClientID: client.ID, + ProjectID: projectID, + BillableRate: sql.NullInt64{}, + }) + + return err +} + +func getOrCreateClient(q *queries.Queries, name string) (*queries.Client, error) { + client, err := q.GetClientByName(context.Background(), name) + if err == nil { + return &client, nil + } + + if err != sql.ErrNoRows { + return nil, err + } + + createdClient, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: name, + Email: sql.NullString{}, + BillableRate: sql.NullInt64{}, + }) + if err != nil { + return nil, err + } + + return &createdClient, nil +} + +func getOrCreateProject(q *queries.Queries, name string, clientID int64) (*queries.Project, error) { + project, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{ + Name: name, + ClientID: clientID, + }) + if err == nil { + return &project, nil + } + + if err != sql.ErrNoRows { + return nil, err + } + + createdProject, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: name, + ClientID: clientID, + BillableRate: sql.NullInt64{}, + }) + if err != nil { + return nil, err + } + + return &createdProject, nil +} + +func parseClockifyDateTime(date, timeStr string, loc *time.Location) (time.Time, error) { + dateTimeStr := date + " " + timeStr + + layouts := []string{ + "01/02/2006 03:04:05 PM", + "1/2/2006 03:04:05 PM", + "01/02/2006 3:04:05 PM", + "1/2/2006 3:04:05 PM", + "01/02/2006 15:04:05", + "1/2/2006 15:04:05", + } + + for _, layout := range layouts { + if t, err := time.ParseInLocation(layout, dateTimeStr, loc); err == nil { + return t, nil + } + } + + return time.Time{}, fmt.Errorf("unable to parse date/time: %s", dateTimeStr) +} + +// getColumnValue safely extracts a column value from a record using the column index map +func getColumnValue(record []string, columnIndices map[string]int, columnName string) string { + if index, exists := columnIndices[columnName]; exists && index < len(record) { + return strings.TrimSpace(record[index]) + } + return "" +} diff --git a/internal/commands/import_test.go b/internal/commands/import_test.go new file mode 100644 index 0000000..ed59f92 --- /dev/null +++ b/internal/commands/import_test.go @@ -0,0 +1,543 @@ +package commands + +import ( + "context" + "database/sql" + "os" + "path/filepath" + "strings" + "testing" + "time" + + "punchcard/internal/queries" +) + +func TestImportCommand(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) error + csvFile string + args []string + expectedOutputs []string + expectError bool + errorContains string + validateDatabase func(*testing.T, *queries.Queries) + }{ + { + name: "successful import with full CSV format", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "America/New_York"}, + expectedOutputs: []string{"Successfully imported 3 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + // Check clients were created + clients, err := q.ListAllClients(context.Background()) + if err != nil { + t.Errorf("Failed to list clients: %v", err) + return + } + expectedClients := map[string]bool{ + "Acme Corp": false, "Creative Co": false, + } + for _, client := range clients { + if _, exists := expectedClients[client.Name]; exists { + expectedClients[client.Name] = true + } + } + for clientName, found := range expectedClients { + if !found { + t.Errorf("Expected client %q not found", clientName) + } + } + + // Check projects were created + projects, err := q.ListAllProjects(context.Background()) + if err != nil { + t.Errorf("Failed to list projects: %v", err) + return + } + expectedProjects := map[string]bool{ + "Project Alpha": false, "Project Beta": false, "Website Redesign": false, + } + for _, project := range projects { + if _, exists := expectedProjects[project.Name]; exists { + expectedProjects[project.Name] = true + } + } + for projectName, found := range expectedProjects { + if !found { + t.Errorf("Expected project %q not found", projectName) + } + } + }, + }, + { + name: "successful import without billable columns", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_no_billable.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "UTC"}, + expectedOutputs: []string{"Successfully imported 2 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + // Check TechStart Inc client was created + client, err := q.GetClientByName(context.Background(), "TechStart Inc") + if err != nil { + t.Errorf("Expected client 'TechStart Inc' not found: %v", err) + return + } + + // Check projects were created under correct client + project1, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{ + Name: "Project Gamma", ClientID: client.ID, + }) + if err != nil { + t.Errorf("Expected project 'Project Gamma' not found: %v", err) + } + + project2, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{ + Name: "Mobile App", ClientID: client.ID, + }) + if err != nil { + t.Errorf("Expected project 'Mobile App' not found: %v", err) + } + + // Verify the projects belong to the correct client + if project1.ClientID != client.ID { + t.Errorf("Project Gamma should belong to client %d, got %d", client.ID, project1.ClientID) + } + if project2.ClientID != client.ID { + t.Errorf("Mobile App should belong to client %d, got %d", client.ID, project2.ClientID) + } + }, + }, + { + name: "timezone conversion test", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "UTC"}, + expectedOutputs: []string{"Successfully imported 3 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + // We can't easily test the exact timezone conversion without more complex setup, + // but we can verify that entries were created and have valid timestamps + // This is more of an integration test that the timezone parsing doesn't crash + }, + }, + { + name: "duplicate client/project handling", + setupData: func(q *queries.Queries) error { + // Pre-create a client that exists in the CSV + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Acme Corp", + Email: sql.NullString{String: "existing@acme.com", Valid: true}, + }) + if err != nil { + return err + } + return nil + }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Local"}, + expectedOutputs: []string{"Successfully imported 3 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + // Check that we don't have duplicate clients + clients, err := q.ListAllClients(context.Background()) + if err != nil { + t.Errorf("Failed to list clients: %v", err) + return + } + + acmeCount := 0 + for _, client := range clients { + if client.Name == "Acme Corp" { + acmeCount++ + } + } + + if acmeCount != 1 { + t.Errorf("Expected exactly 1 'Acme Corp' client, found %d", acmeCount) + } + }, + }, + { + name: "missing client/project data handling", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_missing_data.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Local"}, + expectedOutputs: []string{"Successfully imported 1 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + // Only the first entry should be imported (has both client and project) + client, err := q.GetClientByName(context.Background(), "Valid Client") + if err != nil { + t.Errorf("Expected client 'Valid Client' not found: %v", err) + } + + project, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{ + Name: "Valid Project", ClientID: client.ID, + }) + if err != nil { + t.Errorf("Expected project 'Valid Project' not found: %v", err) + } + + if project.ClientID != client.ID { + t.Errorf("Project should belong to client %d, got %d", client.ID, project.ClientID) + } + }, + }, + { + name: "invalid CSV format", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_invalid.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Local"}, + expectError: true, + errorContains: "wrong number of fields", + }, + { + name: "nonexistent file", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "nonexistent.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Local"}, + expectError: true, + errorContains: "failed to open file", + }, + { + name: "invalid timezone", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Invalid/Timezone"}, + expectError: true, + errorContains: "invalid timezone", + }, + { + name: "missing source flag", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--timezone", "Local"}, + expectError: true, + errorContains: "required flag(s) \"source\" not set", + }, + { + name: "empty source flag", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "", "--timezone", "Local"}, + expectError: true, + errorContains: "required flag \"source\" not set", + }, + { + name: "invalid source", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_full.csv", + args: []string{"import", "", "--source", "invalid", "--timezone", "Local"}, + expectError: true, + errorContains: "unsupported source: invalid", + }, + { + name: "reordered columns with extra fields", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_reordered.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "UTC"}, + expectedOutputs: []string{"Successfully imported 2 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + client, err := q.GetClientByName(context.Background(), "MegaCorp Inc") + if err != nil { + t.Errorf("Expected client 'MegaCorp Inc' not found: %v", err) + return + } + project, err := q.GetProjectByNameAndClient(context.Background(), queries.GetProjectByNameAndClientParams{ + Name: "Website Overhaul", + ClientID: client.ID, + }) + if err != nil { + t.Errorf("Expected project 'Website Overhaul' not found: %v", err) + } + if project.ClientID != client.ID { + t.Errorf("Website Overhaul should belong to client %d, got %d", client.ID, project.ClientID) + } + }, + }, + { + name: "extra columns beyond required", + setupData: func(q *queries.Queries) error { return nil }, + csvFile: "clockify_extra_columns.csv", + args: []string{"import", "", "--source", "clockify", "--timezone", "Local"}, + expectedOutputs: []string{"Successfully imported 2 time entries"}, + expectError: false, + validateDatabase: func(t *testing.T, q *queries.Queries) { + client, err := q.GetClientByName(context.Background(), "DataCorp") + if err != nil { + t.Errorf("Expected client 'DataCorp' not found: %v", err) + return + } + projects, err := q.ListAllProjects(context.Background()) + if err != nil { + t.Errorf("Failed to list projects: %v", err) + return + } + expectedProjects := map[string]bool{ + "Analytics Dashboard": false, + "API Development": false, + } + for _, project := range projects { + if _, exists := expectedProjects[project.Name]; exists { + expectedProjects[project.Name] = true + if project.ClientID != client.ID { + t.Errorf("Project %s should belong to client %d, got %d", project.Name, client.ID, project.ClientID) + } + } + } + for projectName, found := range expectedProjects { + if !found { + t.Errorf("Expected project %q not found", projectName) + } + } + }, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data if needed + if tt.setupData != nil { + if err := tt.setupData(q); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + } + + // Prepare file path + testDataPath := filepath.Join("testdata", tt.csvFile) + if tt.csvFile != "nonexistent.csv" { + // Verify test file exists + if _, err := os.Stat(testDataPath); os.IsNotExist(err) { + t.Fatalf("Test data file %s does not exist", testDataPath) + } + } + + // Update args with actual file path + args := make([]string, len(tt.args)) + copy(args, tt.args) + if len(args) > 1 && args[1] == "" { + args[1] = testDataPath + } + + // Execute command + output, err := executeCommandWithDB(t, q, args...) + + // Check error expectation + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none. Output: %q, Args: %v", output, args) + } else if tt.errorContains != "" && !strings.Contains(err.Error(), tt.errorContains) { + t.Errorf("Expected error to contain %q, got %q", tt.errorContains, err.Error()) + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + // Note: Output validation is skipped because fmt.Printf writes to os.Stdout + // directly and is not captured by cobra's test framework. + // We rely on database validation instead for testing correctness. + + // Run database validation if provided + if tt.validateDatabase != nil { + tt.validateDatabase(t, q) + } + }) + } +} + +func TestClockifyDateTimeParsing(t *testing.T) { + // Test the parseClockifyDateTime function with various formats + loc, err := time.LoadLocation("America/New_York") + if err != nil { + t.Fatalf("Failed to load timezone: %v", err) + } + + tests := []struct { + name string + date string + timeStr string + expected time.Time + }{ + { + name: "12-hour format with AM", + date: "01/15/2024", + timeStr: "09:30:00 AM", + expected: time.Date(2024, 1, 15, 9, 30, 0, 0, loc), + }, + { + name: "12-hour format with PM", + date: "01/15/2024", + timeStr: "02:45:30 PM", + expected: time.Date(2024, 1, 15, 14, 45, 30, 0, loc), + }, + { + name: "24-hour format", + date: "01/15/2024", + timeStr: "15:20:45", + expected: time.Date(2024, 1, 15, 15, 20, 45, 0, loc), + }, + { + name: "single digit month/day with 12-hour format", + date: "1/5/2024", + timeStr: "9:00:00 AM", + expected: time.Date(2024, 1, 5, 9, 0, 0, 0, loc), + }, + { + name: "single digit month/day with 24-hour format", + date: "1/5/2024", + timeStr: "09:00:00", + expected: time.Date(2024, 1, 5, 9, 0, 0, 0, loc), + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + result, err := parseClockifyDateTime(tt.date, tt.timeStr, loc) + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if !result.Equal(tt.expected) { + t.Errorf("Expected %v, got %v", tt.expected, result) + } + }) + } + + // Test error cases + errorTests := []struct { + name string + date string + timeStr string + }{ + {"invalid date format", "2024-01-15", "09:00:00 AM"}, + {"invalid time format", "01/15/2024", "25:00:00"}, + {"empty date", "", "09:00:00 AM"}, + {"empty time", "01/15/2024", ""}, + } + + for _, tt := range errorTests { + t.Run(tt.name, func(t *testing.T) { + _, err := parseClockifyDateTime(tt.date, tt.timeStr, loc) + if err == nil { + t.Errorf("Expected error for invalid input, but got none") + } + }) + } +} + +func TestGetOrCreateClientAndProject(t *testing.T) { + // Test getOrCreateClient + t.Run("create new client", func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + client, err := getOrCreateClient(q, "New Client") + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if client.Name != "New Client" { + t.Errorf("Expected client name 'New Client', got %q", client.Name) + } + }) + + t.Run("get existing client", func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + // First create a client + originalClient, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Existing Client", + Email: sql.NullString{String: "existing@example.com", Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + // Now try to get or create it again + client, err := getOrCreateClient(q, "Existing Client") + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if client.ID != originalClient.ID { + t.Errorf("Expected to get existing client with ID %d, got %d", originalClient.ID, client.ID) + } + }) + + // Test getOrCreateProject + t.Run("create new project", func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + // First create a client + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Project Client", + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + project, err := getOrCreateProject(q, "New Project", client.ID) + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if project.Name != "New Project" { + t.Errorf("Expected project name 'New Project', got %q", project.Name) + } + + if project.ClientID != client.ID { + t.Errorf("Expected project to belong to client %d, got %d", client.ID, project.ClientID) + } + }) + + t.Run("get existing project", func(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + // First create a client and project + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Another Client", + }) + if err != nil { + t.Fatalf("Failed to create client: %v", err) + } + + originalProject, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Existing Project", + ClientID: client.ID, + }) + if err != nil { + t.Fatalf("Failed to create project: %v", err) + } + + // Now try to get or create it again + project, err := getOrCreateProject(q, "Existing Project", client.ID) + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if project.ID != originalProject.ID { + t.Errorf("Expected to get existing project with ID %d, got %d", originalProject.ID, project.ID) + } + }) +} diff --git a/internal/commands/in.go b/internal/commands/in.go new file mode 100644 index 0000000..abb57f1 --- /dev/null +++ b/internal/commands/in.go @@ -0,0 +1,236 @@ +package commands + +import ( + "context" + "database/sql" + "errors" + "fmt" + "strconv" + "time" + + punchctx "punchcard/internal/context" + "punchcard/internal/queries" + + "github.com/spf13/cobra" +) + +func NewInCmd() *cobra.Command { + var clientFlag, projectFlag string + + cmd := &cobra.Command{ + Use: "in []", + Aliases: []string{"i"}, + Short: "Start a timer", + Long: `Start tracking time for the current work session. + +If no flags are provided, copies the most recent time entry. +If -p/--project is provided without -c/--client, uses the project's client. +If a timer is already active: + - Same parameters: no-op + - Different parameters: stops current timer and starts new one + +Examples: + punch in # Copy most recent entry + punch in "Working on website redesign" # Copy most recent but change description + punch in -c "Acme Corp" "Client meeting" # Specific client + punch in -p "Website Redesign" "Frontend development" # Project (client auto-selected) + punch in --client 1 --project "Website Redesign" # Explicit client and project`, + Args: cobra.MaximumNArgs(1), + RunE: func(cmd *cobra.Command, args []string) error { + var description string + if len(args) > 0 { + description = args[0] + } + + billableRateFloat, _ := cmd.Flags().GetFloat64("hourly-rate") + billableRate := int64(billableRateFloat * 100) // Convert dollars to cents + + q := punchctx.GetDB(cmd.Context()) + if q == nil { + return fmt.Errorf("database not available in context") + } + + // Check if there's already an active timer + activeEntry, err := q.GetActiveTimeEntry(cmd.Context()) + var hasActiveTimer bool + if err != nil && !errors.Is(err, sql.ErrNoRows) { + return fmt.Errorf("failed to check for active timer: %w", err) + } + hasActiveTimer = (err == nil) + + // Validate and get project first (if provided) + var project queries.Project + var projectID sql.NullInt64 + if projectFlag != "" { + proj, err := findProject(cmd.Context(), q, projectFlag) + if err != nil { + return fmt.Errorf("invalid project: %w", err) + } + project = proj + projectID = sql.NullInt64{Int64: project.ID, Valid: true} + } + + // Validate and get client + var clientID int64 + if clientFlag != "" { + client, err := findClient(cmd.Context(), q, clientFlag) + if err != nil { + return fmt.Errorf("invalid client: %w", err) + } + clientID = client.ID + + // If project is specified, verify it belongs to this client + if projectID.Valid && project.ClientID != clientID { + return fmt.Errorf("project %q does not belong to client %q", projectFlag, clientFlag) + } + } else if projectID.Valid { + clientID = project.ClientID + } else if clientFlag == "" && projectFlag == "" { + mostRecentEntry, err := q.GetMostRecentTimeEntry(cmd.Context()) + if err != nil { + if errors.Is(err, sql.ErrNoRows) { + return fmt.Errorf("no previous time entries found - client is required for first entry: use -c/--client flag") + } + return fmt.Errorf("failed to get most recent time entry: %w", err) + } + + clientID = mostRecentEntry.ClientID + projectID = mostRecentEntry.ProjectID + if description == "" && mostRecentEntry.Description.Valid { + description = mostRecentEntry.Description.String + } + } else { + return fmt.Errorf("client is required: use -c/--client flag to specify client") + } + + if hasActiveTimer { + // Check if the new timer would be identical to the active one + if timeEntriesMatch(clientID, projectID, description, activeEntry) { + // No-op: identical timer already active + cmd.Printf("Timer already active with same parameters (ID: %d)\n", activeEntry.ID) + return nil + } + + // Stop the active timer before starting new one + stoppedEntry, err := q.StopTimeEntry(cmd.Context()) + if err != nil { + return fmt.Errorf("failed to stop active timer: %w", err) + } + + duration := stoppedEntry.EndTime.Time.Sub(stoppedEntry.StartTime) + cmd.Printf("Stopped previous timer (ID: %d). Duration: %v\n", + stoppedEntry.ID, duration.Round(time.Second)) + } + + // Create time entry + var descParam sql.NullString + if description != "" { + descParam = sql.NullString{String: description, Valid: true} + } + + var billableRateParam sql.NullInt64 + if billableRate > 0 { + billableRateParam = sql.NullInt64{Int64: billableRate, Valid: true} + } + + timeEntry, err := q.CreateTimeEntry(cmd.Context(), queries.CreateTimeEntryParams{ + Description: descParam, + ClientID: clientID, + ProjectID: projectID, + BillableRate: billableRateParam, + }) + if err != nil { + return fmt.Errorf("failed to create time entry: %w", err) + } + + // Build output message + output := fmt.Sprintf("Started timer (ID: %d)", timeEntry.ID) + + // Add client info + client, _ := findClient(cmd.Context(), q, strconv.FormatInt(clientID, 10)) + output += fmt.Sprintf(" for client: %s", client.Name) + + // Add project info if provided + if projectID.Valid { + project, _ := findProject(cmd.Context(), q, strconv.FormatInt(projectID.Int64, 10)) + output += fmt.Sprintf(", project: %s", project.Name) + } + + // Add description if provided + if description != "" { + output += fmt.Sprintf(", description: %s", description) + } + + cmd.Print(output + "\n") + return nil + }, + } + + cmd.Flags().StringVarP(&clientFlag, "client", "c", "", "Client name or ID") + cmd.Flags().StringVarP(&projectFlag, "project", "p", "", "Project name or ID") + cmd.Flags().Float64("hourly-rate", 0, "Override hourly billable rate for this time entry") + + return cmd +} + +func findProject(ctx context.Context, q *queries.Queries, projectRef string) (queries.Project, error) { + // Parse projectRef as ID if possible, otherwise use 0 + var idParam int64 + if id, err := strconv.ParseInt(projectRef, 10, 64); err == nil { + idParam = id + } + + // Search by both ID and name using UNION ALL + projects, err := q.FindProject(ctx, queries.FindProjectParams{ + ID: idParam, + Name: projectRef, + }) + if err != nil { + return queries.Project{}, fmt.Errorf("database error looking up project: %w", err) + } + + // Check results + switch len(projects) { + case 0: + return queries.Project{}, fmt.Errorf("project not found: %s", projectRef) + case 1: + return projects[0], nil + default: + return queries.Project{}, fmt.Errorf("ambiguous project: %s", projectRef) + } +} + +// timeEntriesMatch checks if a new time entry would be identical to an active one +// by comparing client ID, project ID, and description +func timeEntriesMatch(clientID int64, projectID sql.NullInt64, description string, activeEntry queries.TimeEntry) bool { + // Client must match + if activeEntry.ClientID != clientID { + return false + } + + // Check project ID matching + if projectID.Valid != activeEntry.ProjectID.Valid { + // One has a project, the other doesn't + return false + } + if projectID.Valid { + // Both have projects - compare IDs + if activeEntry.ProjectID.Int64 != projectID.Int64 { + return false + } + } + + // Check description matching + if (description != "") != activeEntry.Description.Valid { + // One has description, the other doesn't + return false + } + if activeEntry.Description.Valid { + // Both have descriptions - compare strings + if activeEntry.Description.String != description { + return false + } + } + + return true +} diff --git a/internal/commands/in_test.go b/internal/commands/in_test.go new file mode 100644 index 0000000..3832037 --- /dev/null +++ b/internal/commands/in_test.go @@ -0,0 +1,566 @@ +package commands + +import ( + "context" + "database/sql" + "strings" + "testing" + + "punchcard/internal/queries" +) + +func TestInCommand(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) (clientID, projectID int64) + args []string + expectedOutputs []string // Multiple possible outputs to check + expectError bool + errorContains string + }{ + { + name: "punch in with client by name", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + Email: sql.NullString{String: "test@testcorp.com", Valid: true}, + BillableRate: sql.NullInt64{}, + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "TestCorp"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: TestCorp"}, + expectError: false, + }, + { + name: "punch in with client by ID", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TechSolutions", + Email: sql.NullString{String: "contact@techsolutions.com", Valid: true}, + BillableRate: sql.NullInt64{}, + }) + return client.ID, 0 + }, + args: []string{"in", "--client", "1"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: TechSolutions"}, + expectError: false, + }, + { + name: "punch in with client and project", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "StartupXYZ", + Email: sql.NullString{String: "hello@startupxyz.io", Valid: true}, + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Website Redesign", + ClientID: client.ID, + BillableRate: sql.NullInt64{}, + }) + return client.ID, project.ID + }, + args: []string{"in", "-c", "StartupXYZ", "-p", "Website Redesign"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: StartupXYZ, project: Website Redesign"}, + expectError: false, + }, + { + name: "punch in with description", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "BigCorp", + Email: sql.NullString{String: "contact@bigcorp.com", Valid: true}, + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "BigCorp", "Working on frontend"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: BigCorp, description: Working on frontend"}, + expectError: false, + }, + { + name: "punch in with client, project, and description", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "MegaCorp", + Email: sql.NullString{String: "info@megacorp.com", Valid: true}, + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Mobile App", + ClientID: client.ID, + }) + return client.ID, project.ID + }, + args: []string{"in", "-c", "MegaCorp", "-p", "Mobile App", "Implementing login flow"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: MegaCorp, project: Mobile App, description: Implementing login flow"}, + expectError: false, + }, + { + name: "punch in without client", + setupData: func(q *queries.Queries) (int64, int64) { return 0, 0 }, + args: []string{"in"}, + expectError: true, + errorContains: "client is required", + }, + { + name: "punch in with nonexistent client", + setupData: func(q *queries.Queries) (int64, int64) { return 0, 0 }, + args: []string{"in", "-c", "NonexistentClient"}, + expectError: true, + errorContains: "invalid client", + }, + { + name: "punch in with nonexistent project but no client", + setupData: func(q *queries.Queries) (int64, int64) { return 0, 0 }, + args: []string{"in", "-p", "SomeProject"}, + expectError: true, + errorContains: "invalid project: project not found", + }, + { + name: "punch in with project not belonging to client", + setupData: func(q *queries.Queries) (int64, int64) { + client1, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Client1", + Email: sql.NullString{String: "test1@example.com", Valid: true}, + }) + client2, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Client2", + Email: sql.NullString{String: "test2@example.com", Valid: true}, + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Project1", + ClientID: client2.ID, + }) + return client1.ID, project.ID + }, + args: []string{"in", "-c", "Client1", "-p", "Project1"}, + expectError: true, + errorContains: "does not belong to client", + }, + { + name: "punch in with project but no client - uses project's client", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ProjectClient", + Email: sql.NullString{String: "project@client.com", Valid: true}, + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "TestProject", + ClientID: client.ID, + }) + return client.ID, project.ID + }, + args: []string{"in", "-p", "TestProject"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: ProjectClient, project: TestProject"}, + expectError: false, + }, + { + name: "punch in with no flags - no previous entries", + setupData: func(q *queries.Queries) (int64, int64) { return 0, 0 }, + args: []string{"in"}, + expectError: true, + errorContains: "no previous time entries found", + }, + { + name: "punch in with billable rate flag", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "BillableClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{}, + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "BillableClient", "--hourly-rate", "250.75", "Premium work"}, + expectedOutputs: []string{"Started timer (ID: 1) for client: BillableClient, description: Premium work"}, + expectError: false, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + tt.setupData(q) + + // Execute command + output, err := executeCommandWithDB(t, q, tt.args...) + + // Check error expectation + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } else if tt.errorContains != "" && !strings.Contains(err.Error(), tt.errorContains) { + t.Errorf("Expected error to contain %q, got %q", tt.errorContains, err.Error()) + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + // Check output contains expected strings + found := false + for _, expectedOutput := range tt.expectedOutputs { + if strings.Contains(output, expectedOutput) { + found = true + break + } + } + + if !found { + t.Errorf("Expected output to contain one of %v, got %q", tt.expectedOutputs, output) + } + }) + } +} + +func TestInCommandActiveTimerBehaviors(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) error + firstArgs []string + secondArgs []string + expectFirstIn string + expectSecondIn string + }{ + { + name: "no-op when identical timer already active", + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "TestProject", + ClientID: client.ID, + }) + return err + }, + firstArgs: []string{"in", "-c", "TestClient", "-p", "TestProject", "Working on tests"}, + secondArgs: []string{"in", "-c", "TestClient", "-p", "TestProject", "Working on tests"}, // Identical + expectFirstIn: "Started timer (ID: 1)", + expectSecondIn: "Timer already active with same parameters (ID: 1)", + }, + { + name: "stop current and start new when different description", + setupData: func(q *queries.Queries) error { + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + }) + return err + }, + firstArgs: []string{"in", "-c", "TestClient", "First task"}, + secondArgs: []string{"in", "-c", "TestClient", "Second task"}, // Different description + expectFirstIn: "Started timer (ID: 1)", + expectSecondIn: "Stopped previous timer (ID: 1)", + }, + { + name: "stop current and start new when different client", + setupData: func(q *queries.Queries) error { + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Client1", + }) + if err != nil { + return err + } + + _, err = q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Client2", + }) + return err + }, + firstArgs: []string{"in", "-c", "Client1"}, + secondArgs: []string{"in", "-c", "Client2"}, // Different client + expectFirstIn: "Started timer (ID: 1)", + expectSecondIn: "Stopped previous timer (ID: 1)", + }, + { + name: "copy most recent entry when no flags provided", + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + }) + if err != nil { + return err + } + + // Create and immediately stop a time entry + if _, err := q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Previous work", Valid: true}, + ClientID: client.ID, + }); err != nil { + return err + } + + _, err = q.StopTimeEntry(context.Background()) + return err + }, + firstArgs: []string{"in"}, // No flags - should copy most recent + secondArgs: []string{"in"}, // Same - should be no-op + expectFirstIn: "Started timer (ID: 2) for client: TestClient, description: Previous work", + expectSecondIn: "Timer already active with same parameters (ID: 2)", + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + if err := tt.setupData(q); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + + // Execute first command + output1, err1 := executeCommandWithDB(t, q, tt.firstArgs...) + if err1 != nil { + t.Fatalf("First command failed: %v", err1) + } + + if !strings.Contains(output1, tt.expectFirstIn) { + t.Errorf("First command output should contain %q, got: %s", tt.expectFirstIn, output1) + } + + // Execute second command + output2, err2 := executeCommandWithDB(t, q, tt.secondArgs...) + if err2 != nil { + t.Fatalf("Second command failed: %v", err2) + } + + if !strings.Contains(output2, tt.expectSecondIn) { + t.Errorf("Second command output should contain %q, got: %s", tt.expectSecondIn, output2) + } + }) + } +} + +func TestInCommandBillableRateStorage(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) (clientID, projectID int64) + args []string + expectedRate *int64 // nil means should use coalesced value, values in cents + expectExplicitNil bool // true means should be NULL despite coalescing options + }{ + { + name: "explicit billable rate overrides client rate", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ClientWithRate", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 10000, Valid: true}, // $100.00 + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "ClientWithRate", "--hourly-rate", "175.25"}, + expectedRate: func() *int64 { f := int64(17525); return &f }(), // $175.25 + }, + { + name: "explicit billable rate overrides project rate", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 10000, Valid: true}, // $100.00 + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "ProjectWithRate", + ClientID: client.ID, + BillableRate: sql.NullInt64{Int64: 15000, Valid: true}, // $150.00 + }) + return client.ID, project.ID + }, + args: []string{"in", "-c", "TestClient", "-p", "ProjectWithRate", "--hourly-rate", "200.50"}, + expectedRate: func() *int64 { f := int64(20050); return &f }(), // $200.50 + }, + { + name: "no explicit rate uses project rate", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 10000, Valid: true}, // $100.00 + }) + project, _ := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "ProjectWithRate", + ClientID: client.ID, + BillableRate: sql.NullInt64{Int64: 12500, Valid: true}, // $125.00 + }) + return client.ID, project.ID + }, + args: []string{"in", "-c", "TestClient", "-p", "ProjectWithRate"}, + expectedRate: func() *int64 { f := int64(12500); return &f }(), // $125.00 + }, + { + name: "no explicit rate and no project uses client rate", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ClientOnly", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{Int64: 9000, Valid: true}, // $90.00 + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "ClientOnly"}, + expectedRate: func() *int64 { f := int64(9000); return &f }(), // $90.00 + }, + { + name: "no rates anywhere results in NULL", + setupData: func(q *queries.Queries) (int64, int64) { + client, _ := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "NoRateClient", + Email: sql.NullString{}, + BillableRate: sql.NullInt64{}, + }) + return client.ID, 0 + }, + args: []string{"in", "-c", "NoRateClient"}, + expectExplicitNil: true, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + tt.setupData(q) + + // Execute command + _, err := executeCommandWithDB(t, q, tt.args...) + if err != nil { + t.Fatalf("Unexpected error: %v", err) + } + + // Query the created time entry to verify billable_rate + timeEntry, err := q.GetActiveTimeEntry(context.Background()) + if err != nil { + t.Fatalf("Failed to query created time entry: %v", err) + } + + if tt.expectExplicitNil { + if timeEntry.BillableRate.Valid { + t.Errorf("Expected NULL billable_rate, got %d", timeEntry.BillableRate.Int64) + } + } else if tt.expectedRate != nil { + if !timeEntry.BillableRate.Valid { + t.Errorf("Expected billable_rate %d, got NULL", *tt.expectedRate) + } else if timeEntry.BillableRate.Int64 != *tt.expectedRate { + t.Errorf("Expected billable_rate %d, got %d", *tt.expectedRate, timeEntry.BillableRate.Int64) + } + } + }) + } +} + +// TestFindFunctions tests both findClient and findProject functions with consolidated test cases +func TestFindFunctions(t *testing.T) { + q, cleanup := setupTestDB(t) + defer cleanup() + + // Create test client + testClient, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "Test Client", + Email: sql.NullString{String: "test@example.com", Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create test client: %v", err) + } + + // Create test project + testProject, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Test Project", + ClientID: testClient.ID, + }) + if err != nil { + t.Fatalf("Failed to create test project: %v", err) + } + + // Create entities with name "1" to test ambiguous lookup + _, err = q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "1", + Email: sql.NullString{String: "one@example.com", Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to create ambiguous test client: %v", err) + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "1", + ClientID: testClient.ID, + }) + if err != nil { + t.Fatalf("Failed to create ambiguous test project: %v", err) + } + + tests := []struct { + name string + entityType string // "client" or "project" + ref string + expectError bool + expectedID int64 + }{ + // Client tests + {"find client by name", "client", "Test Client", false, testClient.ID}, + {"find client by ID", "client", "2", false, 2}, // Client with name "1" gets ID 2 + {"find client - ambiguous (name matches ID)", "client", "1", true, 0}, + {"find client - nonexistent name", "client", "Nonexistent Client", true, 0}, + {"find client - nonexistent ID", "client", "999", true, 0}, + + // Project tests + {"find project by name", "project", "Test Project", false, testProject.ID}, + {"find project by ID", "project", "2", false, 2}, // Project with name "1" gets ID 2 + {"find project - ambiguous (name matches ID)", "project", "1", true, 0}, + {"find project - nonexistent name", "project", "Nonexistent Project", true, 0}, + {"find project - nonexistent ID", "project", "999", true, 0}, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + var err error + var actualID int64 + + if tt.entityType == "client" { + client, findErr := findClient(context.Background(), q, tt.ref) + err = findErr + if findErr == nil { + actualID = client.ID + } + } else { + project, findErr := findProject(context.Background(), q, tt.ref) + err = findErr + if findErr == nil { + actualID = project.ID + } + } + + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + if actualID != tt.expectedID { + t.Errorf("Expected %s ID %d, got %d", tt.entityType, tt.expectedID, actualID) + } + }) + } +} diff --git a/internal/commands/out.go b/internal/commands/out.go new file mode 100644 index 0000000..f98a63c --- /dev/null +++ b/internal/commands/out.go @@ -0,0 +1,49 @@ +package commands + +import ( + "database/sql" + "errors" + "fmt" + "time" + + punchctx "punchcard/internal/context" + + "github.com/spf13/cobra" +) + +var ErrNoActiveTimer = errors.New("no active timer found") + +func NewOutCmd() *cobra.Command { + return &cobra.Command{ + Use: "out", + Short: "Stop the active timer", + Long: "Stop tracking time for the current work session by setting the end time of the active time entry.", + Args: cobra.NoArgs, + RunE: func(cmd *cobra.Command, args []string) error { + q := punchctx.GetDB(cmd.Context()) + if q == nil { + return fmt.Errorf("database not available in context") + } + + // Stop the active timer by setting end_time to now + stoppedEntry, err := q.StopTimeEntry(cmd.Context()) + if err != nil { + if errors.Is(err, sql.ErrNoRows) { + return ErrNoActiveTimer + } + return fmt.Errorf("failed to stop timer: %w", err) + } + + // Calculate duration + duration := stoppedEntry.EndTime.Time.Sub(stoppedEntry.StartTime) + + // Output success message + cmd.Printf("Timer stopped. Session duration: %v\n", duration.Round(time.Second)) + + // Show entry ID for reference + cmd.Printf("Time entry ID: %d\n", stoppedEntry.ID) + + return nil + }, + } +} diff --git a/internal/commands/out_test.go b/internal/commands/out_test.go new file mode 100644 index 0000000..aeb2359 --- /dev/null +++ b/internal/commands/out_test.go @@ -0,0 +1,124 @@ +package commands + +import ( + "context" + "database/sql" + "errors" + "testing" + + "punchcard/internal/queries" +) + +func TestOutCommand(t *testing.T) { + tests := []struct { + name string + setupTimeEntry bool + args []string + expectError bool + expectedOutput string + }{ + { + name: "stop active timer", + setupTimeEntry: true, + args: []string{"out"}, + expectError: false, + expectedOutput: "Timer stopped. Session duration:", + }, + { + name: "no active timer", + setupTimeEntry: false, + args: []string{"out"}, + expectError: true, + }, + { + name: "out command with arguments should fail", + setupTimeEntry: false, + args: []string{"out", "extra"}, + expectError: true, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup time entry if needed + if tt.setupTimeEntry { + // Create a test client first + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + Email: sql.NullString{String: "test@example.com", Valid: true}, + }) + if err != nil { + t.Fatalf("Failed to setup test client: %v", err) + } + + // Create active time entry + _, err = q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Test work", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{}, + }) + if err != nil { + t.Fatalf("Failed to setup test time entry: %v", err) + } + } + + // Execute command + output, err := executeCommandWithDB(t, q, tt.args...) + + // Check error expectation + if tt.expectError { + if err == nil { + t.Errorf("Expected error but got none") + } + return + } + + if err != nil { + t.Errorf("Unexpected error: %v", err) + return + } + + // Check output contains expected text + if tt.expectedOutput != "" { + if len(output) == 0 || output[:len(tt.expectedOutput)] != tt.expectedOutput { + t.Errorf("Expected output to start with %q, got %q", tt.expectedOutput, output) + } + } + + // If we set up a time entry, verify it was stopped + if tt.setupTimeEntry { + // Try to get active time entry - should return no rows + _, err := q.GetActiveTimeEntry(context.Background()) + if !errors.Is(err, sql.ErrNoRows) { + t.Errorf("Expected no active time entry after stopping, but got: %v", err) + } + + // Verify the time entry was updated with an end_time + // We can't directly query by ID with the current queries, but we can check that no active entries exist + } + }) + } +} + +func TestOutCommandErrorType(t *testing.T) { + // Test that the specific ErrNoActiveTimer error is returned + q, cleanup := setupTestDB(t) + defer cleanup() + + // Execute out command with no active timer + _, err := executeCommandWithDB(t, q, "out") + + if err == nil { + t.Fatal("Expected error but got none") + } + + // Check that it's specifically the ErrNoActiveTimer error + if !errors.Is(err, ErrNoActiveTimer) { + t.Errorf("Expected ErrNoActiveTimer, got: %v", err) + } +} + diff --git a/internal/commands/report.go b/internal/commands/report.go new file mode 100644 index 0000000..eaa1b49 --- /dev/null +++ b/internal/commands/report.go @@ -0,0 +1,44 @@ +package commands + +import ( + "fmt" + + "github.com/spf13/cobra" +) + +func NewReportCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "report", + Short: "Generate reports from tracked time", + Long: "Generate various types of reports (invoices, timesheets, etc.) from tracked time data.", + } + + cmd.AddCommand(NewReportInvoiceCmd()) + cmd.AddCommand(NewReportTimesheetCmd()) + + return cmd +} + +func NewReportInvoiceCmd() *cobra.Command { + return &cobra.Command{ + Use: "invoice", + Short: "Generate a PDF invoice", + Long: "Generate a PDF invoice from tracked time.", + RunE: func(cmd *cobra.Command, args []string) error { + fmt.Println("Invoice generation (placeholder)") + return nil + }, + } +} + +func NewReportTimesheetCmd() *cobra.Command { + return &cobra.Command{ + Use: "timesheet", + Short: "Generate a PDF timesheet", + Long: "Generate a PDF timesheet report from tracked time.", + RunE: func(cmd *cobra.Command, args []string) error { + fmt.Println("Timesheet generation (placeholder)") + return nil + }, + } +} diff --git a/internal/commands/root.go b/internal/commands/root.go new file mode 100644 index 0000000..6c400ee --- /dev/null +++ b/internal/commands/root.go @@ -0,0 +1,47 @@ +package commands + +import ( + "context" + "database/sql" + + punchctx "punchcard/internal/context" + "punchcard/internal/database" + + "github.com/spf13/cobra" +) + +func NewRootCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "punch", + Short: "A simple time tracking CLI tool", + Long: "Punchcard helps you track your work hours and generate professional invoices and timesheets.", + RunE: NewStatusCmd().RunE, // Default to status command when no subcommand is provided + } + + cmd.AddCommand(NewAddCmd()) + cmd.AddCommand(NewInCmd()) + cmd.AddCommand(NewOutCmd()) + cmd.AddCommand(NewStatusCmd()) + cmd.AddCommand(NewImportCmd()) + cmd.AddCommand(NewReportCmd()) + + return cmd +} + +func Execute() error { + // Get database connection + q, err := database.GetDB() + if err != nil { + return err + } + defer func() { + if db, ok := q.DBTX().(*sql.DB); ok { + db.Close() + } + }() + + // Create context with database + ctx := punchctx.WithDB(context.Background(), q) + + return NewRootCmd().ExecuteContext(ctx) +} diff --git a/internal/commands/status.go b/internal/commands/status.go new file mode 100644 index 0000000..626b258 --- /dev/null +++ b/internal/commands/status.go @@ -0,0 +1,379 @@ +package commands + +import ( + "context" + "database/sql" + "errors" + "fmt" + "strconv" + "time" + + punchctx "punchcard/internal/context" + "punchcard/internal/queries" + + "github.com/spf13/cobra" +) + +func NewStatusCmd() *cobra.Command { + cmd := &cobra.Command{ + Use: "status", + Aliases: []string{"st"}, + Short: "Show current status and summaries", + Long: `Show the current status including: +- Current week work summary by project and client +- Current month work summary by project and client +- Active timer status (if any) +- Clients and projects list (use --clients/-c or --projects/-p to show only one type)`, + Args: cobra.NoArgs, + RunE: func(cmd *cobra.Command, args []string) error { + q := punchctx.GetDB(cmd.Context()) + if q == nil { + return fmt.Errorf("database not available in context") + } + + ctx := cmd.Context() + + // Get active timer status first + activeEntry, err := q.GetActiveTimeEntry(ctx) + var hasActiveTimer bool + if err != nil && !errors.Is(err, sql.ErrNoRows) { + return fmt.Errorf("failed to check for active timer: %w", err) + } + hasActiveTimer = (err == nil) + + // Display active timer status + if hasActiveTimer { + duration := time.Since(activeEntry.StartTime) + cmd.Printf("🔴 Active Timer (running for %v)\n", duration.Round(time.Second)) + + // Get client info + client, err := findClient(ctx, q, strconv.FormatInt(activeEntry.ClientID, 10)) + if err != nil { + cmd.Printf(" Client: ID %d (error getting name: %v)\n", activeEntry.ClientID, err) + } else { + clientInfo := client.Name + if client.BillableRate.Valid { + rateInDollars := float64(client.BillableRate.Int64) / 100.0 + clientInfo += fmt.Sprintf(" ($%.2f/hr)", rateInDollars) + } + cmd.Printf(" Client: %s\n", clientInfo) + } + + // Get project info if exists + if activeEntry.ProjectID.Valid { + project, err := findProject(ctx, q, strconv.FormatInt(activeEntry.ProjectID.Int64, 10)) + if err != nil { + cmd.Printf(" Project: ID %d (error getting name: %v)\n", activeEntry.ProjectID.Int64, err) + } else { + projectInfo := project.Name + if project.BillableRate.Valid { + rateInDollars := float64(project.BillableRate.Int64) / 100.0 + projectInfo += fmt.Sprintf(" ($%.2f/hr)", rateInDollars) + } + cmd.Printf(" Project: %s\n", projectInfo) + } + } else { + cmd.Printf(" Project: (none)\n") + } + + // Show description if exists + if activeEntry.Description.Valid { + cmd.Printf(" Description: %s\n", activeEntry.Description.String) + } else { + cmd.Printf(" Description: (none)\n") + } + + // Show billable rate if it exists on the time entry + if activeEntry.BillableRate.Valid { + rateInDollars := float64(activeEntry.BillableRate.Int64) / 100.0 + cmd.Printf(" Billable Rate: $%.2f/hr\n", rateInDollars) + } + cmd.Printf("\n") + } else { + cmd.Printf("⚪ No active timer\n") + + // Try to show the most recent time entry (will be completed since no active timer) + recentEntry, err := q.GetMostRecentTimeEntry(ctx) + if err != nil && !errors.Is(err, sql.ErrNoRows) { + return fmt.Errorf("failed to get most recent time entry: %w", err) + } + + if err == nil { + // Display the most recent entry + duration := recentEntry.EndTime.Time.Sub(recentEntry.StartTime) + cmd.Printf("\n📝 Most Recent Entry\n") + + // Get client info + client, err := findClient(ctx, q, strconv.FormatInt(recentEntry.ClientID, 10)) + if err != nil { + cmd.Printf(" Client: ID %d (error getting name: %v)\n", recentEntry.ClientID, err) + } else { + clientInfo := client.Name + if client.BillableRate.Valid { + rateInDollars := float64(client.BillableRate.Int64) / 100.0 + clientInfo += fmt.Sprintf(" ($%.2f/hr)", rateInDollars) + } + cmd.Printf(" Client: %s\n", clientInfo) + } + + // Get project info if exists + if recentEntry.ProjectID.Valid { + project, err := findProject(ctx, q, strconv.FormatInt(recentEntry.ProjectID.Int64, 10)) + if err != nil { + cmd.Printf(" Project: ID %d (error getting name: %v)\n", recentEntry.ProjectID.Int64, err) + } else { + projectInfo := project.Name + if project.BillableRate.Valid { + rateInDollars := float64(project.BillableRate.Int64) / 100.0 + projectInfo += fmt.Sprintf(" ($%.2f/hr)", rateInDollars) + } + cmd.Printf(" Project: %s\n", projectInfo) + } + } else { + cmd.Printf(" Project: (none)\n") + } + + // Show description if exists + if recentEntry.Description.Valid { + cmd.Printf(" Description: %s\n", recentEntry.Description.String) + } else { + cmd.Printf(" Description: (none)\n") + } + + // Show billable rate if it exists on the time entry + if recentEntry.BillableRate.Valid { + rateInDollars := float64(recentEntry.BillableRate.Int64) / 100.0 + cmd.Printf(" Billable Rate: $%.2f/hr\n", rateInDollars) + } + + // Show time information + cmd.Printf(" Started: %s\n", recentEntry.StartTime.Format("Jan 2, 2006 at 3:04 PM")) + cmd.Printf(" Ended: %s\n", recentEntry.EndTime.Time.Format("Jan 2, 2006 at 3:04 PM")) + cmd.Printf(" Duration: %v\n", duration.Round(time.Minute)) + } + + cmd.Printf("\n") + } + + // Display clients and projects + showClients, _ := cmd.Flags().GetBool("clients") + showProjects, _ := cmd.Flags().GetBool("projects") + + if err := displayClientsAndProjects(ctx, cmd, q, showClients, showProjects); err != nil { + return fmt.Errorf("failed to display clients and projects: %w", err) + } + + // Display current week summary + if err := displayWeekSummary(ctx, cmd, q); err != nil { + return fmt.Errorf("failed to display week summary: %w", err) + } + + // Display current month summary + if err := displayMonthSummary(ctx, cmd, q); err != nil { + return fmt.Errorf("failed to display month summary: %w", err) + } + + return nil + }, + } + + cmd.Flags().BoolP("clients", "c", false, "Show clients list") + cmd.Flags().BoolP("projects", "p", false, "Show projects list") + + return cmd +} + +func displayClientsAndProjects(ctx context.Context, cmd *cobra.Command, q *queries.Queries, showClients, showProjects bool) error { + if showClients && showProjects { + cmd.Printf("📋 Clients & Projects\n") + } else if showClients { + cmd.Printf("👥 Clients\n") + } else if showProjects { + cmd.Printf("📁 Projects\n") + } + + clients, err := q.ListAllClients(ctx) + if err != nil { + return fmt.Errorf("failed to get clients: %w", err) + } + + projects, err := q.ListAllProjects(ctx) + if err != nil { + return fmt.Errorf("failed to get projects: %w", err) + } + + if len(clients) == 0 { + cmd.Printf(" No clients found\n\n") + return nil + } + + // Group projects by client + projectsByClient := make(map[int64][]queries.ListAllProjectsRow) + for _, project := range projects { + projectsByClient[project.ClientID] = append(projectsByClient[project.ClientID], project) + } + + if showClients && showProjects { + // Show clients with their projects nested + for _, client := range clients { + email := "" + if client.Email.Valid { + email = fmt.Sprintf(" <%s>", client.Email.String) + } + rate := "" + if client.BillableRate.Valid { + rateInDollars := float64(client.BillableRate.Int64) / 100.0 + rate = fmt.Sprintf(" - $%.2f/hr", rateInDollars) + } + cmd.Printf(" • %s%s (ID: %d)%s\n", client.Name, email, client.ID, rate) + + clientProjects := projectsByClient[client.ID] + if len(clientProjects) == 0 { + cmd.Printf(" └── (no projects)\n") + } else { + for i, project := range clientProjects { + prefix := "├──" + if i == len(clientProjects)-1 { + prefix = "└──" + } + rate := "" + if project.BillableRate.Valid { + rateInDollars := float64(project.BillableRate.Int64) / 100.0 + rate = fmt.Sprintf(" - $%.2f/hr", rateInDollars) + } + cmd.Printf(" %s %s (ID: %d)%s\n", prefix, project.Name, project.ID, rate) + } + } + } + } else if showClients { + // Show only clients + for _, client := range clients { + email := "" + if client.Email.Valid { + email = fmt.Sprintf(" <%s>", client.Email.String) + } + rate := "" + if client.BillableRate.Valid { + rateInDollars := float64(client.BillableRate.Int64) / 100.0 + rate = fmt.Sprintf(" - $%.2f/hr", rateInDollars) + } + cmd.Printf(" • %s%s (ID: %d)%s\n", client.Name, email, client.ID, rate) + } + } else if showProjects { + // Show only projects with their client names + for _, project := range projects { + rate := "" + if project.BillableRate.Valid { + rateInDollars := float64(project.BillableRate.Int64) / 100.0 + rate = fmt.Sprintf(" - $%.2f/hr", rateInDollars) + } + cmd.Printf(" • %s (Client: %s, ID: %d)%s\n", project.Name, project.ClientName, project.ID, rate) + } + } + cmd.Printf("\n") + return nil +} + +func displayWeekSummary(ctx context.Context, cmd *cobra.Command, q *queries.Queries) error { + cmd.Printf("📅 This Week\n") + + weekSummary, err := q.GetWeekSummaryByProject(ctx) + if err != nil { + return fmt.Errorf("failed to get week summary: %w", err) + } + + if len(weekSummary) == 0 { + cmd.Printf(" No time entries this week\n\n") + return nil + } + + // Group by client and calculate totals + clientTotals := make(map[int64]time.Duration) + currentClientID := int64(-1) + + for _, row := range weekSummary { + duration := time.Duration(row.TotalSeconds) * time.Second + clientTotals[row.ClientID] += duration + + if row.ClientID != currentClientID { + if currentClientID != -1 { + // Print client total + cmd.Printf(" Total: %v\n", clientTotals[currentClientID].Round(time.Minute)) + } + cmd.Printf(" • %s:\n", row.ClientName) + currentClientID = row.ClientID + } + + projectName := "(no project)" + if row.ProjectName.Valid { + projectName = row.ProjectName.String + } + cmd.Printf(" - %s: %v\n", projectName, duration.Round(time.Minute)) + } + + // Print final client total + if currentClientID != -1 { + cmd.Printf(" Total: %v\n", clientTotals[currentClientID].Round(time.Minute)) + } + + // Print grand total + var grandTotal time.Duration + for _, total := range clientTotals { + grandTotal += total + } + cmd.Printf(" WEEK TOTAL: %v\n\n", grandTotal.Round(time.Minute)) + + return nil +} + +func displayMonthSummary(ctx context.Context, cmd *cobra.Command, q *queries.Queries) error { + cmd.Printf("📊 This Month\n") + + monthSummary, err := q.GetMonthSummaryByProject(ctx) + if err != nil { + return fmt.Errorf("failed to get month summary: %w", err) + } + + if len(monthSummary) == 0 { + cmd.Printf(" No time entries this month\n\n") + return nil + } + + // Group by client and calculate totals + clientTotals := make(map[int64]time.Duration) + currentClientID := int64(-1) + + for _, row := range monthSummary { + duration := time.Duration(row.TotalSeconds) * time.Second + clientTotals[row.ClientID] += duration + + if row.ClientID != currentClientID { + if currentClientID != -1 { + // Print client total + cmd.Printf(" Total: %v\n", clientTotals[currentClientID].Round(time.Minute)) + } + cmd.Printf(" • %s:\n", row.ClientName) + currentClientID = row.ClientID + } + + projectName := "(no project)" + if row.ProjectName.Valid { + projectName = row.ProjectName.String + } + cmd.Printf(" - %s: %v\n", projectName, duration.Round(time.Minute)) + } + + // Print final client total + if currentClientID != -1 { + cmd.Printf(" Total: %v\n", clientTotals[currentClientID].Round(time.Minute)) + } + + // Print grand total + var grandTotal time.Duration + for _, total := range clientTotals { + grandTotal += total + } + cmd.Printf(" MONTH TOTAL: %v\n\n", grandTotal.Round(time.Minute)) + + return nil +} diff --git a/internal/commands/status_test.go b/internal/commands/status_test.go new file mode 100644 index 0000000..7244993 --- /dev/null +++ b/internal/commands/status_test.go @@ -0,0 +1,534 @@ +package commands + +import ( + "context" + "database/sql" + "strings" + "testing" + + "punchcard/internal/queries" +) + +func TestStatusCommand(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) error + expectedContains []string + expectedNotContains []string + }{ + { + name: "status with no data", + setupData: func(q *queries.Queries) error { + return nil // No setup needed + }, + expectedContains: []string{ + "⚪ No active timer", + "📅 This Week", + "No time entries this week", + "📊 This Month", + "No time entries this month", + }, + }, + { + name: "status with active timer", + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ActiveClient", + Email: sql.NullString{String: "active@client.com", Valid: true}, + }) + if err != nil { + return err + } + + project, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Active Project", + ClientID: client.ID, + }) + if err != nil { + return err + } + + _, err = q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Working on tests", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{Int64: project.ID, Valid: true}, + }) + return err + }, + expectedContains: []string{ + "🔴 Active Timer", + "Client: ActiveClient", + "Project: Active Project", + "Description: Working on tests", + }, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + if err := tt.setupData(q); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + + // Execute status command + output, err := executeCommandWithDB(t, q, "status") + if err != nil { + t.Fatalf("Unexpected error: %v", err) + } + + // Check expected content is present + for _, expected := range tt.expectedContains { + if !strings.Contains(output, expected) { + t.Errorf("Expected output to contain %q, but got:\n%s", expected, output) + } + } + + // Check that unwanted content is not present + for _, notExpected := range tt.expectedNotContains { + if strings.Contains(output, notExpected) { + t.Errorf("Expected output to NOT contain %q, but got:\n%s", notExpected, output) + } + } + }) + } +} + +func TestStatusCommandAliases(t *testing.T) { + tests := []struct { + name string + args []string + }{ + {"status command", []string{"status"}}, + {"st alias", []string{"st"}}, + {"default command", []string{}}, // No subcommand should default to status + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Create minimal test data + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestClient", + }) + if err != nil { + t.Fatalf("Failed to create test client: %v", err) + } + + // Execute command + output, err := executeCommandWithDB(t, q, tt.args...) + if err != nil { + t.Fatalf("Unexpected error: %v", err) + } + + // Should always contain the basic status structure + expectedSections := []string{"This Week", "This Month"} + for _, section := range expectedSections { + if !strings.Contains(output, section) { + t.Errorf("Expected output to contain %q section, but got:\n%s", section, output) + } + } + }) + } +} + +func TestStatusCommandWithRecentEntry(t *testing.T) { + tests := []struct { + name string + setupData func(*queries.Queries) error + expectedContains []string + expectedNotContains []string + }{ + { + name: "status shows most recent entry when no active timer", + setupData: func(q *queries.Queries) error { + // Create client and project + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + Email: sql.NullString{String: "test@testcorp.com", Valid: true}, + }) + if err != nil { + return err + } + + project, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Website Project", + ClientID: client.ID, + }) + if err != nil { + return err + } + + // Create a completed time entry + if _, err := q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Working on tests", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{Int64: project.ID, Valid: true}, + }); err != nil { + return err + } + + // Stop the entry to make it completed + _, err = q.StopTimeEntry(context.Background()) + return err + }, + expectedContains: []string{ + "⚪ No active timer", + "📝 Most Recent Entry", + "Client: TestCorp", + "Project: Website Project", + "Description: Working on tests", + "Started:", + "Ended:", + "Duration:", + }, + expectedNotContains: []string{ + "🔴 Active Timer", + }, + }, + { + name: "status with no time entries shows no recent entry", + setupData: func(q *queries.Queries) error { + // Create client but no time entries + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + }) + return err + }, + expectedContains: []string{ + "⚪ No active timer", + }, + expectedNotContains: []string{ + "📝 Most Recent Entry", + "🔴 Active Timer", + }, + }, + { + name: "status with active timer does not show recent entry", + setupData: func(q *queries.Queries) error { + // Create client and project + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + }) + if err != nil { + return err + } + + project, err := q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Active Project", + ClientID: client.ID, + }) + if err != nil { + return err + } + + // Create an active time entry (not stopped) + _, err = q.CreateTimeEntry(context.Background(), queries.CreateTimeEntryParams{ + Description: sql.NullString{String: "Currently working", Valid: true}, + ClientID: client.ID, + ProjectID: sql.NullInt64{Int64: project.ID, Valid: true}, + }) + return err + }, + expectedContains: []string{ + "🔴 Active Timer", + "Client: TestCorp", + "Project: Active Project", + "Description: Currently working", + }, + expectedNotContains: []string{ + "⚪ No active timer", + "📝 Most Recent Entry", + }, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + if err := tt.setupData(q); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + + // Execute command + output, err := executeCommandWithDB(t, q, "status") + if err != nil { + t.Fatalf("Unexpected error: %v", err) + } + + // Check expected content + for _, expected := range tt.expectedContains { + if !strings.Contains(output, expected) { + t.Errorf("Expected output to contain %q, but got:\n%s", expected, output) + } + } + + // Check unexpected content + for _, unexpected := range tt.expectedNotContains { + if strings.Contains(output, unexpected) { + t.Errorf("Expected output to NOT contain %q, but got:\n%s", unexpected, output) + } + } + }) + } +} + +func TestStatusCommandFlags(t *testing.T) { + tests := []struct { + name string + args []string + setupData func(*queries.Queries) error + expectedContains []string + expectedNotContains []string + }{ + { + name: "status with -c flag shows clients only", + args: []string{"status", "-c"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + Email: sql.NullString{String: "test@testcorp.com", Valid: true}, + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Website Redesign", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "👥 Clients", + "• TestCorp (ID: 1)", + "📅 This Week", + "📊 This Month", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "📁 Projects", + "└── Website Redesign (ID: 1)", // Should not show project details + }, + }, + { + name: "status with --clients flag shows clients only", + args: []string{"status", "--clients"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "LongFlagTest", + Email: sql.NullString{String: "long@flag.com", Valid: true}, + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Test Project", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "👥 Clients", + "• LongFlagTest (ID: 1)", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "📁 Projects", + "└── Test Project (ID: 1)", + }, + }, + { + name: "status with -p flag shows projects only", + args: []string{"status", "-p"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + Email: sql.NullString{String: "test@testcorp.com", Valid: true}, + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Website Redesign", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "📁 Projects", + "• Website Redesign (Client: TestCorp, ID: 1)", + "📅 This Week", + "📊 This Month", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "👥 Clients", + "• TestCorp (ID: 1)", // Should not show client details + }, + }, + { + name: "status with --projects flag shows projects only", + args: []string{"status", "--projects"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "LongFlagTest", + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Long Flag Project", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "📁 Projects", + "• Long Flag Project (Client: LongFlagTest, ID: 1)", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "👥 Clients", + }, + }, + { + name: "status with -c -p flags shows both", + args: []string{"status", "-c", "-p"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "TestCorp", + Email: sql.NullString{String: "test@testcorp.com", Valid: true}, + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Website Redesign", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "📋 Clients & Projects", + "• TestCorp (ID: 1)", + "└── Website Redesign (ID: 1)", + "📅 This Week", + "📊 This Month", + }, + expectedNotContains: []string{ + "👥 Clients", + "📁 Projects", + }, + }, + { + name: "status with --clients --projects flags shows both", + args: []string{"status", "--clients", "--projects"}, + setupData: func(q *queries.Queries) error { + client, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "BothFlagsTest", + }) + if err != nil { + return err + } + + _, err = q.CreateProject(context.Background(), queries.CreateProjectParams{ + Name: "Both Flags Project", + ClientID: client.ID, + }) + return err + }, + expectedContains: []string{ + "📋 Clients & Projects", + "• BothFlagsTest (ID: 1)", + "└── Both Flags Project (ID: 1)", + }, + expectedNotContains: []string{ + "👥 Clients", + "📁 Projects", + }, + }, + { + name: "status with -c flag and no clients shows no clients message", + args: []string{"status", "-c"}, + setupData: func(q *queries.Queries) error { + return nil // No setup needed + }, + expectedContains: []string{ + "👥 Clients", + "No clients found", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "📁 Projects", + }, + }, + { + name: "status with -p flag and no projects shows projects header but no projects", + args: []string{"status", "-p"}, + setupData: func(q *queries.Queries) error { + // Create a client but no projects + _, err := q.CreateClient(context.Background(), queries.CreateClientParams{ + Name: "ClientWithoutProjects", + }) + return err + }, + expectedContains: []string{ + "📁 Projects", + }, + expectedNotContains: []string{ + "📋 Clients & Projects", + "👥 Clients", + "• ClientWithoutProjects", // Should not show client in projects-only view + }, + }, + } + + for _, tt := range tests { + t.Run(tt.name, func(t *testing.T) { + // Setup fresh database for each test + q, cleanup := setupTestDB(t) + defer cleanup() + + // Setup test data + if err := tt.setupData(q); err != nil { + t.Fatalf("Failed to setup test data: %v", err) + } + + // Execute command with flags + output, err := executeCommandWithDB(t, q, tt.args...) + if err != nil { + t.Fatalf("Unexpected error: %v", err) + } + + // Check expected content is present + for _, expected := range tt.expectedContains { + if !strings.Contains(output, expected) { + t.Errorf("Expected output to contain %q, but got:\n%s", expected, output) + } + } + + // Check that unwanted content is not present + for _, notExpected := range tt.expectedNotContains { + if strings.Contains(output, notExpected) { + t.Errorf("Expected output to NOT contain %q, but got:\n%s", notExpected, output) + } + } + }) + } +} diff --git a/internal/commands/test_utils.go b/internal/commands/test_utils.go new file mode 100644 index 0000000..282e472 --- /dev/null +++ b/internal/commands/test_utils.go @@ -0,0 +1,48 @@ +package commands + +import ( + "bytes" + "context" + "database/sql" + "testing" + + punchctx "punchcard/internal/context" + "punchcard/internal/database" + "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) + + // Return cleanup function that restores environment immediately + cleanup := func() { + if err := q.DBTX().(*sql.DB).Close(); err != nil { + t.Logf("error closing database: %v", err) + } + } + + return q, cleanup +} + +func executeCommandWithDB(t *testing.T, q *queries.Queries, args ...string) (string, error) { + buf := new(bytes.Buffer) + + // Create context with provided database + ctx := punchctx.WithDB(context.Background(), q) + + // Use factory functions to create fresh command instances for each test + testRootCmd := NewRootCmd() + testRootCmd.SetOut(buf) + testRootCmd.SetErr(buf) + testRootCmd.SetArgs(args) + + err := testRootCmd.ExecuteContext(ctx) + return buf.String(), err +} diff --git a/internal/commands/testdata/clockify_extra_columns.csv b/internal/commands/testdata/clockify_extra_columns.csv new file mode 100644 index 0000000..a563ddc --- /dev/null +++ b/internal/commands/testdata/clockify_extra_columns.csv @@ -0,0 +1,3 @@ +"Project","Client","Description","Start Date","Start Time","End Date","End Time","Billable","Tags","User","Email","Extra 1","Extra 2","Extra 3" +"Analytics Dashboard","DataCorp","Performance monitoring","01/25/2024","10:00:00 AM","01/25/2024","02:15:00 PM","Yes","analytics,monitoring","Eve Green","eve@datacorp.com","unused1","unused2","unused3" +"API Development","DataCorp","REST endpoint creation","01/26/2024","09:30:00 AM","01/26/2024","11:45:00 AM","Yes","backend,api","Frank Miller","frank@datacorp.com","more unused","data here","final column" \ No newline at end of file diff --git a/internal/commands/testdata/clockify_full.csv b/internal/commands/testdata/clockify_full.csv new file mode 100644 index 0000000..766f14d --- /dev/null +++ b/internal/commands/testdata/clockify_full.csv @@ -0,0 +1,4 @@ +"Project","Client","Description","Task","User","Group","Email","Tags","Billable","Start Date","Start Time","End Date","End Time","Duration (h)","Duration (decimal)","Billable Rate (USD)","Billable Amount (USD)" +"Project Alpha","Acme Corp","Initial development work","","John Doe","Dev Team","johndoe@example.com","frontend,react","Yes","01/15/2024","09:00:00 AM","01/15/2024","11:30:00 AM","02:30:00","2.50","75.00","187.50" +"Project Beta","Acme Corp","Code review","","John Doe","Dev Team","johndoe@example.com","backend,review","Yes","01/15/2024","02:00:00 PM","01/15/2024","03:15:00 PM","01:15:00","1.25","75.00","93.75" +"Website Redesign","Creative Co","UI mockups","","Jane Smith","Design Team","janesmith@example.com","design,ui","No","01/16/2024","10:00:00 AM","01/16/2024","12:00:00 PM","02:00:00","2.00","0.00","0.00" \ No newline at end of file diff --git a/internal/commands/testdata/clockify_invalid.csv b/internal/commands/testdata/clockify_invalid.csv new file mode 100644 index 0000000..1d5feb3 --- /dev/null +++ b/internal/commands/testdata/clockify_invalid.csv @@ -0,0 +1,2 @@ +"Project","Client","Description","Task","User" +"Incomplete Project","Test Client","Missing columns" \ No newline at end of file diff --git a/internal/commands/testdata/clockify_missing_data.csv b/internal/commands/testdata/clockify_missing_data.csv new file mode 100644 index 0000000..91be6cd --- /dev/null +++ b/internal/commands/testdata/clockify_missing_data.csv @@ -0,0 +1,4 @@ +"Project","Client","Description","Task","User","Group","Email","Tags","Billable","Start Date","Start Time","End Date","End Time","Duration (h)","Duration (decimal)" +"Valid Project","Valid Client","Valid work","","User","","user@example.com","","Yes","01/20/2024","09:00:00 AM","01/20/2024","10:00:00 AM","01:00:00","1.00" +"","Missing Client","Work without client","","User","","user@example.com","","Yes","01/20/2024","11:00:00 AM","01/20/2024","12:00:00 PM","01:00:00","1.00" +"Missing Project","","Work without project","","User","","user@example.com","","Yes","01/20/2024","01:00:00 PM","01/20/2024","02:00:00 PM","01:00:00","1.00" \ No newline at end of file diff --git a/internal/commands/testdata/clockify_no_billable.csv b/internal/commands/testdata/clockify_no_billable.csv new file mode 100644 index 0000000..9cb34cb --- /dev/null +++ b/internal/commands/testdata/clockify_no_billable.csv @@ -0,0 +1,3 @@ +"Project","Client","Description","Task","User","Group","Email","Tags","Billable","Start Date","Start Time","End Date","End Time","Duration (h)","Duration (decimal)" +"Project Gamma","TechStart Inc","Database optimization","","Bob Wilson","Backend Team","bobwilson@example.com","database,optimization","Yes","01/17/2024","08:30:00 AM","01/17/2024","10:45:00 AM","02:15:00","2.25" +"Mobile App","TechStart Inc","Feature implementation","","Alice Brown","Mobile Team","alicebrown@example.com","mobile,ios","Yes","01/17/2024","01:00:00 PM","01/17/2024","04:30:00 PM","03:30:00","3.50" \ No newline at end of file diff --git a/internal/commands/testdata/clockify_reordered.csv b/internal/commands/testdata/clockify_reordered.csv new file mode 100644 index 0000000..7d24075 --- /dev/null +++ b/internal/commands/testdata/clockify_reordered.csv @@ -0,0 +1,3 @@ +"Client","Start Date","End Date","Project","Description","Start Time","End Time","Extra Field 1","Task","User","Extra Field 2" +"MegaCorp Inc","01/20/2024","01/20/2024","Website Overhaul","Frontend redesign","09:15:00 AM","12:30:00 PM","some extra data","","Charlie Davis","more extra" +"StartupCo","01/21/2024","01/21/2024","Mobile Beta","Bug fixes","02:00:00 PM","05:45:00 PM","random value","","Diana Lee","another column" \ No newline at end of file diff --git a/internal/context/db.go b/internal/context/db.go new file mode 100644 index 0000000..a9f53d3 --- /dev/null +++ b/internal/context/db.go @@ -0,0 +1,23 @@ +package context + +import ( + "context" + + "punchcard/internal/queries" +) + +type dbContextKey struct{} + +// WithDB returns a new context with the database queries instance +func WithDB(ctx context.Context, q *queries.Queries) context.Context { + return context.WithValue(ctx, dbContextKey{}, q) +} + +// GetDB retrieves the database queries instance from context +func GetDB(ctx context.Context) *queries.Queries { + if q, ok := ctx.Value(dbContextKey{}).(*queries.Queries); ok { + return q + } + return nil +} + diff --git a/internal/database/db.go b/internal/database/db.go new file mode 100644 index 0000000..f699d14 --- /dev/null +++ b/internal/database/db.go @@ -0,0 +1,68 @@ +package database + +import ( + "database/sql" + _ "embed" + "fmt" + "os" + "path/filepath" + + "punchcard/internal/queries" + + _ "modernc.org/sqlite" +) + +//go:embed schema.sql +var schema string + +func GetDB() (*queries.Queries, error) { + dataDir := os.Getenv("XDG_DATA_HOME") + if dataDir == "" { + homeDir, err := os.UserHomeDir() + if err != nil { + return nil, fmt.Errorf("failed to get user home directory: %w", err) + } + dataDir = filepath.Join(homeDir, ".local", "share") + } + + punchcardDir := filepath.Join(dataDir, "punchcard") + if err := os.MkdirAll(punchcardDir, 0o755); err != nil { + return nil, fmt.Errorf("failed to create punchcard directory at %s: %w", punchcardDir, err) + } + + dbPath := filepath.Join(punchcardDir, "punchcard.db") + db, err := sql.Open("sqlite", dbPath) + if err != nil { + return nil, fmt.Errorf("failed to open database at %s: %w", dbPath, err) + } + + if err := InitializeDB(db); err != nil { + return nil, err + } + + return queries.New(db), nil +} + +func InitializeDB(db *sql.DB) error { + if _, err := db.Exec(schema); err != nil { + return fmt.Errorf("failed to execute schema: %w", err) + } + + pragmas := []string{ + "PRAGMA foreign_keys = ON;", + "PRAGMA journal_mode = WAL;", + "PRAGMA synchronous = NORMAL;", + "PRAGMA cache_size = -64000;", + "PRAGMA temp_store = MEMORY;", + "PRAGMA mmap_size = 67108864;", + "PRAGMA optimize;", + } + + for _, pragma := range pragmas { + if _, err := db.Exec(pragma); err != nil { + return fmt.Errorf("failed to execute pragma %s: %w", pragma, err) + } + } + + return nil +} diff --git a/internal/database/queries.sql b/internal/database/queries.sql new file mode 100644 index 0000000..b798cbf --- /dev/null +++ b/internal/database/queries.sql @@ -0,0 +1,132 @@ +-- name: CreateClient :one +insert into client (name, email, billable_rate) +values (@name, @email, @billable_rate) +returning *; + +-- name: FindClient :many +select c1.id, c1.name, c1.email, c1.billable_rate, c1.created_at from client c1 where c1.id = cast(@id as integer) +union all +select c2.id, c2.name, c2.email, c2.billable_rate, c2.created_at from client c2 where c2.name = @name; + +-- name: CreateProject :one +insert into project (name, client_id, billable_rate) +values (@name, @client_id, @billable_rate) +returning *; + +-- name: FindProject :many +select p1.id, p1.name, p1.client_id, p1.billable_rate, p1.created_at from project p1 where p1.id = cast(@id as integer) +union all +select p2.id, p2.name, p2.client_id, p2.billable_rate, p2.created_at from project p2 where p2.name = @name; + +-- name: CreateTimeEntry :one +insert into time_entry (start_time, description, client_id, project_id, billable_rate) +values ( + datetime('now', 'utc'), + @description, + @client_id, + @project_id, + coalesce( + @billable_rate, + (select p.billable_rate from project p where p.id = @project_id), + (select c.billable_rate from client c where c.id = @client_id) + ) +) +returning *; + +-- name: GetActiveTimeEntry :one +select * from time_entry +where end_time is null +order by start_time desc +limit 1; + +-- name: StopTimeEntry :one +update time_entry +set end_time = datetime('now', 'utc') +where id = ( + select id + from time_entry + where end_time is null + order by start_time desc + limit 1 +) +returning *; + +-- name: GetMostRecentTimeEntry :one +select * from time_entry +order by start_time desc +limit 1; + +-- name: ListAllClients :many +select * from client +order by name; + +-- name: ListAllProjects :many +select p.*, c.name as client_name from project p +join client c on p.client_id = c.id +order by c.name, p.name; + +-- name: GetWeekSummaryByProject :many +select + p.id as project_id, + p.name as project_name, + c.id as client_id, + c.name as client_name, + cast(sum( + case + when te.end_time is null then + (julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60 + else + (julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60 + end + ) as integer) as total_seconds +from time_entry te +join client c on te.client_id = c.id +left join project p on te.project_id = p.id +where date(te.start_time) >= date('now', 'weekday 1', '-6 days') + and date(te.start_time) <= date('now') +group by p.id, p.name, c.id, c.name +order by c.name, p.name; + +-- name: GetMonthSummaryByProject :many +select + p.id as project_id, + p.name as project_name, + c.id as client_id, + c.name as client_name, + cast(sum( + case + when te.end_time is null then + (julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60 + else + (julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60 + end + ) as integer) as total_seconds +from time_entry te +join client c on te.client_id = c.id +left join project p on te.project_id = p.id +where date(te.start_time) >= date('now', 'start of month') + and date(te.start_time) <= date('now') +group by p.id, p.name, c.id, c.name +order by c.name, p.name; + +-- name: GetClientByName :one +select * from client where name = @name limit 1; + +-- name: GetProjectByNameAndClient :one +select * from project where name = @name and client_id = @client_id limit 1; + +-- name: CreateTimeEntryWithTimes :one +insert into time_entry (start_time, end_time, description, client_id, project_id, billable_rate) +values ( + @start_time, + @end_time, + @description, + @client_id, + @project_id, + coalesce( + @billable_rate, + (select p.billable_rate from project p where p.id = @project_id), + (select c.billable_rate from client c where c.id = @client_id) + ) +) +returning *; diff --git a/internal/database/schema.sql b/internal/database/schema.sql new file mode 100644 index 0000000..a4483e1 --- /dev/null +++ b/internal/database/schema.sql @@ -0,0 +1,28 @@ +create table if not exists client ( + id integer primary key autoincrement, + name text not null unique, + email text, + billable_rate integer, + created_at datetime default current_timestamp +); + +create table if not exists project ( + id integer primary key autoincrement, + name text not null unique, + client_id integer not null, + billable_rate integer, + created_at datetime default current_timestamp, + foreign key (client_id) references client(id) +); + +create table if not exists time_entry ( + id integer primary key autoincrement, + start_time datetime not null, + end_time datetime, + description text, + client_id integer not null, + project_id integer, + billable_rate integer, + foreign key (client_id) references client(id), + foreign key (project_id) references project(id) +); diff --git a/internal/queries/db.go b/internal/queries/db.go new file mode 100644 index 0000000..85679b3 --- /dev/null +++ b/internal/queries/db.go @@ -0,0 +1,31 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 + +package queries + +import ( + "context" + "database/sql" +) + +type DBTX interface { + ExecContext(context.Context, string, ...interface{}) (sql.Result, error) + PrepareContext(context.Context, string) (*sql.Stmt, error) + QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error) + QueryRowContext(context.Context, string, ...interface{}) *sql.Row +} + +func New(db DBTX) *Queries { + return &Queries{db: db} +} + +type Queries struct { + db DBTX +} + +func (q *Queries) WithTx(tx *sql.Tx) *Queries { + return &Queries{ + db: tx, + } +} diff --git a/internal/queries/dbtx.go b/internal/queries/dbtx.go new file mode 100644 index 0000000..ba96dfb --- /dev/null +++ b/internal/queries/dbtx.go @@ -0,0 +1,44 @@ +package queries + +import ( + "context" + "database/sql" + "errors" + "fmt" +) + +func (q *Queries) DBTX() DBTX { + return q.db +} + +func (q *Queries) InTx( + ctx context.Context, + opts *sql.TxOptions, + body func(context.Context, *Queries) error, +) error { + var tx *sql.Tx + var err error + + switch db := q.db.(type) { + case *sql.Tx: + return body(ctx, q) + case interface { + BeginTx(context.Context, *sql.TxOptions) (*sql.Tx, error) + }: + tx, err = db.BeginTx(ctx, opts) + if err != nil { + return fmt.Errorf("Queries.InTx failed to create tx: %w", err) + } + defer func() { + if err == nil { + _ = tx.Commit() + } else { + _ = tx.Rollback() + } + }() + err = body(ctx, New(tx)) + return err + default: + return errors.New("Queries.InTx: invalid DBTX type") + } +} diff --git a/internal/queries/models.go b/internal/queries/models.go new file mode 100644 index 0000000..1257397 --- /dev/null +++ b/internal/queries/models.go @@ -0,0 +1,36 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 + +package queries + +import ( + "database/sql" + "time" +) + +type Client struct { + ID int64 + Name string + Email sql.NullString + BillableRate sql.NullInt64 + CreatedAt sql.NullTime +} + +type Project struct { + ID int64 + Name string + ClientID int64 + BillableRate sql.NullInt64 + CreatedAt sql.NullTime +} + +type TimeEntry struct { + ID int64 + StartTime time.Time + EndTime sql.NullTime + Description sql.NullString + ClientID int64 + ProjectID sql.NullInt64 + BillableRate sql.NullInt64 +} diff --git a/internal/queries/queries.sql.go b/internal/queries/queries.sql.go new file mode 100644 index 0000000..3da8b98 --- /dev/null +++ b/internal/queries/queries.sql.go @@ -0,0 +1,542 @@ +// Code generated by sqlc. DO NOT EDIT. +// versions: +// sqlc v1.29.0 +// source: queries.sql + +package queries + +import ( + "context" + "database/sql" + "time" +) + +const createClient = `-- name: CreateClient :one +insert into client (name, email, billable_rate) +values (?1, ?2, ?3) +returning id, name, email, billable_rate, created_at +` + +type CreateClientParams struct { + Name string + Email sql.NullString + BillableRate sql.NullInt64 +} + +func (q *Queries) CreateClient(ctx context.Context, arg CreateClientParams) (Client, error) { + row := q.db.QueryRowContext(ctx, createClient, arg.Name, arg.Email, arg.BillableRate) + var i Client + err := row.Scan( + &i.ID, + &i.Name, + &i.Email, + &i.BillableRate, + &i.CreatedAt, + ) + return i, err +} + +const createProject = `-- name: CreateProject :one +insert into project (name, client_id, billable_rate) +values (?1, ?2, ?3) +returning id, name, client_id, billable_rate, created_at +` + +type CreateProjectParams struct { + Name string + ClientID int64 + BillableRate sql.NullInt64 +} + +func (q *Queries) CreateProject(ctx context.Context, arg CreateProjectParams) (Project, error) { + row := q.db.QueryRowContext(ctx, createProject, arg.Name, arg.ClientID, arg.BillableRate) + var i Project + err := row.Scan( + &i.ID, + &i.Name, + &i.ClientID, + &i.BillableRate, + &i.CreatedAt, + ) + return i, err +} + +const createTimeEntry = `-- name: CreateTimeEntry :one +insert into time_entry (start_time, description, client_id, project_id, billable_rate) +values ( + datetime('now', 'utc'), + ?1, + ?2, + ?3, + coalesce( + ?4, + (select p.billable_rate from project p where p.id = ?3), + (select c.billable_rate from client c where c.id = ?2) + ) +) +returning id, start_time, end_time, description, client_id, project_id, billable_rate +` + +type CreateTimeEntryParams struct { + Description sql.NullString + ClientID int64 + ProjectID sql.NullInt64 + BillableRate interface{} +} + +func (q *Queries) CreateTimeEntry(ctx context.Context, arg CreateTimeEntryParams) (TimeEntry, error) { + row := q.db.QueryRowContext(ctx, createTimeEntry, + arg.Description, + arg.ClientID, + arg.ProjectID, + arg.BillableRate, + ) + var i TimeEntry + err := row.Scan( + &i.ID, + &i.StartTime, + &i.EndTime, + &i.Description, + &i.ClientID, + &i.ProjectID, + &i.BillableRate, + ) + return i, err +} + +const createTimeEntryWithTimes = `-- name: CreateTimeEntryWithTimes :one +insert into time_entry (start_time, end_time, description, client_id, project_id, billable_rate) +values ( + ?1, + ?2, + ?3, + ?4, + ?5, + coalesce( + ?6, + (select p.billable_rate from project p where p.id = ?5), + (select c.billable_rate from client c where c.id = ?4) + ) +) +returning id, start_time, end_time, description, client_id, project_id, billable_rate +` + +type CreateTimeEntryWithTimesParams struct { + StartTime time.Time + EndTime sql.NullTime + Description sql.NullString + ClientID int64 + ProjectID sql.NullInt64 + BillableRate interface{} +} + +func (q *Queries) CreateTimeEntryWithTimes(ctx context.Context, arg CreateTimeEntryWithTimesParams) (TimeEntry, error) { + row := q.db.QueryRowContext(ctx, createTimeEntryWithTimes, + arg.StartTime, + arg.EndTime, + arg.Description, + arg.ClientID, + arg.ProjectID, + arg.BillableRate, + ) + var i TimeEntry + err := row.Scan( + &i.ID, + &i.StartTime, + &i.EndTime, + &i.Description, + &i.ClientID, + &i.ProjectID, + &i.BillableRate, + ) + return i, err +} + +const findClient = `-- name: FindClient :many +select c1.id, c1.name, c1.email, c1.billable_rate, c1.created_at from client c1 where c1.id = cast(?1 as integer) +union all +select c2.id, c2.name, c2.email, c2.billable_rate, c2.created_at from client c2 where c2.name = ?2 +` + +type FindClientParams struct { + ID int64 + Name string +} + +func (q *Queries) FindClient(ctx context.Context, arg FindClientParams) ([]Client, error) { + rows, err := q.db.QueryContext(ctx, findClient, arg.ID, arg.Name) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Client + for rows.Next() { + var i Client + if err := rows.Scan( + &i.ID, + &i.Name, + &i.Email, + &i.BillableRate, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const findProject = `-- name: FindProject :many +select p1.id, p1.name, p1.client_id, p1.billable_rate, p1.created_at from project p1 where p1.id = cast(?1 as integer) +union all +select p2.id, p2.name, p2.client_id, p2.billable_rate, p2.created_at from project p2 where p2.name = ?2 +` + +type FindProjectParams struct { + ID int64 + Name string +} + +func (q *Queries) FindProject(ctx context.Context, arg FindProjectParams) ([]Project, error) { + rows, err := q.db.QueryContext(ctx, findProject, arg.ID, arg.Name) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Project + for rows.Next() { + var i Project + if err := rows.Scan( + &i.ID, + &i.Name, + &i.ClientID, + &i.BillableRate, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getActiveTimeEntry = `-- name: GetActiveTimeEntry :one +select id, start_time, end_time, description, client_id, project_id, billable_rate from time_entry +where end_time is null +order by start_time desc +limit 1 +` + +func (q *Queries) GetActiveTimeEntry(ctx context.Context) (TimeEntry, error) { + row := q.db.QueryRowContext(ctx, getActiveTimeEntry) + var i TimeEntry + err := row.Scan( + &i.ID, + &i.StartTime, + &i.EndTime, + &i.Description, + &i.ClientID, + &i.ProjectID, + &i.BillableRate, + ) + return i, err +} + +const getClientByName = `-- name: GetClientByName :one +select id, name, email, billable_rate, created_at from client where name = ?1 limit 1 +` + +func (q *Queries) GetClientByName(ctx context.Context, name string) (Client, error) { + row := q.db.QueryRowContext(ctx, getClientByName, name) + var i Client + err := row.Scan( + &i.ID, + &i.Name, + &i.Email, + &i.BillableRate, + &i.CreatedAt, + ) + return i, err +} + +const getMonthSummaryByProject = `-- name: GetMonthSummaryByProject :many +select + p.id as project_id, + p.name as project_name, + c.id as client_id, + c.name as client_name, + cast(sum( + case + when te.end_time is null then + (julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60 + else + (julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60 + end + ) as integer) as total_seconds +from time_entry te +join client c on te.client_id = c.id +left join project p on te.project_id = p.id +where date(te.start_time) >= date('now', 'start of month') + and date(te.start_time) <= date('now') +group by p.id, p.name, c.id, c.name +order by c.name, p.name +` + +type GetMonthSummaryByProjectRow struct { + ProjectID sql.NullInt64 + ProjectName sql.NullString + ClientID int64 + ClientName string + TotalSeconds int64 +} + +func (q *Queries) GetMonthSummaryByProject(ctx context.Context) ([]GetMonthSummaryByProjectRow, error) { + rows, err := q.db.QueryContext(ctx, getMonthSummaryByProject) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetMonthSummaryByProjectRow + for rows.Next() { + var i GetMonthSummaryByProjectRow + if err := rows.Scan( + &i.ProjectID, + &i.ProjectName, + &i.ClientID, + &i.ClientName, + &i.TotalSeconds, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const getMostRecentTimeEntry = `-- name: GetMostRecentTimeEntry :one +select id, start_time, end_time, description, client_id, project_id, billable_rate from time_entry +order by start_time desc +limit 1 +` + +func (q *Queries) GetMostRecentTimeEntry(ctx context.Context) (TimeEntry, error) { + row := q.db.QueryRowContext(ctx, getMostRecentTimeEntry) + var i TimeEntry + err := row.Scan( + &i.ID, + &i.StartTime, + &i.EndTime, + &i.Description, + &i.ClientID, + &i.ProjectID, + &i.BillableRate, + ) + return i, err +} + +const getProjectByNameAndClient = `-- name: GetProjectByNameAndClient :one +select id, name, client_id, billable_rate, created_at from project where name = ?1 and client_id = ?2 limit 1 +` + +type GetProjectByNameAndClientParams struct { + Name string + ClientID int64 +} + +func (q *Queries) GetProjectByNameAndClient(ctx context.Context, arg GetProjectByNameAndClientParams) (Project, error) { + row := q.db.QueryRowContext(ctx, getProjectByNameAndClient, arg.Name, arg.ClientID) + var i Project + err := row.Scan( + &i.ID, + &i.Name, + &i.ClientID, + &i.BillableRate, + &i.CreatedAt, + ) + return i, err +} + +const getWeekSummaryByProject = `-- name: GetWeekSummaryByProject :many +select + p.id as project_id, + p.name as project_name, + c.id as client_id, + c.name as client_name, + cast(sum( + case + when te.end_time is null then + (julianday('now', 'utc') - julianday(te.start_time)) * 24 * 60 * 60 + else + (julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60 + end + ) as integer) as total_seconds +from time_entry te +join client c on te.client_id = c.id +left join project p on te.project_id = p.id +where date(te.start_time) >= date('now', 'weekday 1', '-6 days') + and date(te.start_time) <= date('now') +group by p.id, p.name, c.id, c.name +order by c.name, p.name +` + +type GetWeekSummaryByProjectRow struct { + ProjectID sql.NullInt64 + ProjectName sql.NullString + ClientID int64 + ClientName string + TotalSeconds int64 +} + +func (q *Queries) GetWeekSummaryByProject(ctx context.Context) ([]GetWeekSummaryByProjectRow, error) { + rows, err := q.db.QueryContext(ctx, getWeekSummaryByProject) + if err != nil { + return nil, err + } + defer rows.Close() + var items []GetWeekSummaryByProjectRow + for rows.Next() { + var i GetWeekSummaryByProjectRow + if err := rows.Scan( + &i.ProjectID, + &i.ProjectName, + &i.ClientID, + &i.ClientName, + &i.TotalSeconds, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const listAllClients = `-- name: ListAllClients :many +select id, name, email, billable_rate, created_at from client +order by name +` + +func (q *Queries) ListAllClients(ctx context.Context) ([]Client, error) { + rows, err := q.db.QueryContext(ctx, listAllClients) + if err != nil { + return nil, err + } + defer rows.Close() + var items []Client + for rows.Next() { + var i Client + if err := rows.Scan( + &i.ID, + &i.Name, + &i.Email, + &i.BillableRate, + &i.CreatedAt, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const listAllProjects = `-- name: ListAllProjects :many +select p.id, p.name, p.client_id, p.billable_rate, p.created_at, c.name as client_name from project p +join client c on p.client_id = c.id +order by c.name, p.name +` + +type ListAllProjectsRow struct { + ID int64 + Name string + ClientID int64 + BillableRate sql.NullInt64 + CreatedAt sql.NullTime + ClientName string +} + +func (q *Queries) ListAllProjects(ctx context.Context) ([]ListAllProjectsRow, error) { + rows, err := q.db.QueryContext(ctx, listAllProjects) + if err != nil { + return nil, err + } + defer rows.Close() + var items []ListAllProjectsRow + for rows.Next() { + var i ListAllProjectsRow + if err := rows.Scan( + &i.ID, + &i.Name, + &i.ClientID, + &i.BillableRate, + &i.CreatedAt, + &i.ClientName, + ); err != nil { + return nil, err + } + items = append(items, i) + } + if err := rows.Close(); err != nil { + return nil, err + } + if err := rows.Err(); err != nil { + return nil, err + } + return items, nil +} + +const stopTimeEntry = `-- name: StopTimeEntry :one +update time_entry +set end_time = datetime('now', 'utc') +where id = ( + select id + from time_entry + where end_time is null + order by start_time desc + limit 1 +) +returning id, start_time, end_time, description, client_id, project_id, billable_rate +` + +func (q *Queries) StopTimeEntry(ctx context.Context) (TimeEntry, error) { + row := q.db.QueryRowContext(ctx, stopTimeEntry) + var i TimeEntry + err := row.Scan( + &i.ID, + &i.StartTime, + &i.EndTime, + &i.Description, + &i.ClientID, + &i.ProjectID, + &i.BillableRate, + ) + return i, err +} diff --git a/sqlc.yaml b/sqlc.yaml new file mode 100644 index 0000000..ae6d28d --- /dev/null +++ b/sqlc.yaml @@ -0,0 +1,14 @@ +version: "2" +sql: + - engine: "sqlite" + queries: "internal/database/queries.sql" + schema: "internal/database/schema.sql" + gen: + go: + package: "queries" + out: "internal/queries" + sql_package: "database/sql" + emit_json_tags: false + emit_prepared_queries: false + emit_interface: false + emit_exact_table_names: false -- cgit v1.2.3