'''
Created on 14.2.2011

@author: casties
'''

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

from DBInterface import *


class ZDBInterfaceFolder(DBInterface, Folder):
    """Folder for database queries
    """
    
    meta_type="ZDBInterfaceFolder"
    manage_options=Folder.manage_options+(
        {'label':'Config','action':'manage_editZDBInterfaceFolderForm'},
        )

    # management templates
    manage_editZDBInterfaceFolderForm=PageTemplateFile('zpt/editZDBInterfaceFolder',globals())

    
    def __init__(self, id, title, connection_id=None, autocommit=False, default_search='bw'):
        """init"""
        self.id = id
        self.title = title
        # database connection id
        self.connection_id = connection_id
        # set db connection to autocommit
        self.autocommit = autocommit
        # default text search mode
        self.default_search = default_search

    def getSQLQuery(self, table, argv, ignore_empty=False):
        """returns dict with SQL query string and args. 
        ignore_empty: remove fields with empty string values from query."""
        logging.debug("getSQLquery table=%s argv=%s ignore_empty=%s"%(table,argv,ignore_empty))
        args = []
        select = "*"
        order = None
        wheres = []
        whereOp = "AND"
        processed = {}
        unprocessed = {}
        ignored = {}
        limit = None
        offset = None

        def doOp(op, param, val):
            # handles comparison operations in WHERE clause
            logging.debug("doop op=%s param=%s val=%s"%(op,param,val))
            if ignore_empty and not val:
                # ignore parameter
                logging.debug("  param=%s ignored"%(param))
                return
            
            if isinstance(val, list): 
                # join multiple parameters with spaces (makes sense with checkbox and -op=all)
                val = " ".join(val)
                
            # string comparisons are case-insensitive
            if isinstance(val, basestring):
                param = "LOWER(%s)"%param
                val = val.lower()
                
            if op == "eq":
                wheres.append(param + " = %s")
                args.append(val)
            elif op == "lt":
                wheres.append(param + " < %s")
                args.append(val)
            elif op == "gt":
                wheres.append(param + " > %s")
                args.append(val)
            elif op == "ew":
                wheres.append(param + " ILIKE %s")
                args.append("%" + val)
            elif op == "bw":
                wheres.append(param + " ILIKE %s")
                args.append(val + "%")
            elif op == "ct":
                wheres.append(param + " ILIKE %s")
                args.append("%" + val + "%")
            elif op == "all":
                # p="a b c" -> WHERE (p ILIKE '%a%' AND p ILIKE '%b%' AND p ILIKE '%c%')
                words = []
                for word in val.split(" "):
                    words.append(param + " ILIKE %s")
                    args.append("%" + word + "%")
                wheres.append("(" + " AND ".join(words) + ")")
            elif op == "one":
                # p="a b c" -> WHERE (p ILIKE '%a%' OR p ILIKE '%b%' OR p ILIKE '%c%')
                words = []
                for word in val.split(" "):
                    words.append(param + " ILIKE %s")
                    args.append("%" + word + "%")
                wheres.append("(" + " OR ".join(words) + ")")
            elif op == "in":
                # p="a b c" -> WHERE p IN ('a', 'b', 'c')
                words = []
                for word in val.split(" "):
                    words.append("%s")
                    args.append(word)
                wheres.append(param + " IN (" + ", ".join(words) + ")")
            else:
                logging.error("getSQLquery: unknown op=%s!"%op)
                
            return

        # evaluate argv
        for (key, val) in argv.items():
            logging.debug("process key=%s val=%s"%(key,val))
            if key in processed:
                # parameter has been processed
                logging.debug("  key=%s processed"%(key))
                continue
            
            # beginning of a command should always be "_"
            if key[0] == "-":
                key = "_" + key[1:]
      
            if key == "_select":
                # SELECT expression
                select = sqlName(val, more="*,")
            elif key == "_sort":
                # sort i.e. ORDER BY expression
                order = sqlName(val, more=",")
            elif key == "_lop":
                # logical operation joining WHERE clauses
                whereOp = sqlName(val)
            elif key == "_max" or key == "_size":
                # number of results
                limit = sqlName(val)
            elif key == "_skip" or key == "_start":
                # start at result number
                offset = sqlName(val)
            elif key[:3] == "_op":
                # operation parameters _op_param=val
                param = sqlName(key[4:])
                logging.debug("param=%s key=%s val=%s"%(param,key,val))
                if param in argv:
                    doOp(val, param, argv[param])
                    processed[param] = True
                else:
                    # no corresponding parameter
                    logging.error("getSQLquery: param=%s for op not found!"%param)
            else:
                # parameter=value pair
                unprocessed[key] = val
                
        # process remaining parameters (without _op)
        for (key, val) in unprocessed.items():
            if key not in processed:
                param = sqlName(key)
                # default operation
                doOp(self.default_search, param, val)

        # join it all
        query = "SELECT %s FROM %s"%(select, table)
        if wheres:
            query += " WHERE " + (" " + whereOp + " ").join(wheres)
        
        if order:
            query += " ORDER BY " + order
            
        if limit:
            query += " LIMIT " + limit
            
        if offset:
            query += " OFFSET " + offset

        return {'query' : query, 'args' : args}

    def ZDBInlineSearch(self, _table=None, _ignore_empty=True, **argv):
        """returns result set from search with given parameters"""
        query = self.getSQLQuery(_table, argv, ignore_empty= _ignore_empty)
        result = self.executeZSQL(query['query'], query['args'])
        return result
    
    def manage_editZDBInterfaceFolder(self, title=None, connection_id=None, autocommit=None, default_search=None,
                     REQUEST=None):
        """Change the object"""
        logging.debug("editZDBInterfaceFolder title=%s, connection_id=%s, autocommit=%s default_search=%s"%(title,connection_id,autocommit,default_search))
        if title is not None:
            self.title = title
            
        if connection_id is not None:
            self.connection_id = connection_id
                
        if default_search is not None:
            self.default_search = default_search
                
        self.autocommit = (autocommit == "on")
                
        #checkPermission=getSecurityManager().checkPermission
        REQUEST.RESPONSE.redirect('manage_main')


manage_addZDBInterfaceFolderForm=PageTemplateFile('zpt/addZDBInterfaceFolder',globals())

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