using System;

namespace RecruitmentDAL.Query.InterviewScorecard
{
    public static class InterviewScorecardQB
    {
        #region GET

        public const string GET_INTERVIEWSCORECARD = @"
            SELECT
                SC.INTERVIEWSCORECARDID  AS InterviewScorecardId,
                SC.INTERVIEWID           AS InterviewId,
                SC.INTERVIEWERID         AS InterviewerId,
                IU.USERNAME              AS InterviewerName,

                SC.OVERALLRECOMMENDATION AS OverallRecommendation,
                SC.OVERALLREMARKS        AS OverallRemarks,

                SC.SORTORDER             AS SortOrder,
                SC.STATUS                AS Status,
                SC.VERSION               AS Version,
                SC.TENANTID              AS TenantId,

                SC.CREATEDBYID           AS CreatedById,
                SC.CREATEDON             AS CreatedOn,
                CU.USERNAME              AS CreatedByName,

                SC.MODIFIEDBYID          AS ModifiedById,
                SC.MODIFIEDON            AS ModifiedOn,
                MU.USERNAME              AS ModifiedByName
            FROM TINTERVIEWSCORECARD SC
            LEFT JOIN MUSER IU ON IU.USERID = SC.INTERVIEWERID
            LEFT JOIN MUSER CU ON CU.USERID = SC.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID = SC.MODIFIEDBYID
            WHERE SC.INTERVIEWSCORECARDID = @InterviewScorecardId
            AND   SC.TENANTID             = @TenantId;
        ";

        // All active scorecards (one per interviewer) for one interview.
        public const string GET_INTERVIEWSCORECARD_BY_INTERVIEW = @"
            SELECT
                SC.INTERVIEWSCORECARDID  AS InterviewScorecardId,
                SC.INTERVIEWID           AS InterviewId,
                SC.INTERVIEWERID         AS InterviewerId,
                IU.USERNAME              AS InterviewerName,

                SC.OVERALLRECOMMENDATION AS OverallRecommendation,
                SC.OVERALLREMARKS        AS OverallRemarks,

                SC.SORTORDER             AS SortOrder,
                SC.STATUS                AS Status,
                SC.VERSION               AS Version,
                SC.TENANTID              AS TenantId,

                SC.CREATEDBYID           AS CreatedById,
                SC.CREATEDON             AS CreatedOn,
                CU.USERNAME              AS CreatedByName,

                SC.MODIFIEDBYID          AS ModifiedById,
                SC.MODIFIEDON            AS ModifiedOn,
                MU.USERNAME              AS ModifiedByName
            FROM TINTERVIEWSCORECARD SC
            LEFT JOIN MUSER IU ON IU.USERID = SC.INTERVIEWERID
            LEFT JOIN MUSER CU ON CU.USERID = SC.CREATEDBYID
            LEFT JOIN MUSER MU ON MU.USERID = SC.MODIFIEDBYID
            WHERE SC.INTERVIEWID = @InterviewId
            AND   SC.TENANTID    = @TenantId
            AND   SC.STATUS      = 1
            ORDER BY SC.SORTORDER;
        ";

        public const string GET_INTERVIEWSCORECARDDETAIL = @"
            SELECT
                SCD.INTERVIEWSCORECARDDETAILID AS InterviewScorecardDetailId,
                SCD.INTERVIEWSCORECARDID       AS InterviewScorecardId,

                SCD.SKILLID                    AS SkillId,
                SK.SKILLNAME                   AS SkillName,

                SCD.RATING                     AS Rating,
                SCD.REMARKS                    AS Remarks,

                SCD.STATUS                     AS Status,
                SCD.VERSION                    AS Version,
                SCD.TENANTID                   AS TenantId,

                SCD.CREATEDBYID                AS CreatedById,
                SCD.CREATEDON                  AS CreatedOn,
                CU.USERNAME                    AS CreatedByName,

                SCD.MODIFIEDBYID               AS ModifiedById,
                SCD.MODIFIEDON                 AS ModifiedOn,
                MU.USERNAME                    AS ModifiedByName
            FROM TINTERVIEWSCORECARDDETAIL SCD
            LEFT JOIN MSKILL SK ON SK.SKILLID = SCD.SKILLID
            LEFT JOIN MUSER  CU ON CU.USERID  = SCD.CREATEDBYID
            LEFT JOIN MUSER  MU ON MU.USERID  = SCD.MODIFIEDBYID
            WHERE SCD.INTERVIEWSCORECARDDETAILID = @InterviewScorecardDetailId
            AND   SCD.TENANTID                   = @TenantId;
        ";

        // All active per-skill rating rows for one scorecard — used both by the standalone
        // GetInterviewScorecardDetail-by-parent path and to hydrate
        // InterviewScorecardDTO.InterviewScorecardDetailArray for GetInterviewScorecardByInterview.
        public const string GET_INTERVIEWSCORECARDDETAIL_BY_SCORECARD = @"
            SELECT
                SCD.INTERVIEWSCORECARDDETAILID AS InterviewScorecardDetailId,
                SCD.INTERVIEWSCORECARDID       AS InterviewScorecardId,

