---

USE user333_db;
GO

-- 1. Сначала удаляем таблицы, которые ССЫЛАЮТСЯ на другие (зависимые)
DROP TABLE IF EXISTS Race;
DROP TABLE IF EXISTS Orders;

-- 2. Только теперь удаляем главные таблицы, на которые раньше ссылались внешние ключи
DROP TABLE IF EXISTS Transport;
DROP TABLE IF EXISTS Client;
DROP TABLE IF EXISTS Driver;
GO

-- 3. Создаем таблицу: Водитель (Driver)
CREATE TABLE Driver (
    id_Driver INT IDENTITY(1,1) PRIMARY KEY,
    FIO NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(50) NOT NULL,
    IDDriver NVARCHAR(50) NOT NULL,
    Status NVARCHAR(50) NOT NULL
);
GO

-- 4. Создаем таблицу: Клиент (Client)
CREATE TABLE Client (
    id_Client INT IDENTITY(1,1) PRIMARY KEY,
    NameCompany NVARCHAR(50) NOT NULL,
    Phone NVARCHAR(50) NOT NULL,
    Email NVARCHAR(50) NOT NULL,
    Status NVARCHAR(50) NOT NULL
);
GO

-- 5. Создаем таблицу: Транспорт (Transport)
CREATE TABLE Transport (
    id_Transport INT IDENTITY(1,1) PRIMARY KEY,
    Gosnomer NVARCHAR(50) NOT NULL,
    Marka NVARCHAR(50) NOT NULL
);
GO

-- 6. Создаем таблицу: Заказы (Orders)
CREATE TABLE Orders (
    id_Order INT IDENTITY(1,1) PRIMARY KEY,
    id_Client INT NOT NULL,
    Date DATE NOT NULL,
    Weight DECIMAL(10,2) NOT NULL,
    Status NVARCHAR(50) NOT NULL,
    
    CONSTRAINT FK_Orders_Client FOREIGN KEY (id_Client) 
        REFERENCES Client(id_Client) ON DELETE CASCADE
);
GO

-- 7. Создаем таблицу: Рейсы (Race)
CREATE TABLE Race (
    id_Race INT IDENTITY(1,1) PRIMARY KEY,
    id_Order INT NOT NULL,
    id_Driver INT NOT NULL,
    id_Transport INT NOT NULL,
    Dateofstart DATE NOT NULL,
    Dateofend DATE NOT NULL,
    Price MONEY NOT NULL,
    
    CONSTRAINT FK_Race_Orders FOREIGN KEY (id_Order) 
        REFERENCES Orders(id_Order) ON DELETE CASCADE,
        
    CONSTRAINT FK_Race_Driver FOREIGN KEY (id_Driver) 
        REFERENCES Driver(id_Driver),
        
    CONSTRAINT FK_Race_Transport FOREIGN KEY (id_Transport) 
        REFERENCES Transport(id_Transport)
);
GO