using System.Security.Claims;
using System.Text.Json;
using System.Text.Json.Nodes;
using CareFix.Api.Ai;
using CareFix.Api.Hospitals;
using CareFix.Api.Infrastructure;
using CareFix.Api.Knowledge;
using CareFix.Api.Options;
using CareFix.Api.Security;
using CareFix.Core;
using CareFix.Api.Tickets;
using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;

namespace CareFix.Api.Fixes;

public sealed class FixRow
{
    public int FixId { get; set; }
    public int TicketId { get; set; }
    public string Summary { get; set; } = "";
    public string Risk { get; set; } = "";
    public string State { get; set; } = "";
    public bool LockOverrideNeeded { get; set; }
    public string? ConsentFileName { get; set; }
}

public sealed class FixStepRow
{
    public int StepNo { get; set; }
    public string TableName { get; set; } = "";
    public string PkColumn { get; set; } = "";
    public string PkValue { get; set; } = "";
    public string ColumnName { get; set; } = "";
    public string? OldValue { get; set; }
    public string? NewValue { get; set; }
    public string? Reason { get; set; }
}

public sealed class ApprovalRow
{
    public int ApproverId { get; set; }
    public string ApproverRole { get; set; } = "";
    public string Decision { get; set; } = "";
}

/// <summary>Proposal, approval, execution, verification and rollback of data fixes.</summary>
public sealed class FixService(
    ControlDb db, IHospitalExecutor exec, KnowledgeBase kb, SqlGuard guard, TicketService tickets,
    AiQueue queue, AuditService audit, IOptions<CareFixOptions> options)
{
    // ------------------------------------------------------------------ propose (called by the AI)
    public async Task<string> ProposeAsync(TicketRow t, JsonObject input, CancellationToken ct)
    {
        var s = options.Value.Safety;
        var summary = (input["summary"]?.ToString() ?? "").Trim();
        var evidence = input["evidence"]?.ToString();
        if (summary.Length < 10) throw AppException.BadRequest("summary is required: say in one or two sentences what is wrong and what the fix does.");
        if (input["steps"] is not JsonArray stepsNode || stepsNode.Count == 0) throw AppException.BadRequest("steps is required and must not be empty.");
        if (stepsNode.Count > s.MaxStepsPerFix) throw AppException.BadRequest($"A fix can change at most {s.MaxStepsPerFix} values. Split the work or escalate to the HIS team.");

        var known = await kb.KnownTablesAsync(t.HospitalId, ct);
        var steps = new List<FixStepRow>();
        var reasons = new List<string>();
        var risk = "Low";
        var n = 0;

        foreach (var node in stepsNode)
        {
            n++;
            if (node is not JsonObject st) throw AppException.BadRequest($"Step {n} is not an object.");
            string Req(string k) => st[k]?.ToString() is { Length: > 0 } v ? v.Trim() : throw AppException.BadRequest($"Step {n}: {k} is required.");
            var table = Req("table"); var pkCol = Req("pk_column"); var pk = Req("pk_value"); var col = Req("column");
            var newVal = st["new_value"]?.ToString();
            var reason = st["reason"]?.ToString();

            if (!known.Contains(table)) throw AppException.BadRequest($"Step {n}: table {table} is not in this hospital's schema.");
            var colInfo = await kb.ColumnInfoAsync(t.HospitalId, table, col, ct)
                          ?? throw AppException.BadRequest($"Step {n}: column {table}.{col} does not exist.");
            if (await kb.ColumnInfoAsync(t.HospitalId, table, pkCol, ct) is null)
                throw AppException.BadRequest($"Step {n}: key column {table}.{pkCol} does not exist.");
            if (colInfo.IsPk) throw AppException.BadRequest($"Step {n}: key columns cannot be changed.");
            if (colInfo.Editable == false)
                throw AppException.BadRequest($"Step {n}: {table}.{col} is marked not editable in the Caresoft data dictionary. Use another approach or recommend escalation.");
            if (newVal is null && !colInfo.IsNullable) throw AppException.BadRequest($"Step {n}: {table}.{col} does not allow NULL.");

            var read = await exec.GetValueAsync(t.HospitalId, table, pkCol, pk, col, ct);
            if (read.Rows != 1) throw AppException.BadRequest($"Step {n}: {pkCol} = '{pk}' matches {read.Rows} rows in {table}. Use a key that identifies exactly one row.");
            if (read.Value == newVal) throw AppException.BadRequest($"Step {n}: the new value is the same as the current value ('{read.Value}').");

            var stepRisk = colInfo.RiskLevel ?? "Medium";
            if (colInfo.Meaning is null) reasons.Add($"{table}.{col} is not described in the data dictionary");
            if (RiskRules.IsAmountColumn(col)) { stepRisk = "High"; reasons.Add($"{table}.{col} affects amounts or quantities"); }
            risk = RiskRules.Max(risk, stepRisk);
            steps.Add(new FixStepRow { StepNo = n, TableName = table, PkColumn = pkCol, PkValue = pk, ColumnName = col, OldValue = read.Value, NewValue = newVal, Reason = reason });
        }

        if (steps.Count >= s.HighRiskStepThreshold) { risk = "High"; reasons.Add($"{steps.Count} values change in one fix"); }

        var lockOverride = false;
        foreach (var g in steps.GroupBy(x => (x.TableName.ToUpperInvariant(), x.PkValue)))
        {
            var first = g.First();
            foreach (var rule in await kb.LockRulesAsync(first.TableName, ct))
            {
                var check = guard.ValidateSelect(rule.LockCheckSql, known);
                if (!check.Ok) { reasons.Add($"lock rule {rule.RuleId} could not be checked ({check.Reason})"); risk = "High"; continue; }
                if (await exec.AnyRowsAsync(t.HospitalId, rule.LockCheckSql, first.PkValue, ct))
                {
                    lockOverride = true; risk = "High";
                    reasons.Add($"LOCKED: {rule.LockMessage ?? rule.RuleText} ({first.TableName} {first.PkValue})");
                }
            }
        }

        await using var c = await db.OpenAsync(ct);
        await using var tx = (SqlTransaction)await c.BeginTransactionAsync(ct);
        await c.ExecuteAsync("UPDATE CF_FIX SET State='Superseded' WHERE TicketId=@TicketId AND State='Proposed'", new { t.TicketId }, tx);
        var fixId = await c.ExecuteScalarAsync<int>("""
            INSERT CF_FIX (TicketId, Summary, Evidence, Risk, RiskReasons, RowsExpected, LockOverrideNeeded)
            OUTPUT INSERTED.FixId VALUES (@TicketId, @summary, @evidence, @risk, @reasons, @rows, @lockOverride)
            """, new { t.TicketId, summary, evidence, risk, reasons = reasons.Count == 0 ? null : string.Join("; ", reasons.Distinct()), rows = steps.Count, lockOverride }, tx);
        foreach (var st in steps)
            await c.ExecuteAsync("""
                INSERT CF_FIX_STEP (FixId, StepNo, TableName, PkColumn, PkValue, ColumnName, OldValue, NewValue, Reason)
                VALUES (@fixId, @StepNo, @TableName, @PkColumn, @PkValue, @ColumnName, @OldValue, @NewValue, @Reason)
                """, new { fixId, st.StepNo, st.TableName, st.PkColumn, st.PkValue, st.ColumnName, st.OldValue, st.NewValue, st.Reason }, tx);
        await tx.CommitAsync(ct);

        await tickets.SetStateAsync(t.TicketId, "FixProposed", risk, ct);
        await tickets.AddMessageAsync(t.TicketId, "System", $"Fix #{fixId} proposed ({steps.Count} value(s), {risk} risk). Needs: {ApprovalRules.RuleText(risk)}.", null, ct);
        await audit.LogAsync(null, t.TicketId, "FixProposed", new { fixId, risk, steps = steps.Count, lockOverride }, ct);

        return $"Fix #{fixId} saved with {risk} risk{(lockOverride ? " (lock override needed)" : "")}. Approval needed: {ApprovalRules.RuleText(risk)}. " +
               "Do not propose it again. Tell the engineer in 2-3 short sentences what will change and why, then stop.";
    }

    // ------------------------------------------------------------------ approvals
    private async Task<(FixRow fix, TicketRow ticket)> LoadAsync(ClaimsPrincipal user, int fixId, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var fix = await c.QuerySingleOrDefaultAsync<FixRow>(
            "SELECT FixId, TicketId, Summary, Risk, State, LockOverrideNeeded, ConsentFileName FROM CF_FIX WHERE FixId=@fixId", new { fixId })
            ?? throw AppException.NotFound("Fix not found.");
        var ticket = await tickets.GetForUserAsync(user, fix.TicketId, ct);
        return (fix, ticket);
    }

    public async Task<object> ApproveAsync(ClaimsPrincipal user, int fixId, string? reason, CancellationToken ct)
    {
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (fix.State != "Proposed") throw AppException.Conflict($"This fix is {fix.State}, not waiting for approval.");
        if (ticket.RaisedBy == user.UserId()) throw AppException.Forbidden("You raised this ticket, so another person must approve its fix.");
        var role = user.Role();
        if (!ApprovalRules.CanApprove(role, fix.Risk)) throw AppException.Forbidden($"{fix.Risk} risk fixes need: {ApprovalRules.RuleText(fix.Risk)}.");

        await using var c = await db.OpenAsync(ct);
        var approvals = (await c.QueryAsync<ApprovalRow>("SELECT ApproverId, ApproverRole, Decision FROM CF_APPROVAL WHERE FixId=@fixId", new { fixId })).ToList();
        if (approvals.Any(a => a.ApproverId == user.UserId())) throw AppException.Conflict("You have already approved this fix.");
        await c.ExecuteAsync("INSERT CF_APPROVAL (FixId, ApproverId, ApproverRole, Decision, Reason) VALUES (@fixId, @uid, @role, 'Approved', @reason)",
            new { fixId, uid = user.UserId(), role, reason });
        approvals.Add(new ApprovalRow { ApproverId = user.UserId(), ApproverRole = role, Decision = "Approved" });
        await audit.LogAsync(user.UserId(), ticket.TicketId, "FixApproved", new { fixId, role, reason }, ct);

        return await EvaluateAsync(fix, ticket, approvals, user.UserId(), ct);
    }

    private async Task<object> EvaluateAsync(FixRow fix, TicketRow ticket, List<ApprovalRow> approvals, int userId, CancellationToken ct)
    {
        var hasConsent = fix.ConsentFileName is not null;
        if (ApprovalRules.IsSatisfied(fix.Risk, approvals, hasConsent))
        {
            await using var c = await db.OpenAsync(ct);
            await c.ExecuteAsync("UPDATE CF_FIX SET State='Approved' WHERE FixId=@FixId AND State='Proposed'", new { fix.FixId });
            await tickets.SetStateAsync(ticket.TicketId, "Approved", null, ct);
            await tickets.AddMessageAsync(ticket.TicketId, "System", $"Fix #{fix.FixId} is fully approved and ready to run.", userId, ct);
            return new { state = "Approved" };
        }
        var waiting = fix.Risk == "High" && !hasConsent ? "Waiting for the hospital consent file." : "Waiting for more approvals.";
        await tickets.AddMessageAsync(ticket.TicketId, "System", $"Approval recorded on fix #{fix.FixId}. {waiting}", userId, ct);
        return new { state = "Proposed", waiting };
    }

    public async Task RejectAsync(ClaimsPrincipal user, int fixId, string? reason, CancellationToken ct)
    {
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (fix.State is not ("Proposed" or "Approved")) throw AppException.Conflict($"This fix is {fix.State}.");
        if (user.Role() is not (Roles.Lead or Roles.ProductOwner or Roles.Head)) throw AppException.Forbidden("Only a Lead, Product Owner or Head can reject a fix.");
        if (string.IsNullOrWhiteSpace(reason)) throw AppException.BadRequest("Give a reason so the engineer and AI know what to change.");

        await using var c = await db.OpenAsync(ct);
        await c.ExecuteAsync("""
            INSERT CF_APPROVAL (FixId, ApproverId, ApproverRole, Decision, Reason) VALUES (@fixId, @uid, @role, 'Rejected', @reason);
            UPDATE CF_FIX SET State='Rejected' WHERE FixId=@fixId;
            """, new { fixId, uid = user.UserId(), role = user.Role(), reason });
        await tickets.SetStateAsync(ticket.TicketId, "Diagnosing", null, ct);
        await tickets.AddMessageAsync(ticket.TicketId, "System", $"Fix #{fixId} rejected: {reason}", user.UserId(), ct);
        await audit.LogAsync(user.UserId(), ticket.TicketId, "FixRejected", new { fixId, reason }, ct);
        await tickets.SetAiBusyAsync(ticket.TicketId, true, ct);
        await queue.EnqueueAsync(new AiJob(ticket.TicketId, user.UserId(),
            $"Fix #{fixId} was rejected by {user.Role()} with this reason: {reason}. Re-check the data and propose a corrected fix if one is possible, or explain what a human should check."));
    }

    public async Task<object> UploadConsentAsync(ClaimsPrincipal user, int fixId, IFormFile file, CancellationToken ct)
    {
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (fix.State != "Proposed") throw AppException.Conflict("Consent can only be attached while the fix is waiting for approval.");
        if (file.Length == 0 || file.Length > options.Value.Safety.MaxConsentFileBytes)
            throw AppException.BadRequest($"The consent file must be between 1 byte and {options.Value.Safety.MaxConsentFileBytes / 1024 / 1024} MB.");
        var ext = Path.GetExtension(file.FileName).ToLowerInvariant();
        if (ext is not (".pdf" or ".png" or ".jpg" or ".jpeg" or ".eml" or ".msg"))
            throw AppException.BadRequest("Attach the consent as PDF, image or saved e-mail (.eml/.msg).");

        using var ms = new MemoryStream();
        await file.CopyToAsync(ms, ct);
        await using var c = await db.OpenAsync(ct);
        var p = new DynamicParameters(new { fixId, name = Path.GetFileName(file.FileName), uid = user.UserId() });
        p.Add("data", ms.ToArray(), System.Data.DbType.Binary);
        await c.ExecuteAsync("UPDATE CF_FIX SET ConsentFileName=@name, ConsentFile=@data, ConsentBy=@uid WHERE FixId=@fixId", p);
        fix.ConsentFileName = file.FileName;
        await audit.LogAsync(user.UserId(), ticket.TicketId, "ConsentUploaded", new { fixId, file = file.FileName, bytes = file.Length }, ct);

        var approvals = (await c.QueryAsync<ApprovalRow>("SELECT ApproverId, ApproverRole, Decision FROM CF_APPROVAL WHERE FixId=@fixId", new { fixId })).ToList();
        return await EvaluateAsync(fix, ticket, approvals, user.UserId(), ct);
    }

    public async Task<(string name, byte[] data)> GetConsentAsync(ClaimsPrincipal user, int fixId, CancellationToken ct)
    {
        await LoadAsync(user, fixId, ct);
        await using var c = await db.OpenAsync(ct);
        var r = await c.QuerySingleOrDefaultAsync<(string? Name, byte[]? Data)>("SELECT ConsentFileName, ConsentFile FROM CF_FIX WHERE FixId=@fixId", new { fixId });
        if (r.Data is null || r.Name is null) throw AppException.NotFound("No consent file on this fix.");
        return (r.Name, r.Data);
    }

    // ------------------------------------------------------------------ execute
    private async Task<List<FixStepRow>> StepsAsync(int fixId, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return (await c.QueryAsync<FixStepRow>(
            "SELECT StepNo, TableName, PkColumn, PkValue, ColumnName, OldValue, NewValue, Reason FROM CF_FIX_STEP WHERE FixId=@fixId ORDER BY StepNo",
            new { fixId })).ToList();
    }

    public async Task<object> ExecuteAsync(ClaimsPrincipal user, int fixId, CancellationToken ct)
    {
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (fix.State != "Approved") throw AppException.Conflict($"Only an approved fix can run. This fix is {fix.State}.");
        var role = user.Role();
        if (!(role is Roles.Lead or Roles.Head || (role == Roles.Engineer && ticket.RaisedBy == user.UserId())))
            throw AppException.Forbidden("Only the engineer who raised the ticket, a Lead or a Head can run the fix.");

        var steps = await StepsAsync(fixId, ct);
        foreach (var st in steps)
        {
            var now = await exec.GetValueAsync(ticket.HospitalId, st.TableName, st.PkColumn, st.PkValue, st.ColumnName, ct);
            if (now.Rows != 1 || now.Value != st.OldValue)
                throw AppException.Conflict($"The data changed after approval: {st.TableName}.{st.ColumnName} for {st.PkColumn}={st.PkValue} is now '{now.Value ?? "NULL"}' (expected '{st.OldValue ?? "NULL"}'). Ask the AI to re-diagnose.");
        }

        int executionId;
        await using (var c = await db.OpenAsync(ct))
        {
            executionId = await c.ExecuteScalarAsync<int>(
                "INSERT CF_EXECUTION (FixId, ExecutedBy, Status) OUTPUT INSERTED.ExecutionId VALUES (@fixId, @uid, 'Running')",
                new { fixId, uid = user.UserId() });
            foreach (var g in steps.GroupBy(x => (x.TableName.ToUpperInvariant(), x.PkColumn.ToUpperInvariant(), x.PkValue)))
            {
                var st = g.First();
                var row = await exec.GetRowAsync(ticket.HospitalId, st.TableName, st.PkColumn, st.PkValue, ct);
                await c.ExecuteAsync("INSERT CF_ROW_SNAPSHOT (ExecutionId, TableName, PkColumn, PkValue, RowJson) VALUES (@executionId, @t, @k, @v, @j)",
                    new { executionId, t = st.TableName, k = st.PkColumn, v = st.PkValue, j = JsonSerializer.Serialize(row) });
            }
        }

        int rows;
        try
        {
            rows = await exec.ApplyStepsAsync(ticket.HospitalId,
                steps.Select(x => new StepChange(x.TableName, x.PkColumn, x.PkValue, x.ColumnName, x.OldValue, x.NewValue)).ToList(),
                ticket.TicketNo, ct);
        }
        catch (AppException ex)
        {
            await using var c = await db.OpenAsync(CancellationToken.None);
            await c.ExecuteAsync("""
                UPDATE CF_EXECUTION SET Status='Failed', Error=@e WHERE ExecutionId=@executionId;
                UPDATE CF_FIX SET State='Failed' WHERE FixId=@fixId;
                """, new { executionId, fixId, e = ex.Message });
            await tickets.SetStateAsync(ticket.TicketId, "Diagnosing", null, CancellationToken.None);
            await tickets.AddMessageAsync(ticket.TicketId, "System", $"Fix #{fixId} failed and nothing was changed: {ex.Message}", user.UserId(), CancellationToken.None);
            await audit.LogAsync(user.UserId(), ticket.TicketId, "FixFailed", new { fixId, error = ex.Message }, CancellationToken.None);
            throw;
        }

        await using (var c = await db.OpenAsync(CancellationToken.None))
        {
            await c.ExecuteAsync("""
                UPDATE CF_EXECUTION SET Status='Success', RowsAffected=@rows WHERE ExecutionId=@executionId;
                UPDATE CF_FIX SET State='Executed' WHERE FixId=@fixId;
                """, new { executionId, fixId, rows });
        }
        await tickets.SetStateAsync(ticket.TicketId, "Executed", null, CancellationToken.None);
        await tickets.AddMessageAsync(ticket.TicketId, "System", $"Fix #{fixId} ran successfully: {rows} value(s) changed. The AI is now verifying.", user.UserId(), CancellationToken.None);
        await audit.LogAsync(user.UserId(), ticket.TicketId, "FixExecuted", new { fixId, executionId, rows }, CancellationToken.None);

        await tickets.SetAiBusyAsync(ticket.TicketId, true, CancellationToken.None);
        await queue.EnqueueAsync(new AiJob(ticket.TicketId, user.UserId(),
            $"Fix #{fixId} was executed ({rows} value(s) changed). Verify it: run a SELECT to confirm the original issue is resolved and linked records are consistent, then call verify_fix."));
        return new { executionId, rows };
    }

    public async Task<string> RecordVerificationAsync(int ticketId, JsonObject input, CancellationToken ct)
    {
        var fixId = JsonArg.Int(input["fix_id"]) ?? throw AppException.BadRequest("fix_id is required and must be a number.");
        var resolved = JsonArg.Bool(input["resolved"]) ?? throw AppException.BadRequest("resolved is required and must be true or false.");
        var note = input["note"]?.ToString() ?? "";

        await using var c = await db.OpenAsync(ct);
        var n = await c.ExecuteAsync("""
            UPDATE e SET VerifiedAt = SYSUTCDATETIME(), VerifiedResolved = @resolved, VerifyNote = @note
            FROM CF_EXECUTION e JOIN CF_FIX f ON f.FixId = e.FixId
            WHERE e.FixId = @fixId AND f.TicketId = @ticketId AND f.State = 'Executed' AND e.Status = 'Success'
            """, new { fixId, ticketId, resolved, note });
        if (n == 0) throw AppException.BadRequest($"Fix #{fixId} is not an executed fix on this ticket.");
        if (resolved) await tickets.SetStateAsync(ticketId, "Verified", null, ct);
        await tickets.AddMessageAsync(ticketId, "System", resolved ? $"Verified: fix #{fixId} resolved the issue." : $"Verification: fix #{fixId} did not fully resolve the issue.", null, ct);
        await audit.LogAsync(null, ticketId, "FixVerified", new { fixId, resolved, note }, ct);
        return resolved ? "Verification recorded. Tell the engineer the ticket can be closed." : "Verification recorded as not resolved. Explain what remains to the engineer.";
    }

    // ------------------------------------------------------------------ rollback
    public async Task<object> RollbackAsync(ClaimsPrincipal user, int fixId, string? reason, CancellationToken ct)
    {
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (user.Role() != Roles.Head) throw AppException.Forbidden("Only the Support Head can roll back a fix.");
        if (fix.State != "Executed") throw AppException.Conflict($"Only an executed fix can be rolled back. This fix is {fix.State}.");
        if (string.IsNullOrWhiteSpace(reason)) throw AppException.BadRequest("Give a reason for the rollback.");

        var steps = await StepsAsync(fixId, ct);
        var reverse = steps.OrderByDescending(x => x.StepNo)
            .Select(x => new StepChange(x.TableName, x.PkColumn, x.PkValue, x.ColumnName, ExpectedOld: x.NewValue, NewValue: x.OldValue)).ToList();
        int rows;
        try { rows = await exec.ApplyStepsAsync(ticket.HospitalId, reverse, ticket.TicketNo + "-RB", ct); }
        catch (AppException ex)
        {
            throw AppException.Conflict($"Rollback stopped: {ex.Message} Someone changed these values after the fix. The pre-change rows are saved in the snapshot for manual restore.");
        }

        await using var c = await db.OpenAsync(CancellationToken.None);
        await c.ExecuteAsync("""
            UPDATE CF_EXECUTION SET RolledBackAt=SYSUTCDATETIME(), RolledBackBy=@uid, RollbackReason=@reason WHERE FixId=@fixId AND Status='Success';
            UPDATE CF_FIX SET State='RolledBack' WHERE FixId=@fixId;
            """, new { fixId, uid = user.UserId(), reason });
        await tickets.SetStateAsync(ticket.TicketId, "RolledBack", null, CancellationToken.None);
        await tickets.AddMessageAsync(ticket.TicketId, "System", $"Fix #{fixId} rolled back ({rows} value(s) restored): {reason}", user.UserId(), CancellationToken.None);
        await audit.LogAsync(user.UserId(), ticket.TicketId, "FixRolledBack", new { fixId, rows, reason }, CancellationToken.None);
        return new { rows };
    }

    // ------------------------------------------------------------------ approvals queue
    public async Task<IEnumerable<dynamic>> PendingForAsync(ClaimsPrincipal user, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QueryAsync("""
            SELECT f.FixId, f.Summary, f.Risk, f.State, f.RiskReasons, f.RowsExpected, f.LockOverrideNeeded, f.ConsentFileName, f.ProposedAt,
                   t.TicketId, t.TicketNo, t.Title, h.Name AS HospitalName, u.FullName AS RaisedByName, t.RaisedBy,
                   (SELECT COUNT(*) FROM CF_APPROVAL a WHERE a.FixId = f.FixId AND a.Decision = 'Approved') AS ApprovalCount,
                   CAST(CASE WHEN EXISTS (SELECT 1 FROM CF_APPROVAL a WHERE a.FixId = f.FixId AND a.ApproverId = @uid) THEN 1 ELSE 0 END AS BIT) AS ApprovedByMe
            FROM CF_FIX f JOIN CF_TICKET t ON t.TicketId = f.TicketId JOIN CF_HOSPITAL h ON h.HospitalId = t.HospitalId
            JOIN CF_USER u ON u.UserId = t.RaisedBy
            WHERE f.State IN ('Proposed', 'Approved')
              AND (@all = 1 OR t.HospitalId IN (SELECT HospitalId FROM CF_USER_HOSPITAL WHERE UserId = @uid))
            ORDER BY CASE f.Risk WHEN 'High' THEN 0 WHEN 'Medium' THEN 1 ELSE 2 END, f.ProposedAt
            """, new { uid = user.UserId(), all = user.SeesAllHospitals() });
    }

    // ------------------------------------------------------------------ playbook from fix
    public async Task<int> PromoteToPlaybookAsync(ClaimsPrincipal user, int fixId, string title, string? issueType, string keywords, 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 add playbooks.");
        var (fix, ticket) = await LoadAsync(user, fixId, ct);
        if (fix.State != "Executed") throw AppException.Conflict("Only a successfully executed fix can become a playbook.");
        if (string.IsNullOrWhiteSpace(title) || string.IsNullOrWhiteSpace(keywords)) throw AppException.BadRequest("Title and keywords are required.");

        var steps = await StepsAsync(fixId, ct);
        var guidance = "Change pattern used in the original fix:\n" + string.Join("\n", steps
            .GroupBy(s => (s.TableName, s.ColumnName))
            .Select(g => $"- {g.Key.TableName}.{g.Key.ColumnName} (key {g.First().PkColumn}): e.g. '{g.First().OldValue ?? "NULL"}' -> '{g.First().NewValue ?? "NULL"}'. {g.First().Reason}"));

        await using var c = await db.OpenAsync(ct);
        var diagnosis = await c.ExecuteScalarAsync<string?>(
            "SELECT TOP 1 SqlText FROM CF_QUERY_LOG WHERE TicketId=@TicketId AND Blocked=0 AND RowsReturned > 0 ORDER BY QueryId", new { ticket.TicketId });
        var module = await c.ExecuteScalarAsync<string?>("SELECT Module FROM CF_KB_TABLE WHERE TableName=@t", new { t = steps.First().TableName });
        var id = await c.ExecuteScalarAsync<int>("""
            INSERT CF_PLAYBOOK (Title, IssueType, Module, Keywords, Description, DiagnosisSql, FixGuidance, Risk, SourceFixId, CreatedBy, SuccessCount)
            OUTPUT INSERTED.PlaybookId VALUES (@title, @issueType, @module, @keywords, @desc, @diagnosis, @guidance, @Risk, @fixId, @uid, 1)
            """, new { title, issueType, module, keywords, desc = fix.Summary, diagnosis, guidance, fix.Risk, fixId, uid = user.UserId() });
        await audit.LogAsync(user.UserId(), ticket.TicketId, "PlaybookCreated", new { id, fixId, title }, ct);
        return id;
    }
}