---

CREATE TABLE Roles (
    RoleID INT IDENTITY(1,1) PRIMARY KEY,
    RoleName NVARCHAR(50) NOT NULL
);

CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    Login NVARCHAR(50) NOT NULL UNIQUE,
    Password NVARCHAR(50) NOT NULL,
    FullName NVARCHAR(100) NOT NULL,
    RoleID INT NOT NULL,
    CONSTRAINT FK_Users_Roles FOREIGN KEY (RoleID)
        REFERENCES Roles(RoleID)
);

CREATE TABLE Clients (
    ClientID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Phone NVARCHAR(20) NOT NULL,
    BirthDate DATE NOT NULL,
    MembershipNumber NVARCHAR(30) NOT NULL UNIQUE
);

CREATE TABLE Trainers (
    TrainerID INT IDENTITY(1,1) PRIMARY KEY,
    FullName NVARCHAR(100) NOT NULL,
    Specialization NVARCHAR(100) NOT NULL,
    Phone NVARCHAR(20) NOT NULL
);

CREATE TABLE TrainingBookings (
    BookingID INT IDENTITY(1,1) PRIMARY KEY,
    ClientID INT NOT NULL,
    TrainerID INT NOT NULL,
    TrainingDate DATE NOT NULL,
    TrainingTime TIME NOT NULL,
    Comment NVARCHAR(200) NULL,
    CONSTRAINT FK_TrainingBookings_Clients FOREIGN KEY (ClientID)
        REFERENCES Clients(ClientID),
    CONSTRAINT FK_TrainingBookings_Trainers FOREIGN KEY (TrainerID)
        REFERENCES Trainers(TrainerID)
);




INSERT INTO Roles (RoleName)
VALUES ('Менеджер'), ('Администратор');

INSERT INTO Users (Login, Password, FullName, RoleID)
VALUES 
('manager1', '123', 'Иванов Иван Иванович', 1),
('admin1', '123', 'Петров Петр Петрович', 2);

INSERT INTO Clients (FullName, Phone, BirthDate, MembershipNumber)
VALUES
('Смирнов Алексей Олегович', '+79990001111', '2000-05-12', 'CL001'),
('Кузнецова Мария Сергеевна', '+79990002222', '1999-08-20', 'CL002'),
('Орлов Денис Игоревич', '+79990003333', '2002-02-15', 'CL003');

INSERT INTO Trainers (FullName, Specialization, Phone)
VALUES
('Соколов Артем Викторович', 'Фитнес', '+79991110011'),
('Лебедева Анна Павловна', 'Йога', '+79991110022'),
('Морозов Кирилл Андреевич', 'Силовые тренировки', '+79991110033');

INSERT INTO TrainingBookings (ClientID, TrainerID, TrainingDate, TrainingTime, Comment)
VALUES
(1, 1, '2026-04-20', '10:00', 'Первая тренировка'),
(2, 2, '2026-04-21', '12:30', 'Йога для начинающих'),
(3, 3, '2026-04-22', '15:00', NULL);