add reporting for subscriptions.. (#7299)

diff --git a/BTCPayServer.Tests/SubscriptionTests.cs b/BTCPayServer.Tests/SubscriptionTests.cs
index a50dcc0..b2b0e05 100644
--- a/BTCPayServer.Tests/SubscriptionTests.cs
+++ b/BTCPayServer.Tests/SubscriptionTests.cs
@@ -15,6 +15,7 @@ using BTCPayServer.HostedServices;
 using BTCPayServer.Plugins.Emails.HostedServices;
 using BTCPayServer.Plugins.Subscriptions;
 using BTCPayServer.Tests.PMO;
+using BTCPayServer.Views.Stores;
 using Microsoft.Playwright;
 using NBitcoin;
 using NBXplorer;
@@ -277,6 +278,15 @@ public class SubscriptionTests(ITestOutputHelper testOutputHelper) : UnitTestBas
             expectedBalance = 0m;
             await portal.AssertCredit(creditBalance: $"${expectedBalance.ToString("F2", CultureInfo.InvariantCulture)}");
         }
+        await s.GoToStore(s.StoreId);
+        await s.GoToStore(s.StoreId, StoreNavPages.Reporting);
+        await s.Page.ClickAsync("a[data-view='Subscribers']");
+        await s.Page.ClickAsync("#searchBtn");
+        await s.Page.WaitForSelectorAsync("#raw-data-table table");
+
+        await s.Page.ClickAsync("a[data-view='Credit History']");
+        await s.Page.ClickAsync("#searchBtn");
+        await s.Page.WaitForSelectorAsync("#raw-data-table table");
     }
 
     [Fact]
diff --git a/BTCPayServer/Plugins/Subscriptions/SubscriptionsPlugin.cs b/BTCPayServer/Plugins/Subscriptions/SubscriptionsPlugin.cs
index 71a3e8b..e9576c2 100644
--- a/BTCPayServer/Plugins/Subscriptions/SubscriptionsPlugin.cs
+++ b/BTCPayServer/Plugins/Subscriptions/SubscriptionsPlugin.cs
@@ -1,4 +1,4 @@
-#nullable enable
+#nullable enable
 using System;
 using System.Collections.Generic;
 using System.Threading.Tasks;
@@ -34,6 +34,8 @@ public class SubscriptionsPlugin : BaseBTCPayServerPlugin
         services.AddScheduledTask<SubscriptionHostedService>(TimeSpan.FromMinutes(5));
         services.AddSingleton<SubscriptionHostedService>();
         services.AddSingleton<IHostedService>(s => s.GetRequiredService<SubscriptionHostedService>());
