-- name: GetUserByID :one
SELECT * FROM users WHERE id = $1;

-- name: GetUserByEmail :one
SELECT * FROM users WHERE email = $1;

-- name: GetUserByEmployeeID :one
SELECT * FROM users WHERE employee_id = $1;

-- name: CreateUser :one
INSERT INTO users (name, email, password_hash, phone, avatar_url, role, status, sso_provider, sso_external_id)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING *;

-- name: UpdateUserProfile :one
UPDATE users
SET name = COALESCE(sqlc.narg('name'), name),
    phone = COALESCE(sqlc.narg('phone'), phone),
    avatar_url = COALESCE(sqlc.narg('avatar_url'), avatar_url)
WHERE id = $1
RETURNING *;

-- name: UpdateUserRole :exec
UPDATE users SET role = $2 WHERE id = $1;

-- name: UpdateUserStatus :exec
UPDATE users SET status = $2 WHERE id = $1;

-- name: UpdateUserLogin :exec
UPDATE users
SET last_login_at = NOW(), login_count = login_count + 1
WHERE id = $1;

-- name: ListUsers :many
SELECT * FROM users
WHERE (sqlc.narg('role')::VARCHAR IS NULL OR role = sqlc.narg('role'))
  AND (sqlc.narg('status')::VARCHAR IS NULL OR status = sqlc.narg('status'))
  AND (sqlc.narg('search')::VARCHAR IS NULL
       OR name ILIKE '%' || sqlc.narg('search') || '%'
       OR email ILIKE '%' || sqlc.narg('search') || '%'
       OR employee_id ILIKE '%' || sqlc.narg('search') || '%')
ORDER BY created_at DESC
LIMIT $1 OFFSET $2;

-- name: CountUsers :one
SELECT COUNT(*) FROM users
WHERE (sqlc.narg('role')::VARCHAR IS NULL OR role = sqlc.narg('role'))
  AND (sqlc.narg('status')::VARCHAR IS NULL OR status = sqlc.narg('status'))
  AND (sqlc.narg('search')::VARCHAR IS NULL
       OR name ILIKE '%' || sqlc.narg('search') || '%'
       OR email ILIKE '%' || sqlc.narg('search') || '%'
       OR employee_id ILIKE '%' || sqlc.narg('search') || '%');