namespace RecruitmentDAL.Reports
{
    public static class RecruiterWorkloadReportQB
    {
        // Covering index note: TJOBREQUISITION should carry
        // IX_TJOBREQUISITION_ASSIGNEDRECRUITERID (added by
        // 20260904_Recruitment_Phase5_Analytics_Schema_SqlServer.sql) to keep the GROUP BY below
        // index-covered as row volume grows; TAPPLICATION should carry
        // IX_TAPPLICATION_JOBREQUISITIONID_STAGE (already ships with the Phase1 migration) for the
        // OUTER APPLY.
        //
        // Deliberately no leading CTE (";WITH ...") — IQueryExecutor.QueryPagedAsync wraps this
        // exact SQL as "SELECT COUNT(*) FROM (<sql>) AS Total" to derive TotalCount, and a leading
        // CTE cannot be nested inside a derived table (fails with HTTP 200 + empty result set, the
        // real syntax error buried in the response body's GeneralErrors — see
        // feedback_voucherdetail_report_patterns lesson 8). Per-requisition pending-application
        // count is computed via OUTER APPLY instead (valid inside a derived table) and summed per
        // recruiter in the outer GROUP BY.
        //
        // {ClosedDateFilter} is appended directly after "JR.REQUISITIONSTATUS = 7" inside the CASE
        // WHEN below (RecruitmentReportFilterBuilder.AppendDateRange's " AND ..." fragment is valid
        // straight after a boolean predicate, not just after a WHERE clause).
        public const string GET_RECRUITER_WORKLOAD = @"
SELECT
    JR.ASSIGNEDRECRUITERID                          AS RecruiterId,
    COALESCE(MU.USERNAME, 'Unassigned')             AS RecruiterName,
    SUM(CASE WHEN JR.REQUISITIONSTATUS IN (1,2,3,5,6) THEN 1 ELSE 0 END) AS OpenRequisitionCount,
    SUM(ISNULL(Pending.PendingCount, 0))            AS PendingApplicationCount,
    SUM(CASE WHEN JR.REQUISITIONSTATUS = 7{ClosedDateFilter} THEN 1 ELSE 0 END) AS ClosedRequisitionCount
FROM TJOBREQUISITION JR
LEFT JOIN MUSER MU ON MU.USERID = JR.ASSIGNEDRECRUITERID
OUTER APPLY (
    SELECT COUNT(1) AS PendingCount
    FROM   TAPPLICATION A
    INNER JOIN MSELECTIONPROCESSSTAGE SPS ON SPS.SELECTIONPROCESSSTAGEID = A.CURRENTSTAGEID
                                          AND SPS.TENANTID = @TenantId
    WHERE  A.JOBREQUISITIONID = JR.JOBREQUISITIONID
    AND    A.TENANTID         = @TenantId
    AND    A.STATUS           = 1
    AND    NOT (SPS.STAGECATEGORY = 7 AND SPS.ISTERMINAL = 1)
) Pending
WHERE JR.TENANTID = @TenantId
AND   JR.STATUS   = 1
{AssignedRecruiterFilter}
GROUP BY JR.ASSIGNEDRECRUITERID, MU.USERNAME
ORDER BY RecruiterName;
";
    }
}