namespace RecruitmentDAL.Query.Referral
{
    public static class ReferralQB
    {
        // ==============================
        // GET
        // ==============================

        // Covering index: IX_TREFERRAL_TENANTID + PK_TREFERRAL already ship with the Phase 6
        // Referral migration.
        public const string GET_REFERRAL = @"
SELECT
    R.REFERRALID            AS ReferralId,
    R.REFERRINGEMPLOYEEID   AS ReferringEmployeeId,
    R.CANDIDATEID            AS CandidateId,
    R.JOBREQUISITIONID       AS JobRequisitionId,
    R.REFERRALDATE           AS ReferralDate,
    R.BONUSSTATUS            AS BonusStatus,
    R.BONUSAMOUNT            AS BonusAmount,
    R.PAYOUTDATE             AS PayoutDate,

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

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

    R.MODIFIEDBYID             AS ModifiedById,
    R.MODIFIEDON               AS ModifiedOn,
    MU.USERNAME                 AS ModifiedByName
FROM TREFERRAL R
LEFT JOIN MUSER CU ON CU.USERID = R.CREATEDBYID
LEFT JOIN MUSER MU ON MU.USERID = R.MODIFIEDBYID
WHERE R.REFERRALID = @ReferralId
AND   R.TENANTID    = @TenantId;";

        // Used by ReferralBLL.MarkEligibleOnHireAsync (the ApplicationBLL.SaveApplicationStageHistory
        // "genuine Hired" hook — see that method's referral-eligibility-check comment) to find the
        // still-Pending referral row for a Candidate that just got Hired. Picks the newest
        // still-Pending row for the Candidate — a Candidate is expected to have at most one live
        // referral, but this fails safe (newest wins) rather than erroring on an unexpected
        // duplicate.
        public const string GET_LATEST_PENDING_REFERRAL_BY_CANDIDATE = @"
SELECT TOP 1
    R.REFERRALID            AS ReferralId,
    R.REFERRINGEMPLOYEEID   AS ReferringEmployeeId,
    R.CANDIDATEID            AS CandidateId,
    R.JOBREQUISITIONID       AS JobRequisitionId,
    R.REFERRALDATE           AS ReferralDate,
    R.BONUSSTATUS            AS BonusStatus,
    R.BONUSAMOUNT            AS BonusAmount,
    R.PAYOUTDATE             AS PayoutDate,
    R.STATUS                 AS Status,
    R.VERSION                AS Version,
    R.TENANTID               AS TenantId,
    R.CREATEDBYID             AS CreatedById,
    R.CREATEDON               AS CreatedOn,
    R.MODIFIEDBYID             AS ModifiedById,
    R.MODIFIEDON               AS ModifiedOn
FROM TREFERRAL R
WHERE R.CANDIDATEID = @CandidateId
AND   R.BONUSSTATUS  = 1  -- Pending
AND   R.STATUS       = 1
AND   R.TENANTID     = @TenantId
ORDER BY R.CREATEDON DESC;";

        // ==============================
        // SAVE / UPDATE
        // ==============================
        public const string SAVE_REFERRAL = @"
INSERT INTO TREFERRAL
(
    REFERRALID, REFERRINGEMPLOYEEID, CANDIDATEID, JOBREQUISITIONID,
    REFERRALDATE, BONUSSTATUS, BONUSAMOUNT, PAYOUTDATE,
    STATUS, VERSION, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @ReferralId, @ReferringEmployeeId, @CandidateId, @JobRequisitionId,
    @ReferralDate, @BonusStatus, @BonusAmount, @PayoutDate,
    @Status, @Version, @TenantId,
    @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
);";

        public const string UPDATE_REFERRAL = @"
UPDATE TREFERRAL SET
    REFERRINGEMPLOYEEID = @ReferringEmployeeId,
    CANDIDATEID          = @CandidateId,
    JOBREQUISITIONID     = @JobRequisitionId,
    REFERRALDATE         = @ReferralDate,
    BONUSSTATUS          = @BonusStatus,
    BONUSAMOUNT          = @BonusAmount,
    PAYOUTDATE           = @PayoutDate,
    STATUS               = @Status,
    VERSION              = @Version,
    MODIFIEDBYID         = @ModifiedById,
    MODIFIEDON           = @ModifiedOn
WHERE REFERRALID = @ReferralId
AND   TENANTID    = @TenantId;";

        // ==============================
        // DELETE (soft delete — STATUS=2, matching BgVerificationQB.DELETE_BGVERIFICATION /
        // ApplicationQB.DELETE_APPLICATION's established convention)
        // ==============================
        public const string DELETE_REFERRAL = @"
UPDATE TREFERRAL SET
    STATUS       = 2,
    MODIFIEDBYID = @ModifiedById,
    MODIFIEDON   = @ModifiedOn
WHERE REFERRALID = @ReferralId
AND   TENANTID    = @TenantId;";

        // ==============================
        // SELECT LIST — one referring employee's own referral history, newest first.
        // ==============================
        public const string GET_SELECTLIST_REFERRAL_BY_EMPLOYEE = @"
SELECT
    R.REFERRALID            AS ReferralId,
    R.REFERRINGEMPLOYEEID   AS ReferringEmployeeId,
    R.CANDIDATEID            AS CandidateId,
    R.JOBREQUISITIONID       AS JobRequisitionId,
    R.REFERRALDATE           AS ReferralDate,
    R.BONUSSTATUS            AS BonusStatus,
    R.BONUSAMOUNT            AS BonusAmount,
    R.PAYOUTDATE             AS PayoutDate,
    R.STATUS                 AS Status,
    R.TENANTID               AS TenantId
FROM TREFERRAL R
WHERE R.REFERRINGEMPLOYEEID = @ReferringEmployeeId
AND   R.STATUS               <> 2
AND   R.TENANTID              = @TenantId
ORDER BY R.CREATEDON DESC;";
    }
}