def charactersbyuser(userid, form):
    if userid not in staff.MODS:
        raise WeasylError("Unexpected")

    query = d.execute("""
        SELECT
            ch.charid, pr.username, ch.unixtime,
            ch.char_name, ch.age, ch.gender, ch.height, ch.weight, ch.species,
            ch.content, ch.rating, ch.settings, ch.page_views, pr.config
        FROM character ch
        INNER JOIN profile pr ON ch.userid = pr.userid
        INNER JOIN login ON ch.userid = login.userid
        WHERE login.login_name = '%s'
    """, [d.get_sysname(form.name)])

    return [{
        "contype": 20,
        "userid": userid,
        "charid": item[0],
        "username": item[1],
        "unixtime": item[2],
        "title": item[3],
        "rating": item[10],
        "settings": item[11],
        "sub_media": character.fake_media_items(item[0], userid, d.get_sysname(item[1]), item[11]),
    } for item in query]

    def add_criterion(self, criterion):
        def add_nonempty(s, item):
            if item:
                s.add(item)

        find_modifier = _QUERY_FIND_MODIFIERS.get(criterion)

        if find_modifier:
            self.find = find_modifier
            return

        rating_modifier = _QUERY_RATING_MODIFIERS.get(criterion)

        if rating_modifier:
            self.ratings.add(rating_modifier)
            return

        if criterion.startswith(("user:", "+user:")):
            user = d.get_sysname(criterion.split(":", 1)[1])
            add_nonempty(self.required_user_includes, user)
        elif criterion.startswith("-user:"):
            user = d.get_sysname(criterion.split(":", 1)[1])
            add_nonempty(self.required_user_excludes, user)
        elif criterion.startswith("+"):
            tag = d.get_search_tag(criterion[1:])
            add_nonempty(self.required_includes, tag)
        elif criterion.startswith("-"):
            tag = d.get_search_tag(criterion[1:])
            add_nonempty(self.required_excludes, tag)
        elif criterion.startswith("|"):
            tag = d.get_search_tag(criterion[1:])
            add_nonempty(self.possible_includes, tag)
        else:
            tag = d.get_search_tag(criterion)
            add_nonempty(self.required_includes, tag)

def resolve(userid, otherid, othername, myself=True):
    """
    Attempts to determine the userid of a specified user; resolves using otherid,
    othername, and userid (if myself is True), in that order. If no userid can be
    resolved, returns 0 instead.
    """
    result = None

    if otherid:
        result = d.execute("SELECT userid FROM login WHERE userid = %i", [d.get_int(otherid)], ["element"])

        if result:
            return result
    elif othername:
        result = d.execute("SELECT userid FROM login WHERE login_name = '%s'", [d.get_sysname(othername)], ["element"])

        if result:
            return result

        result = d.execute("SELECT userid FROM useralias WHERE alias_name = '%s'", [d.get_sysname(othername)], ["element"])

        if result:
            return result
    elif userid and myself:
        return userid

    return 0

