def last_activity(app_id):
    sql = text('''SELECT finish_time FROM task_run WHERE app_id=:app_id
               ORDER BY finish_time DESC LIMIT 1''')
    results = db.engine.execute(sql, app_id=app_id)
    for row in results:
        if row is not None:
            print pretty_date(row[0])
            return pretty_date(row[0])
        else:
            return None

def get_all(category):
    """Return a list of projects with at least one task and a task_presenter.
    """
    sql = text('''SELECT project.id, project.name, project.short_name,
               project.description, project.info, project.created, project.updated,
               project.category_id, project.featured, "user".fullname AS owner
               FROM "user", task, project
               LEFT OUTER JOIN category ON project.category_id=category.id
               WHERE
               category.short_name=:category
               AND project.hidden=0
               AND "user".id=project.owner_id
               AND project.info LIKE('%task_presenter%')
               AND task.project_id=project.id
               GROUP BY project.id, "user".id ORDER BY project.name;''')

    results = session.execute(sql, dict(category=category))
    projects = []
    for row in results:
        project = dict(id=row.id,
                       name=row.name, short_name=row.short_name,
                       created=row.created,
                       updated=row.updated,
                       description=row.description,
                       owner=row.owner,
                       featured=row.featured,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=dict(json.loads(row.info)))
        projects.append(project)
    return projects

def get_users_page(page, per_page=24):
    """Return users with a paginator."""
    offset = (page - 1) * per_page
    sql = text(
        """SELECT "user".id, "user".name,
               "user".fullname, "user".email_addr,
               "user".created, "user".info, COUNT(task_run.id) AS task_runs
               FROM task_run, "user"
               WHERE "user".id=task_run.user_id GROUP BY "user".id
               ORDER BY "user".created DESC LIMIT :limit OFFSET :offset"""
    )
    results = session.execute(sql, dict(limit=per_page, offset=offset))
    accounts = []
    for row in results:
        user = dict(
            id=row.id,
            name=row.name,
            fullname=row.fullname,
            email_addr=row.email_addr,
            created=row.created,
            task_runs=row.task_runs,
            info=row.info,
            registered_ago=pretty_date(row.created),
        )
        accounts.append(user)
    return accounts

def get_all_draft(category=None):
    """Return list of all draft projects."""
    sql = text(
        """SELECT project.id, project.name, project.short_name, project.created,
               project.description, project.info, project.updated, "user".fullname as owner
               FROM "user", project LEFT JOIN task ON project.id=task.project_id
               WHERE task.project_id IS NULL
               AND (project.info->>'task_presenter') IS NULL
               AND project.hidden=0
               AND project.owner_id="user".id;"""
    )

    results = session.execute(sql)
    projects = []
    for row in results:
        project = dict(
            id=row.id,
            name=row.name,
            short_name=row.short_name,
            created=row.created,
            updated=row.updated,
            description=row.description,
            owner=row.owner,
            last_activity=pretty_date(last_activity(row.id)),
            last_activity_raw=last_activity(row.id),
            overall_progress=overall_progress(row.id),
            n_tasks=n_tasks(row.id),
            n_volunteers=n_volunteers(row.id),
            info=row.info,
        )
        projects.append(project)
    return projects

def get(category, page=1, per_page=5):
    """Return a list of apps with at least one task and a task_presenter
       with a pagination for a given category"""
    sql = text('''SELECT app.id, app.name, app.short_name, app.description,
               app.info, app.created, app.category_id, app.featured, "user".fullname AS owner
               FROM "user", task, app
               LEFT OUTER JOIN category ON app.category_id=category.id
               WHERE
               category.short_name=:category
               AND app.hidden=0
               AND "user".id=app.owner_id
               AND app.info LIKE('%task_presenter%')
               AND task.app_id=app.id
               GROUP BY app.id, "user".id ORDER BY app.name
               OFFSET :offset
               LIMIT :limit;''')

    offset = (page - 1) * per_page
    results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
    apps = []
    for row in results:
        app = dict(id=row.id,
                   name=row.name, short_name=row.short_name,
                   created=row.created,
                   description=row.description,
                   owner=row.owner,
                   featured=row.featured,
                   last_activity=pretty_date(last_activity(row.id)),
                   last_activity_raw=last_activity(row.id),
                   overall_progress=overall_progress(row.id),
                   n_tasks=n_tasks(row.id),
                   n_volunteers=n_volunteers(row.id),
                   info=dict(json.loads(row.info)))
        apps.append(app)
    return apps

