"""
Developed by Massimo Di Pierro, optional component of web2py, GPL2 license.
"""
import re
import pickle
import copy


def quote(text):
    return str(text).replace('\\', '\\\\').replace("'", "\\'")


class Node:
    """
    Example::

         # controller
         from gluon.contrib.spreadsheet import Sheet

         def callback():
             return cache.ram('sheet1', lambda: None, None).process(request)

         def index():
             sheet = cache.ram('sheet1',
                 lambda: Sheet(10, 10, URL(r=request, f='callback')), 0)
             #sheet.cell('r0c3', value='=r0c0+r0c1+r0c2', readonly=True)
             return dict(sheet=sheet)

         # view
         {{extend 'layout.html'}}
         {{=sheet}}

         or insert invidivual cells via

         {{=sheet.nodes['r0c0']}}

    """

    def __init__(self, name, value, url='.', readonly=False, active=True,
            onchange=None):
        self.url = url
        self.name = name
        self.value = str(value)
        self.computed_value = ''
        self.incoming = {}
        self.outcoming = {}
        self.readonly = readonly
        self.active = active
        self.onchange = onchange
        self.size = 4
        self.locked = False

    def xml(self):
        return """<input name="%s" id="%s" value="%s" size="%s"
        onkeyup="ajax('%s/keyup',['%s'], ':eval');"
        onfocus="ajax('%s/focus',['%s'], ':eval');"
        onblur="ajax('%s/blur',['%s'], ':eval');" %s/>
        """ % (self.name, self.name, self.computed_value, self.size,
               self.url, self.name, self.url, self.name, self.url, self.name,
               (self.readonly and 'readonly ') or '')

    def __repr__(self):
        return '%s:%s' % (self.name, self.computed_value)


