Generate invoice PDFs

2 files changed, 121 insertions, 0 deletions

diff --git a/internal/database/queries.sql b/internal/database/queries.sql
index 073574a..32114a6 100644
--- a/internal/database/queries.sql
+++ b/internal/database/queries.sql
@@ -130,3 +130,104 @@ values (
     )
 )
 returning *;
+
+-- name: GetInvoiceDataByClient :many
+select 
+    te.id as time_entry_id,
+    te.start_time,
+    te.end_time,
+    te.description,
+    te.billable_rate as entry_billable_rate,
+    c.id as client_id,
+    c.name as client_name,
+    c.billable_rate as client_billable_rate,
+    p.id as project_id,
+    p.name as project_name,
+    p.billable_rate as project_billable_rate,
+    cast(round((julianday(te.end_time) - julianday(te.start_time)) * 24 * 60 * 60) as integer) as duration_seconds,
+    case
+        when te.billable_rate is not null then 'entry'
+        when p.billable_rate is not null then 'project'
+        else 'client'
+    end as rate_source
+from time_entry te
+join client c on te.client_id = c.id
+left join project p on te.project_id = p.id
+where c.id = @client_id
+    and te.start_time >= @start_time
+    and te.start_time <= @end_time
+    and te.end_time is not null
+order by te.start_time;
+
+-- name: GetInvoiceDataByProject :many
+select 
+    te.id as time_entry_id,
+    te.start_time,
+    te.end_time,
+    te.description,
+    te.billable_rate as entry_billable_rate,
+    c.id as client_id,
+    c.name as client_name,
+    c.billable_rate as client_billable_rate,
+    p.id as project_id,
+    p.name as project_name,
+    p.billable_rate as project_billable_rate,
+    cast(
+        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 duration_seconds,
+    case
+        when te.billable_rate is not null then 'entry'
+        when p.billable_rate is not null then 'project'
+        else 'client'
+    end as rate_source
+from time_entry te
+join client c on te.client_id = c.id
+join project p on te.project_id = p.id
+where p.id = @project_id
+    and te.start_time >= @start_time
+    and te.start_time <= @end_time
+    and te.end_time is not null
+order by te.start_time;
+
+-- name: GetContractor :one
+select * from contractor 
+order by id 
+limit 1;
+
+-- name: CreateContractor :one
+insert into contractor (name, label, email)
+values (@name, @label, @email)
+returning *;
+
+-- name: UpdateContractor :one
+update contractor 
+set name = @name, label = @label, email = @email
+where id = (select id from contractor order by id limit 1)
+returning *;
+
+-- name: UpdateClient :one
+update client
+set name = @name, email = @email, billable_rate = @billable_rate
+where id = @id
+returning *;
+
+-- name: UpdateProject :one
+update project
+set name = @name, billable_rate = @billable_rate
+where id = @id
+returning *;
+
+-- name: GetHighestInvoiceNumber :one
+select cast(coalesce(max(number), 0) as integer) as max_number
+from invoice
+where year = @year and month = @month;
+
+-- name: CreateInvoice :one
+insert into invoice (year, month, number, client_id, total_amount)
+values (@year, @month, @number, @client_id, @total_amount)
+returning *;
diff --git a/internal/database/schema.sql b/internal/database/schema.sql
index a4483e1..84f4f02 100644
--- a/internal/database/schema.sql
+++ b/internal/database/schema.sql
@@ -15,6 +15,26 @@ create table if not exists project (
     foreign key (client_id) references client(id)
 );
 
+create table if not exists contractor (
+    id integer primary key autoincrement,
+    name text not null,
+    label text not null,
+    email text not null,
+    created_at datetime default current_timestamp
+);
+
+create table if not exists invoice (
+    id integer primary key autoincrement,
+    year integer not null,
+    month integer not null,
+    number integer not null,
+    client_id integer not null,
+    total_amount integer not null,
+    created_at datetime default current_timestamp,
+    unique(year, month, number),
+    foreign key (client_id) references client(id)
+);
+
 create table if not exists time_entry (
     id integer primary key autoincrement,
     start_time datetime not null,