def create_generic(userid, submission, **kwargs):
            tags = kwargs['tags']

            if submission.subtype not in valid_types:
                submission.subtype = expected_type * 1000 + 999

            if not submission.title:
                raise WeasylError("titleInvalid")
            elif not submission.rating:
                raise WeasylError("ratingInvalid")
            elif len(tags) < 2:
                raise WeasylError("notEnoughTags")
            elif not folder.check(userid, submission.folderid):
                raise WeasylError("Unexpected")

            profile.check_user_rating_allowed(userid, submission.rating)

            newid = create_specific(
                userid=userid,
                submission=submission,
                **kwargs)
            if newid:
                p = d.meta.tables['profile']
                d.connect().execute(p.update().where(p.c.userid == userid).values(latest_submission_time=arrow.utcnow()))
            return newid

def main():
    db = d.connect()
    q = (
        sa.select([sa.func.array_agg(orm.MediaItem.mediaid)])
        .where(orm.MediaItem.sha256 != None)
        .group_by(orm.MediaItem.sha256)
        .having(sa.func.count() > 1))
    [[count]] = db.execute(
        sa.select([sa.func.count()])
        .select_from(q.alias('query'))
    ).fetchall()
    for e, (mediaids,) in enumerate(db.execute(q), start=1):
        sys.stdout.write('\r%d/%d' % (e, count))
        sys.stdout.flush()
        old_media_item = db.query(orm.DiskMediaItem).get(mediaids[0])
        new_media_item = orm.DiskMediaItem(
            sha256=old_media_item.sha256, file_type=old_media_item.file_type)
        with open(old_media_item.full_file_path, 'rb') as infile:
            data = infile.read()
        new_media_item.init_from_data(data)
        db.add(new_media_item)
        db.flush()
        for table, column in [('submission_media_links', 'mediaid'),
                              ('user_media_links', 'mediaid'),
                              ('media_media_links', 'described_with_id'),
                              ('media_media_links', 'describee_id')]:
            table_obj = d.meta.tables[table]
            q = (
                table_obj.update()
                .values(**{column: new_media_item.mediaid})
                .where(table_obj.c[column].in_(mediaids)))
            db.execute(q)
    print

def main():
    db = define.connect()
    all_media = sa.union(
        sa.select([orm.UserMediaLink.mediaid]),
        sa.select([orm.SubmissionMediaLink.mediaid]),
        sa.select([orm.MediaMediaLink.describee_id]),
        sa.select([orm.MediaMediaLink.described_with_id]),
    ).alias('all_media')
    q = (
        db.query(orm.MediaItem)
        .with_polymorphic([orm.DiskMediaItem])
        .outerjoin(all_media)
        .filter(all_media.c.mediaid == None))
    count = q.count()
    for e, media_item in enumerate(q, start=1):
        sys.stdout.write('\r%d/%d' % (e, count))
        sys.stdout.flush()
        db.delete(media_item)
        try:
            os.unlink(media_item.full_file_path)
        except OSError as e:
            if e.errno == errno.ENOENT:
                continue
            raise
    db.flush()
    print

def create_user(full_name="", birthday=arrow.get(586162800), config=None,
                username=None, password=None, email_addr=None, user_id=None):
    """ Creates a new user and profile, and returns the user ID. """
    if username is None:
        username = "User-" + str(next(_user_index))

    while True:
        user = add_entity(users.Login(login_name=legacy.login_name(username),
                                      last_login=arrow.get(0)))

        if user.userid not in staff.MODS and user.userid not in staff.DEVELOPERS:
            break

        db = d.connect()
        db.delete(user)
        db.flush()

    add_entity(users.Profile(userid=user.userid, username=username,
                             full_name=full_name, unixtime=arrow.get(0), config=config))
    add_entity(users.UserInfo(userid=user.userid, birthday=birthday))
    # Set a password for this user
    if password is not None:
        d.engine.execute("INSERT INTO authbcrypt VALUES (%(id)s, %(bcrypthash)s)",
                         id=user.userid, bcrypthash=login.passhash(password))
    # Set an email address for this user
    if email_addr is not None:
        d.engine.execute("UPDATE login SET email = %(email)s WHERE userid = %(id)s",
                         email=email_addr, id=user.userid)
    # Force the userID to a user-defined value and return it
    if user_id is not None:
        d.engine.execute("UPDATE login SET userid = %(newid)s WHERE userid = %(oldid)s", newid=user_id, oldid=user.userid)
        return user_id
    return user.userid

