namespace RecruitmentDAL.Query.SelectionProcess
{
    public static class SelectionProcessQB
    {
        #region GET

        // Covering index: IX_MSELECTIONPROCESSTEMPLATE_TENANTID (TENANTID)
        public const string GET_SELECTIONPROCESSTEMPLATE = @"
SELECT
    SPT.SELECTIONPROCESSTEMPLATEID AS SelectionProcessTemplateId,
    SPT.TEMPLATENAME               AS TemplateName,
    SPT.TEMPLATECODE               AS TemplateCode,
    SPT.RECRUITMENTTYPE            AS RecruitmentType,
    SPT.DESCRIPTION                AS Description,

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

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

    SPT.MODIFIEDBYID               AS ModifiedById,
    SPT.MODIFIEDON                 AS ModifiedOn,
    MU.USERNAME                    AS ModifiedByName

FROM MSELECTIONPROCESSTEMPLATE SPT

LEFT JOIN MUSER CU ON CU.USERID = SPT.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = SPT.MODIFIEDBYID

WHERE SPT.SELECTIONPROCESSTEMPLATEID = @SelectionProcessTemplateId
  AND SPT.TENANTID                   = @TenantId;
";

        // Covering index: IX_MSELECTIONPROCESSSTAGE_TEMPLATEID_ORDER (SELECTIONPROCESSTEMPLATEID, STAGEORDER)
        // This IS the list variant — the FE renders/edits a template's full stage sequence,
        // never a single stage in isolation.
        public const string GET_SELECTIONPROCESSSTAGE = @"
SELECT
    SPS.SELECTIONPROCESSSTAGEID    AS SelectionProcessStageId,
    SPS.SELECTIONPROCESSTEMPLATEID AS SelectionProcessTemplateId,
    SPT.TEMPLATENAME                AS TemplateName,

    SPS.STAGENAME                  AS StageName,
    SPS.STAGEORDER                 AS StageOrder,
    SPS.STAGECATEGORY              AS StageCategory,
    SPS.ISTERMINAL                 AS IsTerminal,
    SPS.QUIZID                     AS QuizId,

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

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

    SPS.MODIFIEDBYID               AS ModifiedById,
    SPS.MODIFIEDON                 AS ModifiedOn,
    MU.USERNAME                    AS ModifiedByName

FROM MSELECTIONPROCESSSTAGE SPS

LEFT JOIN MSELECTIONPROCESSTEMPLATE SPT ON SPT.SELECTIONPROCESSTEMPLATEID = SPS.SELECTIONPROCESSTEMPLATEID
LEFT JOIN MUSER CU ON CU.USERID = SPS.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = SPS.MODIFIEDBYID

WHERE SPS.SELECTIONPROCESSTEMPLATEID = @SelectionProcessTemplateId
  AND SPS.TENANTID                   = @TenantId
  AND SPS.STATUS                     = 1
ORDER BY SPS.STAGEORDER;
";

        // Narrow, single-column read — consumed by ApplicationBLL.SaveApplicationStageHistory /
        // GetRequiredQuizForStage to check whether the stage being entered gates on a TMS Quiz,
        // without pulling the whole stage row. Covering index:
        // IX_MSELECTIONPROCESSSTAGE_TEMPLATEID_ORDER does not cover this lookup (it's keyed by
        // template, not stage id) — PK_MSELECTIONPROCESSSTAGE already covers it.
        public const string GET_SELECTIONPROCESSSTAGE_QUIZID = @"
SELECT QUIZID
FROM   MSELECTIONPROCESSSTAGE
WHERE  SELECTIONPROCESSSTAGEID = @SelectionProcessStageId
AND    TENANTID                = @TenantId;
";

        // Narrow, single-value read — consumed by ApplicationBLL.SaveApplicationStageHistory (via
        // ISelectionProcessBLL.IsSelectionProcessStageTerminalHireCategory) to check, without
        // pulling the whole stage row, whether the stage a candidate just entered is this module's
        // "genuine Hired" terminal category (STAGECATEGORY=7 AND ISTERMINAL=1) — see
        // CandidateConversionBLL's class doc comment for why this pairing (not StageName) is the
        // structural signal used repo-wide, and Recruitment Phase 6's Referral bonus-eligibility
        // hook for the consumer. Covering: PK_MSELECTIONPROCESSSTAGE already covers this lookup
        // (keyed by stage id).
        public const string GET_SELECTIONPROCESSSTAGE_TERMINALHIRE_FLAG = @"
SELECT
    CASE WHEN STAGECATEGORY = 7 AND ISTERMINAL = 1 THEN 1 ELSE 0 END
FROM   MSELECTIONPROCESSSTAGE
WHERE  SELECTIONPROCESSSTAGEID = @SelectionProcessStageId
AND    TENANTID                = @TenantId;
";

