namespace RecruitmentDAL.Query.Reports
{
    // Phase 5 analytics — cycle-time report SQL. See ApplicationCycleTimeDTO for the row grain.
    //
    // "Genuine Hired" signal (HireSignal correlated subquery) mirrors CandidateConversionBLL's own
    // structural definition exactly: the Application's CURRENT stage (TAPPLICATION.CURRENTSTAGEID)
    // must be Terminal (MSELECTIONPROCESSSTAGE.STAGECATEGORY=7 AND ISTERMINAL=1) AND that stage's
    // most recent TAPPLICATIONSTAGEHISTORY row (by StageEnteredOn DESC) must have OUTCOME=2
    // (Passed). TimeToFillDays is DATEDIFF from the first Applied-category (StageCategory=1)
    // stage's StageEnteredOn to that terminal stage's StageEnteredOn.
    //
    // Deliberately uses OUTER APPLY (correlated derived tables), never a CTE/WITH clause — a CTE
    // as the first token of the SQL text breaks IQueryExecutor.QueryPagedAsync's
    // "SELECT COUNT(*) FROM (<sql>) AS Total" wrap (WITH cannot appear inside a derived table
    // unless it is the very first token of the batch) — see this repo's own
    // VoucherDetail-report-pattern precedent for the same gotcha.
    //
    // Covering indexes required (per Phase1 migration + this report's access pattern):
    //   IX_TAPPLICATIONSTAGEHISTORY_APPID_STAGEID (APPLICATIONID, SELECTIONPROCESSSTAGEID)
    //     INCLUDE (STAGEENTEREDON, OUTCOME) — used by both the outer join and the two
    //     correlated HireSignal/AppliedStage subqueries below.
    //   IX_TAPPLICATION_TENANTID_JOBREQUISITIONID (TENANTID, JOBREQUISITIONID) INCLUDE (STATUS,
    //     CANDIDATEID, CURRENTSTAGEID) — covers the optional @JobRequisitionId filter.
    public static class ApplicationCycleTimeQB
    {
        // No ORDER BY / OFFSET — base projection shared by the streaming (export) and paged
        // (UI grid) variants below.
        public const string GET_APPLICATION_CYCLE_TIME_BASE = @"
SELECT
    H.APPLICATIONID              AS ApplicationId,
    A.CANDIDATEID                AS CandidateId,
    (C.FIRSTNAME + ' ' + C.LASTNAME) AS CandidateName,
    A.JOBREQUISITIONID           AS JobRequisitionId,
    JR.REQUISITIONCODE           AS RequisitionCode,

    H.SELECTIONPROCESSSTAGEID    AS SelectionProcessStageId,
    SPS.STAGENAME                AS StageName,
    SPS.STAGEORDER                AS StageOrder,
    SPS.STAGECATEGORY            AS StageCategory,

    H.STAGEENTEREDON             AS StageEnteredOn,
    H.STAGEEXITEDON              AS StageExitedOn,
    DATEDIFF(DAY, H.STAGEENTEREDON, ISNULL(H.STAGEEXITEDON, GETUTCDATE())) AS DaysInStage,

    HireSignal.IsHired           AS IsHired,
    HireSignal.TimeToFillDays    AS TimeToFillDays
FROM TAPPLICATIONSTAGEHISTORY H
INNER JOIN TAPPLICATION A               ON A.APPLICATIONID = H.APPLICATIONID AND A.TENANTID = H.TENANTID
INNER JOIN MCANDIDATE C                 ON C.CANDIDATEID = A.CANDIDATEID AND C.TENANTID = A.TENANTID
INNER JOIN TJOBREQUISITION JR           ON JR.JOBREQUISITIONID = A.JOBREQUISITIONID AND JR.TENANTID = A.TENANTID
INNER JOIN MSELECTIONPROCESSSTAGE SPS   ON SPS.SELECTIONPROCESSSTAGEID = H.SELECTIONPROCESSSTAGEID AND SPS.TENANTID = H.TENANTID
OUTER APPLY (
    SELECT
        CASE WHEN TermStage.STAGECATEGORY = 7 AND TermStage.ISTERMINAL = 1 AND TermHist.OUTCOME = 2
             THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END AS IsHired,
        CASE WHEN TermStage.STAGECATEGORY = 7 AND TermStage.ISTERMINAL = 1 AND TermHist.OUTCOME = 2
             THEN DATEDIFF(DAY, AppliedStage.STAGEENTEREDON, TermHist.STAGEENTEREDON)
             ELSE NULL END AS TimeToFillDays
    FROM MSELECTIONPROCESSSTAGE TermStage
    OUTER APPLY (
        SELECT TOP 1 H2.OUTCOME, H2.STAGEENTEREDON
        FROM TAPPLICATIONSTAGEHISTORY H2
        WHERE H2.APPLICATIONID = A.APPLICATIONID
        AND   H2.SELECTIONPROCESSSTAGEID = A.CURRENTSTAGEID
        AND   H2.TENANTID = A.TENANTID
        ORDER BY H2.STAGEENTEREDON DESC
    ) TermHist
    OUTER APPLY (
        SELECT TOP 1 H3.STAGEENTEREDON
        FROM TAPPLICATIONSTAGEHISTORY H3
        INNER JOIN MSELECTIONPROCESSSTAGE S3 ON S3.SELECTIONPROCESSSTAGEID = H3.SELECTIONPROCESSSTAGEID AND S3.TENANTID = H3.TENANTID
        WHERE H3.APPLICATIONID = A.APPLICATIONID
        AND   S3.STAGECATEGORY = 1
        ORDER BY H3.STAGEENTEREDON ASC
    ) AppliedStage
    WHERE TermStage.SELECTIONPROCESSSTAGEID = A.CURRENTSTAGEID
    AND   TermStage.TENANTID = A.TENANTID
) HireSignal
WHERE H.TENANTID = @TenantId
AND   A.STATUS   = 1
AND   (@JobRequisitionId IS NULL OR A.JOBREQUISITIONID = @JobRequisitionId)";

        // Streaming/export variant (ExecuteReportAsync via QueryStreamAsync) — full result set,
        // deterministically ordered, no row cap.
        public const string GET_APPLICATION_CYCLE_TIME =
            GET_APPLICATION_CYCLE_TIME_BASE + @"
ORDER BY H.APPLICATIONID, H.STAGEENTEREDON;";

        // Paged/UI-grid variant (ExecutePagedAsync via QueryPagedAsync) — same projection with
        // real SQL-level OFFSET/FETCH, matching CandidateQB.GET_SELECTLIST_CANDIDATE's pattern
        // (this report's row count can genuinely exceed a single page, unlike the small aggregate
        // reports below).
        public const string GET_APPLICATION_CYCLE_TIME_PAGED =
            GET_APPLICATION_CYCLE_TIME_BASE + @"
ORDER BY H.APPLICATIONID, H.STAGEENTEREDON
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";
    }
}