---

/*
АРОМА ДОМ — единый автономный скрипт SQL Server 2017+.
Содержит структуру БД (15 таблиц), данные из пяти Excel-файлов,
справочник единиц измерения, роли/разрешения и 2 представления.

ВАЖНО:
1. Для чистого развёртывания используйте сервер без существующей БД AromaDom.
2. При повторном запуске существующие записи не удаляются и не дублируются.
*/

IF DB_ID(N'AromaDom') IS NULL
    CREATE DATABASE [AromaDom];
GO
USE [AromaDom];
GO
SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

-- ОСНОВНЫЕ СУЩНОСТИ И СПРАВОЧНИКИ
IF OBJECT_ID(N'dbo.MaterialTypes',N'U') IS NULL
CREATE TABLE dbo.MaterialTypes (
 MaterialTypeId INT IDENTITY(1,1) CONSTRAINT PK_MaterialTypes PRIMARY KEY,
 TypeName NVARCHAR(150) NOT NULL CONSTRAINT UQ_MaterialTypes_TypeName UNIQUE,
 LossPercent DECIMAL(7,4) NOT NULL CONSTRAINT CK_MaterialTypes_Loss CHECK (LossPercent >= 0 AND LossPercent < 100)
);
IF OBJECT_ID(N'dbo.ProductTypes',N'U') IS NULL
CREATE TABLE dbo.ProductTypes (
 ProductTypeId INT IDENTITY(1,1) CONSTRAINT PK_ProductTypes PRIMARY KEY,
 TypeName NVARCHAR(150) NOT NULL CONSTRAINT UQ_ProductTypes_TypeName UNIQUE,
 TypeCoefficient DECIMAL(12,6) NOT NULL CONSTRAINT CK_ProductTypes_Coeff CHECK (TypeCoefficient > 0)
);
IF OBJECT_ID(N'dbo.Units',N'U') IS NULL
CREATE TABLE dbo.Units (
 UnitId INT IDENTITY(1,1) CONSTRAINT PK_Units PRIMARY KEY,
 Symbol NVARCHAR(20) NOT NULL CONSTRAINT UQ_Units_Symbol UNIQUE,
 UnitName NVARCHAR(80) NOT NULL
);
IF OBJECT_ID(N'dbo.Materials',N'U') IS NULL
CREATE TABLE dbo.Materials (
 MaterialId INT IDENTITY(1,1) CONSTRAINT PK_Materials PRIMARY KEY,
 MaterialName NVARCHAR(250) NOT NULL CONSTRAINT UQ_Materials_Name UNIQUE,
 MaterialTypeId INT NOT NULL CONSTRAINT FK_Materials_Type REFERENCES dbo.MaterialTypes(MaterialTypeId),
 UnitId INT NOT NULL CONSTRAINT FK_Materials_Unit REFERENCES dbo.Units(UnitId),
 UnitPrice DECIMAL(18,2) NOT NULL CONSTRAINT CK_Materials_Price CHECK(UnitPrice >= 0),
 StockQuantity DECIMAL(18,3) NOT NULL CONSTRAINT DF_Materials_Stock DEFAULT 0 CONSTRAINT CK_Materials_Stock CHECK(StockQuantity >= 0),
 MinimumQuantity DECIMAL(18,3) NOT NULL CONSTRAINT DF_Materials_Min DEFAULT 0 CONSTRAINT CK_Materials_Min CHECK(MinimumQuantity >= 0),
 PackageQuantity DECIMAL(18,3) NOT NULL CONSTRAINT CK_Materials_Package CHECK(PackageQuantity > 0),
 IsActive BIT NOT NULL CONSTRAINT DF_Materials_Active DEFAULT 1
);
IF OBJECT_ID(N'dbo.Products',N'U') IS NULL
CREATE TABLE dbo.Products (
 ProductId INT IDENTITY(1,1) CONSTRAINT PK_Products PRIMARY KEY,
 Article NVARCHAR(40) NOT NULL CONSTRAINT UQ_Products_Article UNIQUE,
 ProductName NVARCHAR(250) NOT NULL CONSTRAINT UQ_Products_Name UNIQUE,
 ProductTypeId INT NOT NULL CONSTRAINT FK_Products_Type REFERENCES dbo.ProductTypes(ProductTypeId),
 MinimumPartnerPrice DECIMAL(18,2) NOT NULL CONSTRAINT CK_Products_Price CHECK(MinimumPartnerPrice >= 0),
 IsActive BIT NOT NULL CONSTRAINT DF_Products_Active DEFAULT 1
);
IF OBJECT_ID(N'dbo.ProductMaterials',N'U') IS NULL
CREATE TABLE dbo.ProductMaterials (
 ProductId INT NOT NULL CONSTRAINT FK_ProductMaterials_Product REFERENCES dbo.Products(ProductId),
 MaterialId INT NOT NULL CONSTRAINT FK_ProductMaterials_Material REFERENCES dbo.Materials(MaterialId),
 QuantityPerUnit DECIMAL(18,5) NOT NULL CONSTRAINT CK_ProductMaterials_Quantity CHECK(QuantityPerUnit > 0),
 CONSTRAINT PK_ProductMaterials PRIMARY KEY(ProductId,MaterialId)
);

