WITH

/*List of all successful subscription payments - Alltime*/
    SuccessSubsPayment AS(
         SELECT
            userID,
            paymentDateTime,
            paymentPeriod,
            productID

         FROM `uptimerobot-001.ur.payments`
         WHERE
          paymentType = 2
          AND paymentStatus = 1
     ),

/*Last successful subscription payments by user*/
     LastSuccessSubsPayment AS(
         SELECT
            p1.userID,
            p1.paymentDateTime as lastPaymentDateTime,
            p1.paymentPeriod as lastPaymentPeriod,
            p1.productID as lastPaymentProductID

        FROM SuccessSubsPayment p1
        LEFT JOIN SuccessSubsPayment p2
        ON (p1.userID = p2.userID AND p1.paymentDateTime < p2.paymentDateTime)
        WHERE
            p2.userID IS NULL

     ),

/*added userType - Current / Churned Customer*/
    LastSubsPaymentUsersType AS (
            SELECT
                userID,
                lastPaymentDateTime,
                lastPaymentPeriod,
                (CASE
                    WHEN (DATE_ADD(DATETIME(lastPaymentDateTime), INTERVAL lastPaymentPeriod MONTH) >= CURRENT_DATE()) THEN 'CurrentCustomer'
                    ELSE 'ChurnedCustomer'
                END) AS customerType,
                lastPaymentProductID
            FROM LastSuccessSubsPayment
    ),

/*added productName & ProductValue*/
    LastSubsPaymentUsersTypeProduct AS (
            SELECT
                lsput.userID,
                lsput.lastPaymentDateTime,
                lsput.lastPaymentPeriod,
                lsput.customerType,
                lsput.lastPaymentProductID,
                pr.productName,
                pr.productValue

            FROM
                LastSubsPaymentUsersType as lsput
            LEFT JOIN `uptimerobot-001.ur.products` as pr
                ON pr.productID = lsput.lastPaymentProductID
    ),

/*Deduplication of multiple last successful payments from the same user - some edge cases*/
 LastSuccessSubsPaymentUserTypeProduct AS (
    SELECT
        userID,
        MAX(lastPaymentDateTime) as lastPaymentDateTime,
        MAX(lastPaymentPeriod) as lastPaymentPeriod,
        MAX(customerType) as customerType,
        MAX(lastPaymentProductID) as lastPaymentProductID,
        MAX(productName) as lastPaymentProductName,
        MAX(productValue) as lastPaymentProductValue

    FROM LastSubsPaymentUsersTypeProduct

    GROUP BY userID
    ORDER BY userID DESC
    ),

/*List of All Active Team Integration Alert Contacts*/
   TeamIntegrationAlertContacts AS (

        SELECT
          userID,
          alertContactType,
          alertContactValue,
          alertContactStatus

        FROM `uptimerobot-001.ur.alertcontacts`
        WHERE alertContactType IN (5,7,11,15,17,18,20) AND alertContactStatus = 2
        ),

  /*List users with number of unique team integrations alertContacts*/
    countOfTeamIntegrationAlertContactsByUser AS (
      SELECT
        userID,
        COUNT(DISTINCT alertContactValue) as uniqueTeamIntegrationsCount
      FROM
        TeamIntegrationAlertContacts

      GROUP BY
        userID
    ),

/*List of All Active Not Default E-mail Alert Contacts*/
    NotDefaultEmailAlertContacts AS (

        SELECT
            userID,
            alertContactType,
            alertContactValue,
            alertContactStatus,
            rand() as random
        FROM `uptimerobot-001.ur.alertcontacts`
        WHERE
          alertContactType = 2
          AND alertContactStatus = 2
        ),

/*Number of Active Unique E-mail Alert Contacts per user*/
    CountOfUniqueEmailAlertContactsPerUser AS(
        SELECT
            userID,
            count(DISTINCT alertContactValue) uniqueEmailAlertContacts
        FROM NotDefaultEmailAlertContacts
        GROUP BY userID
        ORDER BY uniqueEmailAlertContacts DESC

    ),

/*List of all users with the count of active unique not default Email Alert Contacts by user type - FREE / Current / Churned Customer & productName & productValue*/
EmailAlertContacts AS(
    SELECT
        CountOfUniqueEmailAlertContactsPerUser.userID,
        uniqueEmailAlertContacts,
        (CASE
            WHEN customerType IS NULL THEN 'FREE'
            ELSE customerType
        END) as customerType,
        (CASE
            WHEN lastPaymentProductName IS NULL THEN 'FREE'
            ELSE lastPaymentProductName
        END) as lastPaymentProductName,
        (CASE
            WHEN lastPaymentProductValue IS NULL THEN 0
            ELSE lastPaymentProductValue
        END) as lastPaymentProductValue


    FROM CountOfUniqueEmailAlertContactsPerUser
    LEFT JOIN LastSuccessSubsPaymentUserTypeProduct
        on CountOfUniqueEmailAlertContactsPerUser.userID = LastSuccessSubsPaymentUserTypeProduct.userID
    ),
/*Number of Users with active Not Default Email AlertContact by customer type and lastPaymentProduct*/
/*
SELECT
    customerType,
    lastPaymentProductName,
    count(userID) as userCount
FROM EmailAlertContacts
GROUP BY
    customerType,
    lastPaymentProductName,
    lastPaymentProductValue
ORDER BY
    customerType,
    lastPaymentProductValue*/

