Data-Driven Report

using Scryber.Components;
using Scryber.PDF;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Net.Http;
using System.Text.Json;
using System.Threading.Tasks;

public class DataDrivenReportGenerator
{
    private readonly string _templatePath;
    private readonly string _connectionString;
    private readonly HttpClient _httpClient;

    public DataDrivenReportGenerator(
        string templatePath,
        string connectionString = null)
    {
        _templatePath = templatePath;
        _connectionString = connectionString;
        _httpClient = new HttpClient();
    }

    public async Task<Stream> GenerateReportAsync(ReportConfiguration config)
    {
        // Collect data from multiple sources
        var reportData = new ReportData
        {
            Company = config.Company,
            Report = new Report
            {
                Title = config.Title,
                Subtitle = config.Subtitle,
                Period = config.Period,
                GeneratedDate = DateTime.Now.ToString("MMMM dd, yyyy HH:mm"),
                Id = Guid.NewGuid().ToString("N").Substring(0, 8).ToUpper()
            }
        };

        // Load data from database
        if (!string.IsNullOrEmpty(_connectionString))
        {
            await LoadDatabaseDataAsync(reportData, config);
        }

        // Load data from API
        if (!string.IsNullOrEmpty(config.ApiEndpoint))
        {
            await LoadApiDataAsync(reportData, config);
        }

        // Calculate aggregates and KPIs
        CalculateKPIs(reportData);
        CalculateTotals(reportData);
        GenerateRecommendations(reportData);

        // Generate charts (if needed)
        if (config.GenerateCharts)
        {
            reportData.Charts = await GenerateChartsAsync(reportData);
        }

        // Generate PDF
        return GeneratePDF(reportData);
    }