-- УЧЁТНЫЕ ЗАПИСИ, РОЛИ И ПРАВА
IF OBJECT_ID(N'dbo.Roles',N'U') IS NULL
CREATE TABLE dbo.Roles (
 RoleId INT IDENTITY(1,1) CONSTRAINT PK_Roles PRIMARY KEY,
 RoleCode VARCHAR(40) NOT NULL CONSTRAINT UQ_Roles_Code UNIQUE,
 RoleName NVARCHAR(100) NOT NULL
);
IF OBJECT_ID(N'dbo.Users',N'U') IS NULL
CREATE TABLE dbo.Users (
 UserId INT IDENTITY(1,1) CONSTRAINT PK_Users PRIMARY KEY,
 Login NVARCHAR(100) NOT NULL CONSTRAINT UQ_Users_Login UNIQUE,
 PasswordHash NVARCHAR(255) NOT NULL,
 DisplayName NVARCHAR(200) NULL,
 IsActive BIT NOT NULL CONSTRAINT DF_Users_Active DEFAULT 1,
 CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_Users_Created DEFAULT SYSUTCDATETIME(),
 LastLoginAt DATETIME2(0) NULL
);
IF OBJECT_ID(N'dbo.UserRoles',N'U') IS NULL
CREATE TABLE dbo.UserRoles (
 UserId INT NOT NULL CONSTRAINT FK_UserRoles_User REFERENCES dbo.Users(UserId),
 RoleId INT NOT NULL CONSTRAINT FK_UserRoles_Role REFERENCES dbo.Roles(RoleId),
 CONSTRAINT PK_UserRoles PRIMARY KEY(UserId,RoleId)
);
IF OBJECT_ID(N'dbo.Permissions',N'U') IS NULL
CREATE TABLE dbo.Permissions (
 PermissionId INT IDENTITY(1,1) CONSTRAINT PK_Permissions PRIMARY KEY,
 PermissionCode VARCHAR(80) NOT NULL CONSTRAINT UQ_Permissions_Code UNIQUE,
 Description NVARCHAR(250) NOT NULL
);
IF OBJECT_ID(N'dbo.RolePermissions',N'U') IS NULL
CREATE TABLE dbo.RolePermissions (
 RoleId INT NOT NULL CONSTRAINT FK_RolePermissions_Role REFERENCES dbo.Roles(RoleId),
 PermissionId INT NOT NULL CONSTRAINT FK_RolePermissions_Permission REFERENCES dbo.Permissions(PermissionId),
 CONSTRAINT PK_RolePermissions PRIMARY KEY(RoleId,PermissionId)
);

-- РАСШИРЕНИЕ ДЛЯ ПОСТАВЩИКОВ, СКЛАДА И АУДИТА
IF OBJECT_ID(N'dbo.Suppliers',N'U') IS NULL
CREATE TABLE dbo.Suppliers (
 SupplierId INT IDENTITY(1,1) CONSTRAINT PK_Suppliers PRIMARY KEY,
 SupplierName NVARCHAR(250) NOT NULL CONSTRAINT UQ_Suppliers_Name UNIQUE,
 Phone NVARCHAR(40) NULL,
 Email NVARCHAR(150) NULL,
 IsActive BIT NOT NULL CONSTRAINT DF_Suppliers_Active DEFAULT 1
);
IF OBJECT_ID(N'dbo.MaterialSuppliers',N'U') IS NULL
CREATE TABLE dbo.MaterialSuppliers (
 SupplierId INT NOT NULL CONSTRAINT FK_MaterialSuppliers_Supplier REFERENCES dbo.Suppliers(SupplierId),
 MaterialId INT NOT NULL CONSTRAINT FK_MaterialSuppliers_Material REFERENCES dbo.Materials(MaterialId),
 SupplierPrice DECIMAL(18,2) NULL CONSTRAINT CK_MaterialSuppliers_Price CHECK(SupplierPrice >= 0),
 CONSTRAINT PK_MaterialSuppliers PRIMARY KEY(SupplierId,MaterialId)
);
IF OBJECT_ID(N'dbo.StockMovements',N'U') IS NULL
CREATE TABLE dbo.StockMovements (
 StockMovementId BIGINT IDENTITY(1,1) CONSTRAINT PK_StockMovements PRIMARY KEY,
 MaterialId INT NOT NULL CONSTRAINT FK_StockMovements_Material REFERENCES dbo.Materials(MaterialId),
 UserId INT NULL CONSTRAINT FK_StockMovements_User REFERENCES dbo.Users(UserId),
 SupplierId INT NULL CONSTRAINT FK_StockMovements_Supplier REFERENCES dbo.Suppliers(SupplierId),
 MovementType VARCHAR(12) NOT NULL CONSTRAINT CK_StockMovements_Type CHECK(MovementType IN ('RECEIPT','ISSUE','ADJUSTMENT')),
 QuantityChange DECIMAL(18,3) NOT NULL CONSTRAINT CK_StockMovements_Quantity CHECK(QuantityChange <> 0),
 MovementAt DATETIME2(0) NOT NULL CONSTRAINT DF_StockMovements_Date DEFAULT SYSUTCDATETIME(),
 Note NVARCHAR(500) NULL
);
IF OBJECT_ID(N'dbo.AuditLogs',N'U') IS NULL
CREATE TABLE dbo.AuditLogs (
 AuditLogId BIGINT IDENTITY(1,1) CONSTRAINT PK_AuditLogs PRIMARY KEY,
 UserId INT NULL CONSTRAINT FK_AuditLogs_User REFERENCES dbo.Users(UserId),
 EntityName NVARCHAR(100) NOT NULL,
 EntityId NVARCHAR(100) NOT NULL,
 ActionType VARCHAR(15) NOT NULL CONSTRAINT CK_AuditLogs_Action CHECK(ActionType IN ('INSERT','UPDATE','DELETE')),
 OccurredAt DATETIME2(0) NOT NULL CONSTRAINT DF_AuditLogs_Date DEFAULT SYSUTCDATETIME(),
 Details NVARCHAR(MAX) NULL
);
GO
-- Индексы для быстрого поиска и соединений
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Materials_Type' AND object_id=OBJECT_ID('dbo.Materials')) CREATE INDEX IX_Materials_Type ON dbo.Materials(MaterialTypeId);
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_Products_Type' AND object_id=OBJECT_ID('dbo.Products')) CREATE INDEX IX_Products_Type ON dbo.Products(ProductTypeId);
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_ProductMaterials_Material' AND object_id=OBJECT_ID('dbo.ProductMaterials')) CREATE INDEX IX_ProductMaterials_Material ON dbo.ProductMaterials(MaterialId);
IF NOT EXISTS(SELECT 1 FROM sys.indexes WHERE name='IX_StockMovements_MaterialDate' AND object_id=OBJECT_ID('dbo.StockMovements')) CREATE INDEX IX_StockMovements_MaterialDate ON dbo.StockMovements(MaterialId,MovementAt DESC);
GO


