namespace FrameworkDAL.Query.Playground
{
    /// <summary>
    /// SQL constants for Playground monitoring queries.
    /// Required index: TEVENTACTIONRUN (PLAYGROUNDRUNID, TENANTID) — see Playground_AddPlaygroundRunId.sql
    /// </summary>
    public static class PlaygroundQB
    {
        // =====================================================================
        // Load MACTION rows linked to an EventType via MEVENTTYPEACTION
        // Used by TriggerEvent (Live mode) to know which actions to queue
        // =====================================================================
        public const string GET_ACTIONS_BY_EVENT_TYPE = @"
SELECT
    a.ACTIONID           AS ActionId,
    a.ACTIONTYPE         AS ActionType,
    a.SENDTO             AS SendTo,
    a.REPLYTO            AS ReplyTo,
    a.TEMPLATEID         AS TemplateId,
    a.MAILCC             AS MailCc,
    a.MAILBCC            AS MailBcc,
    a.WEBSERVICEID       AS WebServiceId,
    a.URIPARAMETERVALUE  AS UriParameterValue,
    a.REPORTID           AS ReportId
FROM   MEVENTTYPEACTION       eta
JOIN   MEVENTTYPEACTIONDETAIL etad ON etad.EVENTTYPEACTIONID = eta.EVENTTYPEACTIONID
JOIN   MACTION                a    ON a.ACTIONID             = etad.ACTIONID
WHERE  eta.EVENTTYPEID  = @EventTypeId
AND    eta.TENANTID     = @TenantId
AND    a.STATUS         = 1
AND    etad.ISACTIVE    = 1
ORDER  BY etad.SLNO
";

        // =====================================================================
        // Aggregate TEVENTACTIONRUN by RUNSTATUS for a playground run
        // =====================================================================
        public const string GET_TASK_STATUS_COUNTS = @"
SELECT
    RUNSTATUS  AS RunStatus,
    COUNT(*)   AS ItemCount
FROM   TEVENTACTIONRUN
WHERE  PLAYGROUNDRUNID = @RunId
AND    TENANTID        = @TenantId
GROUP  BY RUNSTATUS
";

        // =====================================================================
        // Aggregate TACTIONOUTBOX by SENDSTATUS for a playground run
        // =====================================================================
        public const string GET_ACTION_STATUS_COUNTS = @"
SELECT
    ob.SENDSTATUS  AS SendStatus,
    COUNT(*)       AS ItemCount
FROM   TACTIONOUTBOX       ob
JOIN   TEVENTACTIONRUN     ear ON ear.ACTIONRUNID = ob.ACTIONRUNID
WHERE  ear.PLAYGROUNDRUNID = @RunId
AND    ear.TENANTID        = @TenantId
GROUP  BY ob.SENDSTATUS
";

        // =====================================================================
        // Paged list of action runs for the Queue Monitor
        // =====================================================================
        public const string GET_ACTION_QUEUE = @"
SELECT
    ear.ACTIONRUNID         AS ActionRunId,
    ear.ACTIONID            AS ActionId,
    ear.RUNSTATUS           AS RunStatus,
    ear.ATTEMPTS            AS Attempts,
    ear.ERRORMESSAGE        AS ErrorMessage,
    ear.STARTEDON           AS StartedOn,
    ear.COMPLETEDON         AS CompletedOn,
    ob.SENDSTATUS           AS SendStatus,
    ob.DESTINATIONTOPIC     AS DestinationTopic
FROM   TEVENTACTIONRUN     ear
LEFT JOIN TACTIONOUTBOX    ob  ON ob.ACTIONRUNID = ear.ACTIONRUNID
WHERE  ear.PLAYGROUNDRUNID = @RunId
AND    ear.TENANTID        = @TenantId
ORDER  BY ear.ACTIONRUNID  DESC
";

        // =====================================================================
        // Reset a failed run so ActionProcessorWorker will re-pick it up
        // =====================================================================
        public const string RESET_ACTION_RUN = @"
UPDATE TEVENTACTIONRUN
SET    RUNSTATUS    = 0,
       ATTEMPTS     = 0,
       ERRORMESSAGE = NULL,
       MODIFIEDON   = GETDATE(),
       MODIFIEDBYID = @ModifiedById
WHERE  ACTIONRUNID  = @ActionRunId
AND    TENANTID     = @TenantId
";

        public const string RESET_OUTBOX_FOR_RUN = @"
UPDATE TACTIONOUTBOX
SET    SENDSTATUS   = 0,
       ATTEMPTS     = 0,
       MODIFIEDON   = GETDATE(),
       MODIFIEDBYID = @ModifiedById
WHERE  ACTIONRUNID  = @ActionRunId
AND    TENANTID     = @TenantId
";
    }
}