using SurrealDb.Net;
using TarninaHub.Application.Abstractions;
using TarninaHub.Application.Dtos;

namespace TarninaHub.Infrastructure.Persistence;

public sealed class TestFeedbackService : ITestFeedbackService
{
    private const string SelectFields =
        "type::string(id) AS id, type::string(innovation) AS innovation, type::string(author) AS author, " +
        "rating, comment, improvements, created_at";

    private readonly ISurrealDbClient _client;
    private readonly IInnovationService _innovations;

    public TestFeedbackService(ISurrealDbClient client, IInnovationService innovations)
    {
        _client = client;
        _innovations = innovations;
    }

    public async Task<TestFeedbackResponse?> CreateAsync(
        string innovationId,
        string authorId,
        CreateTestFeedbackRequest request,
        CancellationToken cancellationToken)
    {
        var innovation = await _innovations.GetAsync(innovationId, cancellationToken);

        if (innovation is null)
        {
            return null;
        }

        var feedbackId = Guid.NewGuid().ToString("N");
        var (innovationTable, innovationIdPart) = SurrealRecordId.Split(innovationId);
        var (authorTable, authorIdPart) = SurrealRecordId.Split(authorId);

        var response = await _client.RawQuery(
            "CREATE type::record('test_feedback', $id) SET " +
            "innovation = type::record($innovationTable, $innovationId), " +
            "author = type::record($authorTable, $authorId), rating = $rating, " +
            "comment = $comment, improvements = $improvements; " +
            $"SELECT {SelectFields} FROM type::record('test_feedback', $id) LIMIT 1;",
            new Dictionary<string, object?>
            {
                ["id"] = feedbackId,
                ["innovationTable"] = innovationTable,
                ["innovationId"] = innovationIdPart,
                ["authorTable"] = authorTable,
                ["authorId"] = authorIdPart,
                ["rating"] = request.Rating,
                ["comment"] = request.Comment ?? string.Empty,
                ["improvements"] = request.Improvements ?? string.Empty,
            },
            cancellationToken);
        response.EnsureAllOks();

        var rows = response.GetValue<List<TestFeedbackRow>>(1) ?? [];

        return rows.Count > 0 ? Map(rows[0]) : null;
    }

    public async Task<IReadOnlyList<TestFeedbackResponse>> ListForInnovationAsync(
        string innovationId,
        CancellationToken cancellationToken)
    {
        var (table, id) = SurrealRecordId.Split(innovationId);

        var response = await _client.RawQuery(
            $"SELECT {SelectFields} FROM test_feedback WHERE innovation = type::record($table, $id) " +
            "ORDER BY created_at DESC;",
            new Dictionary<string, object?> { ["table"] = table, ["id"] = id },
            cancellationToken);
        response.EnsureAllOks();

        var rows = response.GetValue<List<TestFeedbackRow>>(0) ?? [];

        return rows.Select(Map).ToList();
    }

    private static TestFeedbackResponse Map(TestFeedbackRow row) => new(
        row.Id,
        row.Innovation,
        row.Author,
        row.Rating,
        row.Comment,
        row.Improvements,
        row.CreatedAt);
}