-- -----------------------------------------------
-- Исходные данные точно перенесены из XLSX в VALUES.
-- Временные таблицы существуют только на время запуска.
-- -----------------------------------------------
BEGIN TRY
    BEGIN TRANSACTION;

    -- Не пытаемся молча накатывать импорт поверх старой, несовместимой схемы.
    IF COL_LENGTH(N'dbo.MaterialTypes',N'LossPercent') IS NULL
       OR COL_LENGTH(N'dbo.Materials',N'UnitId') IS NULL
       OR COL_LENGTH(N'dbo.Materials',N'PackageQuantity') IS NULL
       OR COL_LENGTH(N'dbo.Products',N'ProductTypeId') IS NULL
       OR COL_LENGTH(N'dbo.ProductMaterials',N'QuantityPerUnit') IS NULL
       OR COL_LENGTH(N'dbo.Users',N'PasswordHash') IS NULL
       THROW 51000, N'Обнаружена несовместимая схема AromaDom. Используйте пустую базу данных.', 1;

    DROP TABLE IF EXISTS #SrcMaterialTypes;
    DROP TABLE IF EXISTS #SrcProductTypes;
    DROP TABLE IF EXISTS #SrcUnits;
    DROP TABLE IF EXISTS #SrcMaterials;
    DROP TABLE IF EXISTS #SrcProducts;
    DROP TABLE IF EXISTS #SrcRecipes;
    DROP TABLE IF EXISTS #SrcRoles;
    DROP TABLE IF EXISTS #SrcPermissions;
    DROP TABLE IF EXISTS #SrcRolePermissions;


-- 1. Типы материалов (АД_Material_type_import.xlsx)
CREATE TABLE #SrcMaterialTypes (
    TypeName NVARCHAR(150) NOT NULL,
    LossPercent DECIMAL(7,4) NOT NULL
);
INSERT INTO #SrcMaterialTypes (TypeName, LossPercent)
VALUES
    (N'Соевый воск', 0.12),
    (N'Пчелиный воск', 0.18),
    (N'Ароматическое масло', 0.25),
    (N'Краситель', 0.08),
    (N'Фитиль', 0.03),
    (N'Стеклянная тара', 0.04),
    (N'Керамическая тара', 0.06),
    (N'Декор', 0.10),
    (N'Упаковка', 0.05),
    (N'Этикетка', 0.02);

-- 2. Типы продукции (АД_Product_type_import.xlsx)
CREATE TABLE #SrcProductTypes (
    TypeName NVARCHAR(150) NOT NULL,
    TypeCoefficient DECIMAL(12,6) NOT NULL
);
INSERT INTO #SrcProductTypes (TypeName, TypeCoefficient)
VALUES
    (N'Классическая свеча', 1),
    (N'Премиальная свеча', 1.15),
    (N'Подарочный набор', 1.3),
    (N'Интерьерная свеча', 1.2),
    (N'Мини-свеча', 0.8),
    (N'Сезонная коллекция', 1.25);

