def select_query(userid, rating, otherid=None, folderid=None,
                 backid=None, nextid=None, subcat=None, exclude=None,
                 options=[], config=None, profile_page_filter=False,
                 index_page_filter=False, featured_filter=False):
    if config is None:
        config = d.get_config(userid)
    statement = [
        "FROM submission su "
        "INNER JOIN profile pr ON su.userid = pr.userid "
        "LEFT JOIN folder f USING (folderid) "
        "WHERE su.settings !~ 'h'"]
    if profile_page_filter:
        statement.append(" AND COALESCE(f.settings !~ 'u', true)")
    if index_page_filter:
        statement.append(" AND COALESCE(f.settings !~ 'm', true)")
    if featured_filter:
        statement.append(" AND COALESCE(f.settings ~ 'f', false)")

    # Logged in users will see their own submissions regardless of rating
    # EXCEPT if they are in SFW mode
    if userid and not d.is_sfw_mode():
        statement.append(" AND (su.rating <= %i OR su.userid = %i)" % (rating, userid))
    else:
        statement.append(" AND su.rating <= %i" % (rating,))

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

    if folderid:
        statement.append(" AND su.folderid = %i" % (folderid,))

    if exclude:
        statement.append(" AND su.submitid != %i" % (exclude,))

    if subcat:
        statement.append(" AND su.subtype >= %i AND su.subtype < %i" % (subcat, subcat + 1000))

    if "critique" in options:
        statement.append(" AND su.settings ~ 'q' AND su.unixtime > %i" % (d.get_time() - 259200,))

    if backid:
        statement.append(" AND su.submitid > %i" % (backid,))
    elif nextid:
        statement.append(" AND su.submitid < %i" % (nextid,))
    elif "offset" in options:
        statement.append(" AND su.unixtime < %i" % (d.get_time() - 1800,))

    if userid:
        statement.append(m.MACRO_FRIENDUSER_SUBMIT % (userid, userid, userid))

        if not otherid:
            statement.append(m.MACRO_IGNOREUSER % (userid, "su"))

        statement.append(m.MACRO_BLOCKTAG_SUBMIT % (userid, userid))
    else:
        statement.append(" AND su.settings !~ 'f'")
    return statement

def prepare(token):
    # Remove records from the forgotpassword table which have been active for
    # more than one hour, regardless of whether or not the user has clicked the
    # associated link provided to them in the password reset request email, or
    # which have been visited but have not been removed by the password reset
    # script within five minutes of being visited
    d.execute("DELETE FROM forgotpassword WHERE set_time < %i OR link_time > 0 AND link_time < %i",
              [d.get_time() - 3600, d.get_time() - 300])

    # Set the unixtime record for which the link associated with `token` was
    # visited by the user
    d.execute("UPDATE forgotpassword SET link_time = %i WHERE token = '%s'",
              [d.get_time(), token])

def select_streaming(userid, rating, limit, following=True, order_by=None):
    statement = [
        "SELECT userid, pr.username, pr.stream_url, pr.config, pr.stream_text, start_time "
        "FROM profile pr "
        "JOIN user_streams USING (userid) "
        "WHERE end_time > %i" % (d.get_time(),)
    ]

    if userid:
        statement.append(m.MACRO_IGNOREUSER % (userid, "pr"))

        if following:
            pass  # todo
    if order_by:
        statement.append(" ORDER BY %s LIMIT %i" % (order_by, limit))
    else:
        statement.append(" ORDER BY RANDOM() LIMIT %i" % limit)

    ret = [{
        "userid": i[0],
        "username": i[1],
        "stream_url": i[2],
        "stream_text": i[4],
        "stream_time": i[5],
    } for i in d.execute("".join(statement)) if i[2]]

    media.populate_with_user_media(ret)
    return ret

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

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

    # If `user_id` exists, then the supplied email was valid; if not valid, do nothing, raising
    #   no errors for plausible deniability of email existence
    if user_id:
        # 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_id, 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 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 test_login_fails_if_user_is_suspended():
    user_id = db_utils.create_user(password=raw_password, username=user_name)
    d.engine.execute("UPDATE login SET settings = 's' WHERE userid = %(id)s", id=user_id)
    release_date = d.get_time() + 60
    d.engine.execute("INSERT INTO suspension VALUES (%(id)s, %(reason)s, %(rel)s)",
                     id=user_id, reason='test', rel=release_date)
    result = login.authenticate_bcrypt(username=user_name, password=raw_password, request=None)
    assert result == (user_id, 'suspended')

