using System;
using Npgsql;

namespace AiLogia.Web.Data;

public class TestDto
{
    public int Id { get; set; }
    public int? GroupId { get; set; }
    public string Code { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public string AccessMode { get; set; }
}

public class TestResultDto
{
    public int TestId { get; set; }
    public string TestTitle { get; set; } = default!;
    public string ResultText { get; set; } = default!;
    public string? Interpretation { get; set; }
    public DateTime CompletedAt { get; set; }
    public string TestAccessMode { get; set; } = default!;
    public string BotUsername { get; set; } = default!;
}

public interface ITestsRepository
{
    Task<TestDto?> GetTest(int testId);
    Task<IReadOnlyCollection<TestResultDto>> GetResults(long telegramUserId);
}

public class TestsRepository : ITestsRepository
{
    private readonly string _connectionString;

    public TestsRepository(IConfiguration config)
    {
        _connectionString = config["ConnectionStrings:BotDb"] ?? throw new ArgumentException();
    }

    public async Task<IReadOnlyCollection<TestResultDto>> GetResults(long userId)
    {
        var list = new List<TestResultDto>();

        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand(@"
            SELECT r.test_id, t.title, r.result_text, r.interpretation, r.completed_at, t.access_mode
            FROM tests_results r
            JOIN tests t ON t.id = r.test_id
            WHERE r.user_id = @uid and r.is_forgotten = FALSE 
            ORDER BY r.completed_at DESC", conn);

            cmd.Parameters.AddWithValue("uid", userId);

            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                list.Add(new TestResultDto
                {
                    TestId = reader.GetInt32(0),
                    TestTitle = reader.GetString(1),
                    ResultText = reader.GetString(2),
                    Interpretation = reader.IsDBNull(3) ? null : reader.GetString(3),
                    CompletedAt = reader.GetDateTime(4),
                    TestAccessMode = reader.GetString(5)
                });
            }
        }
        catch (Exception ex)
        {
          
        }

        return list;
    }

    public async Task<TestDto?> GetTest(int id)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand(@"
            SELECT id, title, description, access_mode
            FROM tests
            WHERE id = @id
            LIMIT 1", conn);
            cmd.Parameters.AddWithValue("id", id);

            await using var reader = await cmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                return new TestDto
                {
                    Id = reader.GetInt32(0),
                    Title = reader.GetString(1),
                    Description = reader.IsDBNull(2) ? null : reader.GetString(2),
                    AccessMode = reader.GetString(3)
                };
            }
        }
        catch (Exception ex)
        {

        }

        return null;
    }
}
