using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using PIEDAL.DTO.Proposal;

namespace PIEDAL.Query.Proposal;

// Required indexes:
//   ix_pie_proposal_tenant      ON pie.TPIELEARNINGPROPOSAL(TENANTID)
//   ix_pie_proposal_profile     ON pie.TPIELEARNINGPROPOSAL(PIEPROFILEID, STATUS)

public sealed class PIEProposalQB : IQueryBuilder<PIEProposalListQuery, PIEProposalListDTO>
{
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["createdat"]         = "lp.CREATEDON",
            ["status"]            = "lp.STATUS",
            ["proposaltype"]      = "lp.PROPOSALTYPE",
            ["aiconfidencescore"] = "lp.AICONFIDENCESCORE",
            ["deviationrate"]     = "lp.DEVIATIONRATE"
        };

    public (string Sql, DynamicParameters Params) Build(PIEProposalListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort,
            "lp.STATUS ASC, lp.CREATEDON DESC");
        return BuildProposalList(query.Criteria, login, dialect, orderBy);
    }

    public static (string Sql, DynamicParameters Params) BuildProposalList(
        PIEProposalListCriteria c, LoginDTO login, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d)
            .Register("proposal", "lp", schema: "pie")
            .Register("profile",  "p",  schema: "pie");

        var tenantParams = new DynamicParameters();
        tenantParams.Add("TenantId", login.ClientId);

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            new SqlClause("lp.TENANTID = @TenantId", tenantParams),
            SqlClauses.ExactGuid(ctx,   c.ProfileId,    "proposal", "PIEPROFILEID"),
            SqlClauses.ExactShort(ctx,  c.Status,       "proposal", "STATUS"),
            SqlClauses.ExactShort(ctx, c.ProposalType, "proposal", "PROPOSALTYPE"),
            SqlClauses.CodeNameSearch(ctx, c.SearchText, "proposal", "PROPOSALTYPE", "PROPOSALDESCRIPTION"),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
SELECT {d.TotalCountExpr()},
       lp.PIELEARNINGPROPOSALID  AS ProposalId,
       lp.PIEPROFILEID            AS ProfileId,
       p.PIEPROFILECODE           AS ProfileCode,
       lp.PROPOSALTYPE            AS ProposalType,
       lp.PROPOSALDESCRIPTION     AS ProposalDescription,
       lp.STATUS                  AS Status,
       lp.DEVIATIONRATE           AS DeviationRate,
       lp.AICONFIDENCESCORE       AS AiConfidenceScore,
       lp.CREATEDON               AS CreatedAt
FROM   {ctx.Table("proposal", "TPIELEARNINGPROPOSAL")} lp
JOIN   {ctx.Table("profile", "MPIEPROFILE")} p
       ON  p.PIEPROFILEID = lp.PIEPROFILEID
       AND p.TENANTID     = lp.TENANTID
{whereSql}
{pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }

    // ── Proposal CRUD ─────────────────────────────────────────────────────────

    public const string GET_BY_ID = @"
        SELECT lp.PIELEARNINGPROPOSALID  AS ProposalId,
               lp.PIEPROFILEID            AS ProfileId,
               p.PIEPROFILECODE           AS ProfileCode,
               lp.PROPOSALTYPE            AS ProposalType,
               lp.PROPOSALDESCRIPTION     AS ProposalDescription,
               lp.EVIDENCEREFS            AS EvidenceRefs,
               lp.DEVIATIONRATE           AS DeviationRate,
               lp.AICONFIDENCESCORE       AS AiConfidenceScore,
               lp.PROPOSEDCONTENT         AS ProposedContent,
               lp.STATUS                  AS Status,
               lp.REVIEWEDBYID            AS ReviewedBy,
               lp.REVIEWEDON              AS ReviewedAt,
               lp.REVIEWERNOTES           AS ReviewNotes,
               lp.CREATEDON               AS CreatedAt,
               lp.TENANTID                AS TenantId
        FROM   pie.TPIELEARNINGPROPOSAL lp
        JOIN   pie.MPIEPROFILE p ON p.PIEPROFILEID = lp.PIEPROFILEID AND p.TENANTID = lp.TENANTID
        WHERE  lp.PIELEARNINGPROPOSALID = @ProposalId
          AND  lp.TENANTID              = @TenantId";

    public const string INSERT = @"
        INSERT INTO pie.TPIELEARNINGPROPOSAL
            (PIELEARNINGPROPOSALID, PIEPROFILEID, PROPOSALTYPE, PROPOSALDESCRIPTION, EVIDENCEREFS,
             DEVIATIONRATE, AICONFIDENCESCORE, PROPOSEDCONTENT, STATUS, CREATEDON, TENANTID)
        VALUES
            (@ProposalId, @ProfileId, @ProposalType, @ProposalDescription, @EvidenceRefs,
             @DeviationRate, @AiConfidenceScore, @ProposedContent, @Status, @CreatedAt, @TenantId)";

    /// <summary>Product admin reviews a proposal — sets status, reviewer, notes, and audit fields.</summary>
    public const string REVIEW = @"
        UPDATE pie.TPIELEARNINGPROPOSAL SET
            STATUS        = @Status,
            REVIEWEDBYID  = @ReviewedBy,
            REVIEWEDON    = @ReviewedAt,
            REVIEWERNOTES = @ReviewNotes,
            MODIFIEDBYID  = @UpdatedBy,
            MODIFIEDON    = @UpdatedAt
        WHERE PIELEARNINGPROPOSALID = @ProposalId
          AND TENANTID              = @TenantId
          AND STATUS                = 1";

    public const string DELETE = @"
        DELETE FROM pie.TPIELEARNINGPROPOSAL
        WHERE PIELEARNINGPROPOSALID = @ProposalId
          AND TENANTID              = @TenantId
          AND STATUS                = 1";
}