#!/usr/bin/env python
# -*- coding: UTF-8 -*-
# Copyright 2008 VEB IT
# See COPYING for distribution information.
# $Id$

from ConfigParser import ConfigParser
from pyPgSQL import PgSQL

cff = file('/usr/local/etc/vmm.cfg', 'r')
cf = ConfigParser()
cf.readfp(cff)
cff.close()

dbh = PgSQL.connect(database=cf.get('database', 'name'),
        user=cf.get('database', 'user'), host=cf.get('database', 'host'),
        password=cf.get('database', 'pass'), client_encoding='utf8',
        unicode_results=True)
dbc = dbh.cursor()
dbc.execute("SET NAMES 'UTF8'")

# Create new tables
queries = ("CREATE SEQUENCE transport_id",
        """CREATE TABLE transport (
    tid         bigint NOT NULL DEFAULT nextval('transport_id'),
    transport   varchar(270) NOT NULL,
    CONSTRAINT pkey_transport PRIMARY KEY (tid),
    CONSTRAINT ukey_transport UNIQUE (transport)
)""",
        "INSERT INTO transport(transport) VALUES ('dovecot:')",
        "CREATE SEQUENCE maillocation_id",
        """CREATE TABLE maillocation(
    mid     bigint NOT NULL DEFAULT nextval('maillocation_id'),
    maillocation varchar(20) NOT NULL,
    CONSTRAINT pkey_maillocation PRIMARY KEY (mid),
    CONSTRAINT ukey_maillocation UNIQUE (maillocation)
)""",
        "INSERT INTO maillocation(maillocation) VALUES ('Maildir')"
        )
for query in queries:
    dbc.execute(query)
dbh.commit()


# fix table domains (Part I)
dbc.execute('ALTER TABLE domains ADD tid bigint NOT NULL DEFAULT 1')
dbh.commit()
dbc.execute("ALTER TABLE domains ADD CONSTRAINT fkey_domains_tid_transport \
 FOREIGN KEY (tid) REFERENCES transport (tid)")
dbh.commit()

dbc.execute("SELECT DISTINCT transport from domains \
 WHERE transport != 'dovecot:'")
res = dbc.fetchall()
if len(res):
    for trsp in res:
        dbc.execute("INSERT INTO transport(transport) VALUES (%s)", trsp[0])
    dbh.commit()

    dbc.execute("SELECT tid, transport FROM transport WHERE tid > 1")
    res = dbc.fetchall()
    for tid, trsp in res:
        dbc.execute("UPDATE domains SET tid = %s WHERE transport = %s", tid,
                trsp)
    dbh.commit()


# fix table users (Part I)
dbc.execute("ALTER TABLE users ADD mid bigint NOT NULL DEFAULT 1")
dbh.commit()
dbc.execute("ALTER TABLE users ADD tid bigint NOT NULL DEFAULT 1")
dbh.commit()
for service in ['smtp', 'pop3', 'imap', 'managesieve']:
    dbc.execute(
            "ALTER TABLE users ADD %s boolean NOT NULL DEFAULT TRUE" % service)
    dbh.commit()
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_mid_maillocation \
 FOREIGN KEY (mid) REFERENCES maillocation (mid)")
dbh.commit()
dbc.execute("ALTER TABLE users ADD CONSTRAINT fkey_users_tid_transport \
 FOREIGN KEY (tid) REFERENCES transport (tid)")
dbh.commit()

dbc.execute("SELECT DISTINCT mail FROM users WHERE mail != 'Maildir'")
res = dbc.fetchall()
if len(res):
    for mailloc in res:
        dbc.execute("INSERT INTO maillocation(maillocation) VALUES (%s)",
                mailloc[0])
    dbh.commit()

    dbc.execute("SELECT mid, maillocation FROM maillocation WHERE mid > 1")
    res = dbc.fetchall()
    for mid, mailloc in res:
        dbc.execute("UPDATE users SET mid = %s WHERE mail = %s", mid,
                maillocation)
    dbh.commit()

dbc.execute("SELECT uid FROM users WHERE disabled")
res = dbc.fetchall()
if len(res):
    for uid in res:
        dbc.execute("UPDATE users SET smtp = FALSE, pop3 = FALSE, imap = FALSE,\
 managesieve = FALSE WHERE uid = %s", uid[0])
    dbh.commit()

dbc.execute("SELECT gid, tid FROM domains")
res = dbc.fetchall()
for gid, tid in res:
    dbc.execute("UPDATE users SET tid = %s WHERE gid = %s", tid, gid)
dbh.commit()


# Update VIEW postfix_maildir
dbc.execute("""CREATE OR REPLACE VIEW postfix_maildir AS
     SELECT local_part || '@' || domains.domainname AS address,
     domains.domaindir||'/'||uid||'/'||maillocation.maillocation||'/' AS maildir
       FROM users
            LEFT JOIN domains USING (gid)
            LEFT JOIN maillocation USING (mid)""")
dbh.commit()

# Replace VIEW dovecot_user
dbc.execute("DROP VIEW dovecot_user")
dbh.commit()
dbc.execute("""CREATE OR REPLACE VIEW dovecot_user AS
    SELECT local_part || '@' || domains.domainname AS userid,
           uid, gid, domains.domaindir || '/' || uid AS home,
           '~/' || maillocation.maillocation AS mail
      FROM users
           LEFT JOIN domains USING (gid)
           LEFT JOIN maillocation USING (mid);""")
dbh.commit()

# Replace VIEW dovecot_password
dbc.execute("DROP VIEW dovecot_password")
dbh.commit()
dbc.execute("""CREATE OR REPLACE VIEW dovecot_password AS
    SELECT local_part || '@' || domains.domainname AS "user",
           passwd AS "password", smtp, pop3, imap, managesieve
      FROM users
           LEFT JOIN domains USING (gid)""")
dbh.commit()

# fix table users (Part II)
dbc.execute("ALTER TABLE users DROP home")
dbh.commit()
dbc.execute("ALTER TABLE users DROP mail")
dbh.commit()
dbc.execute("ALTER TABLE users DROP disabled")
dbh.commit()


# Replace VIEW postfix_transport
dbc.execute("DROP VIEW postfix_transport")
dbh.commit()
dbc.execute("""CREATE OR REPLACE VIEW postfix_transport AS
    SELECT local_part || '@' || domains.domainname AS address,
           transport.transport
      FROM users
           LEFT JOIN transport USING (tid)
           LEFT JOIN domains USING (gid)""")
dbh.commit()


# fix table domains (Part II)
dbc.execute("ALTER TABLE domains DROP transport")
dbh.commit()


# fix table alias
dbc.execute('ALTER TABLE alias DROP id')
dbh.commit()
dbc.execute('DROP SEQUENCE alias_id')
dbh.commit()


# fix table relocated
dbc.execute('ALTER TABLE relocated DROP id')
dbh.commit()
dbc.execute('DROP SEQUENCE relocated_id')
dbh.commit()


# add new VIEW vmm_domain_info
dbc.execute("""CREATE OR REPLACE VIEW vmm_domain_info AS
    SELECT gid, domainname, transport, domaindir, count(uid) AS accounts,
           aliases
      FROM domains
           LEFT JOIN transport USING (tid)
           LEFT JOIN users USING (gid)
           LEFT JOIN vmm_alias_count USING (gid)
  GROUP BY gid, domainname, transport, domaindir, aliases""")
dbh.commit()
dbh.close()