-- 3. Единицы измерения из АД_Materials_import.xlsx
CREATE TABLE #SrcUnits (
    Symbol NVARCHAR(20) NOT NULL,
    UnitName NVARCHAR(80) NOT NULL
);
INSERT INTO #SrcUnits (Symbol, UnitName)
VALUES
    (N'кг', N'килограмм'),
    (N'л', N'литр'),
    (N'м', N'метр'),
    (N'шт', N'штука');

-- 4. Материалы (АД_Materials_import.xlsx)
CREATE TABLE #SrcMaterials (
    MaterialName NVARCHAR(250) NOT NULL,
    MaterialTypeName NVARCHAR(150) NOT NULL,
    UnitSymbol NVARCHAR(20) NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    StockQuantity DECIMAL(18,3) NOT NULL,
    MinimumQuantity DECIMAL(18,3) NOT NULL,
    PackageQuantity DECIMAL(18,3) NOT NULL
);
INSERT INTO #SrcMaterials (MaterialName, MaterialTypeName, UnitSymbol, UnitPrice, StockQuantity, MinimumQuantity, PackageQuantity)
VALUES
    (N'Соевый воск премиум', N'Соевый воск', N'кг', 890, 120, 40, 20),
    (N'Соевый воск EcoBlend', N'Соевый воск', N'кг', 760, 95, 30, 25),
    (N'Пчелиный воск натуральный', N'Пчелиный воск', N'кг', 1450, 48, 15, 10),
    (N'Масло лаванды', N'Ароматическое масло', N'л', 3200, 18.5, 6, 1),
    (N'Масло ванили', N'Ароматическое масло', N'л', 2950, 22, 8, 1),
    (N'Масло цитрусовое', N'Ароматическое масло', N'л', 2870, 16.7, 5, 1),
    (N'Краситель янтарный', N'Краситель', N'кг', 640, 6.2, 2, 0.5),
    (N'Краситель розовый', N'Краситель', N'кг', 615, 5.1, 1.5, 0.5),
    (N'Фитиль хлопковый 8 см', N'Фитиль', N'шт', 4.5, 2500, 800, 500),
    (N'Фитиль хлопковый 12 см', N'Фитиль', N'шт', 5.8, 1800, 600, 400),
    (N'Стакан прозрачный 200 мл', N'Стеклянная тара', N'шт', 28, 950, 300, 100),
    (N'Стакан матовый 250 мл', N'Стеклянная тара', N'шт', 34, 620, 200, 60),
    (N'Чаша керамическая белая', N'Керамическая тара', N'шт', 72, 280, 80, 40),
    (N'Сухоцветы лаванды', N'Декор', N'кг', 520, 4.8, 1.5, 0.3),
    (N'Лента сатиновая бежевая', N'Упаковка', N'м', 12, 340, 120, 50),
    (N'Коробка подарочная малая', N'Упаковка', N'шт', 18, 420, 150, 50),
    (N'Этикетка фирменная круглая', N'Этикетка', N'шт', 3.2, 1800, 500, 250),
    (N'Наклейка предупреждающая', N'Этикетка', N'шт', 2.1, 1600, 400, 200);

-- 5. Продукция (АД_Products_import.xlsx)
CREATE TABLE #SrcProducts (
    Article NVARCHAR(40) NOT NULL,
    ProductName NVARCHAR(250) NOT NULL,
    ProductTypeName NVARCHAR(150) NOT NULL,
    MinimumPartnerPrice DECIMAL(18,2) NOT NULL
);
INSERT INTO #SrcProducts (Article, ProductName, ProductTypeName, MinimumPartnerPrice)
VALUES
    (N'AR-001', N'Свеча «Лавандовый вечер»', N'Классическая свеча', 980),
    (N'AR-002', N'Свеча «Ванильный крем»', N'Классическая свеча', 950),
    (N'AR-003', N'Свеча «Цитрусовый сад»', N'Классическая свеча', 970),
    (N'AR-004', N'Свеча «Белый мускус»', N'Премиальная свеча', 1290),
    (N'AR-005', N'Свеча «Дикая орхидея»', N'Премиальная свеча', 1360),
    (N'AR-006', N'Свеча «Теплый янтарь»', N'Интерьерная свеча', 1490),
    (N'AR-007', N'Свеча «Сандаловое дерево»', N'Интерьерная свеча', 1520),
    (N'AR-008', N'Мини-свеча «Лаванда»', N'Мини-свеча', 420),
    (N'AR-009', N'Мини-свеча «Ваниль»', N'Мини-свеча', 410),
    (N'AR-010', N'Мини-свеча «Цитрус»', N'Мини-свеча', 430),
    (N'AR-011', N'Набор «Уютный дом»', N'Подарочный набор', 2190),
    (N'AR-012', N'Набор «Теплый вечер»', N'Подарочный набор', 2340),
    (N'AR-013', N'Свеча «Зимние специи»', N'Сезонная коллекция', 1380),
    (N'AR-014', N'Свеча «Хвойный лес»', N'Сезонная коллекция', 1410);

