def _get_distance_flight(distance):
    return Flight.query() \
        .filter(Flight.pilot == g.user) \
        .filter(Flight.olc_classic_distance >= distance) \
        .order_by(Flight.landing_time) \
        .filter(Flight.is_rankable()) \
        .first()

def find_meetings(flight_id):
    logger.info("Searching for near flights of flight %d" % flight_id)

    flight = Flight.get(flight_id)

    # Update FlightPathChunks of current flight
    FlightPathChunks.update_flight_path(flight)

    other_flights = FlightPathChunks.get_near_flights(flight)

    # delete all previous detected points between src and dst
    for key in other_flights:
        FlightMeetings.query() \
            .filter(or_(and_(FlightMeetings.source == flight, FlightMeetings.destination_id == key),
                        and_(FlightMeetings.destination == flight, FlightMeetings.source_id == key))) \
            .delete()

    # Insert new meetings into table
    for flight_id, meetings in other_flights.iteritems():
        other_flight = Flight.get(flight_id)

        for meeting in meetings:
            FlightMeetings.add_meeting(flight, other_flight, meeting['times'][0], meeting['times'][-1])

    db.session.commit()

    def run(self, force, date_from, date_to, ids):
        current_app.add_celery()

        if force:
            # invalidate all results
            Flight.query().update({'needs_analysis': True})

        if ids:
            for flight_id in ids:
                self.do(flight_id)
        elif date_from and date_to:
            print date_from
            try:
                date_from = datetime.strptime(date_from, "%Y-%m-%d")
                date_to = datetime.strptime(date_to, "%Y-%m-%d")
            except:
                print "Cannot parse date."
                quit()

            q = db.session.query(Flight)
            q = q.filter(Flight.takeoff_time >= date_from) \
                 .filter(Flight.takeoff_time <= date_to)

            for flight in q:
                self.do(flight.id)
        else:
            for flight in Flight.query(needs_analysis=True):
                self.do(flight.id)

def _distance_flight(user, distance, schema):
    flight = Flight.query() \
        .filter(Flight.pilot == user) \
        .filter(Flight.olc_classic_distance >= distance) \
        .order_by(Flight.landing_time) \
        .filter(Flight.is_rankable()) \
        .first()

    if flight:
        return schema.dump(flight).data

    def run(self, force, ids):
        current_app.add_celery()

        if force:
            # invalidate all results
            Flight.query().update({'needs_analysis': True})

        if ids:
            for flight_id in ids:
                self.do(flight_id)
        else:
            for flight in Flight.query(needs_analysis=True):
                self.do(flight.id)

def _list():
    query = (
        Notification.query(recipient_id=request.user_id)
        .join("event")
        .options(contains_eager("event"))
        .options(subqueryload("event.actor"))
        .outerjoin(Event.flight)
        .options(contains_eager("event.flight"))
        .filter(or_(Event.flight == None, Flight.is_rankable()))
        .order_by(Event.time.desc())
    )

    query = _filter_query(query, request.args)

    page = request.args.get("page", type=int, default=1)
    per_page = request.args.get("per_page", type=int, default=50)

    query = query.limit(per_page)
    query = query.offset((page - 1) * per_page)

    def get_event(notification):
        event = notification.event
        event.unread = notification.time_read is None
        return event

    events = list(convert_event(get_event(notification)) for notification in query)

    return jsonify(events=events)

def index():
    query = Event.query() \
        .options(subqueryload('actor')) \
        .options(subqueryload('user')) \
        .options(subqueryload('club')) \
        .outerjoin(Event.flight) \
        .options(contains_eager(Event.flight)) \
        .filter(or_(Event.flight == None, Flight.is_rankable())) \
        .order_by(Event.time.desc())

    query = _filter_query(query, request.args)

    page = request.args.get('page', type=int, default=1)
    per_page = request.args.get('per_page', type=int, default=50)

    events = query.limit(per_page).offset((page - 1) * per_page).all()
    events_count = len(events)

    if request.args.get('grouped', True, type=str_to_bool):
        events = group_events(events)

    template_vars = dict(events=events, types=Event.Type)

    if page > 1:
        template_vars['prev_page'] = page - 1
    if events_count == per_page:
        template_vars['next_page'] = page + 1

    return render_template('timeline/list.jinja', **template_vars)

