# -*- encoding: utf-8 -*-
#   Copyright 2008 Agile42 GmbH, Berlin (Germany)
#   Copyright 2007 Andrea Tomasini <andrea.tomasini_at_agile42.com>
#
#   Licensed under the Apache License, Version 2.0 (the "License");
#   you may not use this file except in compliance with the License.
#   You may obtain a copy of the License at
#
#       http://www.apache.org/licenses/LICENSE-2.0
#
#   Unless required by applicable law or agreed to in writing, software
#   distributed under the License is distributed on an "AS IS" BASIS,
#   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#   See the License for the specific language governing permissions and
#   limitations under the License.
#   Authors: 
#       - Andrea Tomasini <andrea.tomasini__at__agile42.com>
#       - Felix Schwarz <felix.schwarz__at__agile42.com>
import cPickle
import threading

from trac.core import TracError, implements, Component
from trac.resource import Resource
from trac.ticket.api import ITicketChangeListener
from trac.ticket.model import Milestone
from trac.ticket.notification import TicketNotifyEmail
from trac.util.datefmt import to_datetime
from trac.util.text import to_unicode
from trac.util.translation import _

from agilo.scrum import BACKLOG_TABLE, BACKLOG_TICKET_TABLE, Sprint
from agilo.ticket.links import LINKS_TABLE
from agilo.ticket.model import AgiloTicket, AgiloTicketModelManager
from agilo.utils import Key, BacklogType, Realm
from agilo.utils.compat import exception_to_unicode
from agilo.utils.config import AgiloConfig
from agilo.utils.db import get_db_for_write, get_null
from agilo.utils.log import debug, error
from agilo.utils.sorting import By, Column, Attribute, SortOrder
from agilo.core import safe_execute, PersistentObjectModelManager
from agilo.core.model import add_as_dict


__all__ = ['Backlog', 'BacklogModelManager', 'backlog_resource', 
           'BACKLOG_TABLE', 'BACKLOG_TICKET_TABLE']


def backlog_resource(name):
    return Resource(Realm.BACKLOG, name)



