Initial commit of punchcard.

Contains working time tracking commands, and the stub of a command to
generate reports.

3 files changed, 228 insertions, 0 deletions

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)
+);