using System.Security.Claims;
using System.Text.Json;
using System.Text.Json.Nodes;
using System.Threading.Channels;
using CareFix.Api.Ai;
using CareFix.Api.Infrastructure;
using CareFix.Api.Security;
using CareFix.Core;
using Dapper;
using Microsoft.Data.SqlClient;

namespace CareFix.Api.Learning;

/// <summary>
/// Turns Caresoft's historical support tickets (issue + the SQL that fixed it) into draft playbooks.
/// A Lead reviews every draft; nothing reaches the AI's playbook list without human acceptance.
/// </summary>
public sealed class TicketMiningService(ControlDb db, PiiMasker masker, SqlGuard guard, AuditService audit)
{
    private readonly Channel<int> _queue = Channel.CreateUnbounded<int>();
    public ChannelReader<int> Queue => _queue.Reader;

    private const int MaxTickets = 3000;

    public async Task<int> UploadAsync(ClaimsPrincipal user, string fileName, string csv, CancellationToken ct)
    {
        var rows = Csv.Read(csv);
        if (rows.Count == 0) throw AppException.BadRequest("The CSV has no data rows.");
        if (!rows[0].ContainsKey("IssueText")) throw AppException.BadRequest("The CSV needs an IssueText column (plus SqlUsed and/or Resolution). See kb-templates/ticket-history.csv.");
        if (!rows[0].ContainsKey("SqlUsed") && !rows[0].ContainsKey("Resolution")) throw AppException.BadRequest("The CSV needs a SqlUsed or Resolution column so the AI can learn how each ticket was fixed.");
        var usable = rows.Where(r => !string.IsNullOrWhiteSpace(r.GetValueOrDefault("IssueText"))
                                  && (!string.IsNullOrWhiteSpace(r.GetValueOrDefault("SqlUsed")) || !string.IsNullOrWhiteSpace(r.GetValueOrDefault("Resolution")))).ToList();
        if (usable.Count < 5) throw AppException.BadRequest("At least 5 tickets with both an issue and a fix are needed.");
        if (usable.Count > MaxTickets) throw AppException.BadRequest($"Upload at most {MaxTickets} tickets at a time.");

        await using var c = await db.OpenAsync(ct);
        await using var tx = (SqlTransaction)await c.BeginTransactionAsync(ct);
        var batchId = await c.ExecuteScalarAsync<int>(
            "INSERT CF_MINE_BATCH (FileName, TicketCount, CreatedBy) OUTPUT INSERTED.BatchId VALUES (@fileName, @n, @uid)",
            new { fileName, n = usable.Count, uid = user.UserId() }, tx);
        var i = 0;
        foreach (var r in usable)
        {
            i++;
            string? M(string k) => r.GetValueOrDefault(k) is { Length: > 0 } v ? masker.MaskText(v.Trim()) : null;
            await c.ExecuteAsync("""
                INSERT CF_MINE_TICKET (BatchId, RowNo, Ref, Module, IssueText, Resolution, SqlUsed)
                VALUES (@batchId, @i, @ref, @module, @issue, @res, @sql)
                """, new
            {
                batchId, i,
                @ref = Csv.NullIfEmpty(r.GetValueOrDefault("TicketNo")) ?? $"row {i + 1}",
                module = Csv.NullIfEmpty(r.GetValueOrDefault("Module")),
                issue = M("IssueText"), res = M("Resolution"), sql = M("SqlUsed"),
            }, tx);
        }
        await tx.CommitAsync(ct);
        await audit.LogAsync(user.UserId(), null, "TicketHistoryUploaded", new { batchId, fileName, tickets = usable.Count }, ct);
        await _queue.Writer.WriteAsync(batchId, ct);
        return batchId;
    }

    public async Task<object> BatchesAsync(CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QueryAsync("""
            SELECT b.BatchId, b.FileName, b.TicketCount, b.State, b.Progress, b.Error, b.CreatedAt, b.FinishedAt, u.FullName AS CreatedByName,
                   (SELECT COUNT(*) FROM CF_PLAYBOOK_DRAFT d WHERE d.BatchId = b.BatchId AND d.State = 'Draft') AS OpenDrafts,
                   (SELECT COUNT(*) FROM CF_PLAYBOOK_DRAFT d WHERE d.BatchId = b.BatchId AND d.State = 'Accepted') AS Accepted
            FROM CF_MINE_BATCH b JOIN CF_USER u ON u.UserId = b.CreatedBy ORDER BY b.BatchId DESC
            """);
    }

    public async Task<object> DraftsAsync(int batchId, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QueryAsync("SELECT * FROM CF_PLAYBOOK_DRAFT WHERE BatchId = @batchId ORDER BY CASE State WHEN 'Draft' THEN 0 ELSE 1 END, TicketCount DESC", new { batchId });
    }