def signin(userid):
    # Update the last login record for the user
    d.execute("UPDATE login SET last_login = %i WHERE userid = %i", [d.get_time(), userid])

    # set the userid on the session
    sess = d.get_weasyl_session()
    sess.userid = userid
    sess.save = True

def run_periodic_tasks():
    now = arrow.utcnow()
    time_now = get_time()

    db = engine.connect()
    with db.begin():
        locked = db.scalar("SELECT pg_try_advisory_xact_lock(0)")
        if not locked:
            return
        last_run = arrow.get(db.scalar("SELECT last_run FROM cron_runs"))
        if not last_run or now < last_run.replace(seconds=59):
            return

        # Recache the latest submissions
        # Every 2 minutes
        if now.minute % 2 == 0:
            index.recent_submissions.refresh()
            log.msg('refreshed recent submissions')

        # Recache the active user counts
        # Every 5 minutes
        if now.minute % 5 == 0:
            active_users.refresh()
            log.msg('refreshed active user counts')

        # Recalculate recently popular submissions
        # Every 10 minutes
        if now.minute % 10 == 0:
            submission.select_recently_popular.refresh()
            log.msg('refreshed recently popular submissions')

        # Delete all records from views table
        # Every 15 minutes
        if now.minute % 15 == 0:
            db.execute("DELETE FROM views")
            log.msg('cleared views')

        # Daily at 0:00
        if now.hour == 0 and now.minute == 0:
            # Delete password resets older than one day
            db.execute("DELETE FROM forgotpassword WHERE set_time < %(expiry)s", expiry=time_now - 86400)
            log.msg('cleared old forgotten password requests')

            # Delete email reset requests older than two days
            db.execute("""
                DELETE FROM emailverify
                WHERE createtimestamp < (NOW() - INTERVAL '2 days')
            """)
            log.msg('cleared stale email change records')

            # Purge stale logincreate records older than seven days
            db.execute("""
                DELETE FROM logincreate
                WHERE unixtime < %(time)s
            """, time=now - (86400 * 2))
            log.msg('cleared stale account creation records')

        db.execute("UPDATE cron_runs SET last_run = %(now)s", now=now.naive)

def remove_all_submissions(userid, only_before=None):
    if not only_before:
        only_before = d.get_time()

    d.engine.execute(
        "DELETE FROM welcome WHERE userid = %(user)s AND type IN (2010, 2030, 2040, 2050) AND unixtime < %(before)s",
        user=userid, before=only_before)

    d._page_header_info.invalidate(userid)

def force_resetbirthday(userid, birthday):
    if not birthday:
        raise WeasylError("birthdayInvalid")
    elif birthday > d.get_time():
        raise WeasylError("birthdayInvalid")

    d.execute("UPDATE userinfo SET birthday = %i WHERE userid = %i", [birthday, userid])
    d.execute("UPDATE login SET settings = REPLACE(settings, 'i', '') WHERE userid = %i", [userid])
    d.get_login_settings.invalidate(userid)

