---

USE user333_db;
GO

-- 1. Умное автоматическое удаление ВСЕХ внешних ключей для наших пяти таблиц
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + N'.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + 
               N' DROP CONSTRAINT ' + QUOTENAME(name) + N';' + CHAR(13)
FROM sys.foreign_keys
WHERE OBJECT_NAME(referenced_object_id) IN ('Client', 'Orders', 'Driver', 'Transport', 'Race')
   OR OBJECT_NAME(parent_object_id) IN ('Client', 'Orders', 'Driver', 'Transport', 'Race');

EXEC sp_executesql @sql;
GO

-- 2. Теперь спокойно удаляем старые таблицы
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