def manage_users(page):
    """
    Admin page for all PyBossa registered users.
    Returns a Jinja2 rendered template with the users.

    Note ::
    This would be an admin only page, hence, rendering cached data
    not necessary. Instead the admin would rather want the most updated data
    """
    per_page = 24
    pagination = User.query.paginate(page, per_page, False)
    accounts = pagination.items
    count = pagination.total

    """
    Normalize accounts for it to be rendered by the global helper functions we use in the theme
    """
    for k in accounts:
        k.n_task_runs = len(k.task_runs)
        k.registered_ago = pretty_date(k.created)

    if not accounts and page !=1 and not current_user.admin:
        abort(404)

    if current_user.is_authenticated():
        user_id = current_user.id
    else:
        user_id = 'anonymous'
    return render_template('geotagx/users/index.html', accounts = accounts,
                           total = count, pagination_page = str(page),
                           title = "Community", pagination = pagination)

def get_all_draft(category=None):
    """Return list of all draft projects."""
    sql = text(
        '''SELECT project.id, project.name, project.short_name, project.created,
            project.description, project.info, project.updated,
            "user".fullname AS owner
           FROM "user", project
           WHERE project.owner_id="user".id
           AND project.published=false;''')

    results = session.execute(sql)
    projects = []
    for row in results:
        project = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created,
                       updated=row.updated,
                       description=row.description,
                       owner=row.owner,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=row.info)
        projects.append(Project().to_public_json(project))
    return projects

def get_draft(category, page=1, per_page=5):
    """Return list of draft applications"""

    count = n_draft()

    sql = text('''
               SELECT app.id, app.name, app.short_name, app.created,
               app.description, app.info, "user".fullname as owner
               FROM "user", app LEFT JOIN task ON app.id=task.app_id
               WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
               AND app.hidden=0
               AND app.owner_id="user".id
               OFFSET :offset
               LIMIT :limit;''')

    offset = (page - 1) * per_page
    results = db.engine.execute(sql, limit=per_page, offset=offset)
    apps = []
    for row in results:
        app = dict(id=row.id, name=row.name, short_name=row.short_name,
                   created=row.created,
                   description=row.description,
                   owner=row.owner,
                   last_activity=pretty_date(last_activity(row.id)),
                   last_activity_raw=last_activity(row.id),
                   overall_progress=overall_progress(row.id),
                   info=dict(json.loads(row.info)))
        apps.append(app)
    return apps, count

def get_user_summary(name):
    """Return user summary."""
    sql = text('''
               SELECT "user".id, "user".name, "user".fullname, "user".created,
               "user".api_key, "user".twitter_user_id, "user".facebook_user_id,
               "user".google_user_id, "user".info,
               "user".email_addr, COUNT(task_run.user_id) AS n_answers,
               "user".valid_email, "user".confirmation_email_sent
               FROM "user"
               LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
               WHERE "user".name=:name
               GROUP BY "user".id;
               ''')
    results = session.execute(sql, dict(name=name))
    user = dict()
    for row in results:
        user = dict(id=row.id, name=row.name, fullname=row.fullname,
                    created=row.created, api_key=row.api_key,
                    twitter_user_id=row.twitter_user_id,
                    google_user_id=row.google_user_id,
                    facebook_user_id=row.facebook_user_id,
                    info=dict(json.loads(row.info)),
                    email_addr=row.email_addr, n_answers=row.n_answers,
                    valid_email=row.valid_email,
                    confirmation_email_sent=row.confirmation_email_sent,
                    registered_ago=pretty_date(row.created))
    if user:
        rank_score = rank_and_score(user['id'])
        user['rank'] = rank_score['rank']
        user['score'] = rank_score['score']
        user['total'] = get_total_users()
        return user
    else:  # pragma: no cover
        return None

