-- =====================================================================
-- Migration Script: ParentId → NetworkParentId & LegPosition Assignment
-- Date: 2025-06-01
-- Purpose: Migrate existing User.ParentId data to new NetworkParentId + LegPosition binary tree structure
-- =====================================================================

BEGIN TRANSACTION;

-- Step 1: Validation - Find CMS.Users with more than 2 children (INVALID for binary tree)
-- این کاربران باید قبل از Migration بررسی شوند
SELECT 
    ParentId, 
    COUNT(*) as ChildCount,
    STRING_AGG(CAST(Id AS VARCHAR), ', ') as ChildIds
FROM CMS.Users
WHERE ParentId IS NOT NULL
GROUP BY ParentId
HAVING COUNT(*) > 2;

-- اگر نتیجه‌ای بود، باید دستی تصمیم بگیرید کدام 2 فرزند باقی بمانند!
-- اگر نتیجه‌ای نبود، ادامه دهید:

-- Step 2: Copy ParentId → NetworkParentId for all CMS.Users
UPDATE CMS.Users
SET NetworkParentId = ParentId
WHERE ParentId IS NOT NULL
  AND NetworkParentId IS NULL;

-- Step 3: Assign LegPosition (Left/Right) based on order
-- برای هر Parent، اولین فرزند = Left، دومین فرزند = Right
WITH RankedChildren AS (
    SELECT 
        Id,
        ParentId,
        ROW_NUMBER() OVER (PARTITION BY ParentId ORDER BY Id ASC) as ChildRank
    FROM CMS.Users
    WHERE ParentId IS NOT NULL
)
UPDATE CMS.Users
SET LegPosition = CASE 
    WHEN rc.ChildRank = 1 THEN 0  -- Left = 0 (enum value)
    WHEN rc.ChildRank = 2 THEN 1  -- Right = 1 (enum value)
    ELSE NULL  -- اگر بیشتر از 2 فرزند بود (نباید اتفاق بیفته)
END
FROM CMS.Users u
INNER JOIN RankedChildren rc ON u.Id = rc.Id;

-- Step 4: Validation - Check for orphaned nodes (Parent doesn't exist)
SELECT 
    Id,
    NetworkParentId,
    'Orphaned: Parent does not exist' as Issue
FROM CMS.Users
WHERE NetworkParentId IS NOT NULL
  AND NetworkParentId NOT IN (SELECT Id FROM CMS.Users);

-- اگر Orphan یافت شد، باید آنها را NULL کنید یا Parent صحیح تخصیص دهید

-- Step 5: Validation - Verify binary tree integrity
-- هر Parent باید حداکثر 2 فرزند داشته باشد
SELECT 
    NetworkParentId, 
    COUNT(*) as ChildCount,
    STRING_AGG(CAST(Id AS VARCHAR), ', ') as ChildIds
FROM CMS.Users
WHERE NetworkParentId IS NOT NULL
GROUP BY NetworkParentId
HAVING COUNT(*) > 2;

-- اگر نتیجه خالی بود، Migration موفق است!

-- Step 6: Statistics
SELECT 
    'Total CMS.Users' as Metric, 
    COUNT(*) as Count 
FROM CMS.Users
UNION ALL
SELECT 
    'CMS.Users with NetworkParentId', 
    COUNT(*) 
FROM CMS.Users 
WHERE NetworkParentId IS NOT NULL
UNION ALL
SELECT 
    'CMS.Users with LegPosition Left', 
    COUNT(*) 
FROM CMS.Users 
WHERE LegPosition = 0
UNION ALL
SELECT 
    'CMS.Users with LegPosition Right', 
    COUNT(*) 
FROM CMS.Users 
WHERE LegPosition = 1;

-- Commit if validation passes
COMMIT;

-- ROLLBACK; -- اگر مشکل پیش آمد، uncomment کنید
