---

-- Дополнение существующей схемы гостиницы.
-- Сначала сделайте резервную копию базы user305_db.
-- Старые таблицы и записи гостей/броней не удаляются.

USE [user305_db];
GO

IF OBJECT_ID(N'dbo.RoomType25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.Room25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.Guest25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.Booking25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.Service25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.BookingService25', N'U') IS NULL OR
   OBJECT_ID(N'dbo.Payment25', N'U') IS NULL
    THROW 51000, N'Не найдена одна из семи исходных таблиц в dbo. Проверьте базу данных.', 1;
GO

-- 1. Скидки
IF OBJECT_ID(N'dbo.Discount25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Discount25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Discount25 PRIMARY KEY,
        Title nvarchar(80) NOT NULL,
        [Percent] decimal(5,2) NOT NULL,
        ClientCategory nvarchar(80) NOT NULL,
        Conditions nvarchar(300) NULL,
        IsActive bit NOT NULL CONSTRAINT DF_Discount25_IsActive DEFAULT (1),
        CONSTRAINT UQ_Discount25_Title UNIQUE (Title),
        CONSTRAINT CK_Discount25_Percent CHECK ([Percent] > 0 AND [Percent] <= 100)
    );
END;
GO

IF COL_LENGTH(N'dbo.Booking25', N'DiscountID') IS NULL
    ALTER TABLE dbo.Booking25 ADD DiscountID int NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys
               WHERE name = N'FK_Booking25_Discount25'
                 AND parent_object_id = OBJECT_ID(N'dbo.Booking25'))
    ALTER TABLE dbo.Booking25 WITH CHECK
        ADD CONSTRAINT FK_Booking25_Discount25 FOREIGN KEY (DiscountID)
        REFERENCES dbo.Discount25(ID);
GO

-- 2. Сотрудники
IF OBJECT_ID(N'dbo.Employee25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Employee25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Employee25 PRIMARY KEY,
        FirstName nvarchar(50) NOT NULL,
        LastName nvarchar(50) NOT NULL,
        Patronymic nvarchar(50) NULL,
        [Position] nvarchar(80) NOT NULL,
        [Floor] int NULL,
        PersonnelNumber nvarchar(20) NOT NULL,
        Phone nvarchar(30) NULL,
        IsActive bit NOT NULL CONSTRAINT DF_Employee25_IsActive DEFAULT (1),
        CONSTRAINT UQ_Employee25_PersonnelNumber UNIQUE (PersonnelNumber),
        CONSTRAINT CK_Employee25_Floor CHECK ([Floor] IS NULL OR [Floor] > 0)
    );
END;
GO

-- Заявка на услугу одновременно служит заданием сотруднику
IF COL_LENGTH(N'dbo.BookingService25', N'Status') IS NULL
    ALTER TABLE dbo.BookingService25
        ADD [Status] nvarchar(30) NOT NULL
            CONSTRAINT DF_BookingService25_Status DEFAULT (N'Ожидает') WITH VALUES;
GO

IF COL_LENGTH(N'dbo.BookingService25', N'EmployeeID') IS NULL
    ALTER TABLE dbo.BookingService25 ADD EmployeeID int NULL;
GO

IF COL_LENGTH(N'dbo.BookingService25', N'CompletedAt') IS NULL
    ALTER TABLE dbo.BookingService25 ADD CompletedAt datetime2(0) NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.foreign_keys
               WHERE name = N'FK_BookingService25_Employee25'
                 AND parent_object_id = OBJECT_ID(N'dbo.BookingService25'))
    ALTER TABLE dbo.BookingService25 WITH CHECK
        ADD CONSTRAINT FK_BookingService25_Employee25 FOREIGN KEY (EmployeeID)
        REFERENCES dbo.Employee25(ID);
GO

-- 3. Учётные записи для будущих входа и регистрации
-- Хеш и соль будут вычисляться в приложении; пароли текстом в БД не записываются
IF OBJECT_ID(N'dbo.UserAccount25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.UserAccount25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserAccount25 PRIMARY KEY,
        [Login] nvarchar(100) NOT NULL,
        PasswordHash varbinary(32) NOT NULL,
        PasswordSalt varbinary(16) NOT NULL,
        PasswordAlgorithm nvarchar(30) NOT NULL
            CONSTRAINT DF_UserAccount25_Algorithm DEFAULT (N'PBKDF2-SHA256'),
        PasswordIterations int NOT NULL,
        [Role] nvarchar(30) NOT NULL,
        GuestID int NULL,
        EmployeeID int NULL,
        IsActive bit NOT NULL CONSTRAINT DF_UserAccount25_IsActive DEFAULT (1),
        CreatedAt datetime2(0) NOT NULL CONSTRAINT DF_UserAccount25_CreatedAt DEFAULT (SYSUTCDATETIME()),
        LastLoginAt datetime2(0) NULL,
        CONSTRAINT UQ_UserAccount25_Login UNIQUE ([Login]),
        CONSTRAINT FK_UserAccount25_Guest25 FOREIGN KEY (GuestID) REFERENCES dbo.Guest25(ID),
        CONSTRAINT FK_UserAccount25_Employee25 FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee25(ID),
        CONSTRAINT CK_UserAccount25_Algorithm CHECK (PasswordAlgorithm = N'PBKDF2-SHA256'),
        CONSTRAINT CK_UserAccount25_Iterations CHECK (PasswordIterations >= 600000),
        CONSTRAINT CK_UserAccount25_RoleLink CHECK (
            ([Role] = N'Клиент' AND GuestID IS NOT NULL AND EmployeeID IS NULL) OR
            ([Role] IN (N'Сотрудник', N'Администратор') AND EmployeeID IS NOT NULL AND GuestID IS NULL)
        )
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = OBJECT_ID(N'dbo.UserAccount25')
                 AND name = N'UX_UserAccount25_GuestID')
    CREATE UNIQUE INDEX UX_UserAccount25_GuestID
        ON dbo.UserAccount25(GuestID) WHERE GuestID IS NOT NULL;
