-- Discovery remarketing cohort: dropped-off P3 users, India, never converted, 18+.
-- Returns the send list (user id + hashed email + name). Run for prod send-list generation.
SELECT u.iUserID, u.vHashedEmail, p.vName
FROM tbl_Users u
JOIN tbl_UserConfig c           ON c.iUserID = u.iUserID
LEFT JOIN tbl_UserData d        ON d.iUserID = u.iUserID
LEFT JOIN tbl_UserProfile p     ON p.iUserID = u.iUserID
LEFT JOIN tbl_UserPrograms upr  ON upr.iUserID = u.iUserID AND upr.iProgramId = 3
WHERE u.iProgramID = 3
  AND u.bDeleted = 0
  AND u.bTestUser = 0
  AND c.bOnBoardComplete = 1
  AND u.iActiveDayID = 1                 -- never progressed past Day 1 (dropped off)
  AND u.dDateCreated >= '2025-06-01'
  AND c.vCountry = 'India'
  AND (d.vAge IS NULL OR d.vAge NOT LIKE '< 18%')   -- exclude under-18
  AND (upr.bPreviouslySubscribed = 0 OR upr.bPreviouslySubscribed IS NULL);  -- never paid
