#!/usr/bin/python3
'''Class model for analysis of Excel spreadsheets'''
from jsonweb.encode import to_object, dumper
from jsonweb.decode import from_object, loader
import json
import eDecoder

import re

try:
  string_types=basestring
except NameError:
  string_types=str

@from_object()
@to_object(exclude_nulls=True)
class Book(object):
  def __init__(self,source,sheets=[],formats=[]):
    assert(isinstance(source,string_types))
    self.source=source
    sheets=list(sheets)
    assert(all(isinstance(s,Sheet) for s in sheets))
    self.sheets=sheets
    formats=list(formats)
    self.formats=formats

  def addSheet(self,sheet):
    assert(isinstance(sheet,Sheet))
    self.sheets.append(sheet)

@from_object()
@to_object(exclude_nulls=True,suppress=["book"])
class Sheet(object):
  def __init__(self,name,book=None,tables=[],docs=[],misc=[]):
    assert(isinstance(name,string_types))
    self.name=name
    tables=list(tables)
    assert(all(isinstance(s,Table) for s in tables))
    self.tables=tables
    docs=list(docs)
    assert(all(isinstance(s,Region) for s in docs))
    self.docs=docs
    misc=list(misc)
    assert(all(isinstance(s,Region) for s in misc))
    self.misc=misc
    if book is not None:
      assert(isinstance(book,Book))
      book.addSheet(self)
    self.book=book

  def addTable(self,table):
    assert(isinstance(table,Region))
    self.tables.append(table)

@from_object()
@to_object(exclude_nulls=True,suppress=["parent"])
class Region(object):
  def __init__(self,name,parent=None,ranges=[],content=[]):
    if parent is not None:
      assert(isinstance(parent,(Region,Sheet)))
    self.parent=parent
    ranges=list(ranges)
    assert(all(isinstance(s,string_types) for s in ranges))
    self.ranges=[Range(s) for s in ranges]
    content=list(content)
    assert(all(isinstance(s,(Region,string_types)) for s in content))
    self.content=content
    assert(isinstance(name,string_types))
    self.name=name
    if parent is not None:
      parent.addRegion(self)

  def addRegion(self,content):
    assert(isinstance(content,(Region,string_types)))
    # convert
    self.content.append(content)

cell=r'(\$?)([A-Z]+)(\$?)([1-9][0-9]*)'
RANGE=re.compile(cell+(r'(:%s)?'%cell))
C=0 # Column
R=1 # Row
F=0 # Fixed ($)
V=1 # Value
@to_object()
class Range(object):
  def __init__(self,estr):
    m=RANGE.match(estr)
    if m is None:
      raise ValueError("string %s doesn't represent a range"%estr)
    rgrps=m.groups()
    self.tl=((rgrps[0]=='$',rgrps[1]),((rgrps[2]=='$',int(rgrps[3]))))
    if rgrps[4] is None:
      self.br=None
      self.dim=0
      self.vertical=None
    else:
      self.br=((rgrps[5]=='$',rgrps[6]),((rgrps[7]=='$',int(rgrps[8]))))
      if self.tl[C][V]==self.br[C][V]:
        self.dim=1
        self.vertical=True
      elif self.tl[R][V]==self.br[R][V]:
        self.dim=1
        self.vertical=False
      else:
        self.dim=2
        self.vertical=None

  def __str__(self):
    tls=_cellStr(self.tl[C])+_cellStr(self.tl[R])
    if self.br is None:
      return tls
    else:
      return '%s:%s'%(tls,_cellStr(self.br[C])+_cellStr(self.br[R]))

def _cellStr(dvp):
  (dollar,val)=dvp
  return '%s%s'%('$' if dollar else '',str(val))

@from_object()
@to_object(exclude_nulls=True)
class M(Region):
  def __init__(self,name,sheet=None,ranges=[],labels=[],data=[]):
    super(M,self).__init__(name,sheet,ranges,labels,data)

@from_object()
@to_object(exclude_nulls=True)
class RC(Region):
  def __init__(self,name,sheet=None,ranges=[],labels=[],data=[]):
    super(RC,self).__init__(name,sheet,ranges,labels,data)

@from_object()
@to_object(exclude_nulls=True,suppress=["sheet"])
class Table(M):
  def __init__(self,name,shape='mixed',sheet=None,ranges=[],labels=[],data=[]):
    super(Table,self).__init__(name,sheet,ranges)
    assert(shape in ('columns','rows','mixed'))
    self.shape=shape # if columns or rows, that's what correspond to DB columns
    labels=list(labels)
    assert(all(isinstance(s,Label) for s in labels))
    self.labels=labels
    data=list(data)
    assert(all(isinstance(s,Region) for s in data))
    self.data=data
    if sheet is not None:
      assert(isinstance(sheet,Sheet))
      sheet.addTable(self)
    self.sheet=sheet

  def addLabel(self,label):
    assert(isinstance(label,Label))
    self.labels.append(label)

  def addData(self,data):
    assert(isinstance(data,Data))
    self.data.append(data)

  def addRegion(self,region):
    assert(isinstance(region,(Label,Data)))
    if isinstance(region,Label):
      self.addLabel(region)
    else:
      self.addData(region)

def lt(filename):
  with open(filename,'r') as js:
    return loader(js.read(),cls=eDecoder.eDecoder)