'''
Created on 2.9.2010

@author: casties
'''

from OFS.Folder import Folder
from Products.PageTemplates.PageTemplateFile import PageTemplateFile
import logging
import re
import psycopg2
import json
import time

from zope.interface import implements
from zope.publisher.interfaces import IPublishTraverse
from ZPublisher.BaseRequest import DefaultPublishTraverse
import Shared.DC.ZRDB.DA
from Products.ZSQLMethods.SQL import SQLConnectionIDs

from RestDbInterface import *


class RestDbJsonStore(RestDbInterface):
    """Object for RESTful access to JSON objects
    path schema: /db/{schema}/{table}/{tag}/{type}/{item}
    """
    implements(IPublishTraverse)
    
    meta_type="RESTjson"
    manage_options=Folder.manage_options+(
        {'label':'Config','action':'manage_editRestDbJsonStoreForm'},
        )

    # management templates
    manage_editRestDbJsonStoreForm=PageTemplateFile('zpt/editRestDbJsonStore',globals())

    # JSON_* templates are scripts
    def JSON_index(self,data):
        """JSON index function"""
        self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
        json.dump(data, self.REQUEST.RESPONSE)        

    
    def __init__(self, id, title, connection_id=None):
        """init"""
        self.id = id
        self.title = title
        # database connection id
        self.connection_id = connection_id

    def getJsonString(self,object):
        """returns a JSON formatted string from object"""
        return json.dumps(object)


    def publishTraverse(self,request,name):
        """change the traversal"""
        # get stored path
        path = request.get('restdb_path', [])
        logging.debug("publishtraverse: name=%s restdb_path=%s"%(name,path))
        
        if name in ("index_html", "PUT"):
            # end of traversal
            if request.get("method") == "POST" and request.get("action",None) == "PUT":
                # fake PUT by POST with action=PUT
                name = "PUT"
                
            return getattr(self, name)
            #TODO: should we check more?
        else:
            # traverse
            if len(path) == 0:
                # first segment
                if name == 'db':
                    # virtual path -- continue traversing
                    path = [name]
                    request['restdb_path'] = path
                else:
                    # try real path
                    tr = DefaultPublishTraverse(self, request)
                    ob = tr.publishTraverse(request, name)
                    return ob
            else:
                path.append(name)

        # continue traversing
        return self
 
    def index_html(self,REQUEST,RESPONSE):
        """index method"""
        # ReST path was stored in request
        path = REQUEST.get('restdb_path',[])
        
        # type and format are real parameter
        resultFormat = REQUEST.get('format','HTML').upper()
        queryType = REQUEST.get('type',None)
        
        logging.debug("index_html path=%s resultFormat=%s queryType=%s"%(path,resultFormat,queryType))

        if queryType is not None:
            # non-empty queryType -- look for template
            pt = getattr(self.template, "%s_%s"%(resultFormat,queryType), None)
            if pt is not None:
                return pt(format=resultFormat,type=queryType,path=path)
            
        if len(path) == 3:
            # list of tags
            return self.showListOfTags(resultFormat=resultFormat,schema=path[1],table=path[2])
        elif len(path) == 4:
            # list of types
            return self.showListOfTypes(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3])
        elif len(path) == 5:
            # list of types
            return self.showListOfItems(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3],type=path[4])
        elif len(path) == 6:
            # show item
            return self.showItem(resultFormat=resultFormat,schema=path[1],table=path[2],tag=path[3],type=path[4],item=path[5])
        
        # don't know what to do
        return str(REQUEST)

    def PUT(self, REQUEST, RESPONSE):
        """
        Implement WebDAV/HTTP PUT/FTP put method for this object.
        """
        path = REQUEST.get('restdb_path',[])
        logging.debug("RestDbInterface PUT (path=%s)"%repr(path))
        logging.debug("PUT request=%s"%REQUEST)
        #logging.debug("req=%s"%REQUEST)
        #self.dav__init(REQUEST, RESPONSE)
        #self.dav__simpleifhandler(REQUEST, RESPONSE)
        # ReST path was stored in request
        if len(path) == 6:
            schema = path[1]
            table = path[2]
            tag = path[3]
            type = path[4]
            item = path[5]
            # maybe the data was form-encoded
            value = REQUEST.get('json_string', None)
            if value is None:
                # then maybe in the body (the hard way...)
                REQUEST.stdin.seek(0)
                value = REQUEST.stdin.read()

            logging.debug("put with schema=%s table=%s tag=%s type=%s item=%s value=%s"%(schema,table,tag,type,item,value))
            res = self.storeItem(schema,table,tag,type,item,value)
            
        else:
            # 400 Bad Request
            RESPONSE.setStatus(400)
            return
    
    def showListOfTags(self,resultFormat,schema,table):
        """shows the list of existing tags"""
        tags = self.getListOfTags(schema, table)
        if resultFormat == 'JSON':
            self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
            json.dump(tags, self.REQUEST.RESPONSE)        
        else:
            json.dump(tags, self.REQUEST.RESPONSE)        
        
    def getListOfTags(self,schema,table):
        """returns the list of existing tags"""
        logging.debug("getlistoftags schema=%s table=%s"%(schema,table))
        sql = 'select distinct json_tag from "%s"."%s"'%(schema,table)
        res = self.executeSQL(sql)
        if len(res['rows']) > 0:
            tags = [r[0] for r in res['rows']]
            return tags
        
        return []
    
    def showListOfTypes(self,resultFormat,schema,table,tag):
        """shows the list of existing types"""
        types = self.getListOfTypes(schema, table,tag)
        if resultFormat == 'JSON':
            self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
            json.dump(types, self.REQUEST.RESPONSE)        
        else:
            json.dump(types, self.REQUEST.RESPONSE)        
        
    def getListOfTypes(self,schema,table,tag):
        """returns the list of existing types"""
        logging.debug("getlistoftypes schema=%s table=%s tag=%s"%(schema,table,tag))
        sql = 'select distinct json_type from "%s"."%s" where json_tag = %%s'%(schema,table)
        res = self.executeSQL(sql,(tag,))
        if len(res['rows']) > 0:
            tags = [r[0] for r in res['rows']]
            return tags
        
        return []
    
    def showListOfItems(self,resultFormat,schema,table,tag,type):
        """shows the list of existing items"""
        recursive = self.REQUEST.get("recursive", False)
        if recursive:
            items = self.getListOfItemsAndValues(schema, table, tag, type)
            # items contain JSON-strings
            its = ",".join(['{"key":"%s","val":%s}'%(i[0],i[1]) for i in items])
            its = "[%s]"%its
            if resultFormat == 'JSON':
                self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
                # we assume utf-8
                self.REQUEST.RESPONSE.write(utf8ify(its))       
            else:
                # we assume utf-8
                self.REQUEST.RESPONSE.write(utf8ify(its))       

        else:
            items = self.getListOfItems(schema, table, tag, type)
            if resultFormat == 'JSON':
                self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
                json.dump(items, self.REQUEST.RESPONSE)        
            else:
                json.dump(items, self.REQUEST.RESPONSE)
                
        
    def getListOfItems(self,schema,table,tag,type):
        """returns the list of existing items"""
        logging.debug("getlistofitems schema=%s table=%s tag=%s type=%s"%(schema,table,tag,type))
        sql = 'select distinct json_item from "%s"."%s" where json_tag = %%s and json_type = %%s'%(schema,table)
            
        res = self.executeSQL(sql,(tag,type))
        if len(res['rows']) > 0:
            items = [r[0] for r in res['rows']]
            return items
        
        return []
    
    def getListOfItemsAndValues(self,schema,table,tag,type):
        """returns the list of existing items and their values"""
        logging.debug("getlistofitemsandvalues schema=%s table=%s tag=%s type=%s"%(schema,table,tag,type))
        sql = 'select json_item, json_value from "%s"."%s" where json_tag = %%s and json_type = %%s'%(schema,table)
            
        res = self.executeSQL(sql,(tag,type))
        if len(res['rows']) > 0:
            return res['rows']
        
        return []
    
    def showItem(self,resultFormat,schema,table,tag,type,item):
        """shows the item"""
        item = self.getItem(schema, table, tag, type, item)
        # item is a string
        if resultFormat == 'JSON':
            self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
            self.REQUEST.RESPONSE.write(item)        
        else:
            self.REQUEST.RESPONSE.write(item)        
        
    def getItem(self,schema,table,tag,type,item):
        """returns the item"""
        logging.debug("getitem schema=%s table=%s tag=%s type=%s item=%s"%(schema,table,tag,type,item))
        sql = 'select json_value from "%s"."%s" where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
        res = self.executeSQL(sql,(tag,type,item))
        if len(res['rows']) > 0:
            # just one item
            item = res['rows'][0][0]
            return item
        
        return "{}"

    def storeItem(self,schema,table,tag,type,item,value):
        """sets the item to value"""
        logging.debug("storeitem schema=%s table=%s tag=%s type=%s item=%s value=%s"%(schema,table,tag,type,item,value))
        # see if the item exists
        sql = 'select 1 from "%s"."%s" where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
        res = self.executeSQL(sql,(tag,type,item))
        if len(res['rows']) > 0:
            # then update
            sql = 'update "%s"."%s" set json_value = %%s where json_tag = %%s and json_type = %%s and json_item = %%s'%(schema,table)
        
        else:
            # then insert
            sql = 'insert into "%s"."%s" (json_value, json_tag, json_type, json_item) values (%%s,%%s,%%s,%%s)'%(schema,table)
        
        self.executeSQL(sql,(value,tag,type,item), hasResult=False)
        return "Ok"
    
manage_addRestDbJsonStoreForm=PageTemplateFile('zpt/addRestDbJsonStore',globals())

def manage_addRestDbJsonStore(self, id, title='', label='', description='',
                     createPublic=0,
                     createUserF=0,
                     REQUEST=None):
        """Add a new object with id *id*."""
    
        ob=RestDbJsonStore(str(id),title)
        self._setObject(id, ob)
        
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')