from optparse import make_option
import psycopg2
import random

from django.conf import settings
from django.contrib.auth.models import User, Group
from django.core.management.base import BaseCommand, CommandError
from django.db import transaction
from django.template.defaultfilters import slugify

from docbow_project.docbow.models import MailingList, DocbowProfile, FileType
from docbow_project.pfwb.models import TabellioDocType


# Generate passwords using capitals and numbers, but no 1 or I, O or 0
__pwd_alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'
def create_password(pwd_length=8):
    password = ''.join([random.choice(__pwd_alphabet) 
        for x in range(pwd_length)])
    return password

def get_username(last_name, first_name):
    username = '%s.%s' % (slugify(first_name), slugify(last_name))
    return username


class Command(BaseCommand):

    @transaction.commit_on_success
    def handle(self, *args, **options):
        verbose = (options.get('verbosity') > 1)

        parl_list = MailingList.objects.get(id=settings.PARLEMENTAIRES_MAILING_ID)
        ministres_list = MailingList.objects.get(id=settings.MINISTRES_MAILING_ID)

        comppols = {}

        # connect to tabellio database
        conn = psycopg2.connect("host=%s dbname=%s user=%s password=%s" % (
                settings.TABELLIO_DBHOST,
                settings.TABELLIO_DBNAME,
                settings.TABELLIO_DBUSER,
                settings.TABELLIO_DBPASSWORD))
        cur = conn.cursor()

        # get list of persons that are neither deputy or ministre so they can
        # be disabled if they exists in docbow
        cur.execute('''SELECT t_pers.nom, t_pers.prenom
                         FROM t_pers
                        WHERE t_pers.st != 'S_PARL'
                          AND t_pers.st != 'S_MINISTRE'
                    ''')

        while True:
            t = cur.fetchone()
            if not t:
                break
            last_name, first_name = t

            username = get_username(last_name, first_name)

            try:
                user = User.objects.get(username=username)
            except User.DoesNotExist:
                # a user didn't exist for that person, fine.
                continue

            if not user.is_active:
                # the user was already disabled, ok.
                continue

            if verbose:
                print 'disabling', username

            # disable the user
            user.is_active = False
            user.save()

            # remove the user from lists
            if user in parl_list.members.all():
                parl_list.members.remove(user)
                parl_list.save()
            if user in ministres_list.members.all():
                ministres_list.members.remove(user)
                ministres_list.save()


        # get current deputies
        cur.execute('''SELECT t_pers.nom, t_pers.prenom, t_adresse.email, t_comppol.abbr
                         FROM t_pers, t_pershistoline, t_adresse, t_comppol
                        WHERE t_pers.st = 'S_PARL'
                          AND t_pers.prenom is not NULL
                          AND t_pershistoline.description = t_comppol.id
                          AND t_pers.id = t_pershistoline.pers
                          AND t_pershistoline.type = 'P_CMPL'
                          AND t_pershistoline.fin IS NULL
                          AND t_adresse.email IS NOT NULL
                          AND (t_adresse.id = t_pers.addrpriv
                               OR t_adresse.id = t_pers.addrprof1
                               OR t_adresse.id = t_pers.addrprof2)
                          AND t_adresse.courriel = true''')

        while True:
            t = cur.fetchone()
            if not t:
                break
            last_name, first_name, email, comppol = t

            username = get_username(last_name, first_name)

            user, created = User.objects.get_or_create(username=username)
            user.last_name = last_name
            user.first_name = first_name
            user.email = email
            user.is_active = True

            if created:
                # this is a new user, assign a password and print the info on
                # stdout
                pw = create_password()
                user.set_password(pw)
                if verbose:
                    print 'creating', username, 'with password', pw

            user.save()

            # create/update matching docbow profile
            profile, created = DocbowProfile.objects.get_or_create(user=user)
            profile.personal_email = email

            # update mailing lists
            if not user in parl_list.members.all():
                parl_list.members.add(user)
                parl_list.save()
            if user in ministres_list.members.all():
                ministres_list.members.remove(user)
                ministres_list.save()

            # keep track of its political group, to be used later
            if not comppol in comppols:
                comppols[comppol] = []
            comppols[comppol].append(user)

        # create mailing lists for political groups
        for comppol, members in comppols.items():
            maillist, created = MailingList.objects.get_or_create(
                    name=u'Appartenance politique - %s' % comppol)
            # remove members of the list that should no longer be in there
            for member in maillist.members.all():
                if member not in members:
                    maillist.members.remove(member)
            # add new members
            for member in members:
                if member not in maillist.members.all():
                    maillist.members.add(member)

        # get current ministres
        cur.execute('''SELECT t_pers.nom, t_pers.prenom, t_adresse.email
                         FROM t_pers, t_pershistoline, t_adresse
                        WHERE t_pers.st = 'S_MINISTRE'
                          AND t_pers.prenom is not NULL
                          AND t_pers.id = t_pershistoline.pers
                          AND t_pershistoline.type = 'M_MINT'
                          AND t_pershistoline.fin IS NULL
                          AND t_adresse.email IS NOT NULL
                          AND (t_adresse.id = t_pers.addrpriv
                               OR t_adresse.id = t_pers.addrprof1
                               OR t_adresse.id = t_pers.addrprof2)
                          AND t_adresse.courriel = true''')

        while True:
            t = cur.fetchone()
            if not t:
                break
            last_name, first_name, email = t

            username = get_username(last_name, first_name)

            user, created = User.objects.get_or_create(username=username)
            user.last_name = last_name
            user.first_name = first_name
            user.email = email
            user.is_active = True

            if created:
                # this is a new user, assign a password and print the info on
                # stdout
                pw = create_password()
                user.set_password(pw)
                if verbose:
                    print 'creating', username, 'with password', pw

            user.save()

            # create/update matching docbow profile
            profile, created = DocbowProfile.objects.get_or_create(user=user)
            profile.personal_email = email

            # update mailing lists
            if user in parl_list.members.all():
                parl_list.members.remove(user)
                parl_list.save()
            if not user in ministres_list.members.all():
                ministres_list.members.add(user)
                ministres_list.save()

        # get list of document types
        cur.execute('''SELECT id, descr
                         FROM t_typedoc''')

        while True:
            t = cur.fetchone()
            if not t:
                break
            id, descr = t
            if descr[0] == descr[0].lower():
                descr = descr[0].upper() + descr[1:]

            try:
                doctype = TabellioDocType.objects.get(tabellio_doc_type=id)
            except TabellioDocType.DoesNotExist:
                filetype = FileType()
                filetype.name = descr
                filetype.save()

                doctype = TabellioDocType()
                doctype.tabellio_doc_type = id
                doctype.filetype = filetype
                doctype.save()
            else:
                if doctype.filetype.name != descr:
                    doctype.filetype.name = descr
                    doctype.filetype.save()

        cur.close()