GO

IF NOT EXISTS (SELECT 1 FROM sys.indexes
               WHERE object_id = OBJECT_ID(N'dbo.UserAccount25')
                 AND name = N'UX_UserAccount25_EmployeeID')
    CREATE UNIQUE INDEX UX_UserAccount25_EmployeeID
        ON dbo.UserAccount25(EmployeeID) WHERE EmployeeID IS NOT NULL;
GO

-- 4. Расписание смен
IF OBJECT_ID(N'dbo.Shift25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Shift25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Shift25 PRIMARY KEY,
        EmployeeID int NOT NULL,
        ShiftDate date NOT NULL,
        StartTime time(0) NOT NULL,
        EndTime time(0) NOT NULL,
        [Floor] int NULL,
        Note nvarchar(200) NULL,
        CONSTRAINT FK_Shift25_Employee25 FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee25(ID),
        CONSTRAINT UQ_Shift25_EmployeeDateStart UNIQUE (EmployeeID, ShiftDate, StartTime)
    );
END;
GO

-- 5. Журнал уборок
IF OBJECT_ID(N'dbo.Cleaning25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Cleaning25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Cleaning25 PRIMARY KEY,
        RoomID int NOT NULL,
        EmployeeID int NULL,
        CleaningDate date NOT NULL,
        [Status] nvarchar(30) NOT NULL,
        Comment nvarchar(300) NULL,
        StartedAt datetime2(0) NULL,
        CompletedAt datetime2(0) NULL,
        CONSTRAINT FK_Cleaning25_Room25 FOREIGN KEY (RoomID) REFERENCES dbo.Room25(ID),
        CONSTRAINT FK_Cleaning25_Employee25 FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee25(ID)
    );
END;
GO

-- 6. Сообщения администратору
IF OBJECT_ID(N'dbo.Message25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.Message25 (
        ID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_Message25 PRIMARY KEY,
        EmployeeID int NOT NULL,
        Subject nvarchar(120) NOT NULL,
        Body nvarchar(1000) NOT NULL,
        SentAt datetime2(0) NOT NULL CONSTRAINT DF_Message25_SentAt DEFAULT (SYSUTCDATETIME()),
        [Status] nvarchar(30) NOT NULL CONSTRAINT DF_Message25_Status DEFAULT (N'Новое'),
        CONSTRAINT FK_Message25_Employee25 FOREIGN KEY (EmployeeID) REFERENCES dbo.Employee25(ID)
    );
END;
GO

