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