---

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    /* 1. Категории верхнего уровня */
    INSERT INTO dbo.Categories (CategoryName, ParentCategoryID)
    SELECT DISTINCT p.Category, NULL
    FROM dbo.Products AS p
    WHERE p.Category IS NOT NULL
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.Categories AS c
          WHERE c.CategoryName = p.Category
            AND c.ParentCategoryID IS NULL
      );

    /* 2. Подкатегории как дочерние категории */
    INSERT INTO dbo.Categories (CategoryName, ParentCategoryID)
    SELECT DISTINCT p.Subcategory, parent.CategoryID
    FROM dbo.Products AS p
    JOIN dbo.Categories AS parent
      ON parent.CategoryName = p.Category
     AND parent.ParentCategoryID IS NULL
    WHERE p.Subcategory IS NOT NULL
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.Categories AS child
          WHERE child.CategoryName = p.Subcategory
            AND child.ParentCategoryID = parent.CategoryID
      );

    /* 3. Производители */
    INSERT INTO dbo.Manufacturers (ManufacturerName)
    SELECT DISTINCT p.Manufacturer
    FROM dbo.Products AS p
    WHERE p.Manufacturer IS NOT NULL
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.Manufacturers AS m
          WHERE m.ManufacturerName = p.Manufacturer
      );

    /* 4. Цвета из справочника и остатков */
    INSERT INTO dbo.Color (ColorName)
    SELECT DISTINCT src.ColorName
    FROM (
        SELECT Color AS ColorName FROM dbo.Colors
        UNION
        SELECT Color FROM dbo.ProductStock
        UNION
        SELECT Color FROM dbo.Orders
    ) AS src
    WHERE src.ColorName IS NOT NULL
      AND NOT EXISTS (
          SELECT 1
          FROM dbo.Color AS c
          WHERE c.ColorName = src.ColorName
      );

    /* 5. Пользователи */
    INSERT INTO dbo.Users
        (Family, Name, Papa, Login, Role)
    SELECT DISTINCT
        u.Family, u.Name, u.Papa, u.Login, u.Role
    FROM dbo.Users AS u
    WHERE 1 = 0;