def finduser(userid, form):
    form.userid = d.get_int(form.userid)
    lo = d.meta.tables['login']
    sh = d.meta.tables['comments']
    q = d.sa.select([
        lo.c.userid,
        lo.c.login_name,
        lo.c.email,
        (d.sa.select([d.sa.func.count()])
         .select_from(sh)
         .where(sh.c.target_user == lo.c.userid)
         .where(sh.c.settings.op('~')('s'))).label('staff_notes'),
    ])

    if form.userid:
        q = q.where(lo.c.userid == form.userid)
    elif form.username:
        q = q.where(lo.c.login_name.op('~')(form.username))
    elif form.email:
        q = q.where(d.sa.or_(
            lo.c.email.op('~')(form.email),
            lo.c.email.op('ilike')('%%%s%%' % form.email),
        ))
    else:
        return []

    q = q.limit(100).order_by(lo.c.login_name.asc())
    db = d.connect()
    return db.execute(q)

def main():
    do_users, min_submission = sys.argv[1:]
    db = connect()
    if do_users == 'yes':
        print 'starting users'
        port_users(db)
    print 'starting submissions'
    port_submissions(db, int(min_submission))

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 check_for_duplicate_media(userid, mediaid):
    db = d.connect()
    q = (
        db.query(orm.Submission)
        .filter_by(userid=userid, is_hidden=False)
        .join(orm.SubmissionMediaLink)
        .filter_by(mediaid=mediaid, link_type='submission'))
    if q.first():
        raise WeasylError('duplicateSubmission')

def tag_history(submitid):
    db = d.connect()
    tu = d.meta.tables['tag_updates']
    pr = d.meta.tables['profile']
    return db.execute(
        sa.select([pr.c.username, tu.c.updated_at, tu.c.added, tu.c.removed])
        .select_from(tu.join(pr, tu.c.userid == pr.c.userid))
        .where(tu.c.submitid == submitid)
        .order_by(tu.c.updated_at.desc()))

def edit_preferences(userid, timezone=None,
                     preferences=None, jsonb_settings=None):
    """
    Apply changes to stored preferences for a given user.
    :param userid: The userid to apply changes to
    :param timezone: (optional) new Timezone to set for user
    :param preferences: (optional) old-style char preferences, overwrites all previous settings
    :param jsonb_settings: (optional) JSON preferences, overwrites all previous settings
    :return: None
    """
    config = d.get_config(userid)

    tooyoung = False
    if preferences is not None:
        tooyoung |= get_user_age(userid) < preferences.rating.minimum_age
    if jsonb_settings is not None:
        sfwrating = jsonb_settings.max_sfw_rating
        sfwrating = ratings.CODE_MAP.get(sfwrating, ratings.GENERAL)
        tooyoung |= get_user_age(userid) < sfwrating.minimum_age

    if tooyoung:
        raise WeasylError("birthdayInsufficient")
    if timezone is not None and timezone not in pytz.all_timezones:
        raise WeasylError('invalidTimezone')

    db = d.connect()
    updates = {}
    if preferences is not None:
        # update legacy preferences
        # clear out the option codes that are being replaced.
        for i in Config.all_option_codes:
            config = config.replace(i, "")
        config_str = config + preferences.to_code()
        updates['config'] = config_str
        d._get_config.invalidate(userid)
    if jsonb_settings is not None:
        # update jsonb preferences
        updates['jsonb_settings'] = jsonb_settings.get_raw()
        d._get_profile_settings.invalidate(userid)

    d.engine.execute(
        tables.profile.update().where(tables.profile.c.userid == userid),
        updates
    )

    # update TZ
    if timezone is not None:
        tz = db.query(orm.UserTimezone).get(userid)
        if tz is None:
            tz = orm.UserTimezone(userid=userid)
            db.add(tz)
        tz.timezone = timezone
        db.flush()
        tz.cache()
    else:
        db.flush()

def create_submission_tag(tagid, targetid, settings=None):
    db = d.connect()
    db.add(
        content.SubmissionTag(tagid=tagid, targetid=targetid, settings=settings))
    db.flush()

    db.execute(
        'INSERT INTO submission_tags (submitid, tags) VALUES (:submission, ARRAY[:tag]) '
        'ON CONFLICT (submitid) DO UPDATE SET tags = submission_tags.tags || :tag',
        {'submission': targetid, 'tag': tagid})

def editprofiletext(userid, otherid, content):
    pr = d.meta.tables['profile']
    db = d.connect()
    condition = pr.c.userid == otherid
    previous_profile = db.scalar(sa.select([pr.c.profile_text]).where(condition))
    db.execute(pr.update().where(condition).values(profile_text=content))
    note_about(
        userid, otherid,
        'Profile text replaced with:',
        '%s\n\n## Profile text was:\n\n%s' % (content, previous_profile))

