namespace TMSDAL.Query.Reports
{
    public static class ComplianceReportsQB
    {
        // One row per mandatory TTRAININGNEED: target headcount vs. actual completed headcount
        // (via TTRAININGINSTANCE.TRAININGNEEDID -> TTRAININGENROLLMENT -> TTRAININGCOMPLETION,
        // COMPLETIONSTATUS=0 Completed). OUTER APPLY avoids the JOIN fan-out that would otherwise
        // multiply-count TARGETHEADCOUNT once per enrollment row (same shape as
        // TmsKpiPostingQB.GET_COMPLIANCE_COVERAGE_PCT's own CROSS APPLY, but per-need here instead
        // of tenant-aggregate). Drill target: the enrollment register for TrainingNeedId.
        public const string GET_COMPLIANCE_COVERAGE_REGISTER = @"
SELECT
    TN.TRAININGNEEDID       AS TrainingNeedId,
    TN.NEEDCODE              AS NeedCode,
    TN.NEEDTITLE             AS NeedTitle,
    MD.DOMAINNAME            AS DomainName,
    TN.TARGETHEADCOUNT       AS TargetHeadcount,
    ISNULL(Completed.CompletedCount, 0) AS CompletedCount,
    CASE WHEN TN.TARGETHEADCOUNT > 0
         THEN CAST(ISNULL(Completed.CompletedCount, 0) * 100.0 / TN.TARGETHEADCOUNT AS DECIMAL(9,4))
         ELSE 0 END          AS CoveragePct,
    TN.COMPLIANCEDEADLINE    AS ComplianceDeadline,
    CASE WHEN TN.COMPLIANCEDEADLINE IS NOT NULL
              AND TN.COMPLIANCEDEADLINE < CAST(GETUTCDATE() AS DATE)
              AND ISNULL(Completed.CompletedCount, 0) < TN.TARGETHEADCOUNT
         THEN 1 ELSE 0 END   AS IsOverdue,
    TN.NEEDSTATUS            AS NeedStatus,
    TN.RAISEDON              AS RaisedOn
FROM DBO.TTRAININGNEED TN
LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = TN.TRAININGDOMAINID AND MD.TENANTID = TN.TENANTID
OUTER APPLY (
    SELECT COUNT(1) AS CompletedCount
    FROM   DBO.TTRAININGINSTANCE TI
    INNER JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID
    INNER JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.COMPLETIONSTATUS = 0
    WHERE  TI.TRAININGNEEDID = TN.TRAININGNEEDID
) Completed
WHERE TN.TENANTID = @TenantId
AND   TN.ISMANDATORY = 1
{DynamicFilter}";

        // GROUP BY domain -- same per-need coverage math, aggregated.
        public const string GET_COMPLIANCE_COVERAGE_SUMMARY = @"
SELECT
    MD.TRAININGDOMAINID     AS TrainingDomainId,
    MD.DOMAINNAME            AS DomainName,
    SUM(TN.TARGETHEADCOUNT)  AS TargetHeadcount,
    SUM(ISNULL(Completed.CompletedCount, 0)) AS CompletedCount,
    CASE WHEN SUM(TN.TARGETHEADCOUNT) > 0
         THEN CAST(SUM(ISNULL(Completed.CompletedCount, 0)) * 100.0 / SUM(TN.TARGETHEADCOUNT) AS DECIMAL(9,4))
         ELSE 0 END          AS CoveragePct
FROM DBO.TTRAININGNEED TN
LEFT JOIN DBO.MTRAININGDOMAIN MD ON MD.TRAININGDOMAINID = TN.TRAININGDOMAINID AND MD.TENANTID = TN.TENANTID
OUTER APPLY (
    SELECT COUNT(1) AS CompletedCount
    FROM   DBO.TTRAININGINSTANCE TI
    INNER JOIN DBO.TTRAININGENROLLMENT TE ON TE.INSTANCEID = TI.INSTANCEID
    INNER JOIN DBO.TTRAININGCOMPLETION TC ON TC.ENROLLMENTID = TE.ENROLLMENTID AND TC.COMPLETIONSTATUS = 0
    WHERE  TI.TRAININGNEEDID = TN.TRAININGNEEDID
) Completed
WHERE TN.TENANTID = @TenantId
AND   TN.ISMANDATORY = 1
{DynamicFilter}
GROUP BY MD.TRAININGDOMAINID, MD.DOMAINNAME";

        // Enrollments not yet HR-approved -- ENROLLMENTSTATUS 0 (Nominated, awaiting Manager) or 1
        // (ManagerApproved, awaiting HR) -- ageing since the last completed step, with the current
        // bottleneck owner. Enrollments that have moved past HR approval (status >= 2) are not a
        // pending-SLA concern and are excluded.
        public const string GET_NOMINATION_APPROVAL_SLA_REGISTER = @"
SELECT
    TE.ENROLLMENTID          AS EnrollmentId,
    TE.ENROLLMENTCODE        AS EnrollmentCode,
    TE.EMPLOYEEID            AS EmployeeId,
    TE.INSTANCEID            AS InstanceId,
    TE.NOMINATEDON           AS NominatedOn,
    TE.MANAGERAPPROVEDON     AS ManagerApprovedOn,
    TE.HRAPPROVEDON          AS HRApprovedOn,
    TE.ENROLLMENTSTATUS      AS EnrollmentStatus,
    CASE TE.ENROLLMENTSTATUS
        WHEN 0 THEN 'Manager'
        WHEN 1 THEN 'HR'
        ELSE 'None'
    END                       AS CurrentBottleneckOwner,
    CASE TE.ENROLLMENTSTATUS
        WHEN 0 THEN DATEDIFF(DAY, TE.NOMINATEDON, GETUTCDATE())
        WHEN 1 THEN DATEDIFF(DAY, TE.MANAGERAPPROVEDON, GETUTCDATE())
        ELSE 0
    END                       AS DaysInCurrentStep
FROM DBO.TTRAININGENROLLMENT TE
WHERE TE.TENANTID = @TenantId
AND   TE.ENROLLMENTSTATUS IN (0, 1)
{DynamicFilter}";
    }
}