IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReservationTablet]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ReservationTablet](
		[ReferenceNumber] [int] NOT NULL,
		[GuestNumber] [smallint] NOT NULL,
		[UserIDNumber] [int] NOT NULL,
		[TimeStamp] [datetime] NOT NULL,
	 CONSTRAINT [PK_ReservationTablet] PRIMARY KEY CLUSTERED 
	(
		[ReferenceNumber] ASC,
		[GuestNumber] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ReservationTablet] ADD  CONSTRAINT [DF_ReservationTablet_TimeStamp]  DEFAULT (getdate()) FOR [TimeStamp]

	ALTER TABLE [dbo].[ReservationTablet]  WITH CHECK ADD  CONSTRAINT [FK_ReservationTablet_PERSONEL] FOREIGN KEY([UserIDNumber])
	REFERENCES [dbo].[PERSONEL] ([UserIDNumber])

	ALTER TABLE [dbo].[ReservationTablet] CHECK CONSTRAINT [FK_ReservationTablet_PERSONEL]

	ALTER TABLE [dbo].[ReservationTablet]  WITH CHECK ADD  CONSTRAINT [FK_ReservationTablet_RESERVATIONS] FOREIGN KEY([ReferenceNumber], [GuestNumber])
	REFERENCES [dbo].[RESERVATIONS] ([ReferenceNumber], [GuestNumber])

	ALTER TABLE [dbo].[ReservationTablet] CHECK CONSTRAINT [FK_ReservationTablet_RESERVATIONS]

END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReservationRegistrationCard]') AND type in (N'U'))
BEGIN
	CREATE TABLE [dbo].[ReservationRegistrationCard](
		[ReferenceNumber] [int] NOT NULL,
		[GuestNumber] [smallint] NOT NULL,
		[FirstName] [varchar](50) NULL,
		[LastName] [varchar](50) NOT NULL,
		[Address1] [varchar](100) NULL,
		[Address2] [varchar](100) NULL,
		[CountryCode] [varchar](3) NULL,
		[ZipCode] [varchar](10) NULL,
		[City] [varchar](50) NULL,
		[Email] [varchar](150) NULL,
		[Phone] [varchar](255) NULL,
		[DateOfBirth] [datetime] NULL,
		[PassPortNumber] [varchar](25) NULL,
		[CompanyName] [varchar](50) NULL,
		[Note] [varchar](MAX) NULL,
	 CONSTRAINT [PK_ReservationRegistrationCard] PRIMARY KEY CLUSTERED 
	(
		[ReferenceNumber] ASC,
		[GuestNumber] ASC
	) ON [PRIMARY]
	) ON [PRIMARY]

	ALTER TABLE [dbo].[ReservationRegistrationCard] ADD  CONSTRAINT [DF_ReservationRegistrationCard_GUESTNUMBER]  DEFAULT ((0)) FOR [GuestNumber]

	ALTER TABLE [dbo].[ReservationRegistrationCard]  WITH CHECK ADD  CONSTRAINT [FK_ReservationRegistrationCard_RESERVATIONS] FOREIGN KEY([ReferenceNumber], [GuestNumber])
	REFERENCES [dbo].[RESERVATIONS] ([ReferenceNumber], [GuestNumber])

	ALTER TABLE [dbo].[ReservationRegistrationCard] CHECK CONSTRAINT [FK_ReservationRegistrationCard_RESERVATIONS]

END

IF EXISTS (select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA='dbo'
      AND TABLE_NAME='ReservationRegistrationCard'
      AND COLUMN_NAME='CompanyName'
	  AND IS_NULLABLE = 'NO')
BEGIN
	ALTER TABLE ReservationRegistrationCard ALTER COLUMN CompanyName VARCHAR(50) NULL;
END

IF NOT EXISTS
(
  SELECT SysColumns.Name 
  FROM SysObjects WITH (NOLOCK) 
    INNER JOIN SysColumns WITH (NOLOCK) ON SysColumns.Id = SysObjects.ID 
    AND SysColumns.Name = 'Note'
    WHERE SysObjects.Name = 'ReservationRegistrationCard'
)
BEGIN
	ALTER TABLE dbo.ReservationRegistrationCard ADD Note varchar(MAX) NULL
END
GO