class Backlog(object):
    """Represent a Backlog, a prioritized list of items, with a defined
    sorting, and a unique name, that can be stored to the Database"""
    
    class BacklogItem(object):
        """Represent a Backlog Item, and object that keep the Ticket
        reference, the id of the ticket and the last position in the 
        Backlog"""
        def __init__(self, ticket, pos=None, level=0, is_visible=True):
            """Initialize a BacklogItem"""
            self.ticket = ticket
            self.id = ticket.id
            self.pos = pos
            self.level = level
            self.modified = False
            
            # BacklogItems are cached in memory so we need to have some kind 
            # of per-thread storage without copying the item..
            self._threadlocal = threading.local()
            # This is only for additional filtering of backlogs by an additional
            # ticket attribute. If False, do not display this backlog item.
            self._threadlocal.is_visible = is_visible
        
        def _get_is_visible(self):
            return getattr(self._threadlocal, 'is_visible', True)
        
        def _set_is_visible(self, value):
            self._threadlocal.is_visible = value
        is_visible = property(_get_is_visible, _set_is_visible)
        
        @property
        def fields_for_type(self):
            """Returns the fields_for_type of the contained ticket"""
            return self.ticket.fields_for_type
            
        def reload(self):
            """Reload the ticket from the DB"""
            self.ticket = AgiloTicketModelManager(self.ticket.env).get(tkt_id=self.id)
            self.modified = False
            
        def delete(self):
            """
            Deletes the included ticket, remember to remove the item
            from the backlog as well
            """
            self.ticket.delete()
            
        def get(self, key):
            """Returns the value of the key attribute of the ticket or None"""
            value = None
            try:
                value = self[key]
            except KeyError:
                pass
            return value
            
        def __setitem__(self, attr, value):
            """
            Implement the setitem to allow setting property into the
            contained ticket, and set the modified attribute to True
            so that when the backlog is saved also the modified tickets
            will be
            """
            if attr == 'pos':
                self.pos = value
            elif attr == 'id':
                pass
            elif self.ticket[attr] != value:
                self.ticket[attr] = value
                self.modified = True
        
        def __getitem__(self, attr):
            """
            implement the getitem to allow retrieving and sorting
            by any ticket property, for pos return the local attribute
            """
            if attr == 'pos':
                return self.pos
            elif attr == 'id':
                return self.id
            else:
                return self.ticket[attr]
                
        def __cmp__(self, other):
            """
            Override standard object comparison, checking that the
            ID of the Backlog Items are matching
            """
            if other is None:
                return -1
            if self.id == other.id:
                return 0
            elif self.id < other.id:
                return -1
            elif self.id > other.id:
                return 1
        
        def __unicode__(self):
            return unicode(self.__str__())
            
        def __str__(self):
            return '<BacklogItem(%s), pos: %s level: %s>' % \
                    (self.ticket, self.pos, self.level)
        
        def __repr__(self):
            return self.__str__()
    
    # Add the as_dict behaviour
    BacklogItem = add_as_dict(BacklogItem)
    
    
    def __init__(self, env, name, ticket_types=[], sorting_keys=[], 
                 scope=None, b_type=BacklogType.GLOBAL, load=True, 
                 description='', b_strict=False, filter_by=None):
        """Initialize a Backlog. Ticket_types is a list of names of 
        the ticket types contained in this backlog, sorting_keys is a 
        list of two-tuples containing the sorting key and a boolean 
        value defining the sort order (True: descending, False: 
        ascending, according to agilo.utils.sorting.By)
        
        Scope can be set to a Sprint or Milestone (either an instance 
        or the corresponding name), in that case, the created Backlog 
        instance will only filter tickets contained in that sprint or 
        milestone.
        
        b_strict defines if the Backlog should load only tickets which 
        are linked to each other or not.
        
        If filter_by is given and a backlog filter attribute is set in 
        the configuration, the filter_by value is used as an 
        additional criteria to determine if a backlog item should be 
        shown."""
        self.env = env
        self.tm = AgiloTicketModelManager(self.env)
        self.log = self.env.log
        self.name = name
        self.b_strict = b_strict
        # The Backlog type
        self.b_type = b_type
        # The Sprint is transient, is not saved in the Backlog, is used
        # Only to filter tickets by Sprint.
        if isinstance(scope, Sprint) or isinstance(scope, Milestone):
            scope = scope.name
        self.scope = scope
        self.description = description
        # Python 2.4 initialization
        self.ticket_types = self._sorting_keys = None
        # Set the existence flag to false, is not yet loaded
        self.exists = False
        # Backlog are cached in memory so we need to have some kind of 
        # per-thread storage without copying the item..
        self._threadlocal = threading.local()
        # Store the tickets references
        self._tickets = list() # List of Backlog Items
        # This is only for additional filtering of backlogs by an additional
        # ticket attribute. 'filter_by' holds the attribute value which should
        # be shown.
        self.filter_by = filter_by
        if not self._load(load):
            self.ticket_types = list(ticket_types)
            self.sorting_keys = list(sorting_keys)
            # Loads the allowed tickets for this Backlog
            if self.b_type == BacklogType.GLOBAL: # if is a global backlog
                self.reload()
        # DEBUG
        self._DEBUG = "[Backlog (%s)]:" % self.name
    
    def _get_filter_by(self, ):
        return getattr(self._threadlocal, 'filter_by', None)
    
    def _set_filter_by(self, value):
        self._threadlocal.filter_by = value
        for bi in self:
            bi.is_visible = self._calculate_item_visibility(bi.ticket)
    filter_by = property(_get_filter_by, _set_filter_by)
    
    def __unicode__(self):
        """Return a unicode representation of this Backlog"""
        return u'<Backlog: %s (%s)>' % (self.name, BacklogType.LABELS[self.b_type])
    
    def __str__(self):
        """Return a string representation of the Backlog"""
        return '<Backlog: %s (%s)>' % (repr(self.name), repr(BacklogType.LABELS[self.b_type]))
    
    @property
    def resource(self):
        """Return the Resource object for this backlog"""
        return backlog_resource(self.name)
    
    def _get_sorting_keys(self):
        """Returns the sorting keys as set by the human :-)"""
        sorting_copy = list(self._sorting_keys)
        sorting_copy.reverse()
        return sorting_copy
    
    def _set_sorting_keys(self, sorting_keys):
        """
        Makes sure that the sorting will happen according to the
        defined order, allowing the python sort method to run a stable
        sort
        """
        sorting_keys.reverse()
        self._sorting_keys = sorting_keys
    
    sorting_keys = property(_get_sorting_keys, _set_sorting_keys)
    
    # Iterator methods, to allow the Backlog to be used directly in for
    # loops
    def __iter__(self):
        """Returns the iterator"""
        self.__next = 0
        return self
        
    def next(self):
        """
        Returns the next item from the list of tickets, according
        to the actual sorting
        """
        if self.__next < len(self._tickets):
            self.__next += 1
            return self._tickets[self.__next - 1]
        raise StopIteration
    
    def __getitem__(self, pos):
        """Returns a backlog item by actual position"""
        if isinstance(pos, int) and pos < len(self._tickets):
            return self._tickets[pos]
    
    def _assign_levels(self, backlog_items):
        # the idea is that all items get a level
        for bi in backlog_items:
            ticket, level = self._calculate_ticket_level(bi.ticket)
            bi.level = level
    
    def sort(self):
        """
        Sort the backlog in multiple levels according to the given sorting
        keys
        """
        
        def sort_by_keys(backlog_items):
            """Sorts the given backlog items according to the sorting keys"""
            for key, order in self._sorting_keys:
                backlog_items.sort(cmp=By(Column(key), order))
                debug(self, "Sorted by: %s (%s) => %s" % \
                      (key, order, ['#%s: %s' % (bi.id, bi.get(key)) for bi in backlog_items]))
        
        def visit_backlog(backlog_items, result=[], indexes=[], level=0, parent=None):
            """
            Visits the backlog and return a list of sorted items, grouped
            by hierarchy. the parameter is the list of first level items
            in the backlog, the others will be extracted from the hierarchy
            """
            def _add_bi(bi, level, result, indexes):
                # insert only once and in the most significant position
                if bi.id not in indexes:
                    bi.pos = len(result)
                    bi.level = level
                    indexes.append(bi.id)
                    result.append(bi)
            
            if backlog_items.get(level) is not None:
                for bi in backlog_items[level]:
                    if parent is not None:
                        # add only if is related
                        if parent.ticket.is_linked_to(bi.ticket):
                            _add_bi(bi, level, result, indexes)
                        else:
                            continue
                    else:
                        _add_bi(bi, level, result, indexes)
                    # check if the item has children in the hierarchy
                    if backlog_items.has_key(level + 1):
                        visit_backlog(backlog_items, result, indexes, level=level + 1, parent=bi)
            debug(self, "Returning sorted level: %s => %s" % (level, result))
            return result
        
        # Build the list of new items starting from the roots elements of
        # the hierarchy.
        backlog_items = list(self._tickets)
        self._assign_levels(backlog_items)
        # sort every level according to the set keys
        levels = dict()
        level = 0
        while len(backlog_items) > 0:
            levels[level] = [bi for bi in backlog_items if bi.level == level]
            if len(levels[level]) == 0:
                del levels[level]
                break
            # sort the level
            sort_by_keys(levels[level])
            level += 1
        # now replace the current tickets with the sorted ones
        self._tickets = visit_backlog(levels)
    
    def _calculate_item_visibility(self, ticket):
        is_visible = True
        if self.filter_by is not None:
            filter_by_attr = AgiloConfig(self.env).backlog_filter_attribute
            if filter_by_attr is not None:
                is_visible = (ticket[filter_by_attr] in (None, '', self.filter_by))
        return is_visible
    
    def add(self, ticket, pos=None, level=0, checked=False):
        """Adds a ticket to the Backlog, the default level is 0 means is not nested"""
        debug(self, "Adding ticket(%s) to Backlog(%s) at position: %s" % \
                    (ticket.id, self.name, pos))
        # Avoid duplicates
        if checked or not self.has_ticket(ticket):
            is_visible = self._calculate_item_visibility(ticket)
            bi = Backlog.BacklogItem(ticket, pos, level, is_visible=is_visible)
            # Append the ticket to the backlog
            self._tickets.append(bi)
    
    def as_dict(self):
        """Utility method to return the current object attributes values as a dict
        of attr:value to be used as value object."""
        attrs = {}
        for attr in dir(self):
            attr_value = getattr(self, attr)
            if isinstance(attr_value, PersistentObjectModelManager):
                continue
            if not attr.startswith('_') and not callable(attr_value) and \
                    not attr.startswith('SQL') and not attr in ('env', 'log'):
                # at: we do not make any data conversion here, cause the
                # format of numbers and dates may be different on the 
                # client, therefore the views should take care of the
                # display part of it.
                if hasattr(attr_value, 'as_dict') and \
                        callable(getattr(attr_value, 'as_dict')):
                    attr_value = attr_value.as_dict()
                elif isinstance(attr_value, list):
                    for i, item in enumerate(attr_value):
                        if hasattr(item, 'as_dict'):
                            attr_value[i] = item.as_dict()
                attrs[attr] = attr_value
        return attrs
    
    def remove(self, t_id_or_pos, pos=False, delete=False):
        """
        Remove the given ticket from the Backlog, either searching for the id
        or directly via the position of the ticket.
        """
        bi = None
        if isinstance(t_id_or_pos, AgiloTicket):
            bi = self.get_item_by_id(t_id_or_pos.id)
        elif isinstance(t_id_or_pos, Backlog.BacklogItem):
            bi = t_id_or_pos
        elif isinstance(t_id_or_pos, int):
            if pos:
                bi = self.get_item_in_pos(t_id_or_pos)
            else:
                bi = self.get_item_by_id(t_id_or_pos)
        
        if bi is not None:
            if delete:
                bi.delete()
            # Remove also the scope from the ticket if it is a scoped backlog
            # or will reappear with the SQL query
            elif self.b_type != BacklogType.GLOBAL:
                bi.ticket[Key.SPRINT] = None
                bi.ticket[Key.MILESTONE] = None
                bi.ticket.save_changes(author="agilo", 
                                       comment=_("Removed from Backlog: %s" % self.name))
            self._tickets.remove(bi)
            return True
        return False
    
    def has_ticket(self, ticket_or_id):
        """Returns True if the given ticket or ticket_id are part of this Backlog"""
        if ticket_or_id is not None:
            if isinstance(ticket_or_id, AgiloTicket):
                ticket_or_id = ticket_or_id.id
            else:
                ticket_or_id = int(ticket_or_id)
            # Create the list of IDs of all the listed tickets, 
            # instances are different because wrapped in BacklogItem
            return ticket_or_id in [b.id for b in self._tickets]
        return False
    
    def get_pos_of_ticket(self, ticket_or_id):
        """
        Returns the actual position in the Backlog for the
        given ticket or ticket ID
        """
        if ticket_or_id is not None:
            if isinstance(ticket_or_id, AgiloTicket):
                ticket_or_id = ticket_or_id.id
            else:
                ticket_or_id = int(ticket_or_id)
            ids = [b.id for b in self._tickets]
            if ticket_or_id in ids:
                return ids.index(ticket_or_id)
        return None
        
    def get_item_in_pos(self, pos):
        """Returns the Backlog Item in the position pos"""
        bi = None
        try:
            bi = self._tickets[pos]
        except IndexError, e:
            exception_to_unicode(e)
        return bi
        
    def get_item_by_id(self, t_id):
        """Returns the BacklogItem with the given id"""
        if t_id is not None:
            if isinstance(t_id, basestring):
                t_id = int(t_id)
            for bi in self._tickets:
                if bi.id == t_id:
                    return bi
    
    def get_items(self):
        """
        Returns the list of the ticket in this Backlog, according
        to the defined order
        """
        return self._tickets
        
    def count(self):
        """Returns the number of items in this Backlog"""
        return len(self._tickets)
    # so the len operator will work too
    __len__ = count
    
    def __nonzero__(self):
        """Returns True if the backlog exists"""
        return self.exists
    
    def move(self, ticket, to_pos, from_pos=None):
        """Moves a ticket in the Backlog from_pos to to_pos. In case
        from_pos is not set, it will move the item in the new position."""
        def _update_ticket_position(backlog_item, new_pos):
            """Updates the position of the moved ticket and the other ticket in 
            the backlog"""
            backlog_item.pos = new_pos
            i = new_pos - 1
            j = new_pos + 1
            while i >= 0:
                self._tickets[i].pos = i
                i -= 1
            while j < self.count():
                self._tickets[j].pos = j
                j += 1
        
        if ticket is not None and to_pos is not None:
            if isinstance(ticket, self.BacklogItem):
                if from_pos is None:
                    from_pos = ticket.pos
            else:
                # We have to insert a BacklogItem ;-)
                is_visible = self._calculate_item_visibility(ticket)
                ticket = Backlog.BacklogItem(ticket, is_visible=is_visible)
            if from_pos is not None:
                try:
                    del self._tickets[from_pos]
                except IndexError, e:
                    exception_to_unicode(e)
                    # We don't make the call fail
            else:
                try:
                    from_pos = self._tickets.index(ticket)
                    if from_pos == to_pos:
                        return True
                    self._tickets.remove(ticket)
                except ValueError, e:
                    exception_to_unicode(e)
                    return False
            self._tickets.insert(to_pos, ticket)
            #ticket.pos = to_pos
            _update_ticket_position(ticket, to_pos)
            return True
        return False

    def _insert_or_update_tickets(self, db):
        """Inserts or updates tickets related to this backlog"""
        # Query to insert tickets
        cursor = db.cursor()
        
        sqld_tickets = "DELETE FROM %s WHERE name=%%(name)s AND ticket_id=%%(t_id)s" % BACKLOG_TICKET_TABLE
        sqli_tickets = "INSERT INTO %s (name, ticket_id, pos, level, scope) VALUES" \
                       " (%%(name)s, %%(t_id)s, %%(pos)s, %%(level)s, %%(scope)s)" % BACKLOG_TICKET_TABLE
        sqlu_tickets = "UPDATE %s SET ticket_id=%%(t_id)s, level=%%(level)s WHERE" \
                       " name=%%(name)s AND pos=%%(pos)s" % BACKLOG_TICKET_TABLE
        sqls_tickets = "SELECT level FROM %s WHERE name=%%(name)s AND pos=%%(pos)s" % BACKLOG_TICKET_TABLE
        sqls_existing = "SELECT ticket_id FROM %s WHERE name=%%(name)s" % BACKLOG_TICKET_TABLE
        # If self.scope ad the scope in the Query
        sql_scope = " AND scope=%(scope)s"
        # Check the backlog type and the value of scope
        if self.b_type != BacklogType.GLOBAL and self.scope is not None:
            sqls_tickets += sql_scope
            sqlu_tickets += sql_scope
            sqls_existing += sql_scope
            
        # Collect all the ids of the existing backlog tickets to check if one or more have
        # been removed from the backlog
        existing_tickets = list()
        safe_execute(cursor, sqls_existing, {'name': self.name, 'scope': self.scope or BacklogType.GLOBAL})
        for t_id, in cursor:
            existing_tickets.append(t_id)
        debug(self, "Number of tickets in this Backlog: %s" % len(self._tickets))
        for i, backlogItem in enumerate(self._tickets):
            debug(self, "BacklogItem(%s) pos: %s i: %s" % (backlogItem, backlogItem.pos, i))
            t_params = {
                'name': self.name,
                't_id': backlogItem.id,
                'pos': i,
                'scope': self.scope or BacklogType.GLOBAL,
                'level': backlogItem.level
            }
            # The ticket is existing
            if backlogItem.id in existing_tickets:
                existing_tickets.remove(backlogItem.id)
            # Check if the ticket is already in the backlog
            debug(self, "SQL: %s (%s)" % (sqls_tickets, t_params))
            safe_execute(cursor, sqls_tickets, t_params)
            if cursor.fetchone() is not None:
                debug(self, "Updating ticket(%s) into Backlog(%s <%s>) at position: %s and level: %s" % \
                            (t_params['t_id'], t_params['name'], t_params['scope'], t_params['pos'], t_params['level']))
                debug(self, "SQL: %s (%s)" % (sqlu_tickets, t_params))
                safe_execute(cursor, sqlu_tickets, t_params)
            else:
                debug(self, "Inserting ticket(%s) into Backlog(%s <%s>) at position: %s and level: %s" % \
                            (t_params['t_id'], t_params['name'], t_params['scope'], t_params['pos'], t_params['level']))
                debug(self, "SQL: %s (%s)" % (sqli_tickets, t_params))
                safe_execute(cursor, sqli_tickets, t_params)
            backlogItem.pos = i
        # Now let's remove all the tickets that are not existing anymore
        for t_id in existing_tickets:
            safe_execute(cursor, sqld_tickets, {
                't_id': t_id,
                'name': self.name
            })

    @staticmethod
    def select(env, db=None, order_by=None):
        """Returns a list of all available Backlog"""
        backlogs = list()
        db, handle_ta = get_db_for_write(env, db)
        sql_query = "SELECT name FROM %s" % BACKLOG_TABLE
        if order_by:
            sql_order_by = []
            if not isinstance(order_by, list):
                order_by = [order_by]
            for clause in order_by:
                if clause.startswith('-'):
                    clause = clause[1:] + ' DESC'
                sql_order_by.append(clause)
            sql_query += " ORDER BY " + ', '.join(sql_order_by)
        debug(env, "[Backlog]: Select => %s" % sql_query)
        try:
            cursor = db.cursor()
            safe_execute(cursor, sql_query)
            for name, in cursor:
                backlogs.append(Backlog(env, name, load=False)) #Don't load tickets
        except Exception, e:
            error(env, "[Backlog]: Error fetching Backlogs: %s" % to_unicode(e))
        return backlogs
    
    def _save_ticket_changes(self, author, comment=None, db=None):
        """Save the changes made to tickets into the backlog"""
        # Get a db connection
        db, handle_ta = get_db_for_write(self.env, db)
        for bi in self._tickets:
            if bi.modified:
                now = to_datetime(t=None)
                try:
                    bi.ticket.save_changes(author, comment, when=now, db=db)
                    bi.modified = False
                    if handle_ta:
                        db.commit()
                except Exception:
                    if handle_ta:
                        db.rollback()
                    raise
                try:
                    tn = TicketNotifyEmail(self.env)
                    tn.notify(bi.ticket, newticket=False, modtime=now)
                except Exception, e:
                    error(self, "Failure sending notification on change to " \
                                "ticket #%s:" % bi.id)
                    exception_to_unicode(e)
    
    def save(self, db=None, author=None, comment=None, config_only=False):
        """Saves the Backlog data to the database"""
        # Gets a db connection and a transaction flag
        db, handle_ta = get_db_for_write(self.env, db)
        params = {
            'name': self.name,
            'description': self.description,
            'ticket_types': cPickle.dumps(self.ticket_types),
            'sorting_keys': cPickle.dumps(self._sorting_keys),
            'b_type': self.b_type,
            'b_strict': self.b_strict is not None and int(self.b_strict) or 0,
        }
        sqli_query = "INSERT INTO %s (name, description, ticket_types, sorting_keys, b_type, b_strict)" \
                    " VALUES (%%(name)s, %%(description)s, %%(ticket_types)s, %%(sorting_keys)s, " \
                    "%%(b_type)s, %%(b_strict)s)" % BACKLOG_TABLE
        sqls_query = "SELECT name FROM %s WHERE name=%%(name)s" % BACKLOG_TABLE
        sqlu_query = "UPDATE %s SET description=%%(description)s, ticket_types=%%(ticket_types)s, " \
                     "b_type=%%(b_type)s, sorting_keys=%%(sorting_keys)s, b_strict=%%(b_strict)s WHERE name=%%(name)s" % BACKLOG_TABLE
        debug(self, "SQL Query: %s\n%s\n%s" % (sqli_query, sqls_query, sqlu_query))
        try:            
            cursor = db.cursor()
            safe_execute(cursor, sqls_query, params)
            if cursor.fetchone() is not None:
                safe_execute(cursor, sqlu_query, params)
            else:
                safe_execute(cursor, sqli_query, params)
            
            if not config_only:
                # Now insert the ticket references
                debug(self, "Before update tickets")
                self._insert_or_update_tickets(db)
                # Now update ticket changes if needed
                debug(self, "Before save tickets")
                # AT: we do not pass the transaction inside here or we will have
                # to rollback the whole changes made to the ticket if only one
                # was updated by someone else outside of the backlog
                self._save_ticket_changes(author, comment)
            
            if handle_ta:
                db.commit()
                self.exists = True
                debug(self, "DB Committed, saved Backlog '%s'" % self.name)
                return True
        
        except Exception, e:
            exception_to_unicode(e)
            if handle_ta:
                db.rollback()
            raise TracError(_("An error occurred while saving Backlog " + \
                              "data: %s" % to_unicode(e)))
        
        return False
    
    def _calculate_ticket_level(self, ticket, tickets_levels=None, level=0):
        """
        Returns the tuple ticket, level, where the first value is the ticket 
        and the second the level at which that ticket in the current link tree 
        is set. The ticket_levels list is modified, containing all the ticket
        parents till the root that are acceptable types for this backlog.
        """
        if tickets_levels is None:
            tickets_levels = list()
        for p in ticket.get_incoming():
            if p.get_type() in self.ticket_types:
                p, level = self._calculate_ticket_level(p, tickets_levels, level)
                tickets_levels.append((p, level))
                # One more level
                level += 1
        return ticket, level

    def _load_allowed_tickets(self, db):
        """
        Loads all the ticket of allowed type into the backlog, in this way the
        Backlog is dynamic.
        """
        sql_scope = sql_from = ""
        sql_allowed = "SELECT DISTINCT id, type FROM %(from)s WHERE ticket.type IN ('%(types)s') %(scope)s"
        
        if self.b_type == BacklogType.GLOBAL:
            if self.b_strict:
                # Enables the strict option on global backlog, restrict only to unplanned items
                # good for Product Backlog, bad for Bug Backlog :-)
                sql_from = "ticket LEFT OUTER JOIN ticket_custom ON ticket.id = ticket_custom.ticket"
                sql_scope = "AND ('sprint' NOT IN (SELECT name FROM ticket_custom WHERE " + \
                            "ticket_custom.ticket=ticket.id) OR (ticket_custom.name='sprint' " + \
                            "AND (ticket_custom.value IS NULL OR ticket_custom.value=''))) AND " + \
                            "(ticket.milestone='' OR ticket.milestone IS NULL)" % \
                            {'null': get_null(self.env)}
            else:
                # If it is a global Backlog we will not load the closed tickets, if is a scoped
                # backlog is not a problem and people may be willing to see their tasks into the
                # Sprint backlog also when closed.
                sql_from = "ticket"
                sql_scope = " AND status != 'closed'"
                
        elif self.b_type in (BacklogType.SPRINT, BacklogType.MILESTONE) and self.scope: 
            sql_from = "ticket LEFT OUTER JOIN ticket_custom ON ticket.id=ticket_custom.ticket"
            sql_scope = "AND (ticket_custom.name='%s' " % Key.SPRINT
            
            if self.b_type == BacklogType.SPRINT:
                sql_scope += "AND ticket_custom.value='%s')" % self.scope
            else:
                # We need to get all the sprint for which the milestone is the 
                # scope
                from agilo.scrum.sprint import SprintModelManager
                sprints = [s.name for s in \
                           SprintModelManager(self.env).select({'milestone': self.scope})]
                sql_scope += "AND ticket_custom.value in ('%s') OR ticket.milestone='%s')" % \
                            ("', '".join(sprints), self.scope)
                            
        if (self.b_type == BacklogType.SPRINT or self.b_type == BacklogType.MILESTONE) and \
                self.scope and not self.b_strict:
            sql_linked = " UNION SELECT src, type FROM " + LINKS_TABLE + " JOIN" + \
                         " ticket ON src=id" + \
                         " LEFT OUTER JOIN ticket_custom ON ticket_custom.ticket=ticket.id" + \
                         " WHERE dest IN (%(table)s) AND type IN ('%(types)s')" + \
                         "%(extra_scope)s UNION SELECT dest, type FROM " + \
                         LINKS_TABLE + " LEFT OUTER JOIN ticket ON dest=id" + \
                         " LEFT OUTER JOIN ticket_custom ON" + \
                         " ticket_custom.ticket=ticket.id WHERE src IN" + \
                         " (%(table)s) AND type IN ('%(types)s') %(extra_scope)s"
            sql_allowed = sql_allowed % {'from': sql_from, 
                                         'types': "', '".join(self.ticket_types),
                                         'scope': sql_scope,}
            sql_extra_scope = sql_scope.replace('AND (', 'AND ((')
            sql_extra_scope += " OR (ticket_custom.name='sprint'" + \
                               " AND (ticket_custom.value IS NULL OR ticket_custom.value='')" + \
                               " OR ('sprint' NOT IN (SELECT name FROM ticket_custom WHERE" + \
                               " ticket_custom.ticket=ticket.id)) ))"
            sql_allowed = sql_allowed + sql_linked % \
                          {'table': sql_allowed.replace('DISTINCT id, type', 'DISTINCT id'),
                           'types': "', '".join(self.ticket_types),
                           'extra_scope': sql_extra_scope}
        else:
            sql_allowed = sql_allowed % {'from': sql_from, 
                                         'types': "', '".join(self.ticket_types),
                                         'scope': sql_scope}
            sql_allowed = sql_allowed % {'table': BACKLOG_TICKET_TABLE,
                                         'name': self.name}
        
        cursor = db.cursor()
        # Get position and level for existing tickets
        tickets_pos_level = self._load_backlog_tickets_position(db)
        
        debug(self, "Other Tickets query: %s" % sql_allowed)
        safe_execute(cursor, sql_allowed)
        # Load the tickets in the backlog
        for t_id, t_type in cursor:
            t_pos = None
            t_level = 0
            # if the ticket is already in the backlog skip it
            if self.has_ticket(t_id):
                continue
            t = self.tm.get(tkt_id=t_id, t_type=t_type, db=db)
            # Check if there is a position and level set
            pos_level = tickets_pos_level.get(t_id)
            if pos_level is not None:
                t_pos, t_level = pos_level
            
            if len(t.get_incoming()) > 0:
                # it has a parents
                parents = list()
                t, t_level = self._calculate_ticket_level(t, parents)
                for p, p_level in parents:
                    if not self.b_strict or \
                            (self.b_type == BacklogType.GLOBAL and \
                            (p[Key.MILESTONE] in (None, '') and \
                             p[Key.SPRINT] in (None, ''))): # Not planned
                        p_pos_level = tickets_pos_level.get(p.id)
                        p_pos = None
                        if p_pos_level is not None:
                            p_pos = p_pos_level[0]
                        # First we lay down the highest level
                        self.add(p, level=p_level, pos=p_pos)
            # If no parent was found load it at level 0
            self.add(t, level=t_level, pos=t_pos, checked=True)
        # Sort backlog by position
        self._tickets.sort(cmp=By(Attribute('pos'), SortOrder.ASCENDING))
            
    def _load_backlog_tickets_position(self, db):
        """
        Loads the position and level of the tickets already stored in the 
        Backlog from the database
        """
        # Dictionary containing the pos and level by ticket_id
        tickets_pos_level = dict()
        # Columns
        sql_columns = ['ticket_id', 'pos', 'level']
        sql_where = ["name='%s'" % self.name]
        sql_order = ['pos']
        # Generic from clause
        sql_from = BACKLOG_TICKET_TABLE
        
        if self.b_type == BacklogType.GLOBAL:
            sql_columns.append('status')
            sql_from = "%s JOIN ticket on ticket_id=id" % BACKLOG_TICKET_TABLE
            sql_where.append("status!='closed'")
        else:
            sql_where.append("scope='%s'" % self.scope)
        
        sql_select = "SELECT %s FROM %s WHERE %s ORDER BY %s" % \
                     (', '.join(sql_columns),
                      sql_from,
                      ' AND '.join(sql_where),
                      ', '.join(sql_order))
                      
        # Get a cursor
        cursor = db.cursor()
        if (self.b_type != BacklogType.GLOBAL and self.scope == None):
            msg = _('You need to provide a scope parameter for this' \
                    'type of Backlog (%s)' % \
                  BacklogType.LABELS[self.b_type])
            error(self, msg)
            raise Exception(msg)
        debug(self, u"SQL Query: %s" % sql_select)
        safe_execute(cursor, sql_select)
        for row in cursor:
            debug(self, u"Loading ticket(%s) in position: %s with " \
                        u"level: %s" % \
                        (row[0], row[1], row[2]))
            tickets_pos_level[row[0]] = (row[1], row[2])
        return tickets_pos_level
        
    def _load(self, load, db=None):
        """Try to load the Backlog from the database"""
        db, handle_ta = get_db_for_write(self.env, db)
        sql_query = "SELECT name, description, ticket_types, sorting_keys, b_type, b_strict" \
                    " FROM %s WHERE name=%%s" % BACKLOG_TABLE
        debug(self, u"SQL Query: %s" % sql_query)
        try:
            cursor = db.cursor()
            safe_execute(cursor, sql_query, [self.name])
            row = cursor.fetchone()
            if row is not None:
                self.name, self.description, ticket_types, sorting_keys, b_type, b_strict = row
                self.b_type = int(b_type) # It is an int in the DB, no risk
                self.b_strict = bool(b_strict)
                try:
                    # Make sure unpickling doesn't break the backlog
                    if isinstance(ticket_types, unicode):
                        ticket_types = ticket_types.encode()
                    self.ticket_types = cPickle.loads(ticket_types)
                    
                    if isinstance(sorting_keys, unicode):
                        sorting_keys = sorting_keys.encode()
                    self._sorting_keys = cPickle.loads(sorting_keys)
                
                except Exception, e:
                    exception_to_unicode(e)
                
                self.exists = True
                # Now fetch the tickets and load the local dictionary
                if load:
                    # Loads the Backlog Tickets
                    #self._load_backlog_tickets(db)
                    # Now load at the end of the Backlog all the not included tickets
                    # of allowed type
                    self._load_allowed_tickets(db)
                return True
            else:
                return False
        except Exception, e:
            exception_to_unicode(e)
            if handle_ta:
                db.rollback()
            raise TracError(_("An error occurred while loading Backlog data: %s" % to_unicode(e)))
    
    def reload(self, db=None):
        """Reloads the Backlog tickets"""
        db, handle_ta = get_db_for_write(self.env, db)
        try:
            # First reset current dictionary, cause there may be deleted
            # tickets still loaded
            self._tickets = list()
            self._load_allowed_tickets(db)
            return True
        except Exception, e:
            exception_text = exception_to_unicode(e)
            self.env.log.exception(e)
            if handle_ta:
                db.rollback()
            raise TracError(_("An error occurred while trying to reload Backlog: %s" % \
                            exception_text))

    def delete(self, db=None):
        """
        Delete the current backlog and the association with the tickets from the
        database
        """        
        db, handle_ta = get_db_for_write(self.env, db)
        sql_delete_backlog = "DELETE FROM %s WHERE name=%%s" % BACKLOG_TABLE
        sql_delete_backlog_t = "DELETE FROM %s WHERE name=%%s" % BACKLOG_TICKET_TABLE
        cursor = db.cursor()
        try:
            safe_execute(cursor, sql_delete_backlog, [self.name])
            safe_execute(cursor, sql_delete_backlog_t, [self.name])
            if handle_ta:
                db.commit()
            return True
        except Exception, e:
            exception_to_unicode(e)
            if handle_ta:
                db.rollback()
            raise TracError(_("An error occurred while trying to delete Backlog: %s" % \
                            unicode(e)))