def test_stale_records_get_deleted_when_function_is_called():
    token_store = []
    for i in range(20):
        user_name = "testPrepare%d" % (i,)
        email_addr = "test%[email protected]" % (i,)
        user_id = db_utils.create_user(email_addr=email_addr, username=user_name)
        form_for_request = Bag(email=email_addr, username=user_name, day=arrow.now().day,
                               month=arrow.now().month, year=arrow.now().year)
        resetpassword.request(form_for_request)
        pw_reset_token = d.engine.scalar("SELECT token FROM forgotpassword WHERE userid = %(id)s", id=user_id)
        token_store.append(pw_reset_token)
    # All tokens should exist at this point
    for i in range(20):
        assert resetpassword.checktoken(token_store[i])
    # Set 5 tokens to be two hours old (0,5) (7200)
    for i in range(0, 5):
        d.engine.execute("UPDATE forgotpassword SET set_time = %(time)s WHERE token = %(token)s",
                         time=d.get_time() - 7200, token=token_store[i])
    # Set 5 tokens to be 30 minutes old (5,10) (1800)
    for i in range(5, 10):
        d.engine.execute("UPDATE forgotpassword SET set_time = %(time)s WHERE token = %(token)s",
                         time=d.get_time() - 1800, token=token_store[i])
    # Set 5 tokens to be 10 minutes old for the last visit time (10,15) (600)
    for i in range(10, 15):
        d.engine.execute("UPDATE forgotpassword SET link_time = %(time)s WHERE token = %(token)s",
                         time=d.get_time() - 600, token=token_store[i])
    # Set 5 tokens to be 2 minutes old for the last visit time (10,15) (120)
    for i in range(15, 20):
        d.engine.execute("UPDATE forgotpassword SET link_time = %(time)s WHERE token = %(token)s",
                         time=d.get_time() - 120, token=token_store[i])
    # This should clear all tokens >1hr old, and all tokens >5 minutes from last visit (10 total)
    resetpassword.prepare('foo')
    # This range should be cleared (set_time > 3600)
    for i in range(0, 5):
        assert not resetpassword.checktoken(token_store[i])
    # This range should still be present (set_time < 3600)
    for i in range(5, 10):
        assert resetpassword.checktoken(token_store[i])
    # This range should be cleared (link_time > 300)
    for i in range(10, 15):
        assert not resetpassword.checktoken(token_store[i])
    # This range should still be present (link_time < 300)
    for i in range(15, 20):
        assert resetpassword.checktoken(token_store[i])

def test_link_time_field_is_updated_when_valid_token_supplied_to_function():
    user_name = "test"
    email_addr = "[email protected]"
    user_id = db_utils.create_user(email_addr=email_addr, username=user_name)
    form_for_request = Bag(email=email_addr, username=user_name, day=arrow.now().day,
                           month=arrow.now().month, year=arrow.now().year)
    resetpassword.request(form_for_request)
    pw_reset_token = d.engine.scalar("SELECT token FROM forgotpassword WHERE userid = %(id)s", id=user_id)
    resetpassword.prepare(pw_reset_token)
    link_time = d.engine.scalar("SELECT link_time FROM forgotpassword WHERE token = %(token)s", token=pw_reset_token)
    assert link_time >= d.get_time()

def test_true_returned_if_token_exists():
    user_id = db_utils.create_user(username='checktoken0002')
    token = "testtokentesttokentesttokentesttokentesttokentesttokentesttokentesttokentesttokentesttokentest000001"
    d.engine.execute(d.meta.tables["forgotpassword"].insert(), {
        "userid": user_id,
        "token": token,
        "set_time": d.get_time(),
        "link_time": 0,
        "address": d.get_address(),
    })
    assert resetpassword.checktoken(token)

def signin(userid):
    # Update the last login record for the user
    d.execute("UPDATE login SET last_login = %i WHERE userid = %i", [d.get_time(), userid])

    # Log the successful login and increment the login count
    d.append_to_log('login.success', userid=userid, ip=d.get_address())
    d.metric('increment', 'logins')

    # set the userid on the session
    sess = d.get_weasyl_session()
    sess.userid = userid
    sess.save = True

