using System.Security.Claims;
using CareFix.Api.Ai;
using CareFix.Api.Infrastructure;
using CareFix.Api.Integration;
using Microsoft.Data.SqlClient;
using CareFix.Api.Security;
using Dapper;

namespace CareFix.Api.Tickets;

public sealed class TicketRow
{
    public int TicketId { get; set; }
    public string TicketNo { get; set; } = "";
    public int HospitalId { get; set; }
    public string HospitalName { get; set; } = "";
    public int RaisedBy { get; set; }
    public string RaisedByName { get; set; } = "";
    public string Title { get; set; } = "";
    public string IssueText { get; set; } = "";
    public string State { get; set; } = "";
    public string? Risk { get; set; }
    public bool AiBusy { get; set; }
    public string? ExternalRef { get; set; }
    public string? CloseNote { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public DateTime? ClosedAt { get; set; }
}


// Typed rows for the ticket detail payload. Dapper maps by column name; the API serialises them camelCase.
public sealed class TicketMessageDto
{
    public long MessageId { get; set; }
    public string Sender { get; set; } = "";
    public string Body { get; set; } = "";
    public DateTime CreatedAt { get; set; }
    public string? Author { get; set; }
}

public sealed class QueryLogDto
{
    public long QueryId { get; set; }
    public string SqlText { get; set; } = "";
    public string? Purpose { get; set; }
    public int? RowsReturned { get; set; }
    public bool Truncated { get; set; }
    public int? DurationMs { get; set; }
    public bool Blocked { get; set; }
    public string? BlockReason { get; set; }
    public string? ResultJson { get; set; }
    public DateTime CreatedAt { get; set; }
}

public sealed class FixDto
{
    public int FixId { get; set; }
    public string Summary { get; set; } = "";
    public string? Evidence { get; set; }
    public string Risk { get; set; } = "";
    public string? RiskReasons { get; set; }
    public int RowsExpected { get; set; }
    public string State { get; set; } = "";
    public bool LockOverrideNeeded { get; set; }
    public string? ConsentFileName { get; set; }
    public DateTime ProposedAt { get; set; }
}

public sealed class FixStepDto
{
    public int FixId { get; set; }
    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 ApprovalDto
{
    public int FixId { get; set; }
    public string ApproverRole { get; set; } = "";
    public string Decision { get; set; } = "";
    public string? Reason { get; set; }
    public DateTime DecidedAt { get; set; }
    public string Approver { get; set; } = "";
}

public sealed class ExecutionDto
{
    public int FixId { get; set; }
    public int ExecutionId { get; set; }
    public DateTime ExecutedAt { get; set; }
    public string Status { get; set; } = "";
    public int? RowsAffected { get; set; }
    public string? Error { get; set; }
    public DateTime? VerifiedAt { get; set; }
    public bool? VerifiedResolved { get; set; }
    public string? VerifyNote { get; set; }
    public DateTime? RolledBackAt { get; set; }
    public string? RollbackReason { get; set; }
    public string ExecutedByName { get; set; } = "";
    public string? RolledBackByName { get; set; }
}

public sealed record FixBundleDto(FixDto Fix, IEnumerable<FixStepDto> Steps, IEnumerable<ApprovalDto> Approvals, IEnumerable<ExecutionDto> Executions);

public sealed record NewTicketInput(int HospitalId, string Title, string IssueText, string? ExternalRef);

public sealed class TicketService(ControlDb db, AiQueue queue, PiiMasker masker, AuditService audit, WebhookOutbox outbox)
{
    private const string SelectTicket = """
        SELECT t.TicketId, t.TicketNo, t.HospitalId, h.Name AS HospitalName, t.RaisedBy, u.FullName AS RaisedByName, t.Title, t.IssueText,
               t.State, t.Risk, t.AiBusy, t.ExternalRef, t.CloseNote, t.CreatedAt, t.UpdatedAt, t.ClosedAt
        FROM CF_TICKET t JOIN CF_HOSPITAL h ON h.HospitalId = t.HospitalId JOIN CF_USER u ON u.UserId = t.RaisedBy
        """;

    public async Task EnsureHospitalAccessAsync(ClaimsPrincipal user, int hospitalId, CancellationToken ct)
    {
        if (user.SeesAllHospitals()) return;
        await using var c = await db.OpenAsync(ct);
        var ok = await c.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM CF_USER_HOSPITAL WHERE UserId = @u AND HospitalId = @hospitalId",
            new { u = user.UserId(), hospitalId }) > 0;
        if (!ok) throw AppException.Forbidden("You are not assigned to this hospital.");
    }

    public async Task<TicketRow> GetRowAsync(int ticketId, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        return await c.QuerySingleOrDefaultAsync<TicketRow>(SelectTicket + " WHERE t.TicketId = @ticketId", new { ticketId })
               ?? throw AppException.NotFound("Ticket not found.");
    }

