namespace RecruitmentDAL.Query.Reports
{
    // Phase 5 analytics — source-quality report SQL. See CandidateSourceQualityDTO for the row
    // grain (one row per Candidate.Source, at most 6 rows per tenant).
    //
    // No real SQL-level OFFSET/FETCH pagination — same "small bounded aggregate" pattern as
    // TMSDAL.Query.Reports.ComplianceReportsQB.GET_COMPLIANCE_COVERAGE_SUMMARY (grouped by
    // TrainingDomainId, also a handful of rows): IQueryExecutor.QueryPagedAsync still drives
    // ExecutePagedAsync (per CLAUDE.md's "every report/list endpoint uses QueryPagedAsync/
    // StreamAsync" rule), it just always returns every group row within one page since the group
    // cardinality can never exceed a handful of rows.
    //
    // "Genuine Hired" signal — see ApplicationCycleTimeQB's doc comment for the exact structural
    // definition this mirrors (CandidateConversionBLL: current stage Terminal + IsTerminal=1 +
    // most recent stage-history row Outcome=Passed).
    //
    // Covering indexes required:
    //   IX_MCANDIDATE_TENANTID_SOURCE (TENANTID, SOURCE) INCLUDE (STATUS) — drives the GROUP BY.
    //   IX_TAPPLICATION_CANDIDATEID (CANDIDATEID) INCLUDE (TENANTID, STATUS, CURRENTSTAGEID) —
    //     already ships per ApplicationQB's own covering-index note.
    //   IX_TAPPLICATIONSTAGEHISTORY_APPID_STAGEID — same index as ApplicationCycleTimeQB, reused
    //     here by the InterviewApp/HiredApp correlated subqueries.
    public static class CandidateSourceQualityQB
    {
        public const string GET_CANDIDATE_SOURCE_QUALITY = @"
SELECT
    C.SOURCE AS Source,
    CASE C.SOURCE
        WHEN 1 THEN 'Website'
        WHEN 2 THEN 'JobPortal'
        WHEN 3 THEN 'Agency'
        WHEN 4 THEN 'Referral'
        WHEN 5 THEN 'Internal'
        WHEN 6 THEN 'Direct/Manual'
        ELSE 'Unknown'
    END AS SourceName,
    COUNT(DISTINCT C.CANDIDATEID)                                        AS TotalCandidates,
    COUNT(DISTINCT A.APPLICATIONID)                                      AS TotalApplications,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                           AS ReachedInterviewCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                               AS HiredCount,
    CASE WHEN COUNT(DISTINCT A.APPLICATIONID) > 0
         THEN CAST(COUNT(DISTINCT HiredApp.APPLICATIONID) * 100.0 / COUNT(DISTINCT A.APPLICATIONID) AS DECIMAL(9,4))
         ELSE 0 END                                                      AS ConversionRatePct
FROM MCANDIDATE C
LEFT JOIN TAPPLICATION A ON A.CANDIDATEID = C.CANDIDATEID AND A.TENANTID = C.TENANTID AND A.STATUS = 1
OUTER APPLY (
    SELECT TOP 1 H.APPLICATIONID
    FROM TAPPLICATIONSTAGEHISTORY H
    INNER JOIN MSELECTIONPROCESSSTAGE S ON S.SELECTIONPROCESSSTAGEID = H.SELECTIONPROCESSSTAGEID AND S.TENANTID = H.TENANTID
    WHERE H.APPLICATIONID = A.APPLICATIONID
    AND   H.TENANTID = A.TENANTID
    AND   S.STAGECATEGORY = 3
) InterviewApp
OUTER APPLY (
    SELECT TOP 1 A.APPLICATIONID
    FROM MSELECTIONPROCESSSTAGE TermStage
    OUTER APPLY (
        SELECT TOP 1 H2.OUTCOME
        FROM TAPPLICATIONSTAGEHISTORY H2
        WHERE H2.APPLICATIONID = A.APPLICATIONID
        AND   H2.SELECTIONPROCESSSTAGEID = A.CURRENTSTAGEID
        AND   H2.TENANTID = A.TENANTID
        ORDER BY H2.STAGEENTEREDON DESC
    ) TermHist
    WHERE TermStage.SELECTIONPROCESSSTAGEID = A.CURRENTSTAGEID
    AND   TermStage.TENANTID = A.TENANTID
    AND   TermStage.STAGECATEGORY = 7
    AND   TermStage.ISTERMINAL = 1
    AND   TermHist.OUTCOME = 2
) HiredApp
WHERE C.TENANTID = @TenantId
AND   C.STATUS   = 1
GROUP BY C.SOURCE
ORDER BY C.SOURCE;";
    }
}