def insert(userid, submitid=None, charid=None, journalid=None):
    if submitid:
        content_table, id_field, target = "submission", "submitid", submitid
    elif charid:
        content_table, id_field, target = "character", "charid", charid
    else:
        content_table, id_field, target = "journal", "journalid", journalid

    query = d.execute("SELECT userid, settings FROM %s WHERE %s = %i",
                      [content_table, id_field, target], options="single")

    if not query:
        raise WeasylError("TargetRecordMissing")
    elif userid == query[0]:
        raise WeasylError("CannotSelfFavorite")
    elif "f" in query[1] and not frienduser.check(userid, query[0]):
        raise WeasylError("FriendsOnly")
    elif ignoreuser.check(userid, query[0]):
        raise WeasylError("YouIgnored")
    elif ignoreuser.check(query[0], userid):
        raise WeasylError("contentOwnerIgnoredYou")

    insert_result = d.engine.execute(
        'INSERT INTO favorite (userid, targetid, type, unixtime) '
        'VALUES (%(user)s, %(target)s, %(type)s, %(now)s) '
        'ON CONFLICT DO NOTHING',
        user=userid,
        target=d.get_targetid(submitid, charid, journalid),
        type='s' if submitid else 'f' if charid else 'j',
        now=d.get_time())

    if insert_result.rowcount == 0:
        return

    # create a list of users to notify
    notified = set(collection.find_owners(submitid))

    # conditions under which "other" should be notified
    def can_notify(other):
        other_jsonb = d.get_profile_settings(other)
        allow_notify = other_jsonb.allow_collection_notifs
        not_ignored = not ignoreuser.check(other, userid)
        return allow_notify and not_ignored
    notified = set(filter(can_notify, notified))
    # always notify for own content
    notified.add(query[0])

    for other in notified:
        welcome.favorite_insert(userid, submitid=submitid, charid=charid, journalid=journalid, otherid=other)

def edit_streaming_settings(my_userid, userid, profile, set_stream=None, stream_length=0):

    if set_stream == 'start':
        try:
            stream_length = int(stream_length)
        except:
            raise WeasylError("streamDurationOutOfRange")

        if stream_length < 1 or stream_length > 360:
            raise WeasylError("streamDurationOutOfRange")

    if set_stream == 'start' and not profile.stream_url:
        raise WeasylError("streamLocationNotSet")

    # unless we're specifically still streaming, clear the user_streams record
    if set_stream != 'still':
        d.execute("DELETE FROM user_streams WHERE userid = %i", [userid])

    settings_flag = ''
    stream_status = None
    # if we're starting to stream, update user_streams to reflect that
    if set_stream == 'start':
        now = d.get_time()
        stream_end = now + stream_length * 60  # stream_length is minutes; we need seconds
        d.execute("INSERT INTO user_streams VALUES (%i, %i, %i)", [userid, now, stream_end])
        stream_status = 'n'
    # if we're going to stream later, update profile.settings to reflect that
    elif set_stream == 'later':
        settings_flag = stream_status = 'l'

    # if stream_status is None, any rows in `welcome` will get cleared. but, if
    # the user is still streaming, that shouldn't happen. otherwise, `welcome`
    # will get updated with the current stream state.
    if set_stream != 'still':
        welcome.stream_insert(userid, stream_status)

    d.execute(
        "UPDATE profile "
        "SET (stream_text, stream_url, settings) = ('%s', '%s', REGEXP_REPLACE(settings, '[nli]', '') || '%s') "
        "WHERE userid = %i",
        [profile.stream_text, profile.stream_url, settings_flag, userid])

    if my_userid != userid:
        from weasyl import moderation
        note_body = (
            '- Stream url: %s\n'
            '- Stream description: %s\n'
            '- Stream status: %s' % (profile.stream_url, profile.stream_text, STREAMING_ACTION_MAP[set_stream]))
        moderation.note_about(my_userid, userid, 'Streaming settings updated:', note_body)

