use Testtt
GO

CREATE TABLE Комфортность_номера
 (
    id_Комфортности int PRIMARY KEY IDENTITY(0, 1),
    Название varchar(15) NOT NULL UNIQUE,
    Множитель float CHECK (Множитель >= 1)
 )
GO
CREATE TABLE Вместимость_номера
 (
    Вместимость int PRIMARY KEY,
    Цена_за_ночь money NOT NULL
 )
GO
CREATE TABLE Номер
 (
    Номер int PRIMARY KEY IDENTITY(0, 1),
    Комфортность int NOT NULL,
    Вместимость int NOT NULL,
    Наценка money NOT NULL DEFAULT(0),
    CONSTRAINT FK_type FOREIGN KEY (Комфортность) REFERENCES Комфортность_номера(id_Комфортности),
    CONSTRAINT FK_q FOREIGN KEY (Вместимость) REFERENCES Вместимость_номера(Вместимость)
 )
GO
CREATE FUNCTION dbo.getPrice(@NUMBER as INT)
RETURNS money
AS
BEGIN
    DECLARE @rtnValue money;
    DECLARE @CostPerNight money;
    DECLARE @comfMn float;
    DECLARE @markup money;
    SELECT @CostPerNight = Цена_за_ночь FROM (Номер JOIN Вместимость_номера ON Номер.Вместимость = Вместимость_номера.Вместимость) WHERE Номер.Номер = @NUMBER;
    SELECT @comfMn = Множитель FROM (Номер JOIN Комфортность_номера ON Номер.Комфортность = Комфортность_номера.id_Комфортности)  WHERE Номер.Номер = @NUMBER;
    SELECT @markup = Наценка FROM Номер WHERE Номер.Номер = @NUMBER;
    SET @rtnValue = @CostPerNight * @comfMn + @markup;
    RETURN @rtnValue;
END
GO
ALTER TABLE Номер
      ADD Цена AS dbo.getPrice(Номер.Номер)
GO
CREATE TABLE Статус_клиента
 (
    id_Статуса int PRIMARY KEY IDENTITY (0, 1),
    Статус varchar(15) NOT NULL UNIQUE,
    Множитель float CHECK(Множитель <= 1)
 )
GO
CREATE TABLE Клиент
 (
    id_Клиента int PRIMARY KEY IDENTITY (0, 1),
    ФИО varchar(50) NOT NULL,
    Статус int NOT NULL,
    Комментарий varchar(100),
    CONSTRAINT FK_status FOREIGN KEY (Статус) REFERENCES Статус_клиента (id_Статуса)
 )
GO

CREATE TABLE Сданный_номер
 (
    id_Услуги int PRIMARY KEY IDENTITY (0, 1),
    Номер int NOT NULL,
    Клиент int NOT NULL,
    Дата_время_въезда datetime NOT NULL,
    Дата_время_окончания_услуги datetime NOT NULL,
    Дата_время_выезда datetime NOT NULL,
    CONSTRAINT FK_nom FOREIGN KEY (Номер) REFERENCES Номер(Номер),
    CONSTRAINT FK_client FOREIGN KEY (Клиент) REFERENCES Клиент(id_Клиента),
    CONSTRAINT unique_sdnom UNIQUE (Номер, Клиент, Дата_время_въезда)
 )
GO
CREATE FUNCTION dbo.getCost(@NUMBER as INT, @CLIENT as INT, @DATE as date)
RETURNS money
AS
BEGIN
    DECLARE @price money;
    DECLARE @clientMn float;
    DECLARE @sale float;
    SELECT @price = Цена FROM Номер WHERE Номер.Номер = @NUMBER;
    SELECT @clientMn = Множитель FROM (Клиент JOIN Статус_клиента ON Клиент.Статус = Статус_клиента.id_Статуса) WHERE Клиент.id_Клиента = @CLIENT;
    SELECT @sale = Множитель FROM Скидка WHERE @DATE >= Скидка.Дата_начала_скидки AND @DATE <= Скидка.Дата_окончания_скидки AND ((SELECT Комфортность
                                                                                                                                 FROM Номер
                                                                                                                                 WHERE Номер.Номер = @NUMBER) = Скидка.Комфортность
                                                                                                                                OR
                                                                                                                                (SELECT Вместимость
                                                                                                                                 FROM Номер
                                                                                                                                 WHERE Номер.Номер = @NUMBER) = Скидка.Вместимость)
    if (@sale is NULL) or @sale = 0
        SET @sale = 1

    RETURN @price * @clientMn * @sale
END
GO
ALTER TABLE Сданный_номер
      ADD Стоимость AS (DATEDIFF(day, Дата_время_въезда, Дата_время_окончания_услуги)) * dbo.getCost(Номер, Клиент, CAST(Дата_время_въезда as date))
GO
CREATE TABLE Забронированный_номер
 (
    id_Брони int PRIMARY KEY IDENTITY (0, 1),
    Номер int NOT NULL,
    Клиент int NOT NULL,
    Дата_время_бронирования datetime NOT NULL,
    Дата_время_въезда datetime NOT NULL,
    Дата_время_окончания_услуги datetime NOT NULL,
    CONSTRAINT FK_brnom FOREIGN KEY (Номер) REFERENCES Номер(Номер),
    CONSTRAINT FK_brclient FOREIGN KEY (Клиент) REFERENCES Клиент(id_Клиента)
 )
GO
ALTER TABLE Забронированный_номер
      ADD Предоплата AS dbo.getCost(Номер, Клиент, CAST(Дата_время_въезда as date))
GO
CREATE TABLE Скидка
 (
    id_Скидки int PRIMARY KEY IDENTITY (0, 1),
    Комфортность int,
    Вместимость int,
    Дата_начала_скидки date NOT NULL,
    Дата_окончания_скидки date NOT NULL,
    Информация varchar(100),
    Множитель float CHECK (Множитель <= 1)
    CONSTRAINT FK_sale_comf FOREIGN KEY (Комфортность) REFERENCES Комфортность_номера(id_Комфортности),
    CONSTRAINT FK_sale_capac FOREIGN KEY (Вместимость) REFERENCES Вместимость_номера(Вместимость),
    CONSTRAINT unique_sale UNIQUE(Комфортность, Вместимость, Дата_начала_скидки, Дата_окончания_скидки)
 )