/*
  DIAGNOSTIC ONLY — maps RefId LIKE 'manualy%' transactions to Users / WalletHistory.

  Background (ChargeUserWallets.sql):
    RefId = 'manualy' + ChangeLogId at charge time (original UserWalletChangeLogs id).
    After migration/rebuild, WH ids shifted; RefrenceId on some rows stayed 0.
    When RefrenceId=0, CreatedBy may store the Transaction Id as a fallback hint —
    but Tx may never have been created; do NOT infer Tx from mobile or manualy suffix alone.

  Rules used here:
    - WH.RefrenceId > 0  → trusted link to Tx
    - WH.RefrenceId = 0  → Tx ONLY if CreatedBy is numeric AND that Tx exists with RefId manualy%
    - Tx → User via mobile (CreatedBy/LastModifiedBy) is informational, not used alone for Ref=0 WH
    - manualy{N} suffix N = original ChangeLog id (may differ from current WH.Id)

  Run on staging before script 6.
*/

SET NOCOUNT ON;

DECLARE @ManualyTx TABLE (
    TransactionId BIGINT PRIMARY KEY,
    RefId NVARCHAR(100) NOT NULL,
    OriginalChangeLogId BIGINT NULL,
    Amount BIGINT,
    TxCreatedBy NVARCHAR(200),
    TxLastModifiedBy NVARCHAR(200),
    PaymentDate DATETIME2
);

INSERT INTO @ManualyTx (TransactionId, RefId, OriginalChangeLogId, Amount, TxCreatedBy, TxLastModifiedBy, PaymentDate)
SELECT
    t.Id,
    t.RefId,
    TRY_CAST(
        SUBSTRING(t.RefId, LEN(N'manualy') + 1, 20)
        AS BIGINT
    ),
    t.Amount,
    t.CreatedBy,
    t.LastModifiedBy,
    t.PaymentDate
FROM CMS.Transactions t
WHERE t.IsDeleted = 0
  AND t.RefId LIKE N'manualy%';

DECLARE @ManualyTxCount INT = (SELECT COUNT(*) FROM @ManualyTx);
PRINT CONCAT('manualy transactions in DB: ', @ManualyTxCount);

------------------------------------------------------------
-- 1) Each manualy Tx → User(s) via WH.RefrenceId / CreatedBy hint / mobile
------------------------------------------------------------
PRINT '=== 1) manualy Tx → User resolution ===';

SELECT
    mt.TransactionId,
    mt.RefId,
    mt.OriginalChangeLogId,
    mt.Amount,
    mt.PaymentDate,
    mt.TxCreatedBy,
    mt.TxLastModifiedBy,
    u_mobile.Id AS UserByMobile,
    u_mobile.Mobile AS UserByMobilePhone,
    u_mobile.FirstName AS UserByMobileFirst,
    u_mobile.LastName AS UserByMobileLast,
    h_linked.Id AS WhIdViaRef,
    h_linked.WalletId AS WhWalletViaRef,
    u_wh.Id AS UserViaWhRef,
    h_hint.Id AS WhIdViaCreatedByHint,
    h_hint.WalletId AS WhWalletViaHint,
    u_hint.Id AS UserViaWhHint,
    CASE
        WHEN u_wh.Id IS NOT NULL THEN N'WH_REFRENCE_ID'
        WHEN u_hint.Id IS NOT NULL THEN N'WH_CREATEDBY_HINT'
        WHEN u_mobile.Id IS NOT NULL THEN N'TX_MOBILE_ONLY'
        ELSE N'UNRESOLVED'
    END AS PrimaryLink,
    CASE
        WHEN u_wh.Id IS NOT NULL AND u_hint.Id IS NOT NULL AND u_wh.Id <> u_hint.Id
            THEN N'CONFLICT_WH_REF_VS_HINT'
        WHEN u_wh.Id IS NOT NULL AND u_mobile.Id IS NOT NULL AND u_wh.Id <> u_mobile.Id
            THEN N'CONFLICT_WH_REF_VS_MOBILE'
        WHEN u_wh.Id IS NULL AND u_hint.Id IS NULL AND u_mobile.Id IS NULL
            THEN N'NO_USER_FOUND'
        ELSE N'OK'
    END AS LinkQuality
FROM @ManualyTx mt
LEFT JOIN CMS.Users u_mobile ON u_mobile.IsDeleted = 0
    AND u_mobile.Mobile IN (
        NULLIF(LTRIM(RTRIM(mt.TxCreatedBy)), N''),
        NULLIF(LTRIM(RTRIM(mt.TxLastModifiedBy)), N'')
    )
