<|start_filename|>Traducir.Web/wwwroot/service-worker.js<|end_filename|>
self.addEventListener('push', function (event) {
    const data = event.data.json();

    const options = {
        body: data.content,
        icon: '/lib/unicorn.png',
        actions: data.actions.map((a, idx) => {
            return {
                action: idx,
                title: a.title
            };
        }),
        requireInteraction: data.requireInteraction,
        data: data,
        tag: data.topic
    };

    const promiseChain = self.registration.showNotification(data.title, options);
    event.waitUntil(promiseChain);
});

self.addEventListener('notificationclick', function (event) {
    const notification = event.notification;

    let destinationUrl = "";
    if (!event.action) {
        destinationUrl = notification.data.url;
    } else {
        const actionId = parseInt(event.action);
        destinationUrl = notification.data.actions[actionId].url;
    }
    destinationUrl = new URL(destinationUrl).href;

    const promiseChain = clients.matchAll({
        type: 'window',
        includeUncontrolled: true
    })
        .then((windowClients) => {
            let matchingClient = null;

            for (let i = 0; i < windowClients.length; i++) {
                const windowClient = windowClients[i];
                if (windowClient.url === destinationUrl) {
                    matchingClient = windowClient;
                    break;
                }
            }

            if (matchingClient) {
                return matchingClient.focus();
            } else {
                return clients.openWindow(destinationUrl);
            }
        });

    event.waitUntil(promiseChain);
    notification.close();
});


<|start_filename|>Traducir.Core/Models/Enums/StringSuggestionState.cs<|end_filename|>
using System;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Reflection;

namespace Traducir.Core.Models.Enums
{
    public enum StringSuggestionState
    {
        [Display(Name = "Created")]
        Created = 1,

        [Display(Name = "Approved by trusted user")]
        ApprovedByTrustedUser = 2,

        [Display(Name = "Approved by reviewer")]
        ApprovedByReviewer = 3,

        [Display(Name = "Rejected")]
        Rejected = 4,

        [Display(Name = "Deleted by owner")]
        DeletedByOwner = 5,

        [Display(Name = "Dismissed by other string")]
        DismissedByOtherString = 6
    }

    public static class StringSuggestionStateExtensions
    {
        public static NotificationType? GetNotificationType(this StringSuggestionState state)
        {
            switch (state)
            {
                case StringSuggestionState.ApprovedByTrustedUser:
                    return NotificationType.SuggestionsApproved;
                case StringSuggestionState.ApprovedByReviewer:
                    return NotificationType.SuggestionsReviewed;
                case StringSuggestionState.Rejected:
                    return NotificationType.SuggestionsRejected;
                case StringSuggestionState.DismissedByOtherString:
                    return NotificationType.SuggestionsOverriden;
                case StringSuggestionState.Created:
                case StringSuggestionState.DeletedByOwner:
                    return null;
                default:
                    throw new ArgumentException($"Missing NotificationType for {state}");
            }
        }

        public static string DisplayName(this StringSuggestionState state)
        {
            if (!Enum.IsDefined(typeof(StringSuggestionState), state))
            {
                return "Unknown";
            }

            return typeof(StringSuggestionState)
                .GetMember(state.ToString())
                .First()
                .GetCustomAttribute<DisplayAttribute>()
                .Name;
        }
    }
}

<|start_filename|>Traducir.Web/bundleconfig.json<|end_filename|>
[
  {
    "outputFileName": "wwwroot/css/site.min.css",
    "inputFiles": [
      "wwwroot/css/*.css"
    ]
  }
]


<|start_filename|>Traducir.Web/ViewModels/Home/IndexViewModel.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Home
{
    public class IndexViewModel
    {
        public StringCountsViewModel StringCounts { get; set; }

        public QueryViewModel StringsQuery { get; set; }

        public FilterResultsViewModel FilterResults { get; set; }

        public bool UserCanSeeIgnoredAndPushStatus { get; set; }

        public int? StringId { get; set; }
    }
}


<|start_filename|>Traducir.Core/Services/ReadonlyTransifexService.cs<|end_filename|>
using System.Collections.Immutable;
using System.Threading.Tasks;
using Microsoft.Extensions.Logging;
using Traducir.Core.Models;
using Traducir.Core.Models.Services;

namespace Traducir.Core.Services
{
    // To be used in development environment only
    public class ReadonlyTransifexService : ITransifexService
    {
        private readonly TransifexService realService;
        private readonly ILogger logger;

        public ReadonlyTransifexService(TransifexService realService, ILoggerFactory loggerFactory)
        {
            this.realService = realService;
            this.logger = loggerFactory.CreateLogger("TRANSIFEX SERVICE");
        }

        public Task<ImmutableArray<TransifexString>> GetStringsFromTransifexAsync()
        {
            return realService.GetStringsFromTransifexAsync();
        }

        public Task<bool> PushStringsToTransifexAsync(ImmutableArray<SOString> strings)
        {
            logger.LogInformation($"{strings.Length} strings were requested to be pushed to Transifex (they weren't)");
            return Task.FromResult<bool>(true);
        }
    }
}

<|start_filename|>Traducir.Core/Services/UserService.cs<|end_filename|>
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Net;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Configuration;
using Traducir.Core.Helpers;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;
using WebPush;

namespace Traducir.Core.Services
{
    public interface IUserService
    {
        Task UpsertUserAsync(User user);

        Task<User> GetUserAsync(int userId);

        Task<List<User>> GetUsersAsync();

        Task<NotificationSettings> GetNotificationSettings(int userId);

        Task<bool> ChangeUserTypeAsync(int userId, UserType userType, int editorId);

        Task<bool> UpdateNotificationSettings(int userId, NotificationSettings newSettings);

        Task WipeNotificationDataAsync(int userId);

        Task<bool> AddNotificationBrowser(int userId, WebPushSubscription subscription);

        Task<bool> SendNotification(int userId, NotificationType type, string host);

        Task SendBatchNotifications(NotificationType type, string host, int? count = null);
    }

    public class UserService : IUserService
    {
        private readonly IDbService _dbService;
        private readonly VapidDetails _vapidDetails;
        private readonly WebPushClient _webPushClient;
        private readonly IConfiguration _configuration;

        public UserService(IDbService dbService, IConfiguration configuration)
        {
            _dbService = dbService;
            _configuration = configuration;

            var subject = configuration.GetValue<string>("VAPID_SUBJECT");
            var publicKey = configuration.GetValue<string>("VAPID_PUBLIC");
            var privateKey = configuration.GetValue<string>("VAPID_PRIVATE");

            _vapidDetails = new VapidDetails(subject, publicKey, privateKey);
            _webPushClient = new WebPushClient();
        }

