/source/Bizio.Repositories/SqlProjectRepository.cs

using Bizio.Core.Data.Companies;
using Bizio.Core.Data.People;
using Bizio.Core.Data.Projects;
using Bizio.Core.Repositories;
using Bizio.Core.Repositories.Bases;
using Bizio.Core.Services.Data;
using Bizio.Core.Utilities;
using BlackPlain.Sql;
using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace Bizio.Repositories
{
    public sealed class SqlProjectRepository : SqlRepositoryBase<BizioConnectionStringOptions>, IProjectRepository
    {
        public SqlProjectRepository(IOptions<BizioConnectionStringOptions> options) : base(options) { }

        public async Task<IEnumerable<ProjectDefinition>> LoadProjectDefinitionsAsync(CancellationToken cancellationToken = default)
        {
            var output = new List<ProjectDefinition>();

            var dbDefinitions = await Sql.BeginSelect()
                .Select("Id")
                .Select("Name")
                .Select("Description")
                .Select("MinimumValue")
                .Select("MaximumValue")
                .Select("MinimumProjectLength")
                .Select("MaximumProjectLength")
                .From("ProjectDefinition")
                .ExecuteAsync(Connection, cancellationToken);

            // TODO: flatten the queries below because they're
            // being called a ton

            // decided to do nested queries instead of joins
            // because the size of the returned result set
            // outweighs the speed of the queries
            foreach(var dbDefinition in dbDefinitions)
            {
                var projectDefinitionId = dbDefinition["Id"].AsShort();

                var getProjectSkillsTask = Sql.BeginSelect()
                    .Select("SkillDefinitionId")
                    .Select("Minimum")
                    .Select("Maximum")
                    .Select("IsRequired")
                    .From("ProjectDefinitionSkill")
                    .Where("ProjectDefinitionId = @ProjectDefinitionId")
                    .AddParameter("@ProjectDefinitionId", projectDefinitionId)
                    .ExecuteAsync(Connection, cancellationToken);

                var getIndustriesTask = Sql.BeginSelect()
                    .Select("IndustryId")
                    .From("ProjectDefinitionIndustry")
                    .Where("ProjectDefinitionId = @ProjectDefinitionId")
                    .AddParameter("@ProjectDefinitionId", projectDefinitionId)
                    .ExecuteAsync(Connection, cancellationToken);

                var skills = (await getProjectSkillsTask)
                    .Select(s => new ProjectDefinitionSkill
                    {
                        SkillDefinition = new SkillDefinition
                        {
                            Id = s["SkillDefinitionId"].AsByte()
                        },
                        Value = new Range<decimal>(s["Minimum"].AsShort(), s["Maximum"].AsShort()),
                        IsRequired = s["IsRequired"].AsBool()
                    })
                    .ToList();

                var industries = (await getIndustriesTask)
                    .Select(i => new Industry
                    {
                        Id = i["IndustryId"].AsByte()
                    })
                    .ToList();

                output.Add(new ProjectDefinition(industries, skills)
                {
                    Id = projectDefinitionId,
                    Name = dbDefinition["Name"].AsString(),
                    Description = dbDefinition["Description"].AsString(),
                    Value = new Range<decimal>(dbDefinition["MinimumValue"].AsInt(), dbDefinition["MaximumValue"].AsInt()),
                    ProjectLength = new Range<byte>(dbDefinition["MinimumProjectLength"].AsByte(), dbDefinition["MaximumProjectLength"].AsByte())
                });
            }

            return output;
        }

        public void SaveGame(int gameId, ProjectServiceData data)
        {
            if(!data.Projects.Any())
            {
                return;
            }

            using (var transaction = BeginTransaction())
            {
                Sql.BeginMerge<Project>()
                    .Using(data.Projects, "SOURCE")
                    .Value("Id", SqlDbType.Int, p => p.Id)
                    .Value("ProjectDefinitionId", SqlDbType.SmallInt, p => p.Definition.Id)
                    .Value("Value", SqlDbType.Int, p => (int)p.Value)
                    .Value("Deadline", SqlDbType.Date, p => p.Deadline)
                    .Value("ExtensionDeadline", SqlDbType.Date, p => p.ExtensionDeadline)
                    .Value("ReputationRequired", SqlDbType.TinyInt, p => (byte)p.ReputationRequired)
                    .Value("StatusId", SqlDbType.TinyInt, p => (byte)p.Status)
                    .Value("ResultId", SqlDbType.TinyInt, p => (byte?)p.Result)
                    .EndValues()
                    .AddParameter("@GameId", gameId)
                    .Into("Project", "TARGET")
                    .On("TARGET.Id = SOURCE.Id")
                    .When().Matched().ThenUpdate()
                    .Set("TARGET.ExtensionDeadline = SOURCE.ExtensionDeadline")
                    .Set("TARGET.StatusId = SOURCE.StatusId")
                    .Set("TARGET.ResultId = SOURCE.ResultId")
                    .EndUpdate()
                    .When().NotMatched().ThenInsert()
                    .Insert("GameId", "@GameId")
                    .Insert("ProjectDefinitionId")
                    .Insert("Value")
                    .Insert("Deadline")
                    .Insert("ExtensionDeadline")
                    .Insert("ReputationRequired")
                    .Insert("StatusId")
                    .Insert("ResultId")
                    .EndInsert()
                    .Execute(Connection, transaction, AssignId);

                var requirements = Flatten(data.Projects, p => p.Id, p => p.Requirements);

                MergeInto("ProjectRequirement", requirements)
                    .Value("ProjectId", SqlDbType.Int, r => r.Id)
                    .Value("SkillDefinitionId", SqlDbType.TinyInt, r => r.Value.SkillDefinition.Id)
                    .Value("TargetValue", SqlDbType.Int, r => r.Value.TargetValue)
                    .Value("CurrentValue", SqlDbType.Decimal, 9, 2, r => r.Value.CurrentValue)
                    .EndValues()
                    .On("TARGET.ProjectId = SOURCE.ProjectId AND TARGET.SkillDefinitionId = SOURCE.SkillDefinitionId")
                    .When().Matched().ThenUpdate()
                    .Set("TARGET.CurrentValue = SOURCE.CurrentValue")
                    .EndUpdate()
                    .When().NotMatchedByTarget().ThenInsert()
                    .Insert("ProjectId")
                    .Insert("SkillDefinitionId")
                    .Insert("TargetValue")
                    .Insert("CurrentValue")
                    .EndInsert()
                    .Execute(Connection, transaction);

                transaction.Commit();
            }
        }

        public ProjectServiceData LoadGame(int userId, int gameId)
        {
            var requirements = Sql.BeginSelect()
                .Select("pr.ProjectId")
                .Select("pr.SkillDefinitionId")
                .Select("pr.TargetValue")
                .Select("pr.CurrentValue")
                .From("ProjectRequirement", "pr")
                .Join("Project", "p")
                .On("p.Id = pr.ProjectId AND p.GameId = @GameId")
                .AddParameter("@GameId", gameId)
                .Execute(Connection, null)
                .GroupBy(d => d["ProjectId"].AsInt())
                .Select(g => Pair(g.Key, g.Select(r => new ProjectRequirement
                {
                    SkillDefinition = new SkillDefinition
                    {
                        Id = r["SkillDefinitionId"].AsByte()
                    },
                    TargetValue = r["TargetValue"].AsInt(),
                    CurrentValue = r["CurrentValue"].AsDecimal()
                }).ToList()));

            var dbProjects = Sql.BeginSelect()
                .Select("p.Id")
                .Select("p.ProjectDefinitionId")
                .Select("p.Value")
                .Select("p.Deadline")
                .Select("p.ExtensionDeadline")
                .Select("p.ReputationRequired")
                .Select("p.StatusId")
                .Select("p.ResultId")
                .From("Project", "p")
                .LeftJoin("CompanyProject", "cp")
                .On("cp.ProjectId = p.Id")
                .Join("GameClock", "gc")
                .On("gc.GameId = p.GameId")
                .Where("p.GameId = @GameId")
                .Where("ISNULL(p.ExtensionDeadline, p.Deadline) > DATEADD(DAY, 7 * gc.WeeksPerTurn * (gc.CurrentTurn - 1), gc.StartDate) OR cp.CompanyId IS NOT NULL")
                .AddParameter("@GameId", gameId)
                .Execute(Connection);

            var projects = new List<Project>();

            foreach(var dbProject in dbProjects)
            {
                var projectId = dbProject["Id"].AsInt();

                var projectRequirements = requirements.First(r => r.Id == projectId);

                projects.Add(new Project(projectRequirements.Value)
                {
                    Id = projectId,
                    Definition = new ProjectDefinition
                    {
                        Id = dbProject["ProjectDefinitionId"].AsShort()
                    },
                    Value = dbProject["Value"].AsInt(),
                    Deadline = dbProject["Deadline"].AsDateTime(),
                    ExtensionDeadline = dbProject["ExtensionDeadline"].As<DateTime?>(),
                    ReputationRequired = (StarCategory)dbProject["ReputationRequired"].AsByte(),
                    Status = (ProjectStatus)dbProject["StatusId"].AsByte(),
                    Result = (ProjectResult?)dbProject["ResultId"].As<byte?>(),
                });
            }

            return new ProjectServiceData
            {
                Projects = projects
            };
        }
    }
}