def reset(form):
    import login

    # Raise an exception if `password` does not enter `passcheck` (indicating
    # that the user mistyped one of the fields) or if `password` does not meet
    # the system's password security requirements
    if form.password != form.passcheck:
        raise WeasylError("passwordMismatch")
    elif not login.password_secure(form.password):
        raise WeasylError("passwordInsecure")

    # Select the user information and record data from the forgotpassword table
    # pertaining to `token`, requiring that the link associated with the record
    # be visited no more than five minutes prior; if the forgotpassword record is
    # not found or does not meet this requirement, raise an exception
    query = d.execute("""
        SELECT lo.userid, lo.login_name, lo.email, fp.link_time, fp.address
        FROM login lo
            INNER JOIN userinfo ui USING (userid)
            INNER JOIN forgotpassword fp USING (userid)
        WHERE fp.token = '%s' AND fp.link_time > %i
    """, [form.token, d.get_time() - 300], options="single")

    if not query:
        raise WeasylError("forgotpasswordRecordMissing")

    USERID, USERNAME, EMAIL, LINKTIME, ADDRESS = query

    # Check `username` and `email` against known correct values and raise an
    # exception if there is a mismatch
    if emailer.normalize_address(form.email) != emailer.normalize_address(EMAIL):
        raise WeasylError("emailIncorrect")
    elif d.get_sysname(form.username) != USERNAME:
        raise WeasylError("usernameIncorrect")
    elif d.get_address() != ADDRESS:
        raise WeasylError("addressInvalid")

    # Update the authbcrypt table with a new password hash
    """ TODO TEMPORARY """
    try:
        d.execute("INSERT INTO authbcrypt VALUES (%i, '')", [USERID])
    except:
        pass

    d.execute("UPDATE authbcrypt SET hashsum = '%s' WHERE userid = %i", [login.passhash(form.password), USERID])

    d.execute("DELETE FROM forgotpassword WHERE token = '%s'", [form.token])

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()

    try:
        d.engine.execute(
            "INSERT INTO forgotpassword (userid, token, set_time, address)"
            " VALUES (%(id)s, %(token)s, %(time)s, %(address)s)",
            id=user.userid, token=token, time=now, address=address)
    except IntegrityError:
        # An IntegrityError will probably indicate that a password reset request
        # already exists and that the existing row should be updated. If the update
        # doesn't find anything, though, the original error should be re-raised.
        result = d.engine.execute("""
            UPDATE forgotpassword SET
                token = %(token)s,
                set_time = %(time)s,
                address = %(address)s
            WHERE userid = %(id)s
        """, id=user.userid, token=token, time=now, address=address)

        if result.rowcount != 1:
            raise

    # 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 run_periodic_tasks():
    web.ctx.clear()

    now = arrow.utcnow()
    time_now = get_time()

    db = connect().connection()
    with db.begin():
        locked = db.scalar("SELECT pg_try_advisory_xact_lock(0)")
        if not locked:
            return
        last_run = arrow.get(db.scalar("SELECT last_run FROM cron_runs"))
        if not last_run or now < last_run.replace(seconds=59):
            return

        # Recache the latest submissions
        # Every 2 minutes
        if now.minute % 2 == 0:
            index.recent_submissions.refresh()
            log.msg('refreshed recent submissions')

        # Recache the active user counts
        # Every 5 minutes
        if now.minute % 5 == 0:
            active_users.refresh()
            log.msg('refreshed active user counts')

        # Recalculate recently popular submissions
        # Every 10 minutes
        if now.minute % 10 == 0:
            submission.select_recently_popular.refresh()
            log.msg('refreshed recently popular submissions')

        # Delete all records from contentview table
        # Every 15 minutes
        if now.minute % 15 == 0:
            db.execute("DELETE FROM views")
            log.msg('cleared views')

        # Delete password resets older than one day
        # Daily at 0:00
        if now.hour == 0 and now.minute == 0:
            db.execute("DELETE FROM forgotpassword WHERE set_time < %(expiry)s", expiry=time_now - 86400)
            log.msg('cleared old forgotten password requests')

        db.execute("UPDATE cron_runs SET last_run = %(now)s", now=now.naive)

def edit(userid, form):
    commentid = d.get_int(form.commentid)

    if form.feature not in ["submit", "char", "journal"]:
        raise WeasylError("Unexpected")

    query = d.execute("SELECT userid, unixtime FROM %scomment WHERE commentid = %i AND settings !~ 'h'",
                      [form.feature, commentid], options="single")

    if not query:
        raise WeasylError("RecordMissing")
    elif userid != query[0] and userid not in staff.MODS:
        raise WeasylError("InsufficientPermissions")
    elif query[1] < d.get_time() - 600:
        raise WeasylError("TimeLimitExceeded")

    d.execute("UPDATE %scomment SET content = '%s' WHERE commentid = %i",
              [form.feature, form.content.strip(), commentid])