#!/usr/bin/python
#
#   Sync LDAP Mailinglists to Horde groups in Univention UCS 4.2
#
#   Public Domain
#

import psycopg2
import subprocess
import sys

# You find the database password with something like
#
#   # univention-app shell horde
#   # grep "password" /etc/horde/horde/conf.d/10-ucs.php

DATABASEPASSWORD = "PASSWORDGOESHERE"
MAILDOMAIN = "@test.local"
CALENDARUSER = "calendar@test.local"
GROUPSTOUPDATE = [ "testgroup" ]

testmode = True
for item in sys.argv:
    if item == "-h":
        print("Help: sync UCS LDAP mailinglists to Horde groups in postgresql database.")
        print("default is a testrun")
        print("-c commit changes")
        sys.exit(0)
    if item == "-c":
        print("Really executing SQL commands.")
        testmode = False

print("Updating groups: %s"%GROUPSTOUPDATE)

p = subprocess.Popen(["/usr/sbin/udm", "mail/lists", "list"], stdout=subprocess.PIPE)
out, err = p.communicate()

groups = {}
curgroup = None
for line in out.split("\n"):
    line = line.strip()
    if line.startswith("mailAddress:"):
        t = line.split(" ")
        print("found group %s"%t)
        curgroup = t[1].replace(MAILDOMAIN,"")
    # Add the calendar user 
        groups[curgroup] = [CALENDARUSER]
    elif line.startswith("members:"):
        t = line.split(" ")
        groups[curgroup].append(t[1])

try:
    conn = psycopg2.connect("dbname='hordedb' user='horde' host='localhost' password='%s'"%DATABASEPASSWORD)
except:
    print "Unable to connect to the database"

cur = conn.cursor()

def quitquit(num=0):
    cur.close()
    conn.close()
    sys.exit(num)

cur.execute("SELECT * FROM horde_groups;")
try:
    groupnumbers = cur.fetchall()
except psycopg2.ProgrammingError:
    print("table horde_groups empty")
    quitquit(1)

print("Group Numbers")
print(groupnumbers)
#hordedb=# SELECT * FROM horde_groups;
# group_uid |    group_name     | group_parents | group_email
#-----------+-------------------+---------------+-------------
#         2 | testgroup1        |               |

cur.execute("SELECT * FROM horde_groups_members;")
try:
    groupusers = cur.fetchall()
except psycopg2.ProgrammingError:
    print("table group_members is empty")
    quitquit(1)
    
print("Group Users")
print(groupusers)
#hordedb=# SELECT * FROM horde_groups_members;
# group_uid |         user_uid
#-----------+---------------------------
#         3 | test@test.local

# check if user needs to be removed from horde group
for i, user in enumerate(groupusers):
    groupnum = user[0]
    username = user[1]
    for item in groupnumbers:
        if item[0] == groupnum:
                groupname = item[1]
                break
							
    if username in groups[groupname]:
        continue
    else:
        SQL = "DELETE FROM horde_groups_members WHERE group_uid = %s AND user_uid = %s;"
        #                   print("user %s needs to be removed from horde group line %s"%(username, i))
        print "sql command: " + SQL%(groupnum, username)
        if not testmode:
            # print("removing %s %s"%(groupnum, username))
            cur.execute(SQL, (groupnum, username))
            try:
                print(cur.fetchall())
            except psycopg2.ProgrammingError:
                pass
conn.commit()

# fetch group number
for item in groupnumbers:
    print("Checking group %s"%item[1])
    groupname = item[1]

    if groupname not in GROUPSTOUPDATE:
        print("skipping group %s"%groupname)
        continue

    groupnumber = item[0]
    try:
        targetusers = groups[groupname]
    except IndexError:
        print("group names do not match at %s, exiting"%groupname)
        quitquit(1)

    # check if user is already in horde group
    for user in targetusers:
        for tuser in groupusers:
            if tuser[0] == groupnumber and tuser[1] == user:
                print("User %s is already in"%user)
                # user is already in the right table
                break
        #
        #   Otherwise add user here
        #
        #print("Need to add user %s to group %s"%(user, name))
        else:
            SQL = "INSERT INTO horde_groups_members VALUES (%s, %s);"
            print "sql command: " + SQL%(groupnumber, user)
            if not testmode:
                cur.execute(SQL, (groupnumber, user))
                try:
                    cur.fetchall()  
                except psycopg2.ProgrammingError:
                    # Empty response
                    pass
# commit changes to DB
conn.commit()

# close everything
quitquit()