path: root/internal/database/queries.sql

-- 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 (
    datetime(@start_time), 
    datetime(@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 *;

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

-- name: GetTimesheetDataByClient :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
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: GetTimesheetDataByProject :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
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: GetTodaySummary :one
select 
    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
where date(te.start_time) = date('now');

-- name: GetRecentTimeEntries :many
select * from time_entry 
where start_time >= @start_time
order by start_time desc;

-- name: UpdateActiveTimerDescription :exec
update time_entry 
set description = @description
where id = (
	select id
	from time_entry
	where end_time is null
	order by start_time desc
	limit 1
);

-- name: RemoveTimeEntry :exec
delete from time_entry
where id = @entry_id;