def check_update_form(prefix, flight_id, name, status):
    if not flight_id:
        return None, None, None

    # Get flight from database and check if it is writable
    flight = Flight.get(flight_id)

    if not flight:
        abort(404)

    if status == UploadStatus.DUPLICATE:
        return flight, None, None

    else:
        if not flight.is_writable(g.current_user):
            abort(403)

        form = UploadUpdateForm(prefix=str(prefix), obj=flight)
        trace = _get_flight_path(flight)

        # Force takeoff_time and landing_time to be within the igc file limits
        if form.takeoff_time.data < trace['igc_start_time']:
            form.takeoff_time.data = trace['igc_start_time']

        if form.landing_time.data > trace['igc_end_time']:
            form.landing_time.data = trace['igc_end_time']

        return flight, trace, form

def update():
    flight_id_list = request.values.getlist('flight_id')
    model_list = request.values.getlist('model')
    registration_list = request.values.getlist('registration')
    competition_id_list = request.values.getlist('competition_id')

    if (flight_id_list is None
            or len(flight_id_list) != len(model_list)
            or len(flight_id_list) != len(registration_list)):
        flash(_('Sorry, some error happened when updating your flight(s). Please contact a administrator for help.'), 'warning')
        return redirect('/flights/latest')

    for index, id in enumerate(flight_id_list):
        # Parse flight id

        try:
            id = int(id)
        except ValueError:
            continue

        # Get flight from database and check if it is writable

        flight = Flight.get(id)

        if not flight or not flight.is_writable(g.current_user):
            continue

        # Parse model, registration and competition ID

        try:
            model_id = int(model_list[index])
        except ValueError:
            model_id = None

        if model_id == 0:
            model_id = None

        registration = registration_list[index]
        if registration is not None:
            registration = registration.strip()
            if not 0 < len(registration) < 32:
                registration = None

        competition_id = competition_id_list[index]
        if competition_id is not None:
            competition_id = competition_id.strip()
            if not 0 < len(competition_id) < 5:
                competition_id = None

        # Set new values

        flight.model_id = model_id
        flight.registration = registration
        flight.competition_id = competition_id
        flight.time_modified = datetime.utcnow()

    db.session.commit()

    flash(_('Your flight(s) have been successfully updated.'))
    return redirect('/flights/latest')

def _get_takeoff_locations(user):
    locations = Location.get_clustered_locations(
        Flight.takeoff_location_wkt,
        filter=and_(Flight.pilot == user, Flight.is_rankable()),
    )

    return [loc.to_lonlat() for loc in locations]

def _update_flight(flight_id, model_id, registration, competition_id,
                   takeoff_time, scoring_start_time,
                   scoring_end_time, landing_time):
    # Get flight from database and check if it is writable
    flight = Flight.get(flight_id)

    if not flight or not flight.is_writable(g.current_user):
        return False

    # Parse model, registration and competition ID
    if model_id == 0:
        model_id = None

    if registration is not None:
        registration = registration.strip()
        if not 0 < len(registration) <= 32:
            registration = None

    if competition_id is not None:
        competition_id = competition_id.strip()
        if not 0 < len(competition_id) <= 5:
            competition_id = None

    # Set new values
    flight.model_id = model_id
    flight.registration = registration
    flight.competition_id = competition_id
    flight.time_modified = datetime.utcnow()

    # Update times only if they are reasonable and have been changed...
    trigger_analysis = False

    if takeoff_time and scoring_start_time and scoring_end_time and landing_time \
       and takeoff_time <= scoring_start_time <= scoring_end_time <= landing_time \
       and (flight.takeoff_time != takeoff_time
            or flight.scoring_start_time != scoring_start_time
            or flight.scoring_end_time != scoring_end_time
            or flight.landing_time != landing_time):

        flight.takeoff_time = takeoff_time
        flight.scoring_start_time = scoring_start_time
        flight.scoring_end_time = scoring_end_time
        flight.landing_time = landing_time

        trigger_analysis = True

    flight.privacy_level = Flight.PrivacyLevel.PUBLIC

    db.session.commit()

    if trigger_analysis:
        analyse_flight(flight)

    try:
        tasks.analyse_flight.delay(flight.id)
        tasks.find_meetings.delay(flight.id)
    except ConnectionError:
        current_app.logger.info('Cannot connect to Redis server')

    return True

