using CareFix.Api.Infrastructure;
using Dapper;

namespace CareFix.Api.Tickets;

public sealed class ReportService(ControlDb db)
{
    private sealed class ClosedRow { public DateTime CreatedAt { get; set; } public DateTime ClosedAt { get; set; } }

    public async Task<object> SummaryAsync(DateTime from, DateTime to, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var p = new { from, to = to.Date.AddDays(1) };
        using var m = await c.QueryMultipleAsync("""
            SELECT State, COUNT(*) AS Count FROM CF_TICKET WHERE CreatedAt >= @from AND CreatedAt < @to GROUP BY State;
            SELECT TOP 20 h.Name AS Hospital, COUNT(*) AS Tickets,
                   SUM(CASE WHEN t.State = 'Closed' THEN 1 ELSE 0 END) AS Closed
              FROM CF_TICKET t JOIN CF_HOSPITAL h ON h.HospitalId = t.HospitalId
              WHERE t.CreatedAt >= @from AND t.CreatedAt < @to GROUP BY h.Name ORDER BY COUNT(*) DESC;
            SELECT CreatedAt, ClosedAt FROM CF_TICKET WHERE ClosedAt IS NOT NULL AND CreatedAt >= @from AND CreatedAt < @to;
            SELECT f.Risk, f.State, COUNT(*) AS Count FROM CF_FIX f JOIN CF_TICKET t ON t.TicketId = f.TicketId
              WHERE t.CreatedAt >= @from AND t.CreatedAt < @to GROUP BY f.Risk, f.State;
            SELECT COUNT(*) AS Executed,
                   SUM(CASE WHEN e.VerifiedResolved = 1 THEN 1 ELSE 0 END) AS VerifiedResolved,
                   SUM(CASE WHEN e.RolledBackAt IS NOT NULL THEN 1 ELSE 0 END) AS RolledBack
              FROM CF_EXECUTION e JOIN CF_FIX f ON f.FixId = e.FixId JOIN CF_TICKET t ON t.TicketId = f.TicketId
              WHERE e.Status = 'Success' AND t.CreatedAt >= @from AND t.CreatedAt < @to;
            SELECT TOP 20 u.FullName AS Engineer, COUNT(*) AS Tickets, SUM(CASE WHEN t.State = 'Closed' THEN 1 ELSE 0 END) AS Closed
              FROM CF_TICKET t JOIN CF_USER u ON u.UserId = t.RaisedBy
              WHERE t.CreatedAt >= @from AND t.CreatedAt < @to GROUP BY u.FullName ORDER BY COUNT(*) DESC;
            SELECT COUNT(*) AS Queries, SUM(CASE WHEN Blocked = 1 THEN 1 ELSE 0 END) AS Blocked
              FROM CF_QUERY_LOG WHERE CreatedAt >= @from AND CreatedAt < @to;
            SELECT ISNULL(SUM(CostUsd), 0) AS TotalUsd,
                   ISNULL(SUM(CASE WHEN Purpose = 'Learning' THEN CostUsd ELSE 0 END), 0) AS LearningUsd,
                   COUNT(DISTINCT TicketId) AS TicketsWithAi
              FROM CF_AI_USAGE WHERE CreatedAt >= @from AND CreatedAt < @to;
            SELECT ISNULL(SUM(CostUsd), 0) FROM CF_AI_USAGE
              WHERE CreatedAt >= DATEFROMPARTS(YEAR(SYSUTCDATETIME()), MONTH(SYSUTCDATETIME()), 1);
            """, p);
        var byState = await m.ReadAsync();
        var byHospital = await m.ReadAsync();
        var closed = (await m.ReadAsync<ClosedRow>()).Select(r => (r.ClosedAt - r.CreatedAt).TotalMinutes).OrderBy(x => x).ToList();
        var fixes = await m.ReadAsync();
        var exec = await m.ReadSingleAsync();
        var byEngineer = await m.ReadAsync();
        var queries = await m.ReadSingleAsync();
        var ai = await m.ReadSingleAsync();
        var monthToDateUsd = await m.ReadSingleAsync<decimal>();

        double? median = closed.Count == 0 ? null
            : closed.Count % 2 == 1 ? closed[closed.Count / 2] : (closed[closed.Count / 2 - 1] + closed[closed.Count / 2]) / 2;
        return new
        {
            from, to,
            byState, byHospital, byEngineer, fixes,
            executions = exec,
            queries,
            ai,
            monthToDateUsd,
            closedTickets = closed.Count,
            medianResolutionMinutes = median is null ? (double?)null : Math.Round(median.Value, 1),
        };
    }
}