namespace QMSDAL.Query.PcsAnalytics
{
    public static class PcsAnalyticsQB
    {
        public const string GET_PCS_KPI = @"
            WITH CardCounts AS (
                SELECT
                    SUM(CASE WHEN OVERALLSTATUS = 1 THEN 1 ELSE 0 END) AS ActiveCards,
                    SUM(CASE WHEN OVERALLSTATUS = 2 THEN 1 ELSE 0 END) AS CompletedCards,
                    SUM(CASE WHEN OVERALLSTATUS = 0 THEN 1 ELSE 0 END) AS NotStarted,
                    SUM(CASE WHEN OVERALLSTATUS IN (3,4) THEN 1 ELSE 0 END) AS NcrCount
                FROM   TPCSCARD
                WHERE  OUID     = @OuId
                AND    PERIODID = @PeriodId
                AND    TENANTID = @TenantId
                AND    STATUS   <> 2
            ),
            AvgCycle AS (
                SELECT AVG(CAST(DATEDIFF(DAY, ACTUALSTART, ACTUALEND) AS FLOAT)) AS AvgCycleDays
                FROM   TPCSCARD
                WHERE  OVERALLSTATUS = 2
                AND    ACTUALSTART   IS NOT NULL
                AND    ACTUALEND     IS NOT NULL
                AND    OUID          = @OuId
                AND    PERIODID      = @PeriodId
                AND    TENANTID      = @TenantId
            ),
            OpenStageCount AS (
                SELECT COUNT(*) AS OpenStages
                FROM   TPCSCARDSTAGE s
                INNER  JOIN TPCSCARD c ON s.PCSCARDID = c.PCSCARDID
                WHERE  s.STAGESTATUS = 1
                AND    c.OUID        = @OuId
                AND    c.PERIODID    = @PeriodId
                AND    c.TENANTID    = @TenantId
            )
            SELECT
                cc.ActiveCards,
                cc.CompletedCards,
                cc.NotStarted,
                cc.NcrCount,
                ac.AvgCycleDays,
                os.OpenStages
            FROM   CardCounts cc
            CROSS  JOIN AvgCycle ac
            CROSS  JOIN OpenStageCount os";

        // INDEX: IX_TPCSCARDSTAGE_BOTTLENECK — STAGESTATUS=2, ACTUALSTART, ACTUALEND
        public const string GET_BOTTLENECK = @"
            SELECT
                s.STAGECODE                AS StageCode,
                s.STAGENAME                AS StageName,
                AVG(rd.ESTIMATEDMINS)      AS EstimatedDur,
                AVG(DATEDIFF(MINUTE, s.ACTUALSTART, s.ACTUALEND)) AS ActualDur,
                CASE
                    WHEN AVG(rd.ESTIMATEDMINS) > 0
                    THEN CAST(AVG(DATEDIFF(MINUTE, s.ACTUALSTART, s.ACTUALEND)) AS FLOAT)
                         / AVG(rd.ESTIMATEDMINS) * 100
                    ELSE 0
                END                        AS PctVariance
            FROM   TPCSCARDSTAGE s
            INNER  JOIN TPCSCARD c          ON s.PCSCARDID      = c.PCSCARDID
            INNER  JOIN MROUTINGDETAIL rd   ON s.ROUTINGDETAILID = rd.ROUTINGDETAILID
            WHERE  s.STAGESTATUS = 2
            AND    s.ACTUALSTART IS NOT NULL
            AND    s.ACTUALEND   IS NOT NULL
            AND    c.OUID        = @OuId
            AND    c.PERIODID    = @PeriodId
            AND    c.TENANTID    = @TenantId
            GROUP  BY s.STAGECODE, s.STAGENAME
            ORDER  BY PctVariance DESC";

        public const string GET_MATERIAL_TRACE = @"
            SELECT
                m.MATERIALNAME             AS MaterialName,
                m.GRADECODE                AS GradeCode,
                m.BATCHNO                  AS BatchNo,
                COUNT(DISTINCT c.PCSCARDID) AS CardCount,
                s.STAGENAME                AS StageName,
                m.DATEOFMANUFACTURE        AS DateOfManufacture,
                m.DATEOFEXPIRY             AS DateOfExpiry,
                MAX(m.ISEXPIRED)           AS IsExpired
            FROM   TPCSCARDMATERIAL m
            INNER  JOIN TPCSCARDSTAGE s ON m.PCSCARDSTAEID = s.PCSCARDSTAEID
            INNER  JOIN TPCSCARD c      ON s.PCSCARDID     = c.PCSCARDID
            WHERE  c.OUID     = @OuId
            AND    c.PERIODID = @PeriodId
            AND    c.TENANTID = @TenantId
            GROUP  BY m.MATERIALNAME, m.GRADECODE, m.BATCHNO, s.STAGENAME,
                      m.DATEOFMANUFACTURE, m.DATEOFEXPIRY
            ORDER  BY m.BATCHNO";
    }
}