    private async Task LoadDatabaseDataAsync(
        ReportData reportData,
        ReportConfiguration config)
    {
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();

            // Load sales by region
            reportData.SalesByRegion = await LoadSalesByRegionAsync(
                connection,
                config.StartDate,
                config.EndDate);

            // Load top products
            reportData.TopProducts = await LoadTopProductsAsync(
                connection,
                config.StartDate,
                config.EndDate,
                limit: 10);

            // Load customer segments
            reportData.CustomerSegments = await LoadCustomerSegmentsAsync(
                connection,
                config.StartDate,
                config.EndDate);
        }
    }

    private async Task<List<RegionalSales>> LoadSalesByRegionAsync(
        SqlConnection connection,
        DateTime startDate,
        DateTime endDate)
    {
        var query = @"
            SELECT
                r.RegionName as Region,
                SUM(o.TotalAmount) as Sales,
                r.Target,
                COUNT(DISTINCT o.CustomerId) as CustomerCount
            FROM Orders o
            JOIN Regions r ON o.RegionId = r.RegionId
            WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
            GROUP BY r.RegionName, r.Target
            ORDER BY Sales DESC";

        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@StartDate", startDate);
            command.Parameters.AddWithValue("@EndDate", endDate);

            var results = new List<RegionalSales>();

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    var sales = reader.GetDecimal(1);
                    var target = reader.GetDecimal(2);
                    var achievement = (sales / target) * 100;

                    results.Add(new RegionalSales
                    {
                        Region = reader.GetString(0),
                        Sales = sales,
                        Target = target,
                        Achievement = achievement,
                        Growth = CalculateGrowth(sales, target),
                        Status = achievement >= 100 ? "Exceeded" :
                                achievement >= 90 ? "On Track" :
                                achievement >= 75 ? "At Risk" : "Behind",
                        StatusClass = achievement >= 100 ? "completed" :
                                     achievement >= 90 ? "active" :
                                     achievement >= 75 ? "pending" : "cancelled"
                    });
                }
            }

            return results;
        }
    }

    private async Task<List<ProductPerformance>> LoadTopProductsAsync(
        SqlConnection connection,
        DateTime startDate,
        DateTime endDate,
        int limit)
    {
        var query = @"
            SELECT TOP (@Limit)
                p.ProductName as Name,
                p.Category,
                SUM(oi.Quantity) as UnitsSold,
                SUM(oi.Quantity * oi.UnitPrice) as Revenue,
                AVG((oi.UnitPrice - oi.Cost) / oi.UnitPrice * 100) as Margin
            FROM OrderItems oi
            JOIN Products p ON oi.ProductId = p.ProductId
            JOIN Orders o ON oi.OrderId = o.OrderId
            WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
            GROUP BY p.ProductName, p.Category
            ORDER BY Revenue DESC";

        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@Limit", limit);
            command.Parameters.AddWithValue("@StartDate", startDate);
            command.Parameters.AddWithValue("@EndDate", endDate);

            var results = new List<ProductPerformance>();
            int rank = 1;

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    results.Add(new ProductPerformance
                    {
                        Rank = rank++,
                        Name = reader.GetString(0),
                        Category = reader.GetString(1),
                        UnitsSold = reader.GetInt32(2),
                        Revenue = reader.GetDecimal(3),
                        Margin = reader.GetDecimal(4)
                    });
                }
            }

            return results;
        }
    }

    private async Task<List<CustomerSegment>> LoadCustomerSegmentsAsync(
        SqlConnection connection,
        DateTime startDate,
        DateTime endDate)
    {
        var query = @"
            SELECT
                cs.SegmentName as Segment,
                cs.Description,
                COUNT(DISTINCT c.CustomerId) as CustomerCount,
                AVG(o.TotalAmount) as AvgOrderValue,
                SUM(o.TotalAmount) / COUNT(DISTINCT c.CustomerId) as LifetimeValue
            FROM Customers c
            JOIN CustomerSegments cs ON c.SegmentId = cs.SegmentId
            LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
            WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
            GROUP BY cs.SegmentName, cs.Description
            ORDER BY LifetimeValue DESC";

        using (var command = new SqlCommand(query, connection))
        {
            command.Parameters.AddWithValue("@StartDate", startDate);
            command.Parameters.AddWithValue("@EndDate", endDate);

            var results = new List<CustomerSegment>();

            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    results.Add(new CustomerSegment
                    {
                        Segment = reader.GetString(0),
                        Description = reader.GetString(1),
                        CustomerCount = reader.GetInt32(2),
                        AvgOrderValue = reader.GetDecimal(3),
                        LifetimeValue = reader.GetDecimal(4)
                    });
                }
            }

            return results;
        }
    }

    private async Task LoadApiDataAsync(
        ReportData reportData,
        ReportConfiguration config)
    {
        try
        {
            var response = await _httpClient.GetAsync(config.ApiEndpoint);
            response.EnsureSuccessStatusCode();

            var json = await response.Content.ReadAsStringAsync();
            var apiData = JsonSerializer.Deserialize<ApiDataResponse>(json);

            // Merge API data with report data
            if (apiData != null)
            {
                // Example: Add external metrics
                reportData.Report.Alerts = apiData.Alerts;
                reportData.Report.HasAlerts = apiData.Alerts?.Any() == true;
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Warning: Failed to load API data: {ex.Message}");
        }
    }

    private void CalculateKPIs(ReportData reportData)
    {
        var totalSales = reportData.SalesByRegion?.Sum(r => r.Sales) ?? 0;
        var totalTarget = reportData.SalesByRegion?.Sum(r => r.Target) ?? 0;
        var achievement = totalTarget > 0 ? (totalSales / totalTarget * 100) : 0;

        reportData.Kpis = new List<Kpi>
        {
            new Kpi
            {
                Label = "Total Revenue",
                Value = $"${totalSales:N0}",
                Change = "↑ 23.5%",
                ChangeClass = "positive"
            },
            new Kpi
            {
                Label = "Target Achievement",
                Value = $"{achievement:F1}%",
                Change = achievement >= 100 ? "↑ Target Met" : "↓ Below Target",
                ChangeClass = achievement >= 100 ? "positive" : "negative"
            },
            new Kpi
            {
                Label = "Total Orders",
                Value = reportData.TopProducts?.Sum(p => p.UnitsSold).ToString("N0") ?? "0",
                Change = "↑ 18.2%",
                ChangeClass = "positive"
            },
            new Kpi
            {
                Label = "Avg Margin",
                Value = $"{reportData.TopProducts?.Average(p => p.Margin):F1}%",
                Change = "↑ 2.3%",
                ChangeClass = "positive"
            }
        };
    }

    private void CalculateTotals(ReportData reportData)
    {
        if (reportData.SalesByRegion?.Any() == true)
        {
            reportData.Totals = new Totals
            {
                Sales = reportData.SalesByRegion.Sum(r => r.Sales),
                Target = reportData.SalesByRegion.Sum(r => r.Target),
                Achievement = 0,
                Growth = reportData.SalesByRegion.Average(r => r.Growth)
            };

            reportData.Totals.Achievement =
                (reportData.Totals.Sales / reportData.Totals.Target) * 100;
        }
    }

    private void GenerateRecommendations(ReportData reportData)
    {
        var recommendations = new List<Recommendation>();

        // Analyze performance and generate recommendations
        if (reportData.Totals?.Achievement < 90)
        {
            recommendations.Add(new Recommendation
            {
                Title = "Focus on Underperforming Regions",
                Description = "Northeast and West regions are below target. Consider increasing marketing spend and sales support in these areas.",
                Impact = "Potential 15% increase in Q4 revenue"
            });
        }

        var topMarginProduct = reportData.TopProducts?
            .OrderByDescending(p => p.Margin)
            .FirstOrDefault();

        if (topMarginProduct != null)
        {
            recommendations.Add(new Recommendation
            {
                Title = $"Promote High-Margin Products",
                Description = $"'{topMarginProduct.Name}' has the highest margin at {topMarginProduct.Margin:F1}%. Increase promotional efforts for similar products.",
                Impact = "Potential 8% improvement in overall margins"
            });
        }

        reportData.Recommendations = recommendations;
    }

    private async Task<Charts> GenerateChartsAsync(ReportData reportData)
    {
        // In a real implementation, you would:
        // 1. Use a charting library (e.g., ScottPlot, OxyPlot)
        // 2. Generate chart images
        // 3. Save to temp files
        // 4. Return file paths

        return new Charts
        {
            SalesTrend = "./charts/sales-trend.png",
            RegionalBreakdown = "./charts/regional-breakdown.png"
        };
    }

    private Stream GeneratePDF(ReportData reportData)
    {
        var doc = Document.ParseDocument(_templatePath);

        // Set metadata
        doc.Info.Title = reportData.Report.Title;
        doc.Info.Author = reportData.Company.Name;
        doc.Info.CreationDate = DateTime.Now;

        // Bind data
        doc.Params["company"] = reportData.Company;
        doc.Params["report"] = reportData.Report;
        doc.Params["kpis"] = reportData.Kpis;
        doc.Params["salesByRegion"] = reportData.SalesByRegion;
        doc.Params["topProducts"] = reportData.TopProducts;
        doc.Params["customerSegments"] = reportData.CustomerSegments;
        doc.Params["totals"] = reportData.Totals;
        doc.Params["recommendations"] = reportData.Recommendations;
        doc.Params["charts"] = reportData.Charts;

        // Configure for performance
        doc.RenderOptions.Compression = PDFCompressionType.FlateDecode;
        doc.RenderOptions.FontSubsetting = true;

        // Generate
        var output = new MemoryStream();
        doc.SaveAsPDF(output);
        output.Position = 0;

        return output;
    }

    private decimal CalculateGrowth(decimal current, decimal previous)
    {
        if (previous == 0) return 0;
        return ((current - previous) / previous) * 100;
    }
}