+        services.AddReportProvider<SubscribersReportProvider>();
+        services.AddReportProvider<SubscriberCreditHistoryReportProvider>();
 
         services.AddSingleton(new BuiltInPermissionScopeProvider.RouteValueToStoreIdQuery(
             "offeringId", "SELECT a.\"StoreDataId\" FROM \"Apps\" a JOIN subs_offerings o ON o.app_id=a.\"Id\" WHERE o.id=@id"
diff --git a/BTCPayServer/Plugins/Subscriptions/SubscriptionsReportProvider.cs b/BTCPayServer/Plugins/Subscriptions/SubscriptionsReportProvider.cs
new file mode 100644
index 0000000..bc06cd6
--- /dev/null
+++ b/BTCPayServer/Plugins/Subscriptions/SubscriptionsReportProvider.cs
@@ -0,0 +1,126 @@
+using System.Collections.Generic;
+using System.Linq;
+using System.Threading;
+using System.Threading.Tasks;
+using BTCPayServer.Client.Models;
+using BTCPayServer.Data;
+using BTCPayServer.Services;
+using BTCPayServer.Services.Reporting;
+using Microsoft.EntityFrameworkCore;
+
+namespace BTCPayServer.Plugins.Subscriptions;
+
+public class SubscribersReportProvider(ApplicationDbContextFactory dbContextFactory, DisplayFormatter displayFormatter) : ReportProvider
+{
+    public override string Name => "Subscribers";
+
+    public override async Task Query(QueryContext queryContext, CancellationToken cancellation)
+    {
+        queryContext.ViewDefinition = new ViewDefinition
+        {
+            Fields = new List<StoreReportResponse.Field>
+            {
+                new("Created", "datetime"),
+                new("Offering", "text"),
+                new("Plan", "text"),
+                new("Email", "text"),
+                new("CustomerId", "text"),
+                new("Phase", "text"),
+                new("Active", "boolean"),
+                new("CreditBalance", "amount"),
+                new("NextBilling", "datetime"),
+            },
+            Charts =
+            {
+                new()
+                {
+                    Name = "Active subscribers by plan",
+                    Groups = { "Plan" },
+                    Aggregates = { "CreditBalance" },
+                    HasGrandTotal = true
+                }
+            }
+        };
+
+        await using var ctx = dbContextFactory.CreateContext();
+        var subscribers = await ctx.Subscribers.AsNoTracking()
+            .Include(s => s.Plan).ThenInclude(p => p.Offering).ThenInclude(o => o.App)
+            .Include(s => s.Customer).ThenInclude(c => c.CustomerIdentities)
+            .Include(s => s.Credits)
+            .Where(s => s.Plan.Offering.App.StoreDataId == queryContext.StoreId)
+            .Where(s => s.CreatedAt >= queryContext.From && s.CreatedAt <= queryContext.To)
+            .ToListAsync(cancellation);
+
+        foreach (var sub in subscribers)
+        {
+            var data = queryContext.AddData();
+            data.Add(sub.CreatedAt);
+            data.Add(sub.Plan.Offering.App.Name);
+            data.Add(sub.Plan.Name);
+            data.Add(sub.Customer.GetPrimaryIdentity());
+            data.Add(sub.CustomerId);
+            data.Add(sub.Phase.ToString());
+            data.Add(sub.IsActive);
+            data.Add(displayFormatter.ToFormattedAmount(sub.GetCredit(), sub.Plan.Currency));
+            data.Add(sub.NextPaymentDue);
+        }
+    }
+}
+
+public class SubscriberCreditHistoryReportProvider(ApplicationDbContextFactory dbContextFactory, DisplayFormatter displayFormatter) : ReportProvider
+{
+    public override string Name => "Credit History";
+
+    public override async Task Query(QueryContext queryContext, CancellationToken cancellation)
+    {
+        queryContext.ViewDefinition = new ViewDefinition
+        {
+            Fields = new List<StoreReportResponse.Field>
+            {
+                new("Date", "datetime"),
+                new("Email", "text"),
+                new("Offering", "text"),
+                new("OfferingId", "text"),
+                new("Plan", "text"),
+                new("Description", "text"),
+                new("Debit", "amount"),
+                new("Credit", "amount"),
+                new("Balance", "amount"),
+            },
+            Charts =
+            {
+                new()
+                {
+                    Name = "Revenue by plan",
+                    Groups = { "Plan" },
+                    Aggregates = { "Debit" },
+                    HasGrandTotal = true
+                }
+            }
+        };
+
+        await using var ctx = dbContextFactory.CreateContext();
+        var history = await ctx.SubscriberCreditHistory.AsNoTracking()
+            .Include(h => h.SubscriberCredit).ThenInclude(c => c.Subscriber).ThenInclude(s => s.Plan).ThenInclude(p => p.Offering).ThenInclude(o => o.App)
+            .Include(h => h.SubscriberCredit).ThenInclude(c => c.Subscriber).ThenInclude(s => s.Customer).ThenInclude(c => c.CustomerIdentities)
+            .Where(h => h.SubscriberCredit.Subscriber.Plan.Offering.App.StoreDataId == queryContext.StoreId)
+            .Where(h => h.CreatedAt >= queryContext.From && h.CreatedAt <= queryContext.To)
+            .OrderBy(h => h.CreatedAt).ToListAsync(cancellation);
+
+        foreach (var entry in history)
+        {
+            var sub = entry.SubscriberCredit.Subscriber;
+            var currency = entry.Currency;
+            var data = queryContext.AddData();
+            data.Add(entry.CreatedAt);
+            data.Add(sub.Customer.GetPrimaryIdentity());
+            data.Add(sub.Plan.Offering.App.Name);
+            data.Add(sub.Plan.OfferingId);
+            data.Add(sub.Plan.Name);
+            data.Add(entry.Description);
+            data.Add(entry.Debit > 0 ? displayFormatter.ToFormattedAmount(entry.Debit, currency) : null);
+            data.Add(entry.Credit > 0 ? displayFormatter.ToFormattedAmount(entry.Credit, currency) : null);
+            data.Add(displayFormatter.ToFormattedAmount(entry.Balance, currency));
+        }
+    }
+}