def tidy_submission(submission):
    submission['posted_at'] = d.iso8601(submission.pop('unixtime'))
    submission['sub_media'] = api.tidy_all_media(submission['sub_media'])
    if 'user_media' in submission:
        submission['owner_media'] = api.tidy_all_media(submission.pop('user_media'))
    submission.pop('userid', None)
    subtype = submission.pop('subtype', None)
    if subtype:
        submission['subtype'] = m.CATEGORY_PARSABLE_MAP[subtype // 1000 * 1000]
    contype = submission.pop('contype', None)
    if contype:
        submission['type'] = m.CONTYPE_PARSABLE_MAP[contype]
    submission['rating'] = ratings.CODE_TO_NAME[submission['rating']]
    submission['owner'] = submission.pop('username')
    submission['owner_login'] = d.get_sysname(submission['owner'])
    submission['media'] = submission.pop('sub_media')
    submitid = 0
    if 'submitid' in submission:
        submitid = submission['submitid']
    if 'charid' in submission:
        submitid = submission['charid']
    if submitid > 0:
        if submission['type'] == "usercollect":
            linktype = "submission"
        else:
            linktype = submission['type']
        submission['link'] = d.absolutify_url(
            "/%s/%d/%s" % (linktype, submitid, slug_for(submission['title'])))
    return submission

def select_view(userid, charid, rating, ignore=True, anyway=None):
    query = _select_character_and_check(
        userid, charid, rating=rating, ignore=ignore, anyway=anyway == 'anyway')

    login = define.get_sysname(query['username'])

    return {
        'charid': charid,
        'userid': query['userid'],
        'username': query['username'],
        'user_media': media.get_user_media(query['userid']),
        'mine': userid == query['userid'],
        'unixtime': query['unixtime'],
        'title': query['char_name'],
        'age': query['age'],
        'gender': query['gender'],
        'height': query['height'],
        'weight': query['weight'],
        'species': query['species'],
        'content': query['content'],
        'rating': query['rating'],
        'settings': query['settings'],
        'reported': report.check(charid=charid),
        'favorited': favorite.check(userid, charid=charid),
        'page_views': query['page_views'],
        'friends_only': 'f' in query['settings'],
        'hidden_submission': 'h' in query['settings'],
        'fave_count': favorite.count(charid, 'character'),
        'comments': comment.select(userid, charid=charid),
        'sub_media': fake_media_items(
            charid, query['userid'], login, query['settings']),
        'tags': searchtag.select(charid=charid),
    }

def select_view_api(userid, journalid, anyway=False, increment_views=False):
    rating = d.get_rating(userid)

    journal = _select_journal_and_check(
        userid, journalid,
        rating=rating, ignore=anyway, anyway=anyway, increment_views=increment_views)

    return {
        'journalid': journalid,
        'title': journal['title'],
        'owner': journal['username'],
        'owner_login': d.get_sysname(journal['username']),
        'owner_media': api.tidy_all_media(
            media.get_user_media(journal['userid'])),
        'content': text.markdown(journal['content']),
        'tags': searchtag.select(journalid=journalid),
        'link': d.absolutify_url('/journal/%d/%s' % (journalid, text.slug_for(journal['title']))),
        'type': 'journal',
        'rating': ratings.CODE_TO_NAME[journal['rating']],
        'views': journal['page_views'],
        'favorites': favorite.count(journalid, 'journal'),
        'comments': comment.count(journalid, 'journal'),
        'favorited': favorite.check(userid, journalid=journalid),
        'friends_only': 'f' in journal['settings'],
        'posted_at': d.iso8601(journal['unixtime']),
    }

def control_streaming_post_(request):
    form = request.web_input(target="", set_stream="", stream_length="", stream_url="", stream_text="")

    if form.target and request.userid not in staff.MODS:
        return Response(define.errorpage(request.userid, errorcode.permission))

    if form.target:
        target = int(form.target)
    else:
        target = request.userid

    stream_length = define.clamp(define.get_int(form.stream_length), 0, 360)
    p = orm.Profile()
    p.stream_text = form.stream_text
    p.stream_url = define.text_fix_url(form.stream_url.strip())
    set_stream = form.set_stream

    profile.edit_streaming_settings(request.userid, target, p,
                                    set_stream=set_stream,
                                    stream_length=stream_length)

    if form.target:
        target_username = define.get_sysname(define.get_display_name(target))
        raise HTTPSeeOther(location="/modcontrol/manageuser?name=" + target_username)
    else:
        raise HTTPSeeOther(location="/control")

def request(form):
    token = security.generate_key(100)
    email = emailer.normalize_address(form.email)
    username = d.get_sysname(form.username)

    # Determine the user associated with `username`; if the user is not found,
    # raise an exception
    user = d.engine.execute(
        "SELECT userid, email FROM login WHERE login_name = %(username)s",
        username=username).first()

    if not user:
        raise WeasylError("loginRecordMissing")

    # Check the user's email address against the provided e-mail address,
    # raising an exception if there is a mismatch
    if email != emailer.normalize_address(user.email):
        raise WeasylError("emailInvalid")

    # Insert a record into the forgotpassword table for the user,
    # or update an existing one
    now = d.get_time()
    address = d.get_address()

    d.engine.execute("""
        INSERT INTO forgotpassword (userid, token, set_time, address)
        VALUES (%(id)s, %(token)s, %(time)s, %(address)s)
        ON CONFLICT (userid) DO UPDATE SET
            token = %(token)s,
            set_time = %(time)s,
            address = %(address)s
    """, id=user.userid, token=token, time=now, address=address)

    # Generate and send an email to the user containing a password reset link
    emailer.append([email], None, "Weasyl Password Recovery", d.render("email/reset_password.html", [token]))

def create(form):
    # Normalize form data
    username = d.plaintext(form.username[:_USERNAME])
    sysname = d.get_sysname(username)

    email = emailer.normalize_address(form.email)
    emailcheck = emailer.normalize_address(form.emailcheck)

    password = form.password
    passcheck = form.passcheck

    if form.day and form.month and form.year:
        try:
            birthday = arrow.Arrow(int(form.year), int(form.month), int(form.day))
        except ValueError:
            raise WeasylError("birthdayInvalid")
    else:
        birthday = None

    # Check mismatched form data
    if password != passcheck:
        raise WeasylError("passwordMismatch")
    if email != emailcheck:
        raise WeasylError("emailMismatch")

    # Check invalid form data
    if birthday is None or d.age_in_years(birthday) < 13:
        raise WeasylError("birthdayInvalid")
    if not password_secure(password):
        raise WeasylError("passwordInsecure")
    if not email:
        raise WeasylError("emailInvalid")
    if not sysname or ";" in username:
        raise WeasylError("usernameInvalid")
    if sysname in ["admin", "administrator", "mod", "moderator", "weasyl",
                   "weasyladmin", "weasylmod", "staff", "security"]:
        raise WeasylError("usernameInvalid")
    if email_exists(email):
        raise WeasylError("emailExists")
    if username_exists(sysname):
        raise WeasylError("usernameExists")

    # Create pending account
    token = security.generate_key(40)

    d.engine.execute(d.meta.tables["logincreate"].insert(), {
        "token": token,
        "username": username,
        "login_name": sysname,
        "hashpass": passhash(password),
        "email": email,
        "birthday": birthday,
        "unixtime": arrow.now(),
    })

    # Queue verification email
    emailer.append([email], None, "Weasyl Account Creation", d.render(
        "email/verify_account.html", [token, sysname]))
    d.metric('increment', 'createdusers')

def _fake_media_items(i):
    if i.contype == _CONTYPE_CHAR:
        return character.fake_media_items(
            i.id,
            i.userid,
            d.get_sysname(i.username),
            i.settings)
    else:
        return None

def submission_(request):
    username = request.matchdict.get('name')
    submitid = request.matchdict.get('submitid')

    form = request.web_input(submitid="", ignore="", anyway="")

    rating = define.get_rating(request.userid)
    submitid = define.get_int(submitid) if submitid else define.get_int(form.submitid)

    extras = {
        "pdf": True,
    }

    if define.user_is_twitterbot():
        extras['twitter_card'] = submission.twitter_card(submitid)

    try:
        item = submission.select_view(
            request.userid, submitid, rating,
            ignore=define.text_bool(form.ignore, True), anyway=form.anyway
        )
    except WeasylError as we:
        we.errorpage_kwargs = extras
        if 'twitter_card' in extras:
            extras['options'] = ['nocache']
        if we.value in ("UserIgnored", "TagBlocked"):
            extras['links'] = [
                ("View Submission", "?ignore=false"),
                ("Return to the Home Page", "/index"),
            ]
        raise

    login = define.get_sysname(item['username'])
    canonical_path = request.route_path('submission_detail_profile', name=login, submitid=submitid, slug=slug_for(item['title']))

    if request.GET.get('anyway'):
        canonical_path += '?anyway=true'

    if login != username:
        raise httpexceptions.HTTPMovedPermanently(location=canonical_path)
    extras["canonical_url"] = canonical_path
    extras["title"] = item["title"]

    page = define.common_page_start(request.userid, **extras)
    page.append(define.render('detail/submission.html', [
        # Myself
        profile.select_myself(request.userid),
        # Submission detail
        item,
        # Subtypes
        macro.MACRO_SUBCAT_LIST,
        # Violations
        [i for i in macro.MACRO_REPORT_VIOLATION if 2000 <= i[0] < 3000],
    ]))

    return Response(define.common_page_end(request.userid, page))

 def prepare(info):
     dinfo = dict(info)
     dinfo['localmin'] = convert_currency(info.pricemin, info.pricesettings, currency)
     dinfo['localmax'] = convert_currency(info.pricemax, info.pricesettings, currency)
     if tags:
         terms = ["user:" + d.get_sysname(info.username)] + ["|" + tag for tag in tags]
         dinfo['searchquery'] = "q=" + urllib.quote(u" ".join(terms).encode("utf-8"))
     else:
         dinfo['searchquery'] = ""
     return dinfo

    def GET(self, a="", b=None):
        if b is None:
            username, submitid = None, a
        else:
            username, submitid = a, b
        now = time.time()

        form = web.input(submitid="", ignore="", anyway="")

        rating = define.get_rating(self.user_id)
        submitid = define.get_int(submitid) if submitid else define.get_int(form.submitid)

        extras = {
            "pdf": True,
        }

        if define.user_is_twitterbot():
            extras['twitter_card'] = submission.twitter_card(submitid)

        try:
            item = submission.select_view(
                self.user_id, submitid, rating,
                ignore=define.text_bool(form.ignore, True), anyway=form.anyway
            )
        except WeasylError as we:
            we.errorpage_kwargs = extras
            if 'twitter_card' in extras:
                extras['options'] = ['nocache']
            if we.value in ("UserIgnored", "TagBlocked"):
                extras['links'] = [
                    ("View Submission", "?ignore=false"),
                    ("Return to the Home Page", "/index"),
                ]
            raise

        login = define.get_sysname(item['username'])
        if username is not None and login != username:
            raise web.seeother('/~%s/post/%s/%s' % (login, submitid, slug_for(item["title"])))
        extras["canonical_url"] = "/submission/%d/%s" % (submitid, slug_for(item["title"]))
        extras["title"] = item["title"]

        page = define.common_page_start(self.user_id, options=["mediaplayer"], **extras)
        page.append(define.render('detail/submission.html', [
            # Myself
            profile.select_myself(self.user_id),
            # Submission detail
            item,
            # Subtypes
            macro.MACRO_SUBCAT_LIST,
            # Violations
            [i for i in macro.MACRO_REPORT_VIOLATION if 2000 <= i[0] < 3000],
        ]))

        return define.common_page_end(self.user_id, page, now=now)

    def POST(self):
        form = web.input(userid="")
        otherid = define.get_int(form.userid)

        if form.action == "ignore":
            if not ignoreuser.check(self.user_id, otherid):
                ignoreuser.insert(self.user_id, otherid)
        elif form.action == "unignore":
            ignoreuser.remove(self.user_id, otherid)

        raise web.seeother("/~%s" % (define.get_sysname(define.get_display_name(otherid))))

def ignoreuser_(request):
    form = request.web_input(userid="")
    otherid = define.get_int(form.userid)

    if form.action == "ignore":
        if not ignoreuser.check(request.userid, otherid):
            ignoreuser.insert(request.userid, otherid)
    elif form.action == "unignore":
        ignoreuser.remove(request.userid, otherid)

    raise HTTPSeeOther(location="/~%s" % (define.get_sysname(define.get_display_name(otherid))))

def select_char(userid, rating, limit, otherid=None, backid=None, nextid=None, config=None):
    if config is None:
        config = d.get_config(userid)
    query = []
    statement = ["""
        SELECT ch.charid, ch.char_name, ch.rating, fa.unixtime, ch.userid, pr.username, ch.settings
        FROM favorite fa
            INNER JOIN character ch ON fa.targetid = ch.charid
            INNER JOIN profile pr ON ch.userid = pr.userid
        WHERE fa.type = 'f'
            AND ch.settings !~ 'h'
    """]

    if userid:
        # filter own content in SFW mode
        if d.is_sfw_mode():
            statement.append(" AND (ch.rating <= %i)" % (rating,))
        else:
            statement.append(" AND (ch.userid = %i OR ch.rating <= %i)" % (userid, rating))
        statement.append(m.MACRO_FRIENDUSER_CHARACTER % (userid, userid, userid))
        statement.append(m.MACRO_IGNOREUSER % (userid, "ch"))
        statement.append(m.MACRO_BLOCKTAG_CHAR % (userid, userid))
    else:
        statement.append(" AND ch.rating <= %i AND ch.settings !~ 'f'" % (rating,))

    if otherid:
        statement.append(" AND fa.userid = %i" % (otherid,))

    if backid:
        statement.append(" AND fa.unixtime > "
                         "(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'f'))"
                         % (otherid, backid))
    elif nextid:
        statement.append(" AND fa.unixtime < "
                         "(SELECT unixtime FROM favorite WHERE (userid, targetid, type) = (%i, %i, 'f'))"
                         % (otherid, nextid))

    statement.append(" ORDER BY fa.unixtime%s LIMIT %i" % ("" if backid else " DESC", limit))

    from weasyl import character
    query = [{
        "contype": 20,
        "charid": i[0],
        "title": i[1],
        "rating": i[2],
        "unixtime": i[3],
        "userid": i[4],
        "username": i[5],
        "sub_media": character.fake_media_items(i[0], i[4], d.get_sysname(i[5]), i[6]),
    } for i in d.execute("".join(statement))]

    return query[::-1] if backid else query

def get_account_verification_token(email=None, username=None):
    email = email and emailer.normalize_address(email)
    username = username and d.get_sysname(username)

    logincreate = d.meta.tables['logincreate']
    statement = select([logincreate.c.token])

    if email:
        statement = statement.where(logincreate.c.email.ilike(email))
    else:
        statement = statement.where(logincreate.c.login_name == username)

    return d.engine.scalar(statement)

def select(**kwargs):
    search = kwargs['search']
    results, next_count, back_count = _find_without_media(**kwargs)

    if search.find == 'submit':
        media.populate_with_submission_media(results)
    elif search.find == 'char':
        for r in results:
            r['sub_media'] = character.fake_media_items(
                r['charid'], r['userid'], d.get_sysname(r['username']), r['settings'])
    elif search.find == 'journal':
        media.populate_with_user_media(results)

    return results, next_count, back_count

def followuser_(request):
    form = request.web_input(userid="")
    otherid = define.get_int(form.userid)

    if request.userid == otherid:
        return Response(define.errorpage(request.userid, "You cannot follow yourself."))

    if form.action == "follow":
        if not followuser.check(request.userid, otherid):
            followuser.insert(request.userid, otherid)
    elif form.action == "unfollow":
        followuser.remove(request.userid, otherid)

    raise HTTPSeeOther(location="/~%s" % (define.get_sysname(define.get_display_name(otherid))))

def journalsbyuser(userid, form):
    if userid not in staff.MODS:
        raise WeasylError("Unexpected")

    results = d.engine.execute("""
        SELECT journalid, title, journal.settings, journal.unixtime, rating,
               profile.username, 30 contype
          FROM journal
               JOIN profile USING (userid)
               JOIN login USING (userid)
         WHERE login_name = %(sysname)s
    """, sysname=d.get_sysname(form.name)).fetchall()

    return map(dict, results)