    public async Task<TicketRow> GetForUserAsync(ClaimsPrincipal user, int ticketId, CancellationToken ct)
    {
        var t = await GetRowAsync(ticketId, ct);
        await EnsureHospitalAccessAsync(user, t.HospitalId, ct);
        return t;
    }

    public async Task<IEnumerable<TicketRow>> ListAsync(ClaimsPrincipal user, string? state, bool mine, string? q, CancellationToken ct)
    {
        await using var c = await db.OpenAsync(ct);
        var sql = SelectTicket + """
             WHERE (@state IS NULL OR t.State = @state OR (@state = 'Open' AND t.State <> 'Closed'))
               AND (@mine = 0 OR t.RaisedBy = @uid)
               AND (@all = 1 OR t.HospitalId IN (SELECT HospitalId FROM CF_USER_HOSPITAL WHERE UserId = @uid))
               AND (@q IS NULL OR t.TicketNo LIKE @q OR t.Title LIKE @q OR h.Name LIKE @q OR t.ExternalRef LIKE @q)
             ORDER BY t.UpdatedAt DESC
             OFFSET 0 ROWS FETCH NEXT 300 ROWS ONLY
            """;
        return await c.QueryAsync<TicketRow>(sql, new
        {
            state = string.IsNullOrWhiteSpace(state) ? null : state, mine, uid = user.UserId(), all = user.SeesAllHospitals(),
            q = string.IsNullOrWhiteSpace(q) ? null : "%" + q.Trim() + "%",
        });
    }

    public async Task<int> CreateAsync(ClaimsPrincipal user, NewTicketInput i, CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(i.IssueText) || i.IssueText.Trim().Length < 15)
            throw AppException.BadRequest("Describe the problem in a sentence or two, with identifiers such as UHID, bill no or IPD no.");
        await EnsureHospitalAccessAsync(user, i.HospitalId, ct);
        var issue = masker.MaskText(i.IssueText.Trim());
        var title = string.IsNullOrWhiteSpace(i.Title) ? (issue.Length > 80 ? issue[..80] + "…" : issue) : i.Title.Trim();

        await using var c = await db.OpenAsync(ct);
        var externalRef = string.IsNullOrWhiteSpace(i.ExternalRef) ? null : i.ExternalRef.Trim();
        int id;
        try
        {
            id = await c.ExecuteScalarAsync<int>("""
                INSERT CF_TICKET (HospitalId, RaisedBy, Title, IssueText, ExternalRef, AiBusy)
                OUTPUT INSERTED.TicketId VALUES (@HospitalId, @uid, @title, @issue, @externalRef, 1)
                """, new { i.HospitalId, uid = user.UserId(), title, issue, externalRef });
        }
        catch (SqlException ex) when (ex.Number is 2601 or 2627)
        {
            var no = await c.ExecuteScalarAsync<string>("SELECT TicketNo FROM CF_TICKET WHERE HospitalId=@HospitalId AND ExternalRef=@externalRef", new { i.HospitalId, externalRef });
            throw AppException.Conflict($"Ticket {no} already exists for helpdesk ref {externalRef}.");
        }
        await AddMessageAsync(id, "Engineer", issue, user.UserId(), ct);
        await audit.LogAsync(user.UserId(), id, "TicketCreated", new { i.HospitalId, title }, ct);
        await queue.EnqueueAsync(new AiJob(id, user.UserId(), issue));
        return id;
    }

    public async Task PostMessageAsync(ClaimsPrincipal user, int ticketId, string text, CancellationToken ct)
    {
        var t = await GetForUserAsync(user, ticketId, ct);
        if (t.State == "Closed") throw AppException.Conflict("This ticket is closed.");
        if (string.IsNullOrWhiteSpace(text)) throw AppException.BadRequest("Type a message.");
        var body = masker.MaskText(text.Trim());
        await AddMessageAsync(ticketId, "Engineer", body, user.UserId(), ct);
        await SetAiBusyAsync(ticketId, true, ct);
        await queue.EnqueueAsync(new AiJob(ticketId, user.UserId(), body));
    }

    public async Task CloseAsync(ClaimsPrincipal user, int ticketId, string? note, CancellationToken ct)
    {
        var t = await GetForUserAsync(user, ticketId, ct);
        if (t.State is "FixProposed" or "Approved")
            throw AppException.Conflict("A fix is waiting for approval or execution. Reject it or finish it before closing.");
        await using var c = await db.OpenAsync(ct);
        await c.ExecuteAsync("UPDATE CF_TICKET SET State='Closed', CloseNote=@note, ClosedAt=SYSUTCDATETIME(), UpdatedAt=SYSUTCDATETIME() WHERE TicketId=@ticketId",
            new { ticketId, note });
        await AddMessageAsync(ticketId, "System", "Ticket closed" + (string.IsNullOrWhiteSpace(note) ? "." : ": " + note), user.UserId(), ct);
        await outbox.EnqueueAsync(ticketId, ct);
        await audit.LogAsync(user.UserId(), ticketId, "TicketClosed", new { note }, ct);
    }

