---

-- Удаление если есть
DROP TABLE IF EXISTS Checks;
DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Products;
DROP TABLE IF EXISTS Shops;
DROP TABLE IF EXISTS Postavshchiki;

-- 1. Поставщик
CREATE TABLE Postavshchiki (
    SupplierID INT PRIMARY KEY,
    SupplierName VARCHAR(100),
    Address VARCHAR(200)
);

-- 2. Магазин
CREATE TABLE Shops (
    ShopID INT PRIMARY KEY,
    ShopName VARCHAR(100),
    Address VARCHAR(200)
);

-- 3. Товар
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

-- 4. Заказ
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE,
    SupplierID INT,
    FOREIGN KEY (SupplierID) REFERENCES Postavshchiki(SupplierID)
);

-- 5. Чек (теперь содержит товар!)
CREATE TABLE Checks (
    CheckID INT,
    OrderID INT,
    ShopID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10,2),
    Sum DECIMAL(10,2),
    Tax DECIMAL(10,2),
    TotalAmount DECIMAL(10,2),
    PRIMARY KEY (CheckID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ShopID) REFERENCES Shops(ShopID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

---------------------------------------------------
-- ДАННЫЕ
---------------------------------------------------

INSERT INTO Postavshchiki VALUES
(34, 'Volga LLC', 'Pannina St. 25');

INSERT INTO Shops VALUES
(1, 'Sport Shop', 'Pushkina St. 45.6, Pavilion 22');

INSERT INTO Products VALUES
(1, 'Pen', 2.80),
(2, 'Gouache', 5.60);

INSERT INTO Orders VALUES
(321, '2024-05-03', 34);

-- Чек = 2 строки (по товарам)
INSERT INTO Checks VALUES
(1, 321, 1, 1, 5, 2.80, 14.00, 1.76, 57.02),
(1, 321, 1, 2, 8, 5.60, 44.80, 1.76, 57.02);