// Data models (continued in next section)

public class ReportConfiguration
{
    public CompanyInfo Company { get; set; }
    public string Title { get; set; }
    public string Subtitle { get; set; }
    public string Period { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
    public string ApiEndpoint { get; set; }
    public bool GenerateCharts { get; set; } = true;
}

public class ReportData
{
    public CompanyInfo Company { get; set; }
    public Report Report { get; set; }
    public List<Kpi> Kpis { get; set; } = new List<Kpi>();
    public List<RegionalSales> SalesByRegion { get; set; } = new List<RegionalSales>();
    public List<ProductPerformance> TopProducts { get; set; } = new List<ProductPerformance>();
    public List<CustomerSegment> CustomerSegments { get; set; } = new List<CustomerSegment>();
    public Totals Totals { get; set; }
    public List<Recommendation> Recommendations { get; set; } = new List<Recommendation>();
    public Charts Charts { get; set; }
}

public class CompanyInfo
{
    public string Name { get; set; }
}

public class Report
{
    public string Title { get; set; }
    public string Subtitle { get; set; }
    public string Period { get; set; }
    public string GeneratedDate { get; set; }
    public string Id { get; set; }
    public bool HasAlerts { get; set; }
    public List<string> Alerts { get; set; }
}

public class Kpi
{
    public string Label { get; set; }
    public string Value { get; set; }
    public string Change { get; set; }
    public string ChangeClass { get; set; }
}

public class RegionalSales
{
    public string Region { get; set; }
    public decimal Sales { get; set; }
    public decimal Target { get; set; }
    public decimal Achievement { get; set; }
    public decimal Growth { get; set; }
    public string Status { get; set; }
    public string StatusClass { get; set; }
}

public class ProductPerformance
{
    public int Rank { get; set; }
    public string Name { get; set; }
    public string Category { get; set; }
    public int UnitsSold { get; set; }
    public decimal Revenue { get; set; }
    public decimal Margin { get; set; }
}

public class CustomerSegment
{
    public string Segment { get; set; }
    public string Description { get; set; }
    public int CustomerCount { get; set; }
    public decimal AvgOrderValue { get; set; }
    public decimal LifetimeValue { get; set; }
}

public class Totals
{
    public decimal Sales { get; set; }
    public decimal Target { get; set; }
    public decimal Achievement { get; set; }
    public decimal Growth { get; set; }
}

public class Recommendation
{
    public string Title { get; set; }
    public string Description { get; set; }
    public string Impact { get; set; }
}

public class Charts
{
    public string SalesTrend { get; set; }
    public string RegionalBreakdown { get; set; }
}

public class ApiDataResponse
{
    public List<string> Alerts { get; set; }
}

// Configure report
var config = new ReportConfiguration
{
    Company = new CompanyInfo { Name = "Acme Corporation" },
    Title = "Q4 2025 Sales Performance Report",
    Subtitle = "Comprehensive Analysis and Insights",
    Period = "October - December 2025",
    StartDate = new DateTime(2025, 10, 1),
    EndDate = new DateTime(2025, 12, 31),
    ApiEndpoint = "https://api.acme.com/alerts",
    GenerateCharts = true
};

// Generate report
var generator = new DataDrivenReportGenerator(
    "data-driven-report-template.html",
    "Server=localhost;Database=SalesDB;Integrated Security=true;"
);

using (var reportStream = await generator.GenerateReportAsync(config))
{
    using (var fileStream = new FileStream("q4-sales-report.pdf", FileMode.Create))
    {
        await reportStream.CopyToAsync(fileStream);
        Console.WriteLine("Data-driven report generated successfully!");
    }
}