class Sheet:

    regex=re.compile('(?<!\w)[a-zA-Z_]\w*')

    re_strings = re.compile(r'(?P<name>'
                            + r"[uU]?[rR]?'''([^']+|'{1,2}(?!'))*'''|"
                            + r"'([^'\\]|\\.)*'|"
                            + r'"""([^"]|"{1,2}(?!"))*"""|'
                            + r'"([^"\\]|\\.)*")', re.DOTALL)

    def dumps(self):
        dump = pickle.dumps(self)
        return dump

    @staticmethod
    def loads(data):
        sheet = pickle.loads(data)
        return sheet

    def process(self, request):
        """
        call this in action that creates table, it will handle ajax callbacks
        """
        cell = request.vars.keys()[0]
        if request.args(0) == 'focus':
            return "jQuery('#%s').val('%s');" % (cell, quote(self[cell].value))
        value = request.vars[cell]
        self[cell] = value
        if request.args(0) == 'blur':
            return "jQuery('#%s').val('%s');" \
                % (cell, quote(self[cell].computed_value))
        elif request.args(0) == 'keyup':
            jquery = ''
            for other_key in self.modified:
                if other_key != cell:
                    jquery += "jQuery('#%s').val('%s');" % \
                        (other_key, quote(self[other_key].computed_value))
        return jquery

    def __init__(self, rows, cols, url='.', readonly=False, active=True,
            onchange=None):
        self.rows = rows
        self.cols = cols
        self.url = url
        self.nodes = {}
        self.error = 'ERROR: %(error)s'
        self.allowed_keywords = ['for', 'in', 'if', 'else', 'and', 'or', 'not',
                               'i', 'j', 'k', 'x', 'y', 'z', 'sum']
        self.environment = {}
        [self.cell('r%sc%s'%(k/cols, k%cols), '0.0', readonly, active, onchange)
            for k in xrange(rows*cols)]
        exec('from math import *', {}, self.environment)

    def delete_from(self, other_list):
        indices = [k for (k, node) in enumerate(other_list) if k == node]
        if indices:
            del other_list[indices[0]]

    def changed(self, node, changed_nodes=[]):
        for other_node in node.outcoming:
            if not other_node in changed_nodes:
                changed_nodes.append(other_node)
                self.changed(other_node, changed_nodes)
        return changed_nodes

    def define(self, name, obj):
        self.environment[name] = obj

    def cell(self, key, value, readonly=False, active=True, onchange=None):
        """
        key is the name of the cell
        value is the initial value of the cell. It can be a formula "=1+3"
        a cell is active if it evaluates formuls
        """
        key = str(key)
        if not self.regex.match(key):
            raise SyntaxError, "Invalid cell name: %s" % key
        node = Node(key, value, self.url, readonly, active, onchange)
        self.nodes[key] = node
        self[key] = value

    def __setitem__(self, key, value):
        key = str(key)
        value = str(value)
        node = self.nodes[key]
        node.value = value
        if value[:1] == '=' and node.active:
            # clear all edges involving current node
            for other_node in node.incoming:
                del other_node.outcoming[node]
            node.incoming.clear()
            # build new edges
            command = self.re_strings.sub("''", value[1:])
            node.locked = False
            for match in self.regex.finditer(command):
                other_key = match.group()
                if other_key == key:
                    self.computed_value = self.error % dict(error='cycle')
                    self.modified={}
                    break
                if other_key in self.nodes:
                    other_node = self.nodes[other_key]
                    other_node.outcoming[node] = True
                    node.incoming[other_node] = True
                elif not other_key in self.allowed_keywords and \
                        not other_key in self.environment:
                    node.locked = True
                    node.computed_value = \
                        self.error % dict(error='invalid keyword: ' + other_key)
                    self.modified = {}
                    break
            self.compute(node)
        else:
            try:
                node.computed_value = int(node.value)
            except:
                try:
                    node.computed_value = float(node.value)
                except:
                    node.computed_value = node.value
            self.environment[key] = node.computed_value
            if node.onchange:
                node.onchange(node)
        self.modified = self.iterate(node)

    def compute(self, node):
        if node.value[:1] == '=' and not node.locked:
            try:
                exec('__value__=' + node.value[1:], {}, self.environment)
                node.computed_value = self.environment['__value__']
                del self.environment['__value__']
            except Exception, e:
                node.computed_value = self.error % dict(error=str(e))
        self.environment[node.name] = node.computed_value
        if node.onchange:
            node.onchange(node)

    def iterate(self, node):
        output = {node.name: node.computed_value}
        changed_nodes = self.changed(node)
        while changed_nodes:
            ok=False
            set_changed_nodes = set(changed_nodes)
            for (k, other_node) in enumerate(changed_nodes):
                #print other_node, changed_nodes
                if not set(other_node.incoming.keys()).\
                        intersection(set_changed_nodes):
                    #print 'ok'
                    self.compute(other_node)
                    output[other_node.name] = other_node.computed_value
                    #print other_node
                    del changed_nodes[k]
                    ok = True
                    break
            if not ok:
                return {}
        return output

    def __getitem__(self, key):
        return self.nodes[str(key)]

    def get_computed_values(self):
        d={}
        for key in self.nodes:
            node = self.nodes[key]
            if node.value[:1] != '=' or not node.active:
                d[key] = node.computed_value
        return d

    def set_computed_values(self, d):
        for key in d:
            if not key in self.nodes:
                continue
            node = self.nodes[key]
            if node.value[:1] != '=' or not node.active:
                node.value = d[key]

    def xml(self):
        import gluon.html
        (DIV, TABLE, TR, TD, TH, BR) = \
            (gluon.html.DIV, gluon.html.TABLE, gluon.html.TR, gluon.html.TD,
             gluon.html.TH, gluon.html.BR)
        regex = re.compile('r\d+c\d+')
        return DIV(TABLE(TR(TH(), *[TH('c%s' % c) for c in range(self.cols)]),
                         *[TR(TH('r%s' % r), *[TD(self.nodes['r%sc%s'%(r, c)]) \
                                                  for c in range(self.cols)]) \
                                                    for r in range(self.rows)]),
                   BR(),
                   TABLE(*[TR(TH(key), TD(self.nodes[key])) \
                       for key in self.nodes if not regex.match(key)])).xml()

if __name__ == '__main__':
    s = Sheet(0, 0)
    s.cell('a', value="2")
    s.cell('b', value="=sin(a)")
    s.cell('c', value="=cos(a)**2+b*b")
    print s['c'].computed_value