LEFT JOIN CMS.UserWalletHistories h_linked ON h_linked.IsDeleted = 0
    AND h_linked.RefrenceId = mt.TransactionId
LEFT JOIN CMS.UserWallets w_linked ON w_linked.Id = h_linked.WalletId AND w_linked.IsDeleted = 0
LEFT JOIN CMS.Users u_wh ON u_wh.Id = w_linked.UserId AND u_wh.IsDeleted = 0
LEFT JOIN CMS.UserWalletHistories h_hint ON h_hint.IsDeleted = 0
    AND (h_hint.RefrenceId IS NULL OR h_hint.RefrenceId = 0)
    AND TRY_CAST(h_hint.CreatedBy AS BIGINT) = mt.TransactionId
LEFT JOIN CMS.UserWallets w_hint ON w_hint.Id = h_hint.WalletId AND w_hint.IsDeleted = 0
LEFT JOIN CMS.Users u_hint ON u_hint.Id = w_hint.UserId AND u_hint.IsDeleted = 0
ORDER BY mt.TransactionId;

------------------------------------------------------------
-- 2) WH rows with RefrenceId=0 — CreatedBy hint analysis
------------------------------------------------------------
PRINT '=== 2) WalletHistory RefrenceId=0 (manualy charge candidates) ===';

SELECT
    h.Id AS WalletHistoryId,
    h.WalletId,
    w.UserId AS WalletUserId,
    u.Mobile AS WalletUserMobile,
    h.RefrenceId AS WhRefrenceId,
    h.ChangeValue,
    h.Created AS WhCreated,
    h.CreatedBy AS WhCreatedBy,
    h.LastModifiedBy AS WhLastModifiedBy,
    t_hint.Id AS TxByCreatedByHint,
    t_hint.RefId AS TxHintRefId,
    t_hint.Amount AS TxHintAmount,
    u_hint_mobile.Id AS TxHintUserByMobile,
    CASE
        WHEN TRY_CAST(h.CreatedBy AS BIGINT) IS NULL OR TRY_CAST(h.CreatedBy AS BIGINT) = 0
            THEN N'NO_NUMERIC_HINT'
        WHEN t_hint.Id IS NULL
            THEN N'HINT_POINTS_TO_MISSING_TX'
        WHEN t_hint.RefId NOT LIKE N'manualy%'
            THEN N'HINT_TX_NOT_MANUALY'
        WHEN w.UserId = u_hint_mobile.Id
            THEN N'FIX_LINK_WH_TO_HINT_TX'
        WHEN u_hint_mobile.Id IS NOT NULL AND w.UserId <> u_hint_mobile.Id
            THEN N'HINT_TX_USER_MISMATCH_WALLET'
        ELSE N'FIX_LINK_REVIEW_MOBILE'
    END AS WhZeroRefVerdict,
    CASE
        WHEN t_hint.Id IS NOT NULL AND t_hint.RefId LIKE N'manualy%'
            THEN CONCAT(N'UPDATE WH ', h.Id, N' SET RefrenceId=', t_hint.Id)
        WHEN TRY_CAST(h.CreatedBy AS BIGINT) IS NULL OR TRY_CAST(h.CreatedBy AS BIGINT) = 0
            THEN N'Cannot auto-link — no CreatedBy Tx hint'
        ELSE N'Tx from hint missing — may need manual charge review'
    END AS SuggestedAction
FROM CMS.UserWalletHistories h
INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
LEFT JOIN CMS.Users u ON u.Id = w.UserId AND u.IsDeleted = 0
LEFT JOIN CMS.Transactions t_hint ON t_hint.Id = TRY_CAST(h.CreatedBy AS BIGINT)
    AND t_hint.IsDeleted = 0
LEFT JOIN CMS.Users u_hint_mobile ON u_hint_mobile.IsDeleted = 0
    AND u_hint_mobile.Mobile IN (
        NULLIF(LTRIM(RTRIM(t_hint.CreatedBy)), N''),
        NULLIF(LTRIM(RTRIM(t_hint.LastModifiedBy)), N'')
    )
WHERE h.IsDeleted = 0
  AND (h.RefrenceId IS NULL OR h.RefrenceId = 0)
  AND (
        TRY_CAST(h.CreatedBy AS BIGINT) IS NOT NULL
     OR h.LastModifiedBy LIKE N'09%'
     OR EXISTS (
            SELECT 1 FROM @ManualyTx mt
            WHERE mt.OriginalChangeLogId = h.Id
        )
  )