def editcatchphrase(userid, otherid, content):
    pr = d.meta.tables['profile']
    db = d.connect()
    condition = pr.c.userid == otherid
    previous_catchphrase = db.scalar(sa.select([pr.c.catchphrase]).where(condition))
    db.execute(pr.update().where(condition).values(catchphrase=content))
    note_about(
        userid, otherid,
        'Catchphrase replaced with:',
        '%s\n\n## Catchphrase was:\n\n%s' % (content, previous_catchphrase))

def test_verify_login_record_is_updated():
    # Use a fake session for this test.
    user_id = db_utils.create_user()
    sess = get_current_request().weasyl_session = create_session(user_id)
    db = d.connect()
    db.add(sess)
    db.flush()
    d.engine.execute("UPDATE login SET last_login = -1 WHERE userid = %(id)s", id=user_id)
    login.signin(get_current_request(), user_id)
    last_login = d.engine.scalar("SELECT last_login FROM login WHERE userid = %(id)s", id=user_id)
    assert last_login > -1

def create_session(user):
    """
    Creates a session for a user and returns the corresponding WZL cookie.
    """
    session = sessions.create_session(user)

    db = d.connect()
    db.add(session)
    db.flush()

    return 'WZL=' + session.sessionid.encode('utf-8')

def count(ownerid, staffnotes=False):
    db = d.connect()
    sh = d.meta.tables['comments']
    op = '~' if staffnotes else '!~'
    q = (
        d.sa.select([d.sa.func.count()])
        .select_from(sh)
        .where(sh.c.settings.op(op)('s'))
        .where(sh.c.target_user == ownerid))
    (ret,), = db.execute(q)
    return ret

def note_about(userid, target_user, title, message=None):
    staff_note = '## ' + title
    if message:
        staff_note = '%s\n\n%s' % (staff_note, message)

    db = d.connect()
    db.execute(
        d.meta.tables['comments'].insert()
        .values(
            userid=userid, target_user=target_user, unixtime=arrow.utcnow(),
            settings='s', content=staff_note,
        ))

def select_view_api(userid, submitid, anyway=False, increment_views=False):
    rating = d.get_rating(userid)
    db = d.connect()
    sub = db.query(orm.Submission).get(submitid)
    if sub is None or 'hidden' in sub.settings:
        raise WeasylError("submissionRecordMissing")
    sub_rating = sub.rating.code
    if 'friends-only' in sub.settings and not frienduser.check(userid, sub.userid):
        raise WeasylError("submissionRecordMissing")
    elif sub_rating > rating and userid != sub.userid:
        raise WeasylError("RatingExceeded")
    elif not anyway and ignoreuser.check(userid, sub.userid):
        raise WeasylError("UserIgnored")
    elif not anyway and blocktag.check(userid, submitid=submitid):
        raise WeasylError("TagBlocked")

    description = sub.content
    embedlink = None
    if 'embedded-content' in sub.settings:
        embedlink, _, description = description.partition('\n')
    elif 'gdocs-embed' in sub.settings:
        embedlink = sub.google_doc_embed.embed_url

    views = sub.page_views
    if increment_views and d.common_view_content(userid, submitid, 'submit'):
        views += 1

    return {
        'submitid': submitid,
        'title': sub.title,
        'owner': sub.owner.profile.username,
        'owner_login': sub.owner.login_name,
        'owner_media': api.tidy_all_media(media.get_user_media(sub.userid)),
        'media': api.tidy_all_media(media.get_submission_media(submitid)),
        'description': text.markdown(description),
        'embedlink': embedlink,
        'folderid': sub.folderid,
        'folder_name': sub.folder.title if sub.folderid else None,
        'posted_at': d.iso8601(sub.unixtime),
        'tags': searchtag.select(submitid=submitid),
        'link': d.absolutify_url("/submission/%d/%s" % (submitid, text.slug_for(sub.title))),

        'type': 'submission',
        'subtype': m.CATEGORY_PARSABLE_MAP[sub.subtype // 1000 * 1000],
        'rating': sub.rating.name,

        'views': views,
        'favorites': favorite.count(submitid),
        'comments': comment.count(submitid),
        'favorited': favorite.check(userid, submitid=submitid),
        'friends_only': 'friends-only' in sub.settings,
    }

def create_session(user):
    """
    Creates a session for a user and returns the corresponding WZL cookie.
    """
    session = orm.Session()
    session.sessionid = security.generate_key(64)
    session.userid = user

    db = d.connect()
    db.add(session)
    db.flush()

    return 'WZL=' + session.sessionid.encode('utf-8')

def db(request):
    db = define.connect()

    def tear_down():
        """ Clears all rows from the test database. """
        for k, cls in registry.iteritems():
            if not k[0].isupper():
                continue
            db.query(cls).delete()
        db.flush()

    request.addfinalizer(tear_down)
    return db