#!/usr/bin/env python
# -*- coding:utf8 -*-
import httplib
from StringIO import StringIO

from lxml import objectify

from sqlalchemy import create_engine, text
from sqlalchemy.ext.sqlsoup import SqlSoup

soup = SqlSoup(
    create_engine("postgresql://ebus:ebus@10.2.2.26:5432/ebus") )

def read():
    conn = httplib.HTTPConnection("www.wettermichel.de")
    conn.request("GET", "/weatherdata/params3.xml")
    result = conn.getresponse()
    assert result.status == 200, 'Site answered with Status 200'

    xml = objectify.parse( result )
    conn.close()
    return xml

def get_sensor_id(sensor_name):
    try:
        sensor_id = soup.sensor.filter(soup.sensor.name == sensor_name).one()
        return sensor_id.id
    except:
        conn = soup.connection()
        conn.execute(text("""INSERT INTO sensor(name) VALUES (:name)"""),
                     name = sensor_name)
        soup.commit()
        return sensor_id = soup.sensor.filter(soup.sensor.name == sensor_name).one().id

def insert(sensor_name,value_int=None, value_float=None, value_string=None):
    sql = text("""INSERT INTO value(
          timestamp, sensor_id, type, value_int, value_float, value_string)
          VALUES (
          NOW(), :sensor_id, :type, :value_int, :value_float, :value_string)""")
    conn = soup.connection()
    ttype = value_int != None and "int" or \
            value_float != None and "float" or \
            value_string != None and "string" or \
            None
    assert ttype != None, 'ttype is set'
    conn.execute(sql, sensor_id=get_sensor_id(sensor_name),
                      type=ttype,
                      value_int=value_int,
                      value_float=value_float,
                      value_string=value_string)
    soup.commit()

###############

xml = read()
for keyval in xml.xpath("/data/*"):
    if keyval.tag == "entry":
        continue

    if isinstance(keyval, objectify.IntElement):
        insert("de.wettermichel.%s"%keyval.tag,
               value_int=int(keyval.text))
    elif isinstance(keyval, objectify.FloatElement):
        insert("de.wettermichel.%s"%keyval.tag,
               value_float=float(keyval.text))
    elif isinstance(keyval, objectify.StringElement):
        insert("de.wettermichel.%s"%keyval.tag,
               value_string=keyval.text)
    else:
        print "ignore other: %s %s" % (keyval.tag, keyval.__class__)