<commit_before># -*- coding: utf-8 -*-
"""models including business logic"""

import pytz
import datetime
import sqlalchemy.types as ty
from pyramid_dogpile_cache import get_region

from sqlalchemy import (
    Table,
    ForeignKey,
    func,
    select,
    and_,
    or_,
    text,
    Column
, event)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    mapper,
    relationship
)

from sqlalchemy.dialects.postgresql import TIMESTAMP
from pytz.exceptions import UnknownTimeZoneError
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.orm.session import sessionmaker, Session
from zope.sqlalchemy.datamanager import ZopeTransactionExtension, mark_changed
from datetime import timedelta
from gengine import urlcache
import transaction
from _collections import defaultdict
from pytz import timezone
import hashlib
import warnings

from gengine.metadata import Base, DBSession

try:
    cache_general = get_region('general')
    cache_achievement_eval = get_region('achievement_eval')
    cache_achievements_by_user_for_today = get_region('achievements_by_user_for_today')
    cache_translations = get_region('translations')
except:
    from dogpile.cache import make_region

    cache_general = make_region().configure('dogpile.cache.memory')
    cache_achievement_eval = make_region().configure('dogpile.cache.memory')
    cache_achievements_by_user_for_today = make_region().configure('dogpile.cache.memory')
    cache_translations = make_region().configure('dogpile.cache.memory')
    
    warnings.warn("Warning: cache objects are in memory, are you creating docs?")

t_users = Table("users", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("timezone", ty.String(), nullable=False, default="UTC"),
    Column("country", ty.String(), nullable=True, default=None),
    Column("region", ty.String(), nullable=True, default=None),
    Column("city", ty.String(), nullable=True, default=None),
    Column('created_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow),
)

t_users_users = Table("users_users", Base.metadata,
    Column('from_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_groups = Table("groups", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
)

t_users_groups = Table("users_groups", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('group_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_achievementcategories = Table('achievementcategories', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements = Table('achievements', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column("achievementcategory_id", ty.Integer, ForeignKey("achievementcategories.id", ondelete="SET NULL"), index=True, nullable=True),
    Column('name', ty.String(255), nullable = False), #internal use
    Column('maxlevel',ty.Integer, nullable=False, default=1),
    Column('hidden',ty.Boolean, nullable=False, default=False), 
    Column('valid_start',ty.Date, nullable=True),
    Column('valid_end',ty.Date, nullable=True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("max_distance", ty.Integer, nullable=True),
    Column('priority', ty.Integer, index=True, default=0),
    Column('relevance',ty.Enum("friends","city","own", name="relevance_types"), default="own"),
)

t_goals = Table("goals", Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name_translation_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="RESTRICT"), nullable = False),
    Column('condition', ty.String(255), nullable=True),
    Column('evaluation',ty.Enum("immediately","daily","weekly","monthly","yearly","end", name="evaluation_types")),
    Column('timespan',ty.Integer, nullable=True),
    Column('group_by_key', ty.Boolean(), default=False),
    Column('group_by_dateformat', ty.String(255), nullable=True),
    Column('goal', ty.String(255), nullable=True),
    Column('operator', ty.Enum("geq","leq", name="goal_operators"), nullable=True),
    Column('maxmin', ty.Enum("max","min", name="goal_maxmin"), nullable=True, default="max"),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), nullable=False),
    Column('priority', ty.Integer, index=True, default=0),
) 