def get_featured(category=None, page=1, per_page=5):
    """Return a list of featured apps with a pagination"""
    sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
               app.description,
               "user".fullname AS owner FROM app, "user"
               WHERE app.featured=true AND app.hidden=0
               AND "user".id=app.owner_id GROUP BY app.id, "user".id
               OFFSET(:offset) LIMIT(:limit);
               ''')

    offset = (page - 1) * per_page
    results = session.execute(sql, dict(limit=per_page, offset=offset))
    apps = []
    for row in results:
        app = dict(id=row.id, name=row.name, short_name=row.short_name,
                   created=row.created, description=row.description,
                   last_activity=pretty_date(last_activity(row.id)),
                   last_activity_raw=last_activity(row.id),
                   owner=row.owner,
                   overall_progress=overall_progress(row.id),
                   n_tasks=n_tasks(row.id),
                   n_volunteers=n_volunteers(row.id),
                   info=dict(json.loads(row.info)))
        apps.append(app)
    return apps

def get_featured(category, page=1, per_page=5):
    """Return a list of featured apps with a pagination"""
    try:
        count = n_featured()

        sql = text('''SELECT app.id, app.name, app.short_name, app.info, app.created,
                   app.description,
                   "user".fullname AS owner FROM app, featured, "user"
                   WHERE app.id=featured.app_id AND app.hidden=0
                   AND "user".id=app.owner_id GROUP BY app.id, "user".id
                   OFFSET(:offset) LIMIT(:limit);
                   ''')
        offset = (page - 1) * per_page

        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(limit=per_page, offset=offset))
        apps = []
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created, description=row.description,
                       overall_progress=overall_progress(row.id),
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       owner=row.owner,
                       featured=row.id,
                       info=dict(json.loads(row.info)))
            apps.append(app)
        return apps, count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_draft(category, page=1, per_page=5):
    """Return list of draft projects"""
    try:
        count = n_draft()

        sql = text('''SELECT app.id, app.name, app.short_name, app.created,
                   app.description, app.info, "user".fullname as owner
                   FROM "user", app LEFT JOIN task ON app.id=task.app_id
                   WHERE task.app_id IS NULL AND app.info NOT LIKE('%task_presenter%')
                   AND app.hidden=0
                   AND app.owner_id="user".id
                   OFFSET :offset
                   LIMIT :limit;''')

        offset = (page - 1) * per_page
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(limit=per_page, offset=offset))
        apps = []
        for row in results:
            app = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created,
                       description=row.description,
                       owner=row.owner,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       info=dict(json.loads(row.info)))
            apps.append(app)
        return apps, count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_all_featured(category=None):
    """Return a list of featured projects with a pagination."""
    sql = text(
        '''SELECT project.id, project.name, project.short_name, project.info,
               project.created, project.updated, project.description,
               "user".fullname AS owner
           FROM project, "user"
           WHERE project.featured=true
           AND "user".id=project.owner_id
           GROUP BY project.id, "user".id;''')

    results = session.execute(sql)
    projects = []
    for row in results:
        project = dict(id=row.id, name=row.name, short_name=row.short_name,
                       created=row.created, description=row.description,
                       updated=row.updated,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       owner=row.owner,
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=row.info)
        projects.append(Project().to_public_json(project))
    return projects

def get_all(category):
    """Return a list of published projects for a given category.
    """
    sql = text(
        '''SELECT project.id, project.name, project.short_name,
           project.description, project.info, project.created, project.updated,
           project.category_id, project.featured, "user".fullname AS owner
           FROM "user", project
           LEFT OUTER JOIN category ON project.category_id=category.id
           WHERE
           category.short_name=:category
           AND "user".id=project.owner_id
           AND project.published=true
           AND (project.info->>'passwd_hash') IS NULL
           GROUP BY project.id, "user".id ORDER BY project.name;''')

    results = session.execute(sql, dict(category=category))
    projects = []
    for row in results:
        project = dict(id=row.id,
                       name=row.name, short_name=row.short_name,
                       created=row.created,
                       updated=row.updated,
                       description=row.description,
                       owner=row.owner,
                       featured=row.featured,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       n_tasks=n_tasks(row.id),
                       n_volunteers=n_volunteers(row.id),
                       info=row.info)
        projects.append(Project().to_public_json(project))
    return projects

def get_users_page(page, per_page=24):
    offset = (page - 1) * per_page
    sql = text('''SELECT "user".id, "user".name, "user".fullname, "user".email_addr,
               "user".created, "user".info, COUNT(task_run.id) AS task_runs
               FROM task_run, "user"
               WHERE "user".id=task_run.user_id GROUP BY "user".id
               ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
    results = db.engine.execute(sql, limit=per_page, offset=offset)
    accounts = []
    for row in results:
        user = dict(id=row.id, name=row.name, fullname=row.fullname,
                    email_addr=row.email_addr, created=row.created,
                    task_runs=row.task_runs, info=dict(json.loads(row.info)),
                    registered_ago=pretty_date(row.created))
        accounts.append(user)
    return accounts

