'''
Created on 2.9.2010

@author: casties, fknauft
'''

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

from RestDbInterface import *


gisToSqlTypeMap = {
                   "text": "text",
                   "number": "numeric",
                   "id": "text",
                   "gis_id": "text",
                   "coord_lat": "numeric",
                   "coord_lon": "numeric"
                   }

class RestDbGisApi(RestDbInterface):
    """Object for RESTful GIS database queries
    path schema: /db/{schema}/{table}/
    omitting table gives a list of schemas
    omitting table and schema gives a list of schemas 
    """
    
    meta_type="RESTgis"

    # data templates
    GIS_schema_table = PageTemplateFile('zpt/GIS_schema_table', globals())

    def checkTableMetaPermission(self,action,schema,table,user=None):
        """returns if the requested action on the table is allowed"""
        logging.debug("checktablemetapermissions action=%s schema=%s table=%s user=%s"%(action,schema,table,user))
        if user is None:
            user = self.REQUEST.get('AUTHENTICATED_USER',None)
        logging.debug("user=%s"%user)
        # TODO: what now?
        return True

    def setTableMetaTypes(self,schema,table,fields):
        """sets the GIS meta information for table"""
        logging.debug("settablemetatypes schema=%s, table=%s, fields=%s"%(schema,table,fields))
        gisIdField = None
        latField = None
        lonField = None
        for f in fields:
            t = f['type']
            if t == 'gis_id':
                gisIdField = f['name']
            elif t == 'coord_lat':
                latField = f['name']
            elif t == 'coord_lon':
                lonField = f['name']
                
        res = self.executeSQL("select * from public.metadata where tablename=%s", (table,))
        if len(res['rows']) > 0:
            # meta record exists
            if gisIdField is not None:
                self.executeSQL('update public.metadata set "attribute with gis_id" = %s where tablename = %s', (gisIdField,table), hasResult=False)
                
        else:
            # new meta record
            if gisIdField is not None:
                self.executeSQL('insert into public.metadata ("tablename", "attribute with gis_id") values (%s, %s)', (table,gisIdField), hasResult=False)
    
        
    def showTable(self,resultFormat='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', '*')
        
        # GIS gets special treatment
        if resultFormat=="GIS":
            id = REQUEST.get('id',[])           
            doc = REQUEST.get('doc',None)
            return self.showGoogleMap(schema=schema,table=table,id=id,doc=doc)
        
        elif resultFormat=="KML_URL":
            id = REQUEST.get('id',[])           
            doc = REQUEST.get('doc',None)
            return self.getKmlUrl(schema=schema,table=table,id=id,doc=doc)

        # everything else has its own template
        pt = getattr(self.template, '%s_schema_table'%resultFormat, None)
        if pt is None:
            return "ERROR!! template %s_schema_table not found"%resultFormat
        
        data = self.getTable(schema,table)
        return pt(data=data,tablename=table)
 

    def createEmptyTable(self,schema,table,fields):
        """create a table with the given fields
           returns list of created fields"""
        logging.debug("createEmptyTable")
        sqlFields = []
        for f in fields:
            if isinstance(f,dict):
                # {name: XX, type: YY}
                name = sqlName(f['name'])
                type = f['type']
                sqltype = gisToSqlTypeMap[type]
            
            else:
                # name only
                name = sqlName(f)
                type = 'text'
                sqltype = 'text'
                
            sqlFields.append({'name':name, 'type':type, 'sqltype':sqltype})
            
        if self.checkTableMetaPermission("create", schema, table):
            self.executeSQL('drop table if exists "%s"."%s"'%(schema,table),hasResult=False)
            fieldString = ", ".join(['"%s" %s'%(f['name'],f['sqltype']) for f in sqlFields])
            sqlString = 'create table "%s"."%s" (%s)'%(schema,table,fieldString)
            logging.debug("createemptytable: SQL=%s"%sqlString)
            self.executeSQL(sqlString,hasResult=False)
            self.setTableMetaTypes(schema,table,sqlFields)
            return sqlFields
        else:
            logging.warning("create table not allowed!")
            # throw exception?
            return None
    
    

    # Methods for GoogleMaps creation
    def showGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
        logging.debug("showGoogleMap")
        data = self.getDataForGoogleMap(schema,table,id,doc)
        kmlFileName=self.getKMLname(data=data,table=table)
        initializeStringForGoogleMaps="""onload=\"initialize(\'http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kmlFileName+"""\')\""""#+str(data)
        initializeStringForGoogleMaps=initializeStringForGoogleMaps.replace("None","0")
        googleMap_page=self.htmlHead()+str(self.getGoogleMapString(kml=initializeStringForGoogleMaps))
        return googleMap_page

    def getKmlUrl(self,schema='chgis',table='mpdl',id=[],doc=None):
        logging.debug("getKmlUrl")
        data = self.getDataForGoogleMap(schema,table,id,doc)
        kml=self.getKMLname(data=data,table=table)
        baseUrl = self.absolute_url()
        return "%s/daten/%s"%(baseUrl,kml)

    def getDataForGoogleMap(self,schema='chgis',table='mpdl',id=[],doc=None):
        logging.debug("getDataForGoogleMap")
        qstr="SELECT * FROM "+schema+"."+table
        try:
            if id!=[]:
                qstr=qstr+" WHERE "
                for id_item in id.split(","):
                    if table=='mpdl':
                        qstr=qstr+" mpdl_xmlsource_id = '"+id_item+ "' OR"
                    else:
                        qstr=qstr+" cast(id as text) LIKE '"+id_item+ "' OR"
                qstr=str(qstr).rsplit(" ",1)[0] #to remove last " and "
            data=self.ZSQLSimpleSearch(qstr)
            return data
        except:
            return qstr

    def getKMLname(self,data=[],table=""):
        logging.debug("getKMLname")
        #session=context.REQUEST.SESSION
        kml4Marker="<kml xmlns=\'http://www.opengis.net/kml/2.2\'><Document><Style id=\'marker_icon\'><IconStyle><scale>15</scale><Icon><href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href></Icon></IconStyle></Style>\n"
        initializeStringForGoogleMaps=""
        #doLine=container.getVar('doLine')
        # Mapping a set of points from table-based SQL-query:
        if data!=None:
            try:
                SQL='SELECT "attribute with gis_id" FROM public.metadata WHERE tablename = %s'
                res = self.executeSQL(SQL, (table,))
                gisIDattribute = res['rows'][0][0]
            except:
                return "table not registered within metadata"
            
            for dataset in data:
                try:  
                    xCoord=getattr(dataset,'longitude')
                    yCoord=getattr(dataset,'latitude')
                except:
                    try:
                        xCoord=getattr(dataset,'x_coord')
                        yCoord=getattr(dataset,'y_coord')
                    except:
                        #try:
                        gisID=getattr(dataset,gisIDattribute)
                        coords=self.getPoint4GISid(gisID)
                        if coords!=None:
                            xCoord=coords[0]
                            yCoord=coords[1]
                        # except:
                        #     return "no coordinates found"
                        
                if float(xCoord)!=0:
                    if float(yCoord)!=0:
                        kml4Marker=kml4Marker+"<Placemark>"
                        kml4Marker=kml4Marker+"<description> <![CDATA[<b>"
                        for values in dataset:
                            if values != (None, None):
                                if str(values).find('name')>-1:
                                    kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
                                    continue
                                elif str(values).find('place')>-1:
                                    kml4Marker=kml4Marker+"<name>"+str(values[1])+"</name>\n"
                                    continue
                        
                                kml4Marker=kml4Marker+str(values)+": "
                                attribute_string=str(values).replace("'","__Apostroph__")
                                attribute_string=str(attribute_string).replace('"','__DoubleApostroph__')
                                attribute_string=str(attribute_string).replace(';','__$$__')
                                attribute_string=str(attribute_string).replace('&','&amp;')
                                if str(attribute_string).find('http')>-1:
                                    attribute_string='<A HREF=' + str(attribute_string) + ' target=_blank>' + str(attribute_string) + '</A>'
                                kml4Marker=kml4Marker+attribute_string+"</a><br>\n"
                                  
                        kml4Marker=kml4Marker+"]]></description>\n"
                        kml4Marker=kml4Marker+"<styleURL>#marker_icon</styleURL>\n"
                        kml4Marker=kml4Marker+"<Point>"
                              
                        kml4Marker=kml4Marker+"<coordinates>"+str(xCoord)+","+str(yCoord)+",0</coordinates>\n"
                        kml4Marker=kml4Marker+"</Point>\n"
                        kml4Marker=kml4Marker+"</Placemark>\n"
        
            kml4Marker=kml4Marker+"</Document>\n</kml>"
            kmlFileName="marker"+str(time.time())+".kml"
        
            #kml4Marker=str(kml4Marker).replace('&','$$')
            #kml4Marker=str(kml4Marker).replace(';','__$$__')
            #kml4Marker=str(kml4Marker).replace('#','__SHARP__')
            isLoadReady='false'
            while isLoadReady=='false':
                isLoadReady=self.RESTwrite2File(self.daten,kmlFileName,kml4Marker)

        return kmlFileName

    def getGoogleMapString(self,kml):
        logging.debug("getGoogleMapString")
        printed= '<body %s> '%kml +"""\n <div id="map_canvas" style="width: 98%; height: 95%"> </div> \n </body>" \n </html>"""
        return printed
    
    def getPoint4GISid(self,gis_id):
        j=0
        coords=(0,0)
        if gis_id != None:
         while (True):
           j=j+1
           if (j>100):                                         # FJK: just to prevent endless loops
             break
           if (gis_id.isdigit()):                              # FJK: regular exit from while-loop
             break
           else:
             gis_id=gis_id.strip('abcdefghijklmnopqrstuvwxyz_') # FJK: to strip all letters 
             gis_id=gis_id.strip()                              # FJK: to strip all whitespaces
         resultpoint = [0,0]
         results = None
         try:
            if int(gis_id)>0:
                SQL="SELECT x_coord,y_coord FROM chgis.chgis_coords WHERE gis_id LIKE cast("+ str(gis_id) +" as text);"
                results=self.ZSQLSimpleSearch(SQL)
                #print results
                if results != None:
                    for result in results:
                        resultpoint=[getattr(result,str('x_coord')),getattr(result,str('y_coord'))]
                if resultpoint !=[0,0]: 
                    return resultpoint
            else:
                coords=self.getCoordsFromREST_gisID(joinid)
                SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (" +gis_id+ "," +coords[0][1]+ "," +coords[0][0]+ "); ANALYZE chgis.chgis_coords;"
                returnstring=self.ZSQLSimpleSearch(SQL)
                return coords[0]
         except:
            return "gis_id not to interpretable:"+str(gis_id)
        else:
            return coords[0]

    def getCoordsFromREST_gisID(self,gis_id):
        coordlist=[]
        i=0
        while (i<5 and coordlist==[]):
         
            urlresponse=container.urlFunctions.zUrlopenParseString(container.urlFunctions.zUrlopenRead("http://chgis.hmdc.harvard.edu/xml/id/"+gis_id))
            baseDocElement=container.urlFunctions.zUrlopenDocumentElement(urlresponse)
            childnodes=container.urlFunctions.zUrlopenChildNodes(baseDocElement)
            itemnodes=container.urlFunctions.zUrlopenGetElementsByTagName(baseDocElement,'item')
        
            for i in range(0,container.urlFunctions.zUrlopenLength(itemnodes)):
                itemnode=container.urlFunctions.zUrlopenGetItem(itemnodes,i)
                itemspatialnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemnode,'spatial')
            for j in range(0,container.urlFunctions.zUrlopenLength(itemspatialnodes)):
                coord=[]
                itemspatialnode= container.urlFunctions.zUrlopenGetItem(itemspatialnodes,j)
                itemspatiallatnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_latitude')
                for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallatnodes)):
                    itemspatiallatnode= container.urlFunctions.zUrlopenGetItem(itemspatiallatnodes,k)
                    coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallatnode))
            itemspatiallngnodes=container.urlFunctions.zUrlopenGetElementsByTagName(itemspatialnode,'degrees_longitude')
            for k in range(0,container.urlFunctions.zUrlopenLength(itemspatiallngnodes)):
                itemspatiallngnode= container.urlFunctions.zUrlopenGetItem(itemspatiallngnodes,k)
                coord.append(container.urlFunctions.zUrlopenGetTextData(itemspatiallngnode))
            coordlist.append(coord)
            gis_id= "_"+gis_id
        return coordlist
       
# End for GoogleMaps creation

    def RESTwrite2File(self,datadir, name,text):
        logging.debug("RESTwrite2File datadir=%s name=%s"%(datadir,name))
        try:
            import cStringIO as StringIO
        except:
            import StringIO

        # make filehandle from string
        textfile = StringIO.StringIO(text)
        fileid=name
        if fileid in datadir.objectIds():
            datadir.manage_delObjects(fileid)
        fileInZope=datadir.manage_addFile(id=fileid,file=textfile)
        return "Write successful"
        
    def manage_editRestDbGisApi(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_addRestDbGisApiForm=PageTemplateFile('zpt/addRestDbGisApi',globals())

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