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 index(page=None, id=None):
    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 = pilot.name
        query = query.filter(Flight.pilot_id == pilot.id)

    elif page == "club":
        club = get_requested_record(Club, id)
        name = club.name
        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 = airport.name
        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:
        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 near(flight_id):
    flight = get_requested_record(Flight, flight_id, joinedload=[Flight.igc_file])

    current_user = User.get(request.user_id) if request.user_id else None
    if not flight.is_viewable(current_user):
        return jsonify(), 404

    try:
        latitude = float(request.args["lat"])
        longitude = float(request.args["lon"])
        time = float(request.args["time"])

    except (KeyError, ValueError):
        abort(400)

    location = Location(latitude=latitude, longitude=longitude)
    time = from_seconds_of_day(flight.takeoff_time, time)

    flights = _get_near_flights(flight, location, time, 1000)

    def add_flight_path(flight):
        trace = _get_flight_path(flight, threshold=0.0001, max_points=10000)
        trace["additional"] = dict(registration=flight.registration, competition_id=flight.competition_id)

        return trace

    return jsonify(flights=map(add_flight_path, flights))

def add_comment(flight_id):
    flight = get_requested_record(Flight, flight_id)

    current_user = User.get(request.user_id)
    if not current_user:
        return jsonify(), 403

    json = request.get_json()
    if json is None:
        return jsonify(error="invalid-request"), 400

    try:
        data = FlightCommentSchema().load(json).data
    except ValidationError as e:
        return jsonify(error="validation-failed", fields=e.messages), 422

    comment = FlightComment()
    comment.user = current_user
    comment.flight = flight
    comment.text = data["text"]

    create_flight_comment_notifications(comment)

    db.session.commit()

    return jsonify()

def update(club_id):
    current_user = User.get(request.user_id)
    if not current_user:
        return jsonify(error="invalid-token"), 401

    club = get_requested_record(Club, club_id)
    if not club.is_writable(current_user):
        return jsonify(error="forbidden"), 403

    json = request.get_json()
    if json is None:
        return jsonify(error="invalid-request"), 400

    try:
        data = ClubSchema(partial=True).load(json).data
    except ValidationError as e:
        return jsonify(error="validation-failed", fields=e.messages), 422

    if "name" in data:
        name = data.get("name")

        if name != club.name and Club.exists(name=name):
            return jsonify(error="duplicate-club-name"), 422

        club.name = name

    if "website" in data:
        club.website = data.get("website")

    db.session.commit()

    return jsonify()

def follow(user_id):
    user = get_requested_record(User, user_id)
    current_user = User.get(request.user_id)
    Follower.follow(current_user, user)
    create_follower_notification(user, current_user)
    db.session.commit()
    return jsonify()

def pilot(id):
    pilot = get_requested_record(User, id)

    mark_flight_notifications_read(pilot)

    return _create_list('pilot', request.args, pilot=pilot,
                        default_sorting_column='date', default_sorting_order='desc')

def pilot(id):
    pilot = get_requested_record(User, id)

    mark_user_notifications_read(pilot)

    return _create_list(
        pilot=pilot, default_sorting_column="date", default_sorting_order="desc"
    )

    def _lookup(self, id, *remainder):
        # Fallback for old URLs
        if id == 'id' and len(remainder) > 0:
            id = remainder[0]
            remainder = remainder[1:]

        controller = UserController(get_requested_record(User, id))
        return controller, remainder

def read(club_id):
    current_user = User.get(request.user_id) if request.user_id else None

    club = get_requested_record(Club, club_id)

    json = ClubSchema().dump(club).data
    json["isWritable"] = club.is_writable(current_user) or False

    return jsonify(json)

    def _lookup(self, id, *remainder):
        if not request.identity:
            raise HTTPForbidden

        notification = get_requested_record(Notification, id)
        if request.identity['user'] != notification.recipient:
            raise HTTPForbidden

        controller = NotificationController(notification)
        return controller, remainder

def index(airport_id):
    airport = get_requested_record(Airport, airport_id)

    json = {
        'id': airport.id,
        'name': unicode(airport),
        'countryCode': airport.country_code,
    }

    return jsonify(**json)

def delete_user(user_id):
    current_user = User.get(request.user_id)
    if not current_user.is_manager():
        return jsonify(), 403

    user = get_requested_record(User, user_id)

    user.delete()
    db.session.commit()

    return jsonify()

