category = CategoryPlugin(db)()

class CategoryPlugin():
    title = "Categorizor"
    keywords = "categories, categorize, organization"
    description = "Defines database models and functions for categorization"
    copyright = "GPL v2"

    def __init__(self, db, tablename="plugin_category"):
        self._db = db
        self._tablename = tablename

    def __call__(self):
        self.define_tables()
        return self

    def __getitem__(self, key):
        if isinstance(key, int):
            return self._db[self._tablename][key]
        elif isinstance(key, str):
            return self._db(self._db[self._tablename].name == key).select()
        else:
            raise TypeError("Key must be of type string or int")

    def define_tables(self):
        self._table = self._db.define_table(self._tablename,
            Field("name", "string", unique=True),
            Field("description", "text"),
            Field('left', 'integer'),
            Field('right', 'integer'),
        )

        self._table.name.requires = [
                    IS_NOT_IN_DB(self._db, '%s.name' % self._tablename), 
                    IS_NOT_EMPTY()
        ]
        self.table = self._table
        self.fid = "%s.id" % self._table
        self.fname = "%s.name" % self._table

    def add_node(self, name, parent_name=None, description=""):
        """
        add_node('Food', None)
        add_node('Meat', 'Food')
        add_node('Fruit', 'Food')
        add_node('Apple', 'Fruit')
        """
        if parent_name:
            parent = self._db(self._db[self._tablename].name == parent_name).select().first()
            self._db(self._db[self._tablename].right >= parent.right).update(right=self._db[self._tablename].right+2)
            self._db(self._db[self._tablename].left >= parent.right).update(left=self._db[self._tablename].left+2)
            node_id = self._db[self._tablename].insert(name=name, description=description, left=parent.right, right=parent.right+1)
        else:
            top = self._db(self._db[self._tablename].left > 0).select(orderby=self._db[self._tablename].left).last()
            if top:
                node_id = self._db[self._tablename].insert(name=name, description=description, left=top.right+2, right=top.right+3)
            else:
                node_id = self._db[self._tablename].insert(name=name, description=description, left=1, right=2)

        return node_id

    def ancestors(self, name, *fields):
        """
        print ancestors('Apple', db.category.name)
        Food
        Fruit
        """
        node = self._db(self._db[self._tablename].name == name).select().first()
        return self._db(self._db[self._tablename].left < node.left)(
                  self._db[self._tablename].right > node.right).select(
                            orderby=self._db[self._tablename].left, *fields
                            )

    def descendants(self, name, *fields):
        """ 
        print descendants('Fruit', db.category.name)
        Apple
        """
        node = self._db(self._db[self._tablename].name == name).select().first()
        return self._db(self._db[self._tablename].left > node.left)(
                  self._db[self._tablename].right < node.right).select(
                            orderby=self._db[self._tablename].left, *fields
                            )