        public async Task UpsertUserAsync(User user)
        {
            // Get this config variable to avoid updating moderator flag, usefull in develop
            bool updateMod = !_configuration.GetValue<bool>("DO_NOT_UPDATE_ISMODERATOR");

            using (var db = _dbService.GetConnection())
            {
                await db.ExecuteAsync($@"
Declare @wasReviewer Bit, @wasTrusted Bit;
Select @wasReviewer = IsReviewer, @wasTrusted = IsTrusted From Users Where Id = @Id;

If @wasReviewer Is Null
  -- It's an insert!
  Insert Into Users (Id, DisplayName, IsModerator, IsTrusted, IsReviewer, IsBanned, CreationDate, LastSeenDate)
  Values            (@Id, @DisplayName, @IsModerator, @IsModerator, @IsModerator, 0, @CreationDate, @LastSeenDate)
Else
  -- It's an update
  Update Users
  Set    DisplayName = @DisplayName,
         {(updateMod ? "IsModerator = @IsModerator," : string.Empty)}
         IsReviewer = Case When @wasReviewer = 1 Then 1 Else @IsModerator End, -- if the user was a reviewer, keep them
                                                                               -- if they're now a mod, make them a reviewer
         IsTrusted = Case When @wasTrusted = 1 Then 1 Else @IsModerator End,
         LastSeenDate = @LastSeenDate
Where    Id = @Id", user);
            }
        }

        public async Task<User> GetUserAsync(int userId)
        {
            using (var db = _dbService.GetConnection())
            {
                return await db.QueryFirstOrDefaultAsync<User>(@"
Select Id, DisplayName, IsModerator, IsBanned, IsTrusted, IsReviewer,
       CreationDate, LastSeenDate
From   Users
Where  Id = @userId", new
                {
                    userId
                });
            }
        }

        public async Task<List<User>> GetUsersAsync()
        {
            using (var db = _dbService.GetConnection())
            {
                return (await db.QueryAsync<User>(@"
Select Id, DisplayName, IsModerator, IsBanned, IsTrusted, IsReviewer,
       CreationDate, LastSeenDate
From Users")).AsList();
            }
        }

        public async Task<bool> ChangeUserTypeAsync(int userId, UserType userType, int editorId)
        {
            using (var db = _dbService.GetConnection())
            {
                var rows = await db.ExecuteAsync(@"
Insert Into UserHistory
            (UserId, UpdatedById, CreationDate, HistoryTypeId)
Select Id, @editorId, @now,
       Case When @userType = {=TrustedUser} Then {=HistoryMadeTrustedUser}
            When @userType = {=Banned} Then {=HistoryBanned}
            When @userType = {=User} And IsBanned = 1 Then {=HistoryBanLifted}
            When @userType = {=User} And IsTrusted = 1 Then {=HistoryDemotedToRegularUser} End
From   Users
Where  Id = @userId
And    IsModerator = 0
And    IsReviewer = 0;

Update Users
Set    IsTrusted = Case When @userType = {=TrustedUser} Then 1 Else 0 End,
       IsBanned = Case When @userType = {=Banned} Then 1 Else 0 End
Where  Id = @userId
And    IsModerator = 0
And    IsReviewer = 0;", new
                {
                    userId,
                    editorId,
                    userType,
                    UserType.TrustedUser,
                    UserType.Banned,
                    UserType.User,
                    now = DateTime.UtcNow,
                    HistoryMadeTrustedUser = UserHistoryType.MadeTrustedUser,
                    HistoryBanned = UserHistoryType.Banned,
                    HistoryBanLifted = UserHistoryType.BanLifted,
                    HistoryDemotedToRegularUser = UserHistoryType.DemotedToRegularUser
                });
                return rows > 0;
            }
        }

        public async Task<NotificationSettings> GetNotificationSettings(int userId)
        {
            using (var db = _dbService.GetConnection())
            {
                return await db.QueryFirstOrDefaultAsync<NotificationSettings>(@"
Select Case When NextNotificationUrgentStrings Is Null Then 0 Else 1 End NotifyUrgentStrings,
       Case When NextNotificationSuggestionsAwaitingApproval Is Null Then 0 Else 1 End NotifySuggestionsAwaitingApproval,
       Case When NextNotificationSuggestionsAwaitingReview Is Null Then 0 Else 1 End NotifySuggestionsAwaitingReview,
       Case When NextNotificationStringsPushedToTransifex Is Null Then 0 Else 1 End NotifyStringsPushedToTransifex,
       Case When NextNotificationSuggestionsApproved Is Null Then 0 Else 1 End NotifySuggestionsApproved,
       Case When NextNotificationSuggestionsRejected Is Null Then 0 Else 1 End NotifySuggestionsRejected,
       Case When NextNotificationSuggestionsReviewed Is Null Then 0 Else 1 End NotifySuggestionsReviewed,
       Case When NextNotificationSuggestionsOverriden Is Null Then 0 Else 1 End NotifySuggestionsOverriden,
       NotificationsIntervalId NotificationsInterval,
       NotificationsIntervalValue
From   Users
Where  Id = @userId", new { userId });
            }
        }

        public async Task<bool> UpdateNotificationSettings(int userId, NotificationSettings newSettings)
        {
            using (var db = _dbService.GetConnection())
            {
                return await UpdateNotificationSettings(userId, newSettings, db);
            }
        }

        public async Task WipeNotificationDataAsync(int userId)
        {
            using (var db = _dbService.GetConnection())
            {
                await UpdateNotificationSettings(userId, new NotificationSettings(), db);
                await db.ExecuteAsync(@"
Update Users
Set    NotificationDetails = NULL
Where  Id = @userId", new { userId });
            }
        }

        public async Task<bool> AddNotificationBrowser(int userId, WebPushSubscription subscription)
        {
            using (var db = _dbService.GetConnection())
            {
                var existingNotifications = (await GetCurrentSubscriptions(userId, db)).AsList();
                if (!existingNotifications.Any(n => n.Endpoint == subscription.Endpoint))
                {
                    existingNotifications.Add(subscription);
                }
                else
                {
                    return true;
                }

                return (await SetCurrentSubscriptions(userId, existingNotifications, db)) == 1;
            }
        }

        public async Task<bool> SendNotification(int userId, NotificationType type, string host)
        {
            if (type.ShouldBeBatched())
            {
                throw new InvalidOperationException($"Notifications of type {type} should be batched");
            }

            using (var db = _dbService.GetConnection())
            {
                var shouldSendIt = await db.QueryFirstOrDefaultAsync<bool>($@"
Select Top 1 1
From   Users
Where  Id = @userId
And    {type.GetUserColumnName()} < @now", new { userId, now = DateTime.UtcNow });

                if (shouldSendIt)
                {
                    var message = new Models.Services.PushNotificationMessage(
                        type.GetTitle(),
                        type.GetUrl(host, userId),
                        type.GetBody(),
                        type.ToString(),
                        true);
                    var success = await SendNotification(userId, message);
                    if (success)
                    {
                        await db.ExecuteAsync($@"
Update Users
Set    {type.GetUserColumnName()} = DateAdd(minute, NotificationsIntervalId * NotificationsIntervalValue, @now)
Where  Id = @userId", new { userId, now = DateTime.UtcNow });
                    }

                    return success;
                }
            }

            return false;
        }

        public async Task SendBatchNotifications(NotificationType type, string host, int? count = null)
        {
            if (!type.ShouldBeBatched())
            {
                throw new InvalidOperationException($"Notifications of type {type} can't be batched");
            }

            using (var db = _dbService.GetConnection())
            {
                var userIds = await db.QueryAsync<int>($@"
Select Id
From   Users
Where  {type.GetUserColumnName()} < @now", new { now = DateTime.UtcNow });

                var successfulUserIds = new List<int>();
                foreach (var userId in userIds)
                {
                    var message = new Models.Services.PushNotificationMessage(
                        type.GetTitle(count),
                        type.GetUrl(host, userId),
                        type.GetBody(),
                        type.ToString(),
                        true);
                    if (await SendNotification(userId, message))
                    {
                        successfulUserIds.Add(userId);
                    }
                }

                await db.ExecuteAsync($@"
Update Users
Set    {type.GetUserColumnName()} = DateAdd(minute, NotificationsIntervalId * NotificationsIntervalValue, @now)
Where  Id In @successfulUserIds", new { successfulUserIds, columnName = type.GetUserColumnName(), now = DateTime.UtcNow });
            }
        }

        private static Task<IEnumerable<WebPushSubscription>> GetCurrentSubscriptions(int userId, DbConnection db)
        {
            return db.QueryAsync<WebPushSubscription>(@"
Select      json_value(a.value, '$.Endpoint') Endpoint,
            json_value(a.value, '$.Auth') Auth,
            json_value(a.value, '$.P256dh') P256dh
From        Users u
Cross Apply OpenJson(u.NotificationDetails, '$') a
Where       Id = @userId", new { userId });
        }

        private static Task<int> SetCurrentSubscriptions(int userId, IEnumerable<WebPushSubscription> subscriptions, DbConnection db)
        {
            var content = Jil.JSON.Serialize(subscriptions);
            return db.ExecuteAsync(@"
Update Users
Set    NotificationDetails = @content
Where  Id = @userId", new { userId, content });
        }

        private async Task<bool> UpdateNotificationSettings(int userId, NotificationSettings newSettings, DbConnection db)
        {
            return (await db.ExecuteAsync(@"
Update Users
Set    NextNotificationUrgentStrings = Case When @NotifyUrgentStrings > 0 Then @now End,
       NextNotificationSuggestionsAwaitingApproval = Case When @NotifySuggestionsAwaitingApproval > 0 Then @now End,
       NextNotificationSuggestionsAwaitingReview = Case When @NotifySuggestionsAwaitingReview > 0 Then @now End,
       NextNotificationStringsPushedToTransifex = Case When @NotifyStringsPushedToTransifex > 0 Then @now End,
       NextNotificationSuggestionsApproved = Case When @NotifySuggestionsApproved > 0 Then @now End,
       NextNotificationSuggestionsRejected = Case When @NotifySuggestionsRejected > 0 Then @now End,
       NextNotificationSuggestionsReviewed = Case When @NotifySuggestionsReviewed > 0 Then @now End,
       NextNotificationSuggestionsOverriden = Case When @NotifySuggestionsOverriden > 0 Then @now End,
       NotificationsIntervalId = @NotificationsInterval,
       NotificationsIntervalValue = @NotificationsIntervalValue
Where  Id = @userId", new
            {
                newSettings.NotifyUrgentStrings,
                newSettings.NotifySuggestionsAwaitingApproval,
                newSettings.NotifySuggestionsAwaitingReview,
                newSettings.NotifyStringsPushedToTransifex,
                newSettings.NotifySuggestionsApproved,
                newSettings.NotifySuggestionsRejected,
                newSettings.NotifySuggestionsReviewed,
                newSettings.NotifySuggestionsOverriden,
                newSettings.NotificationsInterval,
                newSettings.NotificationsIntervalValue,
                now = DateTime.UtcNow,
                userId
            })) == 1;
        }

        private async Task<bool> SendNotification(int userId, Models.Services.PushNotificationMessage message)
        {
            using (var db = _dbService.GetConnection())
            {
                var endpointsToRemove = new List<string>();
                var success = false;

                var currentSubscriptions = await GetCurrentSubscriptions(userId, db);
                var options = new Dictionary<string, object> { ["vapidDetails"] = _vapidDetails };
                if (message.Topic.HasValue())
                {
                    options["headers"] = new Dictionary<string, object>
                    {
                        ["topic"] = message.Topic
                    };
                }

                foreach (var subscriptionData in currentSubscriptions)
                {
                    var subscription = new PushSubscription(subscriptionData.Endpoint, subscriptionData.P256dh, subscriptionData.Auth);
                    try
                    {
                        await _webPushClient.SendNotificationAsync(subscription, Jil.JSON.Serialize(message, Jil.Options.CamelCase), options);
                        success = true;
                    }
                    catch (WebPushException exception)
                    {
                        if (exception.StatusCode == HttpStatusCode.NotFound || exception.StatusCode == HttpStatusCode.Gone)
                        {
                            endpointsToRemove.Add(subscriptionData.Endpoint);
                        }
                    }
                }

                if (endpointsToRemove.Count > 0)
                {
                    await SetCurrentSubscriptions(userId, currentSubscriptions.Where(s => !endpointsToRemove.Contains(s.Endpoint)), db);
                }

                return success;
            }
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Users/UsersListViewModel.cs<|end_filename|>
using System.Collections.Generic;
using System.Linq;
using Traducir.Core.Models;

namespace Traducir.Web.ViewModels.Users
{
    public class UsersListViewModel
    {
        private UsersListViewModel()
        {
        }

        public UsersListViewModel(IEnumerable<User> users, string siteDomain, bool currentUserCanManageUsers)
        {
            Users = users.Select(u =>
                new UserSummaryViewModel(u, siteDomain, currentUserCanManageUsers));
        }

        public IEnumerable<UserSummaryViewModel> Users { get; }
    }
}


<|start_filename|>Traducir.Web/Views/Home/FilterResults.cshtml<|end_filename|>
@model Traducir.Web.ViewModels.Home.FilterResultsViewModel

<div class="m-2 text-center">
    <h2>
        Results @(Model.Count > 0 ? $"({Model.Count})" : null)
    </h2>
</div>
<table class="table string-list">
    <thead class="thead-light">
        <tr>
            <th>String</th>
            <th>Translation</th>
            <th>Suggestions</th>
        </tr>
    </thead>
    <tbody>
        @if (Model.Count == 0)
        {
            <tr>
                <td colSpan="3" class="text-center">No results (sad trombone)</td>
            </tr>
        }
        else
        {
            foreach (var str in Model.StringSummaries)
            {
                <partial name="StringSummary" model="str" />
            }
        }
    </tbody>
</table>


<|start_filename|>Traducir.Web/ViewModels/Backups/Backup.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Backups
{
    public class Backup
    {
        public string Filename { get; set; }

        public string Url { get; set; }
    }
}

<|start_filename|>Traducir.Migrations/Migrations/009 - AdjustStringHistoryCommentSize.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(9, "Adjust StringHistory Comment size")]
    public class AdjustStringHistoryCommentSize : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute("Alter Table StringHistory Alter Column Comment NVarChar(Max) Null");
        }
    }
}

<|start_filename|>Traducir.Migrations/Migrations/005 - AddEnumTranslationUpdatedFromDump.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(5, "Add string history enum: TranslationUpdatedFromDump")]
    public class AddEnumTranslationUpdatedFromDump : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If Not Exists (Select 1 From StringHistoryTypes Where Id = 5)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (5, 'TranslationUpdatedFromDump', 'The translation was updated from the SO DB dump')
End
");
        }
    }
}

<|start_filename|>Traducir.Web/Views/Users/UserSummary.cshtml<|end_filename|>
@using Traducir.Core.Helpers
@using Traducir.Core.Models.Enums
@model Traducir.Web.ViewModels.Users.UserSummaryViewModel
@{
    var user = Model.User;
#if RISKY
    var impersonateEnabled = true;
#else
    var impersonateEnabled = false;
#endif
}
<tr data-user-id="@user.Id">
    <td>
        <a href="@Model.UserProfileLink" target="_blank">
            @user.DisplayName @(user.IsModerator ? "♦" : null)
        </a>
    </td>
    <td>@user.UserType.ToDisplayString()</td>
    <td>
        @if (Model.CurrentUserCanManageUsers)
        {
            <div class="btn-group" role="group">
                @if (impersonateEnabled)
                {
                    <a href="/impersonate/@user.Id" class="btn btn-sm btn-danger">
                        Impersonate
                    </a>
                }
                <a href="@Url.Action("SuggestionsByUser", "Users", new { userId = user.Id})" class="btn btn-sm btn-primary">view suggestions</a>
                @if (user.UserType == UserType.User)
                {
                    <button type="button" class="btn btn-sm btn-warning" data-change-to-user-type="@((int)UserType.TrustedUser)">
                        Make trusted user
                    </button>
                }
                @if (user.UserType == UserType.Banned)
                {
                    <button type="button" class="btn btn-sm btn-warning" data-change-to-user-type="@((int)UserType.User)">
                        Lift Ban
                    </button>
                }
                @if (user.UserType == UserType.TrustedUser)
                {
                    <button type="button" class="btn btn-sm btn-danger" data-change-to-user-type="@((int)UserType.User)">
                        Make regular user
                    </button>
                }
                @if (Model.UserCanBeBanned)
                {
                    <button type="button" class="btn btn-sm btn-danger" data-change-to-user-type="@((int)UserType.Banned)">
                        Ban User
                    </button>
                }
            </div>
        }
        else if (impersonateEnabled)
        {
            <a href="/impersonate/<EMAIL>" class="btn btn-sm btn-danger">
                Impersonate
            </a>
        }
    </td>
</tr>

<|start_filename|>Traducir.Core/Models/SOStringSuggestion.cs<|end_filename|>
using System;
using Traducir.Core.Models.Enums;

namespace Traducir.Core.Models
{
    public class SOStringSuggestion
    {
        public int Id { get; set; }

        public int StringId { get; set; }

        public string Suggestion { get; set; }

        public StringSuggestionState State { get; set; }

        public string CreatedByName { get; set; }

        public int CreatedById { get; set; }

        public string LastStateUpdatedByName { get; set; }

        public int? LastStateUpdatedById { get; set; }

        public DateTime CreationDate { get; set; }

        public string OriginalString { get; set; }

        public SOStringSuggestionHistory[] Histories { get; set; }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Users/UserSummaryViewModel.cs<|end_filename|>
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels.Users
{
    public class UserSummaryViewModel
    {
        private UserSummaryViewModel()
        {
        }

        public UserSummaryViewModel(User user, string siteDomain, bool currentUserCanManageUsers)
        {
            this.User = user;
            this.SiteDomain = siteDomain;
            this.CurrentUserCanManageUsers = currentUserCanManageUsers;
        }

        public User User { get; }

        public bool CurrentUserCanManageUsers { get; }

        private string SiteDomain { get; }

        public string UserProfileLink =>
            $"https://{SiteDomain}/users/{User.Id}";

        public bool UserCanBeBanned =>
            User.UserType != UserType.Banned &&
            User.UserType != UserType.TrustedUser &&
            User.UserType != UserType.Reviewer &&
            !User.IsModerator;
    }
}


<|start_filename|>Traducir.Web/wwwroot/lib/bootstrap-native/css/theme.css<|end_filename|>
/* REQUIRED STYLING */
/* styles the close button for the dismissible popovers */
.popover .close { position: absolute; top: 7px; right: 10px; }
/* REQUIRED STYLING */


/* GLOBAL STYLES
-------------------------------------------------- */
/* Padding below the footer and lighter body text */

body {
  color: #5a5a5a;
  font-family: -apple-system,BlinkMacSystemFont,"Segoe UI",Roboto,"Helvetica Neue",Arial,sans-serif;
}

#main-container {padding-top: 20px;}

footer { padding: 20px 0 10px; background: #e6f0f8 }

/* some typo */
h1,h2,h3,h4,h5,strong,b { color: #111 }
h1 { letter-spacing: -2px }
h2 { letter-spacing: -3px; font-size: 56px; font-weight: bold }
h3:not(.popover-title) {
  font-size: 32px;
  font-weight: bold;
  letter-spacing: -2px;
}

hr.clearfix {clear: both; width: 100%; float: left}

/* colors */
.bg-bootstrap { background-color: #563d7c }
.bg-pink { background-color: #E91E63 }
.bg-indigo { background-color: #673ab7 }
.bg-purple { background-color: #9c27b0 }
.bg-red { background-color: #e51c23 }


/* CONTENT STYLING
-------------------------------------------------- */

 /* required for scrollspy to work perfect */
 section { position: relative; display: inline-block; width: 100%; } 

.scrollspy-example {
  position: relative; height: 150px; overflow: auto; margin-bottom: 20px;
  padding: 0 15px; border: 1px solid #ddd; border-radius: 4px 0 0 4px;
}
 /* required for scrollspy to work perfect */

/* some content customization | because why not */
#componentTab .nav-tabs, #componentTab .nav-pills, #componentTab .navbar-nav, #componentTab .nav-stacked { margin-bottom: 15px }

#componentTab .nav-stacked li a {padding: 5px 10px}
#componentTab .nav-stacked,
#componentTab .nav.panel { float: left; margin: 0 15px 20px 0 }
#componentTab .nav-list { float: left; margin: 0 15px 20px 0; border-right: 1px solid #ddd }

#componentTab .nav.panel > li.active > a,
#componentTab .nav-list > li.active > a { background: #286090; color: #fff }

#componentTab .nav.panel > li:first-child > a { border-radius: 3px 3px 0 0; }
#componentTab .nav.panel > li:last-child > a { border-radius: 0 0 3px 3px; }

#componentTab .navbar-nav { background: #eee; width: 100%; border-radius: 3px; }
#componentTab .navbar-nav > li:first-child > a { border-radius: 3px 0 0 3px; }
#componentTab .navbar-nav > li.active > a { background: #286090; color: #fff }

#componentTab .nav.nav-stacked .dropdown-menu,
#componentTab .nav.panel .dropdown-menu,
#componentTab .nav-list .dropdown-menu { margin: 0; top: 0; left: 103%; }

#componentTab .nav.panel + .tab-content,
#componentTab .nav.nav-list + .tab-content { display: table-cell }

/*#componentTab .btn-group { position: absolute; top:8px; left: 85px }*/
#componentTab .btn-group > .btn { margin: 0}
#componentTab .btn-group { margin: 0 0 20px }

/* buttons */
p .btn, p .form-control {vertical-align: top}
p .btn, p .form-control {margin-bottom: 20px; vertical-align: top}

/*p .btn-group {margin-bottom: 20px}*/
#collapseExampleWrapper { padding: 20px 20px 10px }
#collapseExample .well {margin-bottom: 10px}
.btn-group .btn {margin-bottom: 0}

#components {padding-bottom: 20px }

/* nested dropdowns */
.dropdown-menu .dropdown-menu.left {
  top: -1px;
  left: auto; right: 100%;
  margin: 0 3px 0 0;
}
.dropdown-menu .dropdown-menu.right {
  top: -1px;
  left: 100%;
  margin: 0 0 0 3px;
}

/* when on mobile, disable affix position */
#side-nav {clear: both}
#side-nav .nav.affix,
#side-nav .nav.affix.affix-bottom { position: relative; margin: 20px 0; backface-visibility: hidden; }

#side-nav > .nav li.active > a,
#side-nav > .nav li.active:hover > a { background-color: #e6f0f8 }
#side-nav > .nav li:hover > a { background-color: #f5f9fc }


/* CUSTOMIZE THE NAVBAR
-------------------------------------------------- */

.navbar-inverse .navbar-brand { color: #fff }

.navbar-nav .open .dropdown-menu { left: auto; right:0 }

.navbar-collapse.collapsing {overflow-y: hidden}

.navbar-nav { float: none }
.navbar-fixed-top { padding: .5rem 0; border:0 }
.navbar-brand { font-size: 20px }
.navbar-brand svg { display: inline-block; vertical-align:top; margin: -8px 0 0 0 } 
.navbar-brand span { display: inline-block; vertical-align: top }

.navbar-inverse .navbar-nav>.open>a, 
.navbar-inverse .navbar-nav>.open>a:focus, 
.navbar-inverse .navbar-nav>.open>a:hover {
    background-color: transparent;
}

.navbar-inverse .navbar-toggle {
    border-color: #eee;
}
.navbar-inverse .navbar-toggle:hover,
.navbar-inverse .navbar-toggle:focus {
    border-color: #fff;
}

.navbar-inverse .navbar-toggle:focus, .navbar-inverse .navbar-toggle:hover {
    background-color: #673ab7;
}

/* CUSTOMIZE THE CAROUSEL
-------------------------------------------------- */

/* Carousel base class */
#myCarousel.carousel {
  height: 500px;
}
/* Since positioning the image, we need to help out the caption */
.carousel-caption {
  z-index: 10;
}
/* do some transition */ 
#myCarousel .item,
#myCarousel .item.active {
  -webkit-transition-timing-function: cubic-bezier(.45,.18,0,1);
  transition-timing-function: cubic-bezier(.45,.18,0,1);
}
#myCarousel .carousel-caption {
  -webkit-transition: all 0.3s; 
  transition: all 0.3s;
  -webkit-transition-timing-function: cubic-bezier(0,.24,0,1);
  transition-timing-function: cubic-bezier(0,.24,0,1);
  opacity: 0;
}

/* when going forward, captions slide left */
#myCarousel .next .carousel-caption {
  -webkit-transform: translate3d(50%, 0, 0);
  transform: translate3d(50%, 0, 0);
}
#myCarousel .prev .carousel-caption {
  -webkit-transform: translate3d(-50%, 0, 0);
  transform: translate3d(-50%, 0, 0);
}
/* when going backward, captions move different */
#myCarousel .right.next .carousel-caption {
  -webkit-transform: /*translate3d(50%, 0, 0)*/ rotateX(90deg);
  transform: /*translate3d(50%, 0, 0)*/ rotateX(90deg);
}
#myCarousel .right.prev .carousel-caption {
  -webkit-transform: /*translate3d(-50%, 0, 0)*/ rotateX(-90deg);
  transform: /*translate3d(-50%, 0, 0)*/ rotateX(-90deg);
}
#myCarousel .carousel-caption.slide {
  -webkit-transform: translate3d( 0%, 0, 0);
  transform: translate3d(0%, 0, 0);
  opacity:1;
  /*visibility: visible;  */
}

/* Declare heights because of positioning of img element */
#myCarousel .item {
  height: 500px; width: 100%;
  background-color: #777;
}

.carousel h2 { letter-spacing: -1px; font-size: 30px; font-weight: bold }

.carousel .item.prev,
.carousel .item.next {display: block}

#myCarousel .carousel-inner > .item > .item-bg {
  position: absolute;
  top: 0;
  left: 0;
  width: 100%;
  height: 500px;
}

.carousel .btn-default.btn-empty { border-width: 2px; color: #fff; border-color: #fff; background: none transparent }
.carousel .btn-default.btn-empty:hover { color: #337ab7; background: #fff }

.carousel h1,.carousel h2,.carousel h3,.carousel strong,.carousel b { color: #fff }
.carousel a { color: #fff; text-decoration:underline }
.carousel a:hover { color: #eee }
.carousel a.btn { text-decoration:none }

/* the small carousel thumbnail */
#carousel-example-generic { margin: 0 }
#carousel-example-generic,
#carousel-example-generic .carousel-inner > .item > img { height: auto }

/* dropdown form */

.form-vertical {
  padding: 10px 10px 5px 10px;
  width: 250px;
}

/* side navigation */
#side-nav  { margin-top: 40px; clear: none }

/*#side-nav > .nav > li > ul { margin-top: 2px; height:0; opacity: 0; padding-left: 15px; transition: all 0.3s ease-out; }*/
#side-nav > .nav li > ul { height:0; opacity: 0; padding-left: 15px; transition: height 0.3s ease-out, opacity 0.3s ease; }
#side-nav > .nav li { overflow: hidden }
#side-nav > .nav li.active > ul { opacity: 1; margin-top: 2px; }
#side-nav > .nav li.active.list2 > ul { height: 66px; height: calc(32px*2 + 2px); }
#side-nav > .nav li.active.list3 > ul { height: 100px; height: calc(32px*3 + 2px*2); }
#side-nav > .nav li.active.list4 > ul { height: 134px; height: calc(32px*4 + 2px*3); }
#side-nav > .nav li.active.list5 > ul { height: 168px; height: calc(32px*5 + 2px*4); }
#side-nav > .nav li.active.list6 > ul { height: 202px; height: calc(32px*6 + 2px*5); }
#side-nav > .nav li.active.list7 > ul { height: 236px; height: calc(32px*7 + 2px*6); }
#side-nav > .nav li.active.list8 > ul { height: 270px; height: calc(32px*8 + 2px*7); }
#side-nav > .nav li.active.list9 > ul { height: 304px; height: calc(32px*9 + 2px*8); }

#side-nav > .nav li.active.list11 > ul { height: 338px; height: calc(32px*11 + 2px*10); }
#side-nav > .nav li.active.list11.additional > ul { height: 506px; height: calc(32px*11 + 2px*10 + 32px*5 + 2px*4); }

#side-nav > .nav li > a { z-index: 1; padding: 6px 10px; }
#side-nav > .nav li > ul { z-index: 0 }

.tooltip b, .tooltip strong {color: #fff}

/* RESPONSIVE CSS
-------------------------------------------------- */
@media screen and (max-width: 1200px) {
	.carousel-caption {
	  right: 10%;
	  left: 10%;
	  padding-bottom: 30px;
	}
}
@media (min-width: 768px) {

	/* affix example */
	#side-nav .nav.affix,
	#side-nav .nav.affix-bottom { position: fixed !important;  width: 263px; }
	#side-nav .nav.affix { top: 50px; }
	#side-nav .nav.affix-bottom { top: auto; bottom: 100px }

  /* Navbar positioning foo */
  .navbar .navbar-nav {margin-right: 15px; float: right}

  
  /* Bump up size of carousel content */
  .carousel-caption p {
    margin-bottom: 20px;
    font-size: 21px;
    line-height: 1.4;
  }
  .carousel h2 { letter-spacing: -3px; font-size: 56px; font-weight: bold }

}


/* IE8 without modernizr */
.ie8 #side-nav .nav.affix { position: fixed !important;  width: 215px; top: 0; }
.ie8 #side-nav .nav.affix.affix-bottom { top: auto;  bottom: 100px; } 

.ie8 .navbar-wrapper {  margin-top: 40px; }
.ie8 .navbar-wrapper .container { padding-right: 15px; padding-left: 15px; }
.ie8 .navbar-wrapper .navbar { padding-right: 0; padding-left: 0; }

.ie8 .navbar .navbar-nav {margin-right: 15px; float: right}
/* IE8 without modernizr */


/* code wrapping */
pre {
	display: block;
	padding: 10px 15px !important;
	/*margin: 0 0 20px !important;*/
	line-height: 2.08;
	color: #999;
	word-break: break-all;
	background-color: rgb(33,33,33);
	background-color: rgba(11,11,11,0.8);
	border: none;
	border-radius: 4px;
	text-align: left;
	position: relative;
}
pre code {background: none;padding: 0; font-weight: normal; font-size: 100%;}

<|start_filename|>Traducir.Web/ViewModels/Users/SubscriptionViewModel.cs<|end_filename|>
using Traducir.Core.Models;

namespace Traducir.Web.ViewModels.Users
{
    public class SubscriptionViewModel
    {
        public string Endpoint { get; set; }

        public KeysData Keys { get; set; }

        public WebPushSubscription ToWebPushSubscription()
        {
            return new WebPushSubscription
            {
                Endpoint = Endpoint,
                Auth = Keys.Auth,
                P256dh = Keys.P256dh
            };
        }

        public class KeysData
        {
            public string Auth { get; set; }

            public string P256dh { get; set; }
        }
    }
}

<|start_filename|>Traducir.Web/Controllers/UsersController.cs<|end_filename|>
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Traducir.Core.Helpers;
using Traducir.Core.Models.Enums;
using Traducir.Core.Services;
using Traducir.Web.ViewModels.Users;

namespace Traducir.Web.Controllers
{
    public class UsersController : Controller
    {
        private readonly IUserService _userService;
        private readonly IAuthorizationService _authorizationService;
        private readonly IConfiguration _configuration;
        private readonly ISOStringService _soStringService;

        public UsersController(
            IUserService userService,
            IAuthorizationService authorizationService,
            IConfiguration configuration,
            ISOStringService soStringService)
        {
            _userService = userService;
            _authorizationService = authorizationService;
            _configuration = configuration;
            _soStringService = soStringService;
        }

        [Route("users")]
        public async Task<IActionResult> Users()
        {
            var users = (await _userService.GetUsersAsync())
                .OrderByDescending(u => u.UserType)
                .ThenByDescending(u => u.LastSeenDate)
                .ToList();
            var siteDomain = _configuration.GetValue<string>("STACKAPP_SITEDOMAIN");
            var currentUserCanManageUsers = (await _authorizationService.AuthorizeAsync(User, TraducirPolicy.CanManageUsers)).Succeeded;

            var model = new UsersListViewModel(users, siteDomain, currentUserCanManageUsers);
            return View(model);
        }

        [HttpPost]
        [Authorize(Policy = TraducirPolicy.CanManageUsers)]
        [Route("users/change-type")]
        public async Task<IActionResult> ChangeUserType([FromBody] ChangeUserTypeViewModel model)
        {
            // explicitly whitelist accepted types
            if (model.UserType != UserType.Banned && model.UserType != UserType.User && model.UserType != UserType.TrustedUser)
            {
                return BadRequest();
            }

            var success = await _userService.ChangeUserTypeAsync(model.UserId, model.UserType, User.GetClaim<int>(ClaimType.Id));
            if (!success)
            {
                return BadRequest();
            }

            var user = await _userService.GetUserAsync(model.UserId);
            var siteDomain = _configuration.GetValue<string>("STACKAPP_SITEDOMAIN");
            var currentUserCanManageUsers = (await _authorizationService.AuthorizeAsync(User, TraducirPolicy.CanManageUsers)).Succeeded;
            return View("UserSummary", new UserSummaryViewModel(user, siteDomain, currentUserCanManageUsers));
        }

        [Authorize]
        [Route("users/{userId:INT}/suggestions")]
        public async Task<IActionResult> SuggestionsByUser(int userId, StringSuggestionState? state)
        {
            var suggestions = await _soStringService.GetSuggestionsByUser(userId, state);
            var siteDomain = _configuration.GetValue<string>("STACKAPP_SITEDOMAIN");
            var model = new SuggestionsByUserViewModel { CurrentState = state, SiteDomain = siteDomain, UserId = userId, Suggestions = suggestions };

            return View(model);
        }

        [Authorize]
        [Route("users/me/notifications")]
        public async Task<IActionResult> Notifications()
        {
            var userId = User.GetClaim<int>(ClaimType.Id);
            var notificationSettings = await _userService.GetNotificationSettings(userId);
            var vapid = _configuration.GetValue<string>("VAPID_PUBLIC");

            var model = new NotificationsViewModel
            {
                NotificationSettings = notificationSettings,
                VapidPublic = vapid
            };

            return View(model);
        }

        [HttpPost]
        [Authorize]
        [Route("users/me/notifications/update")]
        public async Task<IActionResult> UpdateNotificationSettings([FromBody] UpdateNotificationSettingsViewModel model)
        {
            var userId = User.GetClaim<int>(ClaimType.Id);
            if (!await _userService.UpdateNotificationSettings(userId, model.Notifications))
            {
                return BadRequest();
            }

            if (!await _userService.AddNotificationBrowser(userId, model.Subscription.ToWebPushSubscription()))
            {
                return BadRequest();
            }

            return NoContent();
        }

        [HttpPost]
        [Authorize]
        [Route("/delete-notifications")]
        public async Task<IActionResult> DeleteNotifications()
        {
            var userId = User.GetClaim<int>(ClaimType.Id);
            await _userService.WipeNotificationDataAsync(userId);
            return NoContent();
        }
    }
}

<|start_filename|>Traducir.Web/Helpers/StaticDomainTagHelper.cs<|end_filename|>
using System;
using Microsoft.AspNetCore.Mvc.TagHelpers;
using Microsoft.AspNetCore.Razor.TagHelpers;
using Microsoft.Extensions.Configuration;
using StackExchange.Exceptional.Internal;

namespace Traducir.Web.Helpers
{
    [HtmlTargetElement("script", Attributes = "use-static-domain", TagStructure = TagStructure.WithoutEndTag)]
    [HtmlTargetElement("link", Attributes = "use-static-domain", TagStructure = TagStructure.WithoutEndTag)]
    public class StaticDomainTagHelper : TagHelper
    {
        public StaticDomainTagHelper(IConfiguration configuration)
        {
            StaticDomain = configuration.GetValue<string>("STATIC_DOMAIN");
        }

        public override int Order => 1000;

        private string StaticDomain { get; set; }

        public override void Process(TagHelperContext context, TagHelperOutput output)
        {
            if (context == null)
            {
                throw new ArgumentNullException(nameof(context));
            }

            if (output == null)
            {
                throw new ArgumentNullException(nameof(output));
            }

            if (StaticDomain.HasValue())
            {
                void processAttribute(string attrName)
                {
                    if (context.AllAttributes.ContainsName(attrName))
                    {
                        output.CopyHtmlAttribute(attrName, context);
                        if (output.Attributes.TryGetAttribute(attrName, out var srcAttr))
                        {
                            output.Attributes.SetAttribute(attrName, "https://" + StaticDomain + srcAttr.Value);
                        }
                    }
                }

                processAttribute("src");
                processAttribute("href");
            }

            if (output.Attributes.TryGetAttribute("use-static-domain", out var customAttr))
            {
                output.Attributes.Remove(customAttr);
            }
        }
    }
}

<|start_filename|>Traducir.Web/Controllers/BackupsController.cs<|end_filename|>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;
using System.Xml;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Traducir.Web.ViewModels.Backups;

namespace Traducir.Web.Controllers
{
    public class BackupsController : Controller
    {
        private readonly IConfiguration _configuration;

        public BackupsController(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        [Route("backups")]
        public async Task<IActionResult> Index()
        {
            var account = _configuration.GetValue<string>("AZURE_BACKUP_ACCOUNT");
            var container = _configuration.GetValue<string>("AZURE_BACKUP_CONTAINER");
            var suffix = _configuration.GetValue<string>("AZURE_BACKUP_SUFFIX");

            var files = new List<Backup>();
            using (var httpClient = new HttpClient())
            {
                var response = await httpClient.GetAsync(new Uri($"https://{account}.blob.core.windows.net/{container}?restype=container&comp=list"));
                response.EnsureSuccessStatusCode();

                var dataStr = await response.Content.ReadAsStringAsync();
                var doc = new XmlDocument();
                doc.LoadXml(dataStr);

                foreach (XmlNode node in doc.SelectNodes("//Name"))
                {
                    if (node.InnerText.EndsWith(suffix, StringComparison.OrdinalIgnoreCase))
                    {
                        files.Add(new Backup
                        {
                            Filename = node.InnerText,
                            Url = $"https://{account}.blob.core.windows.net/{container}/{node.InnerText}",
                        });
                    }
                }

                files = files.OrderByDescending(f => f.Filename).ToList();
            }

            return View(files);
        }
    }
}

<|start_filename|>Traducir.Migrations/Program.cs<|end_filename|>
﻿using System;
using System.Data.SqlClient;
using SimpleMigrations;
using SimpleMigrations.Console;
using SimpleMigrations.DatabaseProvider;

namespace Traducir.Migrations
{
    public class Program
    {
        static void Main(string[] args)
        {
            var connectionString = Environment.GetEnvironmentVariable("CONNECTION_STRING");

            var migrationsAssembly = typeof(Program).Assembly;
            using(var db = new SqlConnection(connectionString))
            {
                var databaseProvider = new MssqlDatabaseProvider(db);
                var migrator = new SimpleMigrator(migrationsAssembly, databaseProvider);

                var consoleRunner = new ConsoleRunner(migrator);
                consoleRunner.Run(args);
            }
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/StringCountsViewModel.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Home
{
    public class StringCountsViewModel
    {
        public int TotalStrings { get; set; }

        public int WithoutTranslation { get; set; }

        public int WithPendingSuggestions { get; set; }

        public int WaitingApproval { get; set; }

        public int WaitingReview { get; set; }

        public int UrgentStrings { get; set; }
    }
}


<|start_filename|>Traducir.Web/Views/Home/Index.cshtml<|end_filename|>
@using Traducir.Web.ViewModels.Home
@model Traducir.Web.ViewModels.Home.IndexViewModel

@section Scripts
{
    <script>
        var stringQueryFilters = {
            key: @Json.Serialize(Model.StringsQuery.Key),
            sourceRegex: @Json.Serialize(Model.StringsQuery.SourceRegex),
            translationRegex: @Json.Serialize(Model.StringsQuery.TranslationRegex),
            suggestionsStatus: @((int)Model.StringsQuery.SuggestionsStatus),
            translationStatus: @((int)Model.StringsQuery.TranslationStatus),
            urgencyStatus: @((int)Model.StringsQuery.UrgencyStatus),
            ignoredStatus: @((int)Model.StringsQuery.IgnoredStatus),
            pushStatus:  @((int)Model.StringsQuery.PushStatus)
        };
    </script>

    <script>
        require(["strings/strings"], strings => {
            strings.init()
            @if(Model.StringId.HasValue){
                <text>
                    strings.show(@Json.Serialize(Model.StringId));
                </text>
            }
        });
    </script>
}

<div class="container">
    <div class="m-2 text-center">
        <h2>Filters</h2>
    </div>
    <div class="row">
        <div class="col">
            <div class="form-group">
                <label for="sourceRegex">Source Regex</label>
                <input type="text" class="js-string-query-filter form-control" data-string-query-key="sourceRegex" id="sourceRegex" placeholder="^question" value="@Model.StringsQuery.SourceRegex">
            </div>
        </div>
        <div class="col">
            <div class="form-group">
                <label for="translationRegex">Translation Regex</label>
                <input type="text" class="js-string-query-filter form-control" data-string-query-key="translationRegex" id="translationRegex" placeholder="(?i)pregunta$" value="@Model.StringsQuery.TranslationRegex">
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col">
            <div class="form-group">
                <label for="withoutTranslation">Strings without translation</label>
                @Html.DropDownListFor(
                    model => model.StringsQuery.TranslationStatus,
                    Html.GetEnumSelectList(typeof(TranslationStatus)),
                    null,
                    new { @class = "js-string-query-filter form-control", id = "withoutTranslation", data_string_query_key = "translationStatus" })
            </div>
        </div>
        <div class="col">
            <div class="form-group">
                <label for="suggestionsStatus">Strings with pending suggestions</label>
                @Html.DropDownListFor(
                    model => model.StringsQuery.SuggestionsStatus,
                    Html.GetEnumSelectList(typeof(SuggestionApprovalStatus)),
                    null,
                    new { @class = "js-string-query-filter form-control", id = "suggestionsStatus", data_string_query_key = "suggestionsStatus" })
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col">
            <div class="form-group">
                <label for="key">Key</label>
                <input type="text" class="js-string-query-filter form-control" data-string-query-key="key" id="key" value="@Model.StringsQuery.Key">
            </div>
        </div>
        <div class="col">
            <div class="form-group">
                <label for="suggestionsStatus">Strings with urgency status</label>
                @Html.DropDownListFor(
                    model => model.StringsQuery.UrgencyStatus,
                    Html.GetEnumSelectList(typeof(UrgencyStatus)),
                    null,
                    new { @class = "js-string-query-filter form-control", id = "urgencyStatus", data_string_query_key = "urgencyStatus" })
            </div>
        </div>
    </div>
    @if (Model.UserCanSeeIgnoredAndPushStatus)
    {
        <div class="row">
            <div class="col">
                <div class="form-group">
                    <label for="ignoredStatus">Ignored strings</label>
                    @Html.DropDownListFor(
                        model => model.StringsQuery.IgnoredStatus,
                        Html.GetEnumSelectList(typeof(IgnoredStatus)),
                        null,
                        new { @class = "js-string-query-filter form-control", id = "ignoredStatus", data_string_query_key = "ignoredStatus" })
                </div>
            </div>
            <div class="col">
                <div class="form-group">
                    <label for="suggestionsStatus">Strings that need push</label>
                    @Html.DropDownListFor(
                        model => model.StringsQuery.PushStatus,
                        Html.GetEnumSelectList(typeof(PushStatus)),
                        null,
                        new { @class = "js-string-query-filter form-control", id = "pushStatus", data_string_query_key = "pushStatus" })
                </div>
            </div>
        </div>
    }
    <div class="row text-center mb-5">
        <div class="col">
            <a class="btn btn-secondary" href="/">Reset</a>
        </div>
    </div>

    <div id="strings_list">
        @if (Model.FilterResults == null)
        {
            <div class="row text-center">
                <div class="col d-none d-lg-block">
                    <div class="btn-group" role="group" aria-label="Basic example">
                        <button type="button" class="btn btn-outline-secondary" disabled="">@Model.StringCounts.TotalStrings total strings</button>
                        @if (Model.StringCounts.UrgentStrings > 0)
                        {
                            <a class="btn btn-danger" href="/filters?urgencyStatus=1">@Model.StringCounts.UrgentStrings marked as urgent</a>
                        }
                        <a class="js-string-query-filter btn btn-outline-danger" data-string-query-key="translationStatus" data-string-query-value="2" href="#">@Model.StringCounts.WithoutTranslation without translation</a>
                        <a class="js-string-query-filter btn btn-outline-primary" data-string-query-key="suggestionsStatus" data-string-query-value="3" href="#">@Model.StringCounts.WaitingApproval suggestions awaiting approval</a>
                        <a class="js-string-query-filter btn btn-outline-success" data-string-query-key="suggestionsStatus" data-string-query-value="4" href="#">@Model.StringCounts.WaitingReview approved suggestions awaiting review</a>
                    </div>
                </div>
            </div>
        }
        else
        {
            <partial name="FilterResults" model="Model.FilterResults" />
        }
    </div>
</div>


<|start_filename|>Traducir.Web/Views/Shared/_Layout.cshtml<|end_filename|>
@using Microsoft.Extensions.Configuration
@inject IConfiguration Configuration
@inject Traducir.Web.ViewModels.LayoutViewModel viewModel
@inject Microsoft.AspNetCore.Antiforgery.IAntiforgery Xsrf
@functions{
    public string GetAntiXsrfRequestToken()
    {
        return Xsrf.GetAndStoreTokens(Context).RequestToken;
    }
}
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no" />
    @{
        var siteName = Configuration.GetValue<string>("FRIENDLY_NAME");
    }
    <title>traducir.win - @siteName</title>

    <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.min.css" asp-append-version="true" use-static-domain />
    <environment include="Development">
        <link rel="stylesheet" href="~/css/site.css" />
        <link rel="stylesheet" href="~/css/spinner.css" />
    </environment>
    <environment exclude="Development">
        <link rel="stylesheet" href="~/css/site.min.css" asp-append-version="true" use-static-domain />
    </environment>

    <script defer src="~/lib/fontawesome.js" asp-append-version="true" use-static-domain></script>
</head>
<body>
    <mini-profiler />

    <div id="modal-container" class="modal fade" tabindex="-1" role="dialog" aria-hidden="true">
        <div class="modal-dialog w-95 mw-100" role="document">
            <div class="modal-content">
                <!-- the .setContent() method will update this element's HTML -->
            </div>
        </div>
    </div>
    <div class="loading" id="spinner" style='display:none'>Loading...</div>
    <div id="root">
        <nav class="fixed-top navbar navbar-expand-lg navbar-dark bg-dark">
            <div class="container">
                <a class="navbar-brand d-none d-lg-block" href="/">@viewModel.ConfigName Translations 🦄 @viewModel.UserInfo</a>
                <button type="button" class="navbar-toggler" data-toggle="collapse" data-target="#navbarCollapse" aria-controls="navbarCollapse" aria-expanded="false" aria-label="Toggle navigation">
                    <span class="navbar-toggler-icon"></span>
                </button>
                <div class="collapse navbar-collapse" id="navbarCollapse">
                    <ul class="ml-auto navbar-nav">
                        <li class="nav-item d-lg-none">
                            <a href="/" class="nav-link">Home</a>
                        </li>
                        <li class="nav-item d-none d-lg-block">
                            <a href="https://github.com/g3rv4/Traducir" target="_blank" class="nav-link">Source Code</a>
                        </li>
                        <li class="nav-item dropdown d-none d-lg-block">
                            <a href="#" class="dropdown-toggle nav-link" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                                Database
                            </a>
                            <div tabindex="-1" role="menu" aria-hidden="true" class="dropdown-menu dropdown-menu-right">
                                <a class="dropdown-item" href="/backups">Download Backups</a>
                            </div>
                        </li>
                        @if (viewModel.IsLoggedIn)
                        {
                            <li class="nav-item dropdown">
                                <a href="#" class="dropdown-toggle nav-link" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
                                    My account
                                </a>
                                <div tabindex="-1" role="menu" aria-hidden="true" class="dropdown-menu dropdown-menu-right">
                                    <a class="dropdown-item" href="@Url.Action("SuggestionsByUser", "Users", new { userId = viewModel.UserId})">My Suggestions</a>
                                    <a class="dropdown-item" href="@Url.Action("Notifications", "Users")">My Notifications</a>
                                </div>
                            </li>
                        }
                        <li class="nav-item">
                            <a href="/users" class="nav-link">Users</a>
                        </li>
                        <li class="nav-item">
                            @if (viewModel.IsLoggedIn)
                            {
                                <a href="/logout" class="nav-link js-add-return-url">Log out</a>
                            }
                            else
                            {
                                <a href="/login" class="nav-link js-add-return-url">Log in!</a>
                            }
                        </li>
                    </ul>
                </div>
            </div>
        </nav>

        @RenderBody()
    </div>

    <script src="~/lib/bootstrap-native/js/bootstrap-native-v4.min.js" asp-append-version="true" use-static-domain></script>
    <script src="~/lib/require.2.3.6.min.js" asp-append-version="true" use-static-domain></script>
    <script src="~/js/dist/app.js" asp-append-version="true" use-static-domain></script>
    <script>
        require(["app"], app => {
            app.init('@GetAntiXsrfRequestToken()');
        });
    </script>
    @RenderSection("Scripts", required: false)
</body>
</html>


<|start_filename|>Traducir.Core/Models/Enums/NotificationInterval.cs<|end_filename|>
using System.ComponentModel.DataAnnotations;

namespace Traducir.Core.Models.Enums
{
    public enum NotificationInterval
    {
        [Display(Name="minutes", Order = 2)]
        Minutes = 1,

        [Display(Name = "hours", Order = 1)]
        Hours = 60,

        [Display(Name = "days", Order = 0)]
        Days = 1440
    }
}

<|start_filename|>Traducir.Core/Models/NotificationSettings.cs<|end_filename|>
using System.ComponentModel.DataAnnotations;
using Traducir.Core.Models.Enums;

namespace Traducir.Core.Models
{
    public class NotificationSettings
    {
        [Display(Name = "Urgent Strings", Order = 0)]
        public bool NotifyUrgentStrings { get; set; }

        [Display(Name = "Suggestions awaiting approval", Order = 1)]
        public bool NotifySuggestionsAwaitingApproval { get; set; }

        [Display(Name = "Suggestions awaiting review", Order = 2)]
        public bool NotifySuggestionsAwaitingReview { get; set; }

        [Display(Name = "Strings pushed to Transifex", Order = 3)]
        public bool NotifyStringsPushedToTransifex { get; set; }

        [Display(Name = "Suggestion approved", Order = 4)]
        public bool NotifySuggestionsApproved { get; set; }

        [Display(Name = "Suggestion rejected", Order = 5)]
        public bool NotifySuggestionsRejected { get; set; }

        [Display(Name = "Suggestion reviewed", Order = 6)]
        public bool NotifySuggestionsReviewed { get; set; }

        [Display(Name = "Suggestion overriden", Order = 7)]
        public bool NotifySuggestionsOverriden { get; set; }

        public NotificationInterval NotificationsInterval { get; set; }

        public int NotificationsIntervalValue { get; set; }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/StringSummaryViewModel.cs<|end_filename|>
using System.Linq;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels.Home
{
    public class StringSummaryViewModel
    {
        public SOString String { get; set; }

        public int ApprovedSuggestionsCount => String.Suggestions?.Count(s => s.State == StringSuggestionState.ApprovedByTrustedUser) ?? 0;

        public int PendingSuggestionsCount => String.Suggestions?.Count(s => s.State == StringSuggestionState.Created) ?? 0;

        public bool UserCanManageIgnoring { get; set; }

        public bool RenderAsChanged { get; set; }
    }
}


<|start_filename|>Traducir.Migrations/Migrations/004 - AddStringHistoryEnum.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(4, "Add the string history types enum")]
    public class AddStringHistoryEnum : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If dbo.fnTableExists('StringHistoryTypes') = 0
Begin
  Create Table dbo.StringHistoryTypes
  (
    Id TinyInt Not Null,
    Name VarChar(100) Not Null,
    Description VarChar(255) Not Null

    Constraint PK_StringHistoryTypes Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

-- Insert the current types

If Not Exists (Select 1 From StringHistoryTypes Where Id = 1)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (1, 'Created', 'The string was imported for the first time')
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 2)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (2, 'Updated', 'The string was updated from Transifex')
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 3)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (3, 'Deleted', 'The string was deleted from Transifex')
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 4)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (4, 'Undeleted', 'The string came back to life on Transifex')
End

-- ready for the FK!
If dbo.fnConstraintExists('StringHistory', 'FK_StringHistory_StringHistoryTypes') = 0
Begin
      Alter Table StringHistory With Check Add Constraint [FK_StringHistory_StringHistoryTypes]
        Foreign Key (HistoryTypeId)
        References StringHistoryTypes (Id);
End
");
        }
    }
}

<|start_filename|>Traducir.Migrations/Migrations/011 - AddNotifications.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(11, "Add Notifications")]
    public class AddNotifications : Migration
    {
        protected override void Up()
        {
            Execute(@"
If dbo.fnColumnExists('Users', 'NextNotificationUrgentStrings') = 0
Begin
    Alter Table Users Add NextNotificationUrgentStrings DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsAwaitingApproval') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsAwaitingApproval DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsAwaitingReview') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsAwaitingReview DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationStringsPushedToTransifex') = 0
Begin
    Alter Table Users Add NextNotificationStringsPushedToTransifex DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsApproved') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsApproved DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsRejected') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsRejected DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsReviewed') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsReviewed DateTime Null;
End

If dbo.fnColumnExists('Users', 'NextNotificationSuggestionsOverriden') = 0
Begin
    Alter Table Users Add NextNotificationSuggestionsOverriden DateTime Null;
End

If dbo.fnColumnExists('Users', 'NotificationDetails') = 0
Begin
    Alter Table Users Add NotificationDetails VarChar(Max) Null;
End

If dbo.fnColumnExists('Users', 'NotificationsIntervalId') = 0
Begin
    Alter Table Users Add NotificationsIntervalId SmallInt Not Null Default(1440); -- days
End

If dbo.fnColumnExists('Users', 'NotificationsIntervalValue') = 0
Begin
    Alter Table Users Add NotificationsIntervalValue SmallInt Not Null Default(7);
End

If dbo.fnColumnExists('Users', 'NotificationsIntervalMinutes') = 0
Begin
    Alter Table Users Add NotificationsIntervalMinutes Int Not Null Default(10080); -- 7 days (the default)
End
");
        }

        protected override void Down()
        {
            throw new System.NotImplementedException();
        }
    }
}

<|start_filename|>Traducir.Core/Models/Services/UserModels.cs<|end_filename|>
using System;
using System.Collections.Immutable;
using Traducir.Core.Helpers;

namespace Traducir.Core.Models.Services
{
    public class PushNotificationMessage
    {
        public PushNotificationMessage(string title, string url, string content, string topic = null, bool requireInteraction = false, ImmutableArray<Action> actions = default(ImmutableArray<Action>))
        {
            if (!title.HasValue() || !content.HasValue())
            {
                throw new ArgumentException("title and content need a value");
            }

            Title = title;
            Url = url;
            Topic = topic;
            Content = content;
            RequireInteraction = requireInteraction;
            Actions = actions.IsDefault ? ImmutableArray<Action>.Empty : actions;
        }

        public string Title { get; }

        public string Topic { get; }

        public string Url { get; }

        public string Content { get; }

        public bool RequireInteraction { get; }

        public ImmutableArray<Action> Actions { get; }

        public class Action
        {
            public Action(string title, string url)
            {
                Title = title;
                Url = url;
            }

            public string Title { get; }

            public string Url { get; }
        }
    }
}

<|start_filename|>Traducir.Web/Views/_ViewImports.cshtml<|end_filename|>
﻿@using StackExchange.Profiling
@using Traducir.Web
@using Traducir.Web.ViewModels
@addTagHelper *, MiniProfiler.AspNetCore.Mvc
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers
@addTagHelper *, Traducir.Web


<|start_filename|>Traducir.Web/Controllers/AdminController.cs<|end_filename|>
using System;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using SimpleMigrations;
using SimpleMigrations.DatabaseProvider;
using StackExchange.Exceptional;
using Traducir.Core.Models.Enums;
using Traducir.Core.Services;

namespace Traducir.Web.Controllers
{
    public class AdminController : Controller
    {
        private readonly ITransifexService _transifexService;
        private readonly ISOStringService _soStringService;
        private readonly INotificationService _notificationService;
        private readonly IUserService _userService;
        private readonly IConfiguration _configuration;

        public AdminController(ITransifexService transifexService,
                               ISOStringService soStringService,
                               INotificationService notificationService,
                               IUserService userService,
                               IConfiguration configuration)
        {
            _transifexService = transifexService;
            _soStringService = soStringService;
            _notificationService = notificationService;
            _userService = userService;
            _configuration = configuration;
        }

        [Route("admin/throw")]
        public static IActionResult Throw()
        {
            throw new InvalidOperationException();
        }

        [Route("admin/pull")]
        public async Task<IActionResult> PullStrings()
        {
            var strings = await _transifexService.GetStringsFromTransifexAsync();
            await _soStringService.StoreNewStringsAsync(strings);
            return NoContent();
        }

        [Route("admin/update-translations-from-so-db")]
        public async Task<IActionResult> UpdateTranslationsFromSODB(bool? overrideExisting = null)
        {
            await _soStringService.UpdateTranslationsFromSODB(overrideExisting ?? false);
            return NoContent();
        }

#if !RISKY
        [Route("admin/push")]
        public async Task<IActionResult> PushStrings()
        {
            var stringsToPush = await _soStringService.GetStringsAsync(s => s.HasTranslation, includeEverything: true);
            if (stringsToPush.Length > 0)
            {
                var sendNotifications = (await _soStringService.CountStringsAsync(s => s.NeedsPush)) > 0;
                await _transifexService.PushStringsToTransifexAsync(stringsToPush);

                if (sendNotifications)
                {
                    await _userService.SendBatchNotifications(NotificationType.StringsPushedToTransifex, null);
                }
            }

            return NoContent();
        }
#endif

        [Route("admin/generate-notifications")]
        public async Task<IActionResult> GenerateNotifications()
        {
            await _notificationService.SendStateNotifications(Request.Host.ToString());
            return NoContent();
        }

        [Route("admin/migrate")]
        public IActionResult Migrate()
        {
            var migrationsAssembly = typeof(Migrations.Program).Assembly;
            using (var db = new SqlConnection(_configuration.GetValue<string>("CONNECTION_STRING")))
            {
                var databaseProvider = new MssqlDatabaseProvider(db);
                var migrator = new SimpleMigrator(migrationsAssembly, databaseProvider);

                migrator.Load();
                migrator.MigrateToLatest();
            }

            return NoContent();
        }

        [Route("errors/{path?}/{subPath?}")]
        public async Task Exceptions() => await ExceptionalMiddleware.HandleRequestAsync(HttpContext);
    }
}

<|start_filename|>Traducir.Core/Services/NotificationService.cs<|end_filename|>
using System;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using Microsoft.Extensions.Configuration;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;

namespace Traducir.Core.Services
{
    public interface INotificationService
    {
        Task SendStateNotifications(string host);
    }

    public class NotificationService : INotificationService
    {
        private readonly IDbService _dbService;
        private readonly IUserService _userService;
        private IConfiguration _configuration;

        public NotificationService(IDbService dbService, IUserService userService, IConfiguration configuration)
        {
            _dbService = dbService;
            _userService = userService;
            _configuration = configuration;
        }

        public async Task SendStateNotifications(string host)
        {
            const string sql = @"
Select Count(1)
From   Strings
Where  IsUrgent = 1
And    DeletionDate Is Null;

Select   ss.StateId, Count(1)
From     StringSuggestions ss
Join     Strings s on s.Id = ss.StringId
Where    s.DeletionDate Is Null
Group By StateId";

            using (var db = _dbService.GetConnection())
            {
                using (var reader = await db.QueryMultipleAsync(sql))
                {
                    var urgentStrings = await reader.ReadFirstAsync<int>();
                    var suggestionCounts = (await reader.ReadAsync<(StringSuggestionState state, int count)>()).ToDictionary(e => e.state, e => e.count);
                    NotificationType type;

                    if (urgentStrings > 0)
                    {
                        type = NotificationType.UrgentStrings;
                        await _userService.SendBatchNotifications(type, host, urgentStrings);
                    }

                    if (suggestionCounts.TryGetValue(StringSuggestionState.Created, out var count))
                    {
                        type = NotificationType.SuggestionsAwaitingApproval;
                        await _userService.SendBatchNotifications(type, host, count);
                    }

                    if (suggestionCounts.TryGetValue(StringSuggestionState.ApprovedByTrustedUser, out count))
                    {
                        type = NotificationType.SuggestionsAwaitingReview;
                        await _userService.SendBatchNotifications(type, host, count);
                    }
                }
            }
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Users/NotificationsViewModel.cs<|end_filename|>
using System.ComponentModel.DataAnnotations;
using System.Globalization;
using System.Linq;
using System.Reflection;
using Traducir.Core.Models;

namespace Traducir.Web.ViewModels.Users
{
    public class NotificationsViewModel
    {
        public NotificationSettings NotificationSettings { get; set; }

        public string VapidPublic { get; set; }

        public static string ClassFor(bool value) =>
            $"notification-type list-group-item {(value ? "active" : null)}";

        public NotificationProperty[] NotificationProperties(int group)
        {
            var minOrder = group == 0 ? 0 : 4;
            var maxOrder = minOrder + 3;
            return typeof(NotificationSettings)
                .GetProperties()
                .Select(p =>
                {
                    var attribute = p.GetCustomAttributes<DisplayAttribute>().SingleOrDefault();
                    if (attribute == null || attribute.Order < minOrder || attribute.Order > maxOrder)
                    {
                        return null;
                    }
                    return new NotificationProperty
                    {
                        DataName = char.ToLower(p.Name[0], CultureInfo.InvariantCulture) + p.Name.Substring(1),
                        DisplayName = attribute.Name,
                        Value = (bool)p.GetValue(NotificationSettings, null),
                        Order = attribute.Order
                    };
                })
                .Where(n => n != null)
                .OrderBy(n => n.Order)
                .ToArray();
        }

        public class NotificationProperty
        {
            public string DataName { get; set; }

            public string DisplayName { get; set; }

            public bool Value { get; set; }

            public int Order { get; set; }
        }
    }
}


<|start_filename|>Traducir.Migrations/Migrations/003 - AddEnums.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(3, "Add enums to the database")]
    public class AddEnums : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If dbo.fnTableExists('StringSuggestionStates') = 0
Begin
  Create Table dbo.StringSuggestionStates
  (
    Id TinyInt Not Null,
    Name VarChar(100) Not Null,
    Description VarChar(255) Not Null

    Constraint PK_StringSuggestionStates Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

If dbo.fnTableExists('StringSuggestionHistoryTypes') = 0
Begin
  Create Table dbo.StringSuggestionHistoryTypes
  (
    Id TinyInt Not Null,
    Name VarChar(100) Not Null,
    Description VarChar(255) Not Null

    Constraint PK_StringSuggestionHistoryTypes Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

-- Insert the current states

If Not Exists (Select 1 From StringSuggestionStates Where Id = 1)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (1, 'Created', 'The suggestion is waiting for review')
End

If Not Exists (Select 1 From StringSuggestionStates Where Id = 2)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (2, 'ApprovedByTrustedUser', 'The suggestion is approved by a trusted user and waiting for a reviewer')
End

If Not Exists (Select 1 From StringSuggestionStates Where Id = 3)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (3, 'ApprovedByReviewer', 'The suggestion is approved and used as a translation')
End

If Not Exists (Select 1 From StringSuggestionStates Where Id = 4)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (4, 'Rejected', 'The suggestion is rejected. Nobody will ever discover it again')
End

If Not Exists (Select 1 From StringSuggestionStates Where Id = 5)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (5, 'DeletedByOwner', 'The suggestion was deleted by its owner')
End

If Not Exists (Select 1 From StringSuggestionStates Where Id = 6)
Begin
    Insert Into StringSuggestionStates (Id, Name, Description)
    values (6, 'DismissedByOtherString', 'The suggestion was dismissed by the approval of other suggestion')
End

-- ready for the FK!
If dbo.fnConstraintExists('StringSuggestions', 'FK_StringSuggestions_StringSuggestionStates') = 0
Begin
      Alter Table StringSuggestions With Check Add Constraint [FK_StringSuggestions_StringSuggestionStates]
        Foreign Key (StateId)
        References StringSuggestionStates (Id);
End

-- insert the current history types
If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 1)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (1, 'Created', 'The suggestion was created')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 2)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (2, 'ApprovedByTrusted', 'The suggestion was approved by a trusted user')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 3)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (3, 'ApprovedByReviewer', 'The suggestion was approved by a reviewer')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 4)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (4, 'RejectedByTrusted', 'The suggestion was rejected by a trusted user')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 5)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (5, 'RejectedByReviewer', 'The suggestion was rejected by a reviewer')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 6)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (6, 'DeletedByOwner', 'The suggestion was deleted by its owner')
End

If Not Exists (Select 1 From StringSuggestionHistoryTypes Where Id = 7)
Begin
    Insert Into StringSuggestionHistoryTypes (Id, Name, Description)
    values (7, 'DismissedByOtherString', 'The suggestion was dismissed because other suggestion was approved')
End

-- ready for the FK!
If dbo.fnConstraintExists('StringSuggestionHistory', 'FK_StringSuggestionHistory_StringSuggestionHistoryTypes') = 0
Begin
      Alter Table StringSuggestionHistory With Check Add Constraint [FK_StringSuggestionHistory_StringSuggestionHistoryTypes]
        Foreign Key (HistoryTypeId)
        References StringSuggestionHistoryTypes (Id);
End
");
        }
    }
}

<|start_filename|>Traducir.Core/Models/Enums/UserType.cs<|end_filename|>
namespace Traducir.Core.Models.Enums
{
    public enum UserType
    {
        Unknown = 0,
        Banned = 1,
        User = 2,
        TrustedUser = 3,
        Reviewer = 4
    }
}

<|start_filename|>Traducir.Migrations/Migrations/010 - AdjustAnotherColumnSize.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(10, "Adjust StringSuggestionHistory Comment size")]
    public class AdjustAnotherColumnSize : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute("Alter Table StringSuggestionHistory Alter Column Comment NVarChar(Max) Null");
        }
    }
}

