using System;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Xunit;

namespace Dapper.Tests
{
    [Collection("ProcedureTests")]
    public sealed class SystemSqlClientProcedureTests : ProcedureTests<SystemSqlClientProvider> { }
#if MSSQLCLIENT
    [Collection("ProcedureTests")]
    public sealed class MicrosoftSqlClientProcedureTests : ProcedureTests<MicrosoftSqlClientProvider> { }
#endif
    public abstract class ProcedureTests<TProvider> : TestBase<TProvider> where TProvider : DatabaseProvider
    {
        [Fact]
        public void TestProcWithOutParameter()
        {
            connection.Execute(
            @"CREATE PROCEDURE #TestProcWithOutParameter
                @ID int output,
                @Foo varchar(100),
                @Bar int
            AS
                SET @ID = @Bar + LEN(@Foo)");
            var obj = new
            {
                ID = 0,
                Foo = "abc",
                Bar = 4
            };
            var args = new DynamicParameters(obj);
            args.Add("ID", 0, direction: ParameterDirection.Output);
            connection.Execute("#TestProcWithOutParameter", args, commandType: CommandType.StoredProcedure);
            Assert.Equal(7, args.Get<int>("ID"));
        }

        [Fact]
        public void TestProcWithOutAndReturnParameter()
        {
            connection.Execute(
            @"CREATE PROCEDURE #TestProcWithOutAndReturnParameter
                @ID int output,
                @Foo varchar(100),
                @Bar int
            AS
                SET @ID = @Bar + LEN(@Foo)
                RETURN 42");
            var obj = new
            {
                ID = 0,
                Foo = "abc",
                Bar = 4
            };
            var args = new DynamicParameters(obj);
            args.Add("ID", 0, direction: ParameterDirection.Output);
            args.Add("result", 0, direction: ParameterDirection.ReturnValue);
            connection.Execute("#TestProcWithOutAndReturnParameter", args, commandType: CommandType.StoredProcedure);
            Assert.Equal(7, args.Get<int>("ID"));
            Assert.Equal(42, args.Get<int>("result"));
        }

        [Fact]
        public void TestIssue17648290()
        {
            var p = new DynamicParameters();
            const int code = 1, getMessageControlId = 2;
            p.Add("@Code", code);
            p.Add("@MessageControlID", getMessageControlId);
            p.Add("@SuccessCode", dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("@ErrorDescription", dbType: DbType.String, direction: ParameterDirection.Output, size: 255);
            connection.Execute(
            @"CREATE PROCEDURE #up_MessageProcessed_get
                @Code varchar(10),
                @MessageControlID varchar(22),
                @SuccessCode int OUTPUT,
                @ErrorDescription varchar(255) OUTPUT
            AS
            BEGIN
                Select 2 as MessageProcessID, 38349348 as StartNum, 3874900 as EndNum, GETDATE() as StartDate, GETDATE() as EndDate
                SET @SuccessCode = 0
                SET @ErrorDescription = 'Completed successfully'
            END");
            var result = connection.Query(sql: "#up_MessageProcessed_get", param: p, commandType: CommandType.StoredProcedure);
            var row = result.Single();
            Assert.Equal(2, (int)row.MessageProcessID);
            Assert.Equal(38349348, (int)row.StartNum);
            Assert.Equal(3874900, (int)row.EndNum);
            DateTime startDate = row.StartDate, endDate = row.EndDate;
            Assert.Equal(0, p.Get<int>("SuccessCode"));
            Assert.Equal("Completed successfully", p.Get<string>("ErrorDescription"));
        }

        [Theory]
        [InlineData(CommandType.StoredProcedure)]
        [InlineData(null)] // auto
        public void InferProcedure(CommandType? commandType)
        {
            connection.Execute("CREATE PROCEDURE #InferProcedure @id int AS BEGIN SELECT -@id END");
            var result = connection.QuerySingle<int>("#InferProcedure", new { id = 42 }, commandType: commandType);
            Assert.Equal(-42, result);
        }

        [Theory]
        [InlineData(CommandType.Text)]
        [InlineData(null)] // auto
        public void InferNotProcedure(CommandType? commandType)
        {
            connection.Execute("CREATE PROCEDURE #InferNotProcedure @id int AS BEGIN SELECT -@id END");
            var result = connection.QuerySingle<int>("EXEC #InferNotProcedure @id", new { id = 42 }, commandType: commandType);
            Assert.Equal(-42, result);
        }

        [Fact]
        public void SO24605346_ProcsAndStrings()
        {
            connection.Execute(
            @"create proc #GetPracticeRebateOrderByInvoiceNumber 
                @TaxInvoiceNumber nvarchar(20) 
            as
                select @TaxInvoiceNumber as [fTaxInvoiceNumber]");
            const string InvoiceNumber = "INV0000000028PPN";
            var result = connection.Query<PracticeRebateOrders>("#GetPracticeRebateOrderByInvoiceNumber", new
            {
                TaxInvoiceNumber = InvoiceNumber
            }, commandType: CommandType.StoredProcedure).FirstOrDefault();

            Assert.NotNull(result);
            Assert.Equal("INV0000000028PPN", result.TaxInvoiceNumber);
        }

        private class PracticeRebateOrders
        {
            public string? fTaxInvoiceNumber;
            [System.Xml.Serialization.XmlElement(Form = System.Xml.Schema.XmlSchemaForm.Unqualified)]
            public string? TaxInvoiceNumber
            {
                get { return fTaxInvoiceNumber; }
                set { fTaxInvoiceNumber = value; }
            }
        }

        [Fact]
        public void Issue327_ReadEmptyProcedureResults()
        {
            // Actually testing for not erroring here on the mapping having no rows to map on in Read<T>();
            connection.Execute(@"
            CREATE PROCEDURE #TestEmptyResults
            AS
                SELECT Top 0 1 Id, 'Bob' Name;
                SELECT Top 0 'Billy Goat' Creature, 'Unicorn' SpiritAnimal, 'Rainbow' Location;");
            var query = connection.QueryMultiple("#TestEmptyResults", commandType: CommandType.StoredProcedure);
            var result1 = query.Read<Issue327_Person>();
            var result2 = query.Read<Issue327_Magic>();
            Assert.False(result1.Any());
            Assert.False(result2.Any());
        }

        private class Issue327_Person
        {
            public int Id { get; set; }
            public string? Name { get; set; }
        }

        private class Issue327_Magic
        {
            public string? Creature { get; set; }
            public string? SpiritAnimal { get; set; }
            public string? Location { get; set; }
        }

        [Fact]
        public void TestProcSupport()
        {
            var p = new DynamicParameters();
            p.Add("a", 11);
            p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

            connection.Execute(@"
            create proc #TestProc 
	            @a int,
	            @b int output
            as 
            begin
	            set @b = 999
	            select 1111
	            return @a
            end");
            Assert.Equal(1111, connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First());

            Assert.Equal(11, p.Get<int>("c"));
            Assert.Equal(999, p.Get<int>("b"));
        }

        // https://stackoverflow.com/q/8593871
        [Fact]
        public void TestListOfAnsiStrings()
        {
            var results = connection.Query<string>("select * from (select 'a' str union select 'b' union select 'c') X where str in @strings",
                new
                {
                    strings = new[] {
                    new DbString { IsAnsi = true, Value = "a" },
                    new DbString { IsAnsi = true, Value = "b" }
                }
                }).ToList();

            Assert.Equal(2, results.Count);
            results.Sort();
            Assert.Equal("a", results[0]);
            Assert.Equal("b", results[1]);
        }

        [Fact]
        public void TestDateTime2PrecisionPreservedInDynamicParameters()
        {
            const string tempSPName = "#" + nameof(TestDateTime2PrecisionPreservedInDynamicParameters);

            DateTime datetimeDefault = new(2000, 1, 1, 0, 0, 0, DateTimeKind.Utc);
            DateTime datetime2 = datetimeDefault.AddTicks(1); // Add 100 ns

            Assert.True(datetimeDefault < datetime2);

            connection.Execute(
            $@"create proc {tempSPName} 
	            @a datetime2,
	            @b datetime2 output
            as 
            begin
	            set @b = @a
	            select DATEADD(ns, -100, @b)
            end");

            var p = new DynamicParameters();
            // Note: parameters declared as DateTime2
            p.Add("a", datetime2, dbType: DbType.DateTime2, direction: ParameterDirection.Input);
            p.Add("b", dbType: DbType.DateTime2, direction: ParameterDirection.Output);

            DateTime fromSelect = connection.Query<DateTime>(tempSPName, p, commandType: CommandType.StoredProcedure).First();

            Assert.Equal(datetimeDefault, fromSelect);

            Assert.Equal(datetime2, p.Get<DateTime>("b"));
        }

        [Theory]
        [InlineData(null)]
        [InlineData(DbType.DateTime)]
        public void TestDateTime2LosePrecisionInDynamicParameters(DbType? dbType)
        {
            const string tempSPName = "#" + nameof(TestDateTime2LosePrecisionInDynamicParameters);

            DateTime datetimeDefault = new(2000, 1, 1, 0, 0, 0, DateTimeKind.Utc);
            DateTime datetime2 = datetimeDefault.AddTicks(1); // Add 100 ns

            Assert.True(datetimeDefault < datetime2);

            connection.Execute(
            $@"create proc {tempSPName}
	            @a datetime2,
	            @b datetime2 output
            as 
            begin
	            set @b = DATEADD(ns, 100, @a)
	            select @b
            end");

            var p = new DynamicParameters();
            // Note: input parameter declared as DateTime (or implicitly as this) but SP has DateTime2
            p.Add("a", datetime2, dbType: dbType, direction: ParameterDirection.Input);
            p.Add("b", dbType: DbType.DateTime, direction: ParameterDirection.Output);

            DateTime fromSelect = connection.Query<DateTime>(tempSPName, p, commandType: CommandType.StoredProcedure).First();

            // @a truncates to datetimeDefault when passed into SP by DynamicParameters, add 100ns and it comes out as DateTime2
            Assert.Equal(datetime2, fromSelect);

            // @b gets set to datetime2 value but is truncated back to DbType.DateTime by DynamicParameter's Output declaration
            Assert.Equal(datetimeDefault, p.Get<DateTime>("b"));
        }


        [Fact]
        public async Task Issue591_NoResultsAsync()
        {
            const string tempSPName = "#" + nameof(Issue591_NoResultsAsync);

            var result = await connection.QueryAsync(
            $@"create proc {tempSPName}
            as 
            begin
                -- basically a failed if statement, so the select is not happening and the stored proc return nothing
                if 1=0
                begin
                    select 1 as Num
                end
            end
            
            exec {tempSPName}");

            Assert.Empty(result);
        }

        [Theory]
        [InlineData(" ")]
        [InlineData("\u00A0")] // nbsp
        [InlineData("\u202F")] // narrow nbsp
        [InlineData("\u2000")] // n quad
        [InlineData("\t")]
        [InlineData("\r")]
        [InlineData("\n")]
        public async Task Issue1986_AutoProc_Whitespace(string space)
        {
            var sql = "select!42".Replace("!", space);
            var result = await connection.QuerySingleAsync<int>(sql);
            Assert.Equal(42, result);
        }

        [Theory]
        [InlineData("foo", CommandType.StoredProcedure)]
        [InlineData("foo;", CommandType.Text)]
        [InlineData("foo bar", CommandType.Text)]
        [InlineData("foo bar;", CommandType.Text)]
        [InlineData("vacuum", CommandType.Text)]
        [InlineData("vacuum;", CommandType.Text)]
        [InlineData("FOO", CommandType.StoredProcedure)]
        [InlineData("FOO;", CommandType.Text)]
        [InlineData("FOO BAR", CommandType.Text)]
        [InlineData("FOO BAR;", CommandType.Text)]
        [InlineData("VACUUM", CommandType.Text)]
        [InlineData("VACUUM;", CommandType.Text)]
        [InlineData("cOmmiT", CommandType.Text)]
        [InlineData("rOllbAck", CommandType.Text)]
        [InlineData("reVErt", CommandType.Text)]

        // comments imply text
        [InlineData("foo--bar", CommandType.Text)]
        [InlineData("foo/*bar*/", CommandType.Text)]
        public void InferCommandType(string sql, CommandType commandType)
        {
            Assert.Equal(commandType, CommandDefinition.InferCommandType(sql));
        }
    }
}