# -*- coding: utf-8 -*-
# Copyright (c) 2006 Nuxeo SAS <http://nuxeo.com>
# Authors : Tarek Ziadé <tziade@nuxeo.com>
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 as published
# by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
# 02111-1307, USA.
#
# $Id: sql.py 46848 2006-06-29 18:57:28Z ogrisel $
from sqlalchemy import *
from settings import SQLURI

_connector = create_engine(SQLURI)
_metadata = BoundMetaData(_connector)

lang = Table('classifier_lang', _metadata,
             Column('iso', String(3), primary_key=True),
             Column('user', String(40), primary_key=True))

word = Table('classifier_word', _metadata,
            Column('value', String(200), primary_key=True),
            Column('lang_iso', String(3), ForeignKey('classifier_lang.iso')),
            Column('count', Integer),
            Column('user', String(40), primary_key=True)
            )

category = Table('classifier_category', _metadata,
                 Column('name', String(50), primary_key=True),
                 Column('description', String(300)),
                 Column('user', String(40), primary_key=True))


word_category = Table('classifier_word_category', _metadata,
                 #Column('id', Integer, primary_key=True),
                 Column('word_value', String(200),
                        ForeignKey('classifier_word.value'), primary_key=True),
                 Column('count', Integer),
                 Column('category_name', String(50),
                        ForeignKey('classifier_category.name'), primary_key=True),
                 Column('user', String(40), primary_key=True))


tables = (lang, word, category, word_category)

for table in tables:
    try:
        table.create()
    except exceptions.SQLError:
        pass

