namespace RecruitmentDAL.Query.BgVerification
{
    public static class BgVerificationQB
    {
        // ==============================
        // GET
        // ==============================

        // Covering index: IX_TBGVERIFICATION_APPLICATIONID + IX_TBGVERIFICATION_TENANTID already
        // ship with the Phase 6 migration.
        public const string GET_BGVERIFICATION = @"
SELECT
    BV.BGVERIFICATIONID     AS BgVerificationId,
    BV.APPLICATIONID        AS ApplicationId,
    BV.CHECKTYPE             AS CheckType,
    BV.CHECKSTATUS           AS CheckStatus,
    BV.VENDORREFERENCE       AS VendorReference,
    BV.REPORTATTACHMENTID    AS ReportAttachmentId,
    BV.INITIATEDON           AS InitiatedOn,
    BV.COMPLETEDON           AS CompletedOn,

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

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

    BV.MODIFIEDBYID            AS ModifiedById,
    BV.MODIFIEDON              AS ModifiedOn,
    MU.USERNAME                AS ModifiedByName
FROM TBGVERIFICATION BV
LEFT JOIN MUSER CU ON CU.USERID = BV.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = BV.MODIFIEDBYID
WHERE BV.BGVERIFICATIONID = @BgVerificationId
AND   BV.TENANTID          = @TenantId;";

        // Used by RecruitmentFlsBridgeBLL's ReferenceCheck submit handling to find the specific
        // BG-verification row (CheckType=Reference) a dispatched FLS action is fulfilling, when
        // the dispatch itself didn't carry a BgVerificationId (e.g. an older link, or one
        // prepared before this column existed). Picks the newest still-open (Pending/InProgress)
        // row for the pair, matching "most recently initiated check wins" semantics.
        public const string GET_LATEST_OPEN_BGVERIFICATION_BY_APPLICATION_AND_TYPE = @"
SELECT TOP 1
    BV.BGVERIFICATIONID     AS BgVerificationId,
    BV.APPLICATIONID        AS ApplicationId,
    BV.CHECKTYPE             AS CheckType,
    BV.CHECKSTATUS           AS CheckStatus,
    BV.VENDORREFERENCE       AS VendorReference,
    BV.REPORTATTACHMENTID    AS ReportAttachmentId,
    BV.INITIATEDON           AS InitiatedOn,
    BV.COMPLETEDON           AS CompletedOn,
    BV.STATUS                AS Status,
    BV.VERSION               AS Version,
    BV.TENANTID              AS TenantId,
    BV.CREATEDBYID            AS CreatedById,
    BV.CREATEDON              AS CreatedOn,
    BV.MODIFIEDBYID            AS ModifiedById,
    BV.MODIFIEDON              AS ModifiedOn
FROM TBGVERIFICATION BV
WHERE BV.APPLICATIONID = @ApplicationId
AND   BV.CHECKTYPE      = @CheckType
AND   BV.STATUS         = 1
AND   BV.CHECKSTATUS    IN (1, 2)
AND   BV.TENANTID       = @TenantId
ORDER BY BV.CREATEDON DESC;";

        // ==============================
        // SAVE / UPDATE
        // ==============================
        public const string SAVE_BGVERIFICATION = @"
INSERT INTO TBGVERIFICATION
(
    BGVERIFICATIONID, APPLICATIONID, CHECKTYPE, CHECKSTATUS,
    VENDORREFERENCE, REPORTATTACHMENTID, INITIATEDON, COMPLETEDON,
    STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @BgVerificationId, @ApplicationId, @CheckType, @CheckStatus,
    @VendorReference, @ReportAttachmentId, @InitiatedOn, @CompletedOn,
    @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);";

        public const string UPDATE_BGVERIFICATION = @"
UPDATE TBGVERIFICATION SET
    APPLICATIONID       = @ApplicationId,
    CHECKTYPE            = @CheckType,
    CHECKSTATUS          = @CheckStatus,
    VENDORREFERENCE      = @VendorReference,
    REPORTATTACHMENTID   = @ReportAttachmentId,
    INITIATEDON          = @InitiatedOn,
    COMPLETEDON          = @CompletedOn,
    STATUS               = @Status,
    VERSION              = @Version,
    MODIFIEDBYID         = @ModifiedById,
    MODIFIEDON           = @ModifiedOn
WHERE BGVERIFICATIONID = @BgVerificationId
AND   TENANTID          = @TenantId;";

        // ==============================
        // DELETE (soft delete — STATUS=2, matching ApplicationQB.DELETE_APPLICATION /
        // RecruitmentFlsActionQB.DELETE_RECRUITMENTFLSACTION's established convention)
        // ==============================
        public const string DELETE_BGVERIFICATION = @"
UPDATE TBGVERIFICATION SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE BGVERIFICATIONID = @BgVerificationId
AND   TENANTID          = @TenantId;";

        // ==============================
        // SELECT LIST — the "BG verification panel" for one Application (every concurrent
        // check, newest first).
        // ==============================
        public const string GET_SELECTLIST_BGVERIFICATION_BY_APPLICATION = @"
SELECT
    BV.BGVERIFICATIONID     AS BgVerificationId,
    BV.APPLICATIONID        AS ApplicationId,
    BV.CHECKTYPE             AS CheckType,
    BV.CHECKSTATUS           AS CheckStatus,
    BV.VENDORREFERENCE       AS VendorReference,
    BV.REPORTATTACHMENTID    AS ReportAttachmentId,
    BV.INITIATEDON           AS InitiatedOn,
    BV.COMPLETEDON           AS CompletedOn,
    BV.STATUS                AS Status,
    BV.TENANTID              AS TenantId
FROM TBGVERIFICATION BV
WHERE BV.APPLICATIONID = @ApplicationId
AND   BV.STATUS        <> 2
AND   BV.TENANTID       = @TenantId
ORDER BY BV.CREATEDON DESC;";
    }
}