Editor.pm

package MusicBrainz::Server::Data::Editor;
use feature 'state';
use Moose;
use namespace::autoclean;
use LWP;
use URI::Escape;

use Authen::Passphrase;
use Authen::Passphrase::BlowfishCrypt;
use Authen::Passphrase::RejectAll;
use DateTime;
use DateTime::Format::ISO8601;
use DateTime::Format::Pg;
use Encode;
use Text::Trim qw( trim );
use MusicBrainz::Server::Constants qw(
    :create_entity
    :edit_status
    :privileges
    :vote
    %ENTITIES
    $DIGEST_AUTH_TOKEN_FLAG
    $EDIT_EVENT_ADD_EVENT_ART
    $EDIT_HISTORIC_ADD_RELEASE
    $EDIT_RELEASE_ADD_COVER_ART
    $PASSPHRASE_BCRYPT_COST
    entities_with
);
use MusicBrainz::Server::Entity::Preferences;
use MusicBrainz::Server::Entity::Editor;
use MusicBrainz::Server::Entity::Util::JSON qw( to_json_array );
use MusicBrainz::Server::Data::Utils qw(
    generate_token
    ha1_password
    hash_to_row
    load_subobjects
    non_empty
    placeholders
    sanitize_username
);

extends 'MusicBrainz::Server::Data::Entity';

with 'MusicBrainz::Server::Data::Role::Area',
     'MusicBrainz::Server::Data::Role::Subscription' => {
        table => 'editor_subscribe_editor',
        column => 'subscribed_editor',
        active_class => 'MusicBrainz::Server::Entity::EditorSubscription',
     };

sub _table
{
    return 'editor';
}

sub _build_columns
{
    return join q(, ), (
        'editor.id',
        'editor.name COLLATE musicbrainz',
        'password',
        'privs',
        'email',
        'website',
        'bio',
        'member_since',
        'email_confirm_date',
        'last_login_date',
        'gender',
        'area',
        'birth_date',
        'ha1',
        'deleted',
    );
}

has '_columns' => (
    is => 'ro',
    isa => 'Str',
    lazy => 1,
    builder => '_build_columns',
);

sub _area_columns { [qw( area )] }

sub _column_mapping
{
    return {
        id                      => 'id',
        name                    => 'name',
        email                   => 'email',
        password                => 'password',
        privileges              => 'privs',
        website                 => 'website',
        biography               => 'bio',
        email_confirmation_date => 'email_confirm_date',
        registration_date       => 'member_since',
        last_login_date         => 'last_login_date',
        gender_id               => 'gender',
        area_id                 => 'area',
        birth_date              => 'birth_date',
                                   # The `ha1` column is `CHAR(32)`, so an empty value will consist of 32 spaces.
                                   # Trim blank values so that they're stored as '' on the instance.
        ha1                     => sub { my ($row, $prefix) = @_; return trim($row->{ $prefix . 'ha1' }); },
        deleted                 => 'deleted',
    };
}

sub _entity_class
{
    return 'MusicBrainz::Server::Entity::Editor';
}

sub get_by_name
{
    my ($self, $name) = @_;
    my $query = 'SELECT ' . $self->_columns .
                ' FROM ' . $self->_table .
                ' WHERE lower(name) = lower(?) LIMIT 1';
    my $row = $self->sql->select_single_row_hash($query, $name);
    my $editor = $self->_new_from_row($row);
    $self->load_preferences($editor);
    return $editor;
}

sub summarize_ratings
{
    my ($self, $user, $me) = @_;

    return {
        map {
            my $entity_properties = $ENTITIES{$_};
            my $model = $self->c->model($entity_properties->{model});
            my ($entities) = $model->rating
                ->find_editor_ratings($user->id, $me, 10, 0);

            $self->c->model('ArtistCredit')->load(@$entities)
                if $entity_properties->{artist_credits};

            $model->load_aliases(@$entities)
                if $entity_properties->{aliases};

            ($_ => to_json_array($entities));
        } entities_with('ratings'),
    };
}

sub _get_tags_for_type
{
    my ($self, $id, $type, $show_downvoted) = @_;

    my $is_upvote = $show_downvoted ? 0 : 1;

    my $query = "SELECT tag, count(tag)
        FROM ${type}_tag_raw
        WHERE editor = ? AND is_upvote = ?
        GROUP BY tag";

    my $results = $self->c->sql->select_list_of_hashes($query, $id, $is_upvote);

    return { map { $_->{tag} => $_ } @$results };
}