def _update_flight(flight_id, model_id, registration, competition_id):
    # Get flight from database and check if it is writable
    flight = Flight.get(flight_id)

    if not flight or not flight.is_writable(g.current_user):
        return False

    # Parse model, registration and competition ID
    if model_id == 0:
        model_id = None

    if registration is not None:
        registration = registration.strip()
        if not 0 < len(registration) <= 32:
            registration = None

    if competition_id is not None:
        competition_id = competition_id.strip()
        if not 0 < len(competition_id) <= 5:
            competition_id = None

    # Set new values
    flight.model_id = model_id
    flight.registration = registration
    flight.competition_id = competition_id
    flight.time_modified = datetime.utcnow()

    db.session.commit()

    return True

def _largest_flight(user, schema):
    flight = user.get_largest_flights() \
        .filter(Flight.is_rankable()) \
        .first()

    if flight:
        return schema.dump(flight).data

def analyse_flight(flight_id, full=2048, triangle=6144, sprint=512):
    logger.info("Analysing flight %d" % flight_id)

    if analysis.analyse_flight(Flight.get(flight_id), full, triangle, sprint):
        db.session.commit()
    else:
        logger.warn("Analysis of flight %d failed." % flight_id)

def list():
    query = Notification.query(recipient_id=request.user_id) \
        .join('event') \
        .options(contains_eager('event')) \
        .options(subqueryload('event.actor')) \
        .outerjoin(Event.flight) \
        .options(contains_eager('event.flight')) \
        .filter(or_(Event.flight == None, Flight.is_rankable())) \
        .order_by(Event.time.desc())

    query = _filter_query(query, request.args)

    page = request.args.get('page', type=int, default=1)
    per_page = request.args.get('per_page', type=int, default=50)

    query = query.limit(per_page)
    query = query.offset((page - 1) * per_page)

    def get_event(notification):
        event = notification.event
        event.unread = (notification.time_read is None)
        return event

    events = map(get_event, query)

    return jsonify(events=(map(convert_event, events)))

def _get_last_year_statistics():
    query = db.session.query(func.count('*').label('flights'),
                             func.sum(Flight.olc_classic_distance).label('distance'),
                             func.sum(Flight.duration).label('duration')) \
                      .filter(Flight.pilot == g.user) \
                      .filter(Flight.date_local > (date.today() - timedelta(days=365))) \
                      .filter(Flight.is_rankable()) \
                      .first()

    last_year_statistics = dict(flights=0,
                                distance=0,
                                duration=timedelta(0),
                                speed=0)

    if query and query.flights > 0:
        duration_seconds = query.duration.days * 24 * 3600 + query.duration.seconds

        if duration_seconds > 0:
            last_year_statistics['speed'] = float(query.distance) / duration_seconds

        last_year_statistics['flights'] = query.flights
        last_year_statistics['distance'] = query.distance
        last_year_statistics['duration'] = query.duration

        last_year_statistics['average_distance'] = query.distance / query.flights
        last_year_statistics['average_duration'] = query.duration / query.flights

    return last_year_statistics

def _get_result(model, flight_field, year=None):
    subq = db.session \
        .query(getattr(Flight, flight_field),
               func.count('*').label('count'),
               func.sum(Flight.index_score).label('total')) \
        .group_by(getattr(Flight, flight_field)) \
        .outerjoin(Flight.model) \
        .filter(Flight.is_rankable())

    if isinstance(year, int):
        year_start = date(year, 1, 1)
        year_end = date(year, 12, 31)
        subq = subq.filter(Flight.date_local >= year_start) \
                   .filter(Flight.date_local <= year_end)

    subq = subq.subquery()

    result = db.session \
        .query(model, subq.c.count, subq.c.total,
               over(func.rank(), order_by=desc('total')).label('rank')) \
        .join((subq, getattr(subq.c, flight_field) == model.id))

    if model == User:
        result = result.outerjoin(model.club)
        result = result.options(eagerload(model.club))

    return result