<|start_filename|>Traducir.Migrations/Migrations/007 - UserHistory.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(7, "Add UserHistory and friends")]
    public class UserHistory : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If dbo.fnTableExists('UserHistoryTypes') = 0
Begin
  Create Table dbo.UserHistoryTypes
  (
    Id TinyInt Not Null,
    Name VarChar(100) Not Null,
    Description VarChar(255) Not Null

    Constraint PK_UserHistoryTypes Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

If Not Exists (Select 1 From UserHistoryTypes Where Id = 1)
Begin
    Insert Into UserHistoryTypes (Id, Name, Description)
    values (1, 'Banned', 'The user was banned')
End

If Not Exists (Select 1 From UserHistoryTypes Where Id = 2)
Begin
    Insert Into UserHistoryTypes (Id, Name, Description)
    values (2, 'BanLifted', 'The user ban was lifted')
End

If Not Exists (Select 1 From UserHistoryTypes Where Id = 3)
Begin
    Insert Into UserHistoryTypes (Id, Name, Description)
    values (3, 'MadeTrustedUser', 'The user was made trusted user')
End

If Not Exists (Select 1 From UserHistoryTypes Where Id = 4)
Begin
    Insert Into UserHistoryTypes (Id, Name, Description)
    values (4, 'DemotedToRegularUser', 'The trusted user was made a regular user')
End

If dbo.fnTableExists('UserHistory') = 0
Begin
  Create Table dbo.UserHistory
  (
    Id Int Not Null Identity (1, 1),
    UserId Int Not Null Constraint FK_UserHistory_User Foreign Key References Users (Id),
    HistoryTypeId TinyInt Not Null Constraint FK_UserHistory_UserHistoryType Foreign Key References UserHistoryTypes (Id),
    Comment NVarChar(100) Null,
    UpdatedById Int Null Constraint FK_UserHistory_UpdatedById Foreign Key References Users (Id),
    CreationDate datetime Not Null

    Constraint PK_UserHistory Primary Key Clustered (Id Asc)
  )
  On [Primary]
End");
        }
    }
}

