namespace AutomationDAL.Query.DocumentFlow;

public static class DocumentFlowQB
{
    // Covering index required: IX_TDOCUMENTFLOW_ORDER_STAGE (unique, TENANTID, ORDERID, STAGE)
    public const string GET_BY_ORDER_STAGE = @"
        SELECT FLOWID, TENANTID, ORDERID, STAGE, STATUS, RUNID, ACKCALLBACKURL, CREATEDON, UPDATEDON
        FROM   AUTO.TDOCUMENTFLOW
        WHERE  TENANTID = @TenantId
        AND    ORDERID  = @OrderId
        AND    STAGE    = @Stage";

    public const string GET_BY_ORDER = @"
        SELECT FLOWID, TENANTID, ORDERID, STAGE, STATUS, RUNID, ACKCALLBACKURL, CREATEDON, UPDATEDON
        FROM   AUTO.TDOCUMENTFLOW
        WHERE  TENANTID = @TenantId
        AND    ORDERID  = @OrderId
        ORDER BY CREATEDON";

    // Upsert-by-(TenantId,OrderId,Stage) — a Failed row can be retried (overwritten back to
    // Pending with a new RunId); a Pending/Success row is left alone by the caller's own
    // idempotency check before this ever runs, so this statement itself doesn't need to re-guard.
    public const string UPSERT_PENDING = @"
        UPDATE AUTO.TDOCUMENTFLOW
        SET    STATUS         = 'Pending',
               RUNID          = @RunId,
               ACKCALLBACKURL = @AckCallbackUrl,
               UPDATEDON      = GETUTCDATE()
        WHERE  TENANTID = @TenantId
        AND    ORDERID  = @OrderId
        AND    STAGE    = @Stage;

        IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO AUTO.TDOCUMENTFLOW (TENANTID, ORDERID, STAGE, STATUS, RUNID, ACKCALLBACKURL, CREATEDON)
            VALUES (@TenantId, @OrderId, @Stage, 'Pending', @RunId, @AckCallbackUrl, GETUTCDATE());
        END";

    public const string UPDATE_STATUS_BY_RUNID = @"
        UPDATE AUTO.TDOCUMENTFLOW
        SET    STATUS    = @Status,
               UPDATEDON = GETUTCDATE()
        WHERE  RUNID = @RunId";

    public const string GET_BY_RUNID = @"
        SELECT FLOWID, TENANTID, ORDERID, STAGE, STATUS, RUNID, ACKCALLBACKURL, CREATEDON, UPDATEDON
        FROM   AUTO.TDOCUMENTFLOW
        WHERE  RUNID = @RunId";
}