import matplotlib.pyplot as plt
import numpy as np
import psycopg2

scale=1/30000

conn = psycopg2.connect("dbname=michelferreira user=michelferreira")

cursor_psql = conn.cursor()

sql = "create table tempo (tempo_id int primary key, hora int)"
cursor_psql.execute(sql)

for i in range(24):
    sql = "insert into tempo values("+str(i)+","+str(i)+")"
    cursor_psql.execute(sql)

sql = "create table estado (estado_id int primary key, estado varchar(6))"
cursor_psql.execute(sql)

sql = "insert into estado values (0,'FREE'),(1,'BUSY'),(2,'PICKUP'),(3,'PAUSE')"
cursor_psql.execute(sql)

sql = "create table localizacao (localizacao_id int primary key, praca_de_taxi varchar(30),freguesia varchar(80))"
cursor_psql.execute(sql)

sql = "select name,des_simpli from taxi_stands,cont_aad_caop2018 where concelho='PORTO' and st_within(proj_location,proj_boundary) union select 'Fora de praça',des_simpli from taxi_stands,cont_aad_caop2018 where concelho='PORTO'"

cursor_psql.execute(sql)

results = cursor_psql.fetchall()
i = 0
for row in results:    
    sql = "insert into localizacao values ("+str(i)+",'"+row[0]+"','"+row[1]+"')"
    cursor_psql.execute(sql)
    i = i + 1

sql = "create table dw_trajectos (tempo_id int, estado_id int, localizacao_id int, total_trajectos int, total_metros int, media_segundos int)"
cursor_psql.execute(sql)

sql = "insert into dw_trajectos select extract('hour' from to_timestamp(ts)),estado_id,localizacao_id,count(*),cast(sum(st_length(proj_track)) as integer),cast(avg(st_numpoints(proj_track)) as integer) from tracks,taxi_stands,localizacao,estado where state=estado.estado and name=localizacao.praca_de_taxi and st_distance(st_startpoint(proj_track),proj_location)<50 group by 1,2,3 union select extract('hour' from to_timestamp(ts)),estado_id,localizacao_id,count(*),cast(sum(st_length(proj_track)) as integer),cast(avg(st_numpoints(proj_track)) as integer) from tracks,cont_aad_caop2018,localizacao,estado where state=estado.estado and concelho='PORTO' and des_simpli=localizacao.freguesia and localizacao.praca_de_taxi='Fora de praça' and not exists (select * from taxi_stands where st_distance(st_startpoint(proj_track),proj_location)<50) and st_within(st_startpoint(proj_track),proj_boundary) group by 1,2,3"

cursor_psql.execute(sql)

conn.commit()

conn.close()