        #endregion

        #region SAVE

        public const string SAVE_SELECTIONPROCESSTEMPLATE = @"
INSERT INTO MSELECTIONPROCESSTEMPLATE
(
    SELECTIONPROCESSTEMPLATEID, TEMPLATENAME, TEMPLATECODE, RECRUITMENTTYPE, DESCRIPTION,
    SORTORDER, STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @SelectionProcessTemplateId, @TemplateName, @TemplateCode, @RecruitmentType, @Description,
    @SortOrder, @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);
";

        public const string SAVE_SELECTIONPROCESSSTAGE = @"
INSERT INTO MSELECTIONPROCESSSTAGE
(
    SELECTIONPROCESSSTAGEID, SELECTIONPROCESSTEMPLATEID, STAGENAME, STAGEORDER, STAGECATEGORY, ISTERMINAL, QUIZID,
    SORTORDER, STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @SelectionProcessStageId, @SelectionProcessTemplateId, @StageName, @StageOrder, @StageCategory, @IsTerminal, @QuizId,
    @SortOrder, @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);
";

        #endregion

        #region UPDATE

        public const string UPDATE_SELECTIONPROCESSTEMPLATE = @"
UPDATE MSELECTIONPROCESSTEMPLATE SET
    TEMPLATENAME    = @TemplateName,
    TEMPLATECODE    = @TemplateCode,
    RECRUITMENTTYPE = @RecruitmentType,
    DESCRIPTION     = @Description,
    SORTORDER       = @SortOrder,
    STATUS          = @Status,
    VERSION         = @Version,
    MODIFIEDBYID    = @ModifiedById,
    MODIFIEDON      = @ModifiedOn
WHERE SELECTIONPROCESSTEMPLATEID = @SelectionProcessTemplateId
  AND TENANTID                   = @TenantId;
";

        public const string UPDATE_SELECTIONPROCESSSTAGE = @"
UPDATE MSELECTIONPROCESSSTAGE SET
    STAGENAME      = @StageName,
    STAGEORDER     = @StageOrder,
    STAGECATEGORY  = @StageCategory,
    ISTERMINAL     = @IsTerminal,
    QUIZID         = @QuizId,
    SORTORDER      = @SortOrder,
    STATUS         = @Status,
    VERSION        = @Version,
    MODIFIEDBYID   = @ModifiedById,
    MODIFIEDON     = @ModifiedOn
WHERE SELECTIONPROCESSSTAGEID     = @SelectionProcessStageId
  AND SELECTIONPROCESSTEMPLATEID  = @SelectionProcessTemplateId
  AND TENANTID                    = @TenantId;
";

        #endregion

        #region DELETE

        // Soft-delete via STATUS=2 (Deleted) — matches the canonical Status enum
        // (Gb4EnumDTO.cs: 0=Pending,1=Active,2=Deleted,3=Amended,4=Inactive,5=Archived) and
        // the convention used by every other Phase-1 Recruitment entity (JobRequisition,
        // JobProfileLibrary, Application). TMSDAL.TrainingEnrollmentQB's STATUS=0 delete is
        // a non-canonical outlier, not followed here.
        public const string DELETE_SELECTIONPROCESSTEMPLATE = @"
UPDATE MSELECTIONPROCESSTEMPLATE SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE SELECTIONPROCESSTEMPLATEID = @SelectionProcessTemplateId
  AND TENANTID                   = @TenantId;
";

        public const string DELETE_SELECTIONPROCESSSTAGE = @"
UPDATE MSELECTIONPROCESSSTAGE SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE SELECTIONPROCESSSTAGEID = @SelectionProcessStageId
  AND TENANTID                = @TenantId;
";

        #endregion

        #region SELECT LIST

        public const string GET_SELECTLIST_SELECTIONPROCESSTEMPLATE = @"
SELECT
    SELECTIONPROCESSTEMPLATEID AS Id,
    TEMPLATENAME               AS Name
FROM MSELECTIONPROCESSTEMPLATE
WHERE STATUS   = 1
  AND TENANTID = @TenantId
ORDER BY TEMPLATENAME;
";

        public const string GET_SELECTLIST_SELECTIONPROCESSSTAGE = @"
SELECT
    SELECTIONPROCESSSTAGEID AS Id,
    STAGENAME                AS Name
FROM MSELECTIONPROCESSSTAGE
WHERE STATUS   = 1
  AND TENANTID = @TenantId
ORDER BY STAGEORDER;
";

        #endregion
    }
}