'''
Created on 19.5.2010

@author: casties
'''

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

from zope.interface import implements
from zope.publisher.interfaces import IPublishTraverse
from ZPublisher.BaseRequest import DefaultPublishTraverse
#from zope.publisher.interfaces import NotFound 
#from zope.app import zapi 
#from zope.component import queryMultiAdapter
import Shared.DC.ZRDB.DA
from Products.ZSQLMethods.SQL import SQLConnectionIDs


class RestDbInterface(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="RESTdb"
    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())

    
    
    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')
        # create data folder
        #self.manage_addFolder('daten')


    def getCursor(self):
        """returns fresh DB cursor"""
        conn = getattr(self,"_v_database_connection",None)
        if conn is None:
            # create a new connection object
            try:
                if self.connection_id is None:
                    # try to take the first existing ID
                    connids = SQLConnectionIDs(self)
                    if len(connids) > 0:
                        connection_id = connids[0][0]
                        self.connection_id = connection_id
                        logging.debug("connection_id: %s"%repr(connection_id))

                da = getattr(self, self.connection_id)
                da.connect('')
                # we copy the DAs database connection
                conn = da._v_database_connection
                #conn._register() # register with the Zope transaction system
                self._v_database_connection = conn
            except Exception, e:
                raise IOError("No database connection! (%s)"%str(e))
        
        cursor = conn.getcursor()
        return cursor
    
    def executeSQL(self, query, *args):
        """execute query with args on database and return all results.
        result format: {"fields":fields, "rows":data}"""
        cur = self.getCursor()
        cur.execute(query, args)
        # description of returned fields 
        fields = cur.description
        # get all data in an array
        data = cur.fetchall()
        cur.close()
        return {"fields":fields, "rows":data}

    
    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 == "index_html":
            # end of traversal
            return self.index_html
            #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
        format = REQUEST.get('format','HTML').upper()
        type = REQUEST.get('type',None)
        
        # id and doc are used for GoogleMaps content
        id = REQUEST.get('id',[])           
        doc = REQUEST.get('doc',None)
       
        logging.debug("index_html path=%s format=%s type=%s"%(path,format,type))
        RESPONSE.setHeader('Access-Control-Allow-Origin', '*')

        if type is not None:
            # non-empty type -- look for template
            pt = getattr(self.template, "%s_%s"%(format,type), None)
            if pt is not None:
                return pt(format=format,type=type,path=path)
            
        if len(path) == 1:
            # list of schemas
            return self.showListOfSchemas(format=format)
        elif len(path) == 2:
            # list of tables
            return self.showListOfTables(format=format,schema=path[1])
        elif len(path) == 3:
            # GIS
            if format=="GIS":
                return self.showGoogleMap(schema=path[1],table=path[2],id=id,doc=doc)
            if format=="KML_URL":
                return self.getKmlUrl(schema=path[1],table=path[2],id=id,doc=doc)
            # table
            return self.showTable(format=format,schema=path[1],table=path[2])
        
        # don't know what to do
        return str(REQUEST)


    def showTable(self,format='XML',schema='public',table=None):
        """returns PageTemplate with tables"""
        logging.debug("showtable")
        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(data=data,tablename=table)
 
 
    def getTable(self,schema='public',table=None,username='guest'):
        """return table data"""
        logging.debug("gettable")
        data = self.executeSQL("select * from %s.\"%s\" "%(schema,table))
        return data

    def showListOfTables(self,format='XML',schema='public'):
        """returns PageTemplate with list of tables"""
        logging.debug("showlistoftables")
        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(data=data,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 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)"""
        #qstr="select attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) from pg_attribute, pg_class where attrelid = pg_class.oid and pg_attribute.attnum > 0"
        data=self.executeSQL(qstr)
        return data

    def showListOfSchemas(self,format='XML'):
        """returns PageTemplate with list of schemas"""
        logging.debug("showlistofschemas")
        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(data=data)
 
    def getListOfSchemas(self,username='guest'):
        """return list of schemas"""
        logging.debug("getlistofschemas")
        # TODO: really look up schemas
        data={'fields': (('schemas',),), 'rows': [('public','chgis','mpdl'),]}
        return data

    # Methods for GoogleMaps creation
    def showGoogleMap(self,schema='public',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=str(self.htmlHead())+str(self.getGoogleMapString(initializeStringForGoogleMaps))
        return googleMap_page
        
    def kml_header(self):
        return """<?xml version=\"1.0\" encoding=\"UTF-8\"?>
    <kml 
    xmlns=\"http://www.opengis.net/kml/2.2\" 
    xmlns:gx=\"http://www.google.com/kml/ext/2.2\" 
    xmlns:kml=\"http://www.opengis.net/kml/2.2\" 
    xmlns:atom=\"http://www.w3.org/2005/Atom\">"
    <Document>
    <open>1</open>
    <Style id="sh_dot_red">
        <IconStyle>
            <scale>1.2</scale>
            <Icon>
                <href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href>
            </Icon>
        </IconStyle>
        <ListStyle>
        </ListStyle>
    </Style>
    <Style id="sn_dot_red">
        <IconStyle>
            <Icon>
                <href>http://chinagis.mpiwg-berlin.mpg.de/chinagis/images/dot_red.png</href>
            </Icon>
        </IconStyle>
        <ListStyle>
        </ListStyle>
    </Style>
    <StyleMap id="msn_dot_red">
        <Pair>
            <key>normal</key>
            <styleUrl>#sn_dot_red</styleUrl>
        </Pair>
        <Pair>
            <key>highlight</key>
            <styleUrl>#sh_dot_red</styleUrl>
        </Pair>
    </StyleMap>
