using AiLogia.Bot.Models;
using AiLogia.Bot.Services;
using Microsoft.Extensions.Configuration;
using Npgsql;

namespace AiLogia.Bot.Data;

public interface ITestsRepository
{
    Task<IReadOnlyCollection<TestShortModel>> GetAvailableTestsAsync(string audienceType);
    Task<IReadOnlyCollection<TestShortModel>> GetAvailableTestsAsync(UserAccessModel user);
    Task<TestModel?> GetTestByIdAsync(int id);
    Task<int> GetTotalQuestionsCountAsync(int testId);
    Task<QuestionModel?> GetFirstQuestionAsync(int testId);
    Task SaveUserAnswerAsync(long userId, int testId, int questionId, int answerId);
    Task<QuestionModel?> GetNextQuestionAsync(int testId, int afterQuestionId);
    Task<int> GetQuestionOrderIndexAsync(int questionId);
    Task<(string result, string interpretation)> CalculateResultAsync(int testId, long userId, string functionName);
    Task SaveUserResultAsync(long userId, int testId, string resultText, string interpretation, int? totalScore);
    Task ClearUserProgressAsync(long userId, int testId);
    Task<IReadOnlyCollection<TestResultModel>> GetUserResultsAsync(long userId);
    Task<UserAccessModel> GetOrCreateUserAccessAsync(long userId);
    Task MarkAllResultsAsForgottenAsync(long userId);
    Task<QuestionModel?> GetPreviousQuestionAsync(int testId, int currentQuestionId);
}


