namespace RecruitmentDAL.Query.Reports
{
    // Phase 5 analytics — pipeline-conversion (funnel) report SQL. See
    // PipelineConversionFunnelDTO for the row grain (one row per MSELECTIONPROCESSSTAGE, ordered
    // by StageOrder within its template).
    //
    // Same "small bounded aggregate, no real OFFSET/FETCH needed" shape as
    // CandidateSourceQualityQB — stage count per template is single-digit to low-tens, and with
    // the optional @SelectionProcessTemplateId filter applied it's a single template's stage list.
    //
    // Uses a derived table (StageCounts), never a CTE/WITH — see ApplicationCycleTimeQB's doc
    // comment for why a leading WITH clause breaks QueryPagedAsync's COUNT(*) wrap. LAG() is a
    // window function, not a CTE, and is applied on the OUTER select over the derived table so it
    // sees the complete per-stage group before any paging would be applied.
    //
    // ReachedCount = distinct (non-deleted) Applications with an ApplicationStageHistory row for
    // this stage — i.e. "reached or passed through" this stage, matching Phase 5's stated
    // "count of Applications currently at or having passed through each StageOrder" (a stage-
    // history row is written when a candidate enters a stage, so its mere existence for this
    // stage/application pair already means the candidate reached it, still-current or since
    // advanced past it).
    //
    // Covering indexes required:
    //   IX_MSELECTIONPROCESSSTAGE_TEMPLATEID_ORDER — already ships (SelectionProcessQB's own
    //     covering-index note), drives the @SelectionProcessTemplateId filter + StageOrder LAG().
    //   IX_TAPPLICATIONSTAGEHISTORY_STAGEID (SELECTIONPROCESSSTAGEID, TENANTID) INCLUDE
    //     (APPLICATIONID) — drives the ReachedCount join/aggregate.
    public static class PipelineConversionFunnelQB
    {
        public const string GET_PIPELINE_CONVERSION_FUNNEL = @"
SELECT
    StageCounts.SelectionProcessTemplateId,
    StageCounts.TemplateName,
    StageCounts.SelectionProcessStageId,
    StageCounts.StageName,
    StageCounts.StageOrder,
    StageCounts.StageCategory,
    StageCounts.ReachedCount,
    LAG(StageCounts.ReachedCount) OVER (
        PARTITION BY StageCounts.SelectionProcessTemplateId ORDER BY StageCounts.StageOrder
    ) AS PreviousStageReachedCount,
    CASE WHEN LAG(StageCounts.ReachedCount) OVER (
                  PARTITION BY StageCounts.SelectionProcessTemplateId ORDER BY StageCounts.StageOrder
              ) > 0
         THEN CAST(StageCounts.ReachedCount * 100.0 /
                    LAG(StageCounts.ReachedCount) OVER (
                        PARTITION BY StageCounts.SelectionProcessTemplateId ORDER BY StageCounts.StageOrder
                    ) AS DECIMAL(9,4))
         ELSE NULL END AS ConversionFromPreviousPct
FROM (
    SELECT
        SPS.SELECTIONPROCESSTEMPLATEID AS SelectionProcessTemplateId,
        SPT.TEMPLATENAME                AS TemplateName,
        SPS.SELECTIONPROCESSSTAGEID     AS SelectionProcessStageId,
        SPS.STAGENAME                   AS StageName,
        SPS.STAGEORDER                   AS StageOrder,
        SPS.STAGECATEGORY               AS StageCategory,
        COUNT(DISTINCT CASE WHEN A.APPLICATIONID IS NOT NULL THEN H.APPLICATIONID END) AS ReachedCount
    FROM MSELECTIONPROCESSSTAGE SPS
    INNER JOIN MSELECTIONPROCESSTEMPLATE SPT ON SPT.SELECTIONPROCESSTEMPLATEID = SPS.SELECTIONPROCESSTEMPLATEID AND SPT.TENANTID = SPS.TENANTID
    LEFT JOIN TAPPLICATIONSTAGEHISTORY H     ON H.SELECTIONPROCESSSTAGEID = SPS.SELECTIONPROCESSSTAGEID AND H.TENANTID = SPS.TENANTID
    LEFT JOIN TAPPLICATION A                 ON A.APPLICATIONID = H.APPLICATIONID AND A.TENANTID = H.TENANTID AND A.STATUS = 1
    WHERE SPS.TENANTID = @TenantId
    AND   SPS.STATUS   = 1
    AND   (@SelectionProcessTemplateId IS NULL OR SPS.SELECTIONPROCESSTEMPLATEID = @SelectionProcessTemplateId)
    GROUP BY SPS.SELECTIONPROCESSTEMPLATEID, SPT.TEMPLATENAME, SPS.SELECTIONPROCESSSTAGEID,
             SPS.STAGENAME, SPS.STAGEORDER, SPS.STAGECATEGORY
) StageCounts
ORDER BY StageCounts.SelectionProcessTemplateId, StageCounts.StageOrder;";
    }
}