-- 6. Рецептуры (АД_Material_products_import.xlsx)
CREATE TABLE #SrcRecipes (
    MaterialName NVARCHAR(250) NOT NULL,
    ProductName NVARCHAR(250) NOT NULL,
    QuantityPerUnit DECIMAL(18,5) NOT NULL
);
INSERT INTO #SrcRecipes (MaterialName, ProductName, QuantityPerUnit)
VALUES
    (N'Соевый воск премиум', N'Свеча «Лавандовый вечер»', 0.18),
    (N'Масло лаванды', N'Свеча «Лавандовый вечер»', 0.009),
    (N'Фитиль хлопковый 8 см', N'Свеча «Лавандовый вечер»', 1),
    (N'Стакан прозрачный 200 мл', N'Свеча «Лавандовый вечер»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Лавандовый вечер»', 1),
    (N'Соевый воск премиум', N'Свеча «Ванильный крем»', 0.18),
    (N'Масло ванили', N'Свеча «Ванильный крем»', 0.01),
    (N'Фитиль хлопковый 8 см', N'Свеча «Ванильный крем»', 1),
    (N'Стакан прозрачный 200 мл', N'Свеча «Ванильный крем»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Ванильный крем»', 1),
    (N'Соевый воск EcoBlend', N'Свеча «Цитрусовый сад»', 0.175),
    (N'Масло цитрусовое', N'Свеча «Цитрусовый сад»', 0.011),
    (N'Фитиль хлопковый 8 см', N'Свеча «Цитрусовый сад»', 1),
    (N'Стакан прозрачный 200 мл', N'Свеча «Цитрусовый сад»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Цитрусовый сад»', 1),
    (N'Соевый воск премиум', N'Свеча «Белый мускус»', 0.22),
    (N'Масло ванили', N'Свеча «Белый мускус»', 0.006),
    (N'Фитиль хлопковый 12 см', N'Свеча «Белый мускус»', 1),
    (N'Стакан матовый 250 мл', N'Свеча «Белый мускус»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Белый мускус»', 1),
    (N'Соевый воск премиум', N'Свеча «Дикая орхидея»', 0.225),
    (N'Масло лаванды', N'Свеча «Дикая орхидея»', 0.004),
    (N'Фитиль хлопковый 12 см', N'Свеча «Дикая орхидея»', 1),
    (N'Стакан матовый 250 мл', N'Свеча «Дикая орхидея»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Дикая орхидея»', 1),
    (N'Пчелиный воск натуральный', N'Свеча «Теплый янтарь»', 0.28),
    (N'Краситель янтарный', N'Свеча «Теплый янтарь»', 0.004),
    (N'Фитиль хлопковый 12 см', N'Свеча «Теплый янтарь»', 1),
    (N'Чаша керамическая белая', N'Свеча «Теплый янтарь»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Теплый янтарь»', 1),
    (N'Пчелиный воск натуральный', N'Свеча «Сандаловое дерево»', 0.285),
    (N'Масло ванили', N'Свеча «Сандаловое дерево»', 0.003),
    (N'Фитиль хлопковый 12 см', N'Свеча «Сандаловое дерево»', 1),
    (N'Чаша керамическая белая', N'Свеча «Сандаловое дерево»', 1),
    (N'Этикетка фирменная круглая', N'Свеча «Сандаловое дерево»', 1),
    (N'Соевый воск EcoBlend', N'Мини-свеча «Лаванда»', 0.07),
    (N'Масло лаванды', N'Мини-свеча «Лаванда»', 0.003),
    (N'Фитиль хлопковый 8 см', N'Мини-свеча «Лаванда»', 1),
    (N'Стакан прозрачный 200 мл', N'Мини-свеча «Лаванда»', 1),
    (N'Соевый воск EcoBlend', N'Мини-свеча «Ваниль»', 0.07),
    (N'Масло ванили', N'Мини-свеча «Ваниль»', 0.003),
    (N'Фитиль хлопковый 8 см', N'Мини-свеча «Ваниль»', 1),
    (N'Стакан прозрачный 200 мл', N'Мини-свеча «Ваниль»', 1),
    (N'Соевый воск EcoBlend', N'Мини-свеча «Цитрус»', 0.07),
    (N'Масло цитрусовое', N'Мини-свеча «Цитрус»', 0.003),
    (N'Фитиль хлопковый 8 см', N'Мини-свеча «Цитрус»', 1),
    (N'Стакан прозрачный 200 мл', N'Мини-свеча «Цитрус»', 1),
    (N'Соевый воск премиум', N'Набор «Уютный дом»', 0.18),
    (N'Масло лаванды', N'Набор «Уютный дом»', 0.009),
    (N'Фитиль хлопковый 8 см', N'Набор «Уютный дом»', 1),
    (N'Стакан прозрачный 200 мл', N'Набор «Уютный дом»', 1),
    (N'Лента сатиновая бежевая', N'Набор «Уютный дом»', 1.2),
    (N'Коробка подарочная малая', N'Набор «Уютный дом»', 1),
    (N'Этикетка фирменная круглая', N'Набор «Уютный дом»', 1),
    (N'Соевый воск премиум', N'Набор «Теплый вечер»', 0.225),
    (N'Масло ванили', N'Набор «Теплый вечер»', 0.01),
    (N'Фитиль хлопковый 12 см', N'Набор «Теплый вечер»', 1),
    (N'Стакан матовый 250 мл', N'Набор «Теплый вечер»', 1),
    (N'Лента сатиновая бежевая', N'Набор «Теплый вечер»', 1.5),
    (N'Коробка подарочная малая', N'Набор «Теплый вечер»', 1),
    (N'Этикетка фирменная круглая', N'Набор «Теплый вечер»', 1),
    (N'Соевый воск премиум', N'Свеча «Зимние специи»', 0.21),
    (N'Масло ванили', N'Свеча «Зимние специи»', 0.008),
    (N'Краситель янтарный', N'Свеча «Зимние специи»', 0.003),
    (N'Фитиль хлопковый 12 см', N'Свеча «Зимние специи»', 1),
    (N'Стакан матовый 250 мл', N'Свеча «Зимние специи»', 1),
    (N'Наклейка предупреждающая', N'Свеча «Зимние специи»', 1),
    (N'Соевый воск премиум', N'Свеча «Хвойный лес»', 0.215),
    (N'Масло цитрусовое', N'Свеча «Хвойный лес»', 0.004),
    (N'Краситель янтарный', N'Свеча «Хвойный лес»', 0.002),
    (N'Фитиль хлопковый 12 см', N'Свеча «Хвойный лес»', 1),
    (N'Стакан матовый 250 мл', N'Свеча «Хвойный лес»', 1),
    (N'Наклейка предупреждающая', N'Свеча «Хвойный лес»', 1);

