/*
  Cleanup all side-effects of a test FrontOffice user (register + package + club + wallet + invitations).

  Usage:
    EXEC dbo.usp_CleanupTestUser @Mobile = N'09037437157', @DryRun = 1;
    EXEC dbo.usp_CleanupTestUser @Mobile = N'09037437157';
    EXEC dbo.usp_CleanupTestUser @Mobile = N'09...', @ReparentChildren = 1;
    EXEC dbo.usp_CleanupTestUser @Mobile = N'09...', @ReversePool = 1, @AbortIfPoolCalculated = 1;

  Pool reverse: for each ClubMembershipCycle, subtract Packages.ActivationFee
  (fallback: ClubMembership.InitialContribution) from CMS.WeeklyCommissionPools
  for matching WeekDefinition × PackageId.

  Does NOT delete pool rows (shared). Does NOT claw back commission already paid to OTHER users.
  Does NOT touch: Packages master, Contracts master, WeekDefinitions, Roles, catalogs.
*/
CREATE OR ALTER PROCEDURE dbo.usp_CleanupTestUser
    @UserId                 BIGINT        = NULL,
    @Mobile                 NVARCHAR(20)  = NULL,
    @HardDeleteUser         BIT           = 1,
    @ReparentChildren       BIT           = 0,
    @ReversePool            BIT           = 1,
    @AbortIfPoolCalculated  BIT           = 0,
    @DryRun                 BIT           = 0
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;
    SET QUOTED_IDENTIFIER ON;
    SET ANSI_NULLS ON;

    IF @UserId IS NULL AND (@Mobile IS NULL OR LTRIM(RTRIM(@Mobile)) = N'')
    BEGIN
        RAISERROR(N'Provide @UserId and/or @Mobile.', 16, 1);
        RETURN;
    END;

    DECLARE @MobileNorm NVARCHAR(20) = NULL;
    IF @Mobile IS NOT NULL
    BEGIN
        SET @MobileNorm = LTRIM(RTRIM(@Mobile));
        IF LEFT(@MobileNorm, 3) = N'+98' SET @MobileNorm = N'0' + SUBSTRING(@MobileNorm, 4, 20);
        IF LEFT(@MobileNorm, 2) = N'98' AND LEN(@MobileNorm) = 12 SET @MobileNorm = N'0' + SUBSTRING(@MobileNorm, 3, 20);
        IF LEFT(@MobileNorm, 1) = N'9' AND LEN(@MobileNorm) = 10 SET @MobileNorm = N'0' + @MobileNorm;
    END;

    IF @UserId IS NULL
    BEGIN
        SELECT TOP (1) @UserId = Id
        FROM CMS.Users
        WHERE Mobile = @MobileNorm
           OR Mobile = @Mobile
           OR REPLACE(Mobile, N'+98', N'0') = @MobileNorm
        ORDER BY IsDeleted ASC, Id DESC;
    END;

    IF @UserId IS NULL
    BEGIN
        RAISERROR(N'User not found for given @UserId/@Mobile.', 16, 1);
        RETURN;
    END;

    DECLARE @UserMobile NVARCHAR(20);
    SELECT @UserMobile = Mobile FROM CMS.Users WHERE Id = @UserId;
    IF @UserMobile IS NULL
    BEGIN
        RAISERROR(N'UserId %I64d not found.', 16, 1, @UserId);
        RETURN;
    END;

    DECLARE @ChildCount INT =
        (SELECT COUNT(*) FROM CMS.Users WHERE NetworkParentId = @UserId AND IsDeleted = 0);

    IF @ChildCount > 0 AND @ReparentChildren = 0 AND @HardDeleteUser = 1
    BEGIN
        RAISERROR(N'User has %d active network children. Pass @ReparentChildren=1 or soft-delete only.', 16, 1, @ChildCount);
        RETURN;
    END;

    PRINT N'=== usp_CleanupTestUser ===';
    PRINT N'UserId=' + CAST(@UserId AS NVARCHAR(20)) + N' Mobile=' + ISNULL(@UserMobile, N'?')
        + N' DryRun=' + CAST(@DryRun AS NVARCHAR(1))
        + N' ReversePool=' + CAST(@ReversePool AS NVARCHAR(1));

    DECLARE @Wallets TABLE (WalletId BIGINT PRIMARY KEY);
    INSERT INTO @Wallets (WalletId)
    SELECT Id FROM CMS.UserWallets WHERE UserId = @UserId;

    DECLARE @Tx TABLE (TransactionId BIGINT PRIMARY KEY);
    INSERT INTO @Tx (TransactionId)
    SELECT DISTINCT x.TransactionId
    FROM (
        SELECT TransactionId FROM CMS.UserPackagePurchases WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT TransactionId FROM CMS.PaymentTransactions WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT TransactionId FROM CMS.UserOrders WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT TransactionId FROM CMS.ManualPayments WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT TransactionId FROM CMS.DayaLoanContracts WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT TransactionId FROM CMS.DiscountOrders WHERE UserId = @UserId AND TransactionId IS NOT NULL
        UNION
        SELECT h.RefrenceId
        FROM CMS.UserWalletHistories h
        INNER JOIN @Wallets w ON w.WalletId = h.WalletId
        WHERE h.RefrenceId IS NOT NULL
    ) x
    WHERE x.TransactionId IS NOT NULL;

    DECLARE @Orders TABLE (OrderId BIGINT PRIMARY KEY);
    INSERT INTO @Orders (OrderId)
    SELECT Id FROM CMS.UserOrders WHERE UserId = @UserId;

    DECLARE @DiscountOrders TABLE (DiscountOrderId BIGINT PRIMARY KEY);
    INSERT INTO @DiscountOrders (DiscountOrderId)
    SELECT Id FROM CMS.DiscountOrders WHERE UserId = @UserId;

    DECLARE @ClubMemberships TABLE (ClubMembershipId BIGINT PRIMARY KEY);
    INSERT INTO @ClubMemberships (ClubMembershipId)
    SELECT Id FROM CMS.ClubMemberships WHERE UserId = @UserId;

    DECLARE @Cycles TABLE (CycleId BIGINT PRIMARY KEY);
    INSERT INTO @Cycles (CycleId)
    SELECT Id FROM CMS.ClubMembershipCycles WHERE UserId = @UserId
    UNION
    SELECT Id FROM CMS.ClubMembershipCycles WHERE ClubMembershipId IN (SELECT ClubMembershipId FROM @ClubMemberships);

    DECLARE @Payouts TABLE (PayoutId BIGINT PRIMARY KEY);
    INSERT INTO @Payouts (PayoutId)
    SELECT Id FROM CMS.UserCommissionPayouts WHERE UserId = @UserId;

    DECLARE @PoolReversals TABLE (
        CycleId BIGINT,
        PackageId BIGINT,
        WeekDefinitionId BIGINT NULL,
        PoolId BIGINT NULL,
        Fee BIGINT,
        PoolAmountBefore BIGINT NULL,
        IsCalculated BIT NULL,
        Note NVARCHAR(200)
    );

    INSERT INTO @PoolReversals (CycleId, PackageId, WeekDefinitionId, PoolId, Fee, PoolAmountBefore, IsCalculated, Note)
    SELECT
        c.Id,
        c.PackageId,
        wd.Id,
        wp.Id,
        ISNULL(NULLIF(cm.InitialContribution, 0), ISNULL(p.ActivationFee, 0)),
        wp.TotalPoolAmount,
        wp.IsCalculated,
        CASE
            WHEN wd.Id IS NULL THEN N'NO_WEEK_MATCH'
            WHEN wp.Id IS NULL THEN N'NO_POOL_ROW'
            WHEN wp.IsCalculated = 1 THEN N'POOL_ALREADY_CALCULATED'
            ELSE N'OK'
        END
    FROM CMS.ClubMembershipCycles c
    LEFT JOIN CMS.ClubMemberships cm ON cm.Id = c.ClubMembershipId
    LEFT JOIN CMS.Packages p ON p.Id = c.PackageId
    LEFT JOIN CMS.WeekDefinitions wd
        ON CAST(c.PackagePurchasedAt AS DATE) >= CAST(wd.StartDate AS DATE)
       AND CAST(c.PackagePurchasedAt AS DATE) <= CAST(wd.EndDate AS DATE)
       AND wd.IsDeleted = 0
    LEFT JOIN CMS.WeeklyCommissionPools wp
        ON wp.WeekDefinitionId = wd.Id
       AND wp.PackageId = c.PackageId
       AND wp.IsDeleted = 0
    WHERE c.Id IN (SELECT CycleId FROM @Cycles);

    SELECT N'Users' AS Entity, COUNT(*) AS Cnt FROM CMS.Users WHERE Id = @UserId
    UNION ALL SELECT N'OtpTokens', COUNT(*) FROM CMS.OtpTokens WHERE Mobile IN (@UserMobile, @MobileNorm, @Mobile)
    UNION ALL SELECT N'UserInvitations', COUNT(*) FROM CMS.UserInvitations WHERE InviterUserId = @UserId OR InviteeUserId = @UserId
    UNION ALL SELECT N'ClubMemberships', COUNT(*) FROM @ClubMemberships
    UNION ALL SELECT N'ClubMembershipCycles', COUNT(*) FROM @Cycles
    UNION ALL SELECT N'UserPackagePurchases', COUNT(*) FROM CMS.UserPackagePurchases WHERE UserId = @UserId
    UNION ALL SELECT N'PaymentTransactions', COUNT(*) FROM CMS.PaymentTransactions WHERE UserId = @UserId OR Mobile = @UserMobile
    UNION ALL SELECT N'UserWallets', COUNT(*) FROM @Wallets
    UNION ALL SELECT N'UserWalletHistories', COUNT(*) FROM CMS.UserWalletHistories WHERE WalletId IN (SELECT WalletId FROM @Wallets)
    UNION ALL SELECT N'Transactions(collected)', COUNT(*) FROM @Tx
    UNION ALL SELECT N'NetworkChildren', @ChildCount
    UNION ALL SELECT N'UserOrders', COUNT(*) FROM @Orders
    UNION ALL SELECT N'DiscountOrders', COUNT(*) FROM @DiscountOrders
    UNION ALL SELECT N'UserCommissionPayouts', COUNT(*) FROM @Payouts
    UNION ALL SELECT N'PoolReversals(planned)', COUNT(*) FROM @PoolReversals WHERE Fee > 0;

    SELECT N'--- Pool reverse plan ---' AS Info;
    SELECT * FROM @PoolReversals;

    IF @ReversePool = 1 AND @AbortIfPoolCalculated = 1
       AND EXISTS (SELECT 1 FROM @PoolReversals WHERE IsCalculated = 1 AND Fee > 0)
    BEGIN
        RAISERROR(N'One or more target weekly pools are already calculated. Aborting (@AbortIfPoolCalculated=1).', 16, 1);
        RETURN;
    END;

    IF @DryRun = 1
    BEGIN
        PRINT N'DryRun=1 — no changes committed.';
        RETURN;
    END;

    BEGIN TRANSACTION;

    BEGIN TRY
        /* ---- Reverse weekly pool charges FIRST (needs cycle + package data) ---- */
        IF @ReversePool = 1
        BEGIN
            UPDATE wp
            SET wp.TotalPoolAmount = CASE
                    WHEN wp.TotalPoolAmount >= r.Fee THEN wp.TotalPoolAmount - r.Fee
                    ELSE 0
                END,
                wp.LastModified = SYSUTCDATETIME()
            FROM CMS.WeeklyCommissionPools wp
            INNER JOIN (
                SELECT PoolId, SUM(Fee) AS Fee
                FROM @PoolReversals
                WHERE PoolId IS NOT NULL AND Fee > 0
                GROUP BY PoolId
            ) r ON r.PoolId = wp.Id;

            PRINT N'Pool reverse applied for ' + CAST(@@ROWCOUNT AS NVARCHAR(20)) + N' pool row(s).';
        END;

        /* ---- Club ---- */
        DELETE h FROM CMS.ClubMembershipCycleHistories h
        WHERE h.UserId = @UserId OR h.ClubMembershipCycleId IN (SELECT CycleId FROM @Cycles);

        DELETE FROM CMS.ClubMembershipCycles
        WHERE Id IN (SELECT CycleId FROM @Cycles) OR UserId = @UserId;

        DELETE FROM CMS.ClubMembershipHistories WHERE UserId = @UserId
           OR ClubMembershipId IN (SELECT ClubMembershipId FROM @ClubMemberships);

        DELETE FROM CMS.UserClubFeatures WHERE UserId = @UserId
           OR ClubMembershipId IN (SELECT ClubMembershipId FROM @ClubMemberships);

        DELETE FROM CMS.ClubMemberships WHERE UserId = @UserId;

        /* ---- Commission payouts of THIS user only ---- */
        DELETE h FROM CMS.CommissionPayoutHistories h
        WHERE h.UserId = @UserId OR h.UserCommissionPayoutId IN (SELECT PayoutId FROM @Payouts);

        DELETE FROM CMS.UserCommissionPayouts WHERE UserId = @UserId;

        /* ---- Network ---- */
        DELETE FROM CMS.NetworkWeeklyBalances WHERE UserId = @UserId;
        DELETE FROM CMS.NetworkMembershipHistories
        WHERE UserId = @UserId OR OldParentId = @UserId OR NewParentId = @UserId;

        IF @ChildCount > 0 AND @ReparentChildren = 1
            UPDATE CMS.Users SET NetworkParentId = NULL, LastModified = SYSUTCDATETIME()
            WHERE NetworkParentId = @UserId;

        /* ---- Invitations ---- */
        DELETE FROM CMS.UserInvitations
        WHERE InviterUserId = @UserId OR InviteeUserId = @UserId;

        /* ---- Package / payment ---- */
        DELETE FROM CMS.UserPackagePurchases WHERE UserId = @UserId;
        DELETE FROM CMS.PaymentTransactions
        WHERE UserId = @UserId OR Mobile = @UserMobile OR Mobile = @MobileNorm;

        /* ---- Manual / Daya ---- */
        DELETE FROM CMS.ManualPayments WHERE UserId = @UserId;
        DELETE FROM CMS.DayaLoanContracts WHERE UserId = @UserId;

        /* ---- Discount shop ---- */
        DELETE d FROM CMS.DiscountOrderDetails d
        WHERE d.DiscountOrderId IN (SELECT DiscountOrderId FROM @DiscountOrders);
        DELETE FROM CMS.DiscountOrders WHERE UserId = @UserId;
        DELETE FROM CMS.DiscountShoppingCarts WHERE UserId = @UserId;

        /* ---- Stock ---- */
        DELETE FROM CMS.StockMovements
        WHERE PerformedByUserId = @UserId
           OR OrderId IN (SELECT OrderId FROM @Orders)
           OR DiscountOrderId IN (SELECT DiscountOrderId FROM @DiscountOrders);

        /* ---- Orders ---- */
        DELETE FROM CMS.FactorDetails WHERE OrderId IN (SELECT OrderId FROM @Orders);
        DELETE FROM CMS.OrderVATs WHERE OrderId IN (SELECT OrderId FROM @Orders);
        DELETE FROM CMS.UserOrders WHERE UserId = @UserId;

        /* ---- Wallet ---- */
        DELETE FROM CMS.UserWalletHistories WHERE WalletId IN (SELECT WalletId FROM @Wallets);
        DELETE FROM CMS.UserWallets WHERE UserId = @UserId;

        /* ---- Cart / address / roles / contracts ---- */
        DELETE FROM CMS.UserCarts WHERE UserId = @UserId;
        DELETE FROM CMS.UserAddresses WHERE UserId = @UserId;
        DELETE FROM CMS.UserRoles WHERE UserId = @UserId;
        DELETE FROM CMS.UserContracts WHERE UserId = @UserId;

        /* ---- Content authored by test user ---- */
        UPDATE CMS.BlogPosts SET IsDeleted = 1, LastModified = SYSUTCDATETIME()
        WHERE AuthorUserId = @UserId AND IsDeleted = 0;

        UPDATE CMS.PublicMessages SET IsDeleted = 1, LastModified = SYSUTCDATETIME()
        WHERE CreatedByUserId = @UserId AND IsDeleted = 0;

        /* ---- Transactions ---- */
        DELETE t
        FROM CMS.Transactions t
        WHERE t.Id IN (SELECT TransactionId FROM @Tx)
          AND NOT EXISTS (SELECT 1 FROM CMS.UserPackagePurchases p WHERE p.TransactionId = t.Id)
          AND NOT EXISTS (SELECT 1 FROM CMS.PaymentTransactions p WHERE p.TransactionId = t.Id)
          AND NOT EXISTS (SELECT 1 FROM CMS.UserOrders o WHERE o.TransactionId = t.Id)
          AND NOT EXISTS (SELECT 1 FROM CMS.ManualPayments m WHERE m.TransactionId = t.Id)
          AND NOT EXISTS (SELECT 1 FROM CMS.DayaLoanContracts d WHERE d.TransactionId = t.Id)
          AND NOT EXISTS (SELECT 1 FROM CMS.DiscountOrders d WHERE d.TransactionId = t.Id);

        /* ---- OTP ---- */
        DELETE FROM CMS.OtpTokens
        WHERE Mobile IN (@UserMobile, @MobileNorm, ISNULL(@Mobile, N''));

        /* ---- User ---- */
        IF @HardDeleteUser = 1
            DELETE FROM CMS.Users WHERE Id = @UserId;
        ELSE
            UPDATE CMS.Users
            SET IsDeleted = 1, LastModified = SYSUTCDATETIME()
            WHERE Id = @UserId;

        COMMIT TRANSACTION;

        PRINT N'Cleanup completed successfully.';
        SELECT N'DONE' AS Status, @UserId AS UserId, @UserMobile AS Mobile,
               @HardDeleteUser AS HardDeleted, @ReversePool AS PoolReversed;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        DECLARE @Err NVARCHAR(4000) = ERROR_MESSAGE();
        RAISERROR(N'Cleanup failed: %s', 16, 1, @Err);
    END CATCH
END;
GO