t_goal_evaluation_cache = Table("goal_evaluation_cache", Base.metadata,
    Column("goal_id", ty.Integer, ForeignKey("goals.id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("user_id", ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("achieved", ty.Boolean),
    Column("value", ty.Float),
)

t_variables = Table('variables', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False, index=True),
    Column('group', ty.Enum("year","month","week","day","timeslot","none", name="variable_group_types"), nullable = False, default="none"),
)

t_values = Table('values', Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('datetime', TIMESTAMP(timezone=True), primary_key = True, default=datetime.datetime.utcnow),
    Column('variable_id', ty.Integer, ForeignKey("variables.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('value', ty.Integer, nullable = False),
    Column('key', ty.String(100), primary_key=True, default=""),
)

t_properties = Table('properties', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
    Column('is_variable', ty.Boolean, nullable = False, default=False),
)

t_achievements_properties = Table('achievements_properties', Base.metadata,
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('property_id', ty.Integer, ForeignKey("properties.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('value', ty.String(255), nullable = True),
    Column('value_translation_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="RESTRICT"), nullable = True),
    Column('from_level', ty.Integer, nullable = False, default=0, primary_key = True),
)

t_rewards = Table('rewards', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements_rewards = Table('achievements_rewards', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), index = True, nullable=False),
    Column('reward_id', ty.Integer, ForeignKey("rewards.id", ondelete="CASCADE"), index = True, nullable=False),
    Column('value', ty.String(255), nullable = True),
    Column('value_translation_id', ty.Integer, ForeignKey("translationvariables.id"), nullable = True),
    Column('from_level', ty.Integer, nullable = False, default=1, index = True)
)

t_achievements_users = Table('achievements_users', Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id"), primary_key = True, index=True, nullable=False),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('level', ty.Integer, primary_key = True, default=1),
    Column('updated_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow),
)

t_requirements = Table('requirements', Base.metadata,
    Column('from_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
)

t_denials = Table('denials', Base.metadata,
    Column('from_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
)

t_languages = Table('languages', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('name', ty.String(255), nullable = False),
)

t_translationvariables = Table('translationvariables', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('name', ty.String(255), nullable = False),
)

t_translations = Table('translations', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('translationvariable_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="CASCADE"), nullable = False),
   Column('language_id', ty.Integer, ForeignKey("languages.id", ondelete="CASCADE"), nullable = False),
   Column('text', ty.Text(), nullable = False),
)

class ABase(object):
    """abstract base class which introduces a nice constructor for the model classes."""
    
    def __init__(self,*args,**kw):
        """ create a model object.
        
        pass attributes by using named parameters, e.g. name="foo", value=123
        """
        
        for k,v in kw.items():
            setattr(self, k, v)

class User(ABase):
    """A user participates in the gamification, i.e. can get achievements, rewards, participate in leaderbaord etc."""
    
    def __unicode__(self, *args, **kwargs):
        return "User %s" % (self.id,)
    
    def __init__(self, *args, **kw):
        """ create a user object
        
        Each user has a timezone and a location to support time- and geo-aware gamification.
        There is also a friends-relation for leaderboards and a groups-relation.  
        """
        ABase.__init__(self, *args, **kw)
    
    #TODO:Cache
    @classmethod
    def get_user(cls,user_id):
        return DBSession.execute(t_users.select().where(t_users.c.id==user_id)).fetchone()
    
    @classmethod
    def get_cache_expiration_time_for_today(cls,user):
        """return the seconds until the day of the user ends (timezone of the user).
        
        This is needed as achievements may be limited to a specific time (e.g. only during holidays)."""
        
        tzobj = pytz.timezone(user["timezone"])
        now = datetime.datetime.now(tzobj)
        today = now.replace(hour=0,minute=0,second=0,microsecond=0)
        tomorrow = today+timedelta(days=1)
        return int((tomorrow-today).total_seconds())

    @classmethod
    def set_infos(cls,user_id,lat,lng,timezone,country,region,city,friends, groups):
        """set the user's metadata like friends,location and timezone"""
        
        
        new_friends_set = set(friends)
        existing_users_set = {x["id"] for x in DBSession.execute(select([t_users.c.id]).where(t_users.c.id.in_([user_id,]+friends))).fetchall()}
        existing_friends = {x["to_id"] for x in DBSession.execute(select([t_users_users.c.to_id]).where(t_users_users.c.from_id==user_id)).fetchall()}
        friends_to_create = (new_friends_set-existing_users_set-{user_id,})
        friends_to_append = (new_friends_set-existing_friends)
        friends_to_delete = (existing_friends-new_friends_set)
        
        new_groups_set = set(groups)
        existing_groups_set = {x["id"] for x in DBSession.execute(select([t_groups.c.id]).where(t_groups.c.id.in_(groups))).fetchall()}
        existing_groups_of_user = {x["group_id"] for x in DBSession.execute(select([t_users_groups.c.group_id]).where(t_users_groups.c.user_id==user_id)).fetchall()}
        groups_to_create = (new_groups_set-existing_groups_set)
        groups_to_append = (new_groups_set-existing_groups_of_user)
        groups_to_delete = (existing_groups_of_user-new_groups_set)
        
        #add or select user
        if user_id in existing_users_set:
            user = DBSession.query(User).filter_by(id=user_id).first()
        else:
            user = User()
        
        user.id = user_id
        user.lat = lat
        user.lng = lng
        user.timezone = timezone
        user.country = country
        user.region = region
        user.city = city
        
        DBSession.add(user)
        DBSession.flush()
        
        #FRIENDS
        
        #insert missing friends in user table
        if len(friends_to_create)>0:
            update_connection().execute(t_users.insert(), [{"id":f} for f in friends_to_create])
                
        #delete old friends
        if len(friends_to_delete)>0:
            update_connection().execute(t_users_users.delete().where(and_(t_users_users.c.from_id==user_id,
                                                            t_users_users.c.to_id.in_(friends_to_delete))))
        
        #insert missing friends
        if len(friends_to_append)>0:
            update_connection().execute(t_users_users.insert(),[{"from_id":user_id,"to_id":f} for f in friends_to_append])
            
        #GROUPS
            
        #insert missing groups in group table
        if len(groups_to_create)>0:
            update_connection().execute(t_groups.insert(), [{"id":f} for f in groups_to_create])
                
        #delete old groups of user
        if len(groups_to_delete)>0:
            update_connection().execute(t_users_groups.delete().where(and_(t_users_groups.c.user_id==user_id,
                                                                          t_users_groups.c.group_id.in_(groups_to_delete))))
        
        #insert missing groups of user
        if len(groups_to_append)>0:
            update_connection().execute(t_users_groups.insert(),[{"user_id":user_id,"group_id":f} for f in groups_to_append])
        
    @classmethod
    def delete_user(cls,user_id):
        """delete a user including all dependencies."""
        update_connection().execute(t_achievements_users.delete().where(t_achievements_users.c.user_id==user_id))
        update_connection().execute(t_goal_evaluation_cache.delete().where(t_goal_evaluation_cache.c.user_id==user_id))
        update_connection().execute(t_users_users.delete().where(t_users_users.c.to_id==user_id))
        update_connection().execute(t_users_users.delete().where(t_users_users.c.from_id==user_id))
        update_connection().execute(t_users_groups.delete().where(t_users_groups.c.user_id==user_id))
        update_connection().execute(t_values.delete().where(t_values.c.user_id==user_id))
        update_connection().execute(t_users.delete().where(t_users.c.id==user_id))
        
class Group(ABase):
    def __unicode__(self, *args, **kwargs):
        return "(ID: %s)" % (self.id,)

class Variable(ABase):
    """A Variable is anything that should be meassured in your application and be used in :class:`.Goal`.
       
       To save database rows, variables may be grouped by time:
       group needs to be set to "year","month","week","day","timeslot" or "none" (default)
    """
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_variable_by_name(cls,name):
        return DBSession.execute(t_variables.select(t_variables.c.name==name)).fetchone()
    
    @classmethod
    def get_datetime_for_tz_and_group(cls,tz,group):
        """get the datetime of the current row, needed for grouping
        
        when "timezone" is used as a group name, the values are grouped to the nearest time in (09:00, 12:00, 15:00, 18:00, 21:00)
        (timezone to use is given as parameter)
        """
        tzobj = pytz.timezone(tz)
        now = datetime.datetime.now(tzobj)
        #now = now.replace(tzinfo=pytz.utc)
        
        t = None
        if group=="year":
            t = now.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0 )
        elif group=="month":
            t = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        elif group=="week":
            t = now-datetime.timedelta(days=now.weekday())
            t = t.replace(hour=0, minute=0, second=0, microsecond=0)
        elif group=="day":
            t = now.replace(hour=0, minute=0, second=0, microsecond=0)
        elif group=="timeslot":
            
            tslist = [
                (now-datetime.timedelta(days=1)).replace(hour=21, minute=0, second=0, microsecond=0),
                now.replace(hour=9, minute=0, second=0, microsecond=0),
                now.replace(hour=12, minute=0, second=0, microsecond=0),
                now.replace(hour=15, minute=0, second=0, microsecond=0),
                now.replace(hour=18, minute=0, second=0, microsecond=0),
                now.replace(hour=21, minute=0, second=0, microsecond=0),
                (now+datetime.timedelta(days=1)).replace(hour=9, minute=0, second=0, microsecond=0),
            ]
            
            t = min(tslist,key=lambda date : abs(dt-date))
        elif group=="none":
            t = now
        else:
            #return datetime.datetime.max.replace
            return datetime.datetime(year=2000,month=1,day=1,hour=0,minute=0,second=0,microsecond=0).replace(tzinfo=pytz.utc)
        
        return t.astimezone(tzobj)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def map_variables_to_rules(cls):
        """return a map from variable_ids to {"goal":goal_obj,"achievement":achievement_obj} dictionaries.
        
        Used to know which goals need to be reevaluated after a value for the variable changes.
        """
        q = select([t_goals,t_variables.c.id.label("variable_id")])\
            .where(or_(t_goals.c.condition.ilike(func.concat('%"',t_variables.c.name,'"%')),
                       t_goals.c.condition.ilike(func.concat("%'",t_variables.c.name,"'%"))))
        m={}
        for row in DBSession.execute(q).fetchall():
            if not m.has_key(row["variable_id"]):
                m[row["variable_id"]] = []
            
            m[row["variable_id"]].append({"goal":row,"achievement":Achievement.get_achievement(row["achievement_id"])})
            
        return m
    
    @classmethod
    def invalidate_caches_for_variable_and_user(cls,variable_id,user_id):
        """ invalidate the relevant caches for this user and all relevant users with concerned leaderboards"""
        goalsandachievements = cls.map_variables_to_rules().get(variable_id,[])

        Goal.clear_goal_caches(user_id, [entry["goal"]["id"] for entry in goalsandachievements])
        for entry in goalsandachievements:
            Achievement.invalidate_evaluate_cache(user_id,entry["achievement"])
             
    
class Value(ABase):
    """A Value describes the relation of the user to a variable.
    
    (e.g. it counts the occurences of the "events" which the variable represents) """
    
    @classmethod
    def increase_value(cls, variable_name, user, value, key):
        """increase the value of the variable for the user.
        
        In addition to the variable_name there may be an application-specific key which can be used in your :class:`.Goal` definitions
        """
        
        user_id = user["id"]
        tz = user["timezone"]
        
        variable = Variable.get_variable_by_name(variable_name)
        datetime = Variable.get_datetime_for_tz_and_group(tz,variable["group"])
        
        condition = and_(t_values.c.datetime==datetime,
                         t_values.c.variable_id==variable["id"],
                         t_values.c.user_id==user_id,
                         t_values.c.key==str(key))
        
        current_value = DBSession.execute(select([t_values.c.value,]).where(condition)).scalar()
        
        if current_value is not None:
            update_connection().execute(t_values.update(condition, values={"value":current_value+value}))
        else:
            update_connection().execute(t_values.insert({"datetime":datetime,
                                           "variable_id":variable["id"],
                                           "user_id" : user_id,
                                           "key" : str(key),
                                           "value":value}))

        Variable.invalidate_caches_for_variable_and_user(variable["id"],user["id"])
        
class AchievementCategory(ABase):
    """A category for grouping achievement types"""
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievementcategory(cls,achievementcategory_id):
        return DBSession.execute(t_achievementcategories.select().where(t_achievementcategories.c.id==achievementcategory_id)).fetchone()
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
class Achievement(ABase):
    """A collection of goals which has multiple :class:`Property` and :class:`Reward`."""
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievement(cls,achievement_id):
        return DBSession.execute(t_achievements.select().where(t_achievements.c.id==achievement_id)).fetchone()
    
    @classmethod
    def get_achievements_by_user_for_today(cls,user):
        """Returns all achievements that are relevant for the user today.
        
        This is needed as achievements may be limited to a specific time (e.g. only during holidays)
        """ 
        
        def generate_achievements_by_user_for_today():
            today = datetime.date.today()
            by_loc = {x["id"] : x["distance"] for x in cls.get_achievements_by_location(coords(user))}
            by_date = cls.get_achievements_by_date(today)
            
            def update(arr,distance):
                arr["distance"]=distance
                return arr
            
            return [update(arr,by_loc[arr["id"]]) for arr in by_date if by_loc.has_key(arr["id"])]
        
        key = str(user["id"])
        expiration_time = User.get_cache_expiration_time_for_today(user)
        
        return cache_achievements_by_user_for_today.get_or_create(key,generate_achievements_by_user_for_today, expiration_time=expiration_time)
        
    #We need to fetch all achievement data in one of these methods -> by_date is just queried once a date
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievements_by_location(cls,latlng):
        """return achievements which are valid in that location.""" 
        #TODO: invalidate automatically when achievement in user's range is modified
        distance = calc_distance(latlng, (t_achievements.c.lat, t_achievements.c.lng)).label("distance")
        
        q = select([t_achievements.c.id,
                    distance])\
            .where(or_(and_(t_achievements.c.lat==None,t_achievements.c.lng==None),
                        distance < t_achievements.c.max_distance))
        return [dict(x.items()) for x in DBSession.execute(q).fetchall()]
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievements_by_date(cls,date):
        """return achievements which are valid at that date"""
        q = t_achievements.select().where(and_(or_(t_achievements.c.valid_start==None,
                                                          t_achievements.c.valid_start<=date),
                                               or_(t_achievements.c.valid_end==None,
                                                          t_achievements.c.valid_end>=date)
                                               ))
        return [dict(x.items()) for x in DBSession.execute(q).fetchall()]
    
    #TODO:CACHE
    @classmethod
    def get_relevant_users_by_achievement_and_user(cls,achievement,user_id):
        """return all relevant other users for the leaderboard. 
        
        dependes on the "relevance" attribute of the achivement, can be "friends" or "city" (city is still a todo)
        """
        # this is needed to compute the leaderboards
        users=[user_id,] 
        if achievement["relevance"]=="city":
            #TODO
            pass
        elif achievement["relevance"]=="friends":
            users += [x["to_id"] for x in DBSession.execute(select([t_users_users.c.to_id,], t_users_users.c.from_id==user_id)).fetchall()]
        return set(users)
    
    #TODO:CACHE
    @classmethod
    def get_relevant_users_by_achievement_and_user_reverse(cls,achievement,user_id):
        """return all users which have this user as friends and are relevant for this achievement.
        
        the reversed version is needed to know in whose contact list the user is. when the user's value is updated, all the leaderboards of these users need to be regenerated"""
        users=[user_id,] 
        if achievement["relevance"]=="city":
            #TODO
            pass
        elif achievement["relevance"]=="friends":
            users += [x["from_id"] for x in DBSession.execute(select([t_users_users.c.from_id,], t_users_users.c.to_id==user_id)).fetchall()]
        return set(users)
    
    #TODO:CACHE
    @classmethod
    def get_level(cls, user_id, achievement_id):
        """get the current level of the user for this achievement."""
        
        q = select([t_achievements_users.c.level,
                    t_achievements_users.c.updated_at],
                       and_(t_achievements_users.c.user_id==user_id,
                            t_achievements_users.c.achievement_id==achievement_id)).order_by(t_achievements_users.c.level.desc())
        return DBSession.execute(q).fetchall()
    
    @classmethod
    def get_level_int(cls,user_id,achievement_id):
        """get the current level of the user for this achievement as int (0 if the user does not have this achievement)"""
        lvls = Achievement.get_level(user_id, achievement_id)
        
        if not lvls:
            return 0
        else:
            return lvls[0]["level"]
    
    @classmethod
    def basic_output(cls,achievement,goals,include_levels=True,
                     max_level_included=None):
        """construct the basic output structure for the achievement."""
        
        achievementcategory = None
        if achievement["achievementcategory_id"]!=None:
            achievementcategory = AchievementCategory.get_achievementcategory(achievement["achievementcategory_id"])
        
        out = {
            "id" : achievement["id"],
            "internal_name" : achievement["name"],
            "maxlevel" : achievement["maxlevel"],
            "priority" : achievement["priority"],
            "hidden" : achievement["hidden"],
            "achievementcategory" : achievementcategory["name"] if achievementcategory!=None else ""
            #"updated_at" : combine_updated_at([achievement["updated_at"],]),
        }
        
        if include_levels:
            levellimit = achievement["maxlevel"]
            if max_level_included:
                max_level_included = min(max_level_included,levellimit) 
                
            out["levels"] = {
                str(i) : {
                    "level" : i,
                    "goals" : {
                        str(g["id"]) : {
                            "goal_id" : g["id"],
                            "goal_name" : Translation.trs(g["name_translation_id"], {"level":i}),
                            "goal_goal" : eval_formular(g["goal"], {"level":i}),
                            #"updated_at" : g["updated_at"]
                        } for g in goals},
                    "rewards" : {str(r["id"]) : {
                        "id" : r["id"],
                        "reward_id" : r["reward_id"],
                        "name" : r["name"],
                        "value" : eval_formular(r["value"], {"level":i}),
                        "value_translated" : Translation.trs(r["value_translation_id"], {"level":i}),
                    } for r in Achievement.get_rewards(achievement["id"],i)},
                    "properties" : {str(r["property_id"]) : {
                        "property_id" : r["property_id"],
                        "name" : r["name"],
                        "value" : eval_formular(r["value"], {"level":i}),
                        "value_translated" : Translation.trs(r["value_translation_id"], {"level":i}),
                    } for r in Achievement.get_properties(achievement["id"],i)}
            } for i in range(0,max_level_included+1)}
        return out
    
    @classmethod
    def evaluate(cls, user, achievement_id):
        """evaluate the achievement including all its subgoals for the user.
        <commit_msg>p.goal in goal names<commit_after># -*- coding: utf-8 -*-
"""models including business logic"""

import pytz
import datetime
import sqlalchemy.types as ty
from pyramid_dogpile_cache import get_region

from sqlalchemy import (
    Table,
    ForeignKey,
    func,
    select,
    and_,
    or_,
    text,
    Column
, event)

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (
    mapper,
    relationship
)

from sqlalchemy.dialects.postgresql import TIMESTAMP
from pytz.exceptions import UnknownTimeZoneError
from sqlalchemy.orm.scoping import scoped_session
from sqlalchemy.orm.session import sessionmaker, Session
from zope.sqlalchemy.datamanager import ZopeTransactionExtension, mark_changed
from datetime import timedelta
from gengine import urlcache
import transaction
from _collections import defaultdict
from pytz import timezone
import hashlib
import warnings

from gengine.metadata import Base, DBSession

try:
    cache_general = get_region('general')
    cache_achievement_eval = get_region('achievement_eval')
    cache_achievements_by_user_for_today = get_region('achievements_by_user_for_today')
    cache_translations = get_region('translations')
except:
    from dogpile.cache import make_region

    cache_general = make_region().configure('dogpile.cache.memory')
    cache_achievement_eval = make_region().configure('dogpile.cache.memory')
    cache_achievements_by_user_for_today = make_region().configure('dogpile.cache.memory')
    cache_translations = make_region().configure('dogpile.cache.memory')
    
    warnings.warn("Warning: cache objects are in memory, are you creating docs?")

t_users = Table("users", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("timezone", ty.String(), nullable=False, default="UTC"),
    Column("country", ty.String(), nullable=True, default=None),
    Column("region", ty.String(), nullable=True, default=None),
    Column("city", ty.String(), nullable=True, default=None),
    Column('created_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow),
)

t_users_users = Table("users_users", Base.metadata,
    Column('from_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_groups = Table("groups", Base.metadata,
    Column('id', ty.BigInteger, primary_key = True),
)

t_users_groups = Table("users_groups", Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('group_id', ty.BigInteger, ForeignKey("groups.id", ondelete="CASCADE"), primary_key = True, nullable=False)
)

t_achievementcategories = Table('achievementcategories', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements = Table('achievements', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column("achievementcategory_id", ty.Integer, ForeignKey("achievementcategories.id", ondelete="SET NULL"), index=True, nullable=True),
    Column('name', ty.String(255), nullable = False), #internal use
    Column('maxlevel',ty.Integer, nullable=False, default=1),
    Column('hidden',ty.Boolean, nullable=False, default=False), 
    Column('valid_start',ty.Date, nullable=True),
    Column('valid_end',ty.Date, nullable=True),
    Column("lat", ty.Float(Precision=64), nullable=True),
    Column("lng", ty.Float(Precision=64), nullable=True),
    Column("max_distance", ty.Integer, nullable=True),
    Column('priority', ty.Integer, index=True, default=0),
    Column('relevance',ty.Enum("friends","city","own", name="relevance_types"), default="own"),
)

t_goals = Table("goals", Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name_translation_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="RESTRICT"), nullable = False),
    Column('condition', ty.String(255), nullable=True),
    Column('evaluation',ty.Enum("immediately","daily","weekly","monthly","yearly","end", name="evaluation_types")),
    Column('timespan',ty.Integer, nullable=True),
    Column('group_by_key', ty.Boolean(), default=False),
    Column('group_by_dateformat', ty.String(255), nullable=True),
    Column('goal', ty.String(255), nullable=True),
    Column('operator', ty.Enum("geq","leq", name="goal_operators"), nullable=True),
    Column('maxmin', ty.Enum("max","min", name="goal_maxmin"), nullable=True, default="max"),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), nullable=False),
    Column('priority', ty.Integer, index=True, default=0),
) 

t_goal_evaluation_cache = Table("goal_evaluation_cache", Base.metadata,
    Column("goal_id", ty.Integer, ForeignKey("goals.id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("user_id", ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key=True, nullable=False),
    Column("achieved", ty.Boolean),
    Column("value", ty.Float),
)

t_variables = Table('variables', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False, index=True),
    Column('group', ty.Enum("year","month","week","day","timeslot","none", name="variable_group_types"), nullable = False, default="none"),
)

t_values = Table('values', Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('datetime', TIMESTAMP(timezone=True), primary_key = True, default=datetime.datetime.utcnow),
    Column('variable_id', ty.Integer, ForeignKey("variables.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('value', ty.Integer, nullable = False),
    Column('key', ty.String(100), primary_key=True, default=""),
)

t_properties = Table('properties', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
    Column('is_variable', ty.Boolean, nullable = False, default=False),
)

t_achievements_properties = Table('achievements_properties', Base.metadata,
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('property_id', ty.Integer, ForeignKey("properties.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('value', ty.String(255), nullable = True),
    Column('value_translation_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="RESTRICT"), nullable = True),
    Column('from_level', ty.Integer, nullable = False, default=0, primary_key = True),
)

t_rewards = Table('rewards', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('name', ty.String(255), nullable = False),
)

t_achievements_rewards = Table('achievements_rewards', Base.metadata,
    Column('id', ty.Integer, primary_key = True),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), index = True, nullable=False),
    Column('reward_id', ty.Integer, ForeignKey("rewards.id", ondelete="CASCADE"), index = True, nullable=False),
    Column('value', ty.String(255), nullable = True),
    Column('value_translation_id', ty.Integer, ForeignKey("translationvariables.id"), nullable = True),
    Column('from_level', ty.Integer, nullable = False, default=1, index = True)
)

t_achievements_users = Table('achievements_users', Base.metadata,
    Column('user_id', ty.BigInteger, ForeignKey("users.id"), primary_key = True, index=True, nullable=False),
    Column('achievement_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('level', ty.Integer, primary_key = True, default=1),
    Column('updated_at', ty.DateTime, nullable = False, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow),
)

t_requirements = Table('requirements', Base.metadata,
    Column('from_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
)

t_denials = Table('denials', Base.metadata,
    Column('from_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
    Column('to_id', ty.Integer, ForeignKey("achievements.id", ondelete="CASCADE"), primary_key = True, nullable=False),
)

t_languages = Table('languages', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('name', ty.String(255), nullable = False),
)

t_translationvariables = Table('translationvariables', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('name', ty.String(255), nullable = False),
)

t_translations = Table('translations', Base.metadata,
   Column('id', ty.Integer, primary_key = True),
   Column('translationvariable_id', ty.Integer, ForeignKey("translationvariables.id", ondelete="CASCADE"), nullable = False),
   Column('language_id', ty.Integer, ForeignKey("languages.id", ondelete="CASCADE"), nullable = False),
   Column('text', ty.Text(), nullable = False),
)

class ABase(object):
    """abstract base class which introduces a nice constructor for the model classes."""
    
    def __init__(self,*args,**kw):
        """ create a model object.
        
        pass attributes by using named parameters, e.g. name="foo", value=123
        """
        
        for k,v in kw.items():
            setattr(self, k, v)

class User(ABase):
    """A user participates in the gamification, i.e. can get achievements, rewards, participate in leaderbaord etc."""
    
    def __unicode__(self, *args, **kwargs):
        return "User %s" % (self.id,)
    
    def __init__(self, *args, **kw):
        """ create a user object
        
        Each user has a timezone and a location to support time- and geo-aware gamification.
        There is also a friends-relation for leaderboards and a groups-relation.  
        """
        ABase.__init__(self, *args, **kw)
    
    #TODO:Cache
    @classmethod
    def get_user(cls,user_id):
        return DBSession.execute(t_users.select().where(t_users.c.id==user_id)).fetchone()
    
    @classmethod
    def get_cache_expiration_time_for_today(cls,user):
        """return the seconds until the day of the user ends (timezone of the user).
        
        This is needed as achievements may be limited to a specific time (e.g. only during holidays)."""
        
        tzobj = pytz.timezone(user["timezone"])
        now = datetime.datetime.now(tzobj)
        today = now.replace(hour=0,minute=0,second=0,microsecond=0)
        tomorrow = today+timedelta(days=1)
        return int((tomorrow-today).total_seconds())

    @classmethod
    def set_infos(cls,user_id,lat,lng,timezone,country,region,city,friends, groups):
        """set the user's metadata like friends,location and timezone"""
        
        
        new_friends_set = set(friends)
        existing_users_set = {x["id"] for x in DBSession.execute(select([t_users.c.id]).where(t_users.c.id.in_([user_id,]+friends))).fetchall()}
        existing_friends = {x["to_id"] for x in DBSession.execute(select([t_users_users.c.to_id]).where(t_users_users.c.from_id==user_id)).fetchall()}
        friends_to_create = (new_friends_set-existing_users_set-{user_id,})
        friends_to_append = (new_friends_set-existing_friends)
        friends_to_delete = (existing_friends-new_friends_set)
        
        new_groups_set = set(groups)
        existing_groups_set = {x["id"] for x in DBSession.execute(select([t_groups.c.id]).where(t_groups.c.id.in_(groups))).fetchall()}
        existing_groups_of_user = {x["group_id"] for x in DBSession.execute(select([t_users_groups.c.group_id]).where(t_users_groups.c.user_id==user_id)).fetchall()}
        groups_to_create = (new_groups_set-existing_groups_set)
        groups_to_append = (new_groups_set-existing_groups_of_user)
        groups_to_delete = (existing_groups_of_user-new_groups_set)
        
        #add or select user
        if user_id in existing_users_set:
            user = DBSession.query(User).filter_by(id=user_id).first()
        else:
            user = User()
        
        user.id = user_id
        user.lat = lat
        user.lng = lng
        user.timezone = timezone
        user.country = country
        user.region = region
        user.city = city
        
        DBSession.add(user)
        DBSession.flush()
        
        #FRIENDS
        
        #insert missing friends in user table
        if len(friends_to_create)>0:
            update_connection().execute(t_users.insert(), [{"id":f} for f in friends_to_create])
                
        #delete old friends
        if len(friends_to_delete)>0:
            update_connection().execute(t_users_users.delete().where(and_(t_users_users.c.from_id==user_id,
                                                            t_users_users.c.to_id.in_(friends_to_delete))))
        
        #insert missing friends
        if len(friends_to_append)>0:
            update_connection().execute(t_users_users.insert(),[{"from_id":user_id,"to_id":f} for f in friends_to_append])
            
        #GROUPS
            
        #insert missing groups in group table
        if len(groups_to_create)>0:
            update_connection().execute(t_groups.insert(), [{"id":f} for f in groups_to_create])
                
        #delete old groups of user
        if len(groups_to_delete)>0:
            update_connection().execute(t_users_groups.delete().where(and_(t_users_groups.c.user_id==user_id,
                                                                          t_users_groups.c.group_id.in_(groups_to_delete))))
        
        #insert missing groups of user
        if len(groups_to_append)>0:
            update_connection().execute(t_users_groups.insert(),[{"user_id":user_id,"group_id":f} for f in groups_to_append])
        
    @classmethod
    def delete_user(cls,user_id):
        """delete a user including all dependencies."""
        update_connection().execute(t_achievements_users.delete().where(t_achievements_users.c.user_id==user_id))
        update_connection().execute(t_goal_evaluation_cache.delete().where(t_goal_evaluation_cache.c.user_id==user_id))
        update_connection().execute(t_users_users.delete().where(t_users_users.c.to_id==user_id))
        update_connection().execute(t_users_users.delete().where(t_users_users.c.from_id==user_id))
        update_connection().execute(t_users_groups.delete().where(t_users_groups.c.user_id==user_id))
        update_connection().execute(t_values.delete().where(t_values.c.user_id==user_id))
        update_connection().execute(t_users.delete().where(t_users.c.id==user_id))
        
class Group(ABase):
    def __unicode__(self, *args, **kwargs):
        return "(ID: %s)" % (self.id,)

class Variable(ABase):
    """A Variable is anything that should be meassured in your application and be used in :class:`.Goal`.
       
       To save database rows, variables may be grouped by time:
       group needs to be set to "year","month","week","day","timeslot" or "none" (default)
    """
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_variable_by_name(cls,name):
        return DBSession.execute(t_variables.select(t_variables.c.name==name)).fetchone()
    
    @classmethod
    def get_datetime_for_tz_and_group(cls,tz,group):
        """get the datetime of the current row, needed for grouping
        
        when "timezone" is used as a group name, the values are grouped to the nearest time in (09:00, 12:00, 15:00, 18:00, 21:00)
        (timezone to use is given as parameter)
        """
        tzobj = pytz.timezone(tz)
        now = datetime.datetime.now(tzobj)
        #now = now.replace(tzinfo=pytz.utc)
        
        t = None
        if group=="year":
            t = now.replace(month=1, day=1, hour=0, minute=0, second=0, microsecond=0 )
        elif group=="month":
            t = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        elif group=="week":
            t = now-datetime.timedelta(days=now.weekday())
            t = t.replace(hour=0, minute=0, second=0, microsecond=0)
        elif group=="day":
            t = now.replace(hour=0, minute=0, second=0, microsecond=0)
        elif group=="timeslot":
            
            tslist = [
                (now-datetime.timedelta(days=1)).replace(hour=21, minute=0, second=0, microsecond=0),
                now.replace(hour=9, minute=0, second=0, microsecond=0),
                now.replace(hour=12, minute=0, second=0, microsecond=0),
                now.replace(hour=15, minute=0, second=0, microsecond=0),
                now.replace(hour=18, minute=0, second=0, microsecond=0),
                now.replace(hour=21, minute=0, second=0, microsecond=0),
                (now+datetime.timedelta(days=1)).replace(hour=9, minute=0, second=0, microsecond=0),
            ]
            
            t = min(tslist,key=lambda date : abs(dt-date))
        elif group=="none":
            t = now
        else:
            #return datetime.datetime.max.replace
            return datetime.datetime(year=2000,month=1,day=1,hour=0,minute=0,second=0,microsecond=0).replace(tzinfo=pytz.utc)
        
        return t.astimezone(tzobj)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def map_variables_to_rules(cls):
        """return a map from variable_ids to {"goal":goal_obj,"achievement":achievement_obj} dictionaries.
        
        Used to know which goals need to be reevaluated after a value for the variable changes.
        """
        q = select([t_goals,t_variables.c.id.label("variable_id")])\
            .where(or_(t_goals.c.condition.ilike(func.concat('%"',t_variables.c.name,'"%')),
                       t_goals.c.condition.ilike(func.concat("%'",t_variables.c.name,"'%"))))
        m={}
        for row in DBSession.execute(q).fetchall():
            if not m.has_key(row["variable_id"]):
                m[row["variable_id"]] = []
            
            m[row["variable_id"]].append({"goal":row,"achievement":Achievement.get_achievement(row["achievement_id"])})
            
        return m
    
    @classmethod
    def invalidate_caches_for_variable_and_user(cls,variable_id,user_id):
        """ invalidate the relevant caches for this user and all relevant users with concerned leaderboards"""
        goalsandachievements = cls.map_variables_to_rules().get(variable_id,[])

        Goal.clear_goal_caches(user_id, [entry["goal"]["id"] for entry in goalsandachievements])
        for entry in goalsandachievements:
            Achievement.invalidate_evaluate_cache(user_id,entry["achievement"])
             
    
class Value(ABase):
    """A Value describes the relation of the user to a variable.
    
    (e.g. it counts the occurences of the "events" which the variable represents) """
    
    @classmethod
    def increase_value(cls, variable_name, user, value, key):
        """increase the value of the variable for the user.
        
        In addition to the variable_name there may be an application-specific key which can be used in your :class:`.Goal` definitions
        """
        
        user_id = user["id"]
        tz = user["timezone"]
        
        variable = Variable.get_variable_by_name(variable_name)
        datetime = Variable.get_datetime_for_tz_and_group(tz,variable["group"])
        
        condition = and_(t_values.c.datetime==datetime,
                         t_values.c.variable_id==variable["id"],
                         t_values.c.user_id==user_id,
                         t_values.c.key==str(key))
        
        current_value = DBSession.execute(select([t_values.c.value,]).where(condition)).scalar()
        
        if current_value is not None:
            update_connection().execute(t_values.update(condition, values={"value":current_value+value}))
        else:
            update_connection().execute(t_values.insert({"datetime":datetime,
                                           "variable_id":variable["id"],
                                           "user_id" : user_id,
                                           "key" : str(key),
                                           "value":value}))

        Variable.invalidate_caches_for_variable_and_user(variable["id"],user["id"])
        
class AchievementCategory(ABase):
    """A category for grouping achievement types"""
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievementcategory(cls,achievementcategory_id):
        return DBSession.execute(t_achievementcategories.select().where(t_achievementcategories.c.id==achievementcategory_id)).fetchone()
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
class Achievement(ABase):
    """A collection of goals which has multiple :class:`Property` and :class:`Reward`."""
    
    def __unicode__(self, *args, **kwargs):
        return self.name + " (ID: %s)" % (self.id,)
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievement(cls,achievement_id):
        return DBSession.execute(t_achievements.select().where(t_achievements.c.id==achievement_id)).fetchone()
    
    @classmethod
    def get_achievements_by_user_for_today(cls,user):
        """Returns all achievements that are relevant for the user today.
        
        This is needed as achievements may be limited to a specific time (e.g. only during holidays)
        """ 
        
        def generate_achievements_by_user_for_today():
            today = datetime.date.today()
            by_loc = {x["id"] : x["distance"] for x in cls.get_achievements_by_location(coords(user))}
            by_date = cls.get_achievements_by_date(today)
            
            def update(arr,distance):
                arr["distance"]=distance
                return arr
            
            return [update(arr,by_loc[arr["id"]]) for arr in by_date if by_loc.has_key(arr["id"])]
        
        key = str(user["id"])
        expiration_time = User.get_cache_expiration_time_for_today(user)
        
        return cache_achievements_by_user_for_today.get_or_create(key,generate_achievements_by_user_for_today, expiration_time=expiration_time)
        
    #We need to fetch all achievement data in one of these methods -> by_date is just queried once a date
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievements_by_location(cls,latlng):
        """return achievements which are valid in that location.""" 
        #TODO: invalidate automatically when achievement in user's range is modified
        distance = calc_distance(latlng, (t_achievements.c.lat, t_achievements.c.lng)).label("distance")
        
        q = select([t_achievements.c.id,
                    distance])\
            .where(or_(and_(t_achievements.c.lat==None,t_achievements.c.lng==None),
                        distance < t_achievements.c.max_distance))
        return [dict(x.items()) for x in DBSession.execute(q).fetchall()]
    
    @classmethod
    @cache_general.cache_on_arguments()
    def get_achievements_by_date(cls,date):
        """return achievements which are valid at that date"""
        q = t_achievements.select().where(and_(or_(t_achievements.c.valid_start==None,
                                                          t_achievements.c.valid_start<=date),
                                               or_(t_achievements.c.valid_end==None,
                                                          t_achievements.c.valid_end>=date)
                                               ))
        return [dict(x.items()) for x in DBSession.execute(q).fetchall()]
    
    #TODO:CACHE
    @classmethod
    def get_relevant_users_by_achievement_and_user(cls,achievement,user_id):
        """return all relevant other users for the leaderboard. 
        
        dependes on the "relevance" attribute of the achivement, can be "friends" or "city" (city is still a todo)
        """
        # this is needed to compute the leaderboards
        users=[user_id,] 
        if achievement["relevance"]=="city":
            #TODO
            pass
        elif achievement["relevance"]=="friends":
            users += [x["to_id"] for x in DBSession.execute(select([t_users_users.c.to_id,], t_users_users.c.from_id==user_id)).fetchall()]
        return set(users)
    
    #TODO:CACHE
    @classmethod
    def get_relevant_users_by_achievement_and_user_reverse(cls,achievement,user_id):
        """return all users which have this user as friends and are relevant for this achievement.
        
        the reversed version is needed to know in whose contact list the user is. when the user's value is updated, all the leaderboards of these users need to be regenerated"""
        users=[user_id,] 
        if achievement["relevance"]=="city":
            #TODO
            pass
        elif achievement["relevance"]=="friends":
            users += [x["from_id"] for x in DBSession.execute(select([t_users_users.c.from_id,], t_users_users.c.to_id==user_id)).fetchall()]
        return set(users)
    
    #TODO:CACHE
    @classmethod
    def get_level(cls, user_id, achievement_id):
        """get the current level of the user for this achievement."""
        
        q = select([t_achievements_users.c.level,
                    t_achievements_users.c.updated_at],
                       and_(t_achievements_users.c.user_id==user_id,
                            t_achievements_users.c.achievement_id==achievement_id)).order_by(t_achievements_users.c.level.desc())
        return DBSession.execute(q).fetchall()
    
    @classmethod
    def get_level_int(cls,user_id,achievement_id):
        """get the current level of the user for this achievement as int (0 if the user does not have this achievement)"""
        lvls = Achievement.get_level(user_id, achievement_id)
        
        if not lvls:
            return 0
        else:
            return lvls[0]["level"]
    
    @classmethod
    def basic_output(cls,achievement,goals,include_levels=True,
                     max_level_included=None):
        """construct the basic output structure for the achievement."""
        
        achievementcategory = None
        if achievement["achievementcategory_id"]!=None:
            achievementcategory = AchievementCategory.get_achievementcategory(achievement["achievementcategory_id"])
        
        out = {
            "id" : achievement["id"],
            "internal_name" : achievement["name"],
            "maxlevel" : achievement["maxlevel"],
            "priority" : achievement["priority"],
            "hidden" : achievement["hidden"],
            "achievementcategory" : achievementcategory["name"] if achievementcategory!=None else ""
            #"updated_at" : combine_updated_at([achievement["updated_at"],]),
        }
        
        def goal_out(g):
            goal_goal = eval_formular(g["goal"], {"level":i})
            return {
                "goal_id" : g["id"],
                "goal_name" : Translation.trs(g["name_translation_id"], {"level":i, "goal":goal_goal}),
                "goal_goal" : goal_goal,
                #"updated_at" : g["updated_at"]
            }
        
        if include_levels:
            levellimit = achievement["maxlevel"]
            if max_level_included:
                max_level_included = min(max_level_included,levellimit) 
            
            out["levels"] = {
                str(i) : {
                    "level" : i,
                    "goals" : { str(g["id"]) : goal_out(g) for g in goals},
                    "rewards" : {str(r["id"]) : {
                        "id" : r["id"],
                        "reward_id" : r["reward_id"],
                        "name" : r["name"],
                        "value" : eval_formular(r["value"], {"level":i}),
                        "value_translated" : Translation.trs(r["value_translation_id"], {"level":i}),
                    } for r in Achievement.get_rewards(achievement["id"],i)},
                    "properties" : {str(r["property_id"]) : {
                        "property_id" : r["property_id"],
                        "name" : r["name"],
                        "value" : eval_formular(r["value"], {"level":i}),
                        "value_translated" : Translation.trs(r["value_translation_id"], {"level":i}),
                    } for r in Achievement.get_properties(achievement["id"],i)}
            } for i in range(0,max_level_included+1)}
        return out
    
    @classmethod
    def evaluate(cls, user, achievement_id):
        """evaluate the achievement including all its subgoals for the user.
        
{'fraction_non_alphanumeric': 0.13032663144170728, 'fraction_numerical': 0.0053866739861387495, 'mean_word_length': 3.056011689396048, 'pattern_counts': {'":': 61, '<': 7, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 42, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '869781', 'n_tokens_mistral': 17867, 'n_tokens_neox': 17406, 'n_words': 3462}