def edit(userid, journal, friends_only=False):
    if not journal.title:
        raise WeasylError("titleInvalid")
    elif not journal.content:
        raise WeasylError("contentInvalid")
    elif not journal.rating:
        raise WeasylError("ratingInvalid")
    profile.check_user_rating_allowed(userid, journal.rating)

    query = d.execute("SELECT userid, settings FROM journal WHERE journalid = %i", [journal.journalid], options="single")

    if not query or "h" in query[1]:
        raise WeasylError("Unexpected")
    elif userid != query[0] and userid not in staff.MODS:
        raise WeasylError("InsufficientPermissions")

    settings = [query[1].replace("f", "")]
    settings.append("f" if friends_only else "")
    settings = "".join(settings)

    if "f" in settings:
        welcome.journal_remove(journal.journalid)

    d.execute("UPDATE journal SET (title, content, rating, settings) = ('%s', '%s', %i, '%s') WHERE journalid = %i",
              [journal.title, journal.content, journal.rating.code, settings, journal.journalid])

    if userid != query[0]:
        moderation.note_about(
            userid, query[0], 'The following journal was edited:',
            '- ' + text.markdown_link(journal.title, '/journal/%s?anyway=true' % (journal.journalid,)))

def _create_char(userid, x1, y1, x2, y2, charid, config=None, remove=True):
    x1, y1, x2, y2 = d.get_int(x1), d.get_int(y1), d.get_int(x2), d.get_int(y2)
    filename = d.url_make(charid, "char/.thumb", root=True)
    if not m.os.path.exists(filename):
        filename = d.url_make(charid, "char/cover", root=True)
        if not filename:
            return
        remove = False

    im = image.read(filename)
    size = im.size.width, im.size.height

    d.execute("""
        UPDATE character
        SET settings = REGEXP_REPLACE(settings, '-.', '') || '-%s'
        WHERE charid = %i
    """, [image.image_setting(im), charid])
    dest = os.path.join(d.get_character_directory(charid), '%i.thumb%s' % (charid, images.image_extension(im)))

    bounds = None
    if image.check_crop(size, x1, y1, x2, y2):
        bounds = geometry.Rectangle(x1, y1, x2, y2)
    thumb = images.make_thumbnail(im, bounds)
    thumb.write(dest, format=images.image_file_type(thumb))
    if remove:
        os.remove(filename)

def select_view(userid, noteid):
    query = d.execute(
        "SELECT ps.userid, ps.username, pr.userid, pr.username, "
        "ms.title, ms.content, ms.unixtime, ms.settings FROM message ms INNER "
        "JOIN profile ps ON ms.userid = ps.userid INNER JOIN profile pr ON "
        "ms.otherid = pr.userid WHERE ms.noteid = %i", [noteid],
        options=["single"])

    if not query:
        raise WeasylError("noteRecordMissing")
    elif userid == query[0] and "s" in query[7]:
        raise WeasylError("noteRecordMissing")
    elif userid == query[2] and "r" in query[7]:
        raise WeasylError("noteRecordMissing")
    elif userid not in [query[0], query[2]]:
        raise WeasylError("InsufficientPermissions")

    if query[2] == userid and "u" in query[7]:
        d.execute("UPDATE message SET settings = REPLACE(settings, 'u', '') WHERE noteid = %i", [noteid])
        d._page_header_info.invalidate(userid)

    return {
        "noteid": noteid,
        "senderid": query[0],
        "mine": userid == query[0],
        "sendername": query[1],
        "recipientid": query[2],
        "recipientname": query[3],
        "title": query[4],
        "content": query[5],
        "unixtime": query[6],
    }

