namespace TMSDAL.Query.EvaluationResponse
{
    /// <summary>
    /// Required indexes:
    ///   IX_TEVALUATIONRESPONSE_EVALUATIONINSTANCEID on (EVALUATIONINSTANCEID)
    ///   IX_TEVALUATIONRESPONSE_RESPONDENTID on (RESPONDENTID)
    /// </summary>
    public static class EvaluationResponseQB
    {
        // =====================================================
        // GET SINGLE
        // =====================================================
        public const string GET_EVALUATIONRESPONSE = @"
SELECT  er.EVALUATIONRESPONSEID     AS EvaluationResponseId,
        er.EVALUATIONINSTANCEID     AS EvaluationInstanceId,
        er.RESPONDENTID             AS RespondentId,
        emp.EMPLOYEENAME            AS RespondentName,
        er.RESPONSEDATAJSON         AS ResponseDataJson,
        er.OVERALLRATING            AS OverallRating,
        er.SUBMITTEDON              AS SubmittedOn,
        er.ISANONYMOUS              AS IsAnonymous,
        er.VERSION                  AS Version,
        er.STATUS                   AS Status,
        er.SORTORDER                AS SortOrder,
        er.SOURCETYPE               AS SourceType,
        er.TENANTID                 AS TenantId,
        er.CREATEDBYID              AS CreatedById,
        er.CREATEDON                AS CreatedOn,
        er.MODIFIEDBYID             AS ModifiedById,
        er.MODIFIEDON               AS ModifiedOn
FROM    DBO.TEVALUATIONRESPONSE er
LEFT JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = er.RESPONDENTID
WHERE   er.EVALUATIONRESPONSEID = @EvaluationResponseId
AND     er.TENANTID             = @TenantId";


        // =====================================================
        // GET LIST BY INSTANCE
        // =====================================================
        public const string GET_EVALUATIONRESPONSE_LIST = @"
SELECT  er.EVALUATIONRESPONSEID     AS EvaluationResponseId,
        er.EVALUATIONINSTANCEID     AS EvaluationInstanceId,
        er.RESPONDENTID             AS RespondentId,
        emp.EMPLOYEENAME            AS RespondentName,
        er.RESPONSEDATAJSON         AS ResponseDataJson,
        er.OVERALLRATING            AS OverallRating,
        er.SUBMITTEDON              AS SubmittedOn,
        er.ISANONYMOUS              AS IsAnonymous,
        er.VERSION                  AS Version,
        er.STATUS                   AS Status,
        er.TENANTID                 AS TenantId,
        er.CREATEDBYID              AS CreatedById,
        er.CREATEDON                AS CreatedOn,
        er.MODIFIEDBYID             AS ModifiedById,
        er.MODIFIEDON               AS ModifiedOn
FROM    DBO.TEVALUATIONRESPONSE er
LEFT JOIN DBO.MEMPLOYEE emp
        ON emp.EMPLOYEEID = er.RESPONDENTID
WHERE   er.EVALUATIONINSTANCEID = @EvaluationInstanceId
AND     er.TENANTID             = @TenantId
ORDER BY er.SUBMITTEDON DESC";


        // =====================================================
        // SAVE — Responses are immutable, no UPDATE
        // =====================================================
        public const string SAVE_EVALUATIONRESPONSE = @"
INSERT INTO DBO.TEVALUATIONRESPONSE
(
    EVALUATIONRESPONSEID, EVALUATIONINSTANCEID, RESPONDENTID,
    RESPONSEDATAJSON, OVERALLRATING, SUBMITTEDON, ISANONYMOUS,
    VERSION, STATUS, SORTORDER, SOURCETYPE, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @EvaluationResponseId, @EvaluationInstanceId, @RespondentId,
    @ResponseDataJson, @OverallRating, @SubmittedOn, @IsAnonymous,
    @Version, @Status, @SortOrder, @SourceType, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
)";
    }
}