namespace RecruitmentDAL.Reports
{
    public static class HrRecruitmentAnalyticsReportQB
    {
        // Same no-leading-CTE constraint as RecruiterWorkloadReportQB (see that file's doc
        // comment / feedback_voucherdetail_report_patterns lesson 8) — the two org-wide aggregate
        // figures (PlannedHeadcount, Hires.ActualHires/AvgCostPerHire) are each computed by a
        // single non-correlated derived-table CROSS JOIN instead of a CTE, so they are still valid
        // nested inside QueryPagedAsync's "SELECT COUNT(*) FROM (<sql>) AS Total" wrap.
        //
        // {HiredDateFilter} scopes "hires in period" by the terminal stage-history row's
        // STAGEENTEREDON (the date the application entered its Hired/Terminal stage) — appended
        // inside the Hires derived table's WHERE clause below.
        public const string GET_HR_RECRUITMENT_ANALYTICS = @"
SELECT
    JR.REQUISITIONTYPE      AS RequisitionType,
    JR.REQUISITIONSTATUS    AS RequisitionStatus,
    COUNT(1)                AS RequisitionCount,
    PlannedHC.PlannedHeadcount AS PlannedHeadcount,
    Hires.ActualHires          AS ActualHires,
    Hires.AvgCostPerHire       AS AvgCostPerHire
FROM TJOBREQUISITION JR
CROSS JOIN (
    SELECT ISNULL(SUM(JR2.NOOFOPENINGS), 0) AS PlannedHeadcount
    FROM   TJOBREQUISITION JR2
    WHERE  JR2.TENANTID          = @TenantId
    AND    JR2.STATUS            = 1
    AND    JR2.REQUISITIONSTATUS IN (3, 5) -- Approved, Published
) PlannedHC
CROSS JOIN (
    SELECT
        COUNT(DISTINCT H.APPLICATIONID) AS ActualHires,
        AVG(JO.CTC)                     AS AvgCostPerHire
    FROM   TAPPLICATIONSTAGEHISTORY H
    INNER JOIN MSELECTIONPROCESSSTAGE SPS ON SPS.SELECTIONPROCESSSTAGEID = H.SELECTIONPROCESSSTAGEID
                                          AND SPS.TENANTID = @TenantId
    LEFT JOIN  MJOBOFFER JO ON JO.APPLICATIONID = H.APPLICATIONID
                            AND JO.TENANTID = @TenantId
                            AND JO.OFFERRESPONSESTATUS = 1 -- Accepted
    WHERE  H.TENANTID      = @TenantId
    AND    H.STATUS        = 1
    AND    H.OUTCOME       = 2 -- Passed
    AND    SPS.STAGECATEGORY = 7 -- Terminal
    AND    SPS.ISTERMINAL    = 1
    {HiredDateFilter}
) Hires
WHERE JR.TENANTID = @TenantId
AND   JR.STATUS   = 1
GROUP BY JR.REQUISITIONTYPE, JR.REQUISITIONSTATUS, PlannedHC.PlannedHeadcount, Hires.ActualHires, Hires.AvgCostPerHire
ORDER BY JR.REQUISITIONTYPE, JR.REQUISITIONSTATUS;
";
    }
}