<|start_filename|>Traducir.Web/wwwroot/css/site.css<|end_filename|>
body {
    padding-top: 54px;
}

@media (min-width: 992px) {
    body {
        padding-top: 56px;
    }
}

.w-95 {
    width: 95% !important;
}

.modal-dialog:not(.alert-modal) .modal-content {
    overflow: scroll;
}

.string-list tr td:first-of-type {
    cursor: pointer;
}

.notification-type {
    cursor: pointer;
}

.sorry-no-push {
    text-align: center;
    margin-top: 100px;
}


<|start_filename|>Traducir.Web/ViewModels/Home/QueryViewModel.cs<|end_filename|>
using System.ComponentModel.DataAnnotations;

namespace Traducir.Web.ViewModels.Home
{
#pragma warning disable CA1717
    public enum SuggestionApprovalStatus
    {
        [Display(Name = "Any string", Order = 0)]
        AnyStatus = 0,

        [Display(Name = "Strings without suggestions", Order = 1)]
        DoesNotHaveSuggestions = 1,

        [Display(Name = "Strings with suggestions awaiting review", Order = 3)]
        HasSuggestionsNeedingReview = 2,

        [Display(Name = "Strings with suggestions awaiting approval", Order = 2)]
        HasSuggestionsNeedingApproval = 3,