    public sealed record DraftEdit(string Title, string? IssueType, string? Module, string Keywords, string? Description, string? DiagnosisSql, string? FixGuidance, string Risk);

    public async Task<int> AcceptAsync(ClaimsPrincipal user, int draftId, DraftEdit e, CancellationToken ct)
    {
        if (user.Role() is not (Roles.Lead or Roles.Head or Roles.Admin)) throw AppException.Forbidden("Only a Lead, Head or Admin can accept playbooks.");
        if (string.IsNullOrWhiteSpace(e.Title) || string.IsNullOrWhiteSpace(e.Keywords)) throw AppException.BadRequest("Title and keywords are required.");
        if (e.Risk is not ("Low" or "Medium" or "High")) throw AppException.BadRequest("Risk must be Low, Medium or High.");
        if (!string.IsNullOrWhiteSpace(e.DiagnosisSql) && guard.ValidateSelect(e.DiagnosisSql, null) is { Ok: false } g)
            throw AppException.BadRequest("The diagnosis query must be a single SELECT: " + g.Reason);

        await using var c = await db.OpenAsync(ct);
        await using var tx = (SqlTransaction)await c.BeginTransactionAsync(ct);
        var state = await c.ExecuteScalarAsync<string?>("SELECT State FROM CF_PLAYBOOK_DRAFT WITH (UPDLOCK) WHERE DraftId = @draftId", new { draftId }, tx)
                    ?? throw AppException.NotFound("Draft not found.");
        if (state != "Draft") throw AppException.Conflict($"This draft is already {state}.");
        var count = await c.ExecuteScalarAsync<int>("SELECT TicketCount FROM CF_PLAYBOOK_DRAFT WHERE DraftId = @draftId", new { draftId }, tx);
        var id = await c.ExecuteScalarAsync<int>("""
            INSERT CF_PLAYBOOK (Title, IssueType, Module, Keywords, Description, DiagnosisSql, FixGuidance, Risk, CreatedBy, SuccessCount)
            OUTPUT INSERTED.PlaybookId VALUES (@Title, @IssueType, @Module, @Keywords, @Description, @DiagnosisSql, @FixGuidance, @Risk, @uid, @count)
            """, new { e.Title, e.IssueType, e.Module, e.Keywords, e.Description, e.DiagnosisSql, e.FixGuidance, e.Risk, uid = user.UserId(), count }, tx);
        await c.ExecuteAsync("UPDATE CF_PLAYBOOK_DRAFT SET State='Accepted', ReviewedBy=@uid, ReviewedAt=SYSUTCDATETIME(), PlaybookId=@id WHERE DraftId=@draftId",
            new { draftId, uid = user.UserId(), id }, tx);
        await tx.CommitAsync(ct);
        await audit.LogAsync(user.UserId(), null, "PlaybookDraftAccepted", new { draftId, playbookId = id, e.Title }, ct);
        return id;
    }

    public async Task DiscardAsync(ClaimsPrincipal user, int draftId, CancellationToken ct)
    {
        if (user.Role() is not (Roles.Lead or Roles.Head or Roles.Admin)) throw AppException.Forbidden("Only a Lead, Head or Admin can discard drafts.");
        await using var c = await db.OpenAsync(ct);
        var n = await c.ExecuteAsync("UPDATE CF_PLAYBOOK_DRAFT SET State='Discarded', ReviewedBy=@uid, ReviewedAt=SYSUTCDATETIME() WHERE DraftId=@draftId AND State='Draft'",
            new { draftId, uid = user.UserId() });
        if (n == 0) throw AppException.Conflict("Draft not found or already reviewed.");
        await audit.LogAsync(user.UserId(), null, "PlaybookDraftDiscarded", new { draftId }, ct);
    }
}

/// <summary>Background worker: clusters ticket history with Claude in two passes (per chunk, then merge).</summary>
public sealed class TicketMiningWorker(TicketMiningService svc, ControlDb db, ClaudeClient claude, AiUsageService usage, ILogger<TicketMiningWorker> log) : BackgroundService
{
    private const int ChunkSize = 40;
    private const int MaxChunkChars = 60_000;

    private sealed class TicketRow
    {
        public string Ref { get; set; } = "";
        public string? Module { get; set; }
        public string IssueText { get; set; } = "";
        public string? Resolution { get; set; }
        public string? SqlUsed { get; set; }
    }