def delete(flight_id):
    flight = get_requested_record(Flight, flight_id, joinedload=[Flight.igc_file])

    current_user = User.get(request.user_id)
    if not flight.is_writable(current_user):
        abort(403)

    files.delete_file(flight.igc_file.filename)
    db.session.delete(flight)
    db.session.delete(flight.igc_file)
    db.session.commit()

    return jsonify()

def json(flight_id):
    flight = get_requested_record(
        Flight, flight_id, joinedload=(Flight.igc_file, Flight.model)
    )

    current_user = User.get(request.user_id) if request.user_id else None
    if not flight.is_viewable(current_user):
        return jsonify(), 404

    # Return HTTP Status code 304 if an upstream or browser cache already
    # contains the response and if the igc file did not change to reduce
    # latency and server load
    # This implementation is very basic. Sadly Flask (0.10.1) does not have
    # this feature
    last_modified = flight.time_modified.strftime("%a, %d %b %Y %H:%M:%S GMT")
    modified_since = request.headers.get("If-Modified-Since")
    etag = request.headers.get("If-None-Match")
    if (modified_since and modified_since == last_modified) or (
        etag and etag == flight.igc_file.md5
    ):
        return ("", 304)

    trace = _get_flight_path(flight, threshold=0.0001, max_points=10000)
    if not trace:
        abort(404)

    model = AircraftModelSchema().dump(flight.model).data or None

    resp = make_response(
        jsonify(
            points=trace["points"],
            barogram_t=trace["barogram_t"],
            barogram_h=trace["barogram_h"],
            enl=trace["enl"],
            contests=trace["contests"],
            elevations_t=trace["elevations_t"],
            elevations_h=trace["elevations_h"],
            sfid=flight.id,
            geoid=trace["geoid"],
            additional=dict(
                registration=flight.registration,
                competition_id=flight.competition_id,
                model=model,
            ),
        )
    )

    resp.headers["Last-Modified"] = last_modified
    resp.headers["Etag"] = flight.igc_file.md5
    return resp

def update(flight_id):
    flight = get_requested_record(Flight, flight_id)

    current_user = User.get(request.user_id)
    if not flight.is_writable(current_user):
        return jsonify(), 403

    json = request.get_json()
    if json is None:
        return jsonify(error="invalid-request"), 400

    try:
        data = FlightSchema(partial=True).load(json).data
    except ValidationError, e:
        return jsonify(error="validation-failed", fields=e.messages), 422

    def airport(self, id, **kw):
        airport = get_requested_record(Airport, id)

        columns = {
            0: 'takeoff_time',
            1: 'olc_plus_score',
            2: 'display_name',
            3: 'olc_classic_distance',
            4: 'flights.club_id',
            5: 'models.name',
            6: 'takeoff_time',
            7: 'id',
            8: 'num_comments',
        }

        return self.__do_list('airport', kw, airport=airport, columns=columns)

def json(user_id):
    pilot = get_requested_record(User, user_id, joinedload=[User.club])
    last_update = request.values.get('last_update', 0, type=int)

    trace = _get_flight_path(pilot, threshold=0.001, last_update=last_update)
    if not trace:
        abort(404)

    return jsonify(
        points=trace['points'],
        barogram_t=trace['barogram_t'],
        barogram_h=trace['barogram_h'],
        elevations=trace['elevations'],
        enl=trace['enl'],
        geoid=trace['geoid'],
        sfid=pilot.id)

    def club(self, id, **kw):
        club = get_requested_record(Club, id)

        columns = {
            0: 'takeoff_time',
            1: 'olc_plus_score',
            2: 'display_name',
            3: 'olc_classic_distance',
            4: 'airports.name',
            5: 'models.name',
            6: 'takeoff_time',
            7: 'id',
            8: 'num_comments',
        }

        return self.__do_list('club', kw, club=club, columns=columns)

def followers(user_id):
    user = get_requested_record(User, user_id)

    # Query list of pilots that are following the selected user
    query = Follower.query(destination=user) \
        .join('source') \
        .options(contains_eager('source')) \
        .options(subqueryload('source.club')) \
        .order_by(User.name)

    user_schema = UserSchema(only=('id', 'name', 'club'))
    followers = user_schema.dump([follower.source for follower in query], many=True).data

    add_current_user_follows(followers)

    return jsonify(followers=followers)