---

USE user333_db;
GO

-- 1. Принудительно удаляем старые внешние ключи, если они существуют и блокируют удаление
ALTER TABLE Race DROP CONSTRAINT IF EXISTS FK_Race_Orders;
ALTER TABLE Race DROP CONSTRAINT IF EXISTS FK_Race_Driver;
ALTER TABLE Race DROP CONSTRAINT IF EXISTS FK_Race_Transport;
ALTER TABLE Orders DROP CONSTRAINT IF EXISTS FK_Orders_Client;
GO

-- Также на всякий случай удаляем старые ключи из прошлой практической (вдруг имена пересеклись)
ALTER TABLE Dogovor_Arendy DROP CONSTRAINT IF EXISTS FK_Dogovor_Klient;
ALTER TABLE Dogovor_Arendy DROP CONSTRAINT IF EXISTS FK_Dogovor_TS;
GO

-- 2. Теперь SQL Server обязан беспрепятственно удалить таблицы
DROP TABLE IF EXISTS Race;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Transport;
DROP TABLE IF EXISTS Client;
DROP TABLE IF EXISTS Driver;
GO

-- 3. Создаем чистую структуру заново
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

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

CREATE TABLE Transport (
    id_Transport INT IDENTITY(1,1) PRIMARY KEY,
    Gosnomer NVARCHAR(50) NOT NULL,
    Marka NVARCHAR(50) NOT NULL
);
GO

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

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