    public async Task<object> DetailAsync(ClaimsPrincipal user, int ticketId, CancellationToken ct)
    {
        var t = await GetForUserAsync(user, ticketId, ct);
        await using var c = await db.OpenAsync(ct);
        using var m = await c.QueryMultipleAsync("""
            SELECT m.MessageId, m.Sender, m.Body, m.CreatedAt, u.FullName AS Author
              FROM CF_TICKET_MESSAGE m LEFT JOIN CF_USER u ON u.UserId = m.UserId
              WHERE m.TicketId = @ticketId ORDER BY m.MessageId;
            SELECT QueryId, SqlText, Purpose, RowsReturned, Truncated, DurationMs, Blocked, BlockReason, ResultJson, CreatedAt
              FROM CF_QUERY_LOG WHERE TicketId = @ticketId ORDER BY QueryId;
            SELECT FixId, Summary, Evidence, Risk, RiskReasons, RowsExpected, State, LockOverrideNeeded, ConsentFileName, ProposedAt
              FROM CF_FIX WHERE TicketId = @ticketId ORDER BY FixId;
            SELECT s.FixId, s.StepNo, s.TableName, s.PkColumn, s.PkValue, s.ColumnName, s.OldValue, s.NewValue, s.Reason
              FROM CF_FIX_STEP s JOIN CF_FIX f ON f.FixId = s.FixId WHERE f.TicketId = @ticketId ORDER BY s.FixId, s.StepNo;
            SELECT a.FixId, a.ApproverRole, a.Decision, a.Reason, a.DecidedAt, u.FullName AS Approver
              FROM CF_APPROVAL a JOIN CF_USER u ON u.UserId = a.ApproverId JOIN CF_FIX f ON f.FixId = a.FixId
              WHERE f.TicketId = @ticketId ORDER BY a.ApprovalId;
            SELECT e.FixId, e.ExecutionId, e.ExecutedAt, e.Status, e.RowsAffected, e.Error, e.VerifiedAt, e.VerifiedResolved, e.VerifyNote,
                   e.RolledBackAt, e.RollbackReason, u.FullName AS ExecutedByName, rb.FullName AS RolledBackByName
              FROM CF_EXECUTION e JOIN CF_USER u ON u.UserId = e.ExecutedBy LEFT JOIN CF_USER rb ON rb.UserId = e.RolledBackBy
              JOIN CF_FIX f ON f.FixId = e.FixId WHERE f.TicketId = @ticketId ORDER BY e.ExecutionId;
            """, new { ticketId });
        var messages = (await m.ReadAsync<TicketMessageDto>()).ToList();
        var queries = (await m.ReadAsync<QueryLogDto>()).ToList();
        var fixes = (await m.ReadAsync<FixDto>()).ToList();
        var steps = (await m.ReadAsync<FixStepDto>()).ToList();
        var approvals = (await m.ReadAsync<ApprovalDto>()).ToList();
        var executions = (await m.ReadAsync<ExecutionDto>()).ToList();

        return new
        {
            ticket = t,
            messages,
            queries,
            fixes = fixes.Select(f => new FixBundleDto(
                f,
                steps.Where(s => s.FixId == f.FixId),
                approvals.Where(a => a.FixId == f.FixId),
                executions.Where(e => e.FixId == f.FixId))),
        };
    }

    public async Task AddMessageAsync(int ticketId, string sender, string body, int? userId = null, CancellationToken ct = default)
    {
        await using var c = await db.OpenAsync(ct);
        await c.ExecuteAsync("""
            INSERT CF_TICKET_MESSAGE (TicketId, Sender, UserId, Body) VALUES (@ticketId, @sender, @userId, @body);
            UPDATE CF_TICKET SET UpdatedAt = SYSUTCDATETIME() WHERE TicketId = @ticketId;
            """, new { ticketId, sender, userId, body });
    }

    public async Task SetStateAsync(int ticketId, string state, string? risk = null, CancellationToken ct = default)
    {
        await using var c = await db.OpenAsync(ct);
        var changed = await c.ExecuteAsync("""
            UPDATE CF_TICKET SET State=@state, Risk=ISNULL(@risk, Risk), UpdatedAt=SYSUTCDATETIME()
            WHERE TicketId=@ticketId AND (State <> @state OR ISNULL(Risk,'') <> ISNULL(ISNULL(@risk, Risk),''))
            """, new { ticketId, state, risk });
        if (changed > 0) await outbox.EnqueueAsync(ticketId, ct);
    }

    public async Task SetAiBusyAsync(int ticketId, bool busy, CancellationToken ct = default)
    {
        await using var c = await db.OpenAsync(ct);
        await c.ExecuteAsync("UPDATE CF_TICKET SET AiBusy=@busy WHERE TicketId=@ticketId", new { ticketId, busy });
    }
}