namespace RecruitmentDAL.Query.Reports
{
    // Phase 6 analytics — job-ad spend performance report SQL. See JobAdSpendPerformanceDTO for
    // the row grain and for why this is a NEW, separate report rather than an extension of
    // CandidateSourceQualityBLL/DAL (whose existing public method signatures are untouched by
    // this migration).
    //
    // Deliberately uses OUTER APPLY (correlated derived tables), never a CTE/WITH clause — same
    // "CTE breaks IQueryExecutor.QueryPagedAsync's COUNT wrap" gotcha documented on
    // ApplicationCycleTimeQB. The HiredApp OUTER APPLY mirrors CandidateSourceQualityQB's
    // HiredApp block exactly (same "genuine Hired" structural definition).
    //
    // Covering indexes required:
    //   IX_TJOBADSPEND_JOBREQUISITIONID (JOBREQUISITIONID) INCLUDE (CHANNELTYPE, JOBBOARDNAME,
    //     SPENDAMOUNT, STATUS) — already ships with the Phase 6 JobAdSpend migration.
    //   IX_TAPPLICATION_JOBREQUISITIONID_STAGE (JOBREQUISITIONID, CURRENTSTAGEID) — already ships
    //     with the Phase 1 CoreATS migration; drives the ApplicationSource-matched join + the
    //     HiredApp correlated subquery's A.CURRENTSTAGEID lookup.
    //   IX_TAPPLICATIONSTAGEHISTORY_APPID_STAGEID — same index reused by CandidateSourceQualityQB
    //     and ApplicationCycleTimeQB's own Hired-signal subqueries.
    public static class JobAdSpendPerformanceQB
    {
        // No ORDER BY / OFFSET — base projection shared by the streaming (export) and paged
        // (UI grid) variants below. @JobRequisitionId is an optional filter (NULL = every
        // requisition for the tenant).
        public const string GET_JOBADSPEND_PERFORMANCE_BASE = @"
SELECT
    JS.JOBREQUISITIONID       AS JobRequisitionId,
    JR.REQUISITIONCODE        AS RequisitionCode,
    JS.CHANNELTYPE            AS ChannelType,
    CASE JS.CHANNELTYPE
        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 ChannelName,
    JS.JOBBOARDNAME           AS JobBoardName,
    JS.CURRENCYCODE           AS CurrencyCode,
    SUM(JS.SPENDAMOUNT)                                                          AS TotalSpend,
    COUNT(DISTINCT A.APPLICATIONID)                                              AS ApplicationCount,
    COUNT(DISTINCT HiredApp.APPLICATIONID)                                       AS HiredCount,
    CASE WHEN COUNT(DISTINCT A.APPLICATIONID) > 0
         THEN CAST(SUM(JS.SPENDAMOUNT) / COUNT(DISTINCT A.APPLICATIONID) AS DECIMAL(18,2))
         ELSE 0 END                                                              AS CostPerApplication,
    CASE WHEN COUNT(DISTINCT HiredApp.APPLICATIONID) > 0
         THEN CAST(SUM(JS.SPENDAMOUNT) / COUNT(DISTINCT HiredApp.APPLICATIONID) AS DECIMAL(18,2))
         ELSE 0 END                                                              AS CostPerHire
FROM TJOBADSPEND JS
INNER JOIN TJOBREQUISITION JR ON JR.JOBREQUISITIONID = JS.JOBREQUISITIONID AND JR.TENANTID = JS.TENANTID
LEFT JOIN TAPPLICATION A ON A.JOBREQUISITIONID = JS.JOBREQUISITIONID
                         AND A.APPLICATIONSOURCE = JS.CHANNELTYPE
                         AND A.TENANTID = JS.TENANTID
                         AND A.STATUS = 1
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 JS.TENANTID = @TenantId
AND   JS.STATUS   = 1
AND   (@JobRequisitionId IS NULL OR JS.JOBREQUISITIONID = @JobRequisitionId)
GROUP BY JS.JOBREQUISITIONID, JR.REQUISITIONCODE, JS.CHANNELTYPE, JS.JOBBOARDNAME, JS.CURRENCYCODE";

        // Streaming/export variant (ExecuteReportAsync via QueryStreamAsync) — full result set,
        // deterministically ordered, no row cap.
        public const string GET_JOBADSPEND_PERFORMANCE =
            GET_JOBADSPEND_PERFORMANCE_BASE + @"
ORDER BY JS.JOBREQUISITIONID, JS.JOBBOARDNAME;";

        // Paged/UI-grid variant (ExecutePagedAsync via QueryPagedAsync) — same projection with
        // real SQL-level OFFSET/FETCH, matching ApplicationCycleTimeQB's pattern (this report's
        // row count can genuinely exceed a single page across many requisitions/boards, unlike
        // CandidateSourceQualityQB's bounded 6-row aggregate).
        public const string GET_JOBADSPEND_PERFORMANCE_PAGED =
            GET_JOBADSPEND_PERFORMANCE_BASE + @"
ORDER BY JS.JOBREQUISITIONID, JS.JOBBOARDNAME
OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;";
    }
}