using System.Data;
using System.Linq.Expressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query.SqlExpressions;
using Microsoft.EntityFrameworkCore.Storage;

namespace demos;

class FunctionMapping
{
    public static async Task Demo()
    {
        using var db = new MyContext();

        await db.PrepareDatabase(
            """
            create function fn_add(@a float, @b float)
            returns int
            as
            begin
                return @a + @b;
            end
            """,
            """
            create function fn_dummy_top(@count int)
            returns table
                return select top (@count) * from FooBars;
            """);

        await db.FooBars.Where(x => db.Add(x.Float, 10) > 99).LoadAsync();
        await db.FooBars.Where(x => db.DummyTop(x.Id).Count() == 10).LoadAsync();
        await db.FooBars.Where(x => db.IncrementDivide(x.Id, 2) == 0).LoadAsync();
        await db.FooBars.Where(x => EF.Functions.Like(x.Name, "%6%")).LoadAsync();
    }

    class MyContext : MyDbContextBase
    {
        [DbFunction("fn_add")]
        public int Add(float a, float b) => throw new NotImplementedException();

        [DbFunction("fn_dummy_top")]
        public IQueryable<FooBar> DummyTop(int count) => FromExpression(() => DummyTop(count));

        public double IncrementDivide(int a, int b) => throw new NotSupportedException();

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasDbFunction(typeof(MyContext).GetMethod(nameof(IncrementDivide), [typeof(int), typeof(int)])!)
                .HasTranslation(
                    args =>
                        new SqlBinaryExpression(
                            ExpressionType.Divide,
                            new SqlBinaryExpression(
                                ExpressionType.Add,
                                args[0],
                                new SqlConstantExpression(1, typeof(int), new IntTypeMapping("int")),
                                typeof(int),
                                new IntTypeMapping("int")),
                            args[1],
                            typeof(double),
                            new DoubleTypeMapping("double")));
        }
    }
}
