---

-- ==================================================
-- ОТМЕНА ВСЕХ ДЕЙСТВИЙ И ПОЛНАЯ ПЕРЕЗАПУСК
-- ==================================

-- 1. Удаляем представление (если существует)
IF OBJECT_ID('OrdersProductsCustomers', 'V') IS NOT NULL 
    DROP VIEW OrdersProductsCustomers;
GO

-- 2. Удаляем все таблицы в правильном порядке
IF OBJECT_ID('Orders', 'U') IS NOT NULL 
    DROP TABLE Orders;
GO

IF OBJECT_ID('Products', 'U') IS NOT NULL 
    DROP TABLE Products;
GO

IF OBJECT_ID('Customers', 'U') IS NOT NULL 
    DROP TABLE Customers;
GO

-- 3. Создаём таблицы заново
CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);
GO

CREATE TABLE Customers
(
    Id INT IDENTITY PRIMARY KEY,
    FirstName NVARCHAR(30) NOT NULL
);
GO

CREATE TABLE Orders
(
    Id INT IDENTITY PRIMARY KEY,
    ProductId INT NOT NULL REFERENCES Products(Id) ON DELETE CASCADE,
    CustomerId INT NOT NULL REFERENCES Customers(Id) ON DELETE CASCADE,
    CreatedAt DATE NOT NULL,
    ProductCount INT DEFAULT 1,
    Price MONEY NOT NULL
);
GO

-- 4. Создаём представление (ОДНО в своём пакете)
CREATE VIEW OrdersProductsCustomers AS 
SELECT 
    Orders.CreatedAt AS OrderDate, 
    Customers.FirstName AS Customer,
    Products.ProductName AS Product  
FROM Orders 
INNER JOIN Products ON Orders.ProductId = Products.Id
INNER JOIN Customers ON Orders.CustomerId = Customers.Id;
GO

-- 5. Добавляем тестовые данные
INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price) 
VALUES 
    ('iPhone 15', 'Apple', 10, 799.99),
    ('Galaxy S24', 'Samsung', 15, 699.99),
    ('Pixel 8', 'Google', 8, 599.99);
GO

INSERT INTO Customers (FirstName) 
VALUES 
    ('Иван'), 
    ('Мария'), 
    ('Петр');
GO

INSERT INTO Orders (ProductId, CustomerId, CreatedAt, ProductCount, Price)
VALUES 
    (1, 1, '2025-01-15', 1, 799.99),
    (2, 2, '2025-01-16', 2, 1399.98),
    (1, 3, '2025-01-17', 1, 799.99);
GO

-- 6. Проверяем результат
PRINT '==============================';
PRINT 'ГОТОВО! База данных очищена и создана заново';
PRINT '==============================';
GO

SELECT * FROM Products;
SELECT * FROM Customers;
SELECT * FROM Orders;
GO

SELECT * FROM OrdersProductsCustomers;
GO