using System.Data.Common;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.ResponseStandard;
using PIEDAL.DTO.Profile;
using PIEDAL.Query.Profile;

namespace PIEDAL.CustomCode.Profile;

public class PIEProfileDAL : IPIEProfileDAL
{
    private readonly IQueryExecutor _QueryExecutor;

    public PIEProfileDAL(IQueryExecutor queryExecutor)
    {
        _QueryExecutor = queryExecutor;
    }

    // ── Reads ─────────────────────────────────────────────────────────────────

    public async Task<PIEProfileDTO?> GetPIEProfileAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<PIEProfileDTO>(
            login, PIEProfileQB.GET_BY_ID,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false);

    public async Task<IEnumerable<PIEProfileModuleDTO>> GetProfileModulesAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PIEProfileModuleDTO>(
            login, PIEProfileQB.GET_PROFILE_MODULES,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEProfileModuleDTO>();

    public async Task<IEnumerable<PIEProfileVariantDefaultDTO>> GetVariantDefaultsAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PIEProfileVariantDefaultDTO>(
            login, PIEProfileQB.GET_VARIANT_DEFAULTS,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEProfileVariantDefaultDTO>();

    public async Task<IEnumerable<PIEProfileKpiDTO>> GetProfileKpisAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PIEProfileKpiDTO>(
            login, PIEProfileQB.GET_PROFILE_KPIS,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEProfileKpiDTO>();

    public async Task<IEnumerable<PIEDeviationQuestionDTO>> GetDeviationQuestionsAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PIEDeviationQuestionDTO>(
            login, PIEProfileQB.GET_DEVIATION_QUESTIONS,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEDeviationQuestionDTO>();

    public async Task<IEnumerable<PIEDeviationQuestionDTO>> GetDeviationQuestionsByProfileIdsAsync(
        Guid[] profileIds, LoginDTO login, CancellationToken ct)
        // ANY(@ProfileIds) — Npgsql resolves Guid[] automatically
        => await _QueryExecutor.QueryAsync<PIEDeviationQuestionDTO>(
            login, PIEProfileQB.GET_DEVIATION_QUESTIONS_BATCH,
            new { ProfileIds = profileIds },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEDeviationQuestionDTO>();

    public async Task<IEnumerable<PIEDeviationImplicationDTO>> GetDeviationImplicationsAsync(
        Guid[] questionIds, LoginDTO login, CancellationToken ct)
        // ANY(@QuestionIds) — Npgsql resolves Guid[] automatically
        => await _QueryExecutor.QueryAsync<PIEDeviationImplicationDTO>(
            login, PIEProfileQB.GET_DEVIATION_IMPLICATIONS,
            new { QuestionIds = questionIds },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEDeviationImplicationDTO>();

    public async Task<IEnumerable<PIEProfileDTO>> MatchProfilesAsync(
        PIEProfileMatchRequestDTO req, LoginDTO login, CancellationToken ct)
    {
        var param = new
        {
            Industry    = req.Industry,
            Geography   = req.Geography,
            CompanySize = req.CompanySize
        };

        return await _QueryExecutor.QueryAsync<PIEProfileDTO>(
            login, PIEProfileQB.MATCH_PROFILES, param,
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEProfileDTO>();
    }

    public async Task<IEnumerable<PIEProfileSelectListDTO>> GetProfileSelectListAsync(
        LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QueryAsync<PIEProfileSelectListDTO>(
            login, PIEProfileQB.GET_SELECT_LIST,
            new { },
            cancellationToken: ct).ConfigureAwait(false)
            ?? Enumerable.Empty<PIEProfileSelectListDTO>();

    // ── Profile writes ────────────────────────────────────────────────────────

    public async Task<Result<string>> SavePIEProfileAsync(
        PIEProfileDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedBy = login.UserId;
        dto.CreatedAt = DateTime.UtcNow;
        dto.UpdatedBy = login.UserId;
        dto.UpdatedAt = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE profile saved.")
            : Result<string>.Failure("Failed to save PIE profile.");
    }

    public async Task<Result<string>> UpdatePIEProfileAsync(
        PIEProfileDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.UpdatedBy = login.UserId;
        dto.UpdatedAt = DateTime.UtcNow;

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.UPDATE, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE profile updated.")
            : Result<string>.Failure("PIE profile not found or not updated.");
    }

    public async Task<Result<string>> PublishPIEProfileAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        var param = new
        {
            ProfileId   = profileId,
            PublishedAt = DateTime.UtcNow,
            PublishedBy = login.UserId,
            UpdatedAt   = DateTime.UtcNow,
            UpdatedBy   = login.UserId
        };

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.PUBLISH, param, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE profile published.")
            : Result<string>.Failure("PIE profile not found or not in Draft status.");
    }

    public async Task<Result<string>> DeprecatePIEProfileAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        var param = new
        {
            ProfileId = profileId,
            UpdatedAt = DateTime.UtcNow,
            UpdatedBy = login.UserId
        };

        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DEPRECATE, param, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE profile deprecated.")
            : Result<string>.Failure("PIE profile not found or not in Active status.");
    }

    public async Task IncrementUsageCountAsync(
        Guid profileId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INCREMENT_USAGE_COUNT,
            new { ProfileId = profileId },
            cancellationToken: ct).ConfigureAwait(false);

    // ── Child insert / replace ────────────────────────────────────────────────

    public async Task DeleteProfileModulesAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DELETE_PROFILE_MODULES,
            new { ProfileId = profileId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertProfileModuleAsync(
        PIEProfileModuleDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT_PROFILE_MODULE, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeleteVariantDefaultsAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DELETE_VARIANT_DEFAULTS,
            new { ProfileId = profileId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertVariantDefaultAsync(
        PIEProfileVariantDefaultDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT_VARIANT_DEFAULT, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeleteProfileKpisAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DELETE_PROFILE_KPIS,
            new { ProfileId = profileId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertProfileKpiAsync(
        PIEProfileKpiDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT_PROFILE_KPI, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeleteDeviationQuestionsAsync(
        Guid profileId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DELETE_DEVIATION_QUESTIONS,
            new { ProfileId = profileId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task DeleteDeviationImplicationsByQuestionAsync(
        Guid questionId, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.DELETE_DEVIATION_IMPLICATIONS_BY_QUESTION,
            new { QuestionId = questionId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

    public async Task InsertDeviationQuestionAsync(
        PIEDeviationQuestionDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedBy = login.UserId;
        dto.CreatedAt = DateTime.UtcNow;

        await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT_DEVIATION_QUESTION, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);
    }

    public async Task InsertDeviationImplicationAsync(
        PIEDeviationImplicationDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
        => await _QueryExecutor.ExecuteAsync(
            login, PIEProfileQB.INSERT_DEVIATION_IMPLICATION, dto, tx,
            cancellationToken: ct).ConfigureAwait(false);
}