WITH new_solo AS (
SELECT /*+ materialize */client_key, TRUNC(accept_date) AS solo_date FROM (
  SELECT client_key, ACCEPT_DATE FROM krn_add_clients@xldr.bg d
  WHERE CATEGORY_TYPE IN ('SB','SS','SF','SN','SG', 'SA')
  AND status='P'
  AND d.ACCEPT_DATE IN (SELECT MAX(dd.ACCEPT_DATE) FROM krn_add_clients@xldr.bg dd
  WHERE dd.client_key=d.client_key
  AND CATEGORY_TYPE IN ('SB','SS','SF','SN','SG', 'SA'))
)
WHERE TRUNC(accept_date) BETWEEN '11aug2017' AND '25aug2017'
),
camps AS (
SELECT /*+ materialize */ client_key, comment_sysdate, user_name, campaign_no AS camp_no
 FROM krn_offers@xldr.bg
  WHERE run_date > '11aug2017'
  AND comment_main IS NOT NULL
),

digital_logins AS (
SELECT /*+ materialize */ c.client_key, a.last_login, a.login_count FROM
    (SELECT user_id, MAX(login_date) AS last_login,  COUNT(1) AS login_count FROM krn_auth_user_login_hist@xldr.bg
    WHERE login_date > SYSDATE - 90
    AND auth_channel_id IN ('RIB', 'EXPRESSONLINE', 'EXSPRESSONLINE', 'IMB-WEB', 'MOBILE')
    GROUP BY user_id) a
join
    (SELECT id, client_id_cbs AS client_key FROM krn_auth_users@xldr.bg b join new_solo ON
    new_solo.client_key = b.client_id_cbs) c
ON a.user_id = c.id
),

mobile_logins AS (
SELECT /*+ materialize */ c.client_key, a.last_login, a.login_count FROM
    (SELECT user_id, MAX(login_date) AS last_login,  COUNT(1) AS login_count FROM krn_auth_user_login_hist@xldr.bg
    WHERE login_date > SYSDATE - 90
    AND auth_channel_id IN ('MOBILE')
    GROUP BY user_id) a
join
    (SELECT id, client_id_cbs AS client_key FROM krn_auth_users@xldr.bg b join new_solo ON
    new_solo.client_key = b.client_id_cbs) c
ON a.user_id = c.id
),

plus AS (
SELECT /*+ materialize*/ a.client_key, a.label_type, a.label_score
 FROM krn_client_label_hist@xldr.bg a
join new_solo b
 ON a.client_key = b.client_key
WHERE a.label_status = 'A'
 AND end_date = '01jan4444'
),

amex AS (
SELECT new_solo.client_key,
       CASE WHEN x.client_key IS NULL THEN 'NO' ELSE 'YES' END AS AMEX
  FROM new_solo
  left join (SELECT DISTINCT client_key FROM sas.dmt_plc_cards@sas
              WHERE  card_class = 'AMEX'
             AND ( ((date_reg BETWEEN '11aug2017' AND SYSDATE) AND dep_reg LIKE '%SOLO%')
                     OR
                   ((date_act BETWEEN '11aug2017' AND SYSDATE) AND dep_act LIKE '%SOLO%')
                                 )
                        ) x
    ON x.client_key = new_solo.client_key
),


loans AS (
SELECT new_solo.client_key,
       CASE WHEN x.client_key IS NULL THEN 'NO' ELSE 'YES' END AS LOAN
  FROM new_solo
  left join (SELECT DISTINCT client_key
               FROM sas.DMT_CREDIT_AGREEMENTS@sas a
              WHERE a.date_start > '11aug2017'
                AND resp_branch LIKE '%SOLO%') x
    ON x.client_key = new_solo.client_key
),

deposits AS (
SELECT new_solo.client_key,
       CASE WHEN x.client_key IS NULL THEN 'NO' ELSE 'YES' END AS DEPOSIT
  FROM new_solo
  left join (SELECT DISTINCT client_key
                FROM sas.dmt_deposits@sas
             WHERE date_start > '11aug2017'
              AND reg_branch LIKE '%SOLO%') x
    ON x.client_key = new_solo.client_key
)
,plc AS
(SELECT client_key, COUNT(*) PLASTIC_CARD_CNT
   FROM PLC_CARDS@XLDR.BG P
  WHERE P.STATUS = 'W'
    AND P.EXP_DATE >= TRUNC(SYSDATE, 'MM')
  GROUP BY client_key
)

