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