namespace RecruitmentDAL.Query.JobOffer
{
    public static class JobOfferQB
    {
        // ==============================
        // GET
        // ==============================
        public const string GET_JOBOFFER = @"
SELECT
    JO.JOBOFFERID           AS JobOfferId,

    JO.APPLICANTID         AS ApplicantId,
    JO.APPLICATIONID       AS ApplicationId,

    JO.JOBPOSITIONID       AS JobPositionId,
    JO.DEPARTMENTID        AS DepartmentId,

    JO.LOCATIONID          AS LocationId,

    JO.EXPECTEDJOININGDATE AS ExpectedJoiningDate,
    JO.OFFEREXPIRYDATE     AS OfferExpiryDate,
    JO.CTC                 AS CTC,
    JO.EMPLOYEEMENTTYPE    AS EmployeementType,
    JO.PROBATIONPERIOD     AS ProbationPeriod,
    JO.ISORDERGENERATED    AS IsOrderGenerated,
    JO.OFFERRESPONSESTATUS AS OfferResponseStatus,
    JO.OFFERRESPONSEDATE   AS OfferResponseDate,

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

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

    JO.SORTORDER           AS SortOrder,
    JO.STATUS              AS Status,
    JO.VERSION             AS Version,
    JO.SOURCETYPE          AS SourceType,
    JO.TENANTID            AS TenantId

FROM MJOBOFFER JO

-- NOTE: previously LEFT JOINed a nonexistent MAPPLICANT table here (invalid object name at
-- runtime). No column from that join was ever selected (only JO.APPLICANTID, a direct column,
-- is projected above) — the join contributed nothing to the result set, so it is removed rather
-- than repointed at TAPPLICANT (PayRoll's applicant-tracking table, a different domain concept
-- per the Recruitment build-out plan — see JobOfferBLL.SaveJobOffer for the full rationale). A
-- real Candidate/Application join lands in a future phase once that entity exists.
LEFT JOIN MJOBPOSITION JP ON JP.JOBPOSITIONID = JO.JOBPOSITIONID
LEFT JOIN MDEPARTMENT  DP ON DP.DEPARTMENTID  = JO.DEPARTMENTID
LEFT JOIN MLOCATION    LC ON LC.LOCATIONID    = JO.LOCATIONID

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

WHERE JO.JOBOFFERID = @JobOfferId;";


        // ==============================
        // SAVE
        // ==============================
        public const string SAVE_JOBOFFER = @"
INSERT INTO MJOBOFFER (
    JOBOFFERID,
    APPLICANTID,
    APPLICATIONID,
    JOBPOSITIONID,
    DEPARTMENTID,
    LOCATIONID,
    EXPECTEDJOININGDATE,
    OFFEREXPIRYDATE,
    CTC,
    EMPLOYEEMENTTYPE,
    PROBATIONPERIOD,
    ISORDERGENERATED,
    OFFERRESPONSESTATUS,
    OFFERRESPONSEDATE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    TENANTID
)
VALUES (
    @JobOfferId,
    @ApplicantId,
    @ApplicationId,
    @JobPositionId,
    @DepartmentId,
    @LocationId,
    @ExpectedJoiningDate,
    @OfferExpiryDate,
    @CTC,
    @EmployeementType,
    @ProbationPeriod,
    @IsOrderGenerated,
    0,      -- OFFERRESPONSESTATUS: an offer always starts Pending (0), never candidate-supplied at creation
    NULL,   -- OFFERRESPONSEDATE: set only when the candidate actually responds (see UPDATE_JOBOFFER_RESPONSE)
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn,
    @SortOrder,
    @Status,
    @Version,
    @SourceType,
    @TenantId
);
";

        // ==============================
        // UPDATE
        // ==============================
        public const string UPDATE_JOBOFFER = @"
UPDATE MJOBOFFER
SET
    APPLICANTID         = @ApplicantId,
    APPLICATIONID      = @ApplicationId,
    JOBPOSITIONID      = @JobPositionId,
    DEPARTMENTID       = @DepartmentId,
    LOCATIONID         = @LocationId,
    EXPECTEDJOININGDATE = @ExpectedJoiningDate,
    OFFEREXPIRYDATE    = @OfferExpiryDate,
    CTC                = @CTC,
    EMPLOYEEMENTTYPE   = @EmployeementType,
    PROBATIONPERIOD    = @ProbationPeriod,
    ISORDERGENERATED   = @IsOrderGenerated,
    OFFERRESPONSESTATUS = @OfferResponseStatus,
    OFFERRESPONSEDATE   = @OfferResponseDate,
    MODIFIEDBYID       = @ModifiedById,
    MODIFIEDON         = @ModifiedOn,
    SORTORDER          = @SortOrder,
    STATUS             = @Status,
    VERSION            = @Version,
    SOURCETYPE         = @SourceType,
    TENANTID           = @TenantId
WHERE JOBOFFERID = @JobOfferId;
";

        // Dedicated, narrow follow-up UPDATE — fires from JobOfferBLL.RecordOfferResponseAsync
        // once the candidate accepts/declines, independent of the full JobOffer save transaction.
        // Deliberately not part of ExecuteSaveAsync (same non-transactional-follow-up pattern as
        // IApplicationDAL.UpdateApplicationCurrentStage / IInterviewDAL.UpdateInterviewEcpMeetingId).
        public const string UPDATE_JOBOFFER_RESPONSE = @"
UPDATE MJOBOFFER
SET
    OFFERRESPONSESTATUS = @OfferResponseStatus,
    OFFERRESPONSEDATE   = @OfferResponseDate
WHERE JOBOFFERID = @JobOfferId
AND   TENANTID   = @TenantId;
";

        // ==============================
        // DELETE
        // ==============================
        public const string DELETE_JOBOFFER = @"
DELETE FROM MJOBOFFER
WHERE JOBOFFERID = @JobOfferId;
";

        // ==============================
        // SELECT LIST
        // ==============================
        public const string GETSELECTLIST_JOBOFFER = @"
SELECT
    JO.JOBOFFERID      AS JobOfferId,
    JO.APPLICANTID    AS ApplicantId,
    JO.JOBPOSITIONID  AS JobPositionId,
    JO.OFFEREXPIRYDATE AS OfferExpiryDate,
    JO.STATUS         AS Status,
    JO.TENANTID       AS TenantId
FROM MJOBOFFER JO
WHERE JO.STATUS = 1
ORDER BY JO.OFFEREXPIRYDATE;
";
    }
}