def suggest(userid, target):
    if not target:
        return []

    if userid:
        block = d.execute("SELECT tagid FROM blocktag WHERE userid = %i", [userid], options="within")

    query = list()
    target = d.get_search_tag(target)
    statement = ["SELECT title FROM searchtag WHERE title LIKE '%s%%'"]

    if userid and block:
        statement.append(" AND tagid NOT IN %s" % (d.sql_number_list(block),))

    for i in d.execute("".join(statement + [" ORDER BY title LIMIT 10"]), [target], options="within"):
        query.append(i)

    statement = ["SELECT title FROM searchtag WHERE title LIKE '%%%s%%' AND title NOT LIKE '%s%%'"]

    if userid and block:
        statement.append(" AND tagid NOT IN %s" % (d.sql_number_list(block),))

    for i in d.execute("".join(statement + [" ORDER BY title LIMIT 5"]), [target, target], options="within"):
        query.append(i)

    return query

def remove(userid, commentid=None):
    query = d.execute(
        "SELECT userid, target_user, settings FROM comments WHERE commentid = %i AND settings !~ 'h'",
        [commentid], ["single"])

    if not query or ('s' in query[2] and userid not in staff.MODS):
        raise WeasylError("shoutRecordMissing")

    if userid != query[1] and userid not in staff.MODS:
        if userid != query[0]:
            raise WeasylError("InsufficientPermissions")

        # user is commenter
        replies = d.execute(
            "SELECT commentid FROM comments WHERE parentid = %d", [commentid])
        if replies:
            # a commenter cannot remove their comment if it has replies
            raise WeasylError("InsufficientPermissions")

    # remove notifications
    welcome.comment_remove(commentid, 'shout')
    d._page_header_info.invalidate(userid)

    # hide comment
    d.execute("UPDATE comments SET settings = settings || 'h', hidden_by = %i WHERE commentid = %i", [userid, commentid])

    return query[1]

def settings(userid, setting=None):
    if setting:
        return d.execute("SELECT settings ~ '%s' FROM login WHERE userid = %i",
                         [setting, userid], options="bool")
    else:
        return d.execute("SELECT settings FROM login WHERE userid = %i",
                         [userid], options="element")

def check(userid, folderid=None, title=None, parentid=None, root=True):
    """
    Returns True if folderid or title refers to a non-hidden folder owned by
    the user, else False. Additionally, if parentid is non-None, it must refer
    to the parent folder.
    """
    if not folderid and not title:
        return root

    if folderid:
        if parentid is None:
            return d.execute(
                "SELECT EXISTS (SELECT 0 FROM folder WHERE (folderid, userid) = (%i, %i) AND settings !~ 'h')",
                [folderid, userid], options="bool")
        else:
            return d.execute(
                "SELECT EXISTS (SELECT 0 FROM folder WHERE (folderid, userid, parentid) = (%i, %i, %i) AND settings !~ 'h')",
                [folderid, userid, parentid], options="bool")
    elif title:
        if parentid is None:
            return d.execute(
                "SELECT EXISTS (SELECT 0 FROM folder WHERE (userid, title) = (%i, '%s') AND settings !~ 'h')",
                [userid, title], options="bool")
        else:
            return d.execute(
                "SELECT EXISTS (SELECT 0 FROM folder WHERE (userid, parentid, title) = (%i, %i, '%s') AND settings !~ 'h')",
                [userid, parentid, title], options="bool")

def create_price(userid, price, currency="", settings=""):
    if not price.title:
        raise WeasylError("titleInvalid")
    elif price.amount_min > _MAX_PRICE:
        raise WeasylError("minamountInvalid")
    elif price.amount_max > _MAX_PRICE:
        raise WeasylError("maxamountInvalid")
    elif price.amount_max and price.amount_max < price.amount_min:
        raise WeasylError("maxamountInvalid")
    elif not d.execute("SELECT EXISTS (SELECT 0 FROM commishclass WHERE (classid, userid) = (%i, %i))",
                       [price.classid, userid], ["bool"]):
        raise WeasylError("classidInvalid")
    elif not price.classid:
        raise WeasylError("classidInvalid")

    # Settings are at most one currency class, and optionally an 'a' to indicate an add-on price.
    # TODO: replace these character codes with an enum.
    settings = "%s%s" % ("".join(i for i in currency if i in CURRENCY_CHARMAP)[:1],
                         "a" if "a" in settings else "")

    # TODO: should have an auto-increment ID
    priceid = d.execute("SELECT MAX(priceid) + 1 FROM commishprice WHERE userid = %i", [userid], ["element"])

    try:
        d.execute(
            "INSERT INTO commishprice VALUES (%i, %i, %i, '%s', %i, %i, '%s')",
            [priceid if priceid else 1, price.classid, userid, price.title, price.amount_min, price.amount_max, settings])
    except PostgresError:
        return WeasylError("titleExists")