-- 7. Начальные роли и разрешения (проектное расширение, НЕ из Excel)
CREATE TABLE #SrcRoles (
    RoleCode VARCHAR(40) NOT NULL,
    RoleName NVARCHAR(100) NOT NULL
);
INSERT INTO #SrcRoles (RoleCode, RoleName)
VALUES
    ('ADMIN', N'Администратор'),
    ('MANAGER', N'Менеджер'),
    ('WAREHOUSE', N'Кладовщик'),
    ('VIEWER', N'Наблюдатель');

CREATE TABLE #SrcPermissions (
    PermissionCode VARCHAR(80) NOT NULL,
    Description NVARCHAR(250) NOT NULL
);
INSERT INTO #SrcPermissions (PermissionCode, Description)
VALUES
    ('materials.read', N'Просмотр материалов'),
    ('materials.write', N'Добавление и изменение материалов'),
    ('products.read', N'Просмотр продукции'),
    ('products.write', N'Изменение каталога продукции'),
    ('recipes.read', N'Просмотр рецептур'),
    ('recipes.write', N'Изменение рецептур'),
    ('stock.read', N'Просмотр складских операций'),
    ('stock.write', N'Учет движения материалов'),
    ('suppliers.read', N'Просмотр поставщиков'),
    ('suppliers.write', N'Управление поставщиками'),
    ('users.manage', N'Управление пользователями и ролями'),
    ('audit.read', N'Просмотр журнала действий');

