CREATE TABLE Payments (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Payments PRIMARY KEY,
    order_id INT NOT NULL,
    amount INT NOT NULL,
    date DATETIME NOT NULL
)
GO

CREATE TABLE Boxes (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Boxes PRIMARY KEY,
    order_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    wrapping_id INT NOT NULL,
    volume REAL NOT NULL,
    weight REAL NOT NULL
)
GO

CREATE TABLE Wrappings (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Wrappings PRIMARY KEY,
    type NVARCHAR(30) NOT NULL,
    price INT NOT NULL
)
GO

CREATE TABLE Customers (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Customers PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    email NVARCHAR(96) NOT NULL
)
GO

CREATE TABLE Orders (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Orders PRIMARY KEY,
    customer_id INT NOT NULL,
    city_id INT NOT NULL,
    date DATETIME NOT NULL
)
GO

CREATE TABLE Warehouses (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Warehouses PRIMARY KEY,
    city_id INT NOT NULL,
    area INT NOT NULL
)
GO

CREATE TABLE Shipments (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Shipments PRIMARY KEY,
    order_id INT NOT NULL,
    delivery_id INT NOT NULL
)
GO

CREATE TABLE Deliveries (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Deliveries PRIMARY KEY,
    driver_id INT NOT NULL,
    vehicle_id INT NOT NULL,
    route_id INT NOT NULL,
    date DATETIME NOT NULL
)
GO

CREATE TABLE Cities (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Cities PRIMARY KEY,
    name NVARCHAR(30) NOT NULL
)
GO

CREATE TABLE Drivers (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Drivers PRIMARY KEY,
    city_id INT NOT NULL,
    salary INT NOT NULL,
    on_delivery BIT NOT NULL
)
GO

CREATE TABLE Vehicles (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Vehicles PRIMARY KEY,
    name NVARCHAR(30) NOT NULL,
    tonnage INT NOT NULL,
    gas_consumption FLOAT NOT NULL
)
GO

CREATE TABLE Routes (
    id INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Routes PRIMARY KEY,
    city1_id INT NOT NULL,
    city2_id INT NOT NULL,
    distance FLOAT NOT NULL
)
GO


ALTER TABLE Payments ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO


ALTER TABLE Boxes ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO

ALTER TABLE Boxes ADD FOREIGN KEY (warehouse_id) REFERENCES Warehouses (id)
GO

ALTER TABLE Boxes ADD FOREIGN KEY (wrapping_id) REFERENCES Wrappings (id)
GO


ALTER TABLE Orders ADD FOREIGN KEY (customer_id) REFERENCES Customers (id)
GO

ALTER TABLE Orders ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO


ALTER TABLE Warehouses ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO


ALTER TABLE Shipments ADD FOREIGN KEY (order_id) REFERENCES Orders (id)
GO

ALTER TABLE Shipments ADD FOREIGN KEY (delivery_id) REFERENCES Deliveries (id)
GO


ALTER TABLE Deliveries ADD FOREIGN KEY (driver_id) REFERENCES Drivers (id)
GO

ALTER TABLE Deliveries ADD FOREIGN KEY (vehicle_id) REFERENCES Vehicles (id)
GO

ALTER TABLE Deliveries ADD FOREIGN KEY (route_id) REFERENCES Routes (id)
GO


ALTER TABLE Drivers ADD FOREIGN KEY (city_id) REFERENCES Cities (id)
GO


ALTER TABLE Routes ADD FOREIGN KEY (city1_id) REFERENCES Cities (id)
GO

ALTER TABLE Routes ADD FOREIGN KEY (city2_id) REFERENCES Cities (id)
GO

-- Values

-- Wrappings
INSERT INTO Wrappings (type, price)
VALUES (N'Жёсткая', 100),
       (N'Полужёсткая', 100),
       (N'Мягкая', 100),
       (N'Воздушно-Пузырьковая', 100),
       (N'Рулон', 100);
GO

-- Customers
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @customers_count)
begin
    DECLARE @customer_name NVARCHAR = CAST(@n AS NVARCHAR);
    INSERT INTO Customers (name, email) VALUES (@customer_name, @customer_name + '@example.ru');
    SET @n = @n + 1;
end
GO

-- Cities
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @cities_count)
begin
    DECLARE @name NVARCHAR(30) = N'Подольск-' + CAST(@n AS NVARCHAR);
    INSERT INTO Cities (name) VALUES (@name);
    SET @n = @n + 1;
end
GO

-- Orders
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @orders_count)
begin
    DECLARE @customer_id INT = 1 + RAND() * (@customers_count - 1);
    DECLARE @city_id INT = 1 + RAND() * (@cities_count - 1);
    INSERT INTO Orders (customer_id, city_id, date) VALUES (@customer_id, @city_id, '2021-12-05');
    SET @n = @n + 1;
end
GO

-- Payments
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
declare @order_id int, @amount int, @date datetime;
while (@n < @payments_count / 2)
begin
    SET @order_id = 1 + RAND() * (@orders_count - 1);
    SET @amount = RAND() * 50000 + 20000;
    SET @date = '2021-12-05'

    INSERT INTO Payments (order_id, amount, date) VALUES (@order_id, @amount, @date);
    SET @n = @n + 1;
end
set @n = 0;
while (@n < @payments_count / 2)
begin
    SET @order_id = 1 + RAND() * (@orders_count - 1);
    SET @amount = RAND() * 50000 + 20000;
    SET @date = '2021-10-02'

    INSERT INTO Payments (order_id, amount, date) VALUES (@order_id, @amount, @date);
    SET @n = @n + 1;
end
GO

-- Warehouses
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @warehouses_count)
begin
    DECLARE @city_id INT = 1 + RAND() * (@cities_count - 1);
    DECLARE @area INT = RAND() * 100000 + 20000;
    INSERT INTO Warehouses (city_id, area) VALUES (@city_id, @area);
    SET @n = @n + 1;
end
GO

-- Boxes
DECLARE @customers_count INT = 100000;
DECLARE @cities_count INT = 300;
DECLARE @orders_count INT = 100000;
DECLARE @payments_count INT = 100000;
DECLARE @warehouses_count INT = 400;
DECLARE @boxes_count INT = 100000
DECLARE @n INT = 0;
while (@n < @boxes_count)
begin
    declare @order_id int, @warehouse_id int, @wrapping_id int;
    declare @volume float, @weight float;

    SET @order_id = 1 + RAND() * (@orders_count - 1);
    SET @warehouse_id = 1 + RAND() * (@warehouses_count - 1);
    SET @wrapping_id = 1 + RAND() * 4;

    SET @volume = rand() + 0.1;
    SET @weight = @volume * 800;

    INSERT INTO Boxes (order_id, warehouse_id, wrapping_id, volume, weight)
    VALUES (@order_id, @warehouse_id, @wrapping_id, @volume, @weight);
    SET @n = @n + 1;
end
GO