,UTIL AS
(SELECT /*+MATERIALIZE */ TO_NUMBER(CLIENT_KEY) CLIENT_KEY
       ,SUM(CASE WHEN UTIL > 1 THEN 1 ELSE 0 END) UTIL
  FROM (
        SELECT A.CLIENT_KEY
              ,A.HIST_DATE
              ,SUM(NVL(A.ENTRIES_COUNT,0)) UTIL
          FROM GIORGIJAPARIDZE.DMT_ENTRIES@RBRISK A
     LEFT JOIN asabashvili.all_mcc_2 B
            ON CASE
                 WHEN A.UTILITY_PAYMENT_TYPE IS NOT NULL THEN A.UTILITY_PAYMENT_TYPE
                 WHEN A.TRANSACTION_TYPE     IS NOT NULL THEN A.TRANSACTION_TYPE
                 WHEN A.MCC IS NOT NULL THEN TO_CHAR(A.MCC, 'fm0000')
                 ELSE CONCAT(A.PROD_GROUP, A.ENTRY_TYPE)
               END = B.MCC
         WHERE HIST_DATE >= TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -2), 'YYYY/MM')
           AND A.ENTRY_SIDE = 'D'
           AND B.IND_RBRISK = 'Utilities'
         GROUP BY A.CLIENT_KEY, A.HIST_DATE
        )
GROUP BY CLIENT_KEY
)

,DD AS
(
   SELECT /*+MATERIALIZE */ CLIENT_KEY, SUM(OPERATION_COUNT) DD_CNT
     FROM GIORGIJAPARIDZE.DMT_SERVICES_HISTORY@RBRISK
    WHERE SERVICE_TYPE = 'DD'
      AND YEAR_MONTH >= TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY/MM')
    GROUP BY CLIENT_KEY
)

,has_dd AS (SELECT /*+NO_INDEX(a)*/ DISTINCT a.client_key
  FROM krn_clients@xldr.bg a
  JOIN plc
    ON a.client_key = plc.client_key
   AND plc.PLASTIC_CARD_CNT > 0
  JOIN DD
    ON A.CLIENT_KEY = DD.CLIENT_KEY
   AND DD_CNT > 0
  JOIN UTIL
    ON A.CLIENT_KEY = UTIL.CLIENT_KEY
   AND UTIL > 0
WHERE a.tran_status = 'A'
   AND a.business = 'P01'
)
, expiring_cards AS (
 SELECT new_solo.client_key FROM
   (SELECT DISTINCT client_key FROM
        sas.dmt_plc_cards@sas
        WHERE status  = 'W'
        AND date_close IS NULL
        AND date_exp BETWEEN '01oct2017' AND '31oct2017'
        AND valid_to = '01jan4444') a
   join new_solo
      ON a.client_key = new_solo.client_key
)
, expiring_deposits AS (

SELECT new_solo.client_key,
       CASE WHEN x.client_key IS NULL THEN 'NO' ELSE 'YES' END AS HAS_EXPIRING_DEPOSIT
  FROM new_solo
  left join (SELECT DISTINCT client_key
                FROM sas.dmt_deposits@sas
             WHERE date_end BETWEEN '01oct2017' AND '31oct2017'
              AND date_end_real IS NULL) x
    ON x.client_key = new_solo.client_key

)

SELECT new_solo.client_key,
       camps.comment_sysdate AS camp_comment_sysdate,
             camps.camp_no AS camp_no,
             camps.user_name AS camp_user_name,
             digital_logins.last_login AS digital_last_login,
             digital_logins.login_count AS digital_login_count,
             mobile_logins.last_login AS mobile_last_login,
             mobile_logins.login_count AS mobile_login_count,
             plus.label_type AS plus_label_type,
             plus.label_score AS plus_label_score,
             amex.amex,
             loans.loan,
             deposits.deposit,
             CASE WHEN has_dd.client_key IS NOT NULL THEN 'YES' ELSE 'NO' END AS DD,
             CASE WHEN expiring_cards.client_key IS NOT NULL THEN 'YES' ELSE 'NO' END AS has_expiring_card,
             expiring_deposits.HAS_EXPIRING_DEPOSIT
 FROM new_solo left join camps
  ON new_solo.client_key = camps.client_key
 left join digital_logins
  ON new_solo.client_key = digital_logins.client_key
 left join plus
  ON new_solo.client_key = plus.client_key
 left join mobile_logins
  ON new_solo.client_key = mobile_logins.client_key
 left join amex
  ON new_solo.client_key = amex.client_key
 left join loans
  ON new_solo.client_key = loans.client_key
 left join deposits
  ON new_solo.client_key = deposits.client_key
 left join has_dd
  ON new_solo.client_key = has_dd.client_key
 left join expiring_cards
  ON new_solo.client_key = expiring_cards.client_key
 left join expiring_deposits
  ON new_solo.client_key = expiring_deposits.client_key