        [Display(Name = "Strings with approved suggestions awaiting review", Order = 4)]
        HasSuggestionsNeedingReviewApprovedByTrustedUser = 4
    }

    public enum TranslationStatus
    {
        [Display(Name = "Any string", Order = 0)]
        AnyStatus = 0,

        [Display(Name = "Only strings with translation", Order = 2)]
        WithTranslation = 1,

        [Display(Name = "Only strings without translation", Order = 1)]
        WithoutTranslation = 2
    }

    public enum PushStatus
    {
        [Display(Name = "Any status", Order = 0)]
        AnyStatus = 0,

        [Display(Name = "Need push", Order = 1)]
        NeedsPush = 1,

        [Display(Name = "Don't need push", Order = 2)]
        DoesNotNeedPush = 2
    }

    public enum UrgencyStatus
    {
        [Display(Name = "Any string", Order = 0)]
        AnyStatus = 0,

        [Display(Name = "Is urgent", Order = 1)]
        IsUrgent = 1,

        [Display(Name = "Is not urgent", Order = 2)]
        IsNotUrgent = 2
    }

    public enum IgnoredStatus
    {
        [Display(Name = "Hide ignored", Order = 0)]
        AvoidIgnored = 0,

        [Display(Name = "Ignored only", Order = 1)]
        OnlyIgnored = 1,

        [Display(Name = "Ignored an not ignored", Order = 2)]
        IncludeIgnored = 2
    }
#pragma warning restore CA1717

    public class QueryViewModel
    {
        public static QueryViewModel Empty => new QueryViewModel();

        public bool IsEmpty =>
            string.IsNullOrEmpty(SourceRegex) &&
            string.IsNullOrEmpty(TranslationRegex) &&
            string.IsNullOrEmpty(Key) &&
            TranslationStatus == TranslationStatus.AnyStatus &&
            SuggestionsStatus == SuggestionApprovalStatus.AnyStatus &&
            PushStatus == PushStatus.AnyStatus &&
            UrgencyStatus == UrgencyStatus.AnyStatus &&
            IgnoredStatus == IgnoredStatus.AvoidIgnored;

        public string SourceRegex { get; set; }

        public string TranslationRegex { get; set; }

        public string Key { get; set; }

        public TranslationStatus TranslationStatus { get; set; }

        public SuggestionApprovalStatus SuggestionsStatus { get; set; }

        public PushStatus PushStatus { get; set; }

        public UrgencyStatus UrgencyStatus { get; set; }

        public IgnoredStatus IgnoredStatus { get; set; }
    }
}

<|start_filename|>Traducir.Web/ViewModels/LayoutViewModel.cs<|end_filename|>
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Configuration;
using Traducir.Core.Helpers;
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels
{
    public class LayoutViewModel
    {
        public LayoutViewModel(IConfiguration configuration, IHttpContextAccessor httpContextAccessor)
        {
            var httpContext = httpContextAccessor.HttpContext;
            var user = httpContext.User;

            ConfigName = configuration.GetValue<string>("FRIENDLY_NAME");
            CurrentPathAndQuery = httpContext.Request.Path + httpContext.Request.QueryString;
            var userName = user.GetClaim<string>(ClaimType.Name);
            IsLoggedIn = userName != null;

            if (IsLoggedIn)
            {
                UserId = user.GetClaim<int>(ClaimType.Id);
                var userType = user.GetClaim<UserType>(ClaimType.UserType);
                var userIsModerator = user.GetClaim<string>(ClaimType.IsModerator) == "1";
                var userTypeForDisplay = userType.ToDisplayString();
                UserInfo = $"{userName} ({userTypeForDisplay}) {(userIsModerator ? "♦" : null)}";
            }
            else
            {
                UserInfo = null;
            }
        }

        public string ConfigName { get; }

        public int UserId { get; }

        public bool IsLoggedIn { get; }

        public string CurrentPathAndQuery { get; }

        public string UserInfo { get; }
    }
}


<|start_filename|>Traducir.Migrations/Migrations/006 - UrgentStrings.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(6, "Strings can now be marked as urgent")]
    public class UrgentStrings : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If dbo.fnColumnExists('Strings', 'IsUrgent') = 0
Begin
    Alter Table Strings Add IsUrgent Bit Not Null Default(0)
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 6)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (6, 'MadeUrgent', 'The string was marked as urgent')
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 7)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (7, 'MadeNotUrgent', 'The string was marked as not urgent')
End
");
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/FilterResultsViewModel.cs<|end_filename|>
using System.Collections.Generic;
using System.Linq;
using Traducir.Core.Models;

namespace Traducir.Web.ViewModels.Home
{
    public class FilterResultsViewModel
    {
        private FilterResultsViewModel()
        {
        }

        public FilterResultsViewModel(IEnumerable<SOString> strings, bool userCanManageIgnoring)
        {
            StringSummaries = strings.Select(str => new StringSummaryViewModel { String = str, UserCanManageIgnoring = userCanManageIgnoring });
            Count = strings.Count();
        }

        public IEnumerable<StringSummaryViewModel> StringSummaries { get; }

        public int Count { get; }
    }
}


<|start_filename|>Traducir.Web/Views/Home/StringSummary.cshtml<|end_filename|>
@model Traducir.Web.ViewModels.Home.StringSummaryViewModel

@{
    var approvedCount = Model.ApprovedSuggestionsCount;
    var pendingCount = Model.PendingSuggestionsCount;
}

<tr data-string-id="@Model.String.Id" class="js-string-summary @(Model.String.IsUrgent ? "table-danger" : Model.RenderAsChanged ? "table-success" : null)">
    <td>@Model.String.OriginalString</td>
    <td>
        @if (Model.String.Translation != null)
        {
            <text>@Model.String.Translation</text>
        }
        else if (Model.UserCanManageIgnoring)
        {
            if (Model.String.IsIgnored)
            {
                <button type="button" class="btn btn-warning btn-sm" data-string-action="manage-ignore" data-string-action-argument="stop-ignoring">Stop ignoring</button>
            }
            else
            {
                <button type="button" class="btn btn-warning btn-sm" data-string-action="manage-ignore" data-string-action-argument="ignore">Ignore!</button>
            }
        }
    </td>
    <td>
        @if (approvedCount > 0)
        {
            <span class="text-success">@approvedCount</span>
        }
        @if (approvedCount > 0 && pendingCount > 0)
        {
            <span> - </span>
        }
        @if (pendingCount > 0)
        {
            <span class="text-danger">@pendingCount</span>
        }
    </td>
</tr>

<|start_filename|>Traducir.Core/Models/Enums/StringHistoryType.cs<|end_filename|>
namespace Traducir.Core.Models.Enums
{
    public enum StringHistoryType : int
    {
        Created = 1,
        Updated = 2,
        Deleted = 3,
        Undeleted = 4,
        TranslationUpdatedFromDump = 5,
        MadeUrgent = 6,
        MadeNotUrgent = 7,
        Ignored = 8,
        UnIgnored = 9,
    }
}

<|start_filename|>Traducir.Web/Controllers/AccountController.cs<|end_filename|>
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Security.Claims;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authentication;
using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Hosting;
using Traducir.Core.Helpers;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;
using Traducir.Core.Services;

namespace Traducir.Web.Controllers
{
    public class AccountController : Controller
    {
        private readonly ISEApiService _seApiService;
        private readonly IConfiguration _configuration;
        private readonly IUserService _userService;
        private readonly IAuthorizationService _authorizationService;
        private readonly bool _isDevelopmentEnvironment;

        public AccountController(
            IConfiguration configuration,
            ISEApiService seApiService,
            IUserService userService,
            IAuthorizationService authorizationService,
            IWebHostEnvironment hostingEnvironment)
        {
            _seApiService = seApiService;
            _configuration = configuration;
            _userService = userService;
            _authorizationService = authorizationService;
            _isDevelopmentEnvironment = hostingEnvironment.IsDevelopment();
        }

        [Route("login")]
        public IActionResult LogIn(string returnUrl)
        {
            return Redirect(_seApiService.GetInitialOauthUrl(GetOauthReturnUrl(), returnUrl));
        }

        [Route("logout")]
        public async Task<IActionResult> LogOut(string returnUrl = null)
        {
            await HttpContext.SignOutAsync();
            return Redirect(returnUrl ?? "/");
        }

        [Route("oauth-callback")]
        public async Task<IActionResult> OauthCallback(string code, string state = null)
        {
            string returnUrl = state;
            var siteDomain = _configuration.GetValue<string>("STACKAPP_SITEDOMAIN");

            var accessToken = await _seApiService.GetAccessTokenFromCodeAsync(code, GetOauthReturnUrl());
            var currentUser = await _seApiService.GetMyUserAsync(siteDomain, accessToken);

            if (currentUser == null)
            {
                return Content("Could not retrieve a user account on " + siteDomain);
            }

            var minRep = _configuration.GetValue<int>("MIN_REP_TO_LOGIN");
            if (currentUser.Reputation < minRep)
            {
                return Content($"You need at least {minRep} to log in");
            }

            await _userService.UpsertUserAsync(new User
            {
                Id = currentUser.UserId,
                DisplayName = currentUser.DisplayName,
                IsModerator = currentUser.UserType == "moderator",
                CreationDate = DateTime.UtcNow,
                LastSeenDate = DateTime.UtcNow
            });

            await LoginUser(currentUser.UserId);

            return Redirect(returnUrl ?? "/");
        }

#if RISKY
        [Route("impersonate/{userId}")]
        public async Task<IActionResult> Impersonate(int userId)
        {
            await LoginUser(userId);
            return Redirect("/");
        }
#endif

        private async Task LoginUser(int userId)
        {
            var user = await _userService.GetUserAsync(userId);

            var claims = new List<Claim>
            {
                new Claim(ClaimType.Id, user.Id.ToString(CultureInfo.InvariantCulture)),
                new Claim(ClaimType.Name, user.DisplayName),
                new Claim(ClaimType.UserType, user.UserType.ToString()),
            };
            if (user.UserType >= UserType.User)
            {
                claims.Add(new Claim(ClaimType.CanSuggest, "1"));
                if (user.UserType >= UserType.TrustedUser)
                {
                    claims.Add(new Claim(ClaimType.CanReview, "1"));
                }
            }

            if (user.IsModerator)
            {
                claims.Add(new Claim(ClaimType.IsModerator, "1"));
            }

            var identity = new ClaimsIdentity(claims, "login");

            await HttpContext.SignInAsync(
                CookieAuthenticationDefaults.AuthenticationScheme,
                new ClaimsPrincipal(identity));
        }

        private string GetOauthReturnUrl() =>
            Url.Action("OauthCallback", null, null, "https");
    }
}

<|start_filename|>Traducir.Core/Models/WebPushSubscription.cs<|end_filename|>
namespace Traducir.Core.Models
{
    public class WebPushSubscription
    {
        public string Endpoint { get; set; }

        public string Auth { get; set; }

