DROP PROCEDURE IF EXISTS BuyAndSellRequestAnonymiseJob;;
CREATE PROCEDURE BuyAndSellRequestAnonymiseJob (smbr_expiration_period INT, smbr_cool_period INT, id_PIID INT, account_chng_PIID INT)
BEGIN
SET SESSION tx_isolation='READ-COMMITTED';

-- anonymise unsucessful

SELECT  Nic_Handle, NH_Name, NH_Email, NH_Address
 FROM NicHandleHist where Chng_ID = id_PIID INTO @Nic_Handle, @NH_Name, @NH_Email, @NH_Address;

SELECT Id FROM SecondaryMarketBuyRequestHist
 WHERE
    datediff(NOW(),Created_TS) > smbr_expiration_period + smbr_cool_period
    AND Status != 'Sold';

    UPDATE `SecondaryMarketBuyRequestHist` SET
    `Chng_NH`=@Nic_Handle,
    `D_Holder`='PII Purged',
    `Remark`=NULL,
    `H_Remark`=NULL,
    `Admin_Name`=@NH_Name,
    `Admin_Email`=@NH_Email,
    `Admin_Co_Name`=@NH_Name,
    `Creator_NH_Chng_ID`= id_PIID,
    `Account_Chng_ID`= account_chng_PIID,
    `Admin_Address` = @NH_Address WHERE
    datediff(NOW(),Created_TS) > smbr_expiration_period + smbr_cool_period
    AND Id NOT IN (SELECT DISTINCT Id FROM `SecondaryMarketBuyRequestHist` WHERE  Status = 'Sold') and `D_Holder`!='PII Purged';

SET SESSION tx_isolation='REPEATABLE-READ';
END;;

DROP PROCEDURE IF EXISTS BuyAndSellRequestCleanUpJob;;
CREATE PROCEDURE BuyAndSellRequestCleanUpJob (smbr_expiration_period INT, smbr_cool_period INT, smbr_history_retention_period INT, domain_cool_period INT, secondary_market_authcode_expiration_period INT)
BEGIN
SET SESSION tx_isolation='READ-COMMITTED';

SELECT DISTINCT Id FROM (
    SELECT Id FROM SecondaryMarketBuyRequestHist
        WHERE Status != 'Sold' AND
            datediff(NOW(),Created_TS) > (IF (Authcode_TS IS NOT NULL, secondary_market_authcode_expiration_period, smbr_expiration_period) + smbr_cool_period + smbr_history_retention_period)
    UNION ALL
    SELECT Id FROM SecondaryMarketBuyRequestHist SMBRH
        LEFT JOIN DomainHist on Domain_Chng_ID = DomainHist.Chng_ID
             WHERE SMBRH.Status = 'Sold'
             AND  DomainHist.Chng_ID IS NULL
) K;
-- delete SecondaryMarketSellRequestHist

DELETE SMSRH
    FROM SecondaryMarketSellRequestHist SMSRH
    JOIN SecondaryMarketBuyRequestHist SMBRH on BuyRequest_Chng_ID = SMBRH.Chng_ID
    JOIN DomainHist DH on Domain_Chng_ID = DH.Chng_ID
    JOIN Domain D ON DH.D_Name = D.D_Name
WHERE DH.D_Holder != D.D_Holder
    AND datediff(NOW(),SMBRH.Created_TS) > domain_cool_period;

-- delete orphans -- that may be slow

DELETE SMSRH
    FROM SecondaryMarketSellRequestHist SMSRH
    JOIN SecondaryMarketBuyRequestHist SMBRH on BuyRequest_Chng_ID = SMBRH.Chng_ID
    WHERE Domain_Chng_ID NOT IN (SELECT Chng_ID FROM DomainHist);

-- delete unsucesfull SecondaryMarketBuyRequestHist
DELETE IGNORE FROM `SecondaryMarketBuyRequestHist`
    WHERE Status != 'Sold' AND
    datediff(NOW(),Created_TS) > (IF (Authcode_TS IS NOT NULL, secondary_market_authcode_expiration_period, smbr_expiration_period) + smbr_cool_period + smbr_history_retention_period);

-- delete succesfull SecondaryMarketBuyRequestHist (orphaned to domains)
DELETE IGNORE SMBRH FROM SecondaryMarketBuyRequestHist SMBRH
     LEFT JOIN DomainHist on Domain_Chng_ID = DomainHist.Chng_ID
     WHERE SMBRH.Status = 'Sold'
     AND  DomainHist.Chng_ID IS NULL;

SET SESSION tx_isolation='REPEATABLE-READ';
END;;