CREATE TABLE #SrcRolePermissions (
    RoleCode VARCHAR(40) NOT NULL,
    PermissionCode VARCHAR(80) NOT NULL
);
INSERT INTO #SrcRolePermissions (RoleCode, PermissionCode)
VALUES
    ('ADMIN', 'materials.read'),
    ('ADMIN', 'materials.write'),
    ('ADMIN', 'products.read'),
    ('ADMIN', 'products.write'),
    ('ADMIN', 'recipes.read'),
    ('ADMIN', 'recipes.write'),
    ('ADMIN', 'stock.read'),
    ('ADMIN', 'stock.write'),
    ('ADMIN', 'suppliers.read'),
    ('ADMIN', 'suppliers.write'),
    ('ADMIN', 'users.manage'),
    ('ADMIN', 'audit.read'),
    ('MANAGER', 'materials.read'),
    ('MANAGER', 'materials.write'),
    ('MANAGER', 'products.read'),
    ('MANAGER', 'products.write'),
    ('MANAGER', 'recipes.read'),
    ('MANAGER', 'recipes.write'),
    ('MANAGER', 'stock.read'),
    ('MANAGER', 'suppliers.read'),
    ('MANAGER', 'suppliers.write'),
    ('WAREHOUSE', 'materials.read'),
    ('WAREHOUSE', 'products.read'),
    ('WAREHOUSE', 'recipes.read'),
    ('WAREHOUSE', 'stock.read'),
    ('WAREHOUSE', 'stock.write'),
    ('WAREHOUSE', 'suppliers.read'),
    ('VIEWER', 'materials.read'),
    ('VIEWER', 'products.read'),
    ('VIEWER', 'recipes.read'),
    ('VIEWER', 'stock.read'),
    ('VIEWER', 'suppliers.read');


    -- Контроль исходных справочников и связей до вставки.
    IF EXISTS (SELECT 1 FROM #SrcMaterials AS s
               LEFT JOIN #SrcMaterialTypes AS t ON s.MaterialTypeName=t.TypeName
               LEFT JOIN #SrcUnits AS u ON s.UnitSymbol=u.Symbol
               WHERE t.TypeName IS NULL OR u.Symbol IS NULL)
        THROW 51001, N'Источник содержит неизвестный тип материала или единицу измерения.', 1;

    IF EXISTS (SELECT 1 FROM #SrcProducts AS s
               LEFT JOIN #SrcProductTypes AS t ON s.ProductTypeName=t.TypeName
               WHERE t.TypeName IS NULL)
        THROW 51002, N'Источник содержит неизвестный тип продукции.', 1;

    IF EXISTS (SELECT 1 FROM #SrcRecipes AS s
               LEFT JOIN #SrcMaterials AS m ON s.MaterialName=m.MaterialName
               LEFT JOIN #SrcProducts AS p ON s.ProductName=p.ProductName
               WHERE m.MaterialName IS NULL OR p.ProductName IS NULL)
        THROW 51003, N'Рецептура содержит неизвестный материал или продукт.', 1;

    -- Сначала справочники: внешние ключи следующих таблиц на них ссылаются.
    INSERT INTO dbo.MaterialTypes (TypeName,LossPercent)
    SELECT s.TypeName,s.LossPercent FROM #SrcMaterialTypes s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.MaterialTypes t WHERE t.TypeName=s.TypeName);

    INSERT INTO dbo.ProductTypes (TypeName,TypeCoefficient)
    SELECT s.TypeName,s.TypeCoefficient FROM #SrcProductTypes s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.ProductTypes t WHERE t.TypeName=s.TypeName);

    INSERT INTO dbo.Units (Symbol,UnitName)
    SELECT s.Symbol,s.UnitName FROM #SrcUnits s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Units u WHERE u.Symbol=s.Symbol);

    -- JOIN заменяет текстовый тип и единицу на действительные ID справочников.
    INSERT INTO dbo.Materials
      (MaterialName,MaterialTypeId,UnitId,UnitPrice,StockQuantity,MinimumQuantity,PackageQuantity)
    SELECT s.MaterialName,mt.MaterialTypeId,u.UnitId,
           s.UnitPrice,s.StockQuantity,s.MinimumQuantity,s.PackageQuantity
    FROM #SrcMaterials s
    INNER JOIN dbo.MaterialTypes mt ON mt.TypeName=s.MaterialTypeName
    INNER JOIN dbo.Units u ON u.Symbol=s.UnitSymbol
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Materials m WHERE m.MaterialName=s.MaterialName);

    INSERT INTO dbo.Products (Article,ProductName,ProductTypeId,MinimumPartnerPrice)
    SELECT s.Article,s.ProductName,pt.ProductTypeId,s.MinimumPartnerPrice
    FROM #SrcProducts s
    INNER JOIN dbo.ProductTypes pt ON pt.TypeName=s.ProductTypeName
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Products p
                      WHERE p.Article=s.Article OR p.ProductName=s.ProductName);

    -- Составы изделий: внешний ключ по ID берём через названия.
    INSERT INTO dbo.ProductMaterials (ProductId,MaterialId,QuantityPerUnit)
    SELECT p.ProductId,m.MaterialId,s.QuantityPerUnit
    FROM #SrcRecipes s
    INNER JOIN dbo.Products p ON p.ProductName=s.ProductName
    INNER JOIN dbo.Materials m ON m.MaterialName=s.MaterialName
    WHERE NOT EXISTS
         (SELECT 1 FROM dbo.ProductMaterials pm
          WHERE pm.ProductId=p.ProductId AND pm.MaterialId=m.MaterialId);

    -- Справочники ролей/прав без создания пользователей.
    INSERT INTO dbo.Roles (RoleCode,RoleName)
    SELECT s.RoleCode,s.RoleName FROM #SrcRoles s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Roles r WHERE r.RoleCode=s.RoleCode);

    INSERT INTO dbo.Permissions (PermissionCode,Description)
    SELECT s.PermissionCode,s.Description FROM #SrcPermissions s
    WHERE NOT EXISTS (SELECT 1 FROM dbo.Permissions p WHERE p.PermissionCode=s.PermissionCode);

    INSERT INTO dbo.RolePermissions (RoleId,PermissionId)
    SELECT r.RoleId,p.PermissionId
    FROM #SrcRolePermissions s
    INNER JOIN dbo.Roles r ON r.RoleCode=s.RoleCode
    INNER JOIN dbo.Permissions p ON p.PermissionCode=s.PermissionCode
    WHERE NOT EXISTS
         (SELECT 1 FROM dbo.RolePermissions rp
          WHERE rp.RoleId=r.RoleId AND rp.PermissionId=p.PermissionId);

    -- Финальные проверки: даже при импортировании в ранее созданную схему
    -- все записи из Excel должны присутствовать в основном каталоге.
    IF EXISTS (SELECT 1 FROM #SrcMaterialTypes s LEFT JOIN dbo.MaterialTypes t
               ON t.TypeName=s.TypeName WHERE t.MaterialTypeId IS NULL)
       OR EXISTS (SELECT 1 FROM #SrcProductTypes s LEFT JOIN dbo.ProductTypes t
                  ON t.TypeName=s.TypeName WHERE t.ProductTypeId IS NULL)
       OR EXISTS (SELECT 1 FROM #SrcMaterials s LEFT JOIN dbo.Materials m
                  ON m.MaterialName=s.MaterialName WHERE m.MaterialId IS NULL)
       OR EXISTS (SELECT 1 FROM #SrcProducts s LEFT JOIN dbo.Products p
                  ON p.Article=s.Article WHERE p.ProductId IS NULL)
       OR EXISTS (SELECT 1 FROM #SrcRecipes s
                  LEFT JOIN dbo.Materials m ON m.MaterialName=s.MaterialName
                  LEFT JOIN dbo.Products p ON p.ProductName=s.ProductName
                  LEFT JOIN dbo.ProductMaterials pm ON pm.MaterialId=m.MaterialId
                                                AND pm.ProductId=p.ProductId
                  WHERE pm.MaterialId IS NULL)
       THROW 51004, N'Не удалось полностью загрузить набор данных из Excel.', 1;

    DROP TABLE IF EXISTS #SrcMaterialTypes;
    DROP TABLE IF EXISTS #SrcProductTypes;
    DROP TABLE IF EXISTS #SrcUnits;
    DROP TABLE IF EXISTS #SrcMaterials;
    DROP TABLE IF EXISTS #SrcProducts;
    DROP TABLE IF EXISTS #SrcRecipes;
    DROP TABLE IF EXISTS #SrcRoles;
    DROP TABLE IF EXISTS #SrcPermissions;
    DROP TABLE IF EXISTS #SrcRolePermissions;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;
