/*
  Rollback for Backfill_UserPackagePurchases_FromClubMemberships.sql

  What it undoes:
    A) 77 synthetic rows: Tx with RefId LIKE 'UPP-BACKFILL-%' + their UPP
    B) 24 ManualPayment-linked UPP rows inserted by backfill
       (same TransactionId as Approved ManualPayments — NOT deleting those Transactions)

  BEFORE RUN:
    Set @BackfillStartedAt to a timestamp slightly BEFORE you ran the backfill
    (e.g. if you ran at 06:40, use '2026-08-14 06:00:00').
    Part B only deletes UPP with Created >= that time so later real CreateManualPayment rows stay.

  Safe to preview: leave @Commit = 0 (default) to only PRINT counts inside a rolled-back tran.
  Set @Commit = 1 to actually apply.
*/

SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @BackfillStartedAt DATETIME2 = '2026-08-14 00:00:00'; -- << adjust
DECLARE @Commit BIT = 0; -- 0 = dry-run (rollback), 1 = commit deletes

BEGIN TRAN;

------------------------------------------------------------
-- A) Synthetic Tx + UPP (RefId marker)
------------------------------------------------------------
DECLARE @SyntheticUpp TABLE (Id BIGINT PRIMARY KEY, TransactionId BIGINT);

INSERT INTO @SyntheticUpp (Id, TransactionId)
SELECT upp.Id, upp.TransactionId
FROM CMS.UserPackagePurchases upp
INNER JOIN CMS.Transactions t ON t.Id = upp.TransactionId
WHERE t.RefId LIKE N'UPP-BACKFILL-%';

DECLARE @SynthUppCount INT = (SELECT COUNT(*) FROM @SyntheticUpp);
DECLARE @SynthTxCount INT =
(
    SELECT COUNT(*)
    FROM CMS.Transactions t
    WHERE t.RefId LIKE N'UPP-BACKFILL-%'
);

DELETE upp
FROM CMS.UserPackagePurchases upp
INNER JOIN @SyntheticUpp s ON s.Id = upp.Id;

DELETE t
FROM CMS.Transactions t
WHERE t.RefId LIKE N'UPP-BACKFILL-%';

PRINT CONCAT('Deleted synthetic UPP: ', @SynthUppCount);
PRINT CONCAT('Deleted synthetic Transactions: ', @SynthTxCount);

------------------------------------------------------------
-- B) UPP created from ManualPayments reuse (no new Tx)
------------------------------------------------------------
DECLARE @ManualUpp TABLE (Id BIGINT PRIMARY KEY);

INSERT INTO @ManualUpp (Id)
SELECT upp.Id
FROM CMS.UserPackagePurchases upp
INNER JOIN CMS.ManualPayments mp
    ON mp.TransactionId = upp.TransactionId
   AND mp.IsDeleted = 0
   AND mp.Status = 1 -- Approved
WHERE upp.IsDeleted = 0
  AND upp.PurchaseMethod = 3 -- Manual
  AND upp.Created >= @BackfillStartedAt
  -- exclude anything already covered as synthetic (shouldn't overlap)
  AND NOT EXISTS (
      SELECT 1 FROM CMS.Transactions t
      WHERE t.Id = upp.TransactionId AND t.RefId LIKE N'UPP-BACKFILL-%'
  );

DECLARE @ManualUppCount INT = (SELECT COUNT(*) FROM @ManualUpp);

DELETE upp
FROM CMS.UserPackagePurchases upp
INNER JOIN @ManualUpp m ON m.Id = upp.Id;

PRINT CONCAT('Deleted ManualPayment-linked UPP (Created >= @BackfillStartedAt): ', @ManualUppCount);
PRINT CONCAT('@BackfillStartedAt used: ', CONVERT(varchar(30), @BackfillStartedAt, 126));

------------------------------------------------------------
-- Optional sanity after rollback
------------------------------------------------------------
DECLARE @Orphans INT =
(
    SELECT COUNT(*)
    FROM CMS.ClubMemberships m
    WHERE m.IsDeleted = 0
      AND NOT EXISTS (
          SELECT 1
          FROM CMS.UserPackagePurchases upp
          LEFT JOIN CMS.Transactions t ON t.Id = upp.TransactionId AND t.IsDeleted = 0
          WHERE upp.UserId = m.UserId
            AND upp.IsDeleted = 0
            AND (upp.TransactionId IS NULL OR t.PaymentStatus = 0)
      )
);

PRINT CONCAT('Club members without successful UPP after rollback: ', @Orphans);
PRINT '(Expect around 101 if dump-era state, if no new purchases since.)';

IF @Commit = 1
BEGIN
    COMMIT;
    PRINT 'Rollback COMMITTED.';
END
ELSE
BEGIN
    ROLLBACK;
    PRINT 'Dry-run only — ROLLBACK. Set @Commit = 1 after verifying counts.';
END
