Initial commit of punchcard.

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

1 files changed, 132 insertions, 0 deletions

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 *;