udemy-go-web-1/sql/query.sql

-- name: InsertReservation :one
INSERT INTO
  reservations (
    first_name,
    last_name,
    email,
    phone,
    start_date,
    end_date,
    room_id,
    created_at,
    updated_at
  )
VALUES
  ($1, $2, $3, $4, $5, $6, $7, $8, $9)
RETURNING
  id;

-- name: InsertRoomRestriction :exec
INSERT INTO
  room_restrictions (
    start_date,
    end_date,
    room_id,
    reservation_id,
    restriction_id,
    created_at,
    updated_at
  )
VALUES
  ($1, $2, $3, $4, $5, $6, $7);

-- name: SearchAvailabilityByDatesByRoomID :one
SELECT
  count(id)
FROM
  room_restrictions
WHERE
  room_id = $1
  AND $2 < end_date
  AND $3 > start_date;

-- name: SearchAvailabilityForAllRooms :many
SELECT
  r.id,
  r.room_name
FROM
  rooms r
WHERE
  r.id NOT IN (
    SELECT
      room_id
    FROM
      room_restrictions rr
    WHERE
      $1 < rr.end_date
      AND $2 > rr.start_date
  );

-- name: GetRoomById :one
SELECT
  id,
  room_name,
  created_at,
  updated_at
FROM
  rooms
WHERE
  id = $1;

-- name: GetUserByID :one
SELECT
  id,
  first_name,
  last_name,
  email,
  password,
  access_level,
  created_at,
  updated_at
FROM
  users
WHERE
  id = $1;

-- name: UpdateUser :exec
UPDATE users
SET
  first_name = $1,
  last_name = $2,
  email = $3,
  access_level = $4,
  updated_at = $5;

-- name: GetUserCred :one
SELECT
  id,
  password
FROM
  users
WHERE
  email = $1;

-- name: AllReservations :many
SELECT
  r.id,
  r.first_name,
  r.last_name,
  r.email,
  r.phone,
  r.start_date,
  r.end_date,
  r.room_id,
  r.created_at,
  r.updated_at,
  r.processed,
  rm.id,
  rm.room_name
FROM
  reservations r
  LEFT JOIN rooms rm ON (r.room_id = rm.id)
ORDER BY
  r.start_date ASC;

-- name: AllNewReservations :many
SELECT
  r.id,
  r.first_name,
  r.last_name,
  r.email,
  r.phone,
  r.start_date,
  r.end_date,
  r.room_id,
  r.created_at,
  r.updated_at,
  r.processed,
  rm.id,
  rm.room_name
FROM
  reservations r
  LEFT JOIN rooms rm ON (r.room_id = rm.id)
WHERE
  r.processed = 0
ORDER BY
  r.start_date ASC;

-- name: GetReservationByID :one
SELECT
  r.id,
  r.first_name,
  r.last_name,
  r.email,
  r.phone,
  r.start_date,
  r.end_date,
  r.room_id,
  r.created_at,
  r.updated_at,
  r.processed,
  rm.id,
  rm.room_name
FROM
  reservations r
  LEFT JOIN rooms rm ON (r.room_id = rm.id)
WHERE
  r.id = $1;

-- name: UpdateReservation :exec
UPDATE reservations
SET
  first_name = $1,
  last_name = $2,
  email = $3,
  phone = $4,
  updated_at = $5
WHERE
  id = $6;

-- name: DeleteReservation :exec
DELETE FROM reservations
WHERE
  id = $1;

-- name: UpdateProcessedForReservation :exec
UPDATE reservations
SET
  processed = $1
WHERE
  id = $2;

-- name: AllRooms :many
SELECT
  id,
  room_name,
  created_at,
  updated_at
FROM
  rooms
ORDER BY
  room_name;

-- name: GetRestrictionsForRoomByDate :many
SELECT
  id,
  coalesce(reservation_id, 0),
  restriction_id,
  room_id,
  start_date,
  end_date
FROM
  room_restrictions
WHERE
  @start_date < end_date
  AND @end_date >= start_date
  AND room_id = @room_id;

-- name: InsertBlockForRoom :exec
INSERT INTO
  room_restrictions (
    start_date,
    end_date,
    room_id,
    restriction_id,
    created_at,
    updated_at
  )
VALUES
  ($1, $2, $3, $4, $5, $6);

-- name: DeleteBlockByID :exec
DELETE FROM room_restrictions
WHERE
  id = $1;