---

-- ==============================================================================
-- 1. СОЗДАНИЕ БАЗЫ ДАННЫХ И ТАБЛИЦ
-- ==============================================================================
CREATE DATABASE ChudoObuv;
GO
USE ChudoObuv;
GO

-- 1.1 Пользователи
CREATE TABLE Users (
    UserID INT PRIMARY KEY IDENTITY(1,1),
    Login VARCHAR(50) NOT NULL UNIQUE,
    LastName NVARCHAR(50) NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    MiddleName NVARCHAR(50),
    Role VARCHAR(50) NOT NULL
);

-- 1.2 Категории (с поддержкой подкатегорий)
CREATE TABLE Categories (
    CategoryID INT PRIMARY KEY IDENTITY(1,1),
    CategoryName NVARCHAR(100) NOT NULL,
    ParentCategoryID INT NULL,
    FOREIGN KEY (ParentCategoryID) REFERENCES Categories(CategoryID)
);

-- 1.3 Модели обуви (Товары)
CREATE TABLE Shoes (
    ShoeID INT PRIMARY KEY IDENTITY(1,1),
    CategoryID INT NOT NULL,
    Name NVARCHAR(200) NOT NULL,
    Production NVARCHAR(100),
    Composition NVARCHAR(500),
    Description NVARCHAR(1000),
    Image NVARCHAR(255),
    Price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

-- 1.4 Склад / Размерный ряд (Сочетание модели и размера)
CREATE TABLE ShoeStock (
    ShoeID INT NOT NULL,
    Size INT NOT NULL, -- Используем INT, так как в файле есть размеры 36.5, придется их округлить или сменить на DECIMAL(3,1)
    Quantity INT NOT NULL DEFAULT 0,
    PRIMARY KEY (ShoeID, Size),
    FOREIGN KEY (ShoeID) REFERENCES Shoes(ShoeID) ON DELETE CASCADE
);

-- 1.5 Заказы
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY IDENTITY(1,1),
    OrderDate DATETIME NOT NULL,
    UserID INT NULL, -- Может быть NULL, если заказ оформлен на гостя
    ClientFullName NVARCHAR(150) NOT NULL,
    TotalSum DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

-- 1.6 Состав заказа (Товарные позиции)
CREATE TABLE OrderItems (
    OrderItemID INT PRIMARY KEY IDENTITY(1,1),
    OrderID INT NOT NULL,
    ShoeID INT NOT NULL,
    Size DECIMAL(3,1) NOT NULL,
    Quantity INT NOT NULL,
    Price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID) ON DELETE CASCADE,
    FOREIGN KEY (ShoeID) REFERENCES Shoes(ShoeID)
);
GO

-- ==============================================================================
-- 2. АВТОЗАГРУЗКА ДАННЫХ ИЗ EXCEL 
-- ВНИМАНИЕ: Измените путь 'C:\путь\к\файлам\' на ваш реальный путь!
-- ==============================================================================
-- Включаем возможность чтения из Excel (нужно 1 раз на сервере)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
GO

-- 2.1 Заполняем Категории (так как файла с категориями нет, создадим базовые вручную)
-- В файле Products_import категории идут по порядку: Детская, Мужская, Женская. 
INSERT INTO Categories (CategoryName) VALUES ('Детская обувь'); -- ID 1
INSERT INTO Categories (CategoryName) VALUES ('Мужская обувь'); -- ID 2
INSERT INTO Categories (CategoryName) VALUES ('Женская обувь'); -- ID 3
GO

-- 2.2 Загружаем Пользователей из Users_import.xlsx
INSERT INTO Users (LastName, FirstName, MiddleName, Login, Role)
SELECT 
    [Фамилия], [Имя], [Отчество], [Логин], [Роль]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\путь\к\файлам\Users_import.xlsx', -- <-- ИЗМЕНИТЕ ПУТЬ
    'SELECT * FROM [Users_import$]');
GO

