using System.Data;
using Strata.SqlTools.SqlBreakdown.Enums.SQL;
using Strata.SqlTools.SqlBreakdown.Utilities;

namespace Strata.SqlTools.SqlBreakdown.Tests.Utilities;

[TestFixture]
public class SqlUtilsTests
{
    #region String Manipulation

    [Test]
    public void StripColumnTableAlias_RemovesTableAliases()
    {
        // Act & Assert
        Assert.That(SqlUtils.StripColumnTableAlias("a.Col1, b.Col2"), Is.EqualTo("Col1,Col2"));
    }

    [Test]
    public void StripColumnTableAlias_NoAlias_LeavesColumnUntouched()
    {
        // Act & Assert
        Assert.That(SqlUtils.StripColumnTableAlias("MyCol"), Is.EqualTo("MyCol"));
    }

    [TestCase("O'Brien", "O''Brien")]
    [TestCase("", "")]
    [TestCase("clean", "clean")]
    public void EscapeInvalidCharacter_DoublesSingleQuotes(string input, string expected)
    {
        // Act & Assert
        Assert.That(SqlUtils.EscapeInvalidCharacter(input), Is.EqualTo(expected));
    }

    [TestCase("Col", "[Col]")]
    [TestCase("[Col]", "[Col]")]
    [TestCase("[Col", "[Col]")]
    [TestCase("Col]", "[Col]")]
    public void AddBrackets_WrapsWhenMissing(string input, string expected)
    {
        // Act & Assert
        Assert.That(SqlUtils.AddBrackets(input), Is.EqualTo(expected));
    }

    [Test]
    public void RemoveBrackets_StripsSquareBrackets()
    {
        // Act & Assert
        Assert.That(SqlUtils.RemoveBrackets("[Col]"), Is.EqualTo("Col"));
    }

