namespace RecruitmentDAL.Query.Career
{
    // SQL for the public career-site surface (GB5Solution.Recruitment Phase 3). Every query here
    // hard-filters REQUISITIONSTATUS = 5 (Published) AND STATUS = 1 (Active) AND TENANTID in the
    // SQL itself — not merely in BLL after the fact — so a public, unauthenticated caller can
    // never enumerate Draft/PendingApproval/Approved/Rejected/OnHold/Closed requisitions or
    // cross-tenant data, even by guessing a JobRequisitionId.
    public static class CareerQB
    {
        // Public job list — GET /Career/GetPublishedJobs. Only fields appropriate for a public
        // listing are selected (see PublicJobPostingDTO's doc comment for what is deliberately
        // excluded). Paged via QueryPagedAsync with the SQL's own OFFSET/FETCH embedded — same
        // pattern as CandidateQB.GET_SELECTLIST_CANDIDATE (see that constant's comment):
        // IQueryExecutor.QueryPagedAsync(login, sql, parameters, transaction, useReadUncommitted)
        // has no separate count-sql parameter, so it derives TotalCount by wrapping this SQL
        // (OFFSET/FETCH included) in SELECT COUNT(*) FROM (...) — TotalCount therefore reflects
        // this page's row count, consistent with every other QueryPagedAsync consumer in this
        // module today (not a new bug introduced here).
        // Covering index note: existing IX_TJOBREQUISITION_STATUS_REQSTATUS (STATUS,
        // REQUISITIONSTATUS) + IX_TJOBREQUISITION_TENANTID already cover this filter.
        public const string GET_PUBLISHED_JOBS = @"
SELECT
    JR.JOBREQUISITIONID    AS JobRequisitionId,
    JR.REQUISITIONCODE     AS RequisitionCode,
    JP.POSITIONTITLE       AS PositionTitle,
    D.DEPARTMENTNAME       AS DepartmentName,
    JR.NOOFOPENINGS        AS NoOfOpenings,
    JR.REQUISITIONTYPE     AS RequisitionType,
    JR.CREATEDON           AS PostedOn
FROM TJOBREQUISITION JR
LEFT JOIN MJOBPosition JP ON JP.JOBPOSITIONID = JR.JOBPOSITIONID
LEFT JOIN MDEPARTMENT  D  ON D.DEPARTMENTID   = JR.DEPARTMENTID
WHERE JR.REQUISITIONSTATUS = 5
AND   JR.STATUS            = 1
AND   JR.TENANTID          = @TenantId
ORDER BY JR.CREATEDON DESC
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;
";

        // Single published job's public detail — GET /Career/GetPublishedJobDetail. Same
        // Published+Active+tenant filter as GET_PUBLISHED_JOBS, additionally scoped to one
        // JobRequisitionId. An unknown id and an id that exists but is not currently Published
        // both simply return zero rows (QuerySingleAsync -> default(T)/null) — deliberately
        // indistinguishable to an anonymous caller, so this can't be used to probe requisition
        // status by trying ids.
        public const string GET_PUBLISHED_JOB_DETAIL = @"
SELECT
    JR.JOBREQUISITIONID      AS JobRequisitionId,
    JR.REQUISITIONCODE       AS RequisitionCode,
    JP.POSITIONTITLE         AS PositionTitle,
    D.DEPARTMENTNAME         AS DepartmentName,
    JR.NOOFOPENINGS          AS NoOfOpenings,
    JR.REQUISITIONTYPE       AS RequisitionType,
    JR.CREATEDON             AS PostedOn,

    JP.EXPERIENCE            AS Experience,
    JP.OVERVIEW              AS Overview,
    JP.KEYRESPONSIBILITIES   AS KeyResponsibilities,
    JP.REQUIREDQUALIFICATION AS RequiredQualification,
    JP.TECHSKILLS            AS TechSkills,
    JP.EDUREQUIREMENTS       AS EduRequirements
FROM TJOBREQUISITION JR
LEFT JOIN MJOBPosition JP ON JP.JOBPOSITIONID = JR.JOBPOSITIONID
LEFT JOIN MDEPARTMENT  D  ON D.DEPARTMENTID   = JR.DEPARTMENTID
WHERE JR.JOBREQUISITIONID  = @JobRequisitionId
AND   JR.REQUISITIONSTATUS = 5
AND   JR.STATUS            = 1
AND   JR.TENANTID          = @TenantId;
";

        // Narrow, internal-only lookup consumed by the public apply flow
        // (RecruitmentBLL.Career.CareerBLL.SubmitApplication) to (a) confirm the target
        // requisition is genuinely Published+Active for this tenant before accepting an
        // application against it, and (b) resolve the SelectionProcessTemplateId needed to find
        // the pipeline's first stage (StageOrder = 1, via ISelectionProcessBLL). Returns only
        // these two columns — never exposed to a public response DTO.
        public const string GET_PUBLISHED_REQUISITION_FOR_APPLY = @"
SELECT
    JR.JOBREQUISITIONID           AS JobRequisitionId,
    JR.SELECTIONPROCESSTEMPLATEID AS SelectionProcessTemplateId
FROM TJOBREQUISITION JR
WHERE JR.JOBREQUISITIONID  = @JobRequisitionId
AND   JR.REQUISITIONSTATUS = 5
AND   JR.STATUS            = 1
AND   JR.TENANTID          = @TenantId;
";
    }
}