'''
Created on 19.5.2010

@author: casties
'''

from OFS.Folder import Folder
from Products.PageTemplates.PageTemplateFile import PageTemplateFile
from AccessControl import getSecurityManager, Unauthorized
import logging
import re
import json
import time

from zope.interface import implements
from zope.publisher.interfaces import IPublishTraverse
from ZPublisher.BaseRequest import DefaultPublishTraverse

from DBInterface import *


class RestDbInterface(DBInterface, Folder):
    """Object for RESTful database queries
    path schema: /db/{schema}/{table}/
    omitting table gives a list of schemas
    omitting table and schema gives a list of schemas 
    """
    implements(IPublishTraverse)
    
    meta_type="RESTdb2"
    manage_options=Folder.manage_options+(
        {'label':'Config','action':'manage_editRestDbInterfaceForm'},
        )

    # management templates
    manage_editRestDbInterfaceForm=PageTemplateFile('zpt/editRestDbInterface',globals())

    # data templates
    XML_index = PageTemplateFile('zpt/XML_index', globals())
    XML_schema = PageTemplateFile('zpt/XML_schema', globals())
    XML_schema_table = PageTemplateFile('zpt/XML_schema_table', globals())
    HTML_index = PageTemplateFile('zpt/HTML_index', globals())
    HTML_schema = PageTemplateFile('zpt/HTML_schema', globals())
    HTML_schema_table = PageTemplateFile('zpt/HTML_schema_table', globals())
    JSONHTML_index = PageTemplateFile('zpt/JSONHTML_index', globals())
    JSONHTML_schema = PageTemplateFile('zpt/JSONHTML_schema', globals())
    JSONHTML_schema_table = PageTemplateFile('zpt/JSONHTML_schema_table', globals())
    # JSON_* templates are scripts
    def JSON_index(self):
        """JSON index function"""
        self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
        json.dump(self.getListOfSchemas(), self.REQUEST.RESPONSE)        

    def JSON_schema(self,schema):
        """JSON index function"""
        self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
        json.dump(self.getListOfTables(schema), self.REQUEST.RESPONSE)        

    def JSON_schema_table(self,schema,table):
        """JSON index function"""
        self.REQUEST.RESPONSE.setHeader("Content-Type", "application/json")
        json.dump(self.getTable(schema, table), 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
        # create template folder
        self.manage_addFolder('template')
        

    def getRestDbUrl(self):
        """returns url to the RestDb instance"""
        return self.absolute_url()
 
    def getJsonString(self,object):
        """returns a JSON formatted string from object"""
        return json.dumps(object)


    def isAllowed(self,action,schema,table,user=None):
        """returns if the requested action on the table is allowed"""
        if user is None:
            user = self.REQUEST.get('AUTHENTICATED_USER',None)
        logging.debug("isAllowed action=%s schema=%s table=%s user=%s"%(action,schema,table,user))
        # no default policy!
        return True


    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) == 1:
            # list of schemas
            return self.showListOfSchemas(format=resultFormat)
        elif len(path) == 2:
            # list of tables
            return self.showListOfTables(format=resultFormat,schema=path[1])
        elif len(path) == 3:
            # table
            if REQUEST.get("method") == "POST" and REQUEST.get("create_table_file",None) is not None:
                # POST to table to check
                return self.checkTable(format=resultFormat,schema=path[1],table=path[2])
            # else show table
            return self.showTable(format=resultFormat,schema=path[1],table=path[2])
        
        # don't know what to do
        return str(REQUEST)

    def showTable(self,format='XML',schema='public',table=None,REQUEST=None,RESPONSE=None):
        """returns PageTemplate with tables"""
        logging.debug("showtable")
        if REQUEST is None:
            REQUEST = self.REQUEST
            
        # should be cross-site accessible 
        if RESPONSE is None:
            RESPONSE = self.REQUEST.RESPONSE
            
        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')
        
        # everything else has its own template
        pt = getattr(self.template, '%s_schema_table'%format, None)
        if pt is None:
            return "ERROR!! template %s_schema_table not found"%format
        
        #data = self.getTable(schema,table)
        return pt(schema=schema,table=table)
 
    def getTable(self,schema='public',table=None,sortBy=1,username='guest'):
        """return table data"""
        logging.debug("gettable")
        if sortBy:
            data = self.executeSQL('select * from "%s"."%s" order by %s'%(schema,table,sortBy))
        else:
            data = self.executeSQL('select * from "%s"."%s"'%(schema,table))
        return data

    def hasTable(self,schema='public',table=None,username='guest'):
        """return if table exists"""
        logging.debug("hastable")
        data = self.executeSQL('select 1 from information_schema.tables where table_schema=%s and table_name=%s',(schema,table))
        ret = bool(data['rows'])
        return ret

    def showListOfTables(self,format='XML',schema='public',REQUEST=None,RESPONSE=None):
        """returns PageTemplate with list of tables"""
        logging.debug("showlistoftables")
        # should be cross-site accessible 
        if RESPONSE is None:
            RESPONSE = self.REQUEST.RESPONSE
        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')

        pt = getattr(self.template, '%s_schema'%format, None)
        if pt is None:
            return "ERROR!! template %s_schema not found"%format
        
        #data = self.getListOfTables(schema)
        return pt(schema=schema)
 
    def getListOfTables(self,schema='public',username='guest'):
        """return list of tables"""
        logging.debug("getlistoftables")
        # get list of fields and types of db table
        #qstr="""SELECT c.relname AS tablename FROM pg_catalog.pg_class c
        #    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        #    WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
        #    AND pg_catalog.pg_table_is_visible(c.oid) 
        #    AND c.relname ORDER BY 1"""
        qstr = """SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' 
                        AND table_schema = %s ORDER BY 1"""
        data=self.executeSQL(qstr,(schema,))
        return data

    def showListOfSchemas(self,format='XML',REQUEST=None,RESPONSE=None):
        """returns PageTemplate with list of schemas"""
        logging.debug("showlistofschemas")
        # should be cross-site accessible 
        if RESPONSE is None:
            RESPONSE = self.REQUEST.RESPONSE
        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')

        pt = getattr(self.template, '%s_index'%format, None)
        if pt is None:
            return "ERROR!! template %s_index not found"%format
        
        #data = self.getListOfSchemas()
        return pt()
 
    def getListOfSchemas(self,username='guest'):
        """return list of schemas"""
        logging.debug("getlistofschemas")
        # TODO: really look up schemas
        data={'fields': (('schemas',),), 'rows': [('public',),]}
        return data
    
    def manage_editRestDbInterface(self, title=None, connection_id=None,
                     REQUEST=None):
        """Change the object"""
        if title is not None:
            self.title = title
            
        if connection_id is not None:
            self.connection_id = connection_id
                
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')

        
manage_addRestDbInterfaceForm=PageTemplateFile('zpt/addRestDbInterface',globals())

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