preFinalData as (

  SELECT
        NotDefaultEmailAlertContacts.userID,
        userEmail,
        MD5(CAST(NotDefaultEmailAlertContacts.userID as string)) hashedUserID,
        DENSE_RANK() OVER (PARTITION BY customerType ORDER BY MD5(CAST(NotDefaultEmailAlertContacts.userID as string))) as rank,
        (CASE
            WHEN
                userEmail IS NULL THEN '_onlyDefaultEmailAlertContact'
                ELSE userEmail
            END) userEmailDefault,
        alertContactType,
        alertContactValue,

        CONCAT('<li><a href="#" style="color: #3BD771; text-underline: none;">',STRING_AGG(alertContactValue,'</a></li><li><a href="#" style="color: #3BD771; text-underline: none;">' ) OVER (PARTITION BY NotDefaultEmailAlertContacts.userID),'</a></li>') alertContactValueMergedString,
        to_json_string(CONCAT('[',STRING_AGG(alertContactValue,'", "' ) OVER (PARTITION BY NotDefaultEmailAlertContacts.userID),']')) alertContactValueMergedJson,
        (CASE
            WHEN alertContactValue = userEmail THEN 1
            WHEN alertContactValue <> userEmail THEN 0
        ELSE 999999999
        END
        ) isDefaultAlertContact,
        (CASE
            WHEN alertContactValue = userEmail THEN uniqueEmailAlertContacts - 1
            WHEN alertContactValue <> userEmail THEN uniqueEmailAlertContacts
        ELSE 999999999
        END
        ) uniqueEmailAlertContactsCorrected,
        alertContactStatus,
        uniqueEmailAlertContacts,
        (CASE
            WHEN alertContactValue = userEmail AND uniqueEmailAlertContacts <= 4 THEN '<=3'
            WHEN alertContactValue = userEmail AND uniqueEmailAlertContacts >= 5 AND uniqueEmailAlertContacts <= 7 THEN '<4 - 6>'
            WHEN alertContactValue = userEmail AND uniqueEmailAlertContacts >= 8 AND uniqueEmailAlertContacts <= 12 THEN '<7 - 11>'
            WHEN alertContactValue = userEmail AND uniqueEmailAlertContacts >= 13 THEN '>=12'


            WHEN alertContactValue <> userEmail AND uniqueEmailAlertContacts <= 3 THEN '<=3'
            WHEN alertContactValue <> userEmail AND uniqueEmailAlertContacts >= 4 AND uniqueEmailAlertContacts <= 6 THEN '<4 - 6>'
            WHEN alertContactValue <> userEmail AND uniqueEmailAlertContacts >= 7 AND uniqueEmailAlertContacts <= 11 THEN '<7 - 11>'
            WHEN alertContactValue <> userEmail AND uniqueEmailAlertContacts >= 12 THEN '>=12'

            ELSE 'WTF'
        END) emailAlertContactsBucket,
        (CASE
            WHEN customerType IS NULL THEN 'FREE'
            ELSE customerType
        END) as customerType,
        (CASE
            WHEN lastPaymentProductName IS NULL THEN 'FREE'
            ELSE lastPaymentProductName
        END) as lastPaymentProductName,
        (CASE
            WHEN lastPaymentProductValue IS NULL THEN 0
            ELSE lastPaymentProductValue
        END) as lastPaymentProductValue,
        (CASE
            WHEN uniqueTeamIntegrationsCount IS NULL THEN 0
            ELSE uniqueTeamIntegrationsCount
        END) uniqueTeamIntegrationsForFREEusersCount


    FROM
        NotDefaultEmailAlertContacts

    LEFT JOIN
        CountOfUniqueEmailAlertContactsPerUser
    ON NotDefaultEmailAlertContacts.userID = CountOfUniqueEmailAlertContactsPerUser.userID

    LEFT JOIN
        `uptimerobot-001.ur.users` u
    ON NotDefaultEmailAlertContacts.userID = u.userID

    LEFT JOIN LastSuccessSubsPaymentUserTypeProduct
    ON NotDefaultEmailAlertContacts.userID = LastSuccessSubsPaymentUserTypeProduct.userID

    LEFT JOIN countOfTeamIntegrationAlertContactsByUser
    ON NotDefaultEmailAlertContacts.userID = countOfTeamIntegrationAlertContactsByUser.userID


    WHERE
    NotDefaultEmailAlertContacts.userID IS NOT NULL
    AND  alertContactValue <> userEmail -- filtering only rows where user register email is not equal to additional email Alert Contact

    ORDER BY
        customerType DESC,
        hashedUserID,
        emailAlertContactsBucket,
        userID DESC)

SELECT
  userID,
  userEmail as email,
  rank,
  alertContactValueMergedString,
  uniqueEmailAlertContactsCorrected,
  emailAlertContactsBucket,
  customerType,
  lastPaymentProductValue,
  uniqueTeamIntegrationsForFREEusersCount


FROM
  preFinalData

WHERE
 customerType = 'FREE'
 AND emailAlertContactsBucket = '<=3'
 AND rank >15000

 GROUP BY
  userID,
  userEmail,
  rank,
  alertContactValueMergedString,
  uniqueEmailAlertContactsCorrected,
  emailAlertContactsBucket,
  customerType,
  lastPaymentProductValue,
  uniqueTeamIntegrationsForFREEusersCount

ORDER BY rank