---

/* =====================================================
   СВЯЗИ Users12 С СИСТЕМОЙ ГОСТИНИЦЫ
   База: user284_db
   ===================================================== */


/* -----------------------------------------------------
   1. Связь Users12 -> Guests12

   Один зарегистрированный клиент может иметь
   одну карточку гостя.
   ----------------------------------------------------- */

IF COL_LENGTH('dbo.Guests12', 'UserID') IS NULL
BEGIN
    ALTER TABLE dbo.Guests12
    ADD UserID INT NULL;
END;
GO


IF NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Guests12_Users12'
)
BEGIN
    ALTER TABLE dbo.Guests12
    ADD CONSTRAINT FK_Guests12_Users12
        FOREIGN KEY (UserID)
        REFERENCES dbo.Users12(UserID);
END;
GO


/* Один UserID не должен принадлежать
   сразу нескольким гостям */

IF NOT EXISTS
(
    SELECT 1
    FROM sys.indexes
    WHERE name = 'UX_Guests12_UserID'
      AND object_id = OBJECT_ID('dbo.Guests12')
)
BEGIN
    CREATE UNIQUE INDEX UX_Guests12_UserID
    ON dbo.Guests12(UserID)
    WHERE UserID IS NOT NULL;
END;
GO



/* -----------------------------------------------------
   2. Связь Users12 -> Bookings12

   Запоминаем, какой зарегистрированный пользователь
   создал конкретное бронирование.
   ----------------------------------------------------- */

IF COL_LENGTH('dbo.Bookings12', 'CreatedByUserID') IS NULL
BEGIN
    ALTER TABLE dbo.Bookings12
    ADD CreatedByUserID INT NULL;
END;
GO


IF NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Bookings12_Users12'
)
BEGIN
    ALTER TABLE dbo.Bookings12
    ADD CONSTRAINT FK_Bookings12_Users12
        FOREIGN KEY (CreatedByUserID)
        REFERENCES dbo.Users12(UserID);
END;
GO



/* -----------------------------------------------------
   3. Связь Users12 -> Payments12

   Запоминаем пользователя, который оформил /
   обработал платёж.
   ----------------------------------------------------- */

IF COL_LENGTH('dbo.Payments12', 'ProcessedByUserID') IS NULL
BEGIN
    ALTER TABLE dbo.Payments12
    ADD ProcessedByUserID INT NULL;
END;
GO


IF NOT EXISTS
(
    SELECT 1
    FROM sys.foreign_keys
    WHERE name = 'FK_Payments12_Users12'
)
BEGIN
    ALTER TABLE dbo.Payments12
    ADD CONSTRAINT FK_Payments12_Users12
        FOREIGN KEY (ProcessedByUserID)
        REFERENCES dbo.Users12(UserID);
END;
GO



/* -----------------------------------------------------
   ПРОВЕРКА
   ----------------------------------------------------- */

SELECT
    fk.name AS ForeignKey,
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    COL_NAME(
        fkc.parent_object_id,
        fkc.parent_column_id
    ) AS ColumnName,
    OBJECT_NAME(
        fk.referenced_object_id
    ) AS ReferencedTable
FROM sys.foreign_keys fk

INNER JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id

WHERE fk.referenced_object_id =
      OBJECT_ID('dbo.Users12');
GO