def get(category, page=1, per_page=5):
    """Return a list of apps with at least one task and a task_presenter
       with a pagination for a given category"""
    try:
        count = n_count(category)

        sql = text('''SELECT app.id, app.name, app.short_name, app.description,
                   app.info, app.created, app.category_id, "user".fullname AS owner,
                   featured.app_id as featured
                   FROM "user", task, app
                   LEFT OUTER JOIN category ON app.category_id=category.id
                   LEFT OUTER JOIN featured ON app.id=featured.app_id
                   WHERE
                   category.short_name=:category
                   AND app.hidden=0
                   AND "user".id=app.owner_id
                   AND app.info LIKE('%task_presenter%')
                   AND task.app_id=app.id
                   GROUP BY app.id, "user".id, featured.app_id ORDER BY app.name
                   OFFSET :offset
                   LIMIT :limit;''')

        offset = (page - 1) * per_page
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(category=category, limit=per_page, offset=offset))
        apps = []
        for row in results:
            app = dict(id=row.id,
                       name=row.name, short_name=row.short_name,
                       created=row.created,
                       description=row.description,
                       owner=row.owner,
                       featured=row.featured,
                       last_activity=pretty_date(last_activity(row.id)),
                       last_activity_raw=last_activity(row.id),
                       overall_progress=overall_progress(row.id),
                       info=dict(json.loads(row.info)))
            apps.append(app)
        return apps, count
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_users_page(page, per_page=24):
    try:
        offset = (page - 1) * per_page
        sql = text('''SELECT "user".id, "user".name, "user".fullname, "user".email_addr,
                   "user".created, "user".info, COUNT(task_run.id) AS task_runs
                   FROM task_run, "user"
                   WHERE "user".id=task_run.user_id GROUP BY "user".id
                   ORDER BY "user".created DESC LIMIT :limit OFFSET :offset''')
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(limit=per_page, offset=offset))
        accounts = []
        for row in results:
            user = dict(id=row.id, name=row.name, fullname=row.fullname,
                        email_addr=row.email_addr, created=row.created,
                        task_runs=row.task_runs, info=dict(json.loads(row.info)),
                        registered_ago=pretty_date(row.created))
            accounts.append(user)
        return accounts
    except: # pragma: no cover
        session.rollback()
        raise
    finally:
        session.close()

def get_user_summary(name):
    try:
        # Get USER
        sql = text('''
                   SELECT "user".id, "user".name, "user".fullname, "user".created,
                   "user".api_key, "user".twitter_user_id, "user".facebook_user_id,
                   "user".google_user_id, "user".info,
                   "user".email_addr, COUNT(task_run.user_id) AS n_answers
                   FROM "user" LEFT OUTER JOIN task_run ON "user".id=task_run.user_id
                   WHERE "user".name=:name
                   GROUP BY "user".id;
                   ''')
        session = get_session(db, bind='slave')
        results = session.execute(sql, dict(name=name))
        user = dict()
        for row in results:
            user = dict(id=row.id, name=row.name, fullname=row.fullname,
                        created=row.created, api_key=row.api_key,
                        twitter_user_id=row.twitter_user_id,
                        google_user_id=row.google_user_id,
                        facebook_user_id=row.facebook_user_id,
                        info=dict(json.loads(row.info)),
                        email_addr=row.email_addr, n_answers=row.n_answers,
                        registered_ago=pretty_date(row.created))
        if user:
            rank_score = rank_and_score(user['id'])
            user['rank'] = rank_score['rank']
            user['score'] = rank_score['score']
            user['total'] = get_total_users()
            return user
        else: # pragma: no cover
            return None
    except: # pragma: no cover
        session.rollback()
    finally:
        session.close()

def last_activity(app):
    if (len(app.task_runs) >= 1):
        return pretty_date(app.task_runs[0].finish_time)
    else:
        return "None"

 def pretty_date_filter(s):
     return pretty_date(s)