namespace WiDAL.Query.Wi;

public static class WiAnalyticsQB
{
    // Index used: TWIEXECUTION on (TENANTID, STARTEDON) INCLUDE (WIID, WORKSTATIONID, EMPLOYEEID, EXECSTATUS)
    public const string GET_EXECUTION_HISTORY = @"
        SELECT  e.WIEXECUTIONID           AS WiExecutionId,
                e.WIID                    AS WiId,
                w.WICODE                  AS WiCode,
                w.WITITLE                 AS WiTitle,
                e.WORKSTATIONID           AS WorkstationId,
                ws.WORKSTATIONCODE        AS WorkstationCode,
                e.EMPLOYEEID              AS EmployeeId,
                emp.EMPLOYEENAME          AS EmployeeName,
                e.STARTEDON               AS StartedOn,
                e.COMPLETEDON             AS CompletedOn,
                e.EXECSTATUS              AS ExecStatus,
                e.TOTALACTUALSECS         AS TotalActualSecs,
                e.STDCYCLESECS            AS StdCycleSecs,
                e.FLAGREASON              AS FlagReason
        FROM    WI.TWIEXECUTION e
        JOIN    WI.MWI w             ON w.WIID            = e.WIID
        LEFT JOIN DBO.MWORKSTATION ws ON ws.WORKSTATIONID = e.WORKSTATIONID
        LEFT JOIN DBO.MEMPLOYEE emp   ON emp.EMPLOYEEID   = e.EMPLOYEEID
        WHERE   e.TENANTID = @TenantId
          AND   (@WiId = -1 OR e.WIID = @WiId)
          AND   (@WorkstationId = -1 OR e.WORKSTATIONID = @WorkstationId)
          AND   (@EmployeeId = -1 OR e.EMPLOYEEID = @EmployeeId)
          AND   e.STARTEDON BETWEEN @FromDate AND @ToDate
        ORDER BY e.STARTEDON DESC";

    // Index used: TWISTEPEXECUTION on (WISTEPID) INCLUDE (WIEXECUTIONID, ACTUALDURATIONSECS, STEPSTATUS)
    public const string GET_TIME_STUDY = @"
        SELECT  se.WISTEPID                      AS WiStepId,
                s.SLNO                           AS SlNo,
                s.STEPTITLE                      AS StepTitle,
                s.STDDURATIONSECS                AS StdDurationSecs,
                COUNT(se.WISTEPEXECUTIONID)      AS ExecutionCount,
                AVG(se.ACTUALDURATIONSECS)       AS AvgActualSecs,
                MIN(se.ACTUALDURATIONSECS)       AS MinActualSecs,
                MAX(se.ACTUALDURATIONSECS)       AS MaxActualSecs
        FROM    WI.TWISTEPEXECUTION se
        JOIN    WI.TWIEXECUTION e  ON e.WIEXECUTIONID = se.WIEXECUTIONID
        JOIN    WI.MWISTEP s       ON s.WISTEPID      = se.WISTEPID
        WHERE   e.WIID        = @WiId
          AND   e.TENANTID    = @TenantId
          AND   e.STARTEDON   BETWEEN @FromDate AND @ToDate
          AND   se.STEPSTATUS = 2
        GROUP BY se.WISTEPID, s.SLNO, s.STEPTITLE, s.STDDURATIONSECS
        ORDER BY s.SLNO";

    public const string GET_COMPLIANCE_SUMMARY = @"
        SELECT  e.WIID                                                          AS WiId,
                w.WICODE                                                        AS WiCode,
                w.WITITLE                                                       AS WiTitle,
                COUNT(DISTINCT e.EMPLOYEEID)                                   AS TotalOperators,
                COUNT(DISTINCT a.EMPLOYEEID)                                   AS AcknowledgedCount,
                CAST(COUNT(DISTINCT a.EMPLOYEEID) AS DECIMAL(10,2))
                    / NULLIF(COUNT(DISTINCT e.EMPLOYEEID), 0)                  AS ComplianceRate,
                SUM(CASE WHEN e.EXECSTATUS = 2 THEN 1 ELSE 0 END)             AS CompletedRuns,
                SUM(CASE WHEN e.EXECSTATUS = 3 THEN 1 ELSE 0 END)             AS AbandonedRuns,
                SUM(CASE WHEN e.EXECSTATUS = 4 THEN 1 ELSE 0 END)             AS FlaggedRuns
        FROM    WI.TWIEXECUTION e
        JOIN    WI.MWI w ON w.WIID = e.WIID
        LEFT JOIN WI.TWIACKNOWLEDGEMENT a
                ON  a.WIID       = e.WIID
                AND a.EMPLOYEEID = e.EMPLOYEEID
                AND a.ISVALID    = 1
        WHERE   e.TENANTID = @TenantId
          AND   (@WiId = -1 OR e.WIID = @WiId)
          AND   e.STARTEDON BETWEEN @FromDate AND @ToDate
        GROUP BY e.WIID, w.WICODE, w.WITITLE
        ORDER BY w.WICODE";
}