def select_latest(userid, rating, otherid=None, config=None):
    if config is None:
        config = d.get_config(userid)

    statement = ["SELECT jo.journalid, jo.title, jo.content, jo.unixtime FROM journal jo WHERE"]

    if userid:
        if d.is_sfw_mode():
            statement.append(" (jo.rating <= %i)" % (rating,))
        else:
            statement.append(" (jo.userid = %i OR jo.rating <= %i)" % (userid, rating))
        if not otherid:
            statement.append(m.MACRO_IGNOREUSER % (userid, "jo"))
        statement.append(m.MACRO_BLOCKTAG_JOURNAL % (userid, userid))
    else:
        statement.append(" jo.rating <= %i" % (rating,))

    if otherid:
        statement.append(
            " AND jo.userid = %i AND jo.settings !~ '[%sh]'" % (otherid, "" if frienduser.check(userid, otherid) else "f"))

    statement.append("ORDER BY jo.journalid DESC LIMIT 1")
    query = d.execute("".join(statement), options="single")

    if query:
        return {
            "journalid": query[0],
            "title": query[1],
            "content": query[2],
            "unixtime": query[3],
            "comments": d.execute("SELECT COUNT(*) FROM journalcomment WHERE targetid = %i AND settings !~ 'h'",
                                  [query[0]], ["element"]),
        }

def remove_request(userid, otherid):
    d.execute(
        "DELETE FROM frienduser "
        "WHERE userid IN (%i, %i) "
        "AND otherid IN (%i, %i)",
        [userid, otherid, userid, otherid])
    welcome.frienduserrequest_remove(userid, otherid)

def select_list(userid):
    query = d.execute("SELECT classid, title, amount_min, amount_max, settings, priceid FROM commishprice"
                      " WHERE userid = %i ORDER BY classid, title", [userid])

    content = d.execute("SELECT content FROM commishdesc WHERE userid = %i", [userid], ["element"])

    return {
        "class": [{
            "classid": i[0],
            "title": i[1],
        } for i in d.execute("SELECT classid, title FROM commishclass WHERE userid = %i ORDER BY title", [userid])],
        "price": [{
            "classid": i[0],
            "title": i[1],
            "amount_min": i[2],
            "amount_max": i[3],
            "settings": i[4],
            "priceid": i[5],
        } for i in query if "a" not in i[4]] + [{
            "classid": i[0],
            "title": i[1],
            "amount_min": i[2],
            "amount_max": i[3],
            "settings": i[4],
            "priceid": i[5],
        } for i in query if "a" in i[4]],
        "content": content if content else "",
    }

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 edit_email_password(userid, username, password, newemail, newemailcheck,
                        newpassword, newpasscheck):
    from weasyl import login

    # Check that credentials are correct
    logid, logerror = login.authenticate_bcrypt(username, password, session=False)

    if userid != logid or logerror is not None:
        raise WeasylError("loginInvalid")

    if newemail:
        if newemail != newemailcheck:
            raise WeasylError("emailMismatch")
        elif login.email_exists(newemail):
            raise WeasylError("emailExists")

    if newpassword:
        if newpassword != newpasscheck:
            raise WeasylError("passwordMismatch")
        elif not login.password_secure(newpassword):
            raise WeasylError("passwordInsecure")

    if newemail:
        d.execute("UPDATE login SET email = '%s' WHERE userid = %i", [newemail, userid])

    if newpassword:
        d.execute("UPDATE authbcrypt SET hashsum = '%s' WHERE userid = %i", [login.passhash(newpassword), userid])

