-- Which OTHER accounts used this card (for the staff email on a payment that is not
-- credited yet). Same match as afd_CardSeenOnOtherAccounts (004), which only counts:
-- Braintree's unique card identifier, or the masked card number on earlier payments.
-- At most 20, newest account numbers first.
--
-- Run as a DB admin after 011. Safe to run again. Until it is run, the staff email
-- simply has no "Also used on accounts" line.

DELIMITER ;;

DROP PROCEDURE IF EXISTS `afd_CardOtherAccounts_List`;;
CREATE PROCEDURE `afd_CardOtherAccounts_List`(
  IN p_Fingerprint varchar(64), IN p_MaskedNumber varchar(16), IN p_AccountID bigint)
BEGIN
  SELECT DISTINCT AccountID
  FROM (
    SELECT AccountID FROM afd_RiskSignal
    WHERE p_Fingerprint <> '' AND CardFingerprint = p_Fingerprint AND AccountID <> p_AccountID
    UNION
    SELECT AccountID FROM TransactionLog
    WHERE p_MaskedNumber <> '' AND CCNumber = p_MaskedNumber AND AccountID <> p_AccountID
  ) seen
  ORDER BY AccountID DESC
  LIMIT 20;
END ;;

DELIMITER ;

-- With per-procedure grants:
-- GRANT EXECUTE ON PROCEDURE `prepaid`.`afd_CardOtherAccounts_List` TO 'your_user'@'your_host';