await using var fusionConnection = await context.OpenFusionConnectionAsync();

var departmentDto = new DepartmentDto(
    Id: 1111,
    Name: "Computer Science",
    Budget: 250_000m,
    Courses:
    [
        new CourseDto(
            Id: 101,
            Title: "Introduction to Programming",
            Credits: 5),

        new CourseDto(
            Id: 102,
            Title: "Algorithms and Data Structures",
            Credits: 6),

        new CourseDto(
            Id: 103,
            Title: "Database Systems",
            Credits: 5),

        new CourseDto(
            Id: 104,
            Title: "Operating Systems",
            Credits: 4)
    ]);

var scope = fusionConnection.CreateScope();

scope.Table<Department>().Insert([new Department(
    departmentDto.Id,
    departmentDto.Name,
    departmentDto.Budget)]);
scope.Table<Course>().Insert(
    departmentDto.Courses.Select(courseDto => new Course(
        courseDto.Id,
        courseDto.Title,
        courseDto.Credits,
        departmentDto.Id)));

var result = await scope.FlushAsync();

return result;

await using var fusionConnection = await context.OpenFusionConnectionAsync();

var existingDepartment = await fusionConnection.GetDataNodesAsync(
    """
    SELECT *
    FROM Department D
        LEFT JOIN Course C ON D.DepartmentID = C.DepartmentID
    WHERE D.DepartmentId = @DepartmentID
    ORDER BY D.Name;
    """,
    new From<Department>(
        new Join<Course>()),
    new Parameters { { nameof(Department.DepartmentID), 1111 } }
    );


var incomingDepartmentDto = new DepartmentDto(
    Id: 1111,
    Name: "Computer Science and Engineering",
    Budget: 300_000m,
    Courses:
    [
        new CourseDto(
            Id: 101,
            Title: "Introduction to Programming",
            Credits: 5),

        new CourseDto(
            Id: 102,
            Title: "Advanced Algorithms and Data Structures",
            Credits: 7),

        new CourseDto(
            Id: 104,
            Title: "Operating Systems",
            Credits: 4),

        new CourseDto(
            Id: 0,
            Title: "Distributed Systems",
            Credits: 5)
    ]);

var scope = fusionConnection.CreateScope(existingDepartment);

scope.Table<Department>().Merge([
    new Department(
        incomingDepartmentDto.Id,
        incomingDepartmentDto.Name,
        incomingDepartmentDto.Budget)]);
scope.Table<Course>().Merge(
    incomingDepartmentDto.Courses.Select(courseDto => new Course(
        courseDto.Id,
        courseDto.Title,
        courseDto.Credits,
        incomingDepartmentDto.Id)));

var result = await scope.FlushAsync();

return result;

await using var fusionConnection = await context.OpenFusionConnectionAsync();

var existingDepartment = await fusionConnection.GetDataNodesAsync(
    """
    SELECT *
    FROM Department D
        LEFT JOIN Course C ON D.DepartmentID = C.DepartmentID
    WHERE D.DepartmentId = @DepartmentID
    ORDER BY D.Name;
    """,
    new From<Department>(
        new Join<Course>()),
    new Parameters { { nameof(Department.DepartmentID), 1111 } }
);

var scope = fusionConnection.CreateScope(existingDepartment);

scope.Table<Department>().Delete();
scope.Table<Course>().Delete();

var result = await scope.FlushAsync();

return result;