40 lines
825 B
SQL
40 lines
825 B
SQL
-- name: GetUserByOpenID :one
|
|
SELECT * FROM users
|
|
WHERE openid = ?;
|
|
|
|
-- name: GetUserByID :one
|
|
SELECT * FROM users
|
|
WHERE id = ?;
|
|
|
|
-- name: CreateUser :one
|
|
INSERT INTO users (id, openid, unionid, phone)
|
|
VALUES (?, ?, ?, ?)
|
|
RETURNING *;
|
|
|
|
-- name: TouchUserLogin :one
|
|
UPDATE users
|
|
SET last_login_at = CURRENT_TIMESTAMP
|
|
WHERE openid = ?
|
|
RETURNING *;
|
|
|
|
-- name: BindUserPhone :one
|
|
UPDATE users
|
|
SET phone = ?, updated_at = CURRENT_TIMESTAMP
|
|
WHERE id = ?
|
|
RETURNING *;
|
|
|
|
-- name: ListUsersWithPhone :many
|
|
SELECT * FROM users
|
|
WHERE phone IS NOT NULL AND phone <> ''
|
|
ORDER BY updated_at DESC, created_at DESC;
|
|
|
|
-- name: ListBookingPhoneSummaries :many
|
|
SELECT
|
|
phone,
|
|
COUNT(*) AS booking_count,
|
|
CAST(MAX(created_at) AS TEXT) AS last_booking_at
|
|
FROM bookings
|
|
WHERE phone <> ''
|
|
GROUP BY phone
|
|
ORDER BY last_booking_at DESC, phone ASC;
|