class BacklogUpdater(Component):
    
    implements(ITicketChangeListener)
    
    #===========================================================================
    # ITicketChangeListener methods
    #===========================================================================
    def ticket_created(self, ticket):
        """Called when a ticket is created."""
        pass
    
    def ticket_changed(self, ticket, comment, author, old_values):
        """
        Called when a ticket is modified.
        `old_values` is a dictionary containing the previous values of the
        fields that have changed.
        """
        ticket = AgiloTicket.as_agilo_ticket(ticket)
        debug(self, "Invoked for ticket #%s of type %s with: %s" % \
                     (ticket.id, ticket[Key.TYPE], old_values))
        
        if self._sprint_changed(old_values):
            # Load old sprint backlog, remove it
            old_sprint_name = old_values[Key.SPRINT]
            self._remove_from_sprint_backlogs(ticket, old_sprint_name)
        if self._milestone_changed(old_values):
            old_milestone_name = old_values[Key.MILESTONE]
            self._remove_from_milestone_backlogs(ticket, old_milestone_name)
        
        if ((old_values.get(Key.SPRINT, None) == None) and ticket[Key.SPRINT]) or \
            ((old_values.get(Key.MILESTONE, None) == None) and ticket[Key.MILESTONE]):
            # Remove ticket from global strict backlog
            # Remember, strict has 
            self._remove_from_backlogs(ticket, None, backlog_type=BacklogType.GLOBAL, strict=True)
    
    def ticket_deleted(self, ticket):
        """Called when a ticket is deleted."""
        ticket = AgiloTicket.as_agilo_ticket(ticket)
        self._remove_from_all_backlogs(ticket)
    
    def _sprint_changed(self, old_values):
        return (old_values.get(Key.SPRINT, None) != None)
    
    def _milestone_changed(self, old_values):
        return (old_values.get(Key.MILESTONE, None) != None)
    
    def _remove_from_sprint_backlogs(self, ticket, sprint_name, db=None):
        self._remove_from_backlogs(ticket, sprint_name, BacklogType.SPRINT, db)
        debug(self, u"Removed Ticket: %s from all sprint backlogs." % ticket.id)
    
    def _remove_from_milestone_backlogs(self, ticket, milestone_name, db=None):
        self._remove_from_backlogs(ticket, milestone_name, BacklogType.MILESTONE, db)
        debug(self, u"Removed Ticket: %s from all milestone backlogs." % ticket.id)
    
    def _remove_from_backlogs(self, ticket, scope, backlog_type, strict=None, db=None):
        # We don't know how many backlogs the user configured (e.g. sprint 
        # backlogs) and what's their name so we just get all sprint backlogs.
        subquery_sql = "SELECT name from %(btable)s where b_type=%(btype)d"
        if strict:
            subquery_sql += " and b_strict=1"
        sql_template = "DELETE FROM %(bticket)s WHERE ticket_id=%(ticket)s " + \
                        "and name IN ( " + subquery_sql + ")"
        if scope != None:
            sql_template += " and scope='%(scope)s'"
        parameters = dict(ticket=ticket.id, scope=scope, btype=backlog_type, 
                          bticket=BACKLOG_TICKET_TABLE, btable=BACKLOG_TABLE)
        sql_query = sql_template % parameters
        db, handle_ta = get_db_for_write(self.env, db)
        cursor = db.cursor()
        safe_execute(cursor, sql_query)
        if handle_ta:
            db.commit()
    
    def _remove_from_all_backlogs(self, ticket):
        """Removes a ticket from all the backlog where is in"""
        debug(self, u"Called Remove from all Backlogs: %s" % ticket)
        # Not checking if the ticket is in the Backlog, cause it is
        # cheaper to run a delete for nothing than load a whole backlog
        # Get a db connection
        db = self.env.get_db_cnx()
        cursor = db.cursor()
        try:
            safe_execute(cursor, "DELETE FROM %s WHERE ticket_id=%%s" % BACKLOG_TICKET_TABLE, [ticket.id])
            debug(self, u"Removed ticket: %s from all backlogs" % ticket.id)
            db.commit()
        except Exception, e:
            error(self, "An error occurred while deleting ticket: %s from backlogs:" % ticket)
            exception_to_unicode(e)
            
    # Adds the as_dict behavior to the Backlog
    Backlog = add_as_dict(Backlog)


