import re
import csv
import uuid

#opens wget data file and creates csv output files
users = open('UsersRDA.txt', 'r', encoding='utf-8')
users_csv = open('users_RDA_csv.csv', 'w', encoding='utf-8')
groups_users = open('groupsID_usersID_csv.csv', 'w', encoding='utf-8')

#erases whatever was in the created files so if the code was ran more than once
#it would get rid of the old data
users_csv.truncate(0)
groups_users.truncate(0)

data = users.read()
data = re.split('\|\.\|', data)
group_list = [] #lists created to append list of groups and users
users_list = []

users_csv.write("user_id,title_addr,name,rda-role,pro_title,field,organization,org_type,country,ISO\n")
for x in data: #iterates through data and cleans using regex
    rand_id = str(uuid.uuid4())
    x = x.replace(u'\xa0', u' ')
    x = re.sub('USER_\d+', rand_id, x) #replaces USER with randomly generated uuid
    x = re.sub('\w,\w', ';', x)
    x = re.sub('\|,\||\|;\|', ',', x)
    x = re.sub(',\s', ';', x)
    x = re.sub('(Mr)\s|(Mrs)\s|(Ms)\s|(Dr)\s|(Prof)\s', r'\1\2\3\4\5,', x)
    x = re.sub('\sChair\|name\|', ',chair,', x)
    x = re.sub('\|name\|', ',,', x)
    x = re.sub('%', '', x)
    x = re.sub('https://www.rd-alliance.org/(user|users)/(\w|-)+,', '', x)
    x = re.sub('Professional title:\s', '', x)
    x = re.sub('Primary\sDomain/Field\sof\sExpertise\s\(Other\):\s-', '', x)
    x = re.sub('Primary\sDomain/Field\sof\sExpertise\s\(Other\):\s', '', x)
    x = re.sub('Organization\sname:\s-','', x)
    x = re.sub('Organization\stype:\s-', '', x)
    x = re.sub('Organization\sname:\s', '', x)
    x = re.sub('Organization\stype:\s', '', x)
    x = re.sub('City:\s\w+,', '', x)
    x = re.sub('Country:\s', '', x)
    x = re.sub('NULL', '', x)

    #This is all countries and country codes
    x = re.sub('(Afghanistan)\|country\|', r'\1,AFG', x)
    x = re.sub('(Angola)\|country\|', r'\1,AGO', x)
    x = re.sub('(Albania)\|country\|', r'\1,ALB', x)
    x = re.sub('(United Arab Emirates)\|country\|', r'\1,ARE', x)
    x = re.sub('(Argentina)\|country\|', r'\1,ARG', x)
    x = re.sub('(Armenia)\|country\|', r'\1,ARM', x)
    x = re.sub('(Australia)\|country\|', r'\1,AUS', x)
    x = re.sub('(Austria)\|country\|', r'\1,AUT', x)
    x = re.sub('(Azerbaijan)\|country\|', r'\1,AZE', x)
    x = re.sub('(Burundi)\|country\|', r'\1,BDI', x)
    x = re.sub('(Belgium)\|country\|', r'\1,BEL', x)
    x = re.sub('(Benin)\|country\|', r'\1,BEN', x)
    x = re.sub('(Burkina)\|country\|', r'\1,BFA', x)
    x = re.sub('(Bangladesh)\|country\|', r'\1,BGD', x)
    x = re.sub('(Bulgaria)\|country\|', r'\1,BGR', x)
    x = re.sub('(Bahrain)\|country\|', r'\1,BHR', x)
    x = re.sub('(Bosnia Herzegovina)\|country\|', r'\1,BIH', x)
    x = re.sub('(Belize)\|country\|', r'\1,BLZ', x)
    x = re.sub('(Bolivia)\|country\|', r'\1,BOL', x)
    x = re.sub('(Brazil)\|country\|', r'\1,BRA', x)
    x = re.sub('(Barbados)\|country\|', r'\1,BRB', x)
    x = re.sub('(Bhutan)\|country\|', r'\1,BTN', x)
    x = re.sub('(Botswana)\|country\|', r'\1,BWA', x)
    x = re.sub('(Canada)\|country\|', r'\1,CAN', x)
    x = re.sub('(Switzerland)\|country\|', r'\1,CHE', x)
    x = re.sub('(Chile)\|country\|', r'\1,CHL', x)
    x = re.sub('(China)\|country\|', r'\1,CHN', x)
    x = re.sub('(Ivory Coast)\|country\|', r'\1,CIV', x)
    x = re.sub('(Cameroon)\|country\|', r'\1,CMR', x)
    x = re.sub('(Congo \{Democratic Rep\})\|country\|', r'\1,COD', x)
    x = re.sub('(Colombia)\|country\|', r'\1,COL', x)
    x = re.sub('(Costa Rica)\|country\|', r'\1,CRI', x)
    x = re.sub('(Cuba)\|country\|', r'\1,CUB', x)
    x = re.sub('(Cayman Islands)\|country\|', r'\1,CYM', x)
    x = re.sub('(Cyprus)\|country\|', r'\1,CYP', x)
    x = re.sub('(Czech Republic)\|country\|', r'\1,CZE', x)
    x = re.sub('(Germany)\|country\|', r'\1,DEU', x)
    x = re.sub('(Denmark)\|country\|', r'\1,DNK', x)
    x = re.sub('(Dominican Republic)\|country\|', r'\1,DOM', x)
    x = re.sub('(Algeria)\|country\|', r'\1,DZA', x)
    x = re.sub('(Ecuador)\|country\|', r'\1,ECU', x)
    x = re.sub('(Egypt)\|country\|', r'\1,EGY', x)
    x = re.sub('(Spain)\|country\|', r'\1,ESP', x)
    x = re.sub('(Estonia)\|country\|', r'\1,EST', x)
    x = re.sub('(Ethiopia)\|country\|', r'\1,ETH', x)
    x = re.sub('(Finland)\|country\|', r'\1,FIN', x)
    x = re.sub('(Fiji)\|country\|', r'\1,FJI', x)
    x = re.sub('(France)\|country\|', r'\1,FRA', x)
    x = re.sub('(Gabon)\|country\|', r'\1,GAB', x)
    x = re.sub('(United Kingdom)\|country\|', r'\1,GBR', x)
    x = re.sub('(Georgia)\|country\|', r'\1,GEO', x)
    x = re.sub('(Guernsey)\|country\|', r'\1,GGY', x)
    x = re.sub('(Ghana)\|country\|', r'\1,GHA', x)
    x = re.sub('(Gibraltar)\|country\|', r'\1,GIB', x)
    x = re.sub('(Guinea)\|country\|', r'\1,GIN', x)
    x = re.sub('(Gambia)\|country\|', r'\1,GMB', x)
    x = re.sub('(Greece)\|country\|', r'\1,GRC', x)
    x = re.sub('(Greenland)\|country\|', r'\1,GRL', x)
    x = re.sub('(Guatemala)\|country\|', r'\1,GTM', x)
    x = re.sub('(Hong Kong)\|country\|', r'\1,HKG', x)
    x = re.sub('(Croatia)\|country\|', r'\1,HRV', x)
    x = re.sub('(Hungary)\|country\|', r'\1,HUN', x)
    x = re.sub('(Indonesia)\|country\|', r'\1,IDN', x)
    x = re.sub('(India)\|country\|', r'\1,IND', x)
    x = re.sub('(Ireland \{Republic\})\|country\|', r'\1,IRL', x)
    x = re.sub('(Iran)\|country\|', r'\1,IRN', x)
    x = re.sub('(Iraq)\|country\|', r'\1,IRQ', x)
    x = re.sub('(Iceland)\|country\|', r'\1,ISL', x)
    x = re.sub('(Israel)\|country\|', r'\1,ISR', x)
    x = re.sub('(Italy)\|country\|', r'\1,ITA', x)
    x = re.sub('(Jamaica)\|country\|', r'\1,JAM', x)
    x = re.sub('(Jordan)\|country\|', r'\1,JOR', x)
    x = re.sub('(Japan)\|country\|', r'\1,JPN', x)
    x = re.sub('(Kazakhstan)\|country\|', r'\1,KAZ', x)
    x = re.sub('(Kenya)\|country\|', r'\1,KEN', x)
    x = re.sub('(Kyrgyzstan)\|country\|', r'\1,KGZ', x)
    x = re.sub('(Cambodia)\|country\|', r'\1,KHM', x)
    x = re.sub('(Kiribati)\|country\|', r'\1,KIR', x)
    x = re.sub('(Korea South)\|country\|', r'\1,KOR', x)
    x = re.sub('(Kosovo)\|country\|', r'\1,RKS', x)
    x = re.sub('(Kuwait)\|country\|', r'\1,KWT', x)
    x = re.sub('(St Lucia)\|country\|', r'\1,LCA', x)
    x = re.sub('(Lebanon)\|country\|', r'\1,LBN', x)
    x = re.sub('(Lithuania)\|country\|', r'\1,LTU', x)
    x = re.sub('(Sri Lanka)\|country\|', r'\1,LKA', x)
    x = re.sub('(Luxembourg)\|country\|', r'\1,LUX', x)
    x = re.sub('(Latvia)\|country\|', r'\1,LVA', x)
    x = re.sub('(Morocco)\|country\|', r'\1,MAR', x)
    x = re.sub('(Madagascar)\|country\|', r'\1,MDG', x)
    x = re.sub('(Mexico)\|country\|', r'\1,MEX', x)
    x = re.sub('(Macedonia)\|country\|', r'\1,MKD', x)
    x = re.sub('(Mali)\|country\|', r'\1,MLI', x)
    x = re.sub('(Malta)\|country\|', r'\1,MLT', x)
    x = re.sub('(Myanmar;\{Burma\})\|country\|', r'\1,MMR', x)
    x = re.sub('(Mongolia)\|country\|', r'\1,MNG', x)
    x = re.sub('(Mozambique)\|country\|', r'\1,MOZ', x)
    x = re.sub('(Mauritius)\|country\|', r'\1,MUS', x)
    x = re.sub('(Malawi)\|country\|', r'\1,MWI', x)
    x = re.sub('(Malaysia)\|country\|', r'\1,MYS', x)
    x = re.sub('(Namibia)\|country\|', r'\1,NAM', x)
    x = re.sub('(Nigeria)\|country\|', r'\1,NGA', x)
    x = re.sub('(Niger)\|country\|', r'\1,NER', x)
    x = re.sub('(Netherlands)\|country\|', r'\1,NLD', x)
    x = re.sub('(Norway)\|country\|', r'\1,NOR', x)
    x = re.sub('(Nepal)\|country\|', r'\1,NPL', x)
    x = re.sub('(Nauru)\|country\|', r'\1,NRU', x)
    x = re.sub('(New Zealand)\|country\|', r'\1,NZL', x)
    x = re.sub('(Pakistan)\|country\|', r'\1,PAK', x)
    x = re.sub('(Peru)\|country\|', r'\1,PER', x)
    x = re.sub('(Philippines)\|country\|', r'\1,PHL', x)
    x = re.sub('(Palau)\|country\|', r'\1,PLW', x)
    x = re.sub('(Papua New Guinea)\|country\|', r'\1,PNG', x)
    x = re.sub('(Poland)\|country\|', r'\1,POL', x)
    x = re.sub('(Puerto Rico)\|country\|', r'\1,PRI', x)
    x = re.sub('(Portugal)\|country\|', r'\1,PRT', x)
    x = re.sub('(Paraguay)\|country\|', r'\1,PRY', x)
    x = re.sub('(Palestine)\|country\|', r'\1,PSE', x)
    x = re.sub('(Qatar)\|country\|', r'\1,QAT', x)
    x = re.sub('(Romania)\|country\|', r'\1,ROU', x)
    x = re.sub('(Russian Federation)\|country\|', r'\1,RUS', x)
    x = re.sub('(Rwanda)\|country\|', r'\1,RWA', x)
    x = re.sub('(Saudi Arabia)\|country\|', r'\1,SAU', x)
    x = re.sub('(Sudan)\|country\|', r'\1,SDN', x)
    x = re.sub('(Senegal)\|country\|', r'\1,SEN', x)
    x = re.sub('(Singapore)\|country\|', r'\1,SGP', x)
    x = re.sub('(Solomon Islands)\|country\|', r'\1,SLB', x)
    x = re.sub('(Sierra Leone)\|country\|', r'\1,SLE', x)
    x = re.sub('(Somalia)\|country\|', r'\1,SOM', x)
    x = re.sub('(Serbia)\|country\|', r'\1,SRB', x)
    x = re.sub('(Sao Tome and Principe)\|country\|', r'\1,STP', x)
    x = re.sub('(Slovakia)\|country\|', r'\1,SVK', x)
    x = re.sub('(Slovenia)\|country\|', r'\1,SVN', x)
    x = re.sub('(Sweden)\|country\|', r'\1,SWE', x)
    x = re.sub('(Swaziland)\|country\|', r'\1,SWZ', x)
    x = re.sub('(Togo)\|country\|', r'\1,TGO', x)
    x = re.sub('(Thailand)\|country\|', r'\1,THA', x)
    x = re.sub('(Tunisia)\|country\|', r'\1,TUN', x)
    x = re.sub('(Turkey)\|country\|', r'\1,TUR', x)
    x = re.sub('(Tuvalu)\|country\|', r'\1,TUV', x)
    x = re.sub('(Taiwan)\|country\|', r'\1,TWN', x)
    x = re.sub('(Tanzania)\|country\|', r'\1,TZA', x)
    x = re.sub('(Trinidad & Tobago)\|country\|', r'\1,TTO', x)
    x = re.sub('(Uganda)\|country\|', r'\1,UGA', x)
    x = re.sub('(Ukraine)\|country\|', r'\1,UKR', x)
    x = re.sub('(Uruguay)\|country\|', r'\1,URY', x)
    x = re.sub('(United States)\|country\|', r'\1,USA', x)
    x = re.sub('(Uzbekistan)\|country\|', r'\1,UZB', x)
    x = re.sub('(Vatican City)\|country\|', r'\1,VAT', x)
    x = re.sub('(Venezuela)\|country\|', r'\1,VEN', x)
    x = re.sub('(Vietnam)\|country\|', r'\1,VNM', x)
    x = re.sub('(Vanuatu)\|country\|', r'\1,VUT', x)
    x = re.sub('(South Africa)\|country\|', r'\1,ZAF', x)
    x = re.sub('(Zambia)\|country\|', r'\1,ZMB', x)
    x = re.sub('(Zimbabwe)\|country\|', r'\1,ZWE', x)

    #appends all group names into group_list
    groups = re.search('My\sgroups:.+', x)
    if groups:
        group_list.append(groups.group())
    users_list.append(x)
    x = re.sub('My\sgroups:.*', '', x)
    users_csv.write(x+'\n')

users_csv.close()
users_list = users_list[:-1]

#sorts group_list into alphabetical order
group_list = ''.join(group_list)
group_list = re.sub('My\sgroups:', '', group_list)
group_list = group_list[:-1]
group_list = re.split('\.', group_list)
group_list = list(dict.fromkeys(group_list))
group_list = sorted(group_list)

groups_users.write('Group Name,Group ID,User IDs\n')
#loops through each group name and if the user is in the groups
#writes the user id to the group_users file
for i in group_list:
    rand_id = str(uuid.uuid4())
    i = re.sub('\(', '\(', i)
    i = re.sub('\)', '\)', i)
    for j in users_list:
        if re.search(i, j):
            groups_users.write(i+',')
            groups_users.write(rand_id+',')
            groups_users.write(j[:36]+',')
            groups_users.write('\n')

groups_users.close()
users.close()