-- 7. Реквизиты гостиницы
IF OBJECT_ID(N'dbo.HotelSettings25', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.HotelSettings25 (
        ID int NOT NULL CONSTRAINT PK_HotelSettings25 PRIMARY KEY,
        HotelName nvarchar(150) NOT NULL,
        [Address] nvarchar(250) NULL,
        Phone nvarchar(30) NULL,
        Email nvarchar(100) NULL,
        UpdatedAt datetime2(0) NOT NULL CONSTRAINT DF_HotelSettings25_UpdatedAt DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT CK_HotelSettings25_SingleRow CHECK (ID = 1)
    );
END;
GO

-- Учебные записи на русском. Существующие строки не перезаписываются
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT dbo.Discount25 (Title, [Percent], ClientCategory, Conditions, IsActive)
    SELECT v.Title, v.[Percent], v.ClientCategory, v.Conditions, 1
    FROM (VALUES
        (N'Постоянный гость', CAST(10.00 AS decimal(5,2)), N'Постоянный клиент', N'После трёх завершённых бронирований'),
        (N'Пенсионная', CAST(7.00 AS decimal(5,2)), N'Пенсионер', N'При предъявлении подтверждающего документа'),
        (N'Корпоративная', CAST(15.00 AS decimal(5,2)), N'Корпоративный клиент', N'По договору с организацией')
    ) AS v(Title, [Percent], ClientCategory, Conditions)
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Discount25 d WHERE d.Title = v.Title);

    INSERT dbo.Employee25
        (FirstName, LastName, Patronymic, [Position], [Floor], PersonnelNumber, Phone, IsActive)
    SELECT v.FirstName, v.LastName, v.Patronymic, v.[Position],
           v.[Floor], v.PersonnelNumber, v.Phone, 1
    FROM (VALUES
        (N'Анна', N'Соколова', N'Игоревна', N'Администратор',
         NULL, N'А-001', N'+7 (000) 000-00-01'),
        (N'Мария', N'Иванова', N'Сергеевна', N'Горничная',
         1, N'С-001', N'+7 (000) 000-00-02'),
        (N'Дмитрий', N'Петров', N'Алексеевич', N'Сотрудник ресторана',
         2, N'С-002', N'+7 (000) 000-00-03')
    ) AS v(FirstName, LastName, Patronymic, [Position],
           [Floor], PersonnelNumber, Phone)
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Employee25 e
        WHERE e.PersonnelNumber = v.PersonnelNumber
    );

    IF NOT EXISTS (SELECT 1 FROM dbo.HotelSettings25 WHERE ID = 1)
        INSERT dbo.HotelSettings25
            (ID, HotelName, [Address], Phone, Email)
        VALUES
            (1, N'Гостиница «Айзатулин»',
             N'г. Ярославль, ул. Учебная, д. 1',
             N'+7 (000) 000-00-00', N'info@example.test');

    INSERT dbo.Shift25
        (EmployeeID, ShiftDate, StartTime, EndTime, [Floor], Note)
    SELECT e.ID, CONVERT(date, v.DayText, 112),
           CONVERT(time(0), v.StartText),
           CONVERT(time(0), v.EndText),
           v.[Floor], v.Note
    FROM (VALUES
        (N'С-001', N'20260930', N'08:00', N'16:00', 1, N'Утренняя смена'),
        (N'С-001', N'20261001', N'08:00', N'16:00', 1, N'Утренняя смена'),
        (N'С-002', N'20260930', N'12:00', N'20:00', 2, N'Дневная смена')
    ) AS v(PersonnelNumber, DayText, StartText, EndText, [Floor], Note)
    INNER JOIN dbo.Employee25 e
        ON e.PersonnelNumber = v.PersonnelNumber
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Shift25 s
        WHERE s.EmployeeID = e.ID
          AND s.ShiftDate = CONVERT(date, v.DayText, 112)
          AND s.StartTime = CONVERT(time(0), v.StartText)
    );

    -- Уборки появятся только если в Room25 уже есть номера
    INSERT dbo.Cleaning25
        (RoomID, EmployeeID, CleaningDate, [Status], Comment)
    SELECT TOP (2)
        r.ID, e.ID, CONVERT(date, '20260930', 112),
        N'Запланирована', N'Проверить номер и сменить бельё'
    FROM (
        SELECT TOP (2) ID, RoomNumber
        FROM dbo.Room25
        ORDER BY RoomNumber, ID
    ) r
    INNER JOIN dbo.Employee25 e
        ON e.PersonnelNumber = N'С-001'
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Cleaning25 c
        WHERE c.RoomID = r.ID
          AND c.CleaningDate = CONVERT(date, '20260930', 112)
    )
    ORDER BY r.RoomNumber;

    INSERT dbo.Message25 (EmployeeID, Subject, Body, [Status])
    SELECT e.ID, v.Subject, v.Body, N'Новое'
    FROM (VALUES
        (N'С-001', N'Нужны чистые полотенца',
         N'Для номеров первого этажа требуется пополнить запас полотенец.'),
        (N'С-002', N'Заявка на обслуживание',
         N'Прошу проверить наличие дополнительных услуг для гостей второго этажа.')
    ) AS v(PersonnelNumber, Subject, Body)
    INNER JOIN dbo.Employee25 e
        ON e.PersonnelNumber = v.PersonnelNumber
    WHERE NOT EXISTS (
        SELECT 1 FROM dbo.Message25 m
        WHERE m.EmployeeID = e.ID
          AND m.Subject = v.Subject
    );

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
GO

-- Проверка количества записей
SELECT N'Discount25' AS TableName, COUNT(*) AS RowsCount
FROM dbo.Discount25
UNION ALL SELECT N'Employee25', COUNT(*) FROM dbo.Employee25
UNION ALL SELECT N'UserAccount25', COUNT(*) FROM dbo.UserAccount25
UNION ALL SELECT N'Shift25', COUNT(*) FROM dbo.Shift25
UNION ALL SELECT N'Cleaning25', COUNT(*) FROM dbo.Cleaning25
UNION ALL SELECT N'Message25', COUNT(*) FROM dbo.Message25
UNION ALL SELECT N'HotelSettings25', COUNT(*) FROM dbo.HotelSettings25;