class BacklogModelManager(PersistentObjectModelManager):
    """Manager for the Backlog model object. It allows to store and
    retrieve Backlogs, as well as creating new ones. The Manager also
    guarantees the Object identity"""
    
    model = Backlog
    
    def _get_model_key(self, model_instance=None):
        """Private method to return either a list of primary keys or a 
        tuple with all the primary keys and unique constraints needed 
        to identify a backlog."""
        if isinstance(model_instance, Backlog):
            return ((model_instance.name, 
                     model_instance.scope or 'global'), 
                     None)
        else:
            return [['name', 'scope'], None]
    
    def get(self, **kwargs):
        """Override the get to make sure if the scope parameter is
        set to global if is not set"""
        reload = False
        if 'scope' not in kwargs or kwargs['scope'] is None:
            kwargs['scope'] = 'global'
        if 'reload' in kwargs:
            reload = kwargs['reload']
            del kwargs['reload']
        if 'filter_by' not in kwargs:
            kwargs['filter_by'] = None
        filter = kwargs['filter_by']
        backlog = super(BacklogModelManager, self).get(**kwargs)
        # AT: we need to apply the filter
        if filter is not None:
            backlog.filter_by = filter
        if reload:
            backlog.reload()
        return backlog
    
    def create(self, *args, **kwargs):
        """Specialized method to create a backlog, first create the 
        backlog, than sets all the parameters, if allowed, than saves 
        the backlog"""
        # We need to check load, cause by default the model assumes
        # it is True
        default_values = {'load': True,
                          'ticket_types': [], 
                          'sorting_keys': [],
                          'b_type': BacklogType.GLOBAL,
                          'scope': 'global'}
        for prop in default_values:
            if prop not in kwargs or kwargs[prop] is None:
                kwargs[prop] = default_values[prop]
        # now call the super method
        return super(BacklogModelManager, self).create(*args, **kwargs)
    
    def select(self, criteria=None, order_by=None, limit=None):
        """Must return a list of models matching the query criteria. 
        Criteria should be a dictionary with with key: value pairs."""
        return self.model.select(self.env, order_by=order_by or ['name'])

