99 lines
3.3 KiB
SQL
99 lines
3.3 KiB
SQL
-- name: CreateEvent :one
|
|
INSERT INTO events (id, calendar_id, title, description, location, start_time, end_time, timezone, all_day, recurrence_rule, tags, created_by, updated_by)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
|
|
RETURNING *;
|
|
|
|
-- name: GetEventByID :one
|
|
SELECT * FROM events
|
|
WHERE id = $1 AND deleted_at IS NULL;
|
|
|
|
-- name: ListEventsInRange :many
|
|
SELECT e.* FROM events e
|
|
JOIN calendar_members cm ON cm.calendar_id = e.calendar_id
|
|
WHERE cm.user_id = @user_id
|
|
AND e.deleted_at IS NULL
|
|
AND e.start_time < @range_end
|
|
AND e.end_time > @range_start
|
|
AND (sqlc.narg('calendar_id')::UUID IS NULL OR e.calendar_id = sqlc.narg('calendar_id')::UUID)
|
|
AND (sqlc.narg('search')::TEXT IS NULL OR (e.title ILIKE '%' || sqlc.narg('search')::TEXT || '%' OR e.description ILIKE '%' || sqlc.narg('search')::TEXT || '%'))
|
|
AND (sqlc.narg('tag')::TEXT IS NULL OR sqlc.narg('tag')::TEXT = ANY(e.tags))
|
|
AND (
|
|
sqlc.narg('cursor_time')::TIMESTAMPTZ IS NULL
|
|
OR (e.start_time, e.id) > (sqlc.narg('cursor_time')::TIMESTAMPTZ, sqlc.narg('cursor_id')::UUID)
|
|
)
|
|
ORDER BY e.start_time ASC, e.id ASC
|
|
LIMIT @lim;
|
|
|
|
-- name: ListRecurringEventsInRange :many
|
|
SELECT e.* FROM events e
|
|
JOIN calendar_members cm ON cm.calendar_id = e.calendar_id
|
|
WHERE cm.user_id = @user_id
|
|
AND e.deleted_at IS NULL
|
|
AND e.recurrence_rule IS NOT NULL
|
|
AND e.start_time <= @range_end
|
|
AND (sqlc.narg('calendar_id')::UUID IS NULL OR e.calendar_id = sqlc.narg('calendar_id')::UUID)
|
|
ORDER BY e.start_time ASC;
|
|
|
|
-- name: UpdateEvent :one
|
|
UPDATE events
|
|
SET title = COALESCE(sqlc.narg('title'), title),
|
|
description = COALESCE(sqlc.narg('description'), description),
|
|
location = COALESCE(sqlc.narg('location'), location),
|
|
start_time = COALESCE(sqlc.narg('start_time'), start_time),
|
|
end_time = COALESCE(sqlc.narg('end_time'), end_time),
|
|
timezone = COALESCE(sqlc.narg('timezone'), timezone),
|
|
all_day = COALESCE(sqlc.narg('all_day'), all_day),
|
|
recurrence_rule = sqlc.narg('recurrence_rule'),
|
|
tags = COALESCE(sqlc.narg('tags'), tags),
|
|
updated_by = @updated_by,
|
|
updated_at = now()
|
|
WHERE id = @id AND deleted_at IS NULL
|
|
RETURNING *;
|
|
|
|
-- name: SoftDeleteEvent :exec
|
|
UPDATE events SET deleted_at = now(), updated_at = now()
|
|
WHERE id = $1 AND deleted_at IS NULL;
|
|
|
|
-- name: SoftDeleteEventsByCalendar :exec
|
|
UPDATE events SET deleted_at = now(), updated_at = now()
|
|
WHERE calendar_id = $1 AND deleted_at IS NULL;
|
|
|
|
-- name: SoftDeleteEventsByCreator :exec
|
|
UPDATE events SET deleted_at = now(), updated_at = now()
|
|
WHERE created_by = $1 AND deleted_at IS NULL;
|
|
|
|
-- name: CheckEventOverlap :one
|
|
SELECT EXISTS(
|
|
SELECT 1 FROM events
|
|
WHERE calendar_id = $1
|
|
AND deleted_at IS NULL
|
|
AND start_time < $3
|
|
AND end_time > $2
|
|
) AS overlap;
|
|
|
|
-- name: CheckEventOverlapForUpdate :one
|
|
SELECT EXISTS(
|
|
SELECT 1 FROM events
|
|
WHERE calendar_id = $1
|
|
AND deleted_at IS NULL
|
|
AND start_time < $3
|
|
AND end_time > $2
|
|
FOR UPDATE
|
|
) AS overlap;
|
|
|
|
-- name: ListEventsByCalendarInRange :many
|
|
SELECT * FROM events
|
|
WHERE calendar_id = $1
|
|
AND deleted_at IS NULL
|
|
AND start_time < $3
|
|
AND end_time > $2
|
|
ORDER BY start_time ASC;
|
|
|
|
-- name: ListRecurringEventsByCalendar :many
|
|
SELECT * FROM events
|
|
WHERE calendar_id = $1
|
|
AND deleted_at IS NULL
|
|
AND recurrence_rule IS NOT NULL
|
|
AND start_time <= $2
|
|
ORDER BY start_time ASC;
|