"""Handles all interactions with the sensors and geospatial data
 in the database"""
import nclsensorweb.classes as cl
import nclsensorweb.maintenance as maintenance
import nclsensorweb.tools as tools
import nclsensorweb.db_tools as db_tools
import nclsensorweb.errors as error
import datetime
import psycopg2
from pygeocoder import Geocoder
import time
class DatabaseConnection:
    """handles all database connections"""
    def __init__(self, host, db_name, user, password):
        self.__connection_string = 'host=%s dbname=%s user=%s password = %s' \
         % (host, db_name, user, password)
        self.database = psycopg2.connect(self.__connection_string)
        self.cur = self.database.cursor()

    def query(self, query_string):
        """queries the database and returns results"""
        try:
            self.cur.execute(query_string)
        
            results = self.cur.fetchall()
            return results
        except:
            self.database = psycopg2.connect(self.__connection_string)
            self.cur = self.database.cursor()
            raise NameError('DB ERROR')
        
    
    def insert(self, insert_string):
        """inserts data into the database"""
        self.cur.execute(insert_string)
        self.database.commit()
        
       
    
    

class SensorFunctions:
    """handles all sensor functions"""
    def __init__(self, sensorweb):
        self.sensorweb = sensorweb
        
    def __dict_to_sensor(self,info_dict):
            name = info_dict['name']
            id = info_dict['sensor_int_id']
            geom = info_dict['geom']
            active = info_dict['active']
            source = info_dict['source']
            type = info_dict['type']
            del info_dict['name'],info_dict['sensor_int_id'],info_dict['geom'],
            info_dict['active'],info_dict['source'], info_dict['type']
            return cl.Sensor(
                    self.sensorweb.database_connection,
                    name,
                    id,
                    geom, 
                    active, 
                    source, 
                    type,
                    info_dict
                    )

        


        
        
    def get(self, key=False, value=False,last_record = False,active=True, not_flagged=True,logged_in=False):
        """retrives sensors matching the key value"""
        query_string = "select hstore_to_matrix(info) as info from sensors "
        clauses = []
        if key and value:
            clauses.append("info->'%s' = '%s'" % (key, value))
        if active:
            clauses.append("info->'active' = 'True' ")
        if not_flagged:
            clauses.append("info ? 'flag' = False ")
        if not logged_in:
            clauses.append("info->'auth_needed' = 'False'")
        if last_record:
            clauses.append("sensor_int_id_caster(info -> 'sensor_int_id'::text)  in \
            (select distinct sensor_int_id_caster(info -> 'sensor_id'::text)\
             from sensor_data where proper_timestamp(info->'timestamp') > '%s'\
             and info?'special_tag' = False)" %(last_record,))
        if clauses:
            query_string += ' where %s' % (' and '.join(clauses),)
        sens_row = self.sensorweb.database_connection.query(query_string)
        sensors = []
        for sens in sens_row:
            info = dict(sens[0])
            sensor = self.__dict_to_sensor(info)
            sensors.append(sensor)
        if sensors:
            return cl.SensorGroup(self.sensorweb,sensors)

    def create(self, _name, _geom, _type, _source, _active, _auth_needed,_extra={}):
        """creates a sensor entry"""
        new_type ,_type = db_tools.check_tag(self.sensorweb.database_connection,'sensors','type',_type)
        new_source,_source = db_tools.check_tag(self.sensorweb.database_connection,'sensors','source',_source)
        query_string = "select max(sensor_int_id_caster(info -> 'sensor_int_id'::text) ) from sensors"
        sens_id = self.sensorweb.database_connection.query(query_string)
        id = sens_id[0][0] +1
        sensor = cl.Sensor(
                    self.sensorweb.database_connection, 
                    _name,
                    id, 
                    _geom, 
                    _active, 
                    _source,
                    _type,
                    _extra)
                    
        sensor.link()
        if new_type:
            query_string = "update new_tags set info = case  when info is not null \
             then info||hstore('type','%s') else hstore('type','%s') end  \
             where table_name = 'sensors'" % (_type,_type)
            self.sensorweb.database_connection.insert(query_string)
        if new_source:
            query_string = "update new_tags set info = case  when info is not null \
             then info||hstore('source','%s') else hstore('source','%s') end  \
             where table_name = 'sensors'" % (_source,_source)
            self.sensorweb.database_connection.insert(query_string)
        return sensor
        
    def get_or_create(self, _name, _geom, _type,
                    _source, _active, _auth_needed,_extra ={}):
        """Creates sensor entry or updates one with with the matching name"""
        sensor =  self.get(key='name', value=_name,active=False, not_flagged=False,logged_in=True)
        new_type ,_type = db_tools.check_tag(self.sensorweb.database_connection,'sensors','type',_type)
        new_source,_source = db_tools.check_tag(self.sensorweb.database_connection,'sensors','source',_source)
        if not sensor:
            sensor = self.create(_name, _geom, _type, 
                                _source, _active, _auth_needed)
        else:
            sensor = sensor[0]
        info_dict = {'geom':_geom, 'type':_type, 'source': _source,
                    'active':_active, 'auth_needed': _auth_needed}
        info_dict.update(_extra)
        sensor.update(info_dict)
        if new_type:
            query_string = "update new_tags set info = case  when info is not null \
             then info||hstore('type','%s') else hstore('type','%s') end  \
             where table_name = 'sensors'" % (_type,_type,)
            self.sensorweb.database_connection.insert(query_string)
        if new_source:
            query_string = "update new_tags set info = case  when info is not null \
             then info||hstore('source','%s') else hstore('source','%s') end  \
             where table_name = 'sensors'" % (_source,_source,)
            self.sensorweb.database_connection.insert(query_string)
        return sensor
        
    def sources(self,):
        return db_tools.get_tag_values(self.sensorweb.database_connection,'sensors','source')
        
    def types(self,):
        return db_tools.get_tag_values(self.sensorweb.database_connection,'sensors','type')
        
