# Copyright 2013 Netherlands eScience Center
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#   http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import logging
from iso8601 import parse_date
from pyramid.view import view_config

logger = logging.getLogger(__package__)


@view_config(route_name="home", renderer="home.mako")
def home(request):
    return {}


@view_config(route_name='trackers', renderer='json')
def trackers(request):
    """Returns a list of tracker identifiers the user has access to"""
    cur = request.db.cursor()
    return {'trackers': fetchTrackers(cur)}


def fetchTrackers(cur):
    cur.execute("""
        SELECT DISTINCT device_info_serial as id
        FROM gps.ee_tracker_limited
        JOIN gps.ee_track_session_limited USING (device_info_serial)
        ORDER BY device_info_serial
    """)
    return list(cur)


def fetchTrack(cur, trackerId, start, end):
    # TODO accelartion freq is hardcoded
    freq = 20.0

    sql2 = """
    SELECT
    timezone('zulu', date_time) date_time
    , round(s.latitude::numeric, 5) lat
    , round(s.longitude::numeric, 5) lon
    , s.altitude
    , s.altitude altitude_asl
    , coalesce(nullif(
            (
            SELECT max(the_data[floor(((st_ymax(bbox) - (st_y(location)))    / abs(cellsize_y))+1)]
                     [floor(((st_x(location)- st_xmin(bbox)) / abs(cellsize_x))+1)])::float
            FROM elevation.srtm3
            WHERE bbox && location
            )
        , -9999
        )
        , (
        SELECT max(the_data[floor(((st_ymax(bbox) - (st_y(location)))    / abs(cellsize_y))+1)]
                 [floor(((st_x(location)- st_xmin(bbox)) / abs(cellsize_x))+1)])::float
        FROM elevation.srtm30
        WHERE bbox && location
        )
    ) AS ground_elevation
    , s.temperature
    , round(s.speed_2d::numeric, 5) AS speed
    ,round((
      ST_Length_Spheroid(ST_MakeLine(location, lag(location) over (order by device_info_serial, date_time)), 'SPHEROID["WGS 84",6378137,298.257223563]')
      /
      EXTRACT(EPOCH FROM (date_time - lag(date_time) over (order by device_info_serial, date_time)))
     )::numeric, 5) as tspeed
    , round(s.direction, 2) AS idirection
    , round(degrees(ST_Azimuth(lag(location) over (order by device_info_serial, date_time), location))::numeric, 2) tdirection
    , round(mod(s.direction - lag(s.direction) over (order by device_info_serial, date_time), 180.0), 2) AS delta_idirection
    , round(degrees(
        ST_Azimuth(location, lead(location) over (order by device_info_serial, date_time)) -
        ST_Azimuth(lag(location) over (order by device_info_serial, date_time), location)
    )::numeric %% 180.0, 2) AS delta_tdirection
    , aa.time_acceleration
    , aa.x_acceleration, aa.y_acceleration, aa.z_acceleration
    FROM
    gps.ee_tracking_speed_limited s
    LEFT JOIN
    (
    SELECT date_time
    , array_agg(round(a.index/%s, 4) ORDER BY date_time, index) time_acceleration
    , array_agg(round(((x_acceleration-x_o)/x_s)::numeric, 4) ORDER BY date_time, index) x_acceleration
    , array_agg(round(((y_acceleration-y_o)/y_s)::numeric, 4) ORDER BY date_time, index) y_acceleration
    , array_agg(round(((z_acceleration-z_o)/z_s)::numeric, 4) ORDER BY date_time, index) z_acceleration
    FROM gps.ee_acceleration_limited a
    JOIN (
      SELECT
    DISTINCT device_info_serial
    , x_o, x_s
    , y_o, y_s
    , z_o, z_s
      FROM gps.ee_tracker_limited d
    ) tu USING (device_info_serial)
    WHERE
    device_info_serial = %s AND date_time BETWEEN %s AND %s
    GROUP BY date_time
    ) aa USING (date_time)
    WHERE
    device_info_serial = %s AND date_time BETWEEN %s AND %s
    AND userflag != 1 AND longitude IS NOT NULL
    ORDER BY date_time
    """

    cur.execute(sql2, (freq, trackerId, start, end, trackerId, start, end))
    return cur


@view_config(route_name='tracker', renderer='json')
def tracker(request):
    """Returns gps+accel data of tracker in a certain time range"""
    cur = request.db.cursor()
    trackerId = int(request.matchdict['id'])
    start = parse_date(request.matchdict['start']).isoformat()
    end = parse_date(request.matchdict['end']).isoformat()
    return fetchTrack(cur, trackerId, start, end)

@@ -1595,611 +1595,35 @@
   , 
-coalesce(nullif(%0A            (%0A            SELECT max(the_data%5Bfloor(((st_ymax(bbox) - (st_y(location)))    / abs(cellsize_y))+1)%5D%0A                     %5Bfloor(((st_x(location)- st_xmin(bbox)) / abs(cellsize_x))+1)%5D)::float%0A            FROM elevation.srtm3%0A            WHERE bbox && location%0A            )%0A        , -9999%0A        )%0A        , (%0A        SELECT max(the_data%5Bfloor(((st_ymax(bbox) - (st_y(location)))    / abs(cellsize_y))+1)%5D%0A                 %5Bfloor(((st_x(location)- st_xmin(bbox)) / abs(cellsize_x))+1)%5D)::float%0A        FROM elevation.srtm30%0A        WHERE bbox && location%0A        )%0A    )
+s.altitude - s.altitude_agl
  AS 
@@ -1704,21 +1704,22 @@
 S speed%0A
-
     ,
+ 
 round((%0A

Replace on the fly terrain elevation calculation with pre-calculated value.