using System.Globalization;
using IWS5.Core.Enums;
using IWS5.Core.Models.Request;
using IWS5.DatabaseAccess.Interfaces;
using IWS5.DatabaseAccess.IwsDataContext;
using IWS5.DatabaseAccess.IwsDataModels;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Serilog;

namespace IWS5.DatabaseAccess.Repositories;

public class FieldDefinitionRepository(GreenfieldPd2015Context context, ILogger logger)
    : IWSRepository<AtfieldDefinition>(context: context), IFieldDefinitionRepository
{
    public async Task<List<AtfieldDefinition>> ListFieldDefinitionsAndInstancesByWorkItemReferenceIdAsync(int workItemReferenceId, string userName)
    {
        var allowedFields = context.Atusers
            .Where(atUser => atUser.UserName == userName)
            .SelectMany(atUser => atUser.Parents)
            .SelectMany(atParent => atParent.Atrights)
            .Where(atRight => atRight.ObjectType == (int)ObjectType.FolderField 
                              && (atRight.AllowRights == (int)ATRightAllowRight.ViewFolderField))
            .Select(atRight => atRight.ObjectGuid);
        
        var currentProcessId = await context.AtworkItemReferences
            .Where(wiReference => wiReference.WorkItemReferenceId == workItemReferenceId)
            .Select(wiReference => wiReference.WorkItem.ProcessId)
            .FirstOrDefaultAsync();
        
        var workItemId = await context.AtworkItemReferences
            .Where(wiReference => wiReference.WorkItemReferenceId == workItemReferenceId)
            .Select(wiReference => wiReference.WorkItem.WorkItemId)
            .FirstOrDefaultAsync();
        
        var fieldDefinitions = await context.AtfieldDefinitions
            .Where(fd => fd.ProcessId == currentProcessId && fd.Display && allowedFields.Contains(fd.ObjectId))
            .Include(fDefinition => fDefinition.AtfieldInstanceDates.Where(fid => fid.WorkItemId == workItemId))
            .Include(fDefinition => fDefinition.AtfieldInstanceNumbers.Where(fin => fin.WorkItemId == workItemId))
            .Include(fDefinition => fDefinition.AtfieldInstanceStrings.Where(fis => fis.WorkItemId == workItemId))
            .Include(fDefinition => fDefinition.AtfieldInstanceOptions.Where(fis => fis.WorkItemId == workItemId))
            .ToListAsync();

        return fieldDefinitions;
    }

    public async Task<List<AtfieldDefinition>> ListFieldDefinitionsAndInstancesByWorkItemIdAndQueueIdAsync(
        int[] workItemIds,
        int queueId,
        string userName)
    {
        var allowedFields = context.Atusers
            .Where(atUser => atUser.UserName == userName)
            .SelectMany(atUser => atUser.Parents)
            .SelectMany(atParent => atParent.Atrights)
            .Where(atRight => atRight.ObjectType == (int)ObjectType.FolderField 
                              && atRight.AllowRights == (int)ATRightAllowRight.ViewFolderField)
            .Select(atRight => atRight.ObjectGuid);
        
        var currentProcessId = await context.Atqueues
            .Where(q => q.Id == queueId)
            .Select(q => q.ProcessId)
            .FirstOrDefaultAsync();

        var fieldDefinitions = await context.AtfieldDefinitions
            .Where(fd => fd.ProcessId == currentProcessId && fd.Display && allowedFields.Contains(fd.ObjectId))
            .Include(fDefinition => fDefinition.AtfieldInstanceDates.Where(fid => workItemIds.Contains(fid.WorkItemId)))
            .Include(fDefinition => fDefinition.AtfieldInstanceNumbers.Where(fin => workItemIds.Contains(fin.WorkItemId)))
            .Include(fDefinition => fDefinition.AtfieldInstanceStrings.Where(fis => workItemIds.Contains(fis.WorkItemId)))
            .Include(fDefinition => fDefinition.AtfieldInstanceOptions.Where(fis => workItemIds.Contains(fis.WorkItemId)))
            .ToListAsync();

        return fieldDefinitions;
    }

    public async Task<int> UpdateFieldListOfValues(List<UpdateFolderFieldValueRequest> dateFields,
        List<UpdateFolderFieldValueRequest> numberFields,
        List<UpdateFolderFieldValueRequest> stringFields,
        List<UpdateFolderFieldValueRequest> optionFields,
        int workItemId)
    {
        try
        {
            foreach (var field in dateFields)
            {
                var fieldInstanceDate = context.AtfieldInstanceDates.FirstOrDefault(instance =>
                    instance.FieldId == field.FieldId && instance.WorkItemId == workItemId);
                if (fieldInstanceDate != null)
                {
                    fieldInstanceDate.FieldValue = DateTime.Parse(field.NewValue);
                    context.AtfieldInstanceDates.Update(fieldInstanceDate);
                }
                else
                {
                    context.AtfieldInstanceDates.Add(new AtfieldInstanceDate
                    {
                        FieldId = field.FieldId,
                        WorkItemId = workItemId,
                        FieldName = field.FieldName,
                        FieldValue =  DateTime.Parse(field.NewValue)
                    });
                }
            }

            foreach (var field in numberFields)
            {
                var fieldInstanceNumber = context.AtfieldInstanceNumbers.FirstOrDefault(instance =>
                    instance.FieldId == field.FieldId && instance.WorkItemId == workItemId);
                if (fieldInstanceNumber != null)
                {
                    fieldInstanceNumber.FieldValue = decimal.Parse(field.NewValue);
                    context.AtfieldInstanceNumbers.Update(fieldInstanceNumber);
                }
                else
                {
                    context.AtfieldInstanceNumbers.Add(new AtfieldInstanceNumber
                    {
                        FieldId = field.FieldId,
                        WorkItemId = workItemId,
                        FieldName = field.FieldName,
                        FieldValue = decimal.Parse(field.NewValue)
                    });
                }
            }

            foreach (var field in stringFields)
            {
                var fieldInstanceString = context.AtfieldInstanceStrings.FirstOrDefault(instance =>
                    instance.FieldId == field.FieldId && instance.WorkItemId == workItemId);
                if (fieldInstanceString != null) {

                    fieldInstanceString.FieldValue = field.NewValue;
                    context.AtfieldInstanceStrings.Update(fieldInstanceString);
                }
                else
                {
                    context.AtfieldInstanceStrings.Add(new AtfieldInstanceString
                    {
                        FieldId = field.FieldId,
                        WorkItemId = workItemId,
                        FieldName = field.FieldName,
                        FieldValue = field.NewValue
                    });
                }
            }

            foreach (var field in optionFields)
            {
                var fieldInstanceOption = context.AtfieldInstanceOptions.FirstOrDefault(instance =>
                    instance.FieldId == field.FieldId && instance.WorkItemId == workItemId);
                if (fieldInstanceOption != null)
                {
                    fieldInstanceOption.FieldValue = field.NewValue;
                    context.AtfieldInstanceOptions.Update(fieldInstanceOption);
                } else
                {
                    context.AtfieldInstanceOptions.Add(new AtfieldInstanceOption
                    {
                        FieldId = field.FieldId,
                        WorkItemId = workItemId,
                        FieldName = field.FieldName,
                        FieldValue = field.NewValue
                    });
                }
            }
            var updatedFieldsAmount = await context.SaveChangesAsync();
            logger.Information("Successfully updated folder {updatedFieldsAmount} fields", updatedFieldsAmount);
            return updatedFieldsAmount;
        }
        catch (Exception e)
        {
            logger.Error(e, "Error updating field list of values");
            throw;
        }
    }

    public async Task<List<string>> ListFieldOptionsByInitialValueSqlAsync(string initialValueSql)
    {
        List<string> fieldOptions;
        try
        {
            fieldOptions = await context.Database.SqlQueryRaw<string>(initialValueSql).ToListAsync();
        } catch (SqlException ex)
        {
            logger.Error(ex, $"Invalid SQL in InitialValue: {initialValueSql}. Error: {ex.Message}");
            fieldOptions = new List<string>();
        }
        return fieldOptions;
    }

    public async Task<List<string>> ListFieldOptionsByFieldIdAsync(int fieldId)
    {
        var fieldDefinition = await context.AtfieldDefinitions.Where(fDef => fDef.FieldId == fieldId).FirstOrDefaultAsync();
        var fieldOptions = await ListFieldOptionsByInitialValueSqlAsync(fieldDefinition.InitialValue);
        return fieldOptions;
    }
}