ORDER BY h.Id;

------------------------------------------------------------
-- 3) manualy suffix vs current WH.Id (id-shift detector)
------------------------------------------------------------
PRINT '=== 3) manualy suffix N vs current WH.Id (id shift) ===';

SELECT
    mt.TransactionId,
    mt.RefId,
    mt.OriginalChangeLogId AS ManualySuffixN,
    h_by_suffix.Id AS CurrentWhIdWithSameIdAsSuffix,
    h_by_suffix.RefrenceId AS ThatWhRefrenceId,
    h_by_suffix.WalletId,
    CASE
        WHEN h_by_suffix.Id IS NULL THEN N'SUFFIX_WH_ID_GONE'
        WHEN h_by_suffix.RefrenceId = mt.TransactionId THEN N'SUFFIX_WH_LINKED_OK'
        WHEN h_by_suffix.RefrenceId IS NULL OR h_by_suffix.RefrenceId = 0
            THEN N'SUFFIX_WH_EXISTS_BROKEN_REF'
        ELSE N'SUFFIX_WH_POINTS_OTHER_TX'
    END AS SuffixMatchStatus
FROM @ManualyTx mt
LEFT JOIN CMS.UserWalletHistories h_by_suffix ON h_by_suffix.Id = mt.OriginalChangeLogId
    AND h_by_suffix.IsDeleted = 0
ORDER BY mt.TransactionId;

------------------------------------------------------------
-- 4) Distinct users in scope for script 6 (manualy-linked only)
------------------------------------------------------------
PRINT '=== 4) Users in manualy fix scope ===';

;WITH ScopeUsers AS (
    SELECT DISTINCT w.UserId, mt.TransactionId, N'WH_REF' AS Source
    FROM @ManualyTx mt
    INNER JOIN CMS.UserWalletHistories h ON h.RefrenceId = mt.TransactionId AND h.IsDeleted = 0
    INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0

    UNION

    SELECT DISTINCT w.UserId, t.Id, N'WH_CREATEDBY_HINT'
    FROM CMS.UserWalletHistories h
    INNER JOIN CMS.UserWallets w ON w.Id = h.WalletId AND w.IsDeleted = 0
    INNER JOIN CMS.Transactions t ON t.Id = TRY_CAST(h.CreatedBy AS BIGINT)
        AND t.IsDeleted = 0 AND t.RefId LIKE N'manualy%'
    WHERE h.IsDeleted = 0
      AND (h.RefrenceId IS NULL OR h.RefrenceId = 0)
      AND TRY_CAST(h.CreatedBy AS BIGINT) > 0

    UNION

    SELECT DISTINCT u.Id, mt.TransactionId, N'TX_MOBILE'
    FROM @ManualyTx mt
    INNER JOIN CMS.Users u ON u.IsDeleted = 0
        AND u.Mobile IN (
            NULLIF(LTRIM(RTRIM(mt.TxCreatedBy)), N''),
            NULLIF(LTRIM(RTRIM(mt.TxLastModifiedBy)), N'')
        )
)
SELECT
    su.UserId,
    u.Mobile,
    u.FirstName,
    u.LastName,
    u.PackagePurchaseMethod,
    MIN(su.TransactionId) AS SampleManualyTxId,
    STRING_AGG(su.Source, N', ') WITHIN GROUP (ORDER BY su.Source) AS LinkSources,
    CASE WHEN EXISTS (
        SELECT 1 FROM CMS.Transactions t2
        WHERE t2.IsDeleted = 0 AND t2.RefId LIKE N'C4%'
          AND EXISTS (
              SELECT 1 FROM CMS.Users ux
              WHERE ux.Id = su.UserId AND ux.IsDeleted = 0
                AND ux.Mobile IN (NULLIF(t2.CreatedBy,N''), NULLIF(t2.LastModifiedBy,N''))
          )
    ) THEN 1 ELSE 0 END AS AlsoHasC4Tx,
    (SELECT COUNT(*) FROM CMS.UserPackagePurchases upp
     WHERE upp.UserId = su.UserId AND upp.IsDeleted = 0) AS UppCount
FROM ScopeUsers su
INNER JOIN CMS.Users u ON u.Id = su.UserId AND u.IsDeleted = 0
GROUP BY su.UserId, u.Mobile, u.FirstName, u.LastName, u.PackagePurchaseMethod
ORDER BY su.UserId;

PRINT 'Done (diagnostic only — no changes).';