class SQLStorage(object):

    def __init__(self, user):
        self._user = user

    #
    # categories
    #
    def list_categories(self):
        """ get categories list """
        # XXX hardcoded here, just two cats
        return [cat.name for cat in
                category.select(category.c.user==self._user).execute()]

    def add_category(self, name, description=''):
        """ give the name of the filter """
        if name in self.list_categories():
            return
        category.insert().execute(name=name, user=self._user,
                                  description=description)

    def del_category(self, name):
        """ remove a category """
        category.delete(and_(category.c.name==name,
                             category.c.user==self._user)).execute()

    #
    # langs
    #
    def list_languages(self):
        """ list available languages """
        return [item.iso for item in
                lang.select(lang.c.user==self._user).execute()]

    def add_language(self, iso):
        """ add a language """
        lang.insert().execute(iso=iso, user=self._user)

    def del_language(self, iso):
        """ remove a language """
        lines = lang.delete(and_(lang.c.iso==iso, lang.c.user==self._user))
        lines.execute()

    #
    # words
    #
    def word_categories(self, the_word, language=None):
        """list all categories for a given word in a given langugage"""
        if language is not None:
            the_word = word.select(and_(word.c.lang_iso==language,
                                        word.c.value==the_word,
                                        word.c.user==self._user)).execute()
        else:
            the_word = word.select(and_(word.c.user==self._user,
                                        word.c.value==the_word)).execute()

        the_word = the_word.fetchall()[0].value

        sel = word_category.select(and_(word_category.c.word_value==the_word,
                                        word.c.user==self._user))

        # XXX see how to distinct
        cats = []
        for line in sel.execute():
            if line.category_name not in cats:
                cats.append(line.category_name)

        return cats


    def list_words(self, language=None, complete=False):
        """ list all words, filtered by language if given """
        if language is not None:
            words = word.select(and_(word.c.lang_iso==language,
                                     word.c.user==self._user)).execute()
        else:
            words = word.select(word.c.user==self._user).execute()

        if not complete:
            return ((word.value, word.lang_iso) for word in words)
        else:
            return ((word.value, self._reprWord(word))
                    for word in words)

    def add_word(self, the_word, language, categories=()):
        """ add a word """
        categories = _tuplify(categories)
        #language = _tuplify(language)


        # getting existing word
        sel = word.select(and_(word.c.value==the_word,
                               word.c.user==self._user))
        selection = sel.execute().fetchone()
        cat_insert = word_category.insert()

        if selection is None:
            # new word, let's add it
            res = word.insert().execute(value=the_word, count=1,
                                        user=self._user,
                                        lang_iso=language)

            for category in categories:
                cat_insert.execute(word_value=the_word,
                                   category_name=category, count=1,
                                   user=self._user)
        else:
            # existing word, let's increment it
            res = word.update(and_(word.c.value==the_word,
                                   word.c.user==self._user))
            res.execute(count=selection.count+1)

            #sl = and_(word_category.c.word_value==the_word,
            #         word_category.c.user==self._user)
            #cat = word_category.select(sl)
            #cat = cat.execute().fetchall()

            #for old_cat in cat:
            #    if old_cat.category_name not in categories:
            #        name = old_cat.category_name
            #        sl = and_(word_category.c.category_name==name,
            #                  word_category.c.user==self._user,
            #                  word_category.c.word_value==the_word)

            #        word_category.delete(sl).execute()

            existing_cats = self.list_categories()
            for category in categories:
                # let's check if the category exists in category
                if category not in existing_cats:
                    self.add_category(category)

                sl = and_(word_category.c.category_name == category,
                          word_category.c.user==self._user,
                          word_category.c.word_value==the_word)
                cat = word_category.select(sl)

                cat = cat.execute().fetchone()
                if cat is None:
                    cat_insert.execute(word_value=the_word,
                                       category_name=category,
                                       count=1, user=self._user)

                else:
                    up = word_category.update(and_(word_category.c.user==cat.user,
                                   word_category.c.word_value==cat.word_value,
                                   word_category.c.category_name==cat.category_name))
                    up.execute(count=cat.count+1)


    def _reprWord(self, the_word):
        # XXX ugly code, will do sql joins here later
        word_value = the_word.value
        selection = and_(word_category.c.word_value == word_value,
                         word_category.c.user==self._user)

        catwords = word_category.select(selection).execute()

        word_sel =  and_(word.c.value == word_value,
                         word.c.user==self._user)

        langs = [the_word.lang_iso for the_word in
                 word.select(word_sel).execute()]

        cats = {}
        for catword in catwords:
            cats[catword.category_name] = catword.count

        return tuple(langs), cats, int(the_word.count)

    def get_word(self, the_word, language=None):
        """ get word infos """
        # TODO: handle language
        sl = and_(word.c.word_label==the_word,
                  word.c.user==self._user)

        results = word.select(sl)

        if results.count() == 0:
            return None
        return self._reprWord(results[0])

    def del_word(self, the_word, categories=None, language=None):
        """Remove a word"""
        sl = and_(word.c.value==the_word, word.c.user==self._user)
        the_word = word.select(sl).execute().fetchone()

        if the_word is None:
            return None

        # picking up categorized words to work with
        if categories is None:
            sl = word_category.c.word_value == the_word.value
            catwords = word_category.select(sl).execute(user=self._user)
        else:
            categories = _tuplify(categories)
            sl =  word_category.c.word_value == the_word.value
            sl2 =  word_category.c.category_name.in_(*categories)
            sl = and_(sl, sl2)
            catwords = word_category.select(sl).execute(user=self._user)

        # remove categorized words
        categories = []
        for catword in catwords:
            category_name = catword.category_name
            if category_name not in categories:
                categories.append(category_name)

            sl = and_(word_category.c.category_name==category_name,
                      word_category.c.user==self._user)

            if catword.count == 1:
                word_category.delete(sl).execute()
            else:
                rs = word_category.select(sl).execute().fetchone()
                rs_count = rs.count
                rs.close()
                if rs is not None:
                    sl2 = word_category.update(sl)
                    sl2.execute(count=rs_count-1, user=self._user)

        # remove empty categories
        for category in categories:
            sl = and_(word_category.c.category_name==category,
                      word_category.c.user==self._user)

            current_cat = word_category.select(sl).execute().fetchone()
            if current_cat is not None:
                if current_cat.count == 0:
                    current_cat.close()
                    word_category.delete(sl).execute()
        #else:
        #    # remove categories
        #    sl = and_(word_category.c.word_value==the_word.value,
        #              word_category.c.user==self._user)
        #    word_category.delete(sl).execute()

        # removing word if none use it
        sl = and_(word.c.value==the_word.value, word.c.user==self._user)
        if word.select(sl).execute().fetchone().count == 0:
            word.delete(sl).execute()

    def word_count(self, category=None, language=None):
        if category is None:
            if language is None:
                sl = word.c.user == self._user
                #return len(word.select(sl).execute().fetchall())
                return word.select(sl).count().execute().fetchone()[0]
            else:
                sl = and_(word.c.user == self._user,
                          word.c.lang_iso == language)
                return word.select(sl).count().execute().fetchone()[0]
                #return len(word.select(sl).execute().fetchall())
        else:
            categories = _tuplify(category)
            in_ = word_category.c.category_name.in_(*categories)
            sl = and_(in_, word_category.c.user == self._user)
            return word_category.select(sl).count().execute().fetchone()[0]
            #res = word_category.select(sl).execute()
            #return len(res.fetchall())


def _tuplify(element):
    if not isinstance(element, tuple):
        if isinstance(element, list):
            return tuple(element)
        else:
            return (element,)
    return element