sub get_tags
{
    my ($self, $user, $show_downvoted, $order) = @_;


    my $tags = {};
    my $max = 0;
    foreach my $entity (entities_with('tags'))
    {
        my $data = $self->_get_tags_for_type($user->id, $entity, $show_downvoted);

        foreach (keys %$data)
        {
            if ($tags->{$_})
            {
                $tags->{$_}->{count} += $data->{$_}->{count};
            }
            else
            {
                $tags->{$_} = $data->{$_};
            }

            $max = $tags->{$_}->{count} if $tags->{$_}->{count} > $max;
        }
    }

    my $entities = $self->c->model('Tag')->get_by_ids(keys %$tags);
    foreach (keys %$entities)
    {
        $tags->{$_}->{tag} = $entities->{$_};
    }

    my @tags;
    $order //= '';
    if ($order eq 'count') {
        @tags = reverse sort { $a->{count} <=> $b->{count} } values %$tags;
    } elsif ($order eq 'countdesc') {
        @tags = sort { $a->{count} <=> $b->{count} } values %$tags;
    } else {
        @tags = sort { $a->{tag}->name cmp $b->{tag}->name } values %$tags;
    }

    return { max => $max, tags => \@tags };
}

around '_get_by_keys' => sub {
    my $orig = shift;
    my $self = shift;

    my @ret = $self->$orig(@_);
    $self->load_preferences(@ret);

    return @ret;
};

sub find_by_email
{
    my ($self, $email) = @_;

    my $query = 'SELECT ' . $self->_columns .
                ' FROM ' . $self->_table .
                ' WHERE lower(email) = lower(?)';

    $self->query_to_list($query, [$email]);
}

sub find_by_ip {
    my ($self, $ip, $limit, $offset) = @_;

    my $query = 'SELECT ' . $self->_columns .
        ' FROM ' . $self->_table . ' WHERE id = any(?)' .
        ' ORDER BY member_since';

    my @ids = $self->store->set_members("ipusers:$ip");
    $self->query_to_list_limited($query, [\@ids], $limit, $offset);
}

sub find_possible_spammers {
    my ($self, %args) = @_;

    my ($op, $id, $limit) = @args{qw( op id limit )};

    my %ops = (
        'gt' => '>',
        'gte' => '>=',
        'lt' => '<',
        'lte' => '<=',
    );

    die 'invalid op' unless defined $op && exists $ops{$op};
    my $sql_op = $ops{$op};
    # The order of results is DESC (newest accounts first), but $op
    # determines which direction we're paginating in.
    my $reversed = $op =~ /^g/;

    my $query = 'SELECT ' . $self->_columns .
        ' FROM ' . $self->_table .
        " WHERE id $sql_op \$1" .
        ' AND (privs & $2) = $2' .
        ' AND (privs & $3) = 0' .
        ' AND COALESCE(website, bio, \'\') != \'\'' .
        ' ORDER BY id ' . ($reversed ? 'ASC' : 'DESC') .
        ' LIMIT $4';

    my @editors = $self->query_to_list(
        $query,
        [$id, $BEGINNER_FLAG, $SPAMMER_FLAG, $limit],
    );

    if ($reversed) {
        # We had to query in ASC order, so resort in DESC order.
        # We specifically disable the critic rule suggesting `reverse sort`
        # instead of "$b before $a" to allow an in-place sort.
        ## no critic 'ProhibitReverseSortBlock'
        @editors = sort { $b->id <=> $a->id } @editors;
    }

    my $unused_editors_results = $self->sql->select_list_of_hashes(
        $self->c->model('Editor')->_build_unused_editor_query() . "\n" .
            "AND e.deleted IS false\n" .
            "AND e.privs = ?\n" .
            'AND e.id = any(?)',
        $BEGINNER_FLAG,
        [map { $_->id } @editors],
    );
    my %unused_editors = map { $_->{id} => 1 } @$unused_editors_results;

    for my $editor (@editors) {
        $editor->unused(exists $unused_editors{ $editor->id });
    }
    return @editors;
}

sub search_by_email {
    my ($self, $email_regexp, $limit, $offset) = @_;

    my $query = 'SELECT ' . $self->_columns .
        ' FROM ' . $self->_table .
        q{ WHERE (regexp_replace(regexp_replace(email, '[@+].*', ''), '\.', '', 'g') || regexp_replace(email, '.*@', '@')) ~* ?} .
        ' ORDER BY member_since DESC';

    $self->query_to_list_limited($query, [$email_regexp], $limit, $offset);
}