    [Test]
    public void GetSqlFriendlyName_RemovesInvalidCharactersByDefault()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlFriendlyName("a.b c"), Is.EqualTo("abc"));
    }

    [Test]
    public void GetSqlFriendlyName_UsesReplacement()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlFriendlyName("a.b c", "_"), Is.EqualTo("a_b_c"));
    }

    #endregion

    #region Object Name Handling

    [Test]
    public void GetSqlObjectExpression_BuildsBracketedExpression()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlObjectExpression("dbo", "MyTable"), Is.EqualTo("[dbo].[MyTable]"));
    }

    [Test]
    public void GetSqlObjectExpression_StripsExistingBrackets()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlObjectExpression("[dbo]", "[MyTable]"), Is.EqualTo("[dbo].[MyTable]"));
    }

    [Test]
    public void GetSqlObjectName_ExtractsObjectPortion()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlObjectName("[dbo].[MyTable]"), Is.EqualTo("MyTable"));
    }

    [Test]
    public void GetSqlObjectName_NoDot_ReturnsInput()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlObjectName("MyTable"), Is.EqualTo("MyTable"));
    }

    [Test]
    public void GetSqlObjectName_MultipleDots_Throws()
    {
        // Act & Assert
        Assert.Throws<NotSupportedException>(() => SqlUtils.GetSqlObjectName("a.b.c"));
    }

    [Test]
    public void GetSqlSchemaName_ExtractsSchema()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlSchemaName("data.DimClient"), Is.EqualTo("data"));
    }

    [Test]
    public void GetSqlSchemaName_NoDot_ReturnsDbo()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlSchemaName("DimClient"), Is.EqualTo("dbo"));
    }

    [Test]
    public void GetSqlSchemaName_MultipleDots_Throws()
    {
        // Act & Assert
        Assert.Throws<NotSupportedException>(() => SqlUtils.GetSqlSchemaName("a.b.c"));
    }

    [TestCase("dbo", true)]
    [TestCase("DBO", true)]
    [TestCase("data", false)]
    public void IsDefaultSchema_ChecksAgainstDbo(string schema, bool expected)
    {
        // Act & Assert
        Assert.That(SqlUtils.IsDefaultSchema(schema), Is.EqualTo(expected));
    }

    #endregion

    #region Data Type Conversions

    [Test]
    public void GetStringConversionLength_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetStringConversionLength(SqlDataType.Bit), Is.EqualTo(1));
            Assert.That(SqlUtils.GetStringConversionLength(SqlDataType.Int), Is.EqualTo(int.MaxValue.ToString().Length));
            Assert.That(SqlUtils.GetStringConversionLength(SqlDataType.UniqueIdentifier), Is.EqualTo(Guid.Empty.ToString().Length));
            Assert.That(SqlUtils.GetStringConversionLength(SqlDataType.NVarChar), Is.EqualTo(SqlUtils.MAX_NVARCHAR_LENGTH));
        });
    }

    [Test]
    public void ConvertGuidToAlias_ProducesPrefixedUppercaseAlias()
    {
        // Arrange
        var guid = new Guid("0123abcd-4567-89ef-0123-456789abcdef");

        // Act
        var result = SqlUtils.ConvertGuidToAlias(guid, "p");

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(result, Does.StartWith("p"));
            Assert.That(result, Does.Not.Contain("-"));
            Assert.That(result, Is.EqualTo("p0123ABCD456789EF0123456789ABCDEF"));
        });
    }

    [Test]
    public void IsFixedPrecision_DecimalIsNotFixed()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.IsFixedPrecision(SqlDataType.Decimal), Is.False);
            Assert.That(SqlUtils.IsFixedPrecision(SqlDataType.Int), Is.True);
            // IsFixedScale mirrors IsFixedPrecision
            Assert.That(SqlUtils.IsFixedScale(SqlDataType.Decimal), Is.False);
            Assert.That(SqlUtils.IsFixedScale(SqlDataType.Int), Is.True);
        });
    }

    [Test]
    public void IsFixedMaxLength_VariesByType()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.IsFixedMaxLength(SqlDataType.Int), Is.True);
            Assert.That(SqlUtils.IsFixedMaxLength(SqlDataType.UniqueIdentifier), Is.True);
            Assert.That(SqlUtils.IsFixedMaxLength(SqlDataType.VarChar), Is.False);
        });
    }

    [Test]
    public void GetFixedScale_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetFixedScale(SqlDataType.DateTime), Is.EqualTo(3));
            Assert.That(SqlUtils.GetFixedScale(SqlDataType.Money), Is.EqualTo(4));
            Assert.That(SqlUtils.GetFixedScale(SqlDataType.Int), Is.EqualTo(0));
        });
    }

    [Test]
    public void GetFixedScale_Decimal_Throws()
    {
        // Act & Assert
        Assert.Throws<NotImplementedException>(() => SqlUtils.GetFixedScale(SqlDataType.Decimal));
    }

    [Test]
    public void GetFixedPrecision_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetFixedPrecision(SqlDataType.BigInt), Is.EqualTo(19));
            Assert.That(SqlUtils.GetFixedPrecision(SqlDataType.Int), Is.EqualTo(10));
            Assert.That(SqlUtils.GetFixedPrecision(SqlDataType.Bit), Is.EqualTo(1));
        });
    }

    [Test]
    public void GetFixedMaxLength_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetFixedMaxLength(SqlDataType.Int), Is.EqualTo(4));
            Assert.That(SqlUtils.GetFixedMaxLength(SqlDataType.UniqueIdentifier), Is.EqualTo(16));
            Assert.That(SqlUtils.GetFixedMaxLength(SqlDataType.TinyInt), Is.EqualTo(1));
        });
    }

    [Test]
    public void GetFixedMaxLength_UnsupportedType_Throws()
    {
        // Act & Assert - VarChar has no fixed length
        Assert.Throws<NotImplementedException>(() => SqlUtils.GetFixedMaxLength(SqlDataType.VarChar));
    }

    #endregion

    #region Well-Known SQL Errors

    [Test]
    public void GetWellKnownSqlError_DuplicateKeyRow_IsUniqueKeyViolation()
    {
        // Arrange
        var ex = new Exception("Cannot insert duplicate key row in object 'dbo.Users'.");

        // Act & Assert
        Assert.That(SqlUtils.GetWellKnownSqlError(ex), Is.EqualTo(SqlUtils.WellKnownSqlError.UniqueKeyViolation));
    }

    [Test]
    public void GetWellKnownSqlError_ObjectAlreadyExists_IsDetected()
    {
        // Arrange
        var ex = new Exception("There is already an object named 'Users' in the database.");

        // Act & Assert
        Assert.That(SqlUtils.GetWellKnownSqlError(ex), Is.EqualTo(SqlUtils.WellKnownSqlError.ObjectAlreadyExists));
    }

    [Test]
    public void GetWellKnownSqlError_TruncateForeignKey_IsDetected()
    {
        // Arrange
        var ex = new Exception("Cannot truncate table 'dbo.Orders' because it is being referenced by a FOREIGN KEY constraint.");

        // Act & Assert
        Assert.That(SqlUtils.GetWellKnownSqlError(ex), Is.EqualTo(SqlUtils.WellKnownSqlError.TruncateTableForeignKeyReferenceError));
    }

    [Test]
    public void GetWellKnownSqlError_UnrecognizedMessage_IsUnknown()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetWellKnownSqlError(new Exception("Something else entirely")), Is.EqualTo(SqlUtils.WellKnownSqlError.Unknown));
    }

    #endregion

    #region Data Type Helpers

    [Test]
    public void GetClientFriendlyDataTypeName_MapsCategories()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetClientFriendlyDataTypeName(SqlDataType.NVarChar), Is.EqualTo("Text"));
            Assert.That(SqlUtils.GetClientFriendlyDataTypeName(SqlDataType.Int), Is.EqualTo("Whole Number"));
            Assert.That(SqlUtils.GetClientFriendlyDataTypeName(SqlDataType.Decimal), Is.EqualTo("Decimal"));
            Assert.That(SqlUtils.GetClientFriendlyDataTypeName(SqlDataType.DateTime), Is.EqualTo("Date"));
            Assert.That(SqlUtils.GetClientFriendlyDataTypeName(SqlDataType.Money), Is.EqualTo("Dollars"));
        });
    }

    [Test]
    public void IdentityColumnTypes_ContainsIntegerTypes()
    {
        // Act
        var types = SqlUtils.IdentityColumnTypes().ToList();

        // Assert
        Assert.That(types, Is.EquivalentTo(new[]
        {
            SqlDataType.BigInt, SqlDataType.TinyInt, SqlDataType.Int, SqlDataType.SmallInt
        }));
    }

    [Test]
    public void GetOrderBySqlDirectionString_MapsDirections()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetOrderBySqlDirectionString(SortDirection.Ascending), Is.EqualTo("ASC"));
            Assert.That(SqlUtils.GetOrderBySqlDirectionString(SortDirection.Descending), Is.EqualTo("DESC"));
        });
    }

    [Test]
    public void GetSimpleDataType_MapsToCategories()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSimpleDataType(SqlDataType.Int), Is.EqualTo(SimpleDataType.Numeric));
            Assert.That(SqlUtils.GetSimpleDataType(SqlDataType.DateTime), Is.EqualTo(SimpleDataType.Date));
            Assert.That(SqlUtils.GetSimpleDataType(SqlDataType.Bit), Is.EqualTo(SimpleDataType.Boolean));
            Assert.That(SqlUtils.GetSimpleDataType(SqlDataType.UniqueIdentifier), Is.EqualTo(SimpleDataType.GUID));
            Assert.That(SqlUtils.GetSimpleDataType(SqlDataType.NVarChar), Is.EqualTo(SimpleDataType.String));
        });
    }

    [TestCase("1", true)]
    [TestCase("0", true)]
    [TestCase("yes", true)]
    [TestCase("no", true)]
    [TestCase("true", true)]
    [TestCase("maybe", false)]
    public void IsStringValueValidForSimpleDataType_Boolean(string value, bool expected)
    {
        // Act & Assert
        Assert.That(SqlUtils.IsStringValueValidForSimpleDataType(SimpleDataType.Boolean, value), Is.EqualTo(expected));
    }

    [TestCase("3.14", true)]
    [TestCase("100", true)]
    [TestCase("abc", false)]
    public void IsStringValueValidForSimpleDataType_Numeric(string value, bool expected)
    {
        // Act & Assert
        Assert.That(SqlUtils.IsStringValueValidForSimpleDataType(SimpleDataType.Numeric, value), Is.EqualTo(expected));
    }

    [Test]
    public void IsStringValueValidForSimpleDataType_GuidAndString()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.IsStringValueValidForSimpleDataType(SimpleDataType.GUID, Guid.NewGuid().ToString()), Is.True);
            Assert.That(SqlUtils.IsStringValueValidForSimpleDataType(SimpleDataType.GUID, "nope"), Is.False);
            // String accepts anything
            Assert.That(SqlUtils.IsStringValueValidForSimpleDataType(SimpleDataType.String, "anything"), Is.True);
        });
    }

    [Test]
    public void GetValueType_MapsToClrTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetValueType(SqlDataType.Bit), Is.EqualTo(typeof(bool)));
            Assert.That(SqlUtils.GetValueType(SqlDataType.DateTime), Is.EqualTo(typeof(DateTime)));
            Assert.That(SqlUtils.GetValueType(SqlDataType.Int), Is.EqualTo(typeof(double)));
            Assert.That(SqlUtils.GetValueType(SqlDataType.UniqueIdentifier), Is.EqualTo(typeof(Guid)));
            Assert.That(SqlUtils.GetValueType(SqlDataType.NVarChar), Is.EqualTo(typeof(string)));
        });
    }

    [Test]
    public void GetSqlDataType_MapsFromSqlDbType()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSqlDataType(SqlDbType.Int), Is.EqualTo(SqlDataType.Int));
            Assert.That(SqlUtils.GetSqlDataType(SqlDbType.DateTime2), Is.EqualTo(SqlDataType.DateTime));
            Assert.That(SqlUtils.GetSqlDataType(SqlDbType.UniqueIdentifier), Is.EqualTo(SqlDataType.UniqueIdentifier));
            // Unmapped types fall back to NVarChar
            Assert.That(SqlUtils.GetSqlDataType(SqlDbType.Variant), Is.EqualTo(SqlDataType.NVarChar));
        });
    }

    #endregion

    #region Default Values

    [Test]
    public void GetSqlDefaultValueDefinition_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSqlDefaultValueDefinition(SqlDataType.Int), Is.EqualTo("(0)"));
            Assert.That(SqlUtils.GetSqlDefaultValueDefinition(SqlDataType.VarChar), Is.EqualTo("('')"));
            Assert.That(SqlUtils.GetSqlDefaultValueDefinition(SqlDataType.DateTime), Is.EqualTo("(getdate())"));
            Assert.That(SqlUtils.GetSqlDefaultValueDefinition(SqlDataType.UniqueIdentifier), Is.EqualTo($"('{Guid.Empty}')"));
        });
    }

    [Test]
    public void GetSqlZeroValue_KnownTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSqlZeroValue(SqlDataType.Int), Is.EqualTo(0));
            Assert.That(SqlUtils.GetSqlZeroValue(SqlDataType.NVarChar), Is.EqualTo(string.Empty));
            Assert.That(SqlUtils.GetSqlZeroValue(SqlDataType.DateTime), Is.EqualTo(new DateTime(1900, 1, 1, 0, 0, 0, DateTimeKind.Unspecified)));
            Assert.That(SqlUtils.GetSqlZeroValue(SqlDataType.UniqueIdentifier), Is.EqualTo(Guid.Empty));
        });
    }

    [Test]
    public void GetSqlDefaultValue_NullDefinition_ReturnsNull()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.Int, string.Empty), Is.Null);
    }

    [Test]
    public void GetSqlDefaultValue_ParsesScalarTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.Int, "(0)"), Is.EqualTo(0));
            Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.BigInt, "(123)"), Is.EqualTo(123L));
            Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.Bit, "(1)"), Is.EqualTo(true));
            Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.Bit, "(0)"), Is.EqualTo(false));
            Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.NVarChar, "('hello')"), Is.EqualTo("hello"));
        });
    }

    [Test]
    public void GetSqlDefaultValue_UniqueIdentifierZeroGuid_ReturnsEmpty()
    {
        // Act & Assert
        Assert.That(SqlUtils.GetSqlDefaultValue(SqlDataType.UniqueIdentifier, "(dbo.ZeroGUID())"), Is.EqualTo(Guid.Empty));
    }

    #endregion

    #region Join / Connection Strings

    [Test]
    public void GetSqlJoinTypeString_MapsJoinTypes()
    {
        Assert.Multiple(() =>
        {
            Assert.That(SqlUtils.GetSqlJoinTypeString(SqlJoinType.Left), Is.EqualTo("LEFT"));
            Assert.That(SqlUtils.GetSqlJoinTypeString(SqlJoinType.Right), Is.EqualTo("RIGHT"));
            Assert.That(SqlUtils.GetSqlJoinTypeString(SqlJoinType.Inner), Is.EqualTo("INNER"));
            Assert.That(SqlUtils.GetSqlJoinTypeString(SqlJoinType.Cross), Is.EqualTo("CROSS"));
        });
    }

    [Test]
    public void GetSqlConnectionString_BuildsIntegratedSecurityConnection()
    {
        // Act
        var result = SqlUtils.GetSqlConnectionString("MyServer", "MyDb");

        Assert.Multiple(() =>
        {
            // Assert
            Assert.That(result, Does.Contain("data source=MyServer;"));
            Assert.That(result, Does.Contain("initial catalog=MyDb;"));
            Assert.That(result, Does.Contain("Integrated Security=SSPI;"));
        });
    }

    #endregion
}