using System.Reflection;
using System.Text;
using Equibles.AgentQL.Attributes;
using Equibles.AgentQL.Configuration;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace Equibles.AgentQL.EntityFrameworkCore.Schema;

public class SchemaProvider<TContext> : ISchemaProvider
    where TContext : DbContext
{
    private readonly TContext _context;
    private readonly AgentQLOptions _options;
    private string _cachedSchema;

    public SchemaProvider(TContext context, AgentQLOptions options)
    {
        _context = context;
        _options = options;
    }

    public async Task<string> GetSchemaDescription()
    {
        if (!string.IsNullOrEmpty(_cachedSchema))
            return _cachedSchema;

        var schema = new StringBuilder();
        schema.AppendLine("=== DATABASE SCHEMA ===");
        schema.AppendLine("Generated by Entity Framework Core using Code First approach");
        var databaseInfo = await GetDatabaseInfo();
        schema.AppendLine($"Database: {databaseInfo}");
        schema.AppendLine();

        var inheritanceInfo = AnalyzeInheritancePatterns();
        if (inheritanceInfo.Any())
        {
            schema.AppendLine("=== INHERITANCE PATTERNS ===");
            schema.AppendLine("EF Core inheritance mappings in this database:");
            schema.AppendLine();

            foreach (var info in inheritanceInfo)
            {
                schema.AppendLine(info);
            }

            schema.AppendLine();
        }

        var tables = _context
            .Model.GetRelationalModel()
            .Tables.Where(t => !string.IsNullOrEmpty(t.Name) && HasIncludedEntity(t))
            .OrderBy(t => t.Name);

        schema.AppendLine("=== TABLES ===");
        schema.AppendLine(
            "List of all tables in the database with their columns, relationships, and inheritance patterns:"
        );
        foreach (var table in tables)
        {
            AppendTableInfo(schema, table);
        }

        _cachedSchema = schema.ToString();
        return _cachedSchema;
    }

    private bool HasIncludedEntity(ITable table)
    {
        return table
            .EntityTypeMappings.Select(m => m.TypeBase as IEntityType)
            .Any(e => e != null && !e.IsOwned() && ShouldIncludeEntity(e));
    }

    private IEnumerable<IEntityType> GetNonOwnedEntityTypes()
    {
        return _context.Model.GetEntityTypes().Where(e => !e.IsOwned());
    }

    private bool ShouldIncludeEntity(IEntityType entityType)
    {
        var clrType = entityType.ClrType;

        // Check fluent config first
        if (
            _options.EntityConfigurations.TryGetValue(clrType, out var config)
            && config.Included.HasValue
        )
            return config.Included.Value;

        // Check attribute
        var attr = clrType.GetCustomAttribute<AgentQLEntityAttribute>();
        if (attr != null)
            return true;

        // Fall back to default behavior
        return _options.DefaultBehavior == IncludeBehavior.IncludeAll;
    }

    private bool ShouldIncludeProperty(IEntityType entityType, IProperty property)
    {
        var clrType = entityType.ClrType;
        var propertyName = property.Name;

        // Check fluent property config
        if (_options.EntityConfigurations.TryGetValue(clrType, out var entityConfig))
        {
            if (
                entityConfig.PropertyConfigurations.TryGetValue(propertyName, out var propConfig)
                && propConfig.Included.HasValue
            )
                return propConfig.Included.Value;
        }

        // Check property attributes
        var clrProperty = clrType.GetProperty(propertyName);
        if (clrProperty != null)
        {
            if (clrProperty.GetCustomAttribute<AgentQLIgnoreAttribute>() != null)
                return false;
            if (clrProperty.GetCustomAttribute<AgentQLPropertyAttribute>() != null)
                return true;
        }

        // Fall back to entity's PropertyDefault
        if (entityConfig?.PropertyDefault != null)
            return entityConfig.PropertyDefault == IncludeBehavior.IncludeAll;

        var entityAttr = clrType.GetCustomAttribute<AgentQLEntityAttribute>();
        if (entityAttr != null)
            return entityAttr.PropertyDefault == IncludeBehavior.IncludeAll;

        // Fall back to entity inclusion decision
        return ShouldIncludeEntity(entityType);
    }

    private string GetPropertyDescription(IEntityType entityType, string propertyName)
    {
        var clrType = entityType.ClrType;

        // Check fluent config
        if (_options.EntityConfigurations.TryGetValue(clrType, out var entityConfig))
        {
            if (
                entityConfig.PropertyConfigurations.TryGetValue(propertyName, out var propConfig)
                && propConfig.Description != null
            )
                return propConfig.Description;
        }

        // Check attribute
        var clrProperty = clrType.GetProperty(propertyName);
        var attr = clrProperty?.GetCustomAttribute<AgentQLPropertyAttribute>();
        return attr?.Description;
    }

    private string GetEntityDescription(IEntityType entityType)
    {
        var clrType = entityType.ClrType;

        if (
            _options.EntityConfigurations.TryGetValue(clrType, out var config)
            && config.Description != null
        )
            return config.Description;

        var attr = clrType.GetCustomAttribute<AgentQLEntityAttribute>();
        return attr?.Description;
    }

    private void AppendTableInfo(StringBuilder schema, ITable table)
    {
        var entityTypesInTable = table
            .EntityTypeMappings.Select(m => m.TypeBase as IEntityType)
            .Where(e => e != null && !e.IsOwned())
            .ToList();

        var primaryEntityType = entityTypesInTable
            .OrderBy(e => e.BaseType == null ? 0 : 1)
            .ThenBy(e => e.ClrType.Name)
            .FirstOrDefault();

        if (primaryEntityType == null)
            return;

        schema.AppendLine($"TABLE: \"{table.Name}\"");

        var entityDescription = GetEntityDescription(primaryEntityType);
        if (!string.IsNullOrEmpty(entityDescription))
            schema.AppendLine($"DESCRIPTION: {entityDescription}");

        var inheritanceDetails = GetInheritanceDetailsForTable(primaryEntityType);
        if (!string.IsNullOrEmpty(inheritanceDetails))
            schema.AppendLine($"INHERITANCE: {inheritanceDetails}");

        schema.AppendLine("COLUMNS:");

        var columns = table.Columns.OrderBy(c => c.Name);
        var discriminatorColumn = table.Columns.FirstOrDefault(c =>
            entityTypesInTable.Any(e => e?.FindDiscriminatorProperty()?.GetColumnName() == c.Name)
        );

        foreach (var column in columns)
        {
            if (!ShouldIncludeColumn(column, table, primaryEntityType))
                continue;

            var columnInfo = BuildColumnInfo(column, discriminatorColumn, table);
            var description = GetColumnDescription(column, table, primaryEntityType);
            if (!string.IsNullOrEmpty(description))
                columnInfo += $" -- {description}";
            schema.AppendLine($"  - {columnInfo}");
        }

        var enumInfo = GetEnumInformationForTable(primaryEntityType, table);
        if (enumInfo.Any())
        {
            schema.AppendLine("ENUM VALUES:");
            foreach (var info in enumInfo)
            {
                schema.AppendLine($"  - {info}");
            }
        }

        var foreignKeys = GetForeignKeysForTable(table);
        if (foreignKeys.Any())
        {
            schema.AppendLine("RELATIONSHIPS:");
            foreach (var fk in foreignKeys.Distinct())
            {
                schema.AppendLine($"  - {fk}");
            }
        }

        schema.AppendLine();
    }

    private bool ShouldIncludeColumn(IColumn column, ITable table, IEntityType primaryEntityType)
    {
        // Always include discriminator and PK columns
        var isPrimaryKey = table.PrimaryKey?.Columns.Contains(column) ?? false;
        if (isPrimaryKey)
            return true;

        var isDiscriminator =
            primaryEntityType.FindDiscriminatorProperty()?.GetColumnName() == column.Name;
        if (isDiscriminator)
            return true;

        // Find the property that maps to this column
        foreach (var mapping in table.EntityTypeMappings)
        {
            if (mapping.TypeBase is not IEntityType entityType)
                continue;

            foreach (var colMapping in mapping.ColumnMappings)
            {
                if (colMapping.Column.Name == column.Name)
                {
                    return ShouldIncludeProperty(entityType, colMapping.Property);
                }
            }
        }

        return true;
    }

    private string GetColumnDescription(IColumn column, ITable table, IEntityType primaryEntityType)
    {
        foreach (var mapping in table.EntityTypeMappings)
        {
            if (mapping.TypeBase is not IEntityType entityType)
                continue;

            foreach (var colMapping in mapping.ColumnMappings)
            {
                if (colMapping.Column.Name == column.Name)
                {
                    return GetPropertyDescription(entityType, colMapping.Property.Name);
                }
            }
        }

        return null;
    }

    private async Task<string> GetDatabaseInfo()
    {
        try
        {
            var connection = _context.Database.GetDbConnection();
            var wasOpen = connection.State == System.Data.ConnectionState.Open;

            if (!wasOpen)
                await connection.OpenAsync();

            var databaseName = connection.Database;
            var serverVersion = connection.ServerVersion;
            var dataSource = connection.DataSource;

            if (!wasOpen)
                await connection.CloseAsync();

            var dbType = GetDatabaseTypeFromProvider(_context.Database.ProviderName ?? "Unknown");

            return $"{dbType} (Server: {dataSource}, Database: {databaseName}, Version: {serverVersion})";
        }
        catch (Exception ex)
        {
            return $"Unknown Database (Error: {ex.Message})";
        }
    }

    private static string GetDatabaseTypeFromProvider(string providerName)
    {
        return providerName.ToLowerInvariant() switch
        {
            var p when p.Contains("sqlserver") => "SQL Server",
            var p when p.Contains("mysql") => "MySQL",
            var p when p.Contains("postgresql") || p.Contains("npgsql") => "PostgreSQL",
            var p when p.Contains("sqlite") => "SQLite",
            var p when p.Contains("oracle") => "Oracle",
            _ => providerName,
        };
    }

    private List<string> GetEnumInformationForTable(IEntityType primaryEntityType, ITable table)
    {
        var enumInfo = new List<string>();

        var allProperties = GetAllPropertiesRecursive(primaryEntityType);

        foreach (
            var property in allProperties.Where(p =>
                p.ClrType.IsEnum || (Nullable.GetUnderlyingType(p.ClrType)?.IsEnum == true)
            )
        )
        {
            var actualColumnName = FindActualColumnName(property, table);

            if (!string.IsNullOrEmpty(actualColumnName))
            {
                var enumType = property.ClrType.IsEnum
                    ? property.ClrType
                    : Nullable.GetUnderlyingType(property.ClrType);
                var enumValues = Enum.GetValues(enumType!)
                    .Cast<object>()
                    .Select(v => $"{Convert.ToInt32(v)} = {v}")
                    .ToList();

                var enumInfoStr = $"{actualColumnName}: {string.Join(", ", enumValues)}";
                if (!enumInfo.Contains(enumInfoStr))
                {
                    enumInfo.Add(enumInfoStr);
                }
            }
        }

        return enumInfo;
    }

    private static string FindActualColumnName(IProperty property, ITable table)
    {
        foreach (var column in table.Columns)
        {
            foreach (var entityTypeMapping in table.EntityTypeMappings)
            {
                if (entityTypeMapping.TypeBase is IEntityType entityType)
                {
                    var allProps = GetAllPropertiesRecursive(entityType);
                    var matchingProp = allProps.FirstOrDefault(p =>
                        p.Name == property.Name
                        && p.ClrType == property.ClrType
                        && p.DeclaringType == property.DeclaringType
                    );

                    if (matchingProp != null)
                    {
                        var columnMapping = entityTypeMapping.ColumnMappings.FirstOrDefault(cm =>
                            cm.Property == matchingProp
                        );

                        if (columnMapping != null && columnMapping.Column.Name == column.Name)
                        {
                            return column.Name;
                        }
                    }
                }
            }
        }

        return null;
    }

    private static string BuildColumnInfo(IColumn column, IColumn discriminatorColumn, ITable table)
    {
        var columnName = column.Name;
        var columnType = column.StoreType;
        var isNullable = column.IsNullable;

        var isPrimaryKey = table.PrimaryKey?.Columns.Contains(column) ?? false;
        var isForeignKey = table.ForeignKeyConstraints.Any(fk => fk.Columns.Contains(column));
        var isDiscriminator =
            discriminatorColumn != null && column.Name == discriminatorColumn.Name;

        var columnInfo = $"{columnName} ({columnType})";

        var attributes = new List<string>();
        if (isPrimaryKey)
            attributes.Add("PRIMARY KEY");
        if (isForeignKey)
            attributes.Add("FOREIGN KEY");
        if (isDiscriminator)
            attributes.Add("DISCRIMINATOR");
        if (!isNullable && !isPrimaryKey)
            attributes.Add("NOT NULL");

        if (attributes.Any())
        {
            columnInfo += $" [{string.Join("] [", attributes)}]";
        }

        return columnInfo;
    }

    private static List<IProperty> GetAllPropertiesRecursive(IEntityType entityType)
    {
        var properties = new List<IProperty>();
        properties.AddRange(entityType.GetProperties());

        foreach (var navigation in entityType.GetNavigations())
        {
            if (navigation.TargetEntityType.IsOwned())
            {
                properties.AddRange(GetAllPropertiesRecursive(navigation.TargetEntityType));
            }
        }

        foreach (var skipNavigation in entityType.GetSkipNavigations())
        {
            if (skipNavigation.TargetEntityType.IsOwned())
            {
                properties.AddRange(GetAllPropertiesRecursive(skipNavigation.TargetEntityType));
            }
        }

        return properties;
    }

    private static List<string> GetForeignKeysForTable(ITable table)
    {
        var foreignKeys = new List<string>();

        foreach (var fkConstraint in table.ForeignKeyConstraints)
        {
            var fkColumns = string.Join(", ", fkConstraint.Columns.Select(c => c.Name));
            var pkColumns = string.Join(", ", fkConstraint.PrincipalColumns.Select(c => c.Name));
            var principalTable = fkConstraint.PrincipalTable.Name;

            foreignKeys.Add($"{fkColumns} -> {principalTable}.{pkColumns}");
        }

        return foreignKeys;
    }

    private List<string> AnalyzeInheritancePatterns()
    {
        var patterns = new List<string>();
        var entityTypes = GetNonOwnedEntityTypes().Where(e => ShouldIncludeEntity(e)).ToList();

        var tptHierarchies = new Dictionary<string, List<IEntityType>>();
        var tphTables = new Dictionary<string, List<IEntityType>>();

        foreach (var entityType in entityTypes)
        {
            var tableName = entityType.GetTableName();
            if (string.IsNullOrEmpty(tableName))
                continue;

            if (entityType.BaseType != null)
            {
                var baseTableName = entityType.BaseType.GetTableName();
                if (!string.IsNullOrEmpty(baseTableName) && baseTableName != tableName)
                {
                    if (!tptHierarchies.ContainsKey(baseTableName))
                        tptHierarchies[baseTableName] = new List<IEntityType>();
                    tptHierarchies[baseTableName].Add(entityType);
                }
            }

            var discriminatorProperty = entityType.FindDiscriminatorProperty();
            if (discriminatorProperty != null)
            {
                if (!tphTables.ContainsKey(tableName))
                    tphTables[tableName] = new List<IEntityType>();
                tphTables[tableName].Add(entityType);
            }
        }

        foreach (var (baseTable, derivedEntities) in tptHierarchies)
        {
            var baseEntity = entityTypes.FirstOrDefault(e => e.GetTableName() == baseTable);
            if (baseEntity != null)
            {
                patterns.Add(
                    $"TABLE-PER-TYPE: {baseEntity.ClrType.Name} class (table: {baseTable})"
                );
                patterns.Add($"  - Base table \"{baseTable}\" contains common properties");

                foreach (var derived in derivedEntities)
                {
                    patterns.Add(
                        $"  - Derived table \"{derived.GetTableName()}\" must always be joined with table \"{baseTable}\" to access all properties"
                    );
                }

                patterns.Add(
                    $"  - Primary key relationship: derived tables reference base table's primary key"
                );
                patterns.Add("");
            }
        }

        foreach (var (tableName, entities) in tphTables)
        {
            if (entities.Count > 1)
            {
                var discriminatorProperty = entities.First().FindDiscriminatorProperty();
                var baseEntity =
                    entities.FirstOrDefault(e => e.BaseType == null) ?? entities.First();
                var derivedEntities = entities.Where(e => e.BaseType != null).ToList();

                patterns.Add($"TABLE-PER-HIERARCHY: Table \"{tableName}\"");
                patterns.Add($"  - Base class: {baseEntity.ClrType.Name}");
                if (derivedEntities.Any())
                {
                    patterns.Add(
                        $"  - Derived classes: {string.Join(", ", derivedEntities.Select(e => e.ClrType.Name))}"
                    );
                }

                patterns.Add($"  - Discriminator column: {discriminatorProperty?.GetColumnName()}");

                foreach (var entity in entities)
                {
                    var discriminatorValue = entity.GetDiscriminatorValue();
                    patterns.Add(
                        $"  - {entity.ClrType.Name}: {discriminatorProperty?.GetColumnName()} = '{discriminatorValue}'"
                    );
                }

                patterns.Add("");
            }
        }

        return patterns;
    }

    private string GetInheritanceDetailsForTable(IEntityType entityType)
    {
        var details = new List<string>();

        if (entityType.BaseType != null)
        {
            var baseTableName = entityType.BaseType.GetTableName();
            var currentTableName = entityType.GetTableName();

            if (!string.IsNullOrEmpty(baseTableName) && baseTableName != currentTableName)
            {
                details.Add($"Extends table {baseTableName} (Table-Per-Type pattern)");
            }
        }

        var derivedTypes = GetNonOwnedEntityTypes()
            .Where(e =>
                e.BaseType?.GetTableName() == entityType.GetTableName()
                && e.GetTableName() != entityType.GetTableName()
            )
            .ToList();

        if (derivedTypes.Any())
        {
            var derivedNames = string.Join(", ", derivedTypes.Select(d => d.ClrType.Name));
            details.Add($"Base table for: {derivedNames} (Table-Per-Type pattern)");
        }

        var discriminatorProperty = entityType.FindDiscriminatorProperty();
        if (discriminatorProperty != null)
        {
            var discriminatorValue = entityType.GetDiscriminatorValue();

            var entitiesInTable = GetNonOwnedEntityTypes()
                .Where(e => e.GetTableName() == entityType.GetTableName() && e != entityType)
                .ToList();

            if (entitiesInTable.Any())
            {
                var entityNames = string.Join(", ", entitiesInTable.Select(e => e.ClrType.Name));
                details.Add($"Shares table with: {entityNames} (Table-Per-Hierarchy pattern)");
            }

            details.Add(
                $"Entity type identified by {discriminatorProperty.GetColumnName()} = '{discriminatorValue}' (Table-Per-Hierarchy pattern)"
            );
        }

        return string.Join("; ", details);
    }
}
