WITH

/*List of All Mobile App Alert Contacts*/
 MobileAlertContacts AS (

        SELECT
            userID,
            alertContactType,
            alertContactFriendlyName,
            alertContactValue,
            alertContactStatus
        FROM `uptimerobot-001.ur.alertcontacts`
        WHERE alertContactType IN (12,13)
        ),

/*Number of Unique Mobile App Alert Contacts per user*/
    CountOfUniqueMobileAppAlertContactsPerUser AS(
        SELECT
            userID,
            count(DISTINCT alertContactValue) uniqueMobileAlertContacts
    FROM MobileAlertContacts
    GROUP BY
      userID
    ORDER BY
      uniqueMobileAlertContacts DESC),

/*List of Phone Alert Contacts*/
  phoneAlertContacts as(
    SELECT
      userID,
      alertContactID,
      alertContactType,
      alertContactFriendlyName,
      alertContactValue,
      RIGHT (alertContactValue,3) alertContactValueLastChar,
      alertContactStatus

    FROM
      `uptimerobot-001.ur.alertcontacts`

    WHERE
      alertContactType IN (1,8,14)

  ),

/*Number of Unique Mobile App Alert Contacts per user*/

  CountOfUniquePhoneAlertContactsPerUser as (
  SELECT
    userID,
    COUNT(DISTINCT alertContactValueLastChar) uniquePhoneAlertContactsCount
  FROM
    phoneAlertContacts
  GROUP BY
    userID
  ),

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

        SELECT
            userID,
            alertContactType,
            alertContactFriendlyName,
            alertContactValue,
            alertContactStatus
        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 Active Team Integration Alert Contacts*/
   TeamIntegrationAlertContacts AS (

        SELECT
            userID,
            alertContactType,
            alertContactFriendlyName,
            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 Users with Mobile,Phone,Team Integration and Email Alert Contacts Count*/
MobilePhoneTeamItegrationAndEmailAlertContactsCount as (
    SELECT
      u.userID,
      userEmail,
      (CASE
        WHEN uniqueMobileAlertContacts IS NULL THEN 0
        ELSE uniqueMobileAlertContacts
      END) uniqueMobileAlertContactsCount,

      (CASE
        WHEN uniquePhoneAlertContactsCount IS NULL THEN 0
        ELSE uniquePhoneAlertContactsCount
      END) uniquePhoneAlertContactsCount,
      (CASE
        WHEN uniqueEmailAlertContacts IS NULL THEN 0
        ELSE uniqueEmailAlertContacts
      END) uniqueEmailAlertContactsCount,
      (CASE
        WHEN uniqueTeamIntegrationsCount IS NULL THEN 0
        ELSE uniqueTeamIntegrationsCount
      END) uniqueTeamIntegrationsForFREECount,
      (CASE
        WHEN userIsProPlan IS NULL THEN 'FREE'
        WHEN userIsProPlan = 0 THEN 'FREE'
        WHEN userIsProPlan = 1 THEN 'PRO'
        ELSE 'WTF'
      END) userIsProPlan


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

    LEFT JOIN
      CountOfUniquePhoneAlertContactsPerUser
    ON CountOfUniquePhoneAlertContactsPerUser.userID = u.userID

    LEFT JOIN
      CountOfUniqueEmailAlertContactsPerUser
    ON CountOfUniqueEmailAlertContactsPerUser.userID = u.userID

    LEFT JOIN
      countOfTeamIntegrationAlertContactsByUser
    ON countOfTeamIntegrationAlertContactsByUser.userID = u.userID
),

/*List of All Users with ()>2 Mobile or > 1 Phone or > 1 Team Integration) and 0 additional Email Alert Contacts*/
MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts as (

SELECT
    *
FROM
    MobilePhoneTeamItegrationAndEmailAlertContactsCount

WHERE
  (uniqueMobileAlertContactsCount > 2 OR uniquePhoneAlertContactsCount >1 OR uniqueTeamIntegrationsForFREECount >0)
  AND uniqueEmailAlertContactsCount = 0


ORDER BY
  uniquePhoneAlertContactsCount DESC,
  uniqueMobileAlertContactsCount DESC,
  uniqueTeamIntegrationsForFREECount DESC,
  uniqueEmailAlertContactsCount DESC),

preFinalData as (
  SELECT
    ac.userID,
    userEmail,
    userIsProPlan,
    alertContactType,
    alertContactFriendlyName,
    CONCAT('<li><a style="color: #3BD771; text-underline: none;"><strong>',STRING_AGG(alertContactFriendlyName,'</a></li><li><a style="color: #3BD771; text-underline: none;"><strong>' ) OVER (PARTITION BY ac.userID),'</strong></a></li>') alertContactValueMergedStringListHTML,
    STRING_AGG(alertContactFriendlyName,', ' ) OVER (PARTITION BY ac.userID) alertContactFriendlyNameMergedString,
    to_json_string(CONCAT('[',STRING_AGG(alertContactFriendlyName,', ' ) OVER (PARTITION BY ac.userID),']')) alertContactFriendlyNameMergedJSON,
    alertContactValue,
    STRING_AGG(alertContactValue,', ' ) OVER (PARTITION BY ac.userID) alertContactValueMergedString,
    to_json_string(CONCAT('[',STRING_AGG(alertContactValue,', ' ) OVER (PARTITION BY ac.userID),']')) alertContactValueMergedJSON,
    alertContactStatus,
    uniqueMobileAlertContactsCount,
    uniquePhoneAlertContactsCount,
    uniqueEmailAlertContactsCount,
    uniqueTeamIntegrationsForFREECount

FROM
  `uptimerobot-001.ur.alertcontacts` ac

LEFT JOIN
  MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts

ON ac.userID = MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts.userID

WHERE
/*Filter for All Push, Phone, Email, Team Integration Alert Contacts*/
alertContactType IN (1,5,7,8,11,12,13,14,15,17,18,20)
AND MultipleMobilePhoneTeamItegrationZeroEmailAlertContacts.userID IS NOT NULL

ORDER BY
  ac.userID)

SELECT
  userID,
  userEmail,
  userIsProPlan,
  alertContactValueMergedStringListHTML,
  alertContactFriendlyNameMergedString,
  alertContactValueMergedString,
  MAX(uniqueMobileAlertContactsCount) uniqueMobileAlertContactsCount,
  MAX(uniquePhoneAlertContactsCount) uniquePhoneAlertContactsCount,
  MAX(uniqueEmailAlertContactsCount) uniqueEmailAlertContactsCount,
  MAX(uniqueTeamIntegrationsForFREECount) uniqueTeamIntegrationsForFREECount

FROM
preFinalData

WHERE
  userIsProPlan = 'PRO'

GROUP BY
  userID,
  userEmail,
  userIsProPlan,
  alertContactValueMergedStringListHTML,
  alertContactFriendlyNameMergedString,
  alertContactValueMergedString