                SCD.SKILLID                    AS SkillId,
                SK.SKILLNAME                   AS SkillName,

                SCD.RATING                     AS Rating,
                SCD.REMARKS                    AS Remarks,

                SCD.STATUS                     AS Status,
                SCD.VERSION                    AS Version,
                SCD.TENANTID                   AS TenantId,

                SCD.CREATEDBYID                AS CreatedById,
                SCD.CREATEDON                  AS CreatedOn,
                CU.USERNAME                    AS CreatedByName,

                SCD.MODIFIEDBYID               AS ModifiedById,
                SCD.MODIFIEDON                 AS ModifiedOn,
                MU.USERNAME                    AS ModifiedByName
            FROM TINTERVIEWSCORECARDDETAIL SCD
            LEFT JOIN MSKILL SK ON SK.SKILLID = SCD.SKILLID
            LEFT JOIN MUSER  CU ON CU.USERID  = SCD.CREATEDBYID
            LEFT JOIN MUSER  MU ON MU.USERID  = SCD.MODIFIEDBYID
            WHERE SCD.INTERVIEWSCORECARDID = @InterviewScorecardId
            AND   SCD.TENANTID             = @TenantId
            AND   SCD.STATUS                = 1
            ORDER BY SCD.INTERVIEWSCORECARDDETAILID;
        ";

        #endregion

        #region SAVE

        public const string SAVE_INTERVIEWSCORECARD = @"
            INSERT INTO TINTERVIEWSCORECARD
            (
                INTERVIEWSCORECARDID,
                INTERVIEWID,
                INTERVIEWERID,
                OVERALLRECOMMENDATION,
                OVERALLREMARKS,
                SORTORDER,
                STATUS,
                VERSION,
                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @InterviewScorecardId,
                @InterviewId,
                @InterviewerId,
                @OverallRecommendation,
                @OverallRemarks,
                @SortOrder,
                @Status,
                @Version,
                @TenantId,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            );
        ";

        public const string SAVE_INTERVIEWSCORECARDDETAIL = @"
            INSERT INTO TINTERVIEWSCORECARDDETAIL
            (
                INTERVIEWSCORECARDDETAILID,
                INTERVIEWSCORECARDID,
                SKILLID,
                RATING,
                REMARKS,
                STATUS,
                VERSION,
                TENANTID,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @InterviewScorecardDetailId,
                @InterviewScorecardId,
                @SkillId,
                @Rating,
                @Remarks,
                @Status,
                @Version,
                @TenantId,
                @CreatedById,
                @CreatedOn,
                @ModifiedById,
                @ModifiedOn
            );
        ";

        #endregion

        #region UPDATE

        public const string UPDATE_INTERVIEWSCORECARD = @"
            UPDATE TINTERVIEWSCORECARD
            SET
                INTERVIEWID           = @InterviewId,
                INTERVIEWERID         = @InterviewerId,
                OVERALLRECOMMENDATION = @OverallRecommendation,
                OVERALLREMARKS        = @OverallRemarks,
                SORTORDER             = @SortOrder,
                STATUS                = @Status,
                VERSION               = @Version,
                MODIFIEDBYID          = @ModifiedById,
                MODIFIEDON            = @ModifiedOn
            WHERE INTERVIEWSCORECARDID = @InterviewScorecardId
            AND   TENANTID             = @TenantId;
        ";

        public const string UPDATE_INTERVIEWSCORECARDDETAIL = @"
            UPDATE TINTERVIEWSCORECARDDETAIL
            SET
                SKILLID      = @SkillId,
                RATING       = @Rating,
                REMARKS      = @Remarks,
                STATUS       = @Status,
                VERSION      = @Version,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE INTERVIEWSCORECARDDETAILID = @InterviewScorecardDetailId
            AND   TENANTID                   = @TenantId;
        ";

        #endregion

        #region DELETE

        // Soft delete (STATUS = 2 / Deleted) — TINTERVIEWSCORECARD/DETAIL are part of an
        // interview's audit trail, so rows are marked deleted rather than physically removed,
        // matching the convention already used by JobRequisitionQB.DELETE_JOBREQUISITION /
        // ApplicationQB.DELETE_APPLICATION / SelectionProcessQB's soft-delete queries.
        public const string DELETE_INTERVIEWSCORECARD = @"
            UPDATE TINTERVIEWSCORECARD
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  INTERVIEWSCORECARDID = @InterviewScorecardId
            AND    TENANTID             = @TenantId;
        ";

        public const string DELETE_INTERVIEWSCORECARDDETAIL = @"
            UPDATE TINTERVIEWSCORECARDDETAIL
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  INTERVIEWSCORECARDDETAILID = @InterviewScorecardDetailId
            AND    TENANTID                   = @TenantId;
        ";

        #endregion
    }
}