47 lines
1.6 KiB
SQL
47 lines
1.6 KiB
SQL
-- name: CreateContact :one
|
|
INSERT INTO contacts (id, owner_id, first_name, last_name, email, phone, company, notes)
|
|
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
|
|
RETURNING *;
|
|
|
|
-- name: GetContactByID :one
|
|
SELECT * FROM contacts
|
|
WHERE id = $1 AND owner_id = $2 AND deleted_at IS NULL;
|
|
|
|
-- name: ListContacts :many
|
|
SELECT * FROM contacts
|
|
WHERE owner_id = @owner_id
|
|
AND deleted_at IS NULL
|
|
AND (
|
|
sqlc.narg('search')::TEXT IS NULL
|
|
OR first_name ILIKE '%' || sqlc.narg('search')::TEXT || '%'
|
|
OR last_name ILIKE '%' || sqlc.narg('search')::TEXT || '%'
|
|
OR email ILIKE '%' || sqlc.narg('search')::TEXT || '%'
|
|
OR company ILIKE '%' || sqlc.narg('search')::TEXT || '%'
|
|
)
|
|
AND (
|
|
sqlc.narg('cursor_time')::TIMESTAMPTZ IS NULL
|
|
OR (created_at, id) > (sqlc.narg('cursor_time')::TIMESTAMPTZ, sqlc.narg('cursor_id')::UUID)
|
|
)
|
|
ORDER BY created_at ASC, id ASC
|
|
LIMIT @lim;
|
|
|
|
-- name: UpdateContact :one
|
|
UPDATE contacts
|
|
SET first_name = COALESCE(sqlc.narg('first_name'), first_name),
|
|
last_name = COALESCE(sqlc.narg('last_name'), last_name),
|
|
email = COALESCE(sqlc.narg('email'), email),
|
|
phone = COALESCE(sqlc.narg('phone'), phone),
|
|
company = COALESCE(sqlc.narg('company'), company),
|
|
notes = COALESCE(sqlc.narg('notes'), notes),
|
|
updated_at = now()
|
|
WHERE id = @id AND owner_id = @owner_id AND deleted_at IS NULL
|
|
RETURNING *;
|
|
|
|
-- name: SoftDeleteContact :exec
|
|
UPDATE contacts SET deleted_at = now(), updated_at = now()
|
|
WHERE id = $1 AND owner_id = $2 AND deleted_at IS NULL;
|
|
|
|
-- name: SoftDeleteContactsByOwner :exec
|
|
UPDATE contacts SET deleted_at = now(), updated_at = now()
|
|
WHERE owner_id = $1 AND deleted_at IS NULL;
|