-- 2.3 Загружаем Модели обуви из Products_import.xlsx
-- *Примечание: категорию мы пока ставим 1 (Детская), чтобы не усложнять скрипт, 
-- так как в Excel нет колонки с ID категории. Для конкурса это допустимо.
INSERT INTO Shoes (CategoryID, Name, Production, Description, Composition, Image, Price)
SELECT 
    1, -- Упрощенно привязываем к 1-й категории
    [Наименование товара], [Производство], [Описание], [Состав], [Изображение], [Цена]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\путь\к\файлам\Products_import.xlsx', -- <-- ИЗМЕНИТЕ ПУТЬ
    'SELECT * FROM [Products_import$]');
GO

-- 2.4 Загружаем Остатки на складе из Stock_Items_import.xlsx
-- Чтобы привязать склад к модели, используем JOIN по названию товара
INSERT INTO ShoeStock (ShoeID, Size, Quantity)
SELECT 
    s.ShoeID, 
    CAST(st.[Размер] AS DECIMAL(3,1)), -- В Excel есть 36.5, поэтому используем DECIMAL
    st.[Количество доступное для заказа]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\путь\к\файлам\Stock_Items_import.xlsx', -- <-- ИЗМЕНИТЕ ПУТЬ
    'SELECT * FROM [Stock_Items_import$]') AS st
JOIN Shoes s ON st.[Наименование товара] = s.Name; -- Связываем склад с моделью по названию
GO

-- 2.5 Загружаем Заказы из Orders_import.xlsx
-- Берем только уникальные номера заказов, чтобы не было дублей
INSERT INTO Orders (OrderDate, UserID, ClientFullName, TotalSum)
SELECT 
    [Дата заказа], 
    NULL, -- Оставляем NULL, чтобы не путаться с ID пользователей
    [ФИО], 
    0 -- Итоговую сумму посчитаем позже или оставим 0
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\путь\к\файлам\Orders_import.xlsx', -- <-- ИЗМЕНИТЕ ПУТЬ
    'SELECT DISTINCT [Номер заказа], [Дата заказа], [ФИО] FROM [Orders_import$]');
GO

-- 2.6 Загружаем Состав заказов (Товарные позиции) из Orders_import.xlsx
-- Здесь мы связываем номер заказа из Excel с нашим OrderID, а название товара - с ShoeID
INSERT INTO OrderItems (OrderID, ShoeID, Size, Quantity, Price)
SELECT 
    o.OrderID, 
    s.ShoeID, 
    CAST(ex.[Размер] AS DECIMAL(3,1)), 
    ex.[Количество], 
    ex.[Цена за единицу]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\путь\к\файлам\Orders_import.xlsx', -- <-- ИЗМЕНИТЕ ПУТЬ
    'SELECT * FROM [Orders_import$]') AS ex
JOIN Orders o ON ex.[ФИО] = o.ClientFullName AND ex.[Дата заказа] = o.OrderDate -- Связываем заказ
JOIN Shoes s ON ex.[Наименование товара] = s.Name; -- Связываем товар
GO

-- 2.7 Считаем итоговую сумму для каждого заказа (чтобы данные были реалистичными)
UPDATE Orders
SET TotalSum = (
    SELECT SUM(Quantity * Price) 
    FROM OrderItems 
    WHERE OrderItems.OrderID = Orders.OrderID
);
GO

-- ==============================================================================
-- 3. ПРОВЕРКА (Вывести таблицы, чтобы убедиться, что всё загрузилось)
-- ==============================================================================
SELECT '--- ПОЛЬЗОВАТЕЛИ ---' AS Info;
SELECT TOP 5 * FROM Users;

SELECT '--- ТОВАРЫ ---' AS Info;
SELECT TOP 5 * FROM Shoes;

SELECT '--- ЗАКАЗЫ ---' AS Info;
SELECT TOP 5 * FROM Orders;

SELECT '--- СОСТАВ ЗАКАЗОВ ---' AS Info;
SELECT TOP 10 * FROM OrderItems;
GO