        public string P256dh { get; set; }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/EditStringViewModel.cs<|end_filename|>
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels.Home
{
    public class EditStringViewModel
    {
        public string SiteDomain { get; set; }

        public string TransifexPath { get; set; }

        public bool UserIsLoggedIn { get; set; }

        public int UserId { get; set; }

        public UserType UserType { get; set; }

        public bool UserCanSuggest { get; set; }

        public bool UserCanReview { get; set; }

        public SOString String { get; set; }

        public bool CanSendFinalTranslation => UserType == UserType.Reviewer;

        public bool CurrentUserSuggested(SOStringSuggestion suggestion) =>
            UserIsLoggedIn && suggestion.CreatedById == UserId;

        public bool MustRenderSuggestionActionsFor(SOStringSuggestion suggestion)
        {
            if (!UserIsLoggedIn || !UserCanReview)
            {
                return false;
            }

            // a trusted user can't act on a suggestion approved by a trusted user
            return !(suggestion.State == StringSuggestionState.ApprovedByTrustedUser && UserType == UserType.TrustedUser);
        }
    }
}


<|start_filename|>Traducir.Web/ViewModels/Users/ChangeUserTypeViewModel.cs<|end_filename|>
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels.Users
{
    public class ChangeUserTypeViewModel
    {
        public int UserId { get; set; }

        public UserType UserType { get; set; }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/DeleteSuggestionViewModel.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Home
{
    public class DeleteSuggestionViewModel
    {
        public int SuggestionId { get; set; }
    }
}

<|start_filename|>Traducir.Core/Models/SODBString.cs<|end_filename|>
using Traducir.Core.Helpers;

namespace Traducir.Core.Models
{
    public class SODBString
    {
        public int LCID { get; set; }

        public string Hash { get; set; }

        public string NormalizedHash => Hash.ToNormalizedKey();

        public string EffectiveTranslation { get; set; }
    }
}

<|start_filename|>Traducir.Core/Services/SOStringService.cs<|end_filename|>
using System;
using System.Collections.Generic;
using System.Collections.Immutable;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Threading.Tasks;
using CsvHelper;
using Dapper;
using Microsoft.Extensions.Configuration;
using StackExchange.Profiling;
using Traducir.Core.Helpers;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;
using Traducir.Core.Models.Services;

namespace Traducir.Core.Services
{
    public interface ISOStringService
    {
        Task StoreNewStringsAsync(ImmutableArray<TransifexString> strings);

        Task<SOString> GetStringByIdAsync(int stringId);

        Task<ImmutableArray<SOString>> GetStringsAsync(Func<SOString, bool> predicate = null, bool includeEverything = false);

        Task<int> CountStringsAsync(Func<SOString, bool> predicate = null);

        Task<bool> CreateSuggestionAsync(int stringId, string suggestion, int userId, UserType userType, bool approve);

        Task<bool> ReviewSuggestionAsync(int suggestionId, bool approve, int userId, UserType userType, string host);

        Task UpdateStringsPushed();

        Task UpdateTranslationsFromSODB(bool overrideExisting);

        Task<bool> ManageUrgencyAsync(int stringId, bool isUrgent, int userId);

        Task<bool> ManageIgnoreAsync(int stringId, bool isIgnored, int userId, UserType userType);

        Task<bool> DeleteSuggestionAsync(int suggestionId, int userId);

        Task<ImmutableArray<SOStringSuggestion>> GetSuggestionsByUser(int userId, StringSuggestionState? state);

        Task<bool> ReplaceSuggestionAsync(int suggestionId, string suggestion, int userId);

        Task<int> GetStringIdBySuggestionId(int suggestionId);
    }

    public class SOStringService : ISOStringService
    {
        private readonly IDbService _dbService;
        private readonly IUserService _userService;
        private readonly IConfiguration _configuration;

        public SOStringService(IDbService dbService, IUserService userService, IConfiguration configuration)
        {
            _dbService = dbService;
            _userService = userService;
            _configuration = configuration;
        }

        private ImmutableArray<SOString> Strings { get; set; }

        private Dictionary<int, SOString> StringsById { get; set; }

        public async Task StoreNewStringsAsync(ImmutableArray<TransifexString> strings)
        {
            using (var db = _dbService.GetConnection())
            {
                await CreateTemporaryTable(db);
                using (MiniProfiler.Current.Step("Populate temp table"))
                {
                    var table = new DataTable();
                    table.Columns.Add("NormalizedKey", typeof(string));
                    table.Columns.Add("Key", typeof(string));
                    table.Columns.Add("Variant", typeof(string));
                    table.Columns.Add("OriginalString", typeof(string));
                    table.Columns.Add("Translation", typeof(string));

                    foreach (var s in strings)
                    {
                        table.Rows.Add(s.NormalizedKey, s.Key, s.Variant, s.Source, s.Translation);
                    }

                    var copyDb = (SqlConnection)db.WrappedConnection;
                    using (var copy = new SqlBulkCopy(copyDb))
                    {
                        copy.DestinationTableName = "dbo.ImportTable";
                        foreach (DataColumn c in table.Columns)
                        {
                            copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
                        }

                        copyDb.Open();
                        copy.WriteToServer(table);
                    }
                }

                using (MiniProfiler.Current.Step("Fixing normalized keys"))
                {
                    // once upon a time, someone used to host Traducir in an Ubuntu docker image. That same someone used
                    // OrderBy without specifying a comparer.
                    // A year later, this subject decided to use the Alpine image to host it... it's smaller, they thought
                    // it compiles and it runs... that must be safe!
                    // BUT! our hero never realized that Comparer<string>.Default (and even StringComparer.InvariantCultureIgnoreCase)
                    // order things different. So when that happened, trying to join by NormalizedKey caused this method to
                    // try to insert a bunch of already existing strings. Fortunately, we do have an index that doesn't allow two
                    // strings with the same Key.
                    // Aaanyway, as a sacrifice to the Gods (the old and the new), they were required to fix all the keys before
                    // running the pull. They initially wrote an admin route, but considering this is all in-memory and pretty fast,
                    // let's do it here every time this runs. We'll remove it. Eventually.
                    // await FixNormalizedKeysAsync(db);
                }

                using (MiniProfiler.Current.Step("Delete strings"))
                {
                    await db.ExecuteAsync(@"
Insert Into StringHistory
            (StringId, HistoryTypeId, CreationDate)
Select    s.Id, {=Deleted}, @now
From      Strings s
Left Join ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where     s.DeletionDate Is Null
And       feed.NormalizedKey Is Null;

Update    s
Set       s.DeletionDate = @now
From      Strings s
Left Join ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where     s.DeletionDate Is Null
And       feed.NormalizedKey Is Null;", new { now = DateTime.UtcNow, StringHistoryType.Deleted });
                }

                using (MiniProfiler.Current.Step("Update strings"))
                {
                    await db.ExecuteAsync(@"
Insert Into StringHistory
            (StringId, HistoryTypeId, Comment, CreationDate)
Select s.Id, {=Updated}, Concat('Key Updated from ', s.[Key], ' to ', feed.[Key]), @now
From   Strings s
Join   ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where  s.[Key] <> feed.[Key];

Update s
Set    s.[Key] = feed.[Key], s.Variant = feed.Variant
From   Strings s
Join   ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where  s.[Key] <> feed.[Key];", new { now = DateTime.UtcNow, StringHistoryType.Updated });
                }

                using (MiniProfiler.Current.Step("Undelete strings"))
                {
                    await db.ExecuteAsync(@"
Insert Into StringHistory
            (StringId, HistoryTypeId, CreationDate)
Select s.Id, {=Undeleted}, @now
From   Strings s
Join   ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where  s.DeletionDate Is Not Null;

Update s
Set    s.DeletionDate = Null
From   Strings s
Join   ImportTable feed On feed.NormalizedKey = s.NormalizedKey
Where  s.DeletionDate Is Not Null;", new { now = DateTime.UtcNow, StringHistoryType.Undeleted });
                }

                using (MiniProfiler.Current.Step("Add new strings"))
                {
                    await db.ExecuteAsync(@"
Declare @NormalizedKeysToInsert Table (
    NormalizedKey VarChar(255) Not Null
);

Insert Into @NormalizedKeysToInsert
Select    feed.NormalizedKey
From      ImportTable feed
Left Join Strings s On s.NormalizedKey = feed.NormalizedKey
Where     s.NormalizedKey Is Null;

Insert Into Strings ([Key], NormalizedKey, FamilyKey, Variant, OriginalString, Translation, CreationDate)
Select [Key], feed.NormalizedKey, Left([Key], 32), feed.Variant, feed.OriginalString, feed.Translation, @now
From   ImportTable feed
Join   @NormalizedKeysToInsert s On s.NormalizedKey = feed.NormalizedKey;

Insert Into StringHistory
            (StringId, HistoryTypeId, CreationDate)
Select s.Id, {=Created}, @now
From   @NormalizedKeysToInsert i
Join   Strings s On s.NormalizedKey = i.NormalizedKey;", new { now = DateTime.UtcNow, StringHistoryType.Created });
                }
            }

            ExpireCache();
        }

        public async Task<ImmutableArray<SOString>> GetStringsAsync(Func<SOString, bool> predicate = null, bool includeEverything = false)
        {
            if (Strings == null || Strings.Length == 0)
            {
                await RefreshCacheAsync();
            }

            using (MiniProfiler.Current.Step("Filtering the strings"))
            {
                var matching = predicate == null ? Strings : Strings.Where(predicate);
                if (!includeEverything)
                {
                    matching = matching.Take(200);
                }

                return matching.ToImmutableArray();
            }
        }

        public async Task<int> CountStringsAsync(Func<SOString, bool> predicate = null)
        {
            if (Strings == null || Strings.Length == 0)
            {
                await RefreshCacheAsync();
            }

            if (predicate == null)
            {
                return Strings.Length;
            }

            using (MiniProfiler.Current.Step("Filtering the strings"))
            {
                return Strings.Count(predicate);
            }
        }

        public async Task<SOString> GetStringByIdAsync(int stringId)
        {
            if (Strings == null || Strings.Length == 0)
            {
                await RefreshCacheAsync();
            }

            if (StringsById.TryGetValue(stringId, out var res))
            {
                return res;
            }

            return null;
        }

        public async Task<bool> CreateSuggestionAsync(int stringId, string suggestion, int userId, UserType userType, bool approve)
        {
            using (var db = _dbService.GetConnection())
            {
                try
                {
                    var initialState = userType >= UserType.TrustedUser ? StringSuggestionState.ApprovedByTrustedUser : StringSuggestionState.Created;
                    var suggestionId = await db.QuerySingleOrDefaultAsync<int?>(@"
Declare @suggestionId Int;

Insert Into StringSuggestions
            (StringId, Suggestion, StateId, CreatedById, CreationDate)
Values      (@stringId, @suggestion, @state, @userId, @now);

Select @suggestionId = Scope_Identity();

Insert Into StringSuggestionHistory
            (StringSuggestionId, HistoryTypeId, UserId, Comment, CreationDate)
Values      (@suggestionID, {=HistoryCreated}, @userId, @comment, @now);

Select @suggestionId;", new
                    {
                        stringId,
                        suggestion,
                        StringSuggestionState.Created,
                        userId,
                        state = initialState,
                        now = DateTime.UtcNow,
                        comment = initialState == StringSuggestionState.ApprovedByTrustedUser ? "Created by a trusted user" : null,
                        HistoryCreated = StringSuggestionHistoryType.Created
                    });

                    if (approve)
                    {
                        await ReviewSuggestionAsync(suggestionId.Value, true, userId, userType, null);
                    }
                }
                catch (SqlException e) when (e.Number == 547)
                {
                    return false;
                }

                await RefreshCacheAsync(stringId);
                return true;
            }
        }

        public async Task<bool> ReviewSuggestionAsync(int suggestionId, bool approve, int userId, UserType userType, string host)
        {
            if (userType != UserType.TrustedUser && userType != UserType.Reviewer)
            {
                return false;
            }

            using (var db = _dbService.GetConnection())
            {
                // is this suggestion eligible for review?
                var suggestionData = await db.QueryFirstOrDefaultAsync<(int stringId, int ownerId)>(@"
Select StringId, CreatedById
From   StringSuggestions
Where  Id = @suggestionId
And    StateId In @validStates", new
                {
                    userId,
                    suggestionId,
                    validStates = userType == UserType.Reviewer ? new[] { StringSuggestionState.Created, StringSuggestionState.ApprovedByTrustedUser } : new[] { StringSuggestionState.Created }
                });

                if (suggestionData.stringId > 0)
                {
                    StringSuggestionState newState;
                    StringSuggestionHistoryType historyType;

                    if (userType == UserType.Reviewer)
                    {
                        if (approve)
                        {
                            newState = StringSuggestionState.ApprovedByReviewer;
                            historyType = StringSuggestionHistoryType.ApprovedByReviewer;
                        }
                        else
                        {
                            newState = StringSuggestionState.Rejected;
                            historyType = StringSuggestionHistoryType.RejectedByReviewer;
                        }
                    }
                    else
                    {
                        if (approve)
                        {
                            newState = StringSuggestionState.ApprovedByTrustedUser;
                            historyType = StringSuggestionHistoryType.ApprovedByTrusted;
                        }
                        else
                        {
                            newState = StringSuggestionState.Rejected;
                            historyType = StringSuggestionHistoryType.RejectedByTrusted;
                        }
                    }

                    string sql = @"
Declare @historyId Int;
Insert Into StringSuggestionHistory
            (StringSuggestionId, HistoryTypeId, UserId, CreationDate)
Values      (@suggestionId, @historyType, @userId, @now);
Select @historyId = Scope_Identity();

Update StringSuggestions
Set    StateId = @newState,
       LastStateUpdatedDate = @now,
       LastStateUpdatedById = @userId
Where  Id = @suggestionId;";
                    if (newState == StringSuggestionState.ApprovedByReviewer)
                    {
                        sql += @"
Update h
Set    h.Comment = Concat('Previous translation: ', str.Translation)
From   StringSuggestionHistory h
Join   StringSuggestions sug On sug.Id = h.StringSuggestionId
Join   Strings str On str.Id = sug.StringId
Where  h.Id = @historyId;

Update str
Set    str.Translation = sug.Suggestion,
       str.NeedsPush = 1,
       str.IsUrgent = 0
From   Strings str
Join   StringSuggestions sug On sug.StringId = str.Id
Where  sug.Id = @suggestionId;

-- useful to avoid joins down the line
Declare @stringId Int;
Select @stringId = StringId From StringSuggestions Where Id = @suggestionId;

Insert Into StringSuggestionHistory
            (StringSuggestionId, HistoryTypeId, Comment, UserId, CreationDate)
Select Id, {=DismissedByOtherStringHistory}, Concat('Suggestion ', @suggestionId, ' approved'), @userId, @now
From   StringSuggestions
Where  StringId = @stringId
And    StateId In ({=Created}, {=ApprovedByTrustedUser});

Update StringSuggestions
Set    StateId = {=DismissedByOtherStringState}
Where  StringId = @stringId
And    StateId In ({=Created}, {=ApprovedByTrustedUser});";
                    }

                    await db.ExecuteAsync(sql, new
                    {
                        suggestionId,
                        historyType,
                        userId,
                        newState,
                        StringSuggestionState.Created,
                        StringSuggestionState.ApprovedByTrustedUser,
                        now = DateTime.UtcNow,
                        DismissedByOtherStringHistory = StringSuggestionHistoryType.DismissedByOtherString,
                        DismissedByOtherStringState = StringSuggestionState.DismissedByOtherString,
                    });

                    await RefreshCacheAsync(suggestionData.stringId);

                    var notificationType = newState.GetNotificationType();

                    if (notificationType.HasValue && userId != suggestionData.ownerId)
                    {
                        await _userService.SendNotification(suggestionData.ownerId, notificationType.Value, host);
                    }

                    return true;
                }

                return false;
            }
        }

        public async Task UpdateStringsPushed()
        {
            using (var db = _dbService.GetConnection())
            {
                var rows = await db.ExecuteAsync(@"Update Strings Set NeedsPush = 0 Where NeedsPush = 1");
                if (rows > 0)
                {
                    ExpireCache();
                }
            }
        }

        private async Task FixNormalizedKeysAsync(IDbConnection db)
        {
            var currentNormalized = await db.QueryAsync<string>(@"Select NormalizedKey From Strings Group By NormalizedKey");
            await db.ExecuteAsync(@"Update Strings Set NormalizedKey = @newNK Where NormalizedKey = @oldNK",
                currentNormalized.Select(oldNK => new { oldNK, newNk = oldNK.ToNormalizedKey() })
                                    .Where(e => e.oldNK != e.newNk));
        }

        public async Task UpdateTranslationsFromSODB(bool overrideExisting)
        {
            int? lcid = _configuration.GetValue<int?>("LCID");
            if (!lcid.HasValue)
            {
                throw new Exception("Need an LCID defined");
            }

            SODBString[] soStrings;
            using (MiniProfiler.Current.Step("Fetching data from SO"))
            using (var httpClient = new HttpClient())
            {
                var response = await httpClient.GetAsync("https://stackoverflow.com/api/translation-strings");
                response.EnsureSuccessStatusCode();

                using (var stream = await response.Content.ReadAsStreamAsync())
                using (var reader = new StreamReader(stream))
                {
                    soStrings = Jil.JSON.Deserialize<SODBString[]>(reader);
                }
            }

            using (MiniProfiler.Current.Step("Filtering out strings for other LCIDs"))
            {
                // filter the strings to the locale this site is using
                soStrings = soStrings.Where(s => s.LCID == lcid.Value).ToArray();
            }

            using (var db = _dbService.GetConnection())
            {
                using (MiniProfiler.Current.Step("Bulk inserting the data from the service"))
                {
                    await ResetDumpTable(db);
                    var table = new DataTable();
                    table.Columns.Add("Hash", typeof(string));
                    table.Columns.Add("NormalizedHash", typeof(string));
                    table.Columns.Add("Translation", typeof(string));

                    foreach (var s in soStrings)
                    {
                        table.Rows.Add(s.Hash, s.NormalizedHash, s.EffectiveTranslation);
                    }

                    var copyDb = (SqlConnection)db.WrappedConnection;
                    using (var copy = new SqlBulkCopy(copyDb))
                    {
                        copy.DestinationTableName = "dbo.SODumpTable";
                        foreach (DataColumn c in table.Columns)
                        {
                            copy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(c.ColumnName, c.ColumnName));
                        }

                        copyDb.Open();
                        copy.WriteToServer(table);
                    }
                }

                using (MiniProfiler.Current.Step("Updating the translations!"))
                {
                    await db.ExecuteAsync($@"
Insert Into StringHistory
            (StringId, HistoryTypeId, CreationDate)
Select s.Id, {{=TranslationUpdatedFromDump}}, @now
From   Strings s
Join   SODumpTable dump On dump.NormalizedHash = s.NormalizedKey
Where  s.Translation Is Null;

Update s
Set    s.Translation = dump.Translation,
       s.IsUrgent = 0
From   Strings s
Join   SODumpTable dump On dump.NormalizedHash = s.NormalizedKey
{(overrideExisting ? string.Empty : "Where  s.Translation Is Null;")}", new { now = DateTime.UtcNow, StringHistoryType.TranslationUpdatedFromDump });
                }
            }

            await RefreshCacheAsync();
        }

        public async Task<bool> ManageUrgencyAsync(int stringId, bool isUrgent, int userId)
        {
            var str = await GetStringByIdAsync(stringId);
            if (str == null)
            {
                return false;
            }

            if (str.IsUrgent == isUrgent)
            {
                return true;
            }

            using (var db = _dbService.GetConnection())
            {
                await db.ExecuteAsync(@"
Insert Into StringHistory
            (StringId, UserId, HistoryTypeId, CreationDate)
Values      (@stringId, @userId, @historyType, @now);

Update Strings
Set    IsUrgent = @isUrgent
Where  Id = @stringId", new
                {
                    stringId,
                    isUrgent,
                    userId,
                    historyType = isUrgent ? StringHistoryType.MadeUrgent : StringHistoryType.MadeNotUrgent,
                    now = DateTime.UtcNow
                });
            }

            await RefreshCacheAsync(stringId);
            return true;
        }

        public async Task<bool> ManageIgnoreAsync(int stringId, bool isIgnored, int userId, UserType userType)
        {
            var str = await GetStringByIdAsync(stringId);
            if (str == null)
            {
                return false;
            }

            if (userType < UserType.TrustedUser)
            {
                return false;
            }

            if (str.IsIgnored == isIgnored)
            {
                return true;
            }

            using (var db = _dbService.GetConnection())
            {
                await db.ExecuteAsync(@"
Insert Into StringHistory
            (StringId, UserId, HistoryTypeId, CreationDate)
Select Id, @userId, @historyType, @now
From   Strings
Where  FamilyKey = @familyKey;

Update Strings
Set    IsIgnored = @isIgnored
Where  FamilyKey = @familyKey", new
                {
                    isIgnored,
                    userId,
                    familyKey = str.FamilyKey,
                    historyType = isIgnored ? StringHistoryType.Ignored : StringHistoryType.UnIgnored,
                    now = DateTime.UtcNow
                });
            }

            await RefreshCacheAsync(str.FamilyKey);
            return true;
        }

        public async Task<bool> DeleteSuggestionAsync(int suggestionId, int userId)
        {
            using (var db = _dbService.GetConnection())
            {
                var idString = await db.QuerySingleOrDefaultAsync<int>(@"
Declare @idString int = 0;

Update StringSuggestions
Set    StateId = {=DeletedByOwner},
       LastStateUpdatedById = @userId,
       LastStateUpdatedDate = @now,
       @idString = StringId
Where  Id = @suggestionId
And    CreatedById = @userId;

Insert Into StringSuggestionHistory
            (StringSuggestionId, HistoryTypeId, UserId, CreationDate)
Select Id, {=DeletedByOwner}, CreatedById, LastStateUpdatedDate
From   StringSuggestions
Where  Id = @suggestionId
And    CreatedById = @userId
And    StateId = {=DeletedByOwner};

Select @idString;", new
                {
                    suggestionId,
                    StringSuggestionState.DeletedByOwner,
                    userId,
                    now = DateTime.UtcNow
                });

                // If the Id returned is zero, then no data was updated, because there is no suggestion
                // or the user is not the one who created it.
                if (idString > 0)
                {
                    await RefreshCacheAsync(idString);
                    return true;
                }

                return false;
            }
        }

        public async Task<ImmutableArray<SOStringSuggestion>> GetSuggestionsByUser(int userId, StringSuggestionState? state)
        {
            string hasFilters = string.Empty;
            string sql = $@"
Declare @Ids Table
(
  Id int
);

Insert Into @Ids
Select Top 100 Id
From   StringSuggestions sug
Where  sug.CreatedById = @userId
{(state.HasValue ? "And sug.StateId = @state" : string.Empty)}
Order By sug.CreationDate Desc;

Select    sug.Id, sug.Suggestion, sug.StringId, sug.StateId State, sug.CreationDate, sug.LastStateUpdatedDate, sug.LastStateUpdatedById,
          u.DisplayName LastStateUpdatedByName, s.OriginalString
From      StringSuggestions sug
Join      @Ids ids On ids.Id = sug.Id
Left Join Users u On u.Id = sug.LastStateUpdatedById
Join      Strings s On s.Id = sug.StringId;

Select h.Id, h.StringSuggestionId, h.HistoryTypeId HistoryType, h.Comment, h.UserId, h.CreationDate,
       u.DisplayName UserName
From   StringSuggestionHistory h
Join   @Ids ids On ids.Id = h.StringSuggestionId
Join   Users u On u.Id = h.UserId;
";

            using (var db = _dbService.GetConnection())
            using (var reader = await db.QueryMultipleAsync(sql, new { userId, state }))
            {
                var suggestions = (await reader.ReadAsync<SOStringSuggestion>()).AsList();
                var histories = (await reader.ReadAsync<SOStringSuggestionHistory>()).AsList();
                var historiesById = histories.ToLookup(h => h.StringSuggestionId);

                foreach (var suggestion in suggestions)
                {
                    suggestion.Histories = historiesById[suggestion.Id].ToArray();
                }

                return suggestions.ToImmutableArray();
            }
        }

        public async Task<bool> ReplaceSuggestionAsync(int suggestionId, string suggestion, int userId)
        {
            using (var db = _dbService.GetConnection())
            {
                var idString = await db.QuerySingleOrDefaultAsync<int>(@"
Declare @idString int = 0;
Declare @OriginalString nvarchar(max);

Select @OriginalString = 'Original Suggestion: ' + Suggestion,
@idString = StringId
From StringSuggestions
Where id = @suggestionId
And   CreatedById = @userId;

Update StringSuggestions
Set Suggestion = @suggestion
Where Id = @suggestionId
And   CreatedById = @userId;

Insert into StringSuggestionHistory
            (StringSuggestionId, HistoryTypeId, Comment, UserId, CreationDate)
Select Id, {=ReplacedByOwner}, @OriginalString, CreatedById, @now
from StringSuggestions
Where Id = @suggestionId
And   CreatedById = @userId;

Select @idString", new
                {
                    suggestion,
                    suggestionId,
                    StringSuggestionHistoryType.ReplacedByOwner,
                    userId,
                    now = DateTime.UtcNow
                });

                // If the Id returned is zero, then no data was updated, because there is no suggestion
                // or the user is not the one who created it.
                if (idString > 0)
                {
                    await RefreshCacheAsync(idString);
                    return true;
                }

                return false;
            }
        }

        public async Task<int> GetStringIdBySuggestionId(int suggestionId)
        {
            using (var db = _dbService.GetConnection())
            {
                return await db.QuerySingleAsync<int>(
                    "select StringId from StringSuggestions where Id=@suggestionId",
                    new { suggestionId });
            }
        }

        private static Task ResetDumpTable(DbConnection db)
        {
            return db.ExecuteAsync(@"
Drop Table If Exists dbo.SODumpTable;
Create Table dbo.SODumpTable
(
Id                  Int Not Null Identity (1, 1),
Hash                VarChar(255) Not Null,
NormalizedHash      VarChar(255) Not Null,
Translation         NVarChar(Max) Null,

Constraint PK_SODumpTable Primary Key Clustered (Id),
Constraint IX_SODumpTable_Hash Unique (Hash),
Index      IX_SODumpTable_NormalizedHash NonClustered (NormalizedHash)
)");
        }

        private static Task CreateTemporaryTable(DbConnection db)
        {
            return db.ExecuteAsync(@"
Drop Table If Exists dbo.ImportTable;
Create Table dbo.ImportTable
(
NormalizedKey  VarChar(255) Not Null,
[Key]          VarChar(255) Not Null,
Variant        VarChar(255) Null,
OriginalString NVarChar(Max) Not Null,
Translation    NVarChar(Max) Null,

Constraint PK_ImportTable Primary Key Clustered (NormalizedKey Asc)
)");
        }

        private void ExpireCache()
        {
            Strings = ImmutableArray<SOString>.Empty;
        }

        private Task RefreshCacheAsync(string familyKey)
        {
            return RefreshCacheAsync(null, familyKey);
        }

        private async Task RefreshCacheAsync(int? stringId = null, string familyKey = null)
        {
            string sql = $@"
Select   Id, [Key], FamilyKey, OriginalString, Translation, NeedsPush, IsUrgent, IsIgnored, Variant, CreationDate
From     Strings
Where    IsNull(DeletionDate, @deletionDateLimit) >= @deletionDateLimit
{(stringId.HasValue ? "And Id = @stringId" : string.Empty)}
{(familyKey.HasValue() ? "And FamilyKey = @familyKey" : string.Empty)}
Order By IsUrgent Desc, OriginalString Asc;

Select    ss.Id, ss.StringId, ss.Suggestion, ss.StateId State,
          ss.CreatedById, u.DisplayName CreatedByName,
          ss.LastStateUpdatedById, uu.DisplayName LastStateUpdatedByName,
          ss.CreationDate
From      StringSuggestions ss
Join      Strings s On s.Id = ss.StringId And IsNull(s.DeletionDate, @deletionDateLimit) >= @deletionDateLimit
Join      Users u On ss.CreatedById = u.Id
Left Join Users uu On uu.Id = ss.LastStateUpdatedById
Where     ss.StateId In ({{=Created}}, {{=ApprovedByTrustedUser}})
{(stringId.HasValue ? "And s.Id = @stringId" : string.Empty)}
{(familyKey.HasValue() ? "And s.FamilyKey = @familyKey" : string.Empty)}";

            using (MiniProfiler.Current.Step("Refreshing the strings cache"))
            using (var db = _dbService.GetConnection())
            using (var reader = await db.QueryMultipleAsync(sql, new
            {
                StringSuggestionState.Created,
                StringSuggestionState.ApprovedByTrustedUser,
                stringId,
                familyKey,
                deletionDateLimit = DateTime.UtcNow.AddDays(-5)
            }))
            {
                var strings = (await reader.ReadAsync<SOString>()).AsList();
                var suggestions = (await reader.ReadAsync<SOStringSuggestion>()).AsList();
                Dictionary<int, SOString> stringsById;

                using (MiniProfiler.Current.Step("Attaching the suggestions to the strings"))
                {
                    stringsById = strings.ToDictionary(s => s.Id);
                    foreach (var g in suggestions.GroupBy(g => g.StringId))
                    {
                        if (stringsById.TryGetValue(g.Key, out var str))
                        {
                            str.Suggestions = g.ToArray();
                        }
                    }
                }

                if (stringId.HasValue)
                {
                    StringsById[stringId.Value] = stringsById[stringId.Value];
                    Strings = StringsById.Values.OrderByDescending(s => s.IsUrgent).ThenBy(s => s.OriginalString).ToImmutableArray();
                }
                else if (familyKey.HasValue())
                {
                    foreach (var str in strings)
                    {
                        StringsById[str.Id] = str;
                    }

                    Strings = StringsById.Values.OrderByDescending(s => s.IsUrgent).ThenBy(s => s.OriginalString).ToImmutableArray();
                }
                else
                {
                    StringsById = stringsById;
                    Strings = strings.ToImmutableArray();
                }
            }
        }
    }
}


<|start_filename|>Traducir.Core/Models/SOStringSuggestionHistory.cs<|end_filename|>
using System;
using Traducir.Core.Models.Enums;

namespace Traducir.Core.Models
{
    public class SOStringSuggestionHistory
    {
        public int Id { get; set; }

        public int StringSuggestionId { get; set; }

        public StringSuggestionHistoryType HistoryType { get; set; }

        public string Comment { get; set; }

        public int UserId { get; set; }

        public DateTime CreationDate { get; set; }

        public string UserName { get; set; }
    }
}

<|start_filename|>Traducir.Migrations/Migrations/012 - AddStringSuggestionHistoryTypeForReplace.cs<|end_filename|>
using SimpleMigrations;
using System;
using System.Collections.Generic;
using System.Text;

namespace Traducir.Migrations.Migrations
{

    [Migration(12, "Add new suggestion history type")]
    class AddSuggestionHistoryTypeForReplace: Migration
    {
        protected override void Up()
        {
            Execute(@"
Insert into StringSuggestionHistoryTypes
Values (8, 'ReplacedByOwner', 'The suggestion was replaced by its owner');
");

        }

        protected override void Down()
        {
            Execute(@"
Delete
From StringSuggestionHistoryTypes
Where id = 8;");
        }

    }
}


<|start_filename|>Traducir.Web/ViewModels/Home/ManageUrgencyViewModel.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Home
{
    public class ManageUrgencyViewModel
    {
        public int StringId { get; set; }

        public bool IsUrgent { get; set; }
    }
}

<|start_filename|>Traducir.Web/Views/Users/Notifications.cshtml<|end_filename|>
@using Traducir.Core.Models.Enums
@using Traducir.Web.ViewModels.Users
@model NotificationsViewModel

@{
    var notificationPropertyGroups = new[] { Model.NotificationProperties(0), Model.NotificationProperties(1) };
}

@section Scripts
{
    <script>
        var notificationSettings = {
            @foreach(var notification in notificationPropertyGroups[0].Union(notificationPropertyGroups[1]))
            {
                <text>@($"{notification.DataName}: {notification.Value.ToString().ToLower()},")</text>
            }
            notificationsInterval: @((int)Model.NotificationSettings.NotificationsInterval),
            notificationsIntervalValue: @((int)Model.NotificationSettings.NotificationsIntervalValue),
            vapidPublic: @Json.Serialize(Model.VapidPublic)
        };
    </script>

    <script>require(["users/notifications"], users => { users.default(); });</script>
}

<div class="container" id="main-container">
    <div class="m-2 text-center">
        <h2>Notification settings</h2>
    </div>
    <div class="row mt-4">
        <div class="col text-center">
            <h3>System notifications</h3>
        </div>
        <div class="col text-center">
            <h3>Your suggestion notifications</h3>
        </div>
    </div>
    <div class="row">
        @foreach (var propertyGroup in notificationPropertyGroups)
        {
            <div class="col">
                <ul class="list-group">
                    @foreach (var notification in propertyGroup)
                    {
                        <li class="@NotificationsViewModel.ClassFor(notification.Value)" data-notification-name="@notification.DataName">@notification.DisplayName</li>
                    }
                </ul>
            </div>
        }
    </div>
    <div class="col">
        <div class="row">
            Notify me about the same type of event at most once every
            <input type="number" id="notifications-interval-value" value="@Model.NotificationSettings.NotificationsIntervalValue" />
            @Html.DropDownListFor(
                model => model.NotificationSettings.NotificationsInterval,
                Html.GetEnumSelectList(typeof(NotificationInterval)),
                null,
                new { id = "notifications-interval" }
            )
        </div>
    </div>
    <div class="text-center mt-4">
        <button class="btn btn-primary mr-2" id="save-and-add-browser">Save and add current browser</button>
        <button class="btn btn-danger" id="stop-receiving-notifications">Stop receiving notifications everywhere</button>
    </div>
</div>


<|start_filename|>Traducir.Migrations/Migrations/008 - AddFamilyKey.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(8, "Add FamilyKey to our Strings")]
    public class AddFamilyKey : Migration
    {
        protected override void Down()
        {
            throw new System.NotImplementedException();
        }

        protected override void Up()
        {
            Execute(@"
If dbo.fnColumnExists('Strings', 'FamilyKey') = 0
Begin
    Alter Table Strings Add FamilyKey Char(32) Null;
End

If dbo.fnColumnExists('Strings', 'IsIgnored') = 0
Begin
    Alter Table Strings Add IsIgnored Bit Not Null Default(0);
End
");
            // we need the previous transaction to be committed before updating the field :shrug:
            Execute(@"
If Exists (Select 1 From Strings Where FamilyKey Is Null)
Begin
    Update Strings Set FamilyKey = Left([Key], 32);
    Alter Table Strings Alter Column FamilyKey Char(32) Not Null;
End

If dbo.fnIndexExists('Strings', 'IX_Strings_FamilyKey') = 0
Begin
  Create NonClustered Index IX_Strings_FamilyKey On Strings (FamilyKey)
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 8)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (8, 'Ignored', 'The string was ignored')
End

If Not Exists (Select 1 From StringHistoryTypes Where Id = 9)
Begin
    Insert Into StringHistoryTypes (Id, Name, Description)
    values (9, 'UnIgnored', 'The string was unignored')
End
");
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Users/UpdateNotificationSettingsViewModel.cs<|end_filename|>
using Traducir.Core.Models;

namespace Traducir.Web.ViewModels.Users
{
    public class UpdateNotificationSettingsViewModel
    {
        public NotificationSettings Notifications { get; set; }

        public SubscriptionViewModel Subscription { get; set; }
    }
}

<|start_filename|>Traducir.Core/Models/Enums/NotificationType.cs<|end_filename|>
using System;

namespace Traducir.Core.Models.Enums
{
    public enum NotificationType
    {
        UrgentStrings = 1,
        SuggestionsAwaitingApproval = 2,
        SuggestionsAwaitingReview = 3,
        StringsPushedToTransifex = 4,
        SuggestionsApproved = 5,
        SuggestionsRejected = 6,
        SuggestionsReviewed = 7,
        SuggestionsOverriden = 8,
    }

    public static class NotificationTypeExtensions
    {
        public static string GetTitle(this NotificationType type, int? count = null)
        {
            switch (type)
            {
                case NotificationType.UrgentStrings:
                    return $"{count} urgent strings";
                case NotificationType.SuggestionsAwaitingApproval:
                    return $"{count} awaiting approval";
                case NotificationType.SuggestionsAwaitingReview:
                    return $"{count} awaiting review";
                case NotificationType.StringsPushedToTransifex:
                    return "Strings pushed to transifex";
                case NotificationType.SuggestionsApproved:
                    return "Suggestions approved";
                case NotificationType.SuggestionsRejected:
                    return "Suggestions rejected";
                case NotificationType.SuggestionsReviewed:
                    return "Suggestions reviewed";
                case NotificationType.SuggestionsOverriden:
                    return "Suggestions overriden";
                default:
                    throw new ArgumentException($"Missing title for notification {type}");
            }
        }

        public static string GetBody(this NotificationType type)
        {
            switch (type)
            {
                case NotificationType.UrgentStrings:
                    return "Go to urgent strings";
                case NotificationType.SuggestionsAwaitingApproval:
                case NotificationType.SuggestionsAwaitingReview:
                    return "Go to these suggestions";
                case NotificationType.StringsPushedToTransifex:
                    return "Now we need a CM";
                case NotificationType.SuggestionsApproved:
                case NotificationType.SuggestionsRejected:
                case NotificationType.SuggestionsReviewed:
                case NotificationType.SuggestionsOverriden:
                    return "Go to your suggestions";
                default:
                    throw new ArgumentException($"Missing body for notification {type}");
            }
        }

        public static string GetUserColumnName(this NotificationType type)
        {
            switch (type)
            {
                case NotificationType.UrgentStrings:
                    return "NextNotificationUrgentStrings";
                case NotificationType.SuggestionsAwaitingApproval:
                    return "NextNotificationSuggestionsAwaitingApproval";
                case NotificationType.SuggestionsAwaitingReview:
                    return "NextNotificationSuggestionsAwaitingReview";
                case NotificationType.StringsPushedToTransifex:
                    return "NextNotificationStringsPushedToTransifex";
                case NotificationType.SuggestionsApproved:
                    return "NextNotificationSuggestionsApproved";
                case NotificationType.SuggestionsRejected:
                    return "NextNotificationSuggestionsRejected";
                case NotificationType.SuggestionsReviewed:
                    return "NextNotificationSuggestionsReviewed";
                case NotificationType.SuggestionsOverriden:
                    return "NextNotificationSuggestionsOverriden";
                default:
                    throw new ArgumentException($"Missing user column for notification {type}");
            }
        }

        public static bool ShouldBeBatched(this NotificationType type)
        {
            switch (type)
            {
                case NotificationType.UrgentStrings:
                case NotificationType.SuggestionsAwaitingApproval:
                case NotificationType.SuggestionsAwaitingReview:
                case NotificationType.StringsPushedToTransifex:
                    return true;
                case NotificationType.SuggestionsApproved:
                case NotificationType.SuggestionsRejected:
                case NotificationType.SuggestionsReviewed:
                case NotificationType.SuggestionsOverriden:
                    return false;
                default:
                    throw new ArgumentException($"Missing batching info for notification {type}");
            }
        }

        public static string GetUrl(this NotificationType type, string host, int userId)
        {
            var basePath = "https://" + host;
            switch (type)
            {
                case NotificationType.UrgentStrings:
                    return $"{basePath}/filters?urgencyStatus=1";
                case NotificationType.SuggestionsAwaitingApproval:
                    return $"{basePath}/filters?suggestionsStatus=3";
                case NotificationType.SuggestionsAwaitingReview:
                    return $"{basePath}/filters?suggestionsStatus=4";
                case NotificationType.StringsPushedToTransifex:
                    return null;
                case NotificationType.SuggestionsApproved:
                case NotificationType.SuggestionsRejected:
                case NotificationType.SuggestionsReviewed:
                case NotificationType.SuggestionsOverriden:
                    return $"{basePath}/users/{userId}/suggestions";
                default:
                    throw new ArgumentException($"Missing url info for notification {type}");
            }
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Home/ManageIgnoreViewModel.cs<|end_filename|>
namespace Traducir.Web.ViewModels.Home
{
    public class ManageIgnoreViewModel
    {
        public int StringId { get; set; }

        public bool Ignored { get; set; }
    }
}

<|start_filename|>Traducir.Core/Helpers/ClaimType.cs<|end_filename|>
namespace Traducir.Core.Helpers
{
    public static class ClaimType
    {
        public const string Id = nameof(Id);
        public const string Name = nameof(Name);
        public const string UserType = nameof(UserType);
        public const string CanSuggest = nameof(CanSuggest);
        public const string CanReview = nameof(CanReview);
        public const string IsModerator = nameof(IsModerator);
    }
}

<|start_filename|>Traducir.Core/Models/Enums/StringSuggestionHistoryType.cs<|end_filename|>
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Reflection;

namespace Traducir.Core.Models.Enums
{
    public enum StringSuggestionHistoryType
    {
        [Display(Name = "Created")]
        Created = 1,

        [Display(Name = "Approved by trusted user")]
        ApprovedByTrusted = 2,

        [Display(Name = "Approved by reviewer")]
        ApprovedByReviewer = 3,

        [Display(Name = "Rejected by trusted user")]
        RejectedByTrusted = 4,

        [Display(Name = "Rejected by reviewer")]
        RejectedByReviewer = 5,

        [Display(Name = "Deleted by owner")]
        DeletedByOwner = 6,

        [Display(Name = "Dismissed by other string")]
        DismissedByOtherString = 7,

        [Display(Name = "Replaced by owner")]
        ReplacedByOwner = 8
    }

    public static class StringSuggestionHistoryTypeExtensions
    {
        public static string DisplayName(this StringSuggestionHistoryType state)
        {
            return typeof(StringSuggestionHistoryType)
                .GetMember(state.ToString())
                .First()
                .GetCustomAttribute<DisplayAttribute>()
                .Name;
        }
    }
}

<|start_filename|>Traducir.Core/Models/Enums/UserHistoryType.cs<|end_filename|>
namespace Traducir.Core.Models.Enums
{
    public enum UserHistoryType
    {
        Banned = 1,
        BanLifted = 2,
        MadeTrustedUser = 3,
        DemotedToRegularUser = 4
    }
}

<|start_filename|>Traducir.Web/Views/Users/SuggestionsByUser.cshtml<|end_filename|>
@using System.Linq
@using Traducir.Core.Helpers
@using Traducir.Core.Models.Enums
@using Traducir.Web.ViewModels.Users

@model SuggestionsByUserViewModel

<div class="container">
    <div class="row text-center mt-3">
        <div class="col d-none d-lg-block">
            <div class="btn-group" role="group">
                <a href="@Url.Action("SuggestionsByUser", "Users", new { userId = Model.UserId})" class="btn btn-outline-info nav-link mx-1 @(Model.CurrentState == null ? "active" : null)">All</a>
                @foreach (var state in SuggestionsByUserViewModel.SuggestionStatesForFilters)
                {
                    <a href="@Url.Action("SuggestionsByUser", "Users", new { userId = Model.UserId, state = (int)state})" class="btn btn-outline-info nav-link mx-1 @(state==Model.CurrentState ? "active" : null)">@state.DisplayName()</a>
                }
            </div>
        </div>
    </div>

    @if (Model.Suggestions.Any())
    {
        foreach (var suggestion in Model.Suggestions)
        {
            <div class="mt-5">
                <div>
                    <span class="font-weight-bold">Original String:</span> <pre class="d-inline">@suggestion.OriginalString</pre>
                </div>
                <div>
                    <span class="font-weight-bold">Suggestion:</span> <pre class="d-inline">@suggestion.Suggestion</pre>
                </div>
                <div>
                    <span class="font-weight-bold">State:</span> <span class="badge @SuggestionsByUserViewModel.BadgeClassForState(suggestion.State)">@suggestion.State.DisplayName()</span>
                </div>
                <table class="table">
                    <thead>
                        <tr>
                            <th>Event</th>
                            <th>User</th>
                            <th>Comment</th>
                            <th>Date</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var history in suggestion.Histories)
                        {
                            <tr>
                                <td>
                                    @history.HistoryType.DisplayName()
                                </td>
                                <td>
                                    <a href="https://@Model.SiteDomain/users/@history.UserId"
                                       target="_blank"
                                       title="at @suggestion.CreationDate.ToIsoFormat() UTC">
                                        @history.UserName
                                    </a>
                                </td>
                                <td>
                                    @history.Comment
                                </td>
                                <td>
                                    @history.CreationDate.ToIsoFormat()
                                </td>
                            </tr>
                        }
                    </tbody>
                </table>
            </div>
        }
    }
    else
    {
        <div class="row">
            <div class="col">
                <div class="mx-auto mt-5 w-50 p-3 text-center">
                    <span>No results (sad trombone)</span>
                </div>
            </div>
        </div>
    }
</div>



<|start_filename|>Traducir.Core/Services/TransifexService.cs<|end_filename|>
using System;
using System.Collections.Immutable;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using StackExchange.Profiling;
using Traducir.Core.Models;
using Traducir.Core.Models.Services;

namespace Traducir.Core.Services
{
    public interface ITransifexService
    {
        Task<ImmutableArray<TransifexString>> GetStringsFromTransifexAsync();

        Task<bool> PushStringsToTransifexAsync(ImmutableArray<SOString> strings);
    }

    public class TransifexService : ITransifexService
    {
        private readonly string _apikey;
        private readonly string _resourcePath;
        private readonly ISOStringService _soStringService;

        public TransifexService(IConfiguration configuration, ISOStringService soStringService)
        {
            _apikey = configuration.GetValue<string>("TRANSIFEX_APIKEY");
            _resourcePath = configuration.GetValue<string>("TRANSIFEX_RESOURCE_PATH");
            _soStringService = soStringService;
        }

        private static HttpClient HttpClient { get; set; }

        public async Task<ImmutableArray<TransifexString>> GetStringsFromTransifexAsync()
        {
            using (MiniProfiler.Current.Step("Fetching strings from Transifex"))
            {
                var client = GetHttpClient();
                var response = await client.GetAsync(_resourcePath);
                response.EnsureSuccessStatusCode();

                using (var stream = await response.Content.ReadAsStreamAsync())
                using (var reader = new StreamReader(stream))
                {
                    return Jil.JSON.Deserialize<TransifexString[]>(reader).ToImmutableArray();
                }
            }
        }

#if RISKY
        public Task<bool> PushStringsToTransifexAsync(ImmutableArray<SOString> strings)
        {
            throw new NotImplementedException("Method not available on a RISKY build");
#else
        public async Task<bool> PushStringsToTransifexAsync(ImmutableArray<SOString> strings)
        {
            using (MiniProfiler.Current.Step("Pushing strings to Transifex"))
            {
                ByteArrayContent byteContent;
                using (MiniProfiler.Current.Step("Serializing the payload"))
                {
                    var content = Jil.JSON.Serialize(strings.Select(s => new TransifexStringToPush
                    {
                        Key = s.Key,
                        Translation = s.Translation
                    }));

                    var buffer = Encoding.UTF8.GetBytes(content);
                    byteContent = new ByteArrayContent(buffer);
                    byteContent.Headers.ContentType = new MediaTypeHeaderValue("application/json");
                }

                bool success;
                using (MiniProfiler.Current.Step("Posting to Transifex"))
                {
                    var client = GetHttpClient();
                    var response = await client.PutAsync(_resourcePath, byteContent);
                    success = response.IsSuccessStatusCode;
                    var r = await response.Content.ReadAsStringAsync();
                }

                if (success)
                {
                    await _soStringService.UpdateStringsPushed();
                }

                return success;
            }
#endif
        }

        private HttpClient GetHttpClient()
        {
            if (HttpClient == null)
            {
                var baseAddress = new Uri("https://www.transifex.com");
                HttpClient = new HttpClient { BaseAddress = baseAddress };
                var byteArray = Encoding.ASCII.GetBytes("api:" + _apikey);
                HttpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", Convert.ToBase64String(byteArray));
            }

            return HttpClient;
        }
    }
}

<|start_filename|>Traducir.Web/Views/Users/Users.cshtml<|end_filename|>
@model Traducir.Web.ViewModels.Users.UsersListViewModel

@section Scripts
{
    <script>require(["users/user-edit"], users => { users.default(); });</script>
}

<div class="container">
    <div class="m-2 text-center">
        <h2>Users</h2>
    </div>
    <table class="table">
        <thead class="thead-light">
            <tr>
                <th>User</th>
                <th>Role</th>
                <th>&nbsp;</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var user in Model.Users)
            {
                <partial name="UserSummary" model="user" />
            }
        </tbody>
    </table>
</div>

<|start_filename|>Traducir.Web/Services/StringsService.cs<|end_filename|>
using System;
using System.Collections.Immutable;
using System.Globalization;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Traducir.Core.Helpers;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;
using Traducir.Core.Services;
using Traducir.Web.ViewModels.Enums;
using Traducir.Web.ViewModels.Home;

namespace Traducir.Web.Services
{
    public interface IStringsService
    {
        Task<ImmutableArray<SOString>> Query(QueryViewModel model);

        Task<StringCountsViewModel> GetStringCounts();

        Task<SuggestionCreationResult> CreateSuggestion(CreateSuggestionViewModel model);
    }

    public class StringsService : IStringsService
    {
        private static readonly Regex VariablesRegex = new Regex(@"\$[^ \$]+\$", RegexOptions.Compiled);
        private static readonly Regex WhitespacesRegex = new Regex(@"^(?<start>\s*).*?(?<end>\s*)$", RegexOptions.Singleline | RegexOptions.Compiled);

        private readonly ISOStringService _soStringService;
        private readonly IAuthorizationService _authorizationService;
        private readonly IHttpContextAccessor _httpContextAccessor;

        public StringsService(
            ISOStringService soStringService,
            IAuthorizationService authorizationService,
            IHttpContextAccessor httpContextAccessor)
        {
            _soStringService = soStringService;
            _authorizationService = authorizationService;
            _httpContextAccessor = httpContextAccessor;
        }

        public async Task<ImmutableArray<SOString>> Query(QueryViewModel model)
        {
            Func<SOString, bool> predicate = null;

            void ComposePredicate(Func<SOString, bool> newPredicate)
            {
                if (predicate == null)
                {
                    predicate = newPredicate;
                    return;
                }

                var oldPredicate = predicate;
                predicate = s => oldPredicate(s) && newPredicate(s);
            }

            if (model.TranslationStatus != TranslationStatus.AnyStatus)
            {
                ComposePredicate(s => s.HasTranslation == (model.TranslationStatus == TranslationStatus.WithTranslation));
            }

            if (model.PushStatus != PushStatus.AnyStatus)
            {
                ComposePredicate(s => s.NeedsPush == (model.PushStatus == PushStatus.NeedsPush));
            }

            if (model.UrgencyStatus != UrgencyStatus.AnyStatus)
            {
                ComposePredicate(s => s.IsUrgent == (model.UrgencyStatus == UrgencyStatus.IsUrgent));
            }

            if (model.IgnoredStatus != IgnoredStatus.IncludeIgnored)
            {
                ComposePredicate(s => s.IsIgnored == (model.IgnoredStatus == IgnoredStatus.OnlyIgnored));
            }

            if (model.SuggestionsStatus != SuggestionApprovalStatus.AnyStatus)
            {
                switch (model.SuggestionsStatus)
                {
                    case SuggestionApprovalStatus.DoesNotHaveSuggestions:
                        ComposePredicate(s => !s.HasSuggestions);
                        break;
                    case SuggestionApprovalStatus.HasSuggestionsNeedingReview:
                        ComposePredicate(s =>
                            s.Suggestions != null &&
                            s.Suggestions.Any(sug => sug.State == StringSuggestionState.Created || sug.State == StringSuggestionState.ApprovedByTrustedUser));
                        break;
                    case SuggestionApprovalStatus.HasSuggestionsNeedingApproval:
                        ComposePredicate(s => s.HasSuggestionsWaitingApproval);
                        break;
                    case SuggestionApprovalStatus.HasSuggestionsNeedingReviewApprovedByTrustedUser:
                        ComposePredicate(s => s.HasApprovedSuggestionsWaitingReview);
                        break;
                }
            }

            if (model.Key.HasValue())
            {
                ComposePredicate(s => s.Key.StartsWith(model.Key, true, CultureInfo.InvariantCulture));
            }

            if (model.SourceRegex.HasValue())
            {
                Regex regex;
                try
                {
                    regex = new Regex(model.SourceRegex, RegexOptions.Compiled);
                }
                catch (ArgumentException ex)
                {
                    throw new InvalidOperationException("Invalid source regex", ex);
                }

                ComposePredicate(s => regex.IsMatch(s.OriginalString));
            }

            if (model.TranslationRegex.HasValue())
            {
                Regex regex;
                try
                {
                    regex = new Regex(model.TranslationRegex, RegexOptions.Compiled);
                }
                catch (ArgumentException ex)
                {
                    throw new InvalidOperationException("Invalid translation regex", ex);
                }

                ComposePredicate(s => s.HasTranslation && regex.IsMatch(s.Translation));
            }

            return await _soStringService.GetStringsAsync(predicate);
        }

        public async Task<StringCountsViewModel> GetStringCounts()
        {
            return new StringCountsViewModel
            {
                TotalStrings = await _soStringService.CountStringsAsync(s => !s.IsIgnored),
                WithoutTranslation = await _soStringService.CountStringsAsync(s => !s.HasTranslation && !s.IsIgnored),
                WithPendingSuggestions = await _soStringService.CountStringsAsync(s => s.HasSuggestions && !s.IsIgnored),
                WaitingApproval = await _soStringService.CountStringsAsync(s => s.HasSuggestionsWaitingApproval && !s.IsIgnored),
                WaitingReview = await _soStringService.CountStringsAsync(s => s.HasApprovedSuggestionsWaitingReview && !s.IsIgnored),
                UrgentStrings = await _soStringService.CountStringsAsync(s => s.IsUrgent && !s.IsIgnored),
            };
        }

        public async Task<SuggestionCreationResult> CreateSuggestion(CreateSuggestionViewModel model)
        {
            var user = _httpContextAccessor.HttpContext.User;

            // Verify that everything is valid before calling the service
            var str = await _soStringService.GetStringByIdAsync(model.StringId);

            // if the string id is invalid
            if (str == null)
            {
                return SuggestionCreationResult.InvalidStringId;
            }

            // empty suggestion
            if (model.Suggestion.IsNullOrEmpty())
            {
                return SuggestionCreationResult.EmptySuggestion;
            }

            var usingRawString = model.RawString &&
                (await _authorizationService.AuthorizeAsync(user, TraducirPolicy.CanReview)).Succeeded;

            // fix whitespaces unless user is reviewer and selected raw string
            if (!usingRawString)
            {
                model.Suggestion = FixWhitespaces(model.Suggestion, str.OriginalString);
            }

            // if the suggestion is the same as the current translation
            if (str.Translation == model.Suggestion)
            {
                return SuggestionCreationResult.SuggestionEqualsOriginal;
            }

            // if there's another suggestion with the same value
            if (str.Suggestions != null && str.Suggestions.Any(sug => sug.Suggestion == model.Suggestion))
            {
                return SuggestionCreationResult.SuggestionAlreadyThere;
            }

            // if there are missing or extra values
            var variablesInOriginal = VariablesRegex.Matches(str.OriginalString).Select(m => m.Value).ToArray();
            var variablesInSuggestion = VariablesRegex.Matches(model.Suggestion).Select(m => m.Value).ToArray();

            if (!usingRawString && variablesInOriginal.Any(v => !variablesInSuggestion.Contains(v)))
            {
                return SuggestionCreationResult.TooFewVariables;
            }

            if (variablesInSuggestion.Any(v => !variablesInOriginal.Contains(v)))
            {
                return SuggestionCreationResult.TooManyVariables;
            }

            var suggestionResult = await _soStringService.CreateSuggestionAsync(
                model.StringId,
                model.Suggestion,
                user.GetClaim<int>(ClaimType.Id),
                user.GetClaim<UserType>(ClaimType.UserType),
                model.Approve);

            return suggestionResult ? SuggestionCreationResult.CreationOk : SuggestionCreationResult.DatabaseError;
        }

        private static string FixWhitespaces(string suggestion, string original)
        {
            var match = WhitespacesRegex.Match(original);
            return match.Groups["start"] + suggestion.Trim() + match.Groups["end"];
        }
    }
}


<|start_filename|>Traducir.Web/Views/Backups/Index.cshtml<|end_filename|>
@model List<Traducir.Web.ViewModels.Backups.Backup>
<div class="container">
    <div class="m-2 text-center">
        <h2>Backups</h2>
    </div>
    <table class="table">
        <thead class="thead-light">
            <tr>
                <th>File</th>
            </tr>
        </thead>
        <tbody>
            @foreach (var backup in Model)
            {
                <tr>
                    <td><a href="@backup.Url">@backup.Filename</a></td>
                </tr>
            }
        </tbody>
    </table>
</div>

<|start_filename|>Traducir.Core/Helpers/TraducirPolicy.cs<|end_filename|>
namespace Traducir.Core.Helpers
{
    public static class TraducirPolicy
    {
        public const string CanSuggest = nameof(CanSuggest);
        public const string CanReview = nameof(CanReview);
        public const string CanManageUsers = nameof(CanManageUsers);
    }
}

<|start_filename|>Traducir.Core/Models/Services/SEApiService.cs<|end_filename|>
using System;
using System.Runtime.Serialization;

namespace Traducir.Core.Models.Services
{
    public class PaginatedResponse<T>
    {
        [DataMember(Name = "items")]
        public T[] Items { get; set; }

        [DataMember(Name = "has_more")]
        public bool HasMore { get; set; }

        [DataMember(Name = "quota_max")]
        public int QuotaMax { get; set; }

        [DataMember(Name = "quota_remaining")]
        public int QuotaRemaining { get; set; }
    }

    public abstract class BaseUser
    {
        [DataMember(Name = "account_id")]
        public int AccountId { get; set; }

        [DataMember(Name = "user_id")]
        public int UserId { get; set; }

        [DataMember(Name = "user_type")]
        public string UserType { get; set; }
    }

    public class User : BaseUser
    {
        [DataMember(Name = "display_name")]
        public string DisplayName { get; set; }

        [DataMember(Name = "is_employee")]
        public bool IsEmployee { get; set; }

        [DataMember(Name = "reputation")]
        public int Reputation { get; set; }
    }

    public class NetworkUser : BaseUser
    {
        [DataMember(Name = "site_name")]
        public string SiteName { get; set; }

        [DataMember(Name = "site_url")]
        public string SiteUrl { get; set; }

        public string SiteDomain => new Uri(SiteUrl).Host;
    }
}

<|start_filename|>Traducir.Migrations/Migrations/002 - CreateInitialTables.cs<|end_filename|>
using SimpleMigrations;

namespace Traducir.Migrations.Migrations
{
    [Migration(2, "Create initial tables")]
    public class CreateInitialTables : Migration
    {
        protected override void Up()
        {
            Execute(@"
If dbo.fnTableExists('Strings') = 0
Begin
  Create Table dbo.Strings
  (
    Id Int Not Null Identity (1, 1),
    [Key] VarChar(255) Not Null,
    NormalizedKey VarChar(255) Not Null,
    OriginalString NVarChar(Max) Not Null,
    Translation NVarChar(Max) Null,
    NeedsPush Bit Not Null Default(0),
    Variant VarChar(255) Null,
    CreationDate DateTime Not Null,
    DeletionDate DateTime Null

    Constraint PK_Strings Primary Key Clustered (Id Asc),
    Constraint IX_Strings_Key UNIQUE ([Key]),
    Constraint IX_Strings_NormalizedKey UNIQUE (NormalizedKey)
  )
  On [Primary]
End

If dbo.fnIndexExists('Strings', 'IX_Strings_NormalizedKey_Filtered') = 0
Begin
  Create Index IX_Strings_NormalizedKey_Filtered On Strings (NormalizedKey)
  Include (DeletionDate)
  Where DeletionDate Is Null
End

If dbo.fnTableExists('Users') = 0
Begin
  Create Table dbo.Users
  (
    Id Int Not Null,
    DisplayName NVarChar(150) Not Null,
    IsModerator Bit Not Null,
    IsTrusted Bit Not Null,
    IsReviewer Bit Not Null,
    IsBanned Bit Not Null,
    CreationDate DateTime Not Null,
    LastSeenDate DateTime Null

    Constraint PK_Users Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

If dbo.fnTableExists('StringSuggestions') = 0
Begin
  Create Table dbo.StringSuggestions
  (
    Id Int Not Null Identity (1, 1),
    StringId Int Not Null Constraint FK_StringSuggestions_String Foreign Key References Strings (Id),
    Suggestion NVarChar(Max),
    StateId TinyInt Not Null,
    CreatedById Int Not Null Constraint FK_StringSuggestions_CreatedBy Foreign Key References Users (Id),
    LastStateUpdatedById Int Null Constraint FK_StringSuggestions_LastStateUpdatedById Foreign Key References Users (Id),
    CreationDate DateTime Not Null,
    LastStateUpdatedDate DateTime Null

    Constraint PK_StringSuggestions Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

If dbo.fnTableExists('StringSuggestionHistory') = 0
Begin
  Create Table dbo.StringSuggestionHistory
  (
    Id Int Not Null Identity (1, 1),
    StringSuggestionId Int Not Null Constraint FK_StringSuggestionHistory_StringSuggestion Foreign Key References StringSuggestions (Id),
    HistoryTypeId TinyInt Not Null,
    Comment NVarChar(100) Null,
    UserId Int Null Constraint FK_StringSuggestionHistory_User Foreign Key References Users (Id),
    CreationDate datetime Not Null

    Constraint PK_StringSuggestionHistory Primary Key Clustered (Id Asc)
  )
  On [Primary]
End

If dbo.fnTableExists('StringHistory') = 0
Begin
  Create Table dbo.StringHistory
  (
    Id Int Not Null Identity (1, 1),
    StringId Int Not Null Constraint FK_StringHistory_String Foreign Key References Strings (Id),
    HistoryTypeId TinyInt Not Null,
    Comment NVarChar(100) Null,
    UserId Int Null Constraint FK_StringHistory_User Foreign Key References Users (Id),
    CreationDate datetime Not Null

    Constraint PK_StringHistory Primary Key Clustered (Id Asc)
  )
  On [Primary]
End");
        }

        protected override void Down()
        {
            throw new System.NotImplementedException();
        }
    }
}

<|start_filename|>Traducir.Web/ViewModels/Users/SuggestionsByUserViewModel.cs<|end_filename|>
using System;
using System.Collections.Generic;
using System.Linq;
using Traducir.Core.Models;
using Traducir.Core.Models.Enums;

namespace Traducir.Web.ViewModels.Users
{
    public class SuggestionsByUserViewModel
    {
        public static IEnumerable<StringSuggestionState> SuggestionStatesForFilters =>
            Enum.GetValues(typeof(StringSuggestionState)).OfType<StringSuggestionState>().OrderBy(s => (int)s);

        public IEnumerable<SOStringSuggestion> Suggestions { get; set; }

        public int UserId { get; set; }

        public string SiteDomain { get; set; }

        public StringSuggestionState? CurrentState { get; set; }

        public static string BadgeClassForState(StringSuggestionState state)
        {
            switch (state)
            {
                case StringSuggestionState.Created:
                    return "badge-secondary";
                case StringSuggestionState.ApprovedByReviewer:
                case StringSuggestionState.ApprovedByTrustedUser:
                    return "badge-success";
                case StringSuggestionState.Rejected:
                    return "badge-danger";
                case StringSuggestionState.DeletedByOwner:
                    return "badge-dark";
                default:
                    return null;
            }
        }
    }
}