class GeospatialFunctions:
    """create Goesatial function class"""
    def __init__(self, sensorweb,):
        self.sensorweb = sensorweb
        
    def create_geospatial(self, geospatial_id, geom, theme, source, 
                            timestamp, reading, units, value, extra=None):
        """create geospatial class"""
        hstore = []
        hstore.append('"id"=>"%s"' %(geospatial_id,))
        hstore.append('"geom"=>"%s"' %(geom,))
        hstore.append('"source"=>"%s"' %(source,))
        hstore.append('"theme"=>"%s"' %(theme,))
        hstore.append('"timestamp"=>"%s"' %(timestamp,))
        hstore.append('"reading"=>"%s"' %(reading,))
        hstore.append('"units"=>"%s"' %(units,))
        hstore.append('"value"=>"%s"' %(value,))
        hstore.append('"special_tag"=>"GEO"')
        if extra:
            for key, val in extra.iteritems():
                hstore.append('"%s"=>"%s"' %(key, val))
        query_string = "insert into sensor_data (info) values ('%s')"\
                        % (','.join(hstore),)
        self.sensorweb.database_connection.insert(query_string)
        
    def get_all(self, starttime, endtime,not_flagged=True,):
        """Retrives all geospatial entries between 2 times"""
        query_string = "select hstore_to_matrix(info) as info, ST_AsGeoJSON(info->'geom') as geojson from sensor_data"
        clause = "info -> 'special_tag' = 'GEO' \
                and proper_timestamp(info->'timestamp') > '%s' \
                and proper_timestamp(info->'timestamp') < '%s'" % (starttime, endtime,)
                
        if not_flagged:
            clause+= " and  info ? 'flag' = False "
        if clause:
            query_string += ' where %s' % (clause,)

        sens_row = self.sensorweb.database_connection.query(query_string)
        geo = []
        for sens in sens_row:
            info = dict(sens[0])
            geo.append(
                cl.Geospatial(
                    self.sensorweb.database_connection,
                    str(info['id']), 
                    str(info['geom']),
                    str(sens[1]),
                    str(info['source']),
                    str(info['theme']),
                    str(info['timestamp']),
                    str(info['reading']),
                    str(info['units']),
                    str(info['value']),
                    info)
                    )
        return geo
    
    def get(self, starttime, endtime, key, value,not_flagged=True ):
        """Retrives all geospatial entries between 2 times"""
        query_string = "select hstore_to_matrix(info) as info, ST_AsGeoJSON(info->'geom') as geojson from sensor_data"
        clause = "info -> 'special_tag' = 'GEO' \
                and proper_timestamp(info->'timestamp') > '%s' \
                and proper_timestamp(info->'timestamp') < '%s' and info->'%s'='%s'" % (starttime, endtime,key,value)
        if not_flagged:
            clause+= " and  info ? 'flag' = False "
        if clause:
            query_string += ' where %s' % (clause,)
        sens_row = self.sensorweb.database_connection.query(query_string)
        geo = []
        for sens in sens_row:
            info = dict(sens[0])
            geo.append(
                cl.Geospatial(
                    self.sensorweb.database_connection,
                    str(info['id']), 
                    str(info['geom']),
                    str(sens[1]),
                    str(info['source']),
                    str(info['theme']),
                    str(info['timestamp']),
                    str(info['reading']),
                    str(info['units']),
                    str(info['value']),
                    info)
                    )
        return geo
    
class VariableFunctions:
    def __init__(self,sensorweb):
        self.sensorweb = sensorweb

    def get(self,):
        query_string = "select array_agg(reading_name) from readings" 
        variables = self.sensorweb.database_connection.query(query_string)
        return variables[0][0]
class GeometryFunctions:
    def __init__(self,sensorweb):
        self.sensorweb = sensorweb
        
    def latlon(self,lat,lon):
        """creates postgis binary geometry from lat and lon"""
        query_string = "select ST_SetSRID(ST_MakePoint(%s,%s),4326)" \
                        % (lon, lat,)
        geom = self.sensorweb.database_connection.query(query_string)
        return geom[0][0]
    
    def placename(self,placename):
        geom_results = Geocoder.geocode(placename)
        lat,lon = geom_results[0].coordinates
        geom_binary = self.latlon(lat,lon)
        return geom_binary
    
    def wkt(self,wkt):
        query_string = "select ST_GeomFromText('%s',4326)" % (wkt,)
        geom = self.database_connection.query(query_string)
        return geom[0][0]

    
class SensorWeb:
    """SensorWeb class handles all interactions with the database"""
    def __init__(self, host, db_name, user, password, add_ons=None):
        self.database_connection = DatabaseConnection(host, db_name, 
                                                    user, password)
        self.sensors = SensorFunctions(self)
        self.geospatial = GeospatialFunctions(self)
        self.geometry = GeometryFunctions(self)
        self.variables = VariableFunctions(self)
        self.maintenance = maintenance.maintenance_class(self)
        if add_ons:
            for add_on in add_ons:
                setattr(self, add_on.name, add_on(self))
    def measure_distance(self,e1,n1,e2,n2):
        q = "select st_distance(st_makepoint(%s,%s),st_makepoint(%s,%s))" % (e1,n1,e2,n2)
        return self.database_connection.query(q)[0][0]
        

