IF NOT EXISTS(SELECT * FROM sys.tables (NOLOCK) WHERE name = 'ReservationsExt')
BEGIN
	CREATE TABLE [dbo].[ReservationsExt](
		[FK_ReferenceNumber] [int] NOT NULL,
		[FK_GuestNumber] [smallint] NOT NULL,
		[LastUpdDateStamp] [datetime] NULL,
		LatestBalance money NULL,
		CONSTRAINT [PK_ReservationsExt] PRIMARY KEY CLUSTERED 
	(
		[FK_ReferenceNumber] ASC,
		[FK_GuestNumber] ASC
	)
	) ON [PRIMARY]

	IF EXISTS
	(
	  SELECT SysColumns.Name 
	  FROM SysObjects WITH (NOLOCK) 
		INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
		AND SysColumns.Name = 'LastUpdDateStamp'
		WHERE SysObjects.Name = 'Reservations'
	)
	BEGIN
	  EXEC sp_executesql N'INSERT INTO ReservationsExt (FK_ReferenceNumber, FK_GuestNumber, LastUpdDateStamp)  SELECT ReferenceNumber, GuestNumber, LastUpdDateStamp FROM Reservations WITH (NOLOCK)'
	END

	UPDATE ReservationsExt SET LastUpdDateStamp = GETDATE() WHERE LastUpdDateStamp IS NULL
	ALTER TABLE ReservationsExt ALTER COLUMN LastUpdDateStamp DATETIME NOT NULL
	ALTER TABLE ReservationsExt ADD  DEFAULT (getdate()) FOR [LastUpdDateStamp]

	ALTER TABLE [dbo].[ReservationsExt]  WITH CHECK ADD  CONSTRAINT [FK_ReservationsExt_Reservations] FOREIGN KEY([FK_ReferenceNumber], [FK_GuestNumber])
	REFERENCES [dbo].[Reservations] ([ReferenceNumber], [GuestNumber])

	ALTER TABLE [dbo].[ReservationsExt] CHECK CONSTRAINT [FK_ReservationsExt_Reservations]
END

IF NOT EXISTS
(
  SELECT SysColumns.Name
  FROM SysObjects WITH (NOLOCK)
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID
    AND SysColumns.Name = 'SignatureImage'
    WHERE SysObjects.Name = 'ReservationsExt'
)
BEGIN
  ALTER TABLE ReservationsExt ADD SignatureImage varbinary(MAX) NULL
END

