using IWS5.Core.Constants;
using IWS5.Core.Enums;
using IWS5.Core.Helpers;
using IWS5.Core.Models.Request;
using IWS5.DatabaseAccess.Interfaces;
using IWS5.DatabaseAccess.IwsDataContext;
using IWS5.DatabaseAccess.IwsDataModels;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.Extensions.DependencyInjection;
using Serilog;
using System.Linq.Dynamic.Core;

namespace IWS5.DatabaseAccess.Repositories;

public class DocumentCabinetRepository(GreenfieldPd2015Context context, ILogger logger)
    : IWSRepository<AtdocumentCabinet>(context), IDocumentCabinetRepository
{
    public async Task<AtdocumentCabinet[]> GetProcessDefaultDocumentTemplate()
    {
        var query = context.AtdocumentCabinets
            .Include(cabinet => cabinet.TemplateType);

        var data = await query.Select(template => new AtdocumentCabinet
        {
            CabinetId = template.CabinetId,
            CabinetName = template.CabinetName,
            CabinetDescription = template.CabinetDescription,
            TemplateTypeId = template.TemplateTypeId,
            TemplateType = new AttemplateType { Name = template.TemplateType.Name },
            CreateDate = template.CreateDate,
            ModifyDate = template.ModifyDate,
            ObjectId = template.ObjectId
        }).ToArrayAsync();

        return data;
    }

    public async Task<AtdocumentCabinet> GetProcessDefaultDocumentTemplate(int processId)
    {
        var template = await context.AtprocessDefinitions
            .Include(process => process.DefaultDocTemplateNavigation)
            .ThenInclude(template => template.AtdocumentCabinetDefs)
            .Where(process => process.ProcessId == processId)
            .Select(process => new AtdocumentCabinet
            {
                CabinetId = process.DefaultDocTemplateNavigation.CabinetId,
                ObjectId = process.DefaultDocTemplateNavigation.ObjectId,
                CabinetName = process.DefaultDocTemplateNavigation.CabinetName,
                CabinetDescription = process.DefaultDocTemplateNavigation.CabinetDescription,
                TemplateTypeId = process.DefaultDocTemplateNavigation.TemplateTypeId,
                TemplateType = new AttemplateType { Name = process.DefaultDocTemplateNavigation.TemplateType.Name },
                CreateDate = process.DefaultDocTemplateNavigation.CreateDate,
                ModifyDate = process.DefaultDocTemplateNavigation.ModifyDate,
                AtdocumentCabinetDefs = process.DefaultDocTemplateNavigation.AtdocumentCabinetDefs.Select(templateDef =>
                    new AtdocumentCabinetDef
                    {
                        FieldId = templateDef.FieldId,
                        FieldName = templateDef.FieldName,
                        FieldDescription = templateDef.FieldDescription,
                        FieldDataTypeId = templateDef.FieldDataTypeId,
                        FieldDataType = templateDef.FieldDataType,
                        FieldSize = templateDef.FieldSize,
                        DefaultValue = templateDef.DefaultValue,
                        Display = templateDef.Display,
                        ObjectId = templateDef.ObjectId
                    }).ToArray()
            }).FirstOrDefaultAsync();

        return template;
    }

    public async Task<AtdocumentCabinet> GetAllowedTemplateFields(string userName, int templateId)
    {
        var rights = context.Atusers
            .Where(user => user.UserName == userName)
            .SelectMany(user => user.Parents)
            .SelectMany(parent => parent.Atrights)
            .Where(right => right.ObjectType == (int)ObjectType.DocumentField &&
                            (right.AllowRights == (int)ATRightAllowRight.ViewDocField ||
                             right.AllowRights == (int)ATRightAllowRight.EditDocField))
            .Select(right => right.ObjectGuid);

        var documentTemplate = context.AtdocumentCabinets
            .Include(cabinet => cabinet.AtdocumentCabinetDefs)
            .Where(cabinet => cabinet.CabinetId == templateId)
            .Select(cabinet => new AtdocumentCabinet
            {
                CabinetId = cabinet.CabinetId,
                CabinetName = cabinet.CabinetName,
                AtdocumentCabinetDefs = cabinet.AtdocumentCabinetDefs
                    .Where(def => rights.Contains(def.ObjectId))
                    .Select(def => new AtdocumentCabinetDef
                    {
                        FieldId = def.FieldId,
                        FieldName = def.FieldName,
                        FieldDataTypeId = def.FieldDataTypeId.Value,
                        FieldDataType = def.FieldDataType,
                        FieldSize = def.FieldSize,
                        DefaultValue = def.DefaultValue,
                        Display = def.Display,
                        ObjectId = def.ObjectId
                    }).ToList()
            });
        
        logger.Information("Successfully retrieved allowed fields for template {TemplateId} for user {UserName}", templateId, userName);
        logger.Information("used query: {Query}", documentTemplate.ToQueryString());

        return await documentTemplate.FirstOrDefaultAsync();
    }

    public async Task<List<AtdocumentCabinet>> GetDocumentTemplatesByIds(Guid[] objectIds)
    {
        var query = await context.AtdocumentCabinets
            .AsNoTracking()
            .Include(template => template.AtdocumentCabinetDefs)
            .Where(template => objectIds.Contains(template.ObjectId))
            .ToListAsync();

        var results = query.Select(queryItem => new AtdocumentCabinet
        {
            CabinetName = queryItem.CabinetName,
            ObjectId = queryItem.ObjectId,
            AtdocumentCabinetDefs = queryItem.AtdocumentCabinetDefs.Select(templateDef => new AtdocumentCabinetDef
            {
                FieldId = templateDef.FieldId,
                ObjectId = templateDef.ObjectId,
                FieldName = templateDef.FieldName
            }).ToList()
        }).ToList();

        return results;
    }

    public async Task<AtdocumentCabinet> GetDocumentTemplateById(int templateId)
    {
        var query = context.AtdocumentCabinets
            .Include(template => template.TemplateType)
            .Include(template => template.AtdocumentCabinetDefs);

        var data = await query.Select(template => new AtdocumentCabinet
        {
            CabinetId = template.CabinetId,
            CabinetName = template.CabinetName,
            TemplateTypeId = template.TemplateTypeId,
            TemplateType = new AttemplateType { Name = template.TemplateType.Name },
            CabinetDescription = template.CabinetDescription,
            DbConnectionId = template.DbConnectionId,
            DbName = template.DbName,
            AtdocumentCabinetDefs = template.AtdocumentCabinetDefs.Select(templateDef => new AtdocumentCabinetDef
            {
                FieldId = templateDef.FieldId,
                FieldName = templateDef.FieldName,
                FieldDescription = templateDef.FieldDescription,
                FieldDataType = templateDef.FieldDataType,
                FieldSize = templateDef.FieldSize,
                DefaultValue = templateDef.DefaultValue,
                Display = templateDef.Display
            }).ToArray()
        }).FirstOrDefaultAsync(templateDef => templateDef.CabinetId == templateId);

        return data;
    }

    public async Task CreateDocumentTemplate(CreateDocumentTemplateRequest request)
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                logger.Information("Starting to create document template: {TemplateName}", request.TemplateName);

                var template = new AtdocumentCabinet
                {
                    CabinetName = request.TemplateName,
                    CabinetDescription = request.Description,
                    DbConnectionId = request.DataConnectionId,
                    DbName = request.DatabaseName
                };

                logger.Information("Created AtdocumentCabinet object for template: {TemplateName}",
                    request.TemplateName);

                context.AtdocumentCabinets.Add(template);
                context.SaveChanges();

                logger.Information("Created AtdocumentCabinetDef objects for template: {TemplateName}",
                    request.TemplateName);

                foreach (var field in request.Fields)
                {
                    var newDef = new AtdocumentCabinetDef
                    {
                        FieldName = field.Name,
                        CabinetId = template.CabinetId,
                        FieldDescription = field.Description,
                        FieldDataType = field.FieldType.ToString(),
                        FieldDataTypeId = (int)field.FieldType,
                        FieldSize = field.Size ?? 0,
                        DefaultValue = field.DefaultValue?.ToString(),
                        Display = true
                    };
                    context.AtdocumentCabinetDefs.Add(newDef);
                }

                logger.Information(
                    "Added AtdocumentCabinet and AtdocumentCabinetDef objects to context for template: {TemplateName}",
                    request.TemplateName);

                context.SaveChanges();

                logger.Information("Saved changes to context for template: {TemplateName}", request.TemplateName);

                await CreateTemplateRelatedTable(template, request.Fields);

                logger.Information("Created related table for template: {TemplateName}", request.TemplateName);

                await transaction.CommitAsync();

                logger.Information("Transaction committed for template: {TemplateName}", request.TemplateName);
            }
            catch (Exception ex)
            {
                logger.Error(ex, "Error creating document template: {TemplateName}", request.TemplateName);
                transaction.Rollback();
                throw;
            }
        }
    }

    public async Task UpdateDocumentTemplate(UpdateDocumentTemplateRequest request)
    {
        using (var transaction = context.Database.BeginTransaction())
        {
            try
            {
                logger.Information("Starting to update document template: {TemplateId}", request.TemplateId);

                var template = await context.AtdocumentCabinets
                    .Include(cabinet => cabinet.AtdocumentCabinetDefs)
                    .FirstOrDefaultAsync(cabinet => cabinet.CabinetId == request.TemplateId);

                if (template == null)
                {
                    logger.Error("Document template with ID {TemplateId} not found.", request.TemplateId);
                    throw new InvalidOperationException($"Document template with ID {request.TemplateId} not found.");
                }

                // Update template properties
                template.CabinetName = request.TemplateName;
                template.CabinetDescription = request.Description;
                template.DbConnectionId = request.DataConnectionId;
                template.DbName = request.DatabaseName;

                logger.Information("Updated properties for document template: {TemplateId}", request.TemplateId);

                // Update fields
                var existingFields = template.AtdocumentCabinetDefs;
                var requestFields = request.Fields;

                // Fields to add
                var fieldsToAdd = requestFields
                    .Where(requestField =>
                        !existingFields.Any(existingField => existingField.FieldName == requestField.Name))
                    .ToArray();

                // Fields to remove
                var fieldsToRemove = existingFields
                    .Where(existingField =>
                        !requestFields.Any(requestField => requestField.Name == existingField.FieldName))
                    .ToList();

                logger.Information(
                    "Identified {FieldsToAddCount} fields to add and {FieldsToRemoveCount} fields to remove for template: {TemplateId}",
                    fieldsToAdd.Length, fieldsToRemove.Count, request.TemplateId);

                var fields = new List<AtdocumentCabinetDef>();

                foreach (var field in fieldsToAdd)
                {
                    var newDef = new AtdocumentCabinetDef
                    {
                        FieldName = field.Name,
                        CabinetId = request.TemplateId,
                        FieldDescription = field.Description,
                        FieldDataType = field.FieldType.ToString(),
                        FieldDataTypeId = (int)field.FieldType,
                        FieldSize = field.Size ?? 0,
                        DefaultValue = field.DefaultValue,
                        Display = field.Display,
                    };
                    fields.Add(newDef);
                    context.AtdocumentCabinetDefs.Add(newDef);
                }

                logger.Information("Added {FieldsToAddCount} new fields to context for template: {TemplateId}",
                    fieldsToAdd.Length, request.TemplateId);

                context.AtdocumentCabinetDefs.RemoveRange(fieldsToRemove);
                logger.Information("Removed {FieldsToRemoveCount} fields from context for template: {TemplateId}",
                    fieldsToRemove.Count, request.TemplateId);

                context.SaveChanges();
                logger.Information("Saved changes to context for template: {TemplateId}", request.TemplateId);

                var migrationBuilder = new MigrationBuilder(context.Database.ProviderName);
                var tableName = $"ATD_{request.TemplateName}";

                var createColumnOperations = CreateAddColumnOperations(tableName, fieldsToAdd);
                migrationBuilder.Operations.AddRange(createColumnOperations);
                var dropColumnOperations = CreateDropColumnOperations(tableName,
                    fieldsToRemove.Select(field => field.FieldName).ToArray());
                migrationBuilder.Operations.AddRange(dropColumnOperations);

                var sqlGenerator = context.GetService<IMigrationsSqlGenerator>();
                var commands = sqlGenerator.Generate(migrationBuilder.Operations, context.Model);

                var sql = string.Join(" ", commands.Select(c => c.CommandText));

                logger.Information("Generated SQL query: {sql}", sql);
                logger.Information("Executing SQL to update table: {TableName}", tableName);

                await context.Database.ExecuteSqlRawAsync(sql);
                await transaction.CommitAsync();

                logger.Information("Transaction committed for template: {TemplateId}", request.TemplateId);
            }
            catch (Exception ex)
            {
                logger.Error(ex, "Error updating document template: {TemplateId}", request.TemplateId);
                transaction.Rollback();
                throw;
            }
        }
    }

    private async Task CreateTemplateRelatedTable(AtdocumentCabinet cabinet, DocumentTemplateField[] fields)
    {
        logger.Information("Starting to create related table for cabinet: {CabinetName}", cabinet.CabinetName);

        var migrationBuilder = new MigrationBuilder(context.Database.ProviderName);
        var tableName = DocumentTemplateTableConstants.GetDocumentTemplateTableName(cabinet.CabinetName);

        logger.Information("Creating table: {TableName}", tableName);

        migrationBuilder.CreateTable(
            name: tableName,
            columns: table => new
            {
                InstanceId = table.Column<int>(nullable: false,
                        name: DocumentTemplateTableConstants.InstanceIdColumnName)
                    .Annotation("SqlServer:Identity", "1, 1"),
                DocumentId = table.Column<int>(nullable: false,
                    name: DocumentTemplateTableConstants.DocumentIdColumnName)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_" + cabinet.CabinetName, x => x.InstanceId);
                table.ForeignKey(name: "FK_" + cabinet.CabinetName + "_Document",
                    x => x.DocumentId,
                    principalTable: "ATDocument",
                    principalColumn: "DocumentId");
            });

        logger.Information("Adding columns to table: {TableName}", tableName);

        var createColumnOperations = CreateAddColumnOperations(tableName, fields);
        migrationBuilder.Operations.AddRange(createColumnOperations);

        var sqlGenerator = context.GetService<IMigrationsSqlGenerator>();
        var commands = sqlGenerator.Generate(migrationBuilder.Operations, context.Model);

        var sql = string.Join(" ", commands.Select(c => c.CommandText));

        logger.Information("Generated query: {sql}", sql);
        logger.Information("Executing SQL to create table: {TableName}", tableName);

        await context.Database.ExecuteSqlRawAsync(sql);

        logger.Information("Successfully created related table: {TableName} for cabinet: {CabinetName}", tableName,
            cabinet.CabinetName);
    }

    private List<AddColumnOperation> CreateAddColumnOperations(string tableName, DocumentTemplateField[] fields)
    {
        var addColumnOperations = new List<AddColumnOperation>();

        foreach (var field in fields)
        {
            var addColumnOperation = new AddColumnOperation
            {
                Name = field.Name,
                Table = tableName,
                ClrType = TypeHelpers.GetClrType(field.FieldType),
                ColumnType = TypeHelpers.GetSqlType(field.FieldType),
                MaxLength = field.Size,
                IsNullable = field.IsNullable,
                DefaultValue = TypeHelpers.ParseValue(field.FieldType, field.DefaultValue)
            };

            addColumnOperations.Add(addColumnOperation);
        }

        return addColumnOperations;
    }

    private List<DropColumnOperation> CreateDropColumnOperations(string tableName, string[] fields)
    {
        return fields.Select(field => new DropColumnOperation
        {
            Name = field,
            Schema = "dbo",
            Table = tableName
        }).ToList();
    }
}