    private sealed class Pattern
    {
        public string Title { get; set; } = "";
        public string? IssueType { get; set; }
        public string? Module { get; set; }
        public JsonElement Keywords { get; set; }   // the model sometimes returns a list, sometimes a string
        public string KeywordText => Keywords.ValueKind switch
        {
            JsonValueKind.Array => string.Join(", ", Keywords.EnumerateArray().Select(k => k.ToString().Trim()).Where(k => k.Length > 0)),
            JsonValueKind.String => Keywords.GetString() ?? "",
            _ => "",
        };
        public string? Description { get; set; }
        public string? DiagnosisSql { get; set; }
        public string? FixGuidance { get; set; }
        public string Risk { get; set; } = "Medium";
        [System.Text.Json.Serialization.JsonPropertyName("refs")]
        public JsonElement RefsJson { get; set; }
        [System.Text.Json.Serialization.JsonIgnore]
        public List<string> Refs { get; set; } = [];
        public List<int> SourceIds { get; set; } = [];
    }

    private sealed class PatternList { public List<Pattern> Patterns { get; set; } = []; }

    private static readonly JsonSerializerOptions Json = new(JsonSerializerDefaults.Web);

    private const string System1 = """
        You analyse historical Caresoft HIS (hospital information system, Microsoft SQL Server) database support tickets
        to find recurring data problems and the way support fixed them. Patient details are already masked.
        Reply with JSON only, no prose and no code fences.
        """;

    private const string Instructions1 = """
        Group these tickets by the same underlying data problem AND the same kind of fix. Skip one-off tickets unless the fix is clearly reusable.
        For each group return:
        - title: short, plain, e.g. "Duplicate pharmacy issue on IPD bill"
        - issueType: e.g. Duplicate entry, Wrong amount, Wrong patient, Wrong date, Stuck status
        - module: HIS module
        - keywords: 5 to 10 words or short phrases engineers and hospital staff would use, including common Hinglish
        - description: 1-2 sentences, the symptom and the cause
        - diagnosisSql: ONE SELECT template to find the wrong records, with placeholders like @BillNo, @IpdNo, @Uhid. Empty string if unclear.
        - fixGuidance: which table.column changes to what, which linked tables must change together, what to check first. Never delete rows; use cancel/status flags.
        - risk: High if amounts, receipts, refunds, ledger or Tally are touched; Medium if clinical or status data; Low otherwise
        - refs: the ticket refs in the group
        Output: {"patterns":[...]}
        """;

    private const string Instructions2 = """
        These patterns came from separate batches of the same ticket history, so some are duplicates.
        Merge patterns that describe the same problem and fix. Keep distinct ones separate.
        For each final pattern return the same fields as the input (title, issueType, module, keywords, description, diagnosisSql, fixGuidance, risk)
        plus sourceIds: the input ids that were merged into it. Every input id must appear in exactly one output pattern.
        Output: {"patterns":[...]}
        """;

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        // Batches interrupted by a restart are marked failed so a Lead can upload again.
        await using (var c = await db.OpenAsync(stoppingToken))
            await c.ExecuteAsync("UPDATE CF_MINE_BATCH SET State='Failed', Error='Interrupted by a server restart. Upload the file again.' WHERE State IN ('Queued','Processing')");