public class TestsRepository(
    IConfiguration config,
    ISystemLogService log) : ITestsRepository
{
    private readonly ISystemLogService _log = log;
    private readonly string _connectionString = config.GetConnectionString("Postgres") ??
                            throw new InvalidOperationException("Missing Postgres connection string");

    public async Task<IReadOnlyCollection<TestShortModel>> GetAvailableTestsAsync(string audienceType)
    {
        var list = new List<TestShortModel>();

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

            var cmd = new NpgsqlCommand(@"
            SELECT t.id, t.code, t.title, t.access_mode, g.title AS group_title
            FROM tests t
            LEFT JOIN tests_groups g ON g.id = t.group_id
            WHERE audience ?| @audienceList
            ORDER BY g.title NULLS LAST, t.title", conn);

            cmd.Parameters.AddWithValue(
                "audienceList",
                NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text,
                new[] { audienceType, "all" });

            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                list.Add(new TestShortModel
                {
                    Id = reader.GetInt32(0),
                    Code = reader.GetString(1),
                    Title = reader.GetString(2),
                    AccessMode = reader.GetString(3),
                    GroupTitle = reader.IsDBNull(4) ? null : reader.GetString(4)
                });
            }
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { audienceType });
        }

        return list;
    }

    public async Task<IReadOnlyCollection<TestShortModel>> GetAvailableTestsAsync(UserAccessModel user)
    {
        var list = new List<TestShortModel>();

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

            var cmd = new NpgsqlCommand(@"
            SELECT t.id, t.code, t.title, t.access_mode, g.title AS group_title
            FROM tests t
            LEFT JOIN tests_groups g ON g.id = t.group_id
            WHERE (
                t.access_mode = 'public'
                OR t.access_mode = 'channel_subscription_required'
                OR (t.access_mode = 'payed_only' AND @isPaid = true)
              )
              AND t.audience ?| @audienceList
            ORDER BY g.title NULLS LAST, t.title", conn);

            cmd.Parameters.AddWithValue("isPaid", user.HasActivePaidSubscription);
            cmd.Parameters.AddWithValue(
                "audienceList",
                NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text,
                new[] { user.AudienceType, "all" });

            await using var reader = await cmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                list.Add(new TestShortModel
                {
                    Id = reader.GetInt32(0),
                    Code = reader.GetString(1),
                    Title = reader.GetString(2),
                    AccessMode = reader.GetString(3),
                    GroupTitle = reader.IsDBNull(4) ? null : reader.GetString(4)
                });
            }
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { user.UserId, user.AudienceType, user.AccessLevel });
        }

        return list;
    }



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

            var cmd = new NpgsqlCommand(@"
            SELECT id, title, description, result_function_name, 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 TestModel
                {
                    Id = reader.GetInt32(0),
                    Title = reader.GetString(1),
                    Description = reader.IsDBNull(2) ? null : reader.GetString(2),
                    ResultFunctionName = reader.GetString(3),
                    AccessMode = reader.GetString(4)
                };
            }
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { id });
        }

        return null;
    }

    public async Task<int> GetTotalQuestionsCountAsync(int testId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand("SELECT COUNT(1) FROM questions WHERE test_id = @tid", conn);
            cmd.Parameters.AddWithValue("tid", testId);

            var count = await cmd.ExecuteScalarAsync();
            return Convert.ToInt32(count);
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { testId });
            return 0;
        }
    }

    public async Task<QuestionModel?> GetFirstQuestionAsync(int testId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            // 1. Найдём первый вопрос
            var cmdQ = new NpgsqlCommand(@"
            SELECT id, text
            FROM questions
            WHERE test_id = @tid
            ORDER BY order_index ASC
            LIMIT 1", conn);
            cmdQ.Parameters.AddWithValue("tid", testId);

            int questionId;
            string questionText;

            await using (var reader = await cmdQ.ExecuteReaderAsync())
            {
                if (!await reader.ReadAsync())
                    return null;

                questionId = reader.GetInt32(0);
                questionText = reader.GetString(1);
            }

            // 2. Найдём ответы на вопрос
            var cmdA = new NpgsqlCommand(@"
            SELECT id, letter, text, score
            FROM answers
            WHERE question_id = @qid
            ORDER BY id", conn);
            cmdA.Parameters.AddWithValue("qid", questionId);

            var answers = new List<AnswerOption>();
            await using (var reader = await cmdA.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    answers.Add(new AnswerOption
                    {
                        Id = reader.GetInt32(0),
                        Letter = reader.GetString(1),
                        Text = reader.GetString(2),
                        Score = reader.GetInt32(3)
                    });
                }
            }

            return new QuestionModel
            {
                Id = questionId,
                Text = questionText,
                Answers = answers
            };
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { testId });
            return null;
        }
    }

    public async Task SaveUserAnswerAsync(long userId, int testId, int questionId, int answerId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand(@"
            INSERT INTO tests_progress (user_id, test_id, question_id, answer_id, answered_at)
            VALUES (@uid, @tid, @qid, @aid, now())
            ON CONFLICT (user_id, test_id, question_id) DO UPDATE
            SET answer_id = @aid, answered_at = now()", conn);

            cmd.Parameters.AddWithValue("uid", userId);
            cmd.Parameters.AddWithValue("tid", testId);
            cmd.Parameters.AddWithValue("qid", questionId);
            cmd.Parameters.AddWithValue("aid", answerId);

            await cmd.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { userId, testId, questionId, answerId });
        }
    }

    public async Task<QuestionModel?> GetNextQuestionAsync(int testId, int afterQuestionId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            // Получаем order_index текущего
            var cmdIndex = new NpgsqlCommand("SELECT order_index FROM questions WHERE id = @qid", conn);
            cmdIndex.Parameters.AddWithValue("qid", afterQuestionId);
            var currentIndex = Convert.ToInt32(await cmdIndex.ExecuteScalarAsync());

            // Получаем следующий вопрос
            var cmdQ = new NpgsqlCommand(@"
            SELECT id, text FROM questions
            WHERE test_id = @tid AND order_index > @index
            ORDER BY order_index ASC LIMIT 1", conn);
            cmdQ.Parameters.AddWithValue("tid", testId);
            cmdQ.Parameters.AddWithValue("index", currentIndex);

            int qid;
            string qtext;

            await using (var reader = await cmdQ.ExecuteReaderAsync())
            {
                if (!await reader.ReadAsync()) return null;

                qid = reader.GetInt32(0);
                qtext = reader.GetString(1);
            }

            // Ответы
            var cmdA = new NpgsqlCommand("SELECT id, letter, text, score FROM answers WHERE question_id = @qid", conn);
            cmdA.Parameters.AddWithValue("qid", qid);

            var answers = new List<AnswerOption>();
            await using (var reader = await cmdA.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    answers.Add(new AnswerOption
                    {
                        Id = reader.GetInt32(0),
                        Letter = reader.GetString(1),
                        Text = reader.GetString(2),
                        Score = reader.GetInt32(3)
                    });
                }
            }

            return new QuestionModel
            {
                Id = qid,
                Text = qtext,
                Answers = answers
            };
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { testId, afterQuestionId });
            return null;
        }
    }

    public async Task<int> GetQuestionOrderIndexAsync(int questionId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand("SELECT order_index FROM questions WHERE id = @qid", conn);
            cmd.Parameters.AddWithValue("qid", questionId);

            var result = await cmd.ExecuteScalarAsync();
            return Convert.ToInt32(result);
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { questionId });
            return -1;
        }
    }

    public async Task<(string result, string interpretation)> CalculateResultAsync(int testId, long userId, string functionName)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand($@"
            SELECT result, interpretation
            FROM public.{functionName}(@user_id, @test_id)", conn);

            cmd.Parameters.AddWithValue("test_id", testId);
            cmd.Parameters.AddWithValue("user_id", userId);

            await using var reader = await cmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                var result = reader.GetString(0);
                var interpretation = reader.IsDBNull(1) ? "" : reader.GetString(1);
                return (result, interpretation);
            }
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { testId, userId, functionName });
        }

        return ("", "");
    }

    public async Task SaveUserResultAsync(long userId, int testId, string resultText, string interpretation, int? totalScore)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand(@"
            INSERT INTO tests_results (user_id, test_id, result_text, interpretation, completed_at, total_score)
            VALUES (@uid, @tid, @result, @interp, now(), @score)", conn);

            cmd.Parameters.AddWithValue("uid", userId);
            cmd.Parameters.AddWithValue("tid", testId);
            cmd.Parameters.AddWithValue("result", resultText);
            cmd.Parameters.AddWithValue("interp", (object?)interpretation ?? DBNull.Value);
            cmd.Parameters.AddWithValue("score", (object?)totalScore ?? DBNull.Value);

            await cmd.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { userId, testId, resultText, interpretation, totalScore });
        }
    }
    public async Task ClearUserProgressAsync(long userId, int testId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand("DELETE FROM tests_progress WHERE user_id = @uid AND test_id = @tid", conn);
            cmd.Parameters.AddWithValue("uid", userId);
            cmd.Parameters.AddWithValue("tid", testId);

            await cmd.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { userId, testId });
        }
    }

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

        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 TestResultModel
                {
                    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)
        {
            await _log.ExceptionAsync(ex, new { userId });
        }

        return list;
    }

    public async Task<UserAccessModel> GetOrCreateUserAccessAsync(long userId)
    {
        try
        {
            await using var conn = new NpgsqlConnection(_connectionString);
            await conn.OpenAsync();

            var cmd = new NpgsqlCommand(@"
                INSERT INTO user_access (user_id, audience_type, access_level)
                VALUES (@uid, 'b2c', 'free')
                ON CONFLICT (user_id) DO NOTHING;", conn);
            cmd.Parameters.AddWithValue("uid", userId);
            await cmd.ExecuteNonQueryAsync();

            var select = new NpgsqlCommand(@"
                SELECT audience_type, access_level, access_expires_at
                FROM user_access WHERE user_id = @uid", conn);
            select.Parameters.AddWithValue("uid", userId);

            await using var reader = await select.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                return new UserAccessModel
                {
                    UserId = userId,
                    AudienceType = reader.GetString(0),
                    AccessLevel = reader.GetString(1),
                    SubscriptionExpiresAt = reader.IsDBNull(2) ? null : reader.GetDateTime(2)
                };
            }

            throw new Exception("Failed to fetch user access");
        }
        catch (Exception ex)
        {
            await _log.ExceptionAsync(ex, new { userId });
            return new UserAccessModel { UserId = userId }; // fallback
        }
    }

    public async Task MarkAllResultsAsForgottenAsync(long userId)
    {
        await using var conn = new NpgsqlConnection(_connectionString);
        await conn.OpenAsync();

        var cmd = new NpgsqlCommand("UPDATE tests_results SET is_forgotten = true WHERE user_id = @uid", conn);
        cmd.Parameters.AddWithValue("uid", userId);
        await cmd.ExecuteNonQueryAsync();
    }

    // back button
    public async Task<QuestionModel?> GetPreviousQuestionAsync(int testId, int currentQuestionId)
    {
        await using var conn = new NpgsqlConnection(_connectionString);
        await conn.OpenAsync();

        // Получаем order_index текущего вопроса
        var cmdIndex = new NpgsqlCommand("SELECT order_index FROM questions WHERE id = @qid", conn);
        cmdIndex.Parameters.AddWithValue("qid", currentQuestionId);
        var currentIndex = Convert.ToInt32(await cmdIndex.ExecuteScalarAsync());

        // Получаем предыдущий вопрос по order_index
        var cmdQ = new NpgsqlCommand(@"
        SELECT id, text FROM questions
        WHERE test_id = @tid AND order_index < @index
        ORDER BY order_index DESC LIMIT 1", conn);
        cmdQ.Parameters.AddWithValue("tid", testId);
        cmdQ.Parameters.AddWithValue("index", currentIndex);

        int qid;
        string qtext;

        await using (var reader = await cmdQ.ExecuteReaderAsync())
        {
            if (!await reader.ReadAsync()) return null;

            qid = reader.GetInt32(0);
            qtext = reader.GetString(1);
        }

        // Загружаем ответы
        var cmdA = new NpgsqlCommand("SELECT id, letter, text, score FROM answers WHERE question_id = @qid", conn);
        cmdA.Parameters.AddWithValue("qid", qid);

        var answers = new List<AnswerOption>();
        await using (var reader = await cmdA.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                answers.Add(new AnswerOption
                {
                    Id = reader.GetInt32(0),
                    Letter = reader.GetString(1),
                    Text = reader.GetString(2),
                    Score = reader.GetInt32(3)
                });
            }
        }

        return new QuestionModel
        {
            Id = qid,
            Text = qtext,
            Answers = answers
        };
    }

}