#!/usr/bin/python
# -*- coding: utf-8 -*-
#
# Generate a list of accounts for all Toolforge users who have
# validated their email address via Wikitech and who have not opted out of
# email contact.
#
# Copyright © 2017 Wikimedia Foundation and contributors
# CC0 1.0 <https://creativecommons.org/publicdomain/zero/1.0/>
from __future__ import print_function
import ldap
import subprocess


l = ldap.open('ldap-labs.eqiad.wikimedia.org')
l.protocol_version = ldap.VERSION3

baseDN = 'dc=wikimedia,dc=org'
searchScope = ldap.SCOPE_SUBTREE

# Get a list of all Toolforge project members
res = l.search_s(baseDN, searchScope, 'cn=project-tools', ['member'])
members = res[0][1]['member']

# Get the developer account name for all Toolforge project members
ldap_names = []
for member in members:
    res = l.search_s(baseDN, searchScope, member.split(',')[0], ['cn'])
    if res and 'cn' in res[0][1]:
        ldap_names.append(res[0][1]['cn'][0])
ldap_names = set(ldap_names)

# Get the username for all Wikitech accounts that have validated their address
# at some point and who have not opt-ed out of email contact by other users.
query = """\
SELECT user_name
FROM user
WHERE user_email <> ''
  AND user_email_authenticated IS NOT NULL
  AND user_id NOT IN (
      SELECT up_user
      FROM user_properties
      WHERE up_property='disablemail'
        AND up_value=1
);"""
sql = subprocess.Popen(
    ['/usr/local/bin/sql', 'labswiki'],
    stdout=subprocess.PIPE, stdin=subprocess.PIPE, stderr=subprocess.PIPE)
mysql_names = set(sql.communicate(input=query)[0].strip().split('\n')[1:])

# Find the intersection of the two lists
users = [u for u in ldap_names if u in mysql_names]
users.sort()
for user in users:
    print(user)