using IWS5.Core.Constants;
using IWS5.Core.Enums;
using IWS5.Core.Models.Request;
using IWS5.DatabaseAccess.Helpers;
using IWS5.DatabaseAccess.Interfaces;
using IWS5.DatabaseAccess.IwsDataContext;
using IWS5.DatabaseAccess.IwsDataModels;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using System.Reflection.Metadata;

namespace IWS5.DatabaseAccess.Repositories;

public class DocumentRepository(GreenfieldPd2015Context context)
    : IWSRepository<Atdocument>(context), IDocumentRepository
{
    public async Task<List<Atdocument>> ListDocumentsByWorkItemReference(DocumentsRequest documentRequest)
    {
        // First part: Get permissions by username
        var rightsQuery = from user in context.Atusers
            where user.UserName == documentRequest.UserName
            from parent in user.Parents
            from right in parent.Atrights
            where right.ObjectType == (int)ObjectType.DocumentField &&
                  (right.AllowRights == (int)ATRightAllowRight.ViewDocField ||
                   right.AllowRights == (int)ATRightAllowRight.EditDocField)
            select right.ObjectGuid;

        // Second part: Get documents by workItemReferenceId and filter document fields by DisplayOrder and DocumentName
        var documentsQuery = from wiReference in context.AtworkItemReferences
            join document in context.Atdocuments on wiReference.WorkItemId equals document.WorkItemId
            where wiReference.WorkItemReferenceId == documentRequest.WorkItemReferenceId
            && document.ReviewStatusId != (int)ATDocumenctReviewStatus.Deleted
            orderby document.DisplayOrder, document.DocumentName
            select new Atdocument
            {
                DocumentId = document.DocumentId,
                DocumentName = document.DocumentName,
                Document = document.Document == null || !documentRequest.IncludingDocumentContent ? null : Array.Empty<byte>(),
                DocumentSize = document.DocumentSize,
                DocumentLink = document.DocumentLink,
                UserName = document.UserName,
                LastModified = document.LastModified,
                CabinetId = document.CabinetId,
                DisplayOrder = document.DisplayOrder,
                Cabinet = new AtdocumentCabinet
                {
                    CabinetId = document.Cabinet.CabinetId,
                    CabinetName = document.Cabinet.CabinetName,
                    AtdocumentCabinetDefs = document.Cabinet.AtdocumentCabinetDefs
                        .Where(field => rightsQuery.Contains(field.ObjectId))
                        .Select(field => new AtdocumentCabinetDef
                        {
                            FieldId = field.FieldId,
                            FieldName = field.FieldName,
                            FieldDataType = field.FieldDataType,
                            DefaultValue = field.DefaultValue,
                            FieldDataTypeId = field.FieldDataTypeId,
                            ObjectId = field.ObjectId
                        }).ToList()
                }
            };

        var documents = await documentsQuery.ToListAsync();
        return documents;
    }

    public async Task<List<Dictionary<string, object>>> GetTemplateDataAsync(string tableName, List<string> fields,
        int[] documentIds)
    {
        // Construct the SQL query
        var fieldNames = string.Join(", ", fields);
        var parameterNames = string.Join(", ", documentIds.Select((id, index) => $"@documentId{index}"));
        var sqlQuery = $"SELECT {fieldNames} FROM {DocumentTemplateTableConstants.GetDocumentTemplateTableName(tableName)} WHERE DocumentId IN ({parameterNames})";

        // Execute the query and retrieve the results
        var result = new List<Dictionary<string, object>>();
        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = sqlQuery;

            for (int i = 0; i < documentIds.Length; i++)
            {
                command.Parameters.Add(new SqlParameter($"@documentId{i}", documentIds[i]));
            }

            await context.Database.OpenConnectionAsync();
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    var expandoObject = new Dictionary<string, object>();
                    foreach (var field in fields)
                    {
                        expandoObject[field] = reader[field];
                    }
                    result.Add(expandoObject);
                }
            }
            await context.Database.CloseConnectionAsync();
        }

        return result;
    }

    public async Task<Atdocument> RenameDocumentAsync(string userName, RenameDocumentRequest renameDocumentRequest) {
        await context.Atdocuments
            .Where(doc => doc.DocumentId == renameDocumentRequest.DocumentId)
            .ExecuteUpdateAsync(setters => setters
                .SetProperty(doc => doc.DocumentName, renameDocumentRequest.NewName)
                .SetProperty(doc => doc.UserName, userName)
                .SetProperty(doc => doc.LastModified, DateTime.Now));

        var result = await context.Atdocuments
            .Where(doc => doc.DocumentId == renameDocumentRequest.DocumentId)
            .Select(doc => new Atdocument {
                DocumentId = doc.DocumentId,
                Document = doc.Document,
                DocumentName = doc.DocumentName,
                DisplayOrder = doc.DisplayOrder,
                DocumentSize = doc.DocumentSize,
                UserName = doc.UserName,
                LastModified = doc.LastModified,
                CabinetId = doc.CabinetId,
                Cabinet = new AtdocumentCabinet {
                    CabinetId = doc.Cabinet.CabinetId,
                    CabinetName = doc.Cabinet.CabinetName
                }
            }).FirstOrDefaultAsync();

        return result;
    }

    public async Task<Atdocument> UpdateDocumentContentAsync(string userName, UpdateDocumentContentRequest updateDocumentContentRequest) 
    {
        await using var transaction = await context.Database.BeginTransactionAsync();
        try {
            var workItem = await context.AtworkItemReferences
                .FirstOrDefaultAsync(wi => wi.WorkItemReferenceId == updateDocumentContentRequest.WorkItemReferenceId);

            var document = await context.Atdocuments
                .Include(doc => doc.Cabinet)
                .FirstOrDefaultAsync(doc => doc.DocumentId == updateDocumentContentRequest.DocumentId);

            if (document.WorkItemId != workItem.WorkItemId) {
                throw new InvalidOperationException($"Document's WorkItemId {document.WorkItemId} does not match WorkItemId {workItem.WorkItemId}.");
            }

            document.DocumentSize = updateDocumentContentRequest.DocumentFile.Length;
            document.Document = updateDocumentContentRequest.DocumentFile;
            document.UserName = userName;
            document.LastModified = DateTime.Now;

            context.Atdocuments.Update(document);
            await context.SaveChangesAsync();

            await transaction.CommitAsync();

            return document;
        } catch (Exception) {
            await transaction.RollbackAsync();
            throw;
        }
    }

    public async Task UpdateDocumentFields(string userName, UpdateDocumentFieldListRequest updateDocumentFieldListRequest)
    {
        var documentTemplateTableName = DocumentTemplateTableConstants.GetDocumentTemplateTableName(updateDocumentFieldListRequest.DocumentTemplateName);

        var setClauses = updateDocumentFieldListRequest.FieldsToUpdate
            .Select((field, index) => $"{field.FieldName} = @FieldValue{index}")
            .ToList();

        var query = $"UPDATE {documentTemplateTableName} SET {string.Join(", ", setClauses)} WHERE DocumentId = @DocumentId";

        using (var command = context.Database.GetDbConnection().CreateCommand())
        {
            command.CommandText = query;
            command.Parameters.Add(new SqlParameter("@DocumentId", updateDocumentFieldListRequest.DocumentId));

            for (int i = 0; i < updateDocumentFieldListRequest.FieldsToUpdate.Count; i++)
            {
                command.Parameters.Add(new SqlParameter($"@FieldValue{i}", updateDocumentFieldListRequest.FieldsToUpdate[i].FieldValue));
            }

            await context.Database.OpenConnectionAsync();
            await command.ExecuteNonQueryAsync();
            await context.Database.CloseConnectionAsync();
        }
    }

    public async Task ChangeTemplateForDocument(string userName, string templateToRemoveRecord, UpdateDocumentFieldListRequest updateDocumentFieldListRequest)
    {
        using (var transaction = await context.Database.BeginTransactionAsync())
        {
            try
            {
                var document = await context.Atdocuments
                    .Include(doc => doc.Cabinet)
                    .FirstOrDefaultAsync(doc => doc.DocumentId == updateDocumentFieldListRequest.DocumentId);
                
                document.CabinetId = updateDocumentFieldListRequest.DocumentTemplateId;
                context.Atdocuments.Update(document);
                await context.SaveChangesAsync();

                // Remove record from the old template table
                var deleteQuery = $"DELETE FROM {DocumentTemplateTableConstants.GetDocumentTemplateTableName(templateToRemoveRecord)} WHERE DocumentId = @DocumentId";
                await context.Database.ExecuteSqlRawAsync(deleteQuery, new SqlParameter("@DocumentId", updateDocumentFieldListRequest.DocumentId));

                // Insert record into the new template table
                await DocumentTemplateDefTableHelper.InsertRecordIntoTemplateTableAsync(
                    updateDocumentFieldListRequest.DocumentTemplateName,
                    updateDocumentFieldListRequest.DocumentId,
                    updateDocumentFieldListRequest.FieldsToUpdate,
                    context);

                // Commit transaction
                await transaction.CommitAsync();
            }
            catch
            {
                // Rollback transaction in case of error
                await transaction.RollbackAsync();
                throw;
            }
        }
    }

    public async Task<bool> DeleteDocumentAsync(int documentId, bool permanently)
    {
        var document = await context.Atdocuments
                        .Include(doc => doc.Cabinet)
                        .FirstOrDefaultAsync(doc => doc.DocumentId == documentId);

        if (document == null) return false;

        if (permanently)
        {
            await PermanentlyDeleteDocument(document);
        }
        else
        {
            await SoftDeleteDocument(document);
        }

        await context.SaveChangesAsync();
        return true;
    }

    public async Task<Atdocument> AddDocumentAsync(string userName, AddDocumentRequest document)
    {
        await using var transaction = await context.Database.BeginTransactionAsync();
        try
        {
            var workItem = await context.AtworkItemReferences
                .FirstOrDefaultAsync(wi => wi.WorkItemReferenceId == document.WorkItemId);

            if (workItem == null)
            {
                throw new InvalidOperationException($"Work item with ID {document.WorkItemId} does not exist.");
            }

            var cabinet = await context.AtdocumentCabinets
                .Include(c => c.AtdocumentCabinetDefs)
                .FirstOrDefaultAsync(c => c.CabinetId == document.DocumentTemplateId);

            if (cabinet == null)
            {
                throw new InvalidOperationException($"Cabinet with ID {document.DocumentTemplateId} does not exist.");
            }

            var newDocument = new Atdocument
            {
                DocumentName = document.DocumentName,
                ReviewStatusId = (int)ATDocumenctReviewStatus.Submitted,
                ReviewNumber = 1,
                CabinetId = document.DocumentTemplateId,
                WorkItemId = workItem.WorkItemId,
                DocumentSize = document.DocumentFile.Length,
                Document = document.DocumentFile,
                DocumentLink = null,
                UserName = userName,
                LastModified = DateTime.Now,
                Cabinet = cabinet,
                DisplayOrder = document.DisplayOrder
            };

            context.Atdocuments.Add(newDocument);
            await context.SaveChangesAsync();

            // Add record to ATD_template table
            await DocumentTemplateDefTableHelper.InsertRecordIntoTemplateTableAsync(
                document.DocumentTemplateName,
                newDocument.DocumentId,
                document.Fields,
                context);

            await transaction.CommitAsync();

            return newDocument;
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }


    private async Task PermanentlyDeleteDocument(Atdocument document)
    {
        var templateName = document.Cabinet?.CabinetName;
        var documentNotes = context.Atnotes.Where(note => note.DocumentId == document.DocumentId);
        
        if (documentNotes.Any())
        {
            context.Atnotes.RemoveRange(documentNotes);
        }
        
        if (!string.IsNullOrEmpty(templateName))
        {
            var sql = $"DELETE FROM {DocumentTemplateTableConstants.GetDocumentTemplateTableName(templateName)} WHERE DocumentId = @DocumentId";

            await context.Database.ExecuteSqlRawAsync(sql, new[] { new SqlParameter("@DocumentId", document.DocumentId) });
        }

        context.Atdocuments.Remove(document);
    }

    private async Task SoftDeleteDocument(Atdocument document)
    {
        var deletedStatus = await context.AtdocumenctReviewStatuses
            .FirstOrDefaultAsync(status => status.Id == 10);
        if (deletedStatus == null)
        {
            throw new InvalidOperationException("The 'Deleted' review status (Id: 10) is missing in the database.");
        }

        document.ReviewStatus = deletedStatus;
        context.Atdocuments.Update(document);
    }

    
}