def accept(userid, otherid):
    if check(userid, otherid):
        raise WeasylError("Unexpected")

    d.execute("UPDATE frienduser SET settings = REPLACE(settings, 'p', '')"
              " WHERE (userid, otherid) = (%i, %i)", [otherid, userid])
    welcome.frienduseraccept_insert(userid, otherid)
    welcome.frienduserrequest_remove(userid, otherid)

def remove(userid, tagid=None, title=None):
    if tagid:
        d.execute("DELETE FROM blocktag WHERE (userid, tagid) = (%i, %i)", [userid, tagid])
    elif title:
        d.execute("DELETE FROM blocktag WHERE (userid, tagid) = (%i, (SELECT tagid FROM searchtag WHERE title = '%s'))",
                  [userid, d.get_search_tag(title)])

    select_ids.invalidate(userid)

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 set(userid, username):
    if login.username_exists(username):
        raise WeasylError("usernameExists")
    elif not d.get_premium(userid):
        raise WeasylError("InsufficientPermissions")

    d.execute("DELETE FROM useralias WHERE userid = %i AND settings ~ 'p'", [userid])
    d.execute("INSERT INTO useralias VALUES (%i, '%s', 'p')", [userid, username])

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 insert(userid, shout, staffnotes=False):
    # Check invalid content
    if not shout.content:
        raise WeasylError("commentInvalid")
    elif not shout.userid:
        raise WeasylError("Unexpected")

    # Determine indent and parentuserid
    if shout.parentid:
        query = d.execute("SELECT userid, indent FROM comments WHERE commentid = %i",
                          [shout.parentid], options="single")

        if not query:
            raise WeasylError("shoutRecordMissing")

        indent, parentuserid = query[1] + 1, query[0]
    else:
        indent, parentuserid = 0, None

    # Check permissions
    if userid not in staff.MODS:
        if ignoreuser.check(shout.userid, userid):
            raise WeasylError("pageOwnerIgnoredYou")
        elif ignoreuser.check(userid, shout.userid):
            raise WeasylError("youIgnoredPageOwner")
        elif ignoreuser.check(parentuserid, userid):
            raise WeasylError("replyRecipientIgnoredYou")
        elif ignoreuser.check(userid, parentuserid):
            raise WeasylError("youIgnoredReplyRecipient")

        settings = d.execute("SELECT lo.settings, pr.config FROM login lo"
                             " INNER JOIN profile pr ON lo.userid = pr.userid"
                             " WHERE lo.userid = %i", [shout.userid], options="single")

        if "b" in settings[0] or "w" in settings[1] or "x" in settings[1] and not frienduser.check(userid, shout.userid):
            raise WeasylError("insufficientActionPermissions")

    # Create comment
    settings = 's' if staffnotes else ''
    co = d.meta.tables['comments']
    db = d.connect()
    commentid = db.scalar(
        co.insert()
        .values(userid=userid, target_user=shout.userid, parentid=shout.parentid or None, content=shout.content,
                unixtime=arrow.utcnow(), indent=indent, settings=settings)
        .returning(co.c.commentid))

    # Create notification
    if shout.parentid and userid != parentuserid:
        if not staffnotes or parentuserid in staff.MODS:
            welcome.shoutreply_insert(userid, commentid, parentuserid, shout.parentid, staffnotes)
    elif not staffnotes and shout.userid and userid != shout.userid:
        welcome.shout_insert(userid, commentid, otherid=shout.userid)

    d.metric('increment', 'shouts')

    return commentid

def create_commission_class(userid, title):
    if not title:
        raise error.WeasylError("titleInvalid")

    classid = d.execute("SELECT MAX(classid) + 1 FROM commishclass WHERE userid = %i", [userid], ["element"])

    try:
        d.execute("INSERT INTO commishclass VALUES (%i, %i, '%s')", [classid if classid else 1, userid, title])
    except error.PostgresError:
        raise error.WeasylError("commishclassExists")