#!/usr/bin/env python
# coding: utf-8

# In[1]:


import requests
import pandas as pd
import sqlite3
from tqdm import tqdm


# In[2]:


# Kod 4
r = requests.get('http://api.gios.gov.pl/pjp-api/rest/station/findAll')
stations = pd.io.json.json_normalize(r.json())


# In[3]:


print(stations.shape)


# In[4]:


# you have to create path to db manually
conn = sqlite3.connect('../output/aqip.db')
c = conn.cursor()


# In[5]:


# Kod 1
# stations
create_query = '''
CREATE TABLE IF NOT EXISTS stations (
id integer PRIMARY KEY, 
addressStreet text, 
city_commune_communeName text,
city_commune_districtName text,
city_commune_provinceName text,
city_id integer,
city_name text,
gegrLat real,
gegrLon real,
stationName text)
'''
c.execute(create_query)


# In[6]:


# Kod 2
# sensors
create_query = '''
CREATE TABLE IF NOT EXISTS sensors (
id integer PRIMARY KEY,
param_idParam integer,
param_paramCode text,
param_paramFormula text,
param_paramName text, 
stationId integer)
'''
c.execute(create_query)


# In[7]:


# Kod 3
# readings
create_query = '''
CREATE TABLE IF NOT EXISTS readings (
id integer,
datetime text,
value real,
PRIMARY KEY (id, datetime))
'''
c.execute(create_query)


# In[8]:


# Kod 5
for index, station in stations.iterrows():
    c.execute("INSERT OR REPLACE INTO stations VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
              ([station["id"], 
                station["addressStreet"],
                station["city.commune.communeName"],
                station["city.commune.districtName"],
                station["city.commune.provinceName"], 
                station["city.id"], 
                station["city.name"], 
                station["gegrLat"], 
                station["gegrLon"], 
                station["stationName"]]))
conn.commit()


# In[9]:


# Kod 6
for station_id in tqdm(pd.read_sql_query("select id from stations", conn).values):
    station_id = station_id[0]
    #print(station_id)
    r = requests.get('http://api.gios.gov.pl/pjp-api/rest/station/sensors/' + str(station_id))
    sensors = pd.io.json.json_normalize(r.json())

    for index, sensor in sensors.iterrows():
        #print(sensor)
        c.execute("INSERT OR REPLACE INTO sensors VALUES (?, ?, ?, ?, ?, ?)", 
                  ([sensor["id"], 
                    sensor["param.idParam"],
                    sensor["param.paramCode"],
                    sensor["param.paramFormula"],
                    sensor["param.paramName"], 
                    sensor["stationId"]]))
    conn.commit()
    #break


# In[10]:


# Kod 7
lengths = []
for sensor_id in tqdm(pd.read_sql_query("select id from sensors", conn).values):
    sensor_id = sensor_id[0]
    #print(sensor_id)
    r = requests.get('http://api.gios.gov.pl/pjp-api/rest/data/getData/' + str(sensor_id))
    readings = pd.io.json.json_normalize(r.json())
    
    readingsFrame = pd.DataFrame()
    
    readingsFrame["datetime"] = [d[u'date'] for d in readings["values"].values.item()]
    readingsFrame["value"] = [d[u'value'] for d in readings["values"].values.item()]
    
    length = len(readingsFrame)
    lengths.append(length)
    #if length == 0:
    #    print(sensor_id)
    
    for index, reading in readingsFrame.iterrows():
        #print(reading["datetime"])
        #print(reading["value"])
        
        c.execute("INSERT OR REPLACE INTO readings VALUES (?, ?, ?)", ([int(sensor_id), 
                                                               reading["datetime"],
                                                               reading["value"],
                                                               ]))
    conn.commit()
    #break


# In[11]:


pd.Series(lengths).value_counts()


# ### Reading data from database

# In[13]:


get_ipython().run_cell_magic('time', '', '# Kod 8\n\n\nquery = """\nSELECT readings.id as sensor_id, datetime, value, param_paramFormula, gegrLat, gegrLon, stationId as station_id\nFROM readings, sensors, stations\nWHERE readings.id = sensors.id\n AND sensors.stationId = stations.id\n"""\n\ngios_data = pd.read_sql_query(query, conn)\n\ngios_data.sort_values(by = ["sensor_id", "datetime"], inplace=True)\n')


# In[14]:


gios_data.head()


# In[15]:


gios_data.tail()


# In[ ]: