namespace RecruitmentDAL.Query.Reports
{
    // Phase 5 analytics — DEI/diversity sourcing report SQL. Grain: one row per
    // (DemographicDimension, DemographicValue, Source) group — five UNION ALL blocks, one per
    // MCANDIDATEDEMOGRAPHIC breakdown dimension (GenderIdentity, EthnicityRace, DisabilityStatus,
    // VeteranStatus, AgeBand), each crossed with Candidate.Source, with Applied/Interviewed/Hired
    // pipeline-outcome counts per group.
    //
    // CONSENTTOCOLLECT = 1 is mandatory in every branch's WHERE clause — this is the aggregation
    // boundary: a candidate who declined (or was never asked) contributes NOTHING to this report,
    // not even to an "Unknown"/anonymous bucket. See MCANDIDATEDEMOGRAPHIC's migration header and
    // CandidateDemographicDTO's doc comment for the full isolation rationale this report relies on.
    //
    // Mandatory cell-suppression (never show an exact count below 5) is enforced in
    // DeiSourcingReportBLL.ApplyCellSuppression, AFTER this SQL runs — not in this SQL. This SQL
    // returns raw, un-suppressed counts (DeiSourcingReportRawDTO) deliberately, so the suppression
    // rule lives in exactly one place (application code) rather than being duplicated per branch
    // here and risking drift.
    //
    // "Genuine Hired"/"reached Interview" signals — identical structural definition reused from
    // CandidateSourceQualityQB/ApplicationCycleTimeQB (both already established by the parallel
    // Phase 5 report work in this module): CandidateConversionBLL's own Terminal+IsTerminal=1+
    // Outcome=Passed(2) rule for Hired; any stage-history row against a StageCategory=3 stage for
    // Interviewed.
    //
    // Covering indexes required:
    //   UQ_MCANDIDATEDEMOGRAPHIC_CANDIDATEID / IX_MCANDIDATEDEMOGRAPHIC_TENANTID (ships with the
    //     schema migration) — drives the base MCANDIDATEDEMOGRAPHIC scan+filter.
    //   IX_MCANDIDATE_TENANTID_SOURCE (TENANTID, SOURCE) — drives the Source cross/GROUP BY.
    //   IX_TAPPLICATION_CANDIDATEID (CANDIDATEID) INCLUDE (TENANTID, STATUS, CURRENTSTAGEID).
    //   IX_TAPPLICATIONSTAGEHISTORY_APPID_STAGEID — reused by the InterviewApp/HiredApp
    //     correlated subqueries, same index as CandidateSourceQualityQB/ApplicationCycleTimeQB.
    public static class DeiSourcingReportQB
    {
        private const string PIPELINE_JOINS = @"
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";

        private const string SOURCE_NAME_CASE = @"
    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";

        public const string GET_DEI_SOURCING_REPORT = @"
SELECT
    'GenderIdentity' AS DemographicDimension,
    ISNULL(NULLIF(LTRIM(RTRIM(MD.GENDERIDENTITY)), ''), 'Not Specified') AS DemographicValue,
    C.SOURCE AS Source," + SOURCE_NAME_CASE + @" AS SourceName,
    COUNT(DISTINCT MD.CANDIDATEID)                             AS CandidateCount,
    COUNT(DISTINCT A.APPLICATIONID)                            AS AppliedCount,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                 AS InterviewedCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                     AS HiredCount
FROM MCANDIDATEDEMOGRAPHIC MD
INNER JOIN MCANDIDATE C ON C.CANDIDATEID = MD.CANDIDATEID AND C.TENANTID = MD.TENANTID" + PIPELINE_JOINS + @"
WHERE MD.CONSENTTOCOLLECT = 1
AND   MD.STATUS   = 1
AND   C.STATUS    = 1
AND   MD.TENANTID = @TenantId
GROUP BY MD.GENDERIDENTITY, C.SOURCE

UNION ALL

SELECT
    'EthnicityRace' AS DemographicDimension,
    ISNULL(NULLIF(LTRIM(RTRIM(MD.ETHNICITYRACE)), ''), 'Not Specified') AS DemographicValue,
    C.SOURCE AS Source," + SOURCE_NAME_CASE + @" AS SourceName,
    COUNT(DISTINCT MD.CANDIDATEID)                             AS CandidateCount,
    COUNT(DISTINCT A.APPLICATIONID)                            AS AppliedCount,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                 AS InterviewedCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                     AS HiredCount
FROM MCANDIDATEDEMOGRAPHIC MD
INNER JOIN MCANDIDATE C ON C.CANDIDATEID = MD.CANDIDATEID AND C.TENANTID = MD.TENANTID" + PIPELINE_JOINS + @"
WHERE MD.CONSENTTOCOLLECT = 1
AND   MD.STATUS   = 1
AND   C.STATUS    = 1
AND   MD.TENANTID = @TenantId
GROUP BY MD.ETHNICITYRACE, C.SOURCE

UNION ALL

SELECT
    'DisabilityStatus' AS DemographicDimension,
    CASE WHEN MD.DISABILITYSTATUS IS NULL THEN 'Not Specified'
         WHEN MD.DISABILITYSTATUS = 0 THEN 'Prefer Not To Say'
         ELSE CAST(MD.DISABILITYSTATUS AS VARCHAR(3)) END AS DemographicValue,
    C.SOURCE AS Source," + SOURCE_NAME_CASE + @" AS SourceName,
    COUNT(DISTINCT MD.CANDIDATEID)                             AS CandidateCount,
    COUNT(DISTINCT A.APPLICATIONID)                            AS AppliedCount,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                 AS InterviewedCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                     AS HiredCount
FROM MCANDIDATEDEMOGRAPHIC MD
INNER JOIN MCANDIDATE C ON C.CANDIDATEID = MD.CANDIDATEID AND C.TENANTID = MD.TENANTID" + PIPELINE_JOINS + @"
WHERE MD.CONSENTTOCOLLECT = 1
AND   MD.STATUS   = 1
AND   C.STATUS    = 1
AND   MD.TENANTID = @TenantId
GROUP BY MD.DISABILITYSTATUS, C.SOURCE

UNION ALL

SELECT
    'VeteranStatus' AS DemographicDimension,
    CASE WHEN MD.VETERANSTATUS IS NULL THEN 'Not Specified'
         WHEN MD.VETERANSTATUS = 0 THEN 'Prefer Not To Say'
         ELSE CAST(MD.VETERANSTATUS AS VARCHAR(3)) END AS DemographicValue,
    C.SOURCE AS Source," + SOURCE_NAME_CASE + @" AS SourceName,
    COUNT(DISTINCT MD.CANDIDATEID)                             AS CandidateCount,
    COUNT(DISTINCT A.APPLICATIONID)                            AS AppliedCount,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                 AS InterviewedCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                     AS HiredCount
FROM MCANDIDATEDEMOGRAPHIC MD
INNER JOIN MCANDIDATE C ON C.CANDIDATEID = MD.CANDIDATEID AND C.TENANTID = MD.TENANTID" + PIPELINE_JOINS + @"
WHERE MD.CONSENTTOCOLLECT = 1
AND   MD.STATUS   = 1
AND   C.STATUS    = 1
AND   MD.TENANTID = @TenantId
GROUP BY MD.VETERANSTATUS, C.SOURCE

UNION ALL

SELECT
    'AgeBand' AS DemographicDimension,
    CASE WHEN MD.AGEBAND IS NULL THEN 'Not Specified'
         WHEN MD.AGEBAND = 0 THEN 'Prefer Not To Say'
         ELSE CAST(MD.AGEBAND AS VARCHAR(3)) END AS DemographicValue,
    C.SOURCE AS Source," + SOURCE_NAME_CASE + @" AS SourceName,
    COUNT(DISTINCT MD.CANDIDATEID)                             AS CandidateCount,
    COUNT(DISTINCT A.APPLICATIONID)                            AS AppliedCount,
    COUNT(DISTINCT InterviewApp.APPLICATIONID)                 AS InterviewedCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                     AS HiredCount
FROM MCANDIDATEDEMOGRAPHIC MD
INNER JOIN MCANDIDATE C ON C.CANDIDATEID = MD.CANDIDATEID AND C.TENANTID = MD.TENANTID" + PIPELINE_JOINS + @"
WHERE MD.CONSENTTOCOLLECT = 1
AND   MD.STATUS   = 1
AND   C.STATUS    = 1
AND   MD.TENANTID = @TenantId
GROUP BY MD.AGEBAND, C.SOURCE

ORDER BY DemographicDimension, DemographicValue, Source;
";
    }
}