187 lines
3.8 KiB
SQL
187 lines
3.8 KiB
SQL
-- name: CreateAuthUser :one
|
|
INSERT INTO auth.users (
|
|
id,
|
|
email,
|
|
encrypted_password,
|
|
raw_user_meta_data,
|
|
created_at,
|
|
updated_at
|
|
) VALUES (
|
|
$1,
|
|
$2,
|
|
$3,
|
|
jsonb_build_object('display_name', sqlc.arg(display_name)::text),
|
|
now(),
|
|
now()
|
|
)
|
|
RETURNING id;
|
|
|
|
-- name: GetAuthUserByEmail :one
|
|
SELECT id, email, encrypted_password, created_at, updated_at
|
|
FROM auth.users
|
|
WHERE email = $1
|
|
LIMIT 1;
|
|
|
|
-- name: GetPublicUserByID :one
|
|
SELECT id, email, created_at, updated_at, display_name
|
|
FROM public.users
|
|
WHERE id = $1
|
|
LIMIT 1;
|
|
|
|
-- name: CreateSession :exec
|
|
INSERT INTO auth.sessions (
|
|
id,
|
|
session_token,
|
|
user_id,
|
|
created_at,
|
|
updated_at,
|
|
expires_at
|
|
) VALUES (
|
|
$1,
|
|
$2,
|
|
$3,
|
|
now(),
|
|
now(),
|
|
$4
|
|
);
|
|
|
|
-- name: GetSessionByToken :one
|
|
SELECT id, session_token, user_id, created_at, updated_at, expires_at
|
|
FROM auth.sessions
|
|
WHERE session_token = $1
|
|
LIMIT 1;
|
|
|
|
-- name: DeleteSessionByToken :execrows
|
|
DELETE FROM auth.sessions
|
|
WHERE session_token = $1;
|
|
|
|
-- name: CreateTablo :one
|
|
INSERT INTO public.tablos (
|
|
id,
|
|
owner_id,
|
|
name,
|
|
color,
|
|
status,
|
|
created_at,
|
|
updated_at
|
|
) VALUES (
|
|
$1,
|
|
$2,
|
|
$3,
|
|
$4,
|
|
$5,
|
|
now(),
|
|
now()
|
|
)
|
|
RETURNING id, owner_id, name, color, status, created_at, updated_at, deleted_at;
|
|
|
|
-- name: CreateTask :one
|
|
INSERT INTO public.tasks (
|
|
id,
|
|
tablo_id,
|
|
owner_id,
|
|
title,
|
|
description,
|
|
status,
|
|
assignee_id,
|
|
is_etape,
|
|
parent_task_id,
|
|
due_date,
|
|
created_at,
|
|
updated_at
|
|
) VALUES (
|
|
$1,
|
|
$2,
|
|
$3,
|
|
$4,
|
|
$5,
|
|
$6,
|
|
$7,
|
|
$8,
|
|
$9,
|
|
$10,
|
|
now(),
|
|
now()
|
|
)
|
|
RETURNING id, tablo_id, owner_id, title, description, status, assignee_id, is_etape, parent_task_id, due_date, created_at, updated_at, deleted_at;
|
|
|
|
-- name: ListTablos :many
|
|
SELECT id, owner_id, name, color, status, created_at, updated_at, deleted_at
|
|
FROM public.tablos
|
|
WHERE owner_id = sqlc.arg(owner_id)
|
|
AND deleted_at IS NULL
|
|
AND (
|
|
sqlc.narg(status)::text IS NULL OR status = sqlc.narg(status)::text
|
|
)
|
|
AND (
|
|
sqlc.narg(query)::text IS NULL OR name ILIKE '%' || sqlc.narg(query)::text || '%'
|
|
)
|
|
ORDER BY created_at DESC;
|
|
|
|
-- name: ListTasksByOwner :many
|
|
SELECT id, tablo_id, owner_id, title, description, status, assignee_id, is_etape, parent_task_id, due_date, created_at, updated_at, deleted_at
|
|
FROM public.tasks
|
|
WHERE owner_id = $1
|
|
AND deleted_at IS NULL
|
|
ORDER BY created_at ASC;
|
|
|
|
-- name: ListTasksByTablo :many
|
|
SELECT id, tablo_id, owner_id, title, description, status, assignee_id, is_etape, parent_task_id, due_date, created_at, updated_at, deleted_at
|
|
FROM public.tasks
|
|
WHERE owner_id = $1
|
|
AND tablo_id = $2
|
|
AND deleted_at IS NULL
|
|
ORDER BY created_at ASC;
|
|
|
|
-- name: GetTaskByID :one
|
|
SELECT id, tablo_id, owner_id, title, description, status, assignee_id, is_etape, parent_task_id, due_date, created_at, updated_at, deleted_at
|
|
FROM public.tasks
|
|
WHERE id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL
|
|
LIMIT 1;
|
|
|
|
-- name: UpdateTablo :execrows
|
|
UPDATE public.tablos
|
|
SET name = $3,
|
|
color = $4,
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL;
|
|
|
|
-- name: UpdateTask :one
|
|
UPDATE public.tasks
|
|
SET
|
|
title = $3,
|
|
description = $4,
|
|
status = $5,
|
|
due_date = $6,
|
|
assignee_id = $7,
|
|
parent_task_id = $8,
|
|
updated_at = now()
|
|
WHERE id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL
|
|
RETURNING id, tablo_id, owner_id, title, description, status, assignee_id, is_etape, parent_task_id, due_date, created_at, updated_at, deleted_at;
|
|
|
|
-- name: SoftDeleteTablo :execrows
|
|
UPDATE public.tablos
|
|
SET deleted_at = now(), updated_at = now()
|
|
WHERE id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL;
|
|
|
|
-- name: ClearTaskChildrenParent :execrows
|
|
UPDATE public.tasks
|
|
SET parent_task_id = NULL, updated_at = now()
|
|
WHERE parent_task_id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL;
|
|
|
|
-- name: SoftDeleteTask :execrows
|
|
UPDATE public.tasks
|
|
SET deleted_at = now(), updated_at = now()
|
|
WHERE id = $1
|
|
AND owner_id = $2
|
|
AND deleted_at IS NULL;
|