GO

-- Представление для экрана списка материалов и суммарного расхода
-- по рецептурам (нормы на 1 изделие суммируются как в задании).
CREATE OR ALTER VIEW dbo.vMaterialCatalog
AS
SELECT
    m.MaterialId,
    mt.TypeName AS MaterialType,
    m.MaterialName,
    m.UnitPrice,
    u.Symbol AS UnitSymbol,
    m.StockQuantity,
    m.MinimumQuantity,
    m.PackageQuantity,
    COALESCE(SUM(pm.QuantityPerUnit),CONVERT(DECIMAL(18,5),0)) AS TotalRecipeQuantity
FROM dbo.Materials AS m
INNER JOIN dbo.MaterialTypes AS mt ON mt.MaterialTypeId=m.MaterialTypeId
INNER JOIN dbo.Units AS u ON u.UnitId=m.UnitId
LEFT JOIN dbo.ProductMaterials AS pm ON pm.MaterialId=m.MaterialId
GROUP BY m.MaterialId,mt.TypeName,m.MaterialName,m.UnitPrice,
         u.Symbol,m.StockQuantity,m.MinimumQuantity,m.PackageQuantity;
GO

-- Продукция по материалу (задание модуля № 4).
CREATE OR ALTER VIEW dbo.vMaterialProducts
AS
SELECT
    m.MaterialId, m.MaterialName,
    p.ProductId, p.Article, p.ProductName,
    pm.QuantityPerUnit,
    u.Symbol AS UnitSymbol
FROM dbo.ProductMaterials AS pm
INNER JOIN dbo.Materials AS m ON m.MaterialId=pm.MaterialId
INNER JOIN dbo.Products AS p ON p.ProductId=pm.ProductId
INNER JOIN dbo.Units AS u ON u.UnitId=m.UnitId;
GO

-- Проверка содержимого после развёртывания (на чистой базе ожидаются:
-- 10 типов материалов, 6 типов продукции, 4 единицы,
-- 18 материалов, 14 продуктов, 73 строки рецептур).
SELECT N'MaterialTypes' AS TableName,COUNT_BIG(*) AS [RowCount] FROM dbo.MaterialTypes
UNION ALL SELECT N'ProductTypes',COUNT_BIG(*) FROM dbo.ProductTypes
UNION ALL SELECT N'Units',COUNT_BIG(*) FROM dbo.Units
UNION ALL SELECT N'Materials',COUNT_BIG(*) FROM dbo.Materials
UNION ALL SELECT N'Products',COUNT_BIG(*) FROM dbo.Products
UNION ALL SELECT N'ProductMaterials',COUNT_BIG(*) FROM dbo.ProductMaterials
UNION ALL SELECT N'Roles',COUNT_BIG(*) FROM dbo.Roles
UNION ALL SELECT N'Permissions',COUNT_BIG(*) FROM dbo.Permissions
UNION ALL SELECT N'RolePermissions',COUNT_BIG(*) FROM dbo.RolePermissions;
GO