/* Lab 11
   Philip Bock */

use Restaurant;

if @@error != 0
begin

end
else
begin

end

/*
   Q1
 
   Date:		Dec 31, 2001
   Time:		17:00
   Customer:		Arafat and Sharon
   Contact:		George Bush
   Phone:		666-1287
   Number in Party:	3
   Event:		Peace Talks
   Tables Assigned:	21, 22 
*/

begin tran
/* Insert Reservation */
insert into Reservations
  (ReservationDateTime, ReservationStatus, CustomerName, ContactName, Phone, NumberInParty, EventDescription)
values
  ('Dec 31, 2004 17:00', 'R', 'Arafat and Sharon', 'George Bush', '666-1287', 3, 'Peace Talks')

if @@error != 0
begin
  rollback tran
  print 'Error inserting reservation'
end
else
begin
  /* Reservation inserted successfully */
  declare @ReservationNumber int
  set @ReservationNumber = @@Identity
  insert into TablesForReservation
    (ReservationNumber, TableNumber)
  values
    (@ReservationNumber, '21')

  if @@error != 0
  begin
    rollback tran
    print 'Error inserting 1st table for reservation'
  end
  else
  begin
    /* 1st table inserted successfully */
    insert into TablesForReservation
      (ReservationNumber, TableNumber)
    values
      (@ReservationNumber, '22')

    if @@error != 0
    begin
      rollback tran
      print 'Error inserting 2nd table for reservation'
    end
    else
    begin
      /* 2nd table inserted successfully */
      commit tran
      print 'Tables successfully reserved.'
    end
  end
end

/* Q2 */

drop procedure spCancelReservation
go

create procedure spCancelReservation
  @ReservationNumber int
as
  if @ReservationNumber is NULL
  begin
    print 'Cowardly refusing to cancel NULL reservation'
    return 2
  end
  else
  begin
    if not exists
      (select * from Reservations
       where ReservationStatus = 'R' and ReservationNumber = @ReservationNumber)
    begin
      print 'That reservation does not exist, or it has already been cancelled'
      return 3
    end
    else
    begin
      begin tran

      update Reservations
        set ReservationStatus = 'C'
      where ReservationNumber = @ReservationNumber

      if @@error != 0
      begin
        rollback tran
        print 'Error cancelling reservation'
        return 1
      end
      else
      begin
        delete TablesForReservation where ReservationNumber = @ReservationNumber

        if @@error != 0
        begin
          rollback tran
          print 'Error removing reserved tables'
          return 1
        end
        else
        begin
          commit tran
          print 'Reservation cancelled successfully'
          return 0
        end
      end
    end
  end
go

exec spCancelReservation 34;

/* Q3 */