def index(page=None, id=None):
    if 'application/json' not in request.headers.get('Accept', ''):
        return render_template('ember-page.jinja', active_page='statistics')

    name = None

    query = db.session.query(Flight.year.label('year'),
                             func.count('*').label('flights'),
                             func.count(distinct(Flight.pilot_id)).label('pilots'),
                             func.sum(Flight.olc_classic_distance).label('distance'),
                             func.sum(Flight.duration).label('duration'))

    pilots_query = db.session.query(func.count(distinct(Flight.pilot_id)))

    if page == 'pilot':
        pilot = get_requested_record(User, id)
        name = unicode(pilot)
        query = query.filter(Flight.pilot_id == pilot.id)

    elif page == 'club':
        club = get_requested_record(Club, id)
        name = unicode(club)
        query = query.filter(Flight.club_id == club.id)
        pilots_query = pilots_query.filter(Flight.club_id == club.id)

    elif page == 'airport':
        airport = get_requested_record(Airport, id)
        name = unicode(airport)
        query = query.filter(Flight.takeoff_airport_id == airport.id)
        pilots_query = pilots_query.filter(Flight.takeoff_airport_id == airport.id)

    elif page is not None:
        abort(404)

    query = query.filter(Flight.is_rankable())

    query = query.group_by(Flight.year).order_by(Flight.year.desc())

    if page == 'pilot':
        sum_pilots = 0
    else:
        sum_pilots = pilots_query.scalar()

    list = []
    for row in query:
        row.average_distance = row.distance / row.flights
        row.average_duration = row.duration / row.flights

        list.append({
            'year': row.year,
            'flights': row.flights,
            'distance': row.distance,
            'duration': row.duration.total_seconds(),
            'pilots': row.pilots,
            'average_distance': row.distance / row.flights,
            'average_duration': row.duration.total_seconds() / row.flights,
        })

    return jsonify(name=name, years=list, sumPilots=sum_pilots)

def _patch_query(q):
    current_user = User.get(request.user_id) if request.user_id else None

    return (
        q.join(Flight.igc_file)
        .options(contains_eager(Flight.igc_file))
        .filter(Flight.is_viewable(current_user))
    )

def _get_near_flights(flight, location, time, max_distance=1000):
    # calculate max_distance in degrees at the earth's sphere (approximate,
    # cutoff at +-85 deg)
    max_distance_deg = (max_distance / METERS_PER_DEGREE) / \
        math.cos(math.radians(min(abs(location.latitude), 85)))

    # the distance filter is geometric only, so max_distance must be given in
    # SRID units (which is degrees for WGS84). The filter will be more and more
    # inaccurate further to the poles. But it's a lot faster than the geograpic
    # filter...

    result = Flight.query() \
        .options(undefer_group('path')) \
        .filter(Flight.id != flight.id) \
        .filter(Flight.takeoff_time <= time) \
        .filter(Flight.landing_time >= time) \
        .filter(func.ST_DWithin(Flight.locations,
                                location.to_wkt_element(),
                                max_distance_deg))

    result = _patch_query(result)

    flights = []
    for flight in result:
        # find point closest to given time
        closest = min(range(len(flight.timestamps)),
                      key=lambda x: abs((flight.timestamps[x] - time).total_seconds()))

        trace = to_shape(flight.locations).coords

        if closest == 0 or closest == len(trace) - 1:
            point = trace[closest]
        else:
            # interpolate flight trace between two fixes
            next_smaller = closest if flight.timestamps[closest] < time else closest - 1
            next_larger = closest if flight.timestamps[closest] > time else closest + 1
            dx = (time - flight.timestamps[next_smaller]).total_seconds() / \
                 (flight.timestamps[next_larger] - flight.timestamps[next_smaller]).total_seconds()

            point_next = trace[closest]
            point_prev = trace[closest]

            point = [point_prev[0] + (point_next[0] - point_prev[0]) * dx,
                     point_prev[1] + (point_next[1] - point_prev[1]) * dx]

        point_distance = location.geographic_distance(
            Location(latitude=point[1], longitude=point[0]))

        if point_distance > max_distance:
            continue

        flights.append(flight)

        # limit to 5 flights
        if len(flights) == 5:
            break

    return flights