IF NOT EXISTS(SELECT *
              FROM INFORMATION_SCHEMA.TABLES
              WHERE TABLE_NAME = N'FacilityContacts')
  BEGIN
    CREATE TABLE [dbo].[FacilityContacts]
    (
      [FacilityID] [INT] NOT NULL,
      [ContactID]  [INT] NOT NULL,
      CONSTRAINT [FK_FacilityContacts_Facility]
        FOREIGN KEY ([FacilityID]) REFERENCES [dbo].[Facility] ([ID]) ON DELETE CASCADE,
      CONSTRAINT [FK_FacilityContacts_Contact]
        FOREIGN KEY ([ContactID]) REFERENCES [dbo].[contact] ([ID]) ON DELETE CASCADE
    )
  END
GO


IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[usp_GetFacilityContactListById]')
            AND TYPE IN (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_GetFacilityContactListById]
GO
CREATE PROCEDURE [dbo].usp_GetFacilityContactListById @ID INT
AS
BEGIN
  SELECT *
  FROM Contact c
         LEFT JOIN [dbo].[FacilityContacts] fc
                   ON fc.ContactID = c.Id
  WHERE fc.FacilityID = @id
END
GO

IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[usp_DeleteFromFacilityContacts]')
            AND TYPE IN (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_DeleteFromFacilityContacts]
GO
CREATE PROCEDURE [dbo].usp_DeleteFromFacilityContacts @facilityID INT, @contactID INT
AS
BEGIN
  DELETE
  FROM [dbo].[FacilityContacts]
  WHERE FacilityID = @facilityID
    AND contactID = @contactID
END
GO

IF EXISTS(SELECT *
          FROM sys.objects
          WHERE object_id = OBJECT_ID(N'[usp_AddToFacilityContacts]')
            AND TYPE IN (N'P', N'PC'))
  DROP PROCEDURE [dbo].[usp_AddToFacilityContacts]
GO
CREATE PROCEDURE [dbo].usp_AddToFacilityContacts @facilityID INT, @contactID INT
AS
IF NOT EXISTS
  (SELECT 1
   FROM [dbo].[FacilityContacts]
   WHERE FacilityID = @facilityID
     AND ContactID = @contactID
  )
  BEGIN
    INSERT INTO [dbo].[FacilityContacts](FacilityID, ContactID)
    VALUES (@facilityID,
            @contactID)
  END
GO