"""
    
        
    def htmlHead(self):
        return """ <!DOCTYPE html>
<html>
 <head>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <meta http-Equiv="Cache-Control" Content="no-cache">
    <meta http-Equiv="Pragma" Content="no-cache">
    <meta http-Equiv="Expires" Content="0">
  <link rel="stylesheet" type="text/css"
     href="../../../../mpiwgstyles.css">
  <style type="text/css">
    html { height: 100% }
    body { height: 100%; margin: 0px; padding: 0px }
    #map_canvas { height: 100% }
  </style>
  <style type="text/css">
     td {height:10; font-size:12;}
     th {height:10; font-size:12;}
     b {height:10; font-size:12;}
     h4 {height:10; font-size:15;}
  </style>
    <style type="text/css">
      .tooltip {
        background-color:#ffffff;
        font-weight:bold;
        border:2px #006699 solid;
      }
    </style>


  <script type="text/javascript" src="http://maps.google.com/maps/api/js?sensor=false"> </script>

  <script type="text/javascript">
   var map = null;
   function initialize(data) {
       kmlURL=initialize.arguments[0];
       var myMapOpt = {
          zoom: 5,
          center: new google.maps.LatLng(29,116),
          mapTypeId: google.maps.MapTypeId.TERRAIN
       };
       var map = new google.maps.Map(document.getElementById("map_canvas"), myMapOpt);
       var myLayerOpt = {
          map: map,
          preserveViewport: false
       };
       var newLayer=new google.maps.KmlLayer(kmlURL, myLayerOpt);
    }
  </script>
  </head>  """
  
    def getKmlUrl(self,schema='public',table='mpdl',id=[],doc=None):
        logging.debug("getKmlUrl")
        data = self.getDataForGoogleMap(schema,table,id,doc)
        kml=self.getKMLname(data=data,table=table)
        return """http://chinagis.mpiwg-berlin.mpg.de/chinagis/REST/daten/"""+kml

    def getDataForGoogleMap(self,schema='public',table='mpdl',id=[],doc=None):
        logging.debug("getDataForGoogleMap")
        qstr="SELECT * FROM "+schema+".\""+table+"\""
        data={}
        try:
            if id!=[]:
                qstr=qstr+" WHERE "
                for id_item in id.split(","):
                    if schema=='mpdl' or table.find('mpdl')>-1:
                        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 " OR "
            data=self.ZSQLSimpleSearch(qstr)
            return data
        except:
            return qstr
            
    def getAttributeList(self, table=""):
        attributeList={}
        if table != "":
            sql_for_attributes="SELECT column_name FROM information_schema.columns WHERE table_name = '"+ str(table) + "' AND column_name NOT LIKE 'the_geom'"
            #   print sql_for_attributes
            results=self.ZSQLSimpleSearch(sql_for_attributes)
            #   print results
            for result in results:
              if attributeList=={}:
                 attributeList=[getattr(result,"column_name")]
              else:
                 attributeList.append(getattr(result,"column_name"))
        return attributeList

     
    def getKMLname(self,data=[],table=""):
        logging.debug("getKMLname")
        kml4Marker=self.kml_header()
        initializeStringForGoogleMaps=""
        gisID=""
        xCoord=0
        yCoord=0
        SQL=""
        unknownPointCounter=0
        kmlFileName="marker"+str(time.time())+".kml"
        attributeList=self.getAttributeList(table)
        # 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 LIKE %r" % (table)
              gisIDattribute=self.ZSQLSimpleSearch(SQL)
            except:
                return "table not registered within metadata"
            for dataset in data:
              try:  
                  xCoord=getattr(dataset,'longitude')
                  yCoord=getattr(dataset,'latitude')
                  if str(xCoord)=='None':
                    xCoord=0
                  if str(yCoord)=='None':
                    yCoord=0
              except: 
                  try:
                     xCoord=getattr(dataset,'x_coord')
                     yCoord=getattr(dataset,'y_coord')
                     if str(xCoord)=='None':
                       xCoord=0
                     if str(yCoord)=='None':
                       yCoord=0
                  except:
                     continue2getPoint4GISid=""
              try:
                 if float(xCoord)==0 and float(yCoord)==0:
                    gisID=getattr(dataset,getattr(gisIDattribute[0],'attribute with gis_id'))
                    coords=self.getPoint4GISid(gisID)
                    if coords!=None:
                       xCoord=coords[0]
                       yCoord=coords[1]
              except:
                  kml4Marker=kml4Marker + "No Coordinates for:"+gisID +", "+ str(xCoord)+", "+ str(xCoord)+"\n"
              markerTitel=""
              kml4Marker+="\n<Placemark>\n"
              kml4Marker+="<description>\n<![CDATA[<b>"
              markerTitel="<name>%s</name>\n" % (dataset[2])
              
              i=0
              for value in dataset:
             
             #   if values != (None, None):
              #    if str(values).find('name')>-1:
               #      markerTitel="<name>%s</name>\n" % (values[1])
                #     continue
            #      elif str(values).find('place')>-1:
             #        markerTitel="<name>%s</name>\n" % (values[1])
              #       continue
                
               #   kml4Marker=kml4Marker+": "+str(value)
                  value_string=str(value).replace("'","__Apostroph__")
                  value_string=str(value_string).replace('"','__DoubleApostroph__')
                  value_string=str(value_string).replace(';','__$$__')
                  value_string=str(value_string).replace('&','&amp;')
                  if str(value_string).find('http')>-1:
                      value_string='<A HREF=%r target=_blank>Link to Document</A>' % (value_string)
                  kml4Marker+=attributeList[i]+": "+value_string+"</a><br>\n"
                  i+=1
                          
              kml4Marker+="]]>\n</description>\n"
              kml4Marker+="<styleUrl>#msn_dot_red</styleUrl>\n"
              kml4Marker+="<Point>"
              try:
                if float(xCoord)!=0 and float(yCoord)!=0:
                    kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (xCoord,yCoord)
                else:
                    unknownPointCounter+=1
                    kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (125,25+int(unknownPointCounter)*0.4)
                    markerTitel="<name>Un-referenced Location!</name>\n"
                    
              except:
                  unknownPointCounter+=1
                  kml4Marker+="<coordinates>%s,%s,0</coordinates>" % (125,25+int(unknownPointCounter)*0.4)
                  markerTitel="<name>Un-referenced Location!</name>\n"
              kml4Marker+="</Point>\n"
              kml4Marker+= markerTitel
              kml4Marker+="</Placemark>\n"
        
        kml4Marker=kml4Marker+"</Document>\n</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:
         gis_id_orig=gis_id
         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(gis_id_orig)
                    if len(coords)>0:
                      try:
                        SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (%s,%s,%s); ANALYZE chgis.chgis_coords;"%(gis_id,coords[1],coords[0])
                        returnstring=self.ZSQLSimpleSearch(SQL)
                        return coords
                      except:
                        SQL="INSERT INTO chgis.chgis_coords (gis_id,x_coord,y_coord) VALUES (%s,%s,%s); ANALYZE chgis.chgis_coords;"%(gis_id,coords[0][1],coords[0][0])
                        returnstring=self.ZSQLSimpleSearch(SQL)
                        return coords[0]
         #except:
          #  return "no coords found"
         
        else:
            return coords[0]

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

    def RESTwrite2File(self,datadir, name,text):
#        try:
            fileid=name
            if fileid in datadir.objectIds():
                datadir.manage_delObjects(fileid)
            newfile=open(name,'w')
            newfile.write(text)
            newfile.close()
            file4Read=open(name,'r')
            fileInZope=datadir.manage_addFile(id=fileid,file=file4Read)
            return "Write successful"
#        except:
#            return "Could not write"

        
    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')