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

namespace PIEDAL.CustomCode.Proposal;

public class PIEProposalDAL : IPIEProposalDAL
{
    private readonly IQueryExecutor _QueryExecutor;

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

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

    public async Task<PIELearningProposalDTO?> GetPIEProposalAsync(
        Guid proposalId, LoginDTO login, CancellationToken ct)
        => await _QueryExecutor.QuerySingleAsync<PIELearningProposalDTO>(
            login, PIEProposalQB.GET_BY_ID,
            new { ProposalId = proposalId },
            cancellationToken: ct).ConfigureAwait(false);

    // ── Writes ────────────────────────────────────────────────────────────────

    public async Task<Result<string>> InsertPIEProposalAsync(
        PIELearningProposalDTO dto, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        dto.CreatedAt = DateTime.UtcNow;
        dto.Status    = 1; // Pending — always starts as pending

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

        return rows > 0
            ? Result<string>.Success("PIE learning proposal created.")
            : Result<string>.Failure("Failed to create PIE learning proposal.");
    }

    public async Task<Result<string>> ReviewPIEProposalAsync(
        PIEReviewProposalDTO dto, int reviewedBy, LoginDTO login,
        DbTransaction tx, CancellationToken ct)
    {
        var param = new
        {
            ProposalId  = dto.ProposalId,
            Status      = dto.Status,
            ReviewedBy  = reviewedBy,
            ReviewedAt  = DateTime.UtcNow,
            ReviewNotes = dto.ReviewNotes,
            UpdatedBy   = login.UserId,
            UpdatedAt   = DateTime.UtcNow
        };

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

        return rows > 0
            ? Result<string>.Success("PIE proposal reviewed.")
            : Result<string>.Failure("PIE proposal not found or no longer in Pending status.");
    }

    public async Task<Result<string>> DeletePIEProposalAsync(
        Guid proposalId, LoginDTO login, DbTransaction tx, CancellationToken ct)
    {
        int rows = await _QueryExecutor.ExecuteAsync(
            login, PIEProposalQB.DELETE,
            new { ProposalId = proposalId }, tx,
            cancellationToken: ct).ConfigureAwait(false);

        return rows > 0
            ? Result<string>.Success("PIE proposal deleted.")
            : Result<string>.Failure("PIE proposal not found or not in Pending status.");
    }
}