sub find_by_privileges
{
    my ($self, $privs, $exact_only, $limit, $offset) = @_;

    my $condition;
    my $args;
    if ($exact_only) {
        $condition = 'privs = ?';
        $args = [$privs];
    } else {
        $condition = '(privs & ?) = ?';
        $args = [($privs) x 2];
    }

    my $query = 'SELECT ' . $self->_columns . '
                 FROM ' . $self->_table . "
                 WHERE $condition
                 ORDER BY editor.name, editor.id";
    $self->query_to_list_limited($query, $args, $limit, $offset);
}

sub find_by_subscribed_editor
{
    my ($self, $editor_id, $limit, $offset) = @_;
    my $query = 'SELECT ' . $self->_columns . '
                 FROM ' . $self->_table . '
                    JOIN editor_subscribe_editor s ON editor.id = s.subscribed_editor
                 WHERE s.editor = ?
                 ORDER BY editor.name, editor.id';
    $self->query_to_list_limited($query, [$editor_id], $limit, $offset);
}

sub find_subscribers
{
    my ($self, $editor_id, $limit, $offset) = @_;
    my $query = 'SELECT ' . $self->_columns . '
                 FROM ' . $self->_table . '
                    JOIN editor_subscribe_editor s ON editor.id = s.editor
                 WHERE s.subscribed_editor = ?
                 ORDER BY editor.name, editor.id';
    $self->query_to_list_limited($query, [$editor_id], $limit, $offset);
}

sub _die_if_username_invalid {
    my $name = shift;
    my $sanitized_name = sanitize_username($name);

    die 'Invalid user name' if (
        $name ne $sanitized_name ||
        $sanitized_name =~ qr{^deleted editor \#\d+$}i ||
        $sanitized_name =~ qr{://}
    );
}

sub insert
{
    my ($self, $data) = @_;

    _die_if_username_invalid($data->{name});

    my $plaintext = $data->{password};
    $data->{password} = hash_password($plaintext);
    $data->{ha1} = '';

    return Sql::run_in_transaction(sub {
        return $self->_entity_class->new(
            id => $self->sql->insert_row('editor', $data, 'id'),
            name => $data->{name},
            password => $data->{password},
            privs => $data->{privs} // 0,
            ha1 => '',
            registration_date => DateTime->now,
        );
    }, $self->sql);
}

sub insert_from_metabrainz {
    my ($self, $id, $name, $member_since) = @_;

    _die_if_username_invalid($name);

    die "Editor $id is missing a member_since value"
        unless non_empty($member_since);

    my $member_since_dt = DateTime::Format::ISO8601->parse_datetime($member_since);
    $member_since_dt->set_time_zone('UTC');

    $self->sql->do(
        <<~'SQL',
        INSERT INTO editor (id, name, privs, member_since, password, ha1)
            VALUES (?, ?, ?, ?, '', '')
            ON CONFLICT (id) DO NOTHING
        SQL
        $id,
        $name,
        $BEGINNER_FLAG,
        DateTime::Format::Pg->format_datetime($member_since_dt),
    );

    return $self->get_by_id($id);
}

sub update_email
{
    my ($self, $editor, $email) = @_;

    Sql::run_in_transaction(sub {
        if ($email) {
            my $email_confirmation_date = $self->sql->select_single_value(
                'UPDATE editor SET email=?, email_confirm_date=NOW()
                WHERE id=? RETURNING email_confirm_date', $email, $editor->id);
            $editor->email($email);
            $editor->email_confirmation_date($email_confirmation_date);
        }
        else {
            $self->sql->do('UPDATE editor SET email=NULL, email_confirm_date=NULL
                      WHERE id=?', $editor->id);
            delete $editor->{email};
            delete $editor->{email_confirmation_date};
        }
    }, $self->sql);
}

sub update_password
{
    my ($self, $editor_name, $password) = @_;

    Sql::run_in_transaction(sub {
        $self->sql->do(<<~'SQL', hash_password($password), $editor_name);
            UPDATE editor
            SET password = ?,
                last_login_date = now()
            WHERE lower(name) = lower(?)
            SQL
    }, $self->sql);
}

sub disable_digest_auth_token {
    my ($self, $editor_id) = @_;

    $self->sql->do(q(UPDATE editor SET ha1 = '' WHERE id = ?), $editor_id);
}

sub reset_digest_auth_token {
    my ($self, $editor_id) = @_;

    my $username = $self->sql->select_single_value(<<~'SQL', $editor_id);
        SELECT name FROM editor WHERE id = ?
        SQL
    my $token = generate_token();
    my $ha1 = ha1_password($username, $token);
    $self->sql->do(<<~'SQL', $ha1, $DIGEST_AUTH_TOKEN_FLAG, $editor_id);
        UPDATE editor
           SET ha1 = ?,
               privs = privs | ?
         WHERE id = ?
        SQL
    return $token;
}

sub update_profile
{
    my ($self, $editor, $update) = @_;

    if (defined $update->{username}) {
        _die_if_username_invalid($update->{username});
    }

    my $row = hash_to_row($update, {
        bio => 'biography',
        gender => 'gender_id',
        name => 'username',
        map { $_ => $_ } qw( birth_date website ),
    });

    if (exists $update->{area}) {
        $row->{area} = $update->{area}{id};
    }

    if (my $date = delete $row->{birth_date}) {
        if (%$date) { # if date is given but all NULL, it will be an empty hash.
            $row->{birth_date} = sprintf '%d-%d-%d', map { $date->{$_} } qw( year month day );
        }
        else {
            $row->{birth_date} = undef;
        }
    }

    Sql::run_in_transaction(sub {
        $self->sql->update_row('editor', $row, { id => $editor->id });
    }, $self->sql);
}

sub update_privileges {
    my ($self, $editor, $values) = @_;

    my $should_cancel_edits_and_votes = $values->{spammer};

    # Setting Spammer should also block editing, voting and notes
    $values->{editing_disabled} ||= $values->{spammer};
    $values->{voting_disabled} ||= $values->{spammer};
    $values->{adding_notes_disabled} ||= $values->{spammer};

    my $privs =   ($values->{auto_editor}           // 0) * $AUTO_EDITOR_FLAG
                + ($values->{bot}                   // 0) * $BOT_FLAG
                + ($values->{untrusted}             // 0) * $UNTRUSTED_FLAG
                + ($values->{link_editor}           // 0) * $RELATIONSHIP_EDITOR_FLAG
                + ($values->{location_editor}       // 0) * $LOCATION_EDITOR_FLAG
                + ($values->{no_nag}                // 0) * $DONT_NAG_FLAG
                + ($values->{wiki_transcluder}      // 0) * $WIKI_TRANSCLUSION_FLAG
                + ($values->{banner_editor}         // 0) * $BANNER_EDITOR_FLAG
                + ($values->{mbid_submitter}        // 0) * $MBID_SUBMITTER_FLAG
                + ($values->{account_admin}         // 0) * $ACCOUNT_ADMIN_FLAG
                + ($values->{editing_disabled}      // 0) * $EDITING_DISABLED_FLAG
                + ($values->{adding_notes_disabled} // 0) * $ADDING_NOTES_DISABLED_FLAG
                + ($values->{voting_disabled}       // 0) * $VOTING_DISABLED_FLAG
                + ($values->{spammer}               // 0) * $SPAMMER_FLAG;

    Sql::run_in_transaction(sub {
        $self->sql->do(
            'UPDATE editor SET privs = ? | (privs & ?) WHERE id = ?',
            $privs,
            # Preserve the value of the beginner flag.
            $BEGINNER_FLAG,
            $editor->id,
        );

        if ($should_cancel_edits_and_votes) {
            $self->c->model('Editor')->cancel_edits_and_votes($editor);
        }
    }, $self->sql);
}

sub _build_unused_editor_query {
    my ($self) = @_;

    state $editor_collection_subquery = join(
        ' AND ',
        map {<<~"SQL"} entities_with('collections'),
            NOT EXISTS (
                SELECT TRUE
                FROM editor_collection ec
                JOIN editor_collection_$_ ece ON ece.collection = ec.id
                WHERE ec.editor = e.id
                LIMIT 1
            )
            SQL
    );

    state $editor_rating_subquery = join(
        ' AND ',
        map {<<~"SQL"} entities_with('ratings'),
            NOT EXISTS (
                SELECT TRUE
                FROM ${_}_rating_raw err
                WHERE err.editor = e.id
                LIMIT 1
            )
            SQL
    );

    state $editor_subscription_subquery = join(
        ' AND ',
        map {<<~"SQL"} entities_with('subscriptions'),
            NOT EXISTS (
                SELECT TRUE
                FROM editor_subscribe_$_ ese
                WHERE ese.editor = e.id
                LIMIT 1
            )
            SQL
    );

    state $editor_subscription_deleted_subquery = join(
        ' AND ',
        map {<<~"SQL"} entities_with(['subscriptions', 'deleted']),
            NOT EXISTS (
                SELECT TRUE
                FROM editor_subscribe_${_}_deleted esed
                WHERE esed.editor = e.id
                LIMIT 1
            )
            SQL
    );

    state $editor_tag_subquery = join(
        ' AND ',
        map {<<~"SQL"} entities_with('tags'),
            NOT EXISTS (
                SELECT TRUE
                FROM ${_}_tag_raw etr
                WHERE etr.editor = e.id
                LIMIT 1
            )
            SQL
    );

    return <<~"SQL";
            SELECT e.id, e.name
            FROM editor e
            WHERE
        NOT EXISTS (SELECT 1
                        FROM application
                    WHERE application.owner = e.id)
    AND NOT EXISTS (SELECT 1
                        FROM editor_oauth_token
                    WHERE editor_oauth_token.editor = e.id)
    AND NOT EXISTS (SELECT 1
                        FROM vote
                    WHERE vote.editor = e.id)
    AND NOT EXISTS (SELECT 1
                        FROM edit
                    WHERE edit.editor = e.id)
    AND NOT EXISTS (SELECT 1
                        FROM edit_note
                    WHERE edit_note.editor = e.id)
    AND NOT EXISTS (SELECT 1
                        FROM annotation
                    WHERE annotation.editor = e.id)
    AND $editor_subscription_subquery
    AND $editor_subscription_deleted_subquery
    AND $editor_tag_subquery
    AND $editor_rating_subquery
    AND $editor_collection_subquery
    AND NOT EXISTS (   SELECT 1
                        FROM editor_collection ec
                        JOIN editor_collection_collaborator ecc
                            ON ecc.collection = ec.id
                        WHERE ec.editor = e.id)
    AND NOT EXISTS (   SELECT 1
                        FROM editor_collection_collaborator ecc
                        WHERE ecc.editor = e.id)
    AND NOT EXISTS (SELECT 1
                      FROM autoeditor_election_vote aev
                     WHERE aev.voter = e.id)
    AND NOT EXISTS (SELECT 1
                      FROM autoeditor_election ae
                     WHERE ae.candidate = e.id
                        OR ae.proposer = e.id
                        OR ae.seconder_1 = e.id
                        OR ae.seconder_2 = e.id)
    SQL
}

sub make_autoeditor
{
    my ($self, $editor_id) = @_;

    $self->sql->do('UPDATE editor SET privs = privs | ? WHERE id = ?',
                   $AUTO_EDITOR_FLAG, $editor_id);
}

sub load
{
    my ($self, @objs) = @_;
    load_subobjects($self, 'editor', @objs);
    $self->load_preferences(map { $_->editor } grep { defined } @objs);
}

sub load_preferences
{
    my ($self, @editors) = @_;

    return unless @editors;

    my %editors = map { $_->id => $_ } grep { defined } @editors
        or return;

    my $query = sprintf 'SELECT editor, name, value '.
        'FROM editor_preference WHERE editor IN (%s)',
        placeholders(keys %editors);

    my $prefs = $self->sql->select_list_of_hashes($query, keys %editors);

    for my $pref (@$prefs) {
        my ($editor_id, $key, $value) = ($pref->{editor}, $pref->{name}, $pref->{value});
        next unless $editors{$editor_id}->preferences->can($key);
        $editors{$editor_id}->preferences->$key($value);
    }
}

sub save_preferences
{
    my ($self, $editor, $values) = @_;

    Sql::run_in_transaction(sub {

        $self->sql->do('DELETE FROM editor_preference WHERE editor = ?', $editor->id);
        my $new_preferences = MusicBrainz::Server::Entity::Preferences->new(%$values);
        my $preferences_meta = $editor->preferences->meta;
        foreach my $name (keys %$values) {
            my $default = $preferences_meta->get_attribute($name)->default;
            if (ref $default eq 'CODE') {
                $default = $new_preferences->$default;
            }
            unless ($default eq $values->{$name}) {
                $self->sql->insert_row('editor_preference', {
                    editor => $editor->id,
                    name   => $name,
                    value  => $values->{$name},
                });
            }
        }
        $editor->preferences($new_preferences);

    }, $self->sql);
}

sub donation_check
{
    my ($self, $obj) = @_;

    my $nag = 1;
    $nag = 0 if ($obj->is_nag_free || $obj->is_auto_editor || $obj->is_bot ||
                 $obj->is_relationship_editor || $obj->is_wiki_transcluder ||
                 $obj->is_location_editor);

    my $days = 0.0;
    if ($nag) {
        my $response = $self->c->lwp->get(
            'https://metabrainz.org/donations/nag-check?editor=' . uri_escape_utf8($obj->name),
        );

        if ($response->is_success && $response->content =~ /\s*([-01]+),([-0-9.]+)\s*/) {
            # Possible values for nag will be -1, 0, 1 (only 0 means do not nag)
            $nag = $1;
            $days = $2;
        } else {
            return undef;
        }
    }

    return { nag => $nag, days => $days };
}

sub load_for_collection {
    my ($self, $collection) = @_;

    my $id = $collection->{id};
    return unless $id; # nothing to do

    $self->load($collection);
    my $query = 'SELECT ' . $self->_columns . '
                 FROM ' . $self->_table . "
                 JOIN editor_collection_collaborator ecc ON editor.id = ecc.editor
                 WHERE ecc.collection = $id
                 ORDER BY editor.name, editor.id";
    my @collaborators = $self->query_to_list($query);

    $collection->collaborators(\@collaborators);
}

sub editors_with_subscriptions {
    my ($self, $email_periods, $after, $limit) = @_;

    my @tables = (entities_with('subscriptions',
                                take => sub { return 'editor_subscribe_' . (shift) }),
                  entities_with(['subscriptions', 'deleted'],
                                take => sub { return 'editor_subscribe_' . (shift) . '_deleted' }));
    my $ids = join(' UNION ALL ', map { "SELECT editor FROM $_" } @tables);
    my $query = 'SELECT ' . $self->_columns . ', ep.value AS prefs_value
                   FROM ' . $self->_table . "
              LEFT JOIN editor_preference ep
                     ON ep.editor = editor.id AND
                        ep.name = 'subscriptions_email_period'
                  WHERE coalesce(ep.value, 'daily') = any(?)
                    AND editor.id > ?
                    AND editor.id IN ($ids)
                    AND (editor.privs & $SPAMMER_FLAG) = 0
               ORDER BY editor.id ASC
                  LIMIT ?";

    $self->query_to_list($query, [$email_periods, $after, $limit], sub {
        my ($model, $row) = @_;

        my $editor = $model->_new_from_row($row);
        $editor->preferences->subscriptions_email_period($row->{prefs_value})
            if defined $row->{prefs_value};
        $editor;
    });
}

sub delete {
    my ($self, $editor_id) = @_;
    die "Invalid editor_id: $editor_id" unless $editor_id > 0;
    my $editor = $self->c->model('Editor')->get_by_id($editor_id);
    die "Nonexistent editor_id: $editor_id" unless defined $editor;
    return if $editor->deleted;

    $self->sql->begin;
    $self->sql->do(
        q{UPDATE editor SET name = 'Deleted Editor #' || id,
                           password = ?,
                           ha1 = '',
                           privs = 0,
                           email = NULL,
                           email_confirm_date = NULL,
                           website = NULL,
                           bio = NULL,
                           area = NULL,
                           birth_date = NULL,
                           gender = NULL,
                           deleted = TRUE
         WHERE id = ?},
        Authen::Passphrase::RejectAll->new->as_rfc2307,
        $editor_id,
    );

    $self->sql->do('DELETE FROM editor_preference WHERE editor = ?', $editor_id);
    $self->c->model('EditorLanguage')->delete_editor($editor_id);

    $self->c->model('EditorOAuthToken')->delete_editor($editor_id);
    $self->c->model('Application')->delete_editor($editor_id);

    $self->c->model('EditorSubscriptions')->delete_editor($editor_id);
    $self->c->model('Editor')->unsubscribe_to($editor_id);
    $self->c->model('Collection')->delete_editor($editor_id);

    $self->c->model('Editor')->cancel_edits_and_votes($editor);

    # Delete the editor completely if they're not actually referred to by
    # anything. Otherwise they'll stay behind as "Deleted Editor #...".
    #
    # Tags/ratings are too expensive to delete synchronously here, and are
    # handled by `admin/cleanup/RemoveResidualUserData` instead, which runs
    # hourly.
    #
    # This means the `hard_delete_if_unreferenced` call below won't delete
    # the editor row if only tags or ratings remain. However, the
    # `RemoveResidualUserData` script will later attempt this hard deletion
    # on its own.
    $self->hard_delete_if_unreferenced($editor_id);
    $self->sql->commit;
}

sub hard_delete_if_unreferenced {
    my ($self, @editor_ids) = @_;

    my $unused_editors = $self->sql->select_list_of_hashes(
        $self->_build_unused_editor_query() . "\n" .
            "AND e.deleted\n" .
            'AND e.id = any(?)',
        \@editor_ids,
    );
    my @unused_editor_ids = map { $_->{id} } @$unused_editors;
    if (@unused_editor_ids) {
        $self->sql->do(
            'DELETE FROM editor WHERE id = any(?)',
            \@unused_editor_ids,
        );
    }
    return;
}

sub cancel_edits_and_votes {
    my ($self, $editor) = @_;

    # Cancel any open edits the editor still has
    # We want to cancel the latest edits first, to make sure
    # no conflicts happen that make some cancelling fail and all
    # entities that should be autoremoved do get removed
    my $own_edit_ids = $self->sql->select_single_column_array(
            'SELECT id FROM edit WHERE editor = ? AND status = ? ORDER BY open_time DESC, id DESC',
            $editor->id, $STATUS_OPEN);
    my $own_edits = $self->c->model('Edit')->get_by_ids(@$own_edit_ids);

    for my $edit_id (@$own_edit_ids) {
        $self->c->model('Edit')->cancel($own_edits->{$edit_id});
    }

    # Override any Yes/No votes on open edits with Abstain
    # to avoid pre-deletion vandalism
    my $voted_open_edit_ids = $self->sql->select_single_column_array(
            'SELECT edit.id
             FROM edit
             JOIN vote
               ON edit.id = vote.edit
             WHERE edit.status = ?
               AND vote.editor = ?
               AND vote.vote IN (?, ?)
               AND vote.superseded = FALSE
            ORDER BY open_time DESC',
            $STATUS_OPEN, $editor->id, $VOTE_YES, $VOTE_NO);

    for my $edit_id (@$voted_open_edit_ids) {
        $self->c->model('Vote')->enter_votes(
            $editor,
            [{
                vote    => $VOTE_ABSTAIN,
                edit_id => $edit_id,
            }],
            (override_privs => 1),
        );
    }
}

sub subscription_summary {
    my ($self, $editor_id) = @_;

    $self->sql->select_single_row_hash(
        'SELECT ' .
            join(', ', map {
                "COALESCE(
                   (SELECT count(*) FROM editor_subscribe_$_ WHERE editor = ?),
                   0) AS $_"
            } entities_with('subscriptions')),
        ($editor_id) x 5,
    );
}

sub various_edit_counts {
    my ($self, $editor_id) = @_;
    my %result = map { $_ . '_count' => 0 }
        qw( accepted accepted_auto rejected cancelled open failed );

    my $query =
        q{SELECT
              CASE
                WHEN status = ? THEN
                  CASE
                    WHEN autoedit = 0 THEN 'accepted'
                    ELSE 'accepted_auto'
                  END
                WHEN status = ? THEN 'rejected'
                WHEN status = ? THEN 'cancelled'
                WHEN status = ? THEN 'open'
                ELSE 'failed'
              END AS category,
              COUNT(*) AS count
            FROM edit
           WHERE editor = ?
           GROUP BY category};
    my @params = ($STATUS_APPLIED, $STATUS_FAILEDVOTE, $STATUS_DELETED, $STATUS_OPEN);
    my $rows = $self->sql->select_list_of_lists($query, @params, $editor_id);

    for my $row (@$rows) {
        my ($category, $count) = @$row;
        $result{$category . '_count'} = $count;
    }
    return \%result;
}

sub added_entities_counts {
    my ($self, $editor_id) = @_;

    my $cache_key = "editor:$editor_id:added_entities_counts";
    my $cached_result = $self->c->cache->get($cache_key);
    return $cached_result if defined $cached_result;

    my %result = map { $_ => 0 }
        qw( artist release area cover_art event event_art instrument label
            place recording releasegroup series work other );

    my $query =
        q{SELECT
              CASE
                WHEN type = ? THEN 'artist'
                WHEN type IN (?, ?) THEN 'release'
                WHEN type = ? THEN 'area'
                WHEN type = ? THEN 'cover_art'
                WHEN type = ? THEN 'event'
                WHEN type = ? THEN 'event_art'
                WHEN type = ? THEN 'genre'
                WHEN type = ? THEN 'instrument'
                WHEN type = ? THEN 'label'
                WHEN type = ? THEN 'place'
                WHEN type = ? THEN 'recording'
                WHEN type = ? THEN 'releasegroup'
                WHEN type = ? THEN 'series'
                WHEN type = ? THEN 'work'
                ELSE 'other'
              END AS type,
              COUNT(*) AS count
            FROM edit
           WHERE edit.status = ?
             AND editor = ?
           GROUP BY type};
    my @params = ($EDIT_ARTIST_CREATE, $EDIT_RELEASE_CREATE,
        $EDIT_HISTORIC_ADD_RELEASE, $EDIT_AREA_CREATE, $EDIT_RELEASE_ADD_COVER_ART,
        $EDIT_EVENT_CREATE, $EDIT_EVENT_ADD_EVENT_ART, $EDIT_GENRE_CREATE,
        $EDIT_INSTRUMENT_CREATE, $EDIT_LABEL_CREATE,
        $EDIT_PLACE_CREATE, $EDIT_RECORDING_CREATE, $EDIT_RELEASEGROUP_CREATE,
        $EDIT_SERIES_CREATE, $EDIT_WORK_CREATE, $STATUS_APPLIED);
    my $rows = $self->sql->select_list_of_lists($query, @params, $editor_id);

    for my $row (@$rows) {
        my ($type, $count) = @$row;
        # We just ignore any edits that are not one of the desired types
        if ($type ne 'other') {
            if (defined $result{$type}) {
                $result{$type} += $count;
            } else {
                $result{$type} = $count;
            }
        }
    }

    $self->c->cache->set($cache_key, \%result, 60 * 60 * 24);

    return \%result;
}

sub secondary_counts {
    my ($self, $editor_id, $viewing_own_profile) = @_;

    my $editor = $self->get_by_id($editor_id);
    $self->load_preferences($editor);

    my %result;

    if ($viewing_own_profile || $editor->preferences->public_tags) {
        $result{upvoted_tag_count} = 0;
        $result{downvoted_tag_count} = 0;

        my @tag_tables = entities_with(
            'tags',
            take => sub { shift . '_tag_raw' },
        );
        my $tag_inner_query = join(
            ' UNION ALL ',
            map { "SELECT is_upvote FROM $_ WHERE editor = ?" } @tag_tables,
        );

        my $query = <<~"SQL";
            SELECT x.is_upvote, count(*)
            FROM ($tag_inner_query) x
            GROUP BY x.is_upvote
            SQL

        my $rows = $self->sql->select_list_of_lists(
            $query,
            ($editor_id) x scalar @tag_tables,
        );

        for my $row (@$rows) {
            my ($is_upvote, $count) = @$row;
            if ($is_upvote) {
                $result{upvoted_tag_count} = $count + 0;
            } else {
                $result{downvoted_tag_count} = $count + 0;
            }
        }
  }

    if ($viewing_own_profile || $editor->preferences->public_ratings) {
        my @rating_tables = entities_with(
            'ratings',
            take => sub { shift . '_rating_raw' },
        );
        my $rating_inner_query = join(
            ' UNION ALL ',
            map { "SELECT 1 FROM $_ WHERE editor = ?" } @rating_tables,
        );

        my $query = "SELECT count(*) FROM ($rating_inner_query) x";

        $result{rating_count} = $self->sql->select_single_value(
            $query,
            ($editor_id) x scalar @rating_tables,
        );
    }

    return \%result;
}

sub last_24h_edit_count
{
    my ($self, $editor_id) = @_;

    my $query =
        q{SELECT count(*)
           FROM edit
          WHERE editor = ?
          AND open_time >= (now() - interval '1 day')};

    return $self->sql->select_single_value($query, $editor_id);
}

sub unsubscribe_to {
    my ($self, $editor_id) = @_;
    $self->sql->do(
        'DELETE FROM editor_subscribe_editor WHERE subscribed_editor = ?',
        $editor_id);
}

sub update_last_login_date {
    my ($self, $editor_id) = @_;
    $self->sql->auto_commit(1);
    $self->sql->do('UPDATE editor SET last_login_date = now() WHERE id = ?', $editor_id);
}

sub hash_password {
    my $password = shift;
    Authen::Passphrase::BlowfishCrypt->new(
        salt_random => 1,
        cost => $PASSPHRASE_BCRYPT_COST,
        passphrase => encode('utf-8', $password),
    )->as_rfc2307;
}

sub is_name_used {
    my ($self, $name) = @_;

    return 1 if $self->sql->select_single_value(
        'SELECT 1 FROM editor WHERE lower(name) = lower(?)', $name);
    return 0;
}

sub are_names_equivalent {
    my ($self, $name1, $name2) = @_;

    return $self->sql->select_single_value(
        'SELECT lower(?) = lower(?)', $name1, $name2);
}

no Moose;
__PACKAGE__->meta->make_immutable;
1;

=head1 NAME

MusicBrainz::Server::Data::Editor - database level loading support for
Editors

=head1 DESCRIPTION

Provides support for fetching editors from the database

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2009 Oliver Charles
Copyright (C) 2010 MetaBrainz Foundation

This file is part of MusicBrainz, the open internet music database,
and is licensed under the GPL version 2, or (at your option) any
later version: http://www.gnu.org/licenses/gpl-2.0.txt

=cut