        await foreach (var batchId in svc.Queue.ReadAllAsync(stoppingToken))
        {
            try { await ProcessAsync(batchId, stoppingToken); }
            catch (OperationCanceledException) when (stoppingToken.IsCancellationRequested) { return; }
            catch (Exception ex)
            {
                log.LogError(ex, "Ticket mining batch {BatchId} failed", batchId);
                await SetAsync(batchId, "Failed", null, ex is AppException ? ex.Message : "Unexpected error; see the server log.");
            }
        }
    }

    private async Task SetAsync(int batchId, string state, string? progress, string? error = null)
    {
        await using var c = await db.OpenAsync();
        await c.ExecuteAsync("""
            UPDATE CF_MINE_BATCH SET State=@state, Progress=@progress, Error=@error,
                   FinishedAt = CASE WHEN @state IN ('Done','Failed') THEN SYSUTCDATETIME() ELSE FinishedAt END
            WHERE BatchId=@batchId
            """, new { batchId, state, progress, error });
    }

    private async Task ProcessAsync(int batchId, CancellationToken ct)
    {
        await usage.EnsureMonthlyBudgetAsync(ct);
        List<TicketRow> tickets;
        await using (var c = await db.OpenAsync(ct))
            tickets = (await c.QueryAsync<TicketRow>("SELECT Ref, Module, IssueText, Resolution, SqlUsed FROM CF_MINE_TICKET WHERE BatchId=@batchId ORDER BY RowNo", new { batchId })).ToList();

        // Chunk by count and size.
        var chunks = new List<List<TicketRow>>();
        var cur = new List<TicketRow>(); var chars = 0;
        foreach (var t in tickets)
        {
            var size = t.IssueText.Length + (t.Resolution?.Length ?? 0) + (t.SqlUsed?.Length ?? 0);
            if (cur.Count > 0 && (cur.Count >= ChunkSize || chars + size > MaxChunkChars)) { chunks.Add(cur); cur = []; chars = 0; }
            cur.Add(t); chars += size;
        }
        if (cur.Count > 0) chunks.Add(cur);

        var all = new List<Pattern>();
        for (var i = 0; i < chunks.Count; i++)
        {
            await SetAsync(batchId, "Processing", $"Reading tickets: part {i + 1} of {chunks.Count}");
            var payload = JsonSerializer.Serialize(chunks[i].Select(t => new
            {
                @ref = t.Ref, module = t.Module, issue = Cut(t.IssueText, 1500), resolution = Cut(t.Resolution, 1000), sql = Cut(t.SqlUsed, 2000),
            }));
            var found = await AskAsync(System1, Instructions1 + "\n\nTickets:\n" + payload, ct);
            all.AddRange(found.Patterns.Where(p => !string.IsNullOrWhiteSpace(p.Title)));
        }

        List<Pattern> final;
        if (chunks.Count == 1 || all.Count <= 1) final = all;
        else
        {
            await SetAsync(batchId, "Processing", $"Merging {all.Count} patterns");
            var indexed = all.Select((p, i) => new { id = i, p.Title, p.IssueType, p.Module, keywords = p.KeywordText, p.Description, p.DiagnosisSql, p.FixGuidance, p.Risk, tickets = p.Refs.Count });
            var merged = await AskAsync(System1, Instructions2 + "\n\nPatterns:\n" + JsonSerializer.Serialize(indexed), ct);
            final = [];
            var used = new HashSet<int>();
            foreach (var m in merged.Patterns)
            {
                var ids = m.SourceIds.Where(id => id >= 0 && id < all.Count && used.Add(id)).ToList();
                if (ids.Count == 0) continue;
                m.Refs = ids.SelectMany(id => all[id].Refs).Distinct().ToList();
                final.Add(m);
            }
            // Anything the merge step dropped is kept as-is rather than lost.
            final.AddRange(all.Where((_, i) => !used.Contains(i)));
        }

        await using (var c = await db.OpenAsync(ct))
        {
            foreach (var p in final.OrderByDescending(p => p.Refs.Count))
            {
                var risk = p.Risk is "Low" or "Medium" or "High" ? p.Risk : "Medium";
                await c.ExecuteAsync("""
                    INSERT CF_PLAYBOOK_DRAFT (BatchId, Title, IssueType, Module, Keywords, Description, DiagnosisSql, FixGuidance, Risk, TicketCount, SampleRefs)
                    VALUES (@batchId, @title, @issueType, @module, @keywords, @description, @diagnosisSql, @fixGuidance, @risk, @count, @refs)
                    """, new
                {
                    batchId, title = Cut(p.Title, 200)!, issueType = Cut(p.IssueType, 100), module = Cut(p.Module, 50),
                    keywords = Cut(p.KeywordText, 400),
                    description = Cut(p.Description, 2000), diagnosisSql = string.IsNullOrWhiteSpace(p.DiagnosisSql) ? null : p.DiagnosisSql,
                    fixGuidance = p.FixGuidance, risk, count = Math.Max(1, p.Refs.Count), refs = Cut(string.Join(", ", p.Refs.Take(30)), 2000),
                });
            }
        }
        await SetAsync(batchId, "Done", $"{final.Count} draft playbooks from {tickets.Count} tickets");
    }

    private async Task<PatternList> AskAsync(string system, string user, CancellationToken ct)
    {
        var resp = await claude.CreateMessageAsync(
            new JsonArray(new JsonObject { ["type"] = "text", ["text"] = system }),
            null,
            new JsonArray(new JsonObject { ["role"] = "user", ["content"] = user }),
            ct, maxTokens: 12000, usageContext: new UsageContext(null, "Learning"));
        var text = string.Concat((resp["content"] as JsonArray ?? new JsonArray())
            .Where(b => b?["type"]?.ToString() == "text").Select(b => b!["text"]!.ToString())).Trim();
        if (text.StartsWith("```")) text = text.Trim('`').Replace("json", "", StringComparison.OrdinalIgnoreCase).Trim();
        var start = text.IndexOf('{'); var end = text.LastIndexOf('}');
        if (start < 0 || end <= start) throw new AppException(502, "The AI did not return patterns in the expected format. Try the upload again.");
        PatternList list;
        try { list = JsonSerializer.Deserialize<PatternList>(text[start..(end + 1)], Json) ?? new PatternList(); }
        catch (JsonException) { throw new AppException(502, "The AI returned malformed patterns. Try the upload again or split the file."); }
        foreach (var p in list.Patterns)
            p.Refs = p.RefsJson.ValueKind == JsonValueKind.Array
                ? p.RefsJson.EnumerateArray().Select(r => r.ToString().Trim()).Where(r => r.Length > 0).Distinct().ToList()
                : [];
        return list;
    }

    private static